MySql 主從、主主同步,異步主主同步

1、MySql 主從、主主同步，異步主主同步主從模式備份A、環(huán)境描述服務器A(主) 192.168.1.114服務器B(從) 192.168.1.115Mysql版本：Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3System OS：ubuntu 14.10主從需同步的數(shù)據(jù)庫內容保持一致。B、主從配置過程主服務器 a)創(chuàng)建同步用戶在主服務器上為從服務器建立一個連接帳戶，該帳戶必須授予REPLICAITON SLAVE權限。服務器A：添加一個有 slave權限的賬戶grant replication

2、 slave on *.* tosync192.168.1.% identified byPassword-2;flush privileges;b)修改mysql配置文件mysqldserver-id = 1log-bin=/var/log/mysql/mysql-bin binlog-do-db =mysqlc)重啟mysql服務d)、查看主服務器狀態(tài)從服務器a)修改mysql配置文件mysqldserver-id = 2log-bin =/var/log/mysql/mysql-binreplicate-do-db =mysqlb)重啟mysql服務sudo service mysql

3、restartc)用change master語句指定同步位置由于配置的sync賬戶未能成功登陸，方便實驗使用root賬戶登陸測試mysql CHANGE MASTER TO MASTER_HOST=192.168.1.114, MASTER_USER=root, MASTER_PASSWORD=Password-2, MASTER_LOG_FILE=mysql_bin.000001,MASTER_LOG_POS=871;master_log_file，master_log_pos由上面主服務器查出的狀態(tài)值中確定。master_log_file對應File，master_log_pos對應Po

4、sition。d)啟動從服務器線程mysqlstart slave;e)查看從服務器狀態(tài)mysqlshow slave statusG;。Slave_IO_Running: YesSlave_SQL_Running: Yes。C、測試主從同步在114的mysql數(shù)據(jù)表里插入一條新的數(shù)據(jù)，115同步備份異常，以下提示mysqlstart slave; ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO解決辦法：確認slave和master的上的

5、server_id是否正確?？梢苑謩e在slave和master上運行 SHOW VARIABLES LIKE server_id; 來查看server_id是否和你配置的一樣。如果不一樣可以使用set global server_id=2;更改server_id的值。show slave statusG;。Slave_IO_Running: NoSlave_SQL_Running: Yes。Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: Could not find first

6、 log file name in binary log index file。解決辦法：數(shù)據(jù)庫主從出錯： Slave_IO_Running: No 一方面原因是因為網(wǎng)絡通信的問題也有可能是日志讀取錯誤的問題。以下是日志出錯問題的解決方案： Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: Could not find first log file name in binary log index file解決辦法：從機器停止slavemysql slave stop;到mast

7、er機器登陸mysql：記錄master的bin的位置，例如：mysql show mster status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000001 | 107 | | information_schema,mysql |+-+-+-+-+日志為mysql-bin.000001 刷新日志：mysql flush logs;因為刷新日志file的位置會+1，即File變成為:mysql-bin.000002 馬上到slave執(zhí)行mysql CHANGE MA

8、STER TO MASTER_LOG_FILE=mysql-bin.000002,MASTER_LOG_POS=107;mysql slave start;mysql show slave statusG;主主模式備份A、環(huán)境描述服務器A(主) 192.168.1.114服務器B(從) 192.168.1.115Mysql版本：Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3System OS：ubuntu 14.10主從需同步的數(shù)據(jù)庫內容保持一致。B、主從配置過程a)創(chuàng)建同步用戶在主服務器上為

9、從服務器建立一個連接帳戶，該帳戶必須授予REPLICAITON SLAVE權限。這里服務器A和服務器B互為主從，所以都要分別建立一個同步用戶。服務器A和B：grant replication slave on *.* tosync192.168.1.% identified byPassword-2;flush privileges;b)修改mysql配置文件服務器Amysqldserver-id = 1log-bin = /var/log/mysql/mysql-binbinlog-do-db =mysql#binlog-ignore-db=mysql#主主需加入的部分log-slave-u

10、pdatessync_binlog=1auto_increment_offset=1auto_increment_increment=2replicate-do-db =mysqlreplicate-ignore-db = information_schema服務器Bmysqldserver-id = 2log-bin = /var/log/mysql/mysql-binreplicate-do-db =mysql#replicate-ignore-db = mysql,information_schema#主主需要加入部分binlog-do-db =mysql#binlog-ignore-d

