MySQL主从复制实战案例

MySQL主从复制实战

master：192.168.10.136 server_id=120

slave：192.168.10.135 server_id=130

实验之前得保证两台机器能够通过ssh远程连接

主库配置

1、主库配置[mysqld]

[root@serverb ~]# vim /etc/my.cnf.d/mysql-server.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
# 打开gtid
gtid-mode=on
enforce-gtid-consistency=true
# 设置server-id值并开启binlog参数
log_bin=mysql-bin
server_id=120

2、重启数据库

systemctl restart mysqld

3、建立同步账号

#创建用户
create user 'rep'@'192.168.10.%' identified with mysql_native_password by 'SYCredhat123'
#授权
grant replication slave on *.* to 'rep'@'192.168.10.%';

# 查看授权
mysql> show grants for 'rep'@'192.168.95.%';
# 锁表的方式设置只读，为后面备份准备，注意生产环境要提前申请停机时间
# 提示：如果超过设置时间不操作会自动解锁。
mysql> flush tables with read lock;
# 测试锁表后是否可以创建数据库：
mysql> show variables like '%timeout%'; 

# 查看数据库的用户
mysql> select host,user from mysql.user;

4、查看主库状态

mysql> show master status;

5、备份数据库数据

# 注意/server/backup/目录需要自己创建
[root@servera ~]# mysqldump -uroot -p -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz

6、解锁

mysql> unlock tables;

7、主库备份数据并上传到从库

# 前提是从库上面有/server/backup目录
[root@servera ~]# scp /server/backup/mysql_bak.2022-09-11.sql.gz  serverb:/server/backup/

从库配置

1、从库配置[mysqld]

[root@serverb ~]# vim /etc/my.cnf.d/mysql-server.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
# 打开gtid
gtid-mode=on
enforce-gtid-consistency=true
# 设置server-id值并开启binlog参数
log_bin=mysql-bin
server_id=130

2、重启数据库

systemctl restart mysqld

3、还原从主库备份的数据库

cd /server/backup/
gzip -d mysql_bak.2022-09-11.sql.gz
mysql -uroot -p < mysql_bak.2022-09-11.sql

# 检查还原
mysql -uroot -p -e 'show databases;'

配置数据库同步

1、在从库上面配置同步主库

mysql> change master to 
MASTER_HOST='192.168.95.120',
MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='SYCRedhat123',
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=329;

# MASTER_LOG_FILE 和 MASTER_LOG_POS 需要使用 show master status; 查看参数值

2、启动从库

mysql> start slave;

3、检查状态

mysql> show slave status\G

# 注意查看Last_Error:有没有错误日志，如果有就要解决，无错误日志就配置ok

主从状况检测主要参数

# 主从状况监测主要参数
Slave_IO_Running: IO 线 程 是 否 打 开 YES/No/NULL Slave_SQL_Running: SQL线程是否打开 YES/No/NULL Seconds_Behind_Master: NULL #和主库比同步的延迟的秒数
# 可能导致主从延时的因素:
	# 主从时钟是否一致
	# 网络通信是否存在延迟
	# 是否和日志类型，数据过大有关从库性能，有没开启binlog 从库查询是否优化

常见状态错误排除

情况一：
发现IO进程错误，检查日志，排除故障：
# tail localhost.localdomain.err
...
2015-11-18 10:55:50 3566 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
找到原因：从5.6开始复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样
解决方法：(从库是克隆机器) 修改从库的uuid
# vim auto.cnf server-uuid=******

情况二：
常见状态错误排除
show slave status;报错：Error xxx doesn’t exist
解决方法：
stop slave;
set global sql_slave_skip_counter = 1; 
start slave;

测试主从同步

• 主库建立一个数据库

# mysql -uroot -p -e 'create database test_m_s;'

• 从库检查

# mysql -uroot -p -e 'show databases;' | grep "test_m_s"

生产环境其他常用设置

• 配置忽略权限库同步参数

binlog-ignore-db='information_schema mysql test'

• 从库备份开启binlog

log-slave-updates 
log_bin = mysql-bin 
expire_logs_days = 7
应用场景：级联复制或从库做数据备份。

• 从库只读

read-only来实现
innodb_read_only = ON或1，或者innodb_read_only
结论：当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时，从库的read-only生效！