久久99热只有频精品8,国产精品亚洲综合专区片高清久久久,2019久久久高清456

一、mysql主從復制

1、簡介

我們為什么要用主從復制？

主從復制目的：

可以做數據庫的實時備份，保證數據的完整性；

可做讀寫分離，主服務器只管寫，從服務器只管讀，這樣可以提升整體性能。

原理圖：

詳解MySQL的主從復制、讀寫分離、備份恢復

從上圖可以看出，同步是靠log文件同步讀寫完成的。

2、更改配置文件

兩天機器都操作，確保 server-id 要不同，通常主ID要小于從ID。一定注意。

#?3306和3307分別代表2臺機器 #?打開log-bin,并使server-id不一樣 #vim?/data/3306/my.cnf log-bin?=?/data/3306/mysql-bin server-id?=?1 #vim?/data/3307/my.cnf log-bin?=?/data/3307/mysql-bin server-id?=?3 #檢查 1、 [root@bogon?~]#?egrep?"log-bin|server-id"?/data/3306/my.cnf log-bin?=?/data/3306/mysql-bin server-id?=?1 [root@bogon?~]#?egrep?"log-bin|server-id"?/data/3307/my.cnf? log-bin?=?/data/3307/mysql-bin server-id?=?3 2、 [root@localhost?~]#?mysql?-uroot?-p?-S?/data/3306/mysql.sock?-e?"show?variables?like?'log_bin';" Enter?password: +--------+--------+ |?Variable_name?|?Value?| +--------+--------+ |?log_bin????|?ON?|??#?ON?為開始開啟成功 +--------+--------+

3、建立用于從庫復制的賬號rep

通常會創建一個用于主從復制的專用賬戶，不要忘記授權。

#?主庫授權，允許從庫來連接我取日志 [root@localhost?~]#?mysql?-uroot?-p?-S?/data/3306/mysql.sock Enter?password: #?允許從庫192.168.200網段連接，賬號rep，密碼nick。 mysql&gt;?grant?replication?slave?on?*.*?to?'rep'@'192.168.200.%'?identified?by?'nick'; Query?OK,?0?rows?affected?(0.00?sec) mysql&gt;?flush?privileges; Query?OK,?0?rows?affected?(0.00?sec) #?檢查創建的rep賬號： mysql&gt;?select?user,host?from?mysql.user; +-----+-------------+ |?user?|?host???????| +-----+--------------+ |?root?|?127.0.0.1?????| |?rep?|?192.168.200.%???| |?root?|?localhost??????| |?root?|?localhost.localdomain?| +-----+------------------+ 7??rows?in?set?(0.00?sec)

4、備份主庫，及恢復到從庫

把主庫現有數據備份下來，再恢復到從庫，此時兩個主機的數據一致。

如果事先有數據的話，這不不能忘。

1)在主庫上加鎖，使只有只讀權限。

mysql&gt;?flush?table?with?read?lock; Query?OK,?0?rows?affected?(0.00?sec) #5.1、5.5鎖表命令略有不同。 #?5.1鎖表：flush?tables?with?read?lock; #?5.5鎖表：flush?table?with?read?lock;

2)記住就是這個點備份的。

mysql&gt;?show?master?status; +-------+------+--------+---------+ |?File???????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?| +-------+------+--------+---------+ |?mysql-bin.000013?|??410?|???????|????????| +-------+------+--------+---------+ 1?row?in?set?(0.00?sec)

3)克隆窗口，備份數據。

[root@bogon?~]#?mysqldump?-uroot?-p?-S?/data/3306/mysql.sock?-A?-B?--events?--master-data=2|gzip?&gt;/opt/rep.sql.gz Enter?password: 參數：??-A：備份所有的 #看rep.sql.gz參數 vim?/opt/rep.sql.gz --?CHANGE?MASTER?TO?MASTER_LOG_FILE='mysql-bin.000013',?MASTER_LOG_POS=410;

