温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

一文秒懂MySQL5.7多主一从同步配置

发布时间:2020-07-02 19:30:05 来源:网络 阅读:506 作者:沙漏半杯 栏目:编程语言

多主一从,也称为多源复制,数据流向:

  • 主库1 -> 从库s

  • 主库2 -> 从库s

  • 主库n -> 从库s

应用场景

  • 数据汇总,可将多个主数据库同步汇总到一个从数据库中,方便数据统计分析。

  • 读写分离,从库只用于查询,提高数据库整体性能。

部署环境

    注:使用docker部署mysql实例,方便快速搭建演示环境。但本文重点是讲解主从配置,因此简略描述docker环境构建mysql容器实例。

  • 数据库:MySQL 5.7.x  (相比5.5,5.6而言,5.7同步性能更好,支持多源复制,可实现多主一从,主从库版本应保证一致)

  • 操作系统:CentOS 7.x

  • 容器:Docker 17.09.0-ce

  • 镜像:mysql:5.7

  • 主库300:IP=192.168.10.212; PORT=4300; server-id=300; database=test3; table=user

  • 主库400:IP=192.168.10.212; PORT=4400; server-id=400; database=test4; table=user

  • 主库500:IP=192.168.10.212; PORT=4500; server-id=500; database=test5; table=user

  • 从库10345:IP=192.168.10.212; PORT=4345; server-id=10345; database=test3,test4,test5; table=user

配置约束

  • 主从库必须保证网络畅通可访问

  • 主库必须开启binlog日志

  • 主从库的server-id必须不同

【主库300】操作及配置

配置my.cnf

[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-character-set = utf8mb4[mysqld]########################### summary###########################bind-address = 0.0.0.0#port = 3306#datadir=/datavol/mysql/data #数据存储目录########################### log bin##########################server-id = 300	#必须唯一log_bin = mysql-bin 	#开启及设置二进制日志文件名称binlog_format = MIXEDsync_binlog = 1expire_logs_days =7	#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。#binlog_cache_size = 128m#max_binlog_cache_size = 512m#max_binlog_size = 256Mbinlog-do-db = test3 	#要同步的数据库binlog-ignore-db = mysql 	#不需要同步的数据库  binlog_ignore_db = information_schemabinlog_ignore_db = performation_schemabinlog_ignore_db = sys ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

安装启动

[root@localhost ~]# docker run -d -p 4300:3306 --name=mysql-300 -v /datavol/mysql-300/conf:/etc/mysql/conf.d -v /datavol/mysql-300/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.75691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad [root@localhost ~]# docker psCONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES 5691bac538e6        mysql:5.7                           "docker-entrypoint..."   44 seconds ago      Up 43 seconds       33060/tcp, 0.0.0.0:4300->3306/tcp                    mysql-300 [root@localhost ~]# docker exec -it mysql-300 /bin/bashroot@5691bac538e6:/# mysql -u root -pEnter password:

注:若不熟悉docker,可使用传统方式安装mysql,效果相同。

创建授权用户

连接mysql主数据库,键入命令mysql -u root -p,输入密码后登录数据库。创建用户用于从库同步复制,授予复制、同步访问的权限

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)

log_bin是否开启

mysql> show variables like 'log_bin'; +---------------+-------+| Variable_name | Value | +---------------+-------+| log_bin       | ON    | +---------------+-------+1 row in set

查看master状态

mysql> show master status \G; *************************** 1. row ***************************              File: mysql-bin.000003          Position: 438      Binlog_Do_DB: test3  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【主库400】配置及操作

配置my.cnf

[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-character-set = utf8mb4[mysqld]########################### summary###########################bind-address = 0.0.0.0#port = 3306#datadir=/datavol/mysql/data #数据存储目录########################### log bin##########################server-id = 400	#必须唯一log_bin = mysql-bin 	#开启及设置二进制日志文件名称binlog_format = MIXEDsync_binlog = 1expire_logs_days =7	#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。#binlog_cache_size = 128m#max_binlog_cache_size = 512m#max_binlog_size = 256Mbinlog-do-db = test4 	#要同步的数据库binlog-ignore-db = mysql 	#不需要同步的数据库  binlog_ignore_db = information_schemabinlog_ignore_db = performation_schemabinlog_ignore_db = sys ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

安装启动

[root@localhost ~]# docker run -d -p 4400:3306 --name=mysql-400 -v /datavol/mysql-400/conf:/etc/mysql/conf.d -v /datavol/mysql-400/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.719e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7 [root@localhost ~]# docker psCONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES 19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4400->3306/tcp                    mysql-400 [root@localhost ~]# docker exec -it mysql-400 /bin/bashroot@19e93b6d93ca:/# mysql -u root -pEnter password:

创建授权用户

创建用户用于从库同步复制,授予复制、同步访问的权限

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)

