MySQL主从复制 - 原理
2018-05-30 本文已影响14人
诺之林
目录
环境
主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
