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安全机制
[[email protected] ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[[email protected] ~]# sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config
[[email protected] ~]# reboot
##必要的软件环境准备
[[email protected] ~]# yum -y install wget vim net-tools
[[email protected] ~]# yum -y groupinstall "Development Tools"
[[email protected] ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[[email protected] ~]# rpm -ivh mysql80-community-release-el7-1.noarch.rpm
⚠️需要修改MySQL的安装仓库的设定, 开启5.7的安装源, 关闭8.0的安装源
###安装构建
要求: master-slave架构
构建过程:
##安装配置过程
[[email protected] ~]# yum -y install mysql-community-server
[[email protected] ~]# systemctl enable mysqld && systemctl start mysqld
##主从配置过程
##------------------------------------主库设定------------------------------------##
[[email protected] ~]# vi /etc/my.cnf //开启二进位制日志并设定服务ID
[mysqld]
server-id=21
log-bin=mysql-bin
[[email protected] ~]# systemctl start mysqld && systemctl enable mysqld
[[email protected] ~]# grep 'password' /var/log/mysqld.log
2019-06-22T01:53:04.173732Z 1 [Note] A temporary password is generated for [email protected]: ied,1qj(y/gH //注意修改密码
[[email protected] ~]# 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)
##------------------------------------主库设定------------------------------------##
##------------------------------------从库设定------------------------------------##
[[email protected] ~]# vi /etc/my.cnf
[mysqld]
server_id=22
[[email protected] ~]# systemctl start mysqld && systemctl enable mysqld
[[email protected] ~]# grep 'password' /var/log/mysqld.log
2019-06-22T02:07:33.890742Z 1 [Note] A temporary password is generated for [email protected]: dFS:z5DQ/[[email protected] ~]# 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 statusG;
*************************** 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
##------------------------------------从库设定------------------------------------##
