主從復制及相關 · 王猛-技術整理

[TOC] ### 一、主從備份 ***** #### 主服務器Slave配置 #### 創建同步用戶 ``` mysql> grant replication slave on \*.\* to 'replicate'@'218.206.70.146' identified by '123456'; mysql> flush privileges; ``` #### 測試用戶 ``` mysql -h59.151.15.36 -ureplicate -p123456 ``` #### 修改mysql配置文件 ``` server-id = 1　　　　　　　　//唯一id log-bin=mysql-bin ? ? ? ? ? ? ?//其中這兩行是本來就有的，可以不用動，添加下面兩行即可.指定日志文件 binlog-do-db = test　　　　?//記錄日志的數據庫 binlog-ignore-db = mysql ? ?//不記錄日志的數據庫 ``` #### **重啟mysql服務** ``` systemctl restart mysqld.service systemctl start mysqld.service systemctl stop mysqld.service ``` #### **查看主服務器狀態** `show master status\G;` ***** #### 從服務器Slave配置 #### **修改配置文件** ``` server-id = 2 log-bin=mysql-bin replicate-do-db = test replicate-ignore-db = mysql,information\_schema,performance\_schema ``` #### 重啟從mysql服務 ``` systemctl restart mysqld.service systemctl start mysqld.service systemctl stop mysqld.service ``` #### 用change mster?語句指定同步位置 ``` mysql>stop slave;????????? //先停步slave服務線程，這個是很重要的，如果不這樣做會造成以下操作不成功。 mysql>change master to master_host='59.151.15.36', master_user='replicate',master_password='123456', master_log_file=' mysql-bin.000016 ', master_log_pos=107; ``` #### 查看從服務器（Slave）狀態 ``` show slave status\G; ``` #### 查看下面兩項值均為Yes，即表示設置從服務器成功 ``` Slave\_IO\_Running: Yes Slave\_SQL\_Running: Yes ``` ***** ## 二、主主備份 #### 創建同步用戶服務器A： ``` mysql> grant replication slave on \*.\* to 'replicate'@'218.206.70.146' identified by '123456'; mysql> flush privileges; ``` 服務器B： ``` mysql> grant replication slave on \*.\* to 'replicate'@'59.151.15.36' identified by '123456'; mysql> flush privileges; ``` #### 修改配置文件my.cnf 服務器A ``` server-id = 1 log-bin=mysql-bin? binlog-do-db = test binlog-ignore-db = mysql ``` 主－主形式需要多添加的部分 ``` log-slave-updates sync_binlog = 1 auto_increment_offset = 1 auto_increment_increment = 2 replicate-do-db = test replicate-ignore-db = mysql,information_schema ``` 服務器B： ``` server-id = 2 log-bin=mysql-bin? replicate-do-db = test replicate-ignore-db = mysql,information\_schema,performance\_schema ``` 主－主形式需要多添加的部分 ``` binlog-do-db = test binlog-ignore-db = mysql log-slave-updates sync_binlog = 1 auto_increment_offset = 2 auto_increment_increment = 2 ``` #### 分別重啟A服務器和B服務器上的mysql服務 #### 分別查A服務器和B服務器作為主服務器的狀態 #### 分別在A服務器和B服務器上用change master to?指定同步位置服務器A： ``` mysql>change master to \>master\_host='218.206.70.146',master\_user='replicate',master\_password='123456', \> master\_log\_file=' mysql-bin.000011 ',master\_log\_pos=497; ``` 服務器B： ``` mysql>change master to \>master\_host='59.151.15.36',master\_user='replicate',master\_password='123456', \> master\_log\_file=' mysql-bin.000016 ',master\_log\_pos=107; ``` #### 分別在A和B服務器上重啟從服務線程 `mysql>start slave;` #### 分別在A和B服務器上查看從服務器狀態 `mysql>show slave status\\G;` #### 查看下面兩項值均為Yes，即表示設置從服務器成功。 ``` Slave\_IO\_Running: Yes Slave\_SQL\_Running: Yes ``` [https://www.cnblogs.com/jianmingyuan/p/10903682.html]()