行業動態

MYSQL 主從配置有很多好處，一個是起著實時備份的作用，另外一個也可以讀寫分離，減輕網站的負載，baidu出來的很多配置其實很有問題，在這里重新梳理一下，首先了解一下原理。

該過程的第一部分就是master記錄二進制日志。在每個事務更新數據完成之前，master在二日志記錄這些改變。MySQL將事務串行的寫入二進制日志，即使事務中的語句都是交叉執行的。在事件寫入二進制日志完成后，master通知存儲引擎提交事務。下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然后開始binlog dump process。Binlog dump process從master的二進制日志中讀取事件，如果已經跟上master，它會睡眠并等待master產生新的事件。I/O線程將這些事件寫入中繼日志。

SQL slave thread處理該過程的最后一步。SQL線程從中繼日志讀取事件，更新slave的數據，使其與master中的數據一致。只要該線程與I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志的開銷很小。

此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制過程有一個很重要的限制——復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

一、 前提條件：

1. 數據庫服務器最起碼兩臺，假設是數據庫MYSQL-A, 數據庫MYSQL-B，MYSQL-A的IP 10.0.0.2，MYSQL-B的ip是10.0.0.3；

用戶： root /root

數據庫 virt

2. 建議主從數據庫的版本一致，如果不一致很容易出錯，或者在mysql的配置文檔 my.cnf(window為my.ini) 加上binlog_checksum =none 參數

3. 刪除所有的外鍵，表與表的依賴關系最容易導致主從復制失敗，一般而言，外鍵在測試環境上測試校驗數據的完整性，而在生產環境最好刪除外鍵，刪除外鍵，可以通過mysql數據字典生成刪除腳本，如下

SELECT CONCAT("alter table ",a.`TABLE_SCHEMA`,".",TABLE_NAME," drop foreign key ",CONSTRAINT_NAME,";")

FROM KEY_COLUMN_USAGE a

WHERE a.`table_SCHEMA`="virt" AND  REFERENCED_TABLE_NAME  IS NOT NULL

4. 設置主鍵的不長，通常情況下，數據庫的主鍵是無意義的ID，而且是自增長的，如果數據庫進行主主備份，那么互相復制的過程容易產生主鍵沖突，所有需要設置自增長的步長和奇偶數。

二、 同步用戶

你用root做數據庫同步賬號當然也沒有問題，但更多是建議建立復制賬號，如下

CREATE USER 'backup'@'%' IDENTIFIED BY 'backup';

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.0.0.%' IDENTIFIED BY 'backup';

三、 在同步前先讓兩臺數據庫的數據一致

1. MYSQL-A  下執行SQL命令：flush tables with read lock;目的是鎖表

2. MYSQL-B下面執行命令：mysqldump -h10.0.0.2 -uroot -proot virt > var/backup/virtback.sql；備份數據；

3. MYSQL-A下執行SQL命令：unlock tables；解除鎖定。

4. MYSQL-B 執行命令：mysql -uroot -proot virt < var/backup/virtback.sql；還原數據

四、 配置數據庫主從備份參數

MYSQL-A, vi /etc/my.cnf,下面的參數需要處理一下

server-id = 1

log-bin=/var/lib/mysql/mysql-bin.log

binlog_format=mixed

binlog-do-db=virt

binlog-ignore-db=mysql  

binlog-ignore-db=information_schema  

binlog-ignore-db=performance_schema  

innodb_flush_log_at_trx_commit=1

sync_binlog=1  

expire_logs_days = 5

auto_increment_increment=2

auto_increment_offset=1

MYSQL-B, vi /etc/my.cnf,下面的參數需要處理一下

server-id = 2

log-bin=/var/lib/mysql/mysql-bin.log

binlog_format=mixed

binlog-do-db=virt

binlog-ignore-db=mysql  

binlog-ignore-db=information_schema  

binlog-ignore-db=performance_schema  

innodb_flush_log_at_trx_commit=1

sync_binlog=1  

expire_logs_days = 5

auto_increment_increment=2

auto_increment_offset=2

五、 重啟MYSQL服務

MYSQL-A，MYSQL-B數據庫服務器重啟

命令：service mysql restart

你也可以/etc/init.d/mysqld restart

如果以前有主從備份或者日志，也可以執行SQL命令清除日志

PURGE MASTER LOGS BEFORE '2017-02-21 00:00:00';

RESET MASTER;

六、 從MYSQL-A到MYSQL-B進行復制過程

1. 檢查MYSQL-A 的日志情況，在MYSQL-A執行SQL命令

show master status

File 和 Position在復制過程中要用到。

MASTER_LOG_FILE ， MASTER_LOG_POS

2. 在 MYSQL-B執行SQL命令

#SHOW SLAVE STATUS

CHANGE MASTER TO MASTER_HOST='10.0.0.2',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

START SLAVE;

#STOP SLAVE

3. 查看狀態

MYSQL-B 上面SQL 命令 SHOW SLAVE STATUS

你會看到slave_io_running,slave_sql_runing 的狀態都是yes 那么表示從MYSQL-A到MYSQL-B進行復制過好了

七、 從MYSQL_B到MYSQL-A進行復制過程

4. 檢查MYSQL-B 的日志情況，在MYSQL-B執行SQL命令

show master status

File 和 Position在復制過程中要用到。

MASTER_LOG_FILE ， MASTER_LOG_POS

5. 在 MYSQL-A執行SQL命令

#SHOW SLAVE STATUS

CHANGE MASTER TO MASTER_HOST='10.0.0.2',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

START SLAVE;

#STOP SLAVE

6. 查看狀態

MYSQL-A 上面SQL 命令 SHOW SLAVE STATUS

 你會看到slave_io_running,slave_sql_runing 的狀態都是yes 那么表示從MYSQL-B到MYSQL-A進行復制過好了

八、 測試

1. 建立表

CREATE TABLE `masterslavetest` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `item` varchar(30) DEFAULT NULL,

  `value` varchar(30) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `id` (`id`)

) ENGINE=InnoDB;

2. 在mysql-a 插入數據

INSERT INTO masterslavetest(`item`,`value`) VALUES ("mysql-a","mysql-a");

3. 在mysql-b 查看

執行SQL 命令 SELECT * FROM masterslavetest

4. 在mysql-a 插入數據

INSERT INTO masterslavetest(`item`,`value`) VALUES ("mysql-b","mysql-b");

5. 在mysql-A 查看

通過以上測試，可以說明主從備份，主主互相備份成功了