|
|
一、什么是主从同步
将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态，称之为主从复制。简单概括为，主数据库做什么，从数据库就跟着做什么。
二、主从同步原理
2.1 原理图
主从同步原理图
上述相关名词说明如下:
1.bin log:二进制文件
2.log dump线程:二进制日志转储线程
3.relay log:中继日志
4.I/O线程:用于请求主库的binlog，并且把接收到的binlog信息写入relay log日志文件中
5.SQL线程:用于读取relay log日志文件内容，并且在从库中做回放
2.2 原理概述
1.从库创建两个进程，一个I/O线程，一个SQL线程，其中I/O线程用于请求主库的binlog，并且把接收到的binlog信息写入relay log日志文件中；SQL线程用于读取relay log日志文件内容，并且在从库中做回放。
2.主库创建一个log dump线程，用于发送binlog到从库；
三、为什么做主从同步
1.主从同步可以提高数据库的吞吐量；
2.主从同步可以读写分离，通过主从复制方式同步数据，通过读写分离提高数据库并发处理能力；
3.主从同步可以进行数据备份；
4.主从同步具有高可用性。
四、Mysql主从部署
4.1 环境介绍
| 操作系统 | Mysql版本 | 主机IP | | Centos7-X86_64-Eeverything2009 | 5.6.34 | 192.168.100.135(主) | | Centos7-X86_64-Eeverything2009 | 5.6.34 | 192.168.100.136(备) |
4.2 操作步骤
4.2.1 主库
4.2.1.1 防火墙开放端口
1.执行firewall-cmd --zone=public --add-port=3306/tcp --permanent命令开放3306端口
[root@test ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success参数说明如下:
- –zone:作用域
- –add-port=3306/tcp:添加端口，格式为：端口/通讯协议
- –permanent:永久生效，没有此参数重启后失效
2.执行systemctl restart firewalld.service命令重启防火墙服务
[root@test ~]# systemctl restart firewalld.service3.执行firewall-cmd --list-ports命令查看端口是否开放成功
[root@test ~]# firewall-cmd --list-ports
3306/tcp4.2.1.2 修改主机名
[root@test ~]# hostname master
[root@test ~]# hostname
master4.2.1.3 修改配置文件
1.执行vim /etc/my.cnf命令修改配置文件，添加以下内容
server-id = 1
log-bin=mysql-bin
- server-id = 1:配置服务器的服务号
- log-bin=mysql-bin:指定数据库操作的日志文件
2.其他可选参数
- max_binlog_size= 500M:设置日志文件得最大值，超过该值则另启文件记录日志
- binlog_cache_size= 128K:设置日志文件缓存大小
- replicate-do-db= pos:指定要同步的数据库，如果有多个，请重复该行内容并修改数据库名称为对应数据库
- replicate-ignore-db= mysql:指定不需要同步的数据库
4.2.1.4 重启mysql服务
1.执行service mysqld restart命令重启mysql服务
[root@test ~]# service mysqld restart4.2.1.4 创建用户并授权
# 创建用户
mysql> grant replication slave on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 用户授权
mysql> grant all on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 刷新权限
mysql> Flush privileges;
Query OK, 0 rows affected (0.01 sec)4.2.1.5 查看二进制文件名以及大小
1.执行show master status;语句查看二进制文件名及大小，方便在从库进行绑定
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 592 | | | |
+------------------+----------+--------------+------------------+-------------------+4.2.2 备库
4.2.2.1 防火墙开放端口
1.执行firewall-cmd --zone=public --add-port=3306/tcp --permanent命令开放3306端口
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success参数说明如下:
- –zone:作用域
- –add-port=3306/tcp:添加端口，格式为：端口/通讯协议
- –permanent:永久生效，没有此参数重启后失效
2.执行systemctl restart firewalld.service命令重启防火墙服务
[root@localhost ~]# systemctl restart firewalld.service3.执行firewall-cmd --list-ports命令查看端口是否开放成功
[root@localhost ~]# firewall-cmd --list-ports
3306/tcp4.2.2.2 修改主机名
[root@localhost ~]# hostname slave
[root@localhost ~]# hostname
slave4.2.2.3 修改配置文件
1.执行vim /etc/my.cnf命令修改配置文件，添加以下内容
server-id = 2
log-bin=mysql-bin
- server-id = 2:配置服务器的服务号
- log-bin=mysql-bin:指定数据库操作的日志文件
2.其他可选参数
- replicate-do-db= pos:指定要同步的数据库，如果有多个，请重复该行内容并修改数据库名称为对应数据库。 replicate-ignore-db= mysql:指定不需要同步的数据库
- max_binlog_size= 500M:设置日志文件得最大值，超过该值则另启文件记录日志
- binlog_cache_size= 128K:设置日志文件缓存大小
4.2.2.4 重启mysql服务
1.执行service mysqld restart命令重启mysql服务
[root@localhost ~]# service mysqld restart4.2.2.5 配置主数据库信息
mysql> change master to
master_host='192.168.100.135',master_user='test',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=592;
Query OK, 0 rows affected, 2 warnings (0.02 sec)4.2.2.6 开始同步
mysql> start slave;4.2.2.7 查看同步
1.执行show slave status\G;语句查看同步情况，这里观察到Slave_IO_Running和Slave_SQL_Running显示YES。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.135
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 592
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
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: 592
Relay_Log_Space: 456
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: 1
Master_UUID: f0fd84c8-53ee-11ed-9b81-000c2904ef91
Master_Info_File: /opt/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)4.3 测试
1.在主库执行以下SQL语句创建class库并添加表结构
# 创建数据库class
mysql> create database class;
Query OK, 1 row affected (0.00 sec)
# 进入数据库class
mysql> use class;
Database changed
#添加表结构
mysql> create table student ( id int primary key auto_increment, name varchar(10) );
Query OK, 0 rows affected (0.02 sec)2.在从库执行以下SQL语句查看从库是否存在主库创建的库信息。这里观察到，已同步。
#显示库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| class |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
#进入库
mysql> use class
Database changed五、主从同步缺陷
1.部署时存在一定的复杂度
2.带来一定的主从同步延迟
六、主从同步延迟优化
6.1 强制查询主库
不查询从库，只查询主库。这种方法适用于适用于对实时性业务要求非常高的业务，但是要考虑查询的量级会不会很大，在不在主库的可承受范围内。
6.2 并行复制
将SQL单线程拆分为多个work线程来进行重放
6.3 降低并发
控制主库写入速度，主从同步延迟发生概率变小 |
|
发表于 2023-1-12 15:11:39