4)查看master status；數值是否正常。

mysql&gt;?show?master?status; +------+------+---------+-------+ |?File??????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?| +-------+-----+---------+--------+ |?mysql-bin.000013?|??410?|??????|????????| +--------+----+---------+--------+ 1?row?in?set?(0.00?sec)

5)解鎖庫

mysql&gt;?unlock?tables; Query?OK,?0?rows?affected?(0.00?sec)

6)恢復到從庫

[root@bogon?~]#?gunzip?<p><strong>5、配置從庫及生效</strong><br></p><p>更改從庫和主庫的連接參數，配置生效。檢查就成功了！<strong><br></strong></p><p><strong>1)進入從庫。</strong><br></p><pre class="brush:xml;">[root@bogon?~]#?mysql?-uroot?-p?-S?/data/3307/mysql.sock Enter?password:

2)更改從屬服務器用于與主服務器進行連接和通訊的參數。

mysql&gt;?CHANGE?MASTER?TO ???MASTER_HOST='192.168.200.98', ???MASTER_PORT=3306, ???MASTER_USER='rep', ???MASTER_PASSWORD='nick', ???MASTER_LOG_FILE='mysql-bin.000013', ???MASTER_LOG_POS=410; Query?OK,?0?rows?affected?(0.01?sec)

3)查看更改的參數。

[root@localhost?~]#?cd?/data/3307/data/ [root@localhost?data]#?cat?master.info 18 mysql-bin.000013 410 192.168.200.98 REP nick 3306 60 0 0 1800.000 0

4)生效！

mysql&gt;?start?slave; Query?OK,?0?rows?affected?(0.01?sec)

5)檢查下列參數，符合則正常！

mysql&gt;?show?slave?statusG Relay_Master_Log_File:?mysql-bin.000013 ???????Slave_IO_Running:?Yes????#取logo。 ??????Slave_SQL_Running:?Yes????#讀relay-bin、logo,寫數據。 Seconds_Behind_Master:?0????#落后主庫的秒數。

6)查看relay-bin.logo。

[root@localhost?3307]#?cd?/data/3307 [root@localhost?3307]#?ll 總用量?48 drwxr-xr-x.?9?mysql?mysql?4096?10月?29?18:52?data -rw-r--r--.?1?mysql?mysql?1900?10月?29?11:45?my.cnf -rwx------.?1?root?root??1307?10月?20?17:06?mysql -rw-rw----.?1?mysql?mysql???6?10月?29?11:00?mysqld.pid -rw-r-----.?1?mysql?mysql?15090?10月?29?18:49?mysql_nick3307.err srwxrwxrwx.?1?mysql?mysql???0?10月?29?11:00?mysql.sock -rw-rw----.?1?mysql?mysql??150?10月?29?18:49?relay-bin.000001 -rw-rw----.?1?mysql?mysql??340?10月?29?18:52?relay-bin.000002 -rw-rw----.?1?mysql?mysql??56?10月?29?18:49?relay-bin.index -rw-rw----.?1?mysql?mysql??53?10月?29?18:52?relay-log.info

7)查看relay-log.info。

[root@localhost?3307]#?cat?relay-log.info /data/3307/relay-bin.000002 340 mysql-bin.000013 497

8)查看master.info。

[root@localhost?3307]#?cat?data/master.info 18 mysql-bin.000013 497 192.168.200.98 rep nick 3306 60 0 0 1800.000 0

6、讀寫分離

讀寫分離在生產環境比比皆是，也是必備技能。

忽略MySQL主從復制授權表同步，讀寫分離。

[root@bogon?3306]#?vim?my.cnf #添加以下四行 replicate-ignore-db?=?mysql binlog-ignore-db?=?mysql binlog-ignore-db?=?performance_schema binlog-ignore-db?=?information_schema server-id?=?1

1)通過read-only參數防止數據寫入從庫的方法。