log_bin是否开启

mysql> show variables like 'log_bin'; +---------------+-------+| Variable_name | Value | +---------------+-------+| log_bin       | ON    | +---------------+-------+1 row in set

查看master状态

mysql> show master status \G; *************************** 1. row ***************************              File: mysql-bin.000003          Position: 438      Binlog_Do_DB: test4  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【主库500】配置及操作

配置my.cnf

[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-character-set = utf8mb4[mysqld]########################### summary###########################bind-address = 0.0.0.0#port = 3306#datadir=/datavol/mysql/data #数据存储目录########################### log bin##########################server-id = 500	#必须唯一log_bin = mysql-bin 	#开启及设置二进制日志文件名称binlog_format = MIXEDsync_binlog = 1expire_logs_days =7	#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。#binlog_cache_size = 128m#max_binlog_cache_size = 512m#max_binlog_size = 256Mbinlog-do-db = test5 	#要同步的数据库binlog-ignore-db = mysql 	#不需要同步的数据库  binlog_ignore_db = information_schemabinlog_ignore_db = performation_schemabinlog_ignore_db = sys ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

安装启动

[root@localhost ~]# docker run -d -p 4500:3306 --name=mysql-500 -v /datavol/mysql-500/conf:/etc/mysql/conf.d -v /datavol/mysql-500/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.719e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7 [root@localhost ~]# docker psCONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES 19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4500->3306/tcp                    mysql-500 [root@localhost ~]# docker exec -it mysql-500 /bin/bashroot@19e93b6d93ca:/# mysql -u root -pEnter password:

创建授权用户

创建用户用于从库同步复制,授予复制、同步访问的权限

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec)

log_bin是否开启

mysql> show variables like 'log_bin'; +---------------+-------+| Variable_name | Value | +---------------+-------+| log_bin       | ON    | +---------------+-------+1 row in set

查看master状态

mysql> show master status \G; *************************** 1. row ***************************              File: mysql-bin.000003          Position: 438      Binlog_Do_DB: test5  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【从库10345】配置及操作

配置my.cnf

[client]port = 3306default-character-set = utf8mb4[mysql]port = 3306default-character-set = utf8mb4[mysqld]########################### summary###########################bind-address = 0.0.0.0#port = 3306#datadir=/datavol/mysql/data 	#数据存储目录########################### log bin##########################server-id = 10345master_info_repository      = tablerelay_log_info_repository   = table ########################### character set##########################character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

安装启动

[root@localhost ~]# docker run -d -p 4345:3306 --name=mysql-10345 -v /datavol/mysql-10345/conf:/etc/mysql/conf.d -v /datavol/mysql-10345/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.719e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7 [root@localhost ~]# docker psCONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES 19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4345->3306/tcp                    mysql-10345 [root@localhost ~]# docker exec -it mysql-10345 /bin/bashroot@19e93b6d93ca:/# mysql -u root -pEnter password:

设置【主库】信息

登录【从库10345】,进入mysql命令行。

mysql> stop slave; Query OK, 0 rows affected mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212', MASTER_PORT=4300, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=438 for channel '300'; Query OK, 0 rows affected mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212', MASTER_PORT=4400, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=438 for channel '400'; Query OK, 0 rows affected mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212', MASTER_PORT=4500, MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=438 for channel '500'; Query OK, 0 rows affected mysql> start slave; Query OK, 0 rows affected

stop slave;     //停止同步
start slave;     //开始同步
//必须和【主库】的信息匹配。
CHANGE MASTER TO
MASTER_HOST='192.168.10.212',     //主库IP
MASTER_PORT=4300,                       //主库端口
MASTER_USER='slave',                     //访问主库且有同步复制权限的用户
MASTER_PASSWORD='123456',      //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息,主库show master status返回结果
MASTER_LOG_FILE='mysql-bin.000003',
//【关键处】从文件中指定位置开始读取,主库show master status返回结果
MASTER_LOG_POS=438
for channel '300';            //定义通道名称

查看同步状态

