MySQL主從複製

原理簡介

主從複製過程存在三個線程，Master端的I/O線程，Slave的I/O線程與SQL線程。Master端需要開啟binlog日誌，Slave端需要開啟relay日誌。

1、Slave端的I/O讀取master.info文件，獲取binlog文件名和位置點，然後向Master端的I/O線程請求，該binlog文件名和位置點的binlog信息。

（master.info文件在配置主從複製時使用change master命令來指定生成）

2、Master端的I/O線程會根據Slave端的I/O線程請求的信息來讀取Master的binlog日誌信息與及讀取到最新的binlog文件名和位置點一同返回給Slave的I/O線程。

3、Slave端的I/O線程會把獲取到的binlog日誌寫入relay日誌（中繼日誌）文件中，並且更新master.info文件信息。（把讀取到Master最新的binlog日誌文件名和位置點更新到master.info文件中，下一次當前位置去讀取Master的binlog日誌）

4、Slave端的SQL線程會定期讀取relay日誌，把二進制的日誌解析成SQL語句，並執行這些SQL語句，同步數據到從庫中。

環境準備

軟件及系統: CentOS 7.5 — 2臺、MySQL 5.7 --- Yum安裝

系統環境準備:

##關閉防火牆及SElinux安全機制
[root@mysql ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@mysql ~]# sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config
[root@mysql ~]# reboot
​
##必要的軟件環境準備
[root@mysql ~]# yum -y install wget vim net-tools
[root@mysql ~]# yum -y groupinstall "Development Tools"
[root@mysql ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[root@mysql ~]# rpm -ivh mysql80-community-release-el7-1.noarch.rpm

⚠️需要修改MySQL的安裝倉庫的設置, 開啟5.7的安裝源, 關閉8.0的安裝源

###安裝構建

要求: master-slave架構

構建過程:

##安裝配置過程
[root@mysql ~]# yum -y install mysql-community-server
[root@mysql ~]# systemctl enable mysqld && systemctl start mysqld
##主從配置過程
 ##------------------------------------主庫設置------------------------------------##
[root@master ~]# vi /etc/my.cnf //開啟二進制日誌並設置服務ID
[mysqld]
server-id=21 

log-bin=mysql-bin
​
[root@master ~]# systemctl start mysqld && systemctl enable mysqld
[root@master ~]# grep 'password' /var/log/mysqld.log
2019-06-22T01:53:04.173732Z 1 [Note] A temporary password is generated for root@localhost: ied,1qj(y/gH //注意修改密碼
​
[root@master ~]# mysql -uroot -p'(Cyy..123)'
​
mysql> CREATE USER 'replication'@'192.168.233.22' IDENTIFIED BY "#Cyy..123#";
Query OK, 0 rows affected (0.01 sec)
​
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.233.22';
Query OK, 0 rows affected (0.00 sec)
​
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 877 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
​
 ##------------------------------------主庫設置------------------------------------##
​
 ##------------------------------------從庫設置------------------------------------##
[root@slave ~]# vi /etc/my.cnf
[mysqld]
server_id=22
​
[root@slave ~]# systemctl start mysqld && systemctl enable mysqld
[root@slave ~]# grep 'password' /var/log/mysqld.log
2019-06-22T02:07:33.890742Z 1 [Note] A temporary password is generated for root@localhost: dFS:z5DQ/
[root@slave ~]# mysql -uroot -p'(Cyy..123)'
​
mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.233.21',
 -> MASTER_USER='replication',
 -> MASTER_PASSWORD='#Cyy..123#',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS=877;
​
mysql> start slave;
mysql> show slave status\\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.233.21
 Master_User: replication
 Master_Port: 3306
 Connect_Retry: 60 

 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 1206
 Relay_Log_File: slave-relay-bin.000002
 Relay_Log_Pos: 649
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ##------------------------------------從庫設置------------------------------------##