MySQL5.7.31主从复制实践
MySQL复制架构
MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其他服务器充当从服务器
复制的基本原理
在主库的二进制日志里记录了对数据库的变更，从库从主库那里获取日志，然后在从库中重放这部分日志，从而实现数据的同步。基本步骤类似如下。
- 主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。
- 从库复制主库的二进制日志事件到本地的中继日志（relay log）。
- 从库重放中继日志。
将从服务器设置为复制主服务器的数据后，它将连接主服务器并等待更新过程。如果主服务器失败，或者从服务器与主服务器之间失去了连接，那么从服务器将保持定期尝试连接，直到它能够继续侦听更新为止。由–master-connect-retry选项控制重试间隔，默认时间为60s。
复制的用途
复制有很多用途，比如跨IDC备份数据，使用读写分离架构扩展读，在从库上进行备份，使用从库测试数据库版本升级，高可用自动故障冗余切换等。生产中使用最广泛的用途无疑是进行数据备份，在备份过程中主服务器可以继续处理更新，并在主库不能提供服务的情况下接管服务。
复制的注意事项
- 一般情况下，少量的从库，对于主库来说没有什么开销，但是如果部署了很多从库，就需要考虑从库对主库的影响了，网络带宽或I/O可能都会存在瓶颈。
- 如果只是传送最新的二进制日志到从库，那么从库一般不会对主库有冲击，但如果由于某种原因，需要读取高并发主库上旧的日志，那就可能会带来严重的性能问题，因为主库要读取大量的旧日志，而这些日志没有被操作系统缓存，因此将导致主库I/O瓶颈，同时还有一个潜在的影响，会阻碍主库事务提交，因为MySQL的XA事务有其特殊性，在事务日志提交之前，需要确保二进制日志已写入。
- 复制架构中的从库一般用于扩展读，对于扩展写没有什么用处，复制对于频繁读和少量写的系统好处最大。
- 由于目前MySQL5.1、5.5的复制是单线程的，所以复制可能会成为瓶颈，建议使用SSD来突破瓶颈。（软件不够硬件凑）
- 复制的架构和配置应尽量保持简单，建议生产环境保持简单，所有主从都是完全复制过去，同步所有的数据和权限。保持主从的完全一致，可以减少很多不必要的麻烦。
- 建议将从库配置为只读，因为应用程序可能会配置错误，对从库进行写操作，将会导致数据的不一致性，甚至丢失数据。
- 互为主从的环境，一定要保证同一时刻只写一个数据库。
安装MySQL步骤
安装介质准备
对于MySQL的安装，之前感觉很简单，通过yum安装就结束了，由于下的yum是MySQL官方的包，下载很是慢，试了3次都是以失败告终。均是报了超时
最后使用是用PC机将rpm包下载完成，上传到服务器上，或者直接在Linux上下载也是可以的。
最后看下来是要安装包，MySQL官网下载地址
对应的5.7.31版本的下载链接,如下
在Linux上也可以下载wget或者其他命令均可，目的下载rpm包
https://cdn.mysql.com/archives/mysql-5.7/mysql-community-common-5.7.31-1.el7.x86_64.rpm
https://cdn.mysql.com/archives/mysql-5.7/mysql-community-libs-5.7.31-1.el7.x86_64.rpm
https://cdn.mysql.com/archives/mysql-5.7/mysql-community-libs-compat-5.7.31-1.el7.x86_64.rpm
https://cdn.mysql.com/archives/mysql-5.7/mysql-community-server-5.7.31-1.el7.x86_64.rpm
https://cdn.mysql.com/archives/mysql-5.7/mysql-community-client-5.7.31-1.el7.x86_64.rpm
命令行操作-进行安装
- 检查旧包
查看是否有老的MySQL安装包
rpm -qa|grep -i mysql
#删除对应的包
rpm -ev mysql-community-client-5.7.31-1.el7.x86_64 --nodeps
rpm -ev mysql-community-common-5.7.31-1.el7.x86_64 --nodeps
rpm -ev mysql-community-server-5.7.31-1.el7.x86_64 --nodeps
rpm -ev mysql-community-libs-compat-5.7.31-1.el7.x86_64 --nodeps
rpm -ev mysql-community-libs-5.7.31-1.el7.x86_64 --nodeps
#如果使用yum安装过，则可以
yum list installed | grep mysql
#或者删除具体的包名
yum remove mysql*
- 安装软件
自动按照依赖关系安装
[root@localhost ~]# rpm -ivh mysql*
warning: mysql-community-client-5.7.31-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
mariadb-libs is obsoleted by mysql-community-libs-5.7.31-1.el7.x86_64
mariadb-libs is obsoleted by mysql-community-libs-compat-5.7.31-1.el7.x86_64
[root@localhost ~]#
不省心报错了，网上查询了一下是如下原因
红帽安装rpm安装MySQL时爆出警告： 警告：MySQL-server-5.5.46-1.linux2.6.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY 原因：这是由于yum安装了旧版本的GPG keys造成的 解决办法：后面加上 --force --nodeps 如： rpm -ivh MySQL-server-5.5.46-1.linux2.6.x86_64.rpm --force --nodeps 从 RPM 版本 4.1 开始，在安装或升级软件包时会检查软件包的签名。原文出处
- 解决问题，不抛弃不放弃，再次安装
[root@localhost ~]# rpm -ivh mysql* --force --nodeps
warning: mysql-community-client-5.7.31-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-common-5.7.31-1.e################################# [ 20%]
2:mysql-community-libs-5.7.31-1.el7################################# [ 40%]
3:mysql-community-client-5.7.31-1.e################################# [ 60%]
4:mysql-community-server-5.7.31-1.e################################# [ 80%]
5:mysql-community-libs-compat-5.7.3################################# [100%]
查看MySQL的状态
[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@localhost ~]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2020-11-17 15:18:03 CST; 11s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 6766 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 6710 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 6768 (mysqld)
Tasks: 27
CGroup: /system.slice/mysqld.service
└─6768 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Nov 17 15:17:55 localhost.localdomain systemd[1]: Starting MySQL Server...
Nov 17 15:18:03 localhost.localdomain systemd[1]: Started MySQL Server.
- 查找对应的MySQL初始密码
在安装日志中获取其中的密码
cat /var/log/mysqld.log | grep password |grep root@localhost
2020-11-17T07:18:00.412562Z 1 [Note] A temporary password is generated for root@localhost: X+>sRV3s<&uJ
- 登录修改密码
mysql -u root -p
在MySQL状态下修改密码
set password for root@localhost=password('Pwd@123456');
- 修改my.cnf
主库修改
vim /etc/my.cnf
#设置[mysqld]
[mysqld]
log_bin=mysql-bin #和从库名字一致
server-id=128 #和从库ID不一致
从库修改
vim /etc/my.cnf
#设置[mysqld]
[mysqld]
log_bin=mysql-bin #和从库名字一致
server-id=129 #和主库ID不一致
- 重启mysqld服务
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost ~]#
- 查看主库的file和Position记住方便后面使用
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
配置主从同步
在128主库中创建账号，方便从库同步使用,一下IP是从库129
grant replication slave,replication client on *.* to 'slave1'@'192.168.170.129' identified by 'Pwd@123456';
在129机器上操作从库修改,指向主库，IP是主库128
CHANGE MASTER TO MASTER_HOST='192.168.170.128', MASTER_USER='slave1', MASTER_PASSWORD='Pwd@123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
启动slave
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.170.128
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000014
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: 154
Relay_Log_Space: 578
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: 128
Master_UUID: 2e487710-28bd-11eb-8a61-000c2926a04e
Master_Info_File: /var/lib/mysql/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:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
配置主主双向同步
尝试配置，互为主备双向同步
在从库上即129上执行，指向主库，方便从库到主库同步
grant replication slave,replication client on *.* to 'slave1'@'192.168.170.128' identified by 'Pwd@123456';
在原来的主库上进行即128上，所有信息为从库信息
CHANGE MASTER TO MASTER_HOST='192.168.170.129', MASTER_USER='slave1', MASTER_PASSWORD='Pwd@123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=473;
在安装过程中可能会有网络不通，则需要进行网络连通性测试
telnet 192.168.170.128 3306
#如果没有安装telnet则需安装 telnet
yum install telnet
#开通防火墙
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
firewall-cmd --zone=public --query-port=3306/tcp
firewall-cmd --zone=public --list-ports
启动slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.170.129
Master_User: slave1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 316
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 529
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: 316
Relay_Log_Space: 906
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: 129
Master_UUID: 2fac6cfc-28bd-11eb-89c0-000c2911bfaa
Master_Info_File: /var/lib/mysql/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:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
show master status \G;
测试
在主库数据库，在从库也有对应的数据库被创建，在从库新增数据在主库也会被同步
-- 创建数据库
CREATE DATABASE test001;
-- 切换数据库
USE test001;
-- 创建表
CREATE TABLE IF NOT EXISTS `testTable`(
`test_id` INT UNSIGNED AUTO_INCREMENT COMMENT '测试ID',
`test_title` VARCHAR(100) NOT NULL COMMENT '测试标题',
`test_author` VARCHAR(40) NOT NULL COMMENT '测试作者',
`test_date` DATE COMMENT '时间',
PRIMARY KEY ( `test_id` )
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建索引
CREATE INDEX indexName ON testTable (test_id);
两边保持一致，结束！
常用命令
查看binlog的状态，正在读写的binlog和当前Position
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000014 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
查看从库状态
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.170.129#当前的主服务器主机。
Master_User: slave1 #被用于连接主服务器的当前用户。
Master_Port: 3306#当前的主服务器接口。
Connect_Retry: 60#--master-connect-retry选项的当前值,默认重连时间
Master_Log_File: mysql-bin.000004#I/O线程当前正在读取的主服务器二进制日志文件的名称。
Read_Master_Log_Pos: 1756#在当前的主服务器二进制日志中，I/O线程已经读取的位置。
Relay_Log_File: localhost-relay-bin.000040#SQL线程当前正在读取和执行的中继日志文件的名称。
Relay_Log_Pos: 273#在当前的中继日志中，SQL线程已经读取和执行的位置。
Relay_Master_Log_File: mysql-bin.000004#由SQL线程执行的包含多个近期事件的主服务器二进制日志文件的名称。
Slave_IO_Running: Yes#I/O线程是否被启动并成功地连接到主服务器上。
Slave_SQL_Running: Yes#SQL线程是否被启动。 Slave_IO_Running和Slave_SQL_Running在正常情况下应该均为Yes。
Replicate_Do_DB:
省略
…………
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
查看连接状态
mysql> SHOW PROCESSLIST;
+----+-------------+-----------------------+---------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------------+---------+-------------+-------+---------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 71989 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 18832 | Slave has read all relay log; waiting for more updates | NULL |
| 13 | root | 192.168.170.1:5015 | test001 | Sleep | 16845 | | NULL |
| 18 | root | localhost:56418 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 19 | slave1 | 192.168.170.129:35948 | NULL | Binlog Dump | 12279 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+-------------+-----------------------+---------+-------------+-------+---------------------------------------------------------------+------------------+
5 rows in set (0.00 sec)
mysql>
主库和从库的信息
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 129 | | 3306 | 128 | 2fac6cfc-28bd-11eb-89c0-000c2911bfaa |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
可以考虑为从库配置read-only选项，以保障数据安全，要注意SUPER权限的用户仍然可以写数据库。
mysql> SHOW VARIABLES LIKE 'read-only';
Empty set (0.00 sec)
mysql>
MySQL复制模式
对应地，我们可以设置3种类型的二进制日志格式，使用参数–binlog-format=type进行设置。type的值可以是如下的值。
| 复制模式 | 枚举值 |
|---|---|
| 基于SQL语句的复制（statement-based replication） | STATEMENT：基于语句的日志。 |
| 基于行的复制（row-based replication） | ROW：基于行记录的日志。 |
| 混合模式复制（mixed-based replication） | MIXED：混合日志模式，即默认是基于语句的日志，当需要的时候，将会使用基于行的日志。 |
MySQL在日志模式的选择上不同的版本默认值可能会不一样，建议在生产环境中使用MIXED，即混合模式的日志，一般情况下，它可以工作得很好。我们可以在运行时动态修改日志格式，命令如下。
mysql>SET GLOBAL binlog_format = 'ROW';
mysql>SET SESSION binlog_format = 'ROW';
| 属性 | Value |
|---|---|
| 命令行格式 | –binlog-format=format |
| 系统变量 | binlog_format |
| 全局 | 会话 |
| 动态 | 是 |
| 类型 | 列举 |
| 默认值 | （> = 5.7.7）ROW |
| 默认值 | （<= 5.7.6） STATEMENT |
| 有效值 | ROW、STATEMENT、MIXED |
表格出处binlog_format参数
注意：
可以在运行时更改日志记录格式，但建议您不要在复制过程中对其进行更改。这部分是因为slave库不同步；给定的MySQL服务器只能更改自己的日志记录格式。
基于SQL语句的复制
基于SQL语句的复制（statement-based replication），也就是逻辑复制，MySQL 3.23开始支持。基于语句的复制，复制将执行主库上所执行的语句，也就是说，在从库上执行的语句和在主库上执行的语句是一样的。
基于SQL语句的复制，其优点具体如下
- 相对于基于行记录的日志，它更简单，也更容易实现。
- 数据库的二进制日志更小，因此，主从库之间传输的日志数据也更小。
- 二进制日志的可读性更好，我们可以使用mysqlbinlog方便地读取二进制日志。
- 更有利于排查问题，从库上执行的是和主库一样的语句。
基于SQL语句的复制，其缺点具体如下
- 有些操作将无法正确复制到从库，因为对于主库的操作，并不仅仅取决于SQL文本，还有一些不确定性的因素。不确定性的因素有如下之点。
- 带LIMIT子句但没有使用ORDER BY的操作。
- 修改数据的查询语句里用到了返回不确定性值的自定义函数和存储过程。
- 一些函数在主从上执行的结果不一样，如UUID()、SYSDATE()、RAND()、VERSION()……
还有很多，这里就不一一列举了。
- 从库需要锁住更多的记录，比如INSERT…SELECT…操作会需要锁定比基于行记录的复制多得多的记录，比如UPDATE一个表，如果没有索引，就会锁住整个表。
- 复杂的、代价昂贵的语句需要在从库上再次执行，也就是运行整
基于行的复制
MySQL 5.1开始支持基于行的复制（row-based replication），它的适用范围更广泛，也可靠得多。基于行记录（row-based）的复制，其格式比较难以阅读，即使MySQL官方一直在改进其可读性。基于语句的复制很难处理各种高级特性，如视图、存储过程、触发器。如果你需要应用各种高级特性，那么推荐你使用基于行的复制模式。
基于行的复制优点具体如下
- 所有改变均被复制，对比基于语句的复制，这是一种更安全、更精确的复制。
- 更少的锁定记录。
- 对于存储器、触发器、自定义函数的特性也完善支持。
- 二进制日志更有利于进行数据恢复，因为二进制日志里记录了数据的详细变更信息。
- 更容易发现数据的不一致。比如主库中更改了1笔记录，而从库中不存在这笔记录，那么基于行记录的复制会报错而基于语句的复制则不会报错。
基于行的复制缺点具体如下
- 产生更多的二进制日志数据。
- 二进制日志不易阅读，不方便使用mysqlbinlog解读日志。
- 要求主从表结构一致，这样就限制了它的灵活性，因为生产环境有时需要临时修改从库的表结构，提升从库为主库。
混合日志模式
建议是使用混合模式，即binlog_format=mixed，默认是使用基于语句的复制，但一旦MySQL检测到满足了一定的条件，那么它就会自动切换到基于行的复制。例如，在函数内使用了UUID()，更新了有自增列的表且调用了触发器或存储过程等情况，将会自动切换到基于行的复制。
两个数据库同步还有很多参数可以优化配置，希望大家多多留意。使数据库的性能达到最好。
196
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