11、b=mysqllog-slave-updatessync_binlog=1auto_increment_offset=2auto_increment_increment=2c)分別重啟服務器A、B上的mysql服務sudo service mysql restartd)、分別在服務器A、B上查看做為主服務器狀態(tài)show master statusG;A服務器B服務器很巧合File的編號是一樣的，其實編號可以不一樣！c)分別在服務器A、B上用change master語句指定同步位置A服務器mysqlchange master to master_host=192.168.1.115, mast

12、er_user=root, master_password=Password-2, master_log_file=mysql-bin.000003, master_log_pos=107;B服務器mysqlchange master to master_host=192.168.1.114, master_user=root, master_password=Password-2, master_log_file=mysql-bin.000003, master_log_pos=107;注：master_log_file，master_log_pos由上面主服務器查出的狀態(tài)值中確定。mast

13、er_log_file對應File，master_log_pos對應Position。d)分別在服務器A、B上啟動從服務器線程mysqlstart slave;e)分別在服務器A、B上查看從服務器狀態(tài)show slave statusG;A服務器B服務器查看以上兩項的值，均為Yes則表示狀態(tài)正常。f)、測試主主同步異常，以下提示一：Show slave statusG;Fatal error: The slave I/O thread stops because m aster and slave have equal MySQL server ids; these ids must be d

14、ifferent for rep lication to work (or the -replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).解決辦法Host_name 配置錯誤，修改即可Change master to master_host=192.168.1.114;二：Show slave statusG;Error Duplicate entry slave creat for

15、key PRIMARY on query. Default database: mysql. Query: INSERT INTO servers (Server_name, Host) VALUES (slave creat, 1.1.1.3)解決辦法修改mysql配置文件 /etc/f 在 mysqld下加一行 slave_skip_errors = 1062 ,保存.重啟mysql.三：mysql show slave statusG;Empty set (0.00 sec)ERROR:No query specified解決辦法mysql change master to master

16、_host = 192.168.1.115,master_user = root,master_Password = Password-2,master_log_file = mysql-bin.000002,master_log_pos = 394;Query OK, 0 rows affected (0.00 sec)四：mysql start slave;ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANG E MASTER TO解決辦法mysql chang

17、e master to master_host = 19.90.12.4,master_user = root,master_Password = Password-2,master_log_file = mysql-bin.000002,master_log_pos = 394;Query OK, 0 rows affected (0.00 sec)異步主主同步異步同步是：用戶讀取主庫數(shù)據(jù)前先給主庫加寫入鎖（命令：FLUSH TABLES WITH READ LOCK;此時主庫可讀），禁止寫入新數(shù)據(jù)，此時從庫可以開始更新數(shù)據(jù)，當從庫更新結束確認無誤，等待用戶訪問結束，再次解鎖主庫(命令：UN

18、LOCK TABLES;)讓其同步從庫發(fā)生的更新，如此可以避免用戶在訪問主庫的時刻，從庫更新導致的數(shù)據(jù)異常！配置參數(shù)說明server-idID值唯一的標識了復制群集中的主從服務器，因此它們必須各不相同。master_id必須為1到2321之間的一個正整數(shù)值，slave_id值必須為2到2321之間的一個正整數(shù)值。log-bin表示打開binlog,打開該選項才可以通過I/O寫到Slave的relay-log,也是可以進行replication的前提;binlog-do-db表示需要記錄進制日志的數(shù)據(jù)庫。如果有多個數(shù)據(jù)庫可用逗號分隔，或者使用多個binlog-do-db選項binlog-igno

19、re-db表示不需要記錄二進制日志的數(shù)據(jù)庫。如果有多個數(shù)據(jù)庫可用逗號分隔，或者使用多個binlog-do-db選項replicate-do-db表示需要同步的數(shù)據(jù)庫，如果有多個數(shù)據(jù)庫可用逗號分隔，或者使用多個replicate-do-db選項replicate-ignore-db=mysql表示不需要同步的數(shù)據(jù)庫，如果有多個數(shù)據(jù)庫可用逗號分隔，或者使用多個replicate-ignore-db=mysql選項log-slave-updates配置從庫上的更新操作是否寫入二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數(shù)，以便從庫的從庫能夠進行日志同步slave-skip-er