#修改配置文件。 vim?/data/3307/my.cnf [mysqld] read-only #對用戶授權事不能指定有super或all?privileges權限。不然沒效果。 #創建賬戶suoning，并刷新權限。 mysql&gt;?grant?select,insert,update,delete?on?*.*?to?'suoning'@'localhost'?identified?by?'123'; Query?OK,?0?rows?affected?(0.00?sec) mysql&gt;?flush?privileges; Query?OK,?0?rows?affected?(0.00?sec) #用創建賬戶登錄，并創建庫 [root@bogon?3307]#?mysql?-usuoning?-p123?-S?/data/3307/mysql.sock mysql&gt;?create?user?kangkangkang@'192.%'?identified?by?'old123'; ERROR?1290?(HY000):?The?MySQL?server?is?running?with?the?--read-only?option?so?it?cannot?execute?this?statement

2)主從同步故障

A.?Last_SQL_Errno:?1007 stop?slave; set?global?sql_slave_skip_counter?=?1; start?slave; B.忽略 skip-name-resolve????#忽略名字解析 slave-skip-errors?=?1032,1062,1007??#忽略故障編號 server-id?=?3

3)從庫開啟bin-log

vim?my.cnf log-bin?=?/data/3307/mysql-bin log-slave-updates??????#表示從庫記錄bin-log expire_logs_days?=?7????#保留7天bin-log。

7、主宕機

我們來模擬一下，如果主機宕機了，那我們該如何讓從快速替換，讓損失降到最小？當然了，雙機熱備也是一個不錯的選擇，那下節會給大家講解。

一主多從的環境下，如果主宕機了，選一臺從做主，繼續和其它從同步。

A.查看每個從庫的master.info，看誰的更靠前，最新，更大，丟的數據最少。 [root@localhost?3307]#?cat?/data/3307/data/master.info mysql-bin.000015 326 B.確保所有relay?log全部更新完畢。 在每個從庫上執行stop?slave?io_thread;show?processlist; 知道看到Has?read?all?relay?log;表示從庫更新都執行完畢。 C.登陸mysql?-uroot?-p?-S?/data/3307/mysql.sock stop?slave; reset?master; quit D.進入到數據庫目錄，刪除master.info?relay-log.info cd?/data/3307/data/ rm?-f?master.info?relay-log.info E.?3307提升為主庫 vim?/data/3307/my.cnf 開啟log-bin?=?/data/3307/mysql-bin 如存在log-slave-updates，read-only等一定要注釋。 /data/3307/mysql?restart F.其它從庫操作 stop?slave; change?master?to?master_host?='192.168.200.98'; start?slave; show?slave?statusG

8、雙主

使用主主前提：表的主鍵自增。

雙主情況下，ID會是這樣情況，通過程序指定ID寫庫M1：1,3,5，M2：2,4,6。

[root@localhost?3307]#?vim?my.cnf [mysqld] auto_increment_increment????=?2 auto_increment_offset??????=?2 [root@localhost?3307]#?./mysql?restart [root@localhost?3306]#?vim?my.cnf [mysqld] auto_increment_increment????=?2 auto_increment_offset??????=?1 log-bin?=?/data/3306/mysql-bin log-slave-updates [root@localhost?3306]#?./mysql?restart [root@localhost?3306]#?mysqldump?-uroot?-pnick?-S?/data/3307/mysql.sock?-A?-B?--master-data=1?-x?--events?&gt;?/opt/3307bak.sql [root@localhost?3306]#?mysql?-uroot?-pnick?-S?/data/3306/mysql.sock??CHANGE?MASTER?TO ???MASTER_HOST='192.168.200.98', ???MASTER_PORT=3307, ???MASTER_USER='rep', ???MASTER_PASSWORD='nick'; mysql&gt;?start?slave; Query?OK,?0?rows?affected?(0.00?sec) mysql&gt;?show?slave?status?G

二、MySQL備份及恢復

1、備份單個數據庫

最基礎的備份單個數據庫。

