MySQL Replication主從複製—（執行個體）

MySQL Replication主從複製—（執行個體）

主從複製原理

• MySQL Replication是一個從Master複製到一台或多台Slave的非同步複製過程。
• Master和Slave之間實現整個複製過程主要由三個線程來完成，其中一個IO線程在Master端，兩個線程（SQL線程和IO線程）在Slave端。
• 通過Master伺服器開啟Binary Log（二進位記錄檔）的功能，Slave端從Master端擷取該日誌資訊，然後將二進位檔案解析為SQL語句，並完全順序地執行SQL語句所記錄的各種操作。（Slave擷取到的二進位檔案同時也會寫入到自身的Relay Log檔案中）

Replication概念

• MySQL Replication技術是一個日誌複製過程，在複製過程中一台伺服器充當主，一台或多台其他伺服器充當從伺服器；
• 從伺服器到主伺服器拉取二進位記錄檔，將記錄檔解析成相應的SQL語句，然後在從伺服器上重新執行一遍主伺服器的操作，通過這種方式保證資料的一致性。

主從複製配置步驟：

• 設定server-id（伺服器標識，在一組主從中不能重複）
• 開啟二進位日誌並指定二進位記錄檔儲存的路徑
• 記錄bin-log檔案和bin-log（position）位置
• 若不停在Master時，加入全域鎖，將需要同步的Database Backup到Slave節點上，解除全域鎖
• 建立用於同步複製的使用者
• 使用change master 在Slave和Master建立串連（Slave節點設定主伺服器）
• 啟動Slave
• 檢查Slave的狀態 

Step1：配置master和slave的/etc/my.cnf檔案

12345678910111213

##Master   [mysqld] basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306 socket=/var/lib/mysql/mysql.sock   server-id=1                             #服務識別 log-bin=/data/mysql/binlog/mysql-bin    #binlog記錄檔儲存的路徑 binlog-cache-size=10m                   #binlog日誌緩衝大小 sync-binlog=1                           #每隔N秒將緩衝中的二進位日誌記錄寫回硬碟 expire_logs_days=30                     #二進位記錄檔到期時間（自動清理時間）

 

12345678910111213

##Slave   [mysqld] basedir=/usr/local/mysqldatadir=/data/mysql/mysqlport=3306 socket=/var/lib/mysql/mysql.sock   server_id=2 relay-log=/data/mysql/binlog/mysql-relay-binreplicate-wild-do-table=testdb1.%        #指定需要同步的資料庫 replicate-wild-do-table=testdb2.%        #指定需要同步的資料庫                                          #（與之對應的是replicate-wild-ignore-table）

Step2：手動同步資料庫到slave

1、鎖定Master的表的寫操作（不要退出終端）

12	mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

2、Master上備份資料庫並傳到Slave上

12345

[root@node1 ~]# mysqldump -uroot -pRedHat testdb1 > /root/testdb1.sql [root@node1 ~]# tar zcf testdb1.tar.gz testdb1.sql   [root@node1 ~]# rsync -av /root/testdb1.tar.gz 192.168.1.211:/root/ [root@node1 ~]# scp /root/testdb1.tar.gz root@192.168.1.211:/tmp/

 

3、Slave上建立同步的資料庫並匯入資料檔案

123456789

[root@node2 ~]# mysql -uroot -predhat -e 'create database testdb1' [root@node2 ~]# mysql -uroot -predhat testdb1 < testdb1.sql  mysql> show tables; +-------------------+ | Tables_in_testdb1 | +-------------------+ | tt1               | | tt2               | +-------------------+

 

Step3：Master建立同步的使用者

 

1、Master解除鎖定

1	mysql> unlock tables;

2、建立同步處理的使用者

12	mysql> grant replication slave on *.* to 'repl_user'@'192.168.1.211' identified by 'repl_passwd'; mysql> flush privileges;

 

Step4：Slave串連Master，將node1設定為自己的主伺服器

1、查看Master的master-log-file檔案和position位置

123456

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      120 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+

2、Slave上串連Master，並啟動slave

12345678910

mysql>  change master to master_host='192.168.1.210', master_user='repl_user', master_password='repl_passwd', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=120;   mysql> start slave;

3、Slave上查看slave狀態（Slave_IO_Running、Slave_SQL_Running和Seconds_Behind_Master）

1234567891011121314151617181920212223242526272829303132333435

mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.210                   Master_User: repl_user                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 120                Relay_Log_File: mysql-relay-bin.000002                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000001              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table: testdb1.%,testdb2.%   Replicate_Wild_Ignore_Table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 471               Relay_Log_Space: 807               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0

Step5：測試主從同步功能

1、Master上建立資料庫和表

123	mysql> insert into tt1(id,name) values(1,'hoai'),(2,'dime');   mysql> create database testdb2;

 

2、Slave上查看是否同步

12345678910111213141516171819202122

mysql> select * from tt1; +------+------+ | id   | name | +------+------+ |    1 | hoai | |    2 | dime | +------+------+ 2 rows in set (0.00 sec)     mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | | testdb1            | | testdb2            | +--------------------+ 6 rows in set (0.00 sec)

清除二進位日誌方法（重設主從）：

mysql> reset master;

mysql> reset slave;（針對從上的relay-log檔案） 

本文永久更新連結地址：