CentOS7 MySQL5.7.35主从不停机搭建以及配置

如需安装MySQL，参照MySQL 5.7.35 安装教程

	https://blog.csdn.net/CsethCRM/article/details/119418841

一、主&从环境信息准备

1.1.查看硬盘信息，确保磁盘够用（主&从）

df -h

1.2.查看内存信息（主&从，推荐从库配置略高）

free -h

1.3. 查看主库数据库大小（主库）

SELECT table_schema AS '数据库名称',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '数据库大小(MB)'
FROM information_schema.tables
GROUP BY table_schema;

1.4.查看主库数据表占用的空间大小（主库，查看是否在预期内）

SELECT * FROM 
		(
			SELECT 
			table_schema
			,table_name
			,ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS TableGB
			FROM information_schema.tables 
			WHERE 0=0
			and table_schema ='数据库名称' 
			group by table_schema,table_name
		) T1 
		ORDER BY T1.TableGB desc

1.5. 查看主库和从库的UUID，一定要不相同才可以（主&从）

SHOW VARIABLES LIKE 'server_uuid%';

1.6.确认防火墙已关闭

查看防火墙状态
systemctl status firewalld.service
#关闭运行的防火墙
systemctl stop firewalld.service
#禁止防火墙服务器，保证重新启动服务器之后防火墙还是关闭状态
systemctl disable firewalld.service

1.7.设置 SSH 工具，SSH Keepalive

MobaXterm 为例：SSH keepalive 要 打钩
其他工具 找到各自的 超时时间，默认很多工具 都是 30分钟，如果不设置，超过 30分钟 会自动断开
导致 主库 备份 失败

二、主库配置，配置在 [mysqld] 下方（主）

2.1./etc/my.cnf 配置（主）

# 1.  配置server-id ，主库和从库不可以相同，配置不同的数字即可（主）
server-id       = 1
# 2. 为了保证数据一致性，每次执行写入行操作，都进行磁盘同步; （主）
sync_binlog = 1
# 3. 配置需同步的库（主）
binlog-do-db = 数据库名称1
binlog-do-db = 数据库名称2
# 4. 配置不需要同步的库（主）
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=自定义1
binlog-ignore-db=自定义2
# 5. 配置event_scheduler（主）
event_scheduler = on
# 6. 设置 binlog_format（主）
binlog_format     = row

2.2.Mysql库如果有触发器

建议用 if server-id = 1 来判断 主库才执行，从库不执行
避免 自增列、now()、uuid() 执行结果不一致，从而导致 主从数据不一致

三、从库配置，配置在 [mysqld] 下方（从）

3.1./etc/my.cnf 配置（从）

# 1. 从库 server-id ，和主库不同即可（从）
server-id = 2
# 2.注释掉 log-bin=mysql-bin  从库不需要配置此项，从主库传递到从库，从库没必要开启binlog ( Slave 作为其它 Slave 的 Master 时需要开启，我们只是从库 所以不开启)
# log-bin=mysql-bin
# 3. 支持通配符,忽略 zdy开头的表 进行主从同步（从）
replicate-wild-ignore-table=数据库.zdy_%   # zdy_ 开头的忽略
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = information_schema.%
replicate-wild-ignore-table = sys.%
replicate-wild-ignore-table = performance_schema.%
# 4.从库关闭事件（从）
event_scheduler = off
# 5.从库设置只读（从）
read_only=1
# 6.为保证数据一致性，每次同步都进行磁盘同步（从）
sync_binlog = 1
# 7. 通过复制接收到并执行的更新都会被写入该从服务器的二进制日志中。这使得连接到此从服务器的其他从服务器也能接收到这些自主服务器同步过来的更新。（从）
log_slave_updates = 1
# 8.从库中继日志。Relaylog 的主要目的是提高数据复制的效率和可靠性（从）
relay_log = mysql-relay-bin

四、建立主从

4.1. 建立主从需要的账号 (主-SSH)

# CentOS SSH 连接后登录MySQL
mysql -u root -p
输入 mysql root 的密码：你的密码
# 创建用户，指定该用户只能在 主库IP 上 使用 密码登录
mysql> create user '用户名'@'主库IP' identified by '密码';
# 为用户 赋予 REPLICATION SLAVE 权限
mysql> grant replication slave on *.* to '用户名'@'主库IP';
mysql> GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
# 查看用户
mysql> select user,host from mysql.user;
# 查看master状态
mysql> show master status;

4.2. 主库上进行备份等操作（主-SSH）