1&gt;語法：mysqldump?–u?用戶名?–p?數據庫名&gt;?備份的數據庫名 2&gt;備份nick_defailt數據庫，查看內容。 [root@localhost?~]#?mysqldump?-uroot?-p?-B?nick_defailt?&gt;/opt/mysql_nick_defailt.bak Enter?password: [root@localhost?~]#?egrep?-v?"#|*|--|^$"?/opt/mysql_nick_defailt.bak DROP?TABLE?IF?EXISTS?`oldsuo`; CREATE?TABLE?`oldsuo`?( ?`id`?int(4)?NOT?NULL, ?`name`?char(20)?NOT?NULL, ?`age`?tinyint(2)?NOT?NULL?DEFAULT?'0', ?`dept`?varchar(16)?DEFAULT?NULL )?ENGINE=MyISAM?DEFAULT?CHARSET=latin1; LOCK?TABLES?`oldsuo`?WRITE; INSERT?INTO?`oldsuo`?VALUES?(2,'?′￠???',0,NULL),(3,'?′￠?°?',0,NULL),(4,'?o??o?',0,NULL); UNLOCK?TABLES; DROP?TABLE?IF?EXISTS?`student`; CREATE?TABLE?`student`?( ?`qq`?varchar(15)?DEFAULT?NULL, ?`id`?int(4)?NOT?NULL?AUTO_INCREMENT, ?`name`?char(20)?NOT?NULL, ?`suo`?int(4)?DEFAULT?NULL, ?`age`?tinyint(2)?NOT?NULL?DEFAULT?'0', ?`dept`?varchar(16)?DEFAULT?NULL, ?`sex`?char(4)?DEFAULT?NULL, ?PRIMARY?KEY?(`id`), ?KEY?`index_name`?(`name`) )?ENGINE=MyISAM?AUTO_INCREMENT=8?DEFAULT?CHARSET=latin1; LOCK?TABLES?`student`?WRITE; INSERT?INTO?`student`?VALUES?(NULL,2,'oldsuo',NULL,0,NULL,NULL),(NULL,3,'kangknag',NULL,0,NULL,NULL),(NULL,4,'kangkang',NULL,0,NULL,NULL),(NULL,5,'oldsuo',NULL,0,NULL,NULL),(NULL,6,'kangknag',NULL,0,NULL,NULL),(NULL,7,'kangkang',NULL,0,NULL,NULL); UNLOCK?TABLES; 3&gt;啟用壓縮備份數據庫 [root@localhost~]#mysqldump?-uroot?-p?-B?nick_defailt|gzip&gt;/opt/mysql_nick_defailt.bak.gz Enter?password: [root@localhost?~]#?ll?/opt/ 總用量?28 -rw-r--r--.?1?root?root?2109?10月?24?16:36?data_bak.sq2 -rw-r--r--.?1?root?root?2109?10月?24?16:36?data_bak.sql -rw-r--r--.?1?root?root?1002?10月?27?11:55?mysql_nick_defailt.bak -rw-r--r--.?1?root?root?1002?10月?27?11:56?mysql_nick_defailt.bak.gz -rw-r--r--.?1?root?root?3201?10月?27?11:46?mysql_nick_defailt_B.bak drwxr-xr-x.?2?root?root?4096?11月?22?2013?rh -rw-r--r--.?1?root?root?1396?10月?24?16:11?student_bak.sql 4&gt;恢復nick_defailt數據庫 [root@localhost?~]#?mysql?-uroot?-p?nick_defailt?/mysql_nick_defailt.bak? Enter?password: #加-B恢復方法 [root@localhost?~]#?mysql?-uroot?-p?/mysql_nick_defailt_B.bak???????? Enter?password: 5&gt;總結 1、備份用-B參數。增加use?db,和create?database的信息。 2、用gzip對備份的數據壓縮。

2、備份多個數據庫

備份多個數據庫的情況呢？

