MySQL主从复制 - 原理

目录

• 环境

  • 主MySQL

  • 从MySQL

  • 测试数据

• 原理

  • 主MySQL记录SQL到binary-log

  • 从MySQL连接到主MySQL并读取binary-log至relay-log

  • 从MySQL执行realy-log中的SQL

环境

主MySQL

docker run --name mysql-master -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.17

关于Docker更多参考Docker入门

docker exec -it mysql-master /bin/bash

echo 'server-id = 1' >> /etc/mysql/mysql.conf.d/mysqld.cnf

echo 'log_bin = /var/log/mysql/mysql-bin.log' >> /etc/mysql/mysql.conf.d/mysqld.cnf

docker restart mysql-master

docker exec -it mysql-master /bin/bash

mysql -u root -p

mysql> GRANT replication slave, replication client on *.* to replic_user identified by 'replic_pwd';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> SHOW master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      464 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

关于主从复制环境搭建 更多参考MySQL主从复制 - 入门

从MySQL

docker run --name mysql-slave -p 3307:3307 -e MYSQL_ROOT_PASSWORD=123456 --link mysql-master:mysql-master -d mysql:5.7.17

docker exec -it mysql-slave /bin/bash

echo 'server-id = 2' >> /etc/mysql/mysql.conf.d/mysqld.cnf

echo 'relay_log = /var/log/mysql/mysql-replay.log' >> /etc/mysql/mysql.conf.d/mysqld.cnf

echo 'read_only = 1' >> /etc/mysql/mysql.conf.d/mysqld.cnf

docker restart mysql-slave

docker exec -it mysql-slave /bin/bash

mysql -u root -p

mysql> CHANGE MASTER TO
    -> MASTER_HOST='mysql-master',
    -> MASTER_PORT=3306,
    -> MASTER_USER='replic_user',
    -> MASTER_PASSWORD='replic_pwd',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=464;
Query OK, 0 rows affected, 2 warnings (0.35 sec)

mysql> START slave;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW slave status\G;

测试数据

• 主MySQL

mysql> CREATE database test;
Query OK, 1 row affected (0.02 sec)

• 从MySQL

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

原理

主MySQL记录SQL到binary-log

docker exec -it mysql-master /bin/bash

cat /var/log/mysql/mysql-bin.000001

_bin�[w{5.7.17-log�[8


**4�Ũ�[#����Lc<["A�ʈ�c<[��2 �Ustd
                              root  localhost
                                                 mysqlGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replic_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6450DF6E7FA228BBA0562AFA678A8AD23D3010D3't��o=["A2/�5o=[^! �Ustd
                                                                                            testtestCREATE database test�q�_

开启binary log后MySQL会创建子线程用于写入binary log

从MySQL连接到主MySQL并读取binary-log至relay-log

mysql> START slave;
Query OK, 0 rows affected (0.02 sec)

从MySQl建立连接后会创建子线程读取主MySQL的binary log 并保存至其relay log

docker exec -it mysql-slave /bin/bash

ls -l /var/log/mysql/mysql-replay.*

从MySQL执行realy-log中的SQL

从MySQL会创建子线程执行relay log中的SQL

参考

• 主从复制面试之作用和原理

• MySQL Replication 主從式架構設定教學
  `