# 在主库机器上 SSH 登录后 执行mysqldump命令，此命令会包含 MASTER_LOG_FILE 和 MASTER_LOG_POS 等位置信息
# 后续从库按照 备份的位置开始建立，备份后 变更的数据 会自动同步到从库
mysqldump -u root -p --single-transaction  --master-data=2 --databases 数据库1 数据库2 > /home/dbsql.sql
输入密码：你的密码
# mysqldump 备份完毕后，把备份文件 从 master（主） 传输 至 slave（从） 机器
 scp /home/dbsql.sql root@从库IP:/home
 输入：yes
 输入从库SSH的密码：你的密码

4.3.在从库上还原数据库（从-SSH）

# 导入备份的mysql数据
mysql -u用户名 -p密码 < /home/dbsql.sql 
# 导入成功后，重启mysql
systemctl restart mysqld

4.4.在从库的MySQL中建立主从连接（从）

# 查看 dbsql.sql 文件中的 postion ，查看文件的前 30行（文件小直接打开查看，文件超过GB之后用命令查看）
# SSH命令
cd /home
head -n 30 dbsql.sql

# 找到如下结果，下边建立主从连接会用到：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000276', MASTER_LOG_POS=11613709;

# 建立主从连接，我是在navicate 中操作的，SSH命令中登录mysql也可以，navicate命令如下：
# 我们在建立主从过程中，主机是不需要停止的，这条命令执行完毕，会从此位置（也就是mysqldump的位置） 开始
# 把之后 主 的所有改动 同步至 从库，使主从数据一致，就实现了 不停机 主从

change master to master_host='主的IP', 
master_user='4.1建立的账号'
,MASTER_PORT =3306
,master_password='4.1账号的密码'
,master_log_file='mysql-bin.000276'
,master_log_pos=11613709;

# 停止主从同步
stop slave; 
# 开启主从同步
start slave;  
# 查看 从库 状态 ，看到 2个 Yes  恭喜你，大功告成
show slave status; 

# 看到结果中的下边2列，表示主从建立成功：
Slave_IO_Running	Slave_SQL_Running
Yes	Yes

4.5 不停机主从至此就搭建完毕了，恭喜一下自己吧 ^_

五、主从搭建过程中排查问题的方法

5.1 查看日志，根据日志指引能解决99%的问题：

/var/log/mysqld.log

5.2 日志中并未报错，在从库中停一下再启动也许就好了（从库执行）

# 停止主从同步（从）
stop slave; 
# 开启主从同步（从）
start slave;  
# 查看 从库 状态 ，看到 2个 Yes  恭喜你，大功告成（从）
show slave status;

5.3 解决不了的错误/ 不需要的主从内容可以跳过

https://blog.csdn.net/weixin_72244810/article/details/132569820
slave_skip_errors = 1146

六、其它备忘

清空主从配置（从）

reset slave all;

show slave status\G 超详细全面解释

	https://blog.csdn.net/weixin_48154829/article/details/134974113

主从原理

在这里插入图片描述

mysql5.7 主从复制压力测试

	https://blog.csdn.net/shixiaoling123/article/details/109004274

主从复制的日志格式，这里的日志格式就是指二进制日志的三种格式

binlog_format=statement
binlog_format=row
binlog_format=mixed
其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

硬件推荐

		a) CPU好
		b) SSD硬盘 或者 SAN
		c）同一交换机，万兆环境

尽量不要使用存储过程和触发器、事件

尽量不要使用 temporary tables

尽量不要使用MyISAM类型的表，推荐使用InnoDB，MyISAM不支持事务

mysql主从同步加速方法

1、sync_binlog在slave端设置为0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3、直接禁用slave端的binlog
4、slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

九、附录

1.配置完毕检查语句

# uuid 主从检查，不相同即可
SHOW VARIABLES LIKE 'server_uuid%';
# server-id 主从检查，不相同即可
show variables like '%server_id%';
# event scheduler ，一般主库on 从库 off
show variables like '%event_scheduler%';
# read only ，一般主库 on ，从库off
show variables like '%read_only%';
# sync_binlog
show variables like '%sync_binlog%';
# log_slave_updates 
show variables like '%log_slave_updates%';
# relay_log
show variables like '%relay_log%';

2.主从搭建完毕，检查表个数是否相同

SELECT COUNT(*) TABLES, table_schema
FROM information_schema.TABLES
WHERE table_schema = '数据库名称'
GROUP BY table_schema;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/697601.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

CentOS7 MySQL5.7.35主从 不停机搭建 以及配置