#多個數據庫名中間加空格 [root@localhost?~]#?mysqldump?-uroot?-p?-B?nick_defailt?oldsuo?oldsuo_1|gzip&gt;/opt/mul.sql.gz Enter?password:

3、備份單個及多個表

那如果備份單個和多個表，怎么辦？

1&gt;語法：mysqldump?-u?用戶名?-p?數據庫名?表名?&gt;?備份的文件名 [root@localhost?~]#?mysqldump?-uroot?-p?nick_defailt?student?&gt;/opt/mysql_nick_defailt_student.bak Enter?password: 2&gt;語法：mysqldump?-u?用戶名?-p?數據庫名?表名1?表名2?&gt;?備份的文件名 [root@localhost?~]#?mysqldump?-uroot?-p?nick_defailt?student?oldsuo?&gt;/opt/mysql_nick_defailt.bak???? Enter?password:

4、mysqldump 的參數

mysqldump 的關鍵參數

-B指定多個庫，增加建庫語句和use語句。 --compact去掉注釋，適合調試輸出，生產不用。 -A?備份所有庫。 -F刷新binlog日志。 --master-data?增加binlog日志文件名及對應的位置點。 -x，--lock-all-tables -l,--locktables -d?只備份表結構 -t?只備份數據 --single-transaction?適合innodb事務數據庫備份。

5、增量恢復

重要的來了，生產環境一般是增量備份與恢復；所謂增量，就是在原數據的基礎上繼續添加數據，不必每次都重新添加，省時省力。

A：增量恢復必備條件： 1.開啟MySQL數據庫log-bin參數記錄binlog日志。 [root@localhost?3306]#?grep?log-bin?/data/3306/my.cnf log-bin?=?/data/3306/mysql-bin 2.存在數據庫全備。 B：生產環境?mysqldump備份命令： #?進行數據庫全備，（生產環境還通過定時任務每日凌晨執行） mysqldump?-uroot?-pnick?-S?/data/3306/mysql.sock?--default-character-set=gbk?--single-transaction?-F?-B?nick?|gzip?&gt;/server/backup/mysql_$(date?+%F).sql.gz #?innodb引擎備份 mysqldump?-u$MYUSER?-p$MYPASS?-S?$MYSOCK?-F?--single-transaction?-A?-B?|gzip?&gt;$DATA_FILE #?myisam引擎備份 mysqldump?-u$MYUSER?-p$MYPASS?-S?$MYSOCK?-F?-A?-B?--lock-all-tables?|gzip?&gt;$DATA_FILE C：恢復： #?通過防火墻禁止web等應用向主庫寫數據或者鎖表。讓主庫暫時停止更新，然后再進行恢復。 #?誤操作刪除nick庫！ 1.檢查全備及binlog日志 [root@localhost?3306]#?cd?/server/backup/ [root@localhost?backup]#?gzip?-d?mysql_2015-10-31.sql.gz [root@localhost?backup]#?vim?mysql_2015-10-31.sql [root@localhost?backup]#?grep?-i?"change"?mysql_2015-10-31.sql

2.立即刷新并備份出binlog

[root@localhost?3306]#?mysqladmin?-uroot?-pnick?-S?/data/3306/mysql.sock?flush-logs [root@localhost?3306]#?cp?/data/3306/mysql-bin.000030?/server/backup/ #誤操作log-bin,倒數第二 [root@localhost?backup]#?mysqlbinlog?-d?nick?mysql-bin.000030?&gt;bin.sql??#導出為.sql格式。 [root@localhost?backup]#?vim?bin.sql 找到語句drop?database?nick刪除！！！（誤操作語句）

3.恢復

[root@localhost?backup]#?mysql?-uroot?-pnick?-S?/data/3306/mysql.sock?<mysql_2015-10-31.sql><p>

文章版權歸作者所有，未經允許請勿轉載。

THE END

數據庫
# mysql

喜歡就支持一下吧

點贊8