具体介绍MySQL如何开启主从复制实现数据同步
发布时间：2022-03-17 00:41:37 所属栏目：MySql教程 来源：互联网
导读：主从复制线程 主节点 dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events 从节点 I/O Thread：向Master请求二进制日志事件，并保存于中继日志中 SQL Thread：从中继日志中读取日志事件，在本地完成重放 跟复制功能相关的
|
主从复制线程 主节点 dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events 从节点 I/O Thread：向Master请求二进制日志事件，并保存于中继日志中 SQL Thread：从中继日志中读取日志事件，在本地完成重放 跟复制功能相关的文件 master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等 relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系 主从复制特点 异步复制 主从数据不一致比较常见 复制架构 Master/Slave,Master/Master,环状复制 一主多从 从服务器还可以再有从服务器 一从多主：适用于多个不同数据库 复制需要考虑二进制日志时间记录格式 STATEMENT（5.0之前） ROW（5.1之后，推荐） MIXED 搭建环境准备 主机 IP地址 类型 CentOS7.6 192.168.36.101 Master CentOS7.6 192.168.36.103 Slave CentOS7.6 192.168.36.104 Slave 搭建之前请先确保MySQL软件包已经安装 一主一从 Master节点修改数据库配置文件 [root@Master ~]#cat /etc/my.cnf [mysqld] server_id=1 # 为Master节点设置一个全局唯一的ID号 binlog_format=row # 基于行复制的数据库语句 log-bin=/data/bin/mysql-bin # 启用二进制日志 重新启动数据库服务 [root@Master ~]#service mysqld restart Restarting mysqld (via systemctl): [ OK ] Master节点上创建带有复制权限的用户账号 MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec) 查看Master的日志位置信息 MariaDB [mysql]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 912372 | +------------------+-----------+ 1 row in set (0.00 sec) Slave节点修改配置文件 [root@Slave-1 ~]#cat /etc/my.cnf [mysqld] server_id=2 # Slave节点设置全局唯一的ID号 read_only # 只读 重新启动数据库服务 [root@Slave-1 ~]#systemctl restart mariadb 使用Master创建的复制权限的用户账号进行同步 MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.36.101', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.01 sec) 启动Slave线程 MariaDB [(none)]> slave start; Query OK, 0 rows affected (0.00 sec) 查看线程是否启动 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.36.101 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 7389 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 7673 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes # 从节点的IO线程 Slave_SQL_Running: Yes # 从节点的SQL线程 .... Seconds_Behind_Master: 0 # Master与SLave服务器差别延迟 ..... Master_Server_Id: 1 1 row in set (0.00 sec) 检查数据同步情况 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) 主节点中查看数据库的线程 MariaDB [hellodb]> show processlist; +----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------ | Id | User | Host | db | Command | Time | State +----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------ | 3 | root | localhost | hellodb | Query | 0 | NULL | 6 | repluser | 192.168.36.103:51516 | NULL | Binlog Dump | 115 | Master has sent all binlog to slave; waiting for binlog to be upd +----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------ 2 rows in set (0.00 sec) 从节点中查看数据库的线程 MariaDB [(none)]> show processlist; +----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+-- | Id | User | Host | db | Command | Time | State | I +----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+-- | 4 | root | localhost | NULL | Query | 0 | NULL | s | 5 | system user | | NULL | Connect | 159 | Waiting for master to send event | N | 6 | system user | | NULL | Connect | 29259 | Slave has read all relay log; waiting for the slave I/O thread to update it | N +----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+-- 3 rows in set (0.00 sec) 一主多从：新添加一个从节点 Slave-2 节点安装数据库服务 [root@Slave-2 ~]#yum install -y mariadb-server 修改 Slave-2 节点配置文件 [root@Slave-2 ~]#cat /etc/my.cnf [mysqld] server_id=3 read_only .... 重新启动 Slave-2 节点数据库服务 [root@Slave-2 ~]#systemctl restart mariadb Slave-2 节点配置同步信息 MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.36.101', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.00 sec) 启用线程 MariaDB [(none)]> slave start; Query OK, 0 rows affected (0.00 sec) 检查同步情况 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) 主主复制：两台服务器互为主从 主机 IP地址 类型 CentOS7.6 192.168.36.101 Master-1 CentOS7.6 192.168.36.103 Master-2 Master-1 修改配置文件 [root@Master-1 data]#cat /etc/my.cnf [mysqld] server_id=1 log_bin # 启用日志 # 为避免ID冲突问题，需要添加下面两行配置：Master-1 为奇数增长，Master-2 为偶数增长 auto_increment_offset=1 # 开始点 auto_increment_increment=2 # 增长幅度 .... Master-2 修改配置文件 [root@Master-2 data]#cat /etc/my.cnf [mysqld] server_id=2 log_bin auto_increment_offset=2 auto_increment_increment=2 重新启动服务 [root@Master-1 ~]#systemctl restart mariadb [root@Master-2 ~]#systemctl restart mariadb Master-1 创建权限用户，Master-2 先设置为从服务器进行同步 Master-1 的数据库 MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec) Master-2 同步Master-1 MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.36.101', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.00 sec) 启用线程 MariaDB [(none)]> slave start; Query OK, 0 rows affected (0.00 sec) 检查同步情况 MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> select user,password,host from user; +----------+-------------------------------------------+--------------+ | user | password | host | +----------+-------------------------------------------+--------------+ | root | | localhost | | root | | 127.0.0.1 | | root | | ::1 | | repluser | *128977E278358FF80A246B5046F51043A2B1FCED | 192.168.36.% | +----------+-------------------------------------------+--------------+ 7 rows in set (0.00 sec) Master-1 向 Master-2 进行同步 MariaDB [mysql]> CHANGE MASTER TO -> MASTER_HOST='192.168.36.103', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.05 sec) Master-1 同步状态 MariaDB [(none)]> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.36.103 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes .... Master_SSL_Allowed: No .... Master_Server_Id: 2 1 row in set (0.01 sec) 此时Master-1、Master-2 实现主主复制功能 半同步复制 半同步复制的作用是：主服务器宕机后，所有备份服务器主动向同步数据最多的服务器进行数据的同步，以确保数据损失降到最低。 半同步是通过插件功能达成的 Master节点安装semisync_master.so插件 MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.07 sec) 启用插件功能 MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_enabled=1; Query OK, 0 rows affected (0.00 sec) 设置超时时长 MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_timeout=6000; Query OK, 0 rows affected (0.00 sec) 查看Master插件状态 MariaDB [db1]> show variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 6000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) Master修改配置文件并重启数据库服务 [root@Master data]#cat /etc/my.cnf [mysqld] ... rpl_semi_sync_master_enabled # 启用插件功能 ... [root@Master data]#systemctl restart mariadb Slave节点安装semisync_slave.so插件 MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) 启用slave插件功能 MariaDB [db1]> SET GLOBAL rpl_semi_sync_slave_enabled=1; Query OK, 0 rows affected (0.00 sec) Slave修改配置文件并重启数据库服务 [root@Slave data]#cat /etc/my.cnf [mysqld] ... rpl_semi_sync_slave_enabled # 启用插件功能 ... [root@Slave data]#systemctl restart mariadb 停止Slave 数据库服务进行测试 MariaDB [(none)]> create database db3; Query OK, 1 row affected (6.00 sec) # 由等待时长可以看出，半同步插件已经起到效果。 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
