MySQL主从复制(MySQL Replication)是指从一个MySQL主服务器(master)将数据拷贝到另一台或多台MySQL从服务器(slaves)的过程。将主数据库的DDL和DML操作通过二进制日志(binlog)传到从服务器(slave)上,然后在从服务器上对这些日志重新执行,从而使得主从服务器的数据保持同步。 MySQL从3.23版本开始提供复制的功能。
MySQL的Replication是一个多MySQL数据库做主从同步的方案,广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。
主从复制有以下几方面的好处:
-- 搭建3主1从
-- 配置网络环境
docker pull mysql:5.7.30
docker network create --subnet=172.72.0.0/24 mysql-network
-- 创建参数目录
mkdir -p /lhrmysqltest2/master1/conf.d
mkdir -p /lhrmysqltest2/master2/conf.d
mkdir -p /lhrmysqltest2/master3/conf.d
mkdir -p /lhrmysqltest2/slave/conf.d
-- 删除之前的
docker rm -f mysql5730M33265 mysql5730M33266 mysql5730M33267 mysql5730S33268
-- 申请主库1
docker run -d --name mysql5730M33265 \
-h master1 -p 33265:3306 --net=mysql-network --ip 172.72.0.10 \
-v /lhrmysqltest2/master1/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=lhr \
mysql:5.7.30
-- 申请主库2
docker run -d --name mysql5730M33266 \
-h master2 -p 33266:3306 --net=mysql-network --ip 172.72.0.11 \
-v /lhrmysqltest2/master2/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=lhr \
mysql:5.7.30
-- 申请主库3
docker run -d --name mysql5730M33267 \
-h master3 -p 33267:3306 --net=mysql-network --ip 172.72.0.12 \
-v /lhrmysqltest2/master3/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=lhr \
mysql:5.7.30
-- 申请从库
docker run -d --name mysql5730S33268 \
-h slave1 -p 33268:3306 --net=mysql-network --ip 172.72.0.13 \
-v /lhrmysqltest2/slave/conf.d:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=lhr \
mysql:5.7.30
-- 配置主库1的参数
cat > /lhrmysqltest2/master1/conf.d/my.cnf << "EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033265
log-bin =
binlog_format=row
expire_logs_days = 30
max_binlog_size = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
gtid-mode=ON
enforce-gtid-consistency=on
skip_name_resolve
report_host=172.72.0.10
EOF
-- 配置主库2的参数
cat > /lhrmysqltest2/master2/conf.d/my.cnf << "EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033266
log-bin =
binlog_format=row
expire_logs_days = 30
max_binlog_size = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.11
EOF
-- 配置主库3的参数
cat > /lhrmysqltest2/master3/conf.d/my.cnf << "EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033267
log-bin =
binlog_format=row
expire_logs_days = 30
max_binlog_size = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.12
EOF
-- 配置从库的参数
cat > /lhrmysqltest2/slave/conf.d/my.cnf << "EOF"
[mysqld]
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 573033268
log-bin =
binlog_format=row
expire_logs_days = 30
max_binlog_size = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
report_host=172.72.0.13
master-info-repository = table
relay-log-info-repository = table
EOF
-- 重启主机
docker restart mysql5730M33265
docker restart mysql5730M33266
docker restart mysql5730M33267
docker restart mysql5730S33268
docker ps
docker exec -it mysql5730M33265 bash
docker exec -it mysql5730M33265 mysql -uroot -plhr
mysql -uroot -plhr -h192.168.66.35 -P33265 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33266 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33267 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.66.35 -P33268 -e "select @@hostname,@@server_id,@@server_uuid"
-- 在3台主库分别创建复制用户
mysql -uroot -plhr -h192.168.66.35 -P33265
mysql -uroot -plhr -h192.168.66.35 -P33266
mysql -uroot -plhr -h192.168.66.35 -P33267
grant replication slave on *.* to repl@'%' identified by 'lhr';
select user,host,grant_priv,password_last_changed,authentication_string from mysql.user;
show master status \G;
show slave hosts;
select @@hostname,@@server_id,@@server_uuid;
-- 从库做以下配置
mysql -uroot -plhr -h192.168.66.35 -P33268
-- 配置主库1到从库的复制路径
change master to
master_host='172.72.0.10',
master_port=3306,
master_user='repl',
master_password='lhr',
master_auto_position=1 FOR CHANNEL 'Master1';
-- 配置主库2到从库的复制路径
change master to
master_host='172.72.0.11',
master_port=3306,master_user='repl',
master_password='lhr',
master_auto_position=1 FOR CHANNEL 'Master2';
-- 配置主库3到从库的复制路径
change master to
master_host='172.72.0.12',
master_port=3306,master_user='repl',
master_password='lhr',
master_auto_position=1 FOR CHANNEL 'Master3';
-- 启动所有SLAVE
mysql> START SLAVE;
-- 也可以单独启动需要同步的通道
mysql> START SLAVE FOR CHANNEL 'master1';
mysql> START SLAVE FOR CHANNEL 'master2';
mysql> START SLAVE FOR CHANNEL 'master3';
-- 在从库查询
show slave status \G;
-- 如果要查看单一信道的复制的详细状态,可以使用以下命令:
mysql> SHOW SLAVE STATUS FOR CHANNEL 'master1'\G;
-- 通过表查询
select a.master_log_pos,a.host,a.user_name,a.user_password,a.port,a.uuid,a.channel_name from mysql.slave_master_info a;
select * from mysql.slave_relay_log_info;
select * from mysql.slave_worker_info;
select * from mysql.gtid_executed;
-- 在 performance_schema 库中,提供了复制相关的一些视图,可供查看复制相关的信息。
select * from performance_schema.replication_applier_configuration;
select * from performance_schema.replication_applier_status;
select * from performance_schema.replication_applier_status_by_coordinator;
select * from performance_schema.replication_applier_status_by_worker;
select * from performance_schema.replication_connection_configuration;
select * from performance_schema.replication_connection_status;
select * from performance_schema.replication_group_member_stats;
select * from performance_schema.replication_group_members;
-- 合并SQL
select rcc.CHANNEL_NAME,rcc.`HOST`,rcc.`PORT`,rcc.`USER`,rcc.CONNECTION_RETRY_COUNT,rcc.CONNECTION_RETRY_INTERVAL,
rcs.SOURCE_UUID,rcs.THREAD_ID,rcs.SERVICE_STATE,rcs.COUNT_RECEIVED_HEARTBEATS,rcs.LAST_HEARTBEAT_TIMESTAMP,rcs.LAST_ERROR_NUMBER,rcs.LAST_ERROR_MESSAGE,rcs.LAST_ERROR_TIMESTAMP
from performance_schema.replication_connection_configuration rcc,
performance_schema.replication_connection_status rcs
where rcc.CHANNEL_NAME=rcs.CHANNEL_NAME;
-- 线程查询
SELECT *
FROM performance_schema.threads a
WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
SELECT * FROM information_schema.`PROCESSLIST` a where a.USER='system user' or a.command in ('Binlog Dump','Binlog Dump GTID') ;
MySQL [(none)]> SELECT *
-> FROM performance_schema.threads a
-> WHERE a.`NAME` IN ( 'thread/sql/slave_IO', 'thread/sql/slave_sql' ) or a.PROCESSLIST_COMMAND in ('Binlog Dump','Binlog Dump GTID') ;
+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |
+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
| 29 | thread/sql/slave_io | FOREGROUND | 4 | root | 172.72.0.1 | NULL | Connect | 252 | Waiting for master to send event | NULL | 28 | NULL | YES | YES | NULL | 75 |
| 30 | thread/sql/slave_sql | FOREGROUND | 5 | root | 172.72.0.1 | NULL | Connect | 325 | Slave has read all relay log; waiting for more updates | NULL | 28 | NULL | YES | YES | NULL | 76 |
| 31 | thread/sql/slave_io | FOREGROUND | 6 | root | 172.72.0.1 | NULL | Connect | 252 | Waiting for master to send event | NULL | 28 | NULL | YES | YES | NULL | 77 |
| 32 | thread/sql/slave_sql | FOREGROUND | 7 | root | 172.72.0.1 | NULL | Connect | 322 | Slave has read all relay log; waiting for more updates | NULL | 28 | NULL | YES | YES | NULL | 78 |
| 33 | thread/sql/slave_io | FOREGROUND | 8 | root | 172.72.0.1 | NULL | Connect | 252 | Waiting for master to send event | NULL | 28 | NULL | YES | YES | NULL | 79 |
| 34 | thread/sql/slave_sql | FOREGROUND | 9 | root | 172.72.0.1 | NULL | Connect | 320 | Slave has read all relay log; waiting for more updates | NULL | 28 | NULL | YES | YES | NULL | 80 |
+-----------+----------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+--------------------------------------------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+
6 rows in set (0.09 sec)
-- 测试多源
-- mysql -uroot -plhr -h192.168.66.35 -P33265
create database master1;
use master1;
CREATE TABLE `test1` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test1 values(1,1);
-- mysql -uroot -plhr -h192.168.66.35 -P33266
create database master2;
use master2;
CREATE TABLE `test2` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test2 values(2,2);
-- mysql -uroot -plhr -h192.168.66.35 -P33267
create database master3;
use master3;
CREATE TABLE `test3` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test3 values(3,3);
-- 从库查询
-- mysql -uroot -plhr -h192.168.66.35 -P33268
show databases;
SELECT * FROM master1.test1;
SELECT * FROM master2.test2;
SELECT * FROM master3.test3;
从库查询:
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master1 |
| master2 |
| master3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.05 sec)
MySQL [(none)]> SELECT * FROM master1.test1;
+------+-------+
| id | count |
+------+-------+
| 1 | 1 |
+------+-------+
1 row in set (0.06 sec)
MySQL [(none)]> SELECT * FROM master2.test2;
+------+-------+
| id | count |
+------+-------+
| 2 | 2 |
+------+-------+
1 row in set (0.05 sec)
MySQL [(none)]> SELECT * FROM master3.test3;
+------+-------+
| id | count |
+------+-------+
| 3 | 3 |
+------+-------+
1 row in set (0.05 sec)
MySQL [(none)]>
1、初次配置耗时较长,需要将各个master的数据dump下来,再source到slave上。 2、需要考虑各master数据增长频率,slave的数据增长频率是这些数据的总和。如果太高,会导致大量的磁盘IO,造成数据更新延迟,最严重的是会影响正常的查询。 3、如果多个主数据库实例中存在同名的库,则同名库的表都会放到一个库中; 4、如果同名库中的表名相同且结构相同,则数据会合并到一起;如果结构不同,则先建的有效。
本文结束。