MySQL（二）MySQL-5.7.20二进制主从复制实战

MySQL-5.7.20二进制主从复制实战

主从复制原理

1、从库IO线程，读取master.info内容，获取主库的IP/Port/User/Pass/binlog文件名和位置号（3250）

2、从库通过IO线程与主库连接

3、通过master.info记录的binlog文件名与位置号，“问”主库有没有比这个（3250）更加新的

4、主库看一下（show master status–>3438）告诉从库有更加新的binlog日志

5、从库的IO线程接收主库Dump线程传送来的binlog内容，存储到TCP/IP缓存，立即返回ACK确认给主库

6、从库IO线程将TCP/IP缓存内容写入到relay-log，更新master.info中binlog日志的位置号

7、SQL线程读取relay.info信息，往后执行最新的relay-log日志，执行完成后，再次更新relay-log信息

复制能做什么？

1、辅助复制（解决物理损坏）

2、高可用（MHA）

3、分担负载（读写分离、分布式）程读取relay.info信息，往后执行最新的relay-log日志，执行完成后，再次更新relay-log信息

主从复制先决条件：

[mark_d]基于二进制日志实时同步数据到其他数据库[/mark_d]

1、主库要开启binlog日志功能

2、主库开启专用复制用户（replication slave）

3、主从的server_id号要不同

4、从库要保证在开启主从之前，保证数据和主库在一个时间点上（备份恢复）

二进制多实例主从复制

前文已经有二进制多实例安装的文档，本文档不再从新安装，如果没有安装请点击：二进制多实例安装

本文将使用MySQL-3307实例为主节点，MySQL-3308实例为从节点。

对3307与3308实例开启binlog日志

[root@mysql-manager ~]# mkdir -p /opstands/mysql-330{7,8}/binlog

[root@mysql-manager ~]# cat >> /opstands/mysql-3307/my.cnf <<EOF
log_bin=/opstands/mysql-3307/binlog/mysql-3307-bin
binlog_format=row
sync_binlog=1
EOF


[root@mysql-manager ~]# cat >> /opstands/mysql-3308/my.cnf <<EOF
log_bin=/opstands/mysql-3308/binlog/mysql-3307-bin
binlog_format=row
sync_binlog=1
EOF

授权

[root@mysql-manager ~]# chown -R mysql.mysql /opstands/mysql-*

重启3307与3308实例

[root@mysql-manager ~]# systemctl restart mysqld-3307.service 
[root@mysql-manager ~]# systemctl restart mysqld-3308.service

备份3307实例全库所有数据

[root@mysql-manager ~]# mysqldump -S /opstands/mysql-3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction > /opt/full.sql

# 查看postion起点，这个不重要，主要是知道了postion点后方便使用binlog日志恢复数据
[root@mysql-manager ~]# sed -rn '22s#(.*)(MASTER.*);#\2#gp' /opt/full.sql
MASTER_LOG_POS=154

# 进入3308实例数据库内后临时关闭binlog，然后恢复3307实例的数据
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock 
mysql> set sql_log_bin=0; source /opt/full.sql;

创建主从复制用户

# 配置复制用户，给予所有权限，用户：rep，密码为：redhat
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "grant replication slave on *.* to rep@'%' identified by 'redhat';"

查看3307数据库的binlog文件名与postion点

# 查看到的binlog文件名与postion点后在从库连接主库时要指定的
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "show master status;"
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-3307-bin.000001 |      436 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+

开启主从复制

# 进入3308实例数据库内开启连接到3307实例
# 根据主库的状态来进行配置，SQL语句执行，并启动主从复制功能
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='172.18.1.79',
    ->   MASTER_USER='rep',
    ->   MASTER_PASSWORD='redhat',
    ->   MASTER_PORT=3307,
    ->   MASTER_LOG_FILE='mysql-3307-bin.000001',
    ->   MASTER_LOG_POS=436,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看主从复制状态

[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "show slave status\G;" |egrep 'Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:'
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

测试主从同步效果

[mark_e]可以看到3307与3308两个实例都是没有olda库，接下来在3307库创建olda库，然后查看3308库是否有同步。[/mark_e]

[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "show databases;" 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

可以看到3308实例从库也同步创建了olda库

[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "create database olda charset utf8mb4;"
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| olda               |
| performance_schema |
| sys                |
+--------------------+
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "show databases;" 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| olda               |
| performance_schema |
| sys                |
+---
-------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| olda               |
| performance_schema |
| sys                |
+---