目錄
環(huán)境
主MySQL
docker run --name mysql-master -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.17
關(guān)于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)
關(guān)于主從復(fù)制環(huán)境搭建 更多參考MySQL主從復(fù)制 - 入門
從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;
測試數(shù)據(jù)
- 主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會創(chuàng)建子線程用于寫入binary log
從MySQL連接到主MySQL并讀取binary-log至relay-log
mysql> START slave;
Query OK, 0 rows affected (0.02 sec)
從MySQl建立連接后會創(chuàng)建子線程讀取主MySQL的binary log 并保存至其relay log
docker exec -it mysql-slave /bin/bash
ls -l /var/log/mysql/mysql-replay.*
從MySQL執(zhí)行realy-log中的SQL
從MySQL會創(chuàng)建子線程執(zhí)行relay log中的SQL