mysql> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.10.212                   Master_User: slave                   Master_Port: 4300                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 1860                Relay_Log_File: 0ad84f013600-relay-bin-300.000002                 Relay_Log_Pos: 1742         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 1860               Relay_Log_Space: 1960               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 300                   Master_UUID: 4efae154-ebd4-11e8-bf7d-0242ac110007              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 0          Replicate_Rewrite_DB:                   Channel_Name: 300            Master_TLS_Version:  *************************** 2. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.10.212                   Master_User: slave                   Master_Port: 4400                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 2461                Relay_Log_File: 0ad84f013600-relay-bin-400.000002                 Relay_Log_Pos: 1187         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 2461               Relay_Log_Space: 1405               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 400                   Master_UUID: 8fb0aa81-ebd4-11e8-ac5b-0242ac110009              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 0          Replicate_Rewrite_DB:                   Channel_Name: 400            Master_TLS_Version:  *************************** 3. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.10.212                   Master_User: slave                   Master_Port: 4500                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 2151                Relay_Log_File: 0ad84f013600-relay-bin-500.000002                 Relay_Log_Pos: 626         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB:             Replicate_Do_Table:         Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0                    Last_Error:                   Skip_Counter: 0           Exec_Master_Log_Pos: 2151               Relay_Log_Space: 844               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 500                   Master_UUID: b87704d2-ebd5-11e8-a90c-0242ac11000a              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:        Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:                 Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set:              Executed_Gtid_Set:                  Auto_Position: 0          Replicate_Rewrite_DB:                   Channel_Name: 500            Master_TLS_Version:  3 rows in set (0.00 sec)

可以看见设置三个的主从同步通道的所有状态信息。
只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes,则同步是正常的。
如果是No或者Connecting都不行,可查看mysql-error.log,以排查问题。

mysql> show variables like 'log_error%'; +---------------------+--------+| Variable_name       | Value  | +---------------------+--------+| log_error           | stderr | | log_error_verbosity | 3      | +---------------------+--------+2 rows in set

配置完成,则【从库10345】开始自动同步。

若需要单独启动或停止某个同步通道,可使用如下命令:
start slave for channel '300';     //启动名称为300的同步通道
stop slave for channel '300';     //停止名称为300的同步通道

验证数据同步

建库

使用root账号登录【主库300】,创建test3数据库 

mysql> CREATE DATABASE test3; Query OK, 1 row affected (0.00 sec) mysql> USE test3; Database changed

建表

在【主库300】中创建user表

CREATE TABLE `user` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,   `age` tinyint(3) unsigned NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

新增

在【主库300】中向user表插入一条数据:

mysql> use test3;Database changedmysql> INSERT INTO user (id, name, age) VALUES (300, 'Tom', 18);Database changedmysql> SELECT * FROM user;+-----+------+-----+ | id  | name | age | +-----+------+-----+ | 300 | Tom  |  18 | +-----+------+-----+ 1 row in set (0.00 sec)

在【从库10345】中查询user表数据:

mysql> use test3; Database changed mysql> SELECT * FROM user; +-----+------+-----+| id  | name | age |+-----+------+-----+| 300 | Tom  |  18 |+-----+------+-----+1 row in set (0.00 sec)

新增记录同步成功。

更新

在【主库300】中修改刚才插入的数据:

mysql> UPDATE user SET name='Peter' where id=300;Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from user;+-----+-------+-----+ | id  | name  | age | +-----+-------+-----+ | 300 | Peter |  18 | +-----+-------+-----+ 1 row in set (0.00 sec)

在【从库10345】中查询user表数据:

mysql> select * from user; +-----+-------+-----+| id  | name  | age |+-----+-------+-----+| 300 | Peter |  18 |+-----+-------+-----+1 row in set (0.00 sec)

更新记录同步成功。

删除

在【主库300】中删除刚才更新的数据:

mysql> DELETE FROM user WHERE id=300;Query OK, 1 row affected (0.00 sec)mysql> select * from user;Empty set (0.00 sec)

在【从库10345】中查询user表数据:

mysql> select * from user;Empty set (0.00 sec)

删除记录同步成功。
注:【主库400】、【主库500】的验证操作与上述类似。

补充:

  • 如果【主服务器】重启mysql服务,【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后,从服务器会自动重新连接上主服务器,并正常同步数据。

  • 如果某段时间内,【从数据库】服务器异常导致同步中断(可能是同步点位置不匹配),可以尝试以下恢复方法:进入【主数据库】服务器(正常),在bin-log中找到【从数据库】出错前的position,然后在【从数据库】上执行change master,将master_log_file和master_log_pos重新指定后,开始同步。 

欢迎工作一到五年的Java工程师朋友们加入Java技术交流群:659270626
群内提供免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI