MySQL 主从复制

① 要求

  • 两台机器,主Master 服务器(10.1.1.20)和从 Slave服务器(10.1.1.30)
  • 确保两台机器能够正常通信,分别在两台机器的hosts文件中添加两者的IP+主机名
  • 两台机器的MySQL版本需要一致, mysql-5.6.35
  • Master开启二进制日志

注意:主Master 服务器正常安装MySQL,从Slave服务器不需要进行初始化

②Master 环境准备

# !/bin/bash
#1.解压安装包
tar -zvxf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
#2.移动到安装目录 /usr/local/mysql
mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
#3.创建mysql账号
useradd -r -s /sbin/nologin mysql
#4.分配安装目录权限
chown -R mysql.mysql /usr/local/mysql
#5.进入安装目录
cd /usr/local/mysql 
#6.删除 mariadb-libs 数据库 
yum remove mariadb-libs -y 
#7.初始化数据库vim
scripts/mysql_install_db --user=mysql
#8.将服务添加到 /etc/init.d/ 目录下
cp support-files/mysql.server /etc/init.d/mysql
#9.启动 mysql 服务
service mysql start
#10.数据库安全设置,
#设置root密码,移除匿名用户,允许root远程连接,删除test数据库
bin/mysql_secure_installation

③Slave 环境准备

从服务器不需要进行初始化,直接清空 mysql/data/目录,并拉取Master 服务器的 data 目录

#1.解压安装包
tar -zvxf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
#2.移动到安装目录 /usr/local/mysql
mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
#3.创建mysql账号
useradd -r -s /sbin/nologin mysql
#4.分配安装目录权限
chown -R mysql.mysql /usr/local/mysql
#5.进入安装目录
cd /usr/local/mysql
#6.删除 mariadb-libs 数据库 ,并清空 mysql/data目录内容
yum remove mariadb-libs -y 
rm -rf data/*
#--------------------------
#7.将 Master 服务器 mysql/data 目录中的数据拉到 data 目录下,  ***
rsync -av  root@10.1.1.20:/usr/local/mysql/data/ /usr/local/mysql/data/ 
#--------------------------
#8. 删除 data/auto.cnf ,该文件中保存MySQL的UUID
rm -rf data/auto.cnf
#9.将服务添加到 /etc/init.d/ 目录下
cp support-files/mysql.server /etc/init.d/mysql
#10.启动 mysql 服务
service mysql start

④两台机器添加环境变量

添加mysql/bin 环境变量

# 1.编辑配置文件
vim /etc/profile
# 2.添加如下内容
export PATH=$PATH:/usr/local/mysql/bin
# 3.使环境变量生效
source  /etc/profile

⑤修改配置文件

两台服务器server_id不能重复

Master 开启 log-binSlave 开启 relay-log

修改配置文件:# vim /usr/local/mysql/my.cnf

Master

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=20
socket=/tmp/mysql.sock
character_set_server=utf8mb4
log-bin=/usr/local/mysql/data/binlog
log-error=/usr/local/mysql/data/mysql.err

Slave

需要手动创建my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=30
socket=/tmp/mysql.sock
character_set_server=utf8mb4
relay-log=/usr/local/mysql/data/relaylog
log-error=/usr/local/mysql/data/mysql.err

⑥配置主从并开启同步

Master配置

-- 1.创建账号,用于从服务配置
grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';
-- 2.刷新权限表
flush privileges;
-- 3.锁表 
flush tables with read lock;
-- 4.显示当前二进制文件名称与当前位置,用于从服务器配置
show master status; 
-- binlog.000001  405

Slave配置

-- 1.配置 
-- 配置帮助: help change master to;
change master to master_host='10.1.1.20',master_user='slave',master_password='123',master_port=3306,master_log_file='binlog.000001',master_log_pos=405;
-- 2.开启同步
start slave;
-- 3.查看是否开启成功
-- \G表示竖列显示
show slave status\G
-- 注意:开启成功后关闭 Master 锁表  ***

Master 关闭锁表

 unlock tables

⑦问题及解决

1.确保同步服务已开启

start slave;

2.查看错误信息

show slave status\G
-- Last_IO_Errno:
-- Last_IO_Error:
-- Last_SQL_Errno:
-- Last_SQL_Error:

3.配置文件出错

-- 1.重置slave
reset slave;
-- 2.重新配置
change master to .......

4.检查防火墙配置或SeLinux

5.SQL线程报错

可能原因:两台服务器数据不一致

解决1:清空从服务器 data目录,重新从主服务器拉data数据

解决2:跳过错误,临时解决问题

-- 跳过特定数量的异常
set GLOBAL sql_slave_skip_counter=1;
stop slave;
start slave;

6.uuid相同 (data/auto.cnf

7.server_id相同(my.cnf

扩展:GTIDs

GTIDs(Global transaction identifiers)全局事务标识符,mysql 5.6新加入的技术

特点:

1.添加新的slave时,不需要再指定二进制日志文件名称与位置。简化主从配置

2.GTIDs是完全基于事务的,因此不支持MYISAM存储引擎

①修改主从配置文件

注意:主从服务器都需要开启二进制日志

Mater

# vim /usr/local/mysql/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 20
socket = /tmp/mysql.sock
character_set_server=utf8mb4
log-bin = /usr/local/mysql/data/binlog
log-error = /usr/local/mysql/data/mysql.err
# 添加3行配置
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency

Slave

# vim /usr/local/mysql/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 30
socket = /tmp/mysql.sock
character_set_server=utf8mb4
# 从服务器也需要开启二进制日志
log-bin = /usr/local/mysql/data/binlog
relay-log=/usr/local/mysql/data/relaylog
log-error = /usr/local/mysql/data/mysql.err
# 添加4行配置
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency=1
skip-slave-start

修改后需要重启服务:# service mysql restart

② Master创建slave账号

grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';
flush privileges;

③ Slave 配置

不需要再指定二进制日志文件名称与位置,但是需要设置:master_auto_position=1

change master to master_host='10.1.1.20',master_user='slave',master_password='123',master_port=3306,master_auto_position=1;

④ Slave 开启同步

start slave;
show slave status\G

⑤问题及解决

数据不一致的解决方案(SLAVE写入数据)

第一种解决方案:
在slave服务器中,删除data目录下的所有文件
重新同步
重新配置主从

第二种解决方案:人为跳过异常(但是需要根据GTIDs的编号跳过)
解决思路:先找到异常的SQL语句的GTIDs编号,在SLAVE服务器中设置为当前位置。然后人为跳过,即可解决。
问题:SQL的GTIDs编号在哪里寻找,在relaylog中继中可以找到
SLAVE:
# mysqlbinlog relay.000003 | less
mysql> SET @@SESSION.GTID_NEXT= '错误的SQL语句的GTIDs编号';

mysql> stop slave;
mysql> SET @@SESSION.GTID_NEXT= '1b5c4112-f242-11e9-8e95-000c2948a342:5'/*!*/;
mysql> BEGIN;
mysql> commit;
mysql> SET @@SESSION.GTID_NEXT= 'AUTOMATIC';

mysql> start slave;
mysql> show slave status\G
Last modification:March 25th, 2019 at 10:55 pm