20、rors在復制過程，由于各種原因導致binlog中的sql出錯，默認情況下，從庫會停止復制，要用戶介入?？梢栽O置Slave-skip-errors來定義錯誤號，如果復制過程中遇到的錯誤號是定義的錯誤號，便可以跳過。如果從庫是用來做備份，設置這個參數(shù)會存在數(shù)據(jù)不一致，不要使用。如果是分擔主庫的查詢壓力，可以考慮。sync_binlog=1 or Nsync_binlog的默認值是0，這種模式下，MySQL不會同步到磁盤中去。這樣的話，MySQL依賴操作系統(tǒng)來刷新二進制日志binary log，就像操作系統(tǒng)刷其他文件的機制一樣。因此如果操作系統(tǒng)或機器(不僅僅是MySQL服務器)崩潰，有可能binl

21、og中最后的語句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量，使binlog在每N次binlog寫入后與硬盤同步。當sync_binlog變量設置為1是最安全的，因為在crash崩潰的情況下，你的二進制日志binary log只有可能丟失最多一個語句或者一個事務。但是，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache，使得同步到磁盤的操作非?？欤?。即使sync_binlog設置為1,出現(xiàn)崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，MySQL服務器處理COMMIT語句，它將整個事務寫入binlog并將事務提交到Inn

22、oDB中。如果在兩次操作之間出現(xiàn)崩潰，重啟時，事務被InnoDB回滾，但仍然存在binlog中。可以用innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要innodb-safe-binlog；由于引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務后，MySQL服務器從binlog剪切回滾的 InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，

23、并使從服務器保持與主服務器保持同步(不接收回滾的語句)。auto_increment_offset和auto_increment_incrementauto_increment_increment和auto_increment_offset用于主主服務器(master-to-master)復制，并可以用來控制AUTO_INCREMENT列的操作。兩個變量均可以設置為全局或局部變量，并且假定每個值都可以為1到65,535之間的整數(shù)值。將其中一個變量設置為0會使該變量為1。這兩個變量影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值,au

24、to_increment_offset確定AUTO_INCREMENT列值的起點。如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內已有一些數(shù)據(jù)，就會用現(xiàn)在已有的最大的自增值做為初始值。二進制日志清除主同步服務器產(chǎn)生的二進制日志會占據(jù)大量的磁盤空間，應定期刪除過期的bin-log。A、通過PURGE MASTER LOGS刪除如果您有一個在用的從屬服務器，該服務器當前正在讀取您正在試圖刪除的日志之一，則本語句不會起作用，而是會失敗，并伴隨一個錯誤。不過，如果從屬服務器是停

25、止的，并且您碰巧清理了其想要讀取的日志之一，則從屬服務器啟動后不能復制。當從屬服務器正在復制時，本語句可以安全運行。您不需要停止它們。要清理日志，需按照以下步驟：1、在每個從屬服務器上，使用SHOW SLAVE STATUS來檢查它正在讀取哪個日志。2、使用SHOW MASTER LOGS獲得主服務器上的一系列日志。3、在所有的從屬服務器中判定最早的日志。這個是目標日志。如果所有的從屬服務器是更新的，這是清單上的最后一個日志。4、制作您將要刪除的所有日志的備份。(建議備份)5、清理所有的日志，但是不包括目標日志。PURGE 語法PURGE MASTER | BINARY LOGS TO log

26、_namePURGE MASTER | BINARY LOGS BEFORE date用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除，這樣被給定的日志成為第一個。BEFORE變量的date自變量可以為YYYY-MM-DD hh:mm:ss格式。MASTER和BINARY是同義詞。例如：#刪除binlog.000002之前的而不包含binlog.000002mysql PURGE MASTER LOGS TO binlog.000002;#刪除2011-05-28 1:35:00之前的mysql PURGE MASTER LOGS

27、 BEFORE 2011-05-28 1:35:00;#清除3天前的binlogmysql PURGE MASTER LOGS BEFORE DATE_SUB(NOW( ), INTERVAL 3 DAY);B、設置expire-logs-days參數(shù)缺省expire-logs-days為30天。這里設為7天，可根據(jù)自己情況調整。mysqldexpire-logs-days = 7用于控制主、從服務器的SQL語句A、用于控制主服務器的SQL語句PURGE MASTER LOGS用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除，這樣被給定的日志成為第一個。RESET MASTER可以刪除列于索引文件中的所有二進制日志，把二進制日志索引文件重新設置為空，并創(chuàng)建一個新的二進制日志文件。SET SQL_LOG_BIN如果客戶端使用一個有SUPER權限的賬戶連接，則可以禁用或啟用當前連接的二進制日志記錄。如果客戶端沒有此權限，則語句被拒絕，并伴隨有錯誤。S