1、MySQL主从复制简介
MySQL主从复制（也称A/B复制、Replication）,简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致分为三个步骤：
1）主将更改操作记录到binlog里；
2）从将主的binlog事件（SQL语句）同步到从本机上，并记录在relaylog里；
3）从根据relaylog里面的SQL语句按顺序执行；
主上有一个log dump线程，用来和从的I/O线程传递binlog
从上有两个线程，其中I/O线程，用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的SQL语句落地。
主从配置需要注意的点
(1)主从服务器操作系统版本和位数一致；
(2) Master和Slave数据库的版本要一致；
(3) Master和Slave数据库中的数据要一致；
(4) Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；
2、准备工作
2.1 系统及软件准备
操作系统: CentOS7.8
MySQL版本：5.7.30
Master服务器：192.168.10.243
Slave服务器：192.168.10.143
2.2、MySQL服务器操作系统配置
# 关闭主、从服务器防火墙 systemctl stop firewalld && systemctl disable firewalld # 关闭主备服务器SELinux # 关闭SELinux # 查看SELinux状态 sestatus –v getenforce # 临时关闭SELinux setenforce 0 # 永久关闭SELinux（需要重启操作系统） sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config # 优化文件句柄 # 打开文件的限制 vim /etc/security/limits.conf * soft nofile 65536 * hard nofile 131072 * - nproc unlimited * soft memlock unlimited * hard memlock unlimited # 优化内核参数 修改/etc/sysctl.conf文件 # 增加tcp支持的队列数 # 减少断开连接时 ，资源回收 net.ipv4.tcp_max_tw_buckets = 8000 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_fin_timeout = 10 net.ipv4.ip_local_port_range = 1024 65000 net.ipv4.tcp_max_syn_backlog = 1024000
2.3、安装 MySQL-5.7.30
Master、Slave节点安装MySQL
# MySQL软件下载 wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-client-5.7.30-1.el7.x86_64.rpm wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-common-5.7.30-1.el7.x86_64.rpm wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-devel-5.7.30-1.el7.x86_64.rpm wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-5.7.30-1.el7.x86_64.rpm wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-server-5.7.30-1.el7.x86_64.rpm # 安装MySQL： yum -y localinstall *.rpm
或者配置yum源，直接使用yum命令安装MySQL
vim /etc/yum.repo.d/mysql.repo [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ enabled=1 gpgcheck=0 # 保存退出，执行 yum clean all yum makecache # 安装 mysql-community-server yum -y install mysql-community-server
2.4、启动MySQL服务，并将服务加入开机启动
systemctl enable mysqld systemctl start mysqld systemctl status mysqld
2.5、登录mysql并初始化root密码
# 登录mysql
说明：mysql在安装完成，启动mysqld服务后，MySQL会自动生成一个随机的root用户密码，可以到日志里面查看。
vim /var/log/mysqld.log
# 可以看到类似如下信息：
2020-03-03T06:24:40.573789Z 1 [Note] A temporary password is generated for root@localhost: p5+yU>E*q8hh
# 说明：用该临时密码登录mysql后，必须要先修改密码，如下：
[root@aliy-prod-pubser-server001 opt]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.7.26
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = PASSWORD('6ps?3n2vPcXjhUfz');
# 如果从服务器是克隆的主服务器，则修改 auto.cnf（vi /var/lib/mysql/auto.cnf） 文件中 server-uuid 值
否则主从复制会报 1593 错误，修改完记得重启MySQL
3、MySQL主从复制配置
3.1、master服务器配置
1）用户添加及授权
在master 服务器授权一个账户，拥有slave权限
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.10.143' IDENTIFIED BY 'GrantT^Slave143'; mysql> FLUSH PRIVILEGES;
说明: 192.168.10.143为备库IP，GrantT^Slave143为账号repl的密码，配置时修改成实际环境的信息。
3.2. my.cnf配置修改
修改mysql配置文件，my.ini （windows）或 my.cnf（Linux）找到[mysqld] 标记，下方添加
server-id=1 log-bin=master-bin binlog-format=ROW # 以下两个为可选选项，如果要保证数据不丢失最好在清理binlog之前把要清理的日志备份，日志文件的大小根据磁盘的性能做适当的调整 expire_logs_days = 7 // binlog过期清理时间 max_binlog_size = 1G // binlog每个日志文件大小 max_connections = 1000 character-set-server = utf8mb4 // 配置字符集 collation-server = utf8mb4_unicode_ci // 配置字符集 // 配置不同步的库 binlog-ignore-db = mysql binlog-ignore-db = sys binlog-ignore-db = information_schema binlog-ignore-db = performance_schema
3.3 重启master 的mysql服务
systemctl restart mysqld
4、备库配置
在另一个mysql上配置从服务器
4.1 备库my.cnf修改
找到my.ini （windows） 或 my.cnf （linux），找到[mysqld]标记，更改如下配置
[mysqld] max_connections = 1000 log_bin = slave-bin server_id = 2 relay_log = slave-relay-bin log_slave_updates = 1 read_only = 1 #以下两个为可选选项，如果要保证数据不丢失最好在清理binlog之前把要清理的日志备份，日志文件的大小根据磁盘的性能做适当的调整 expire_logs_days = 7 //binlog过期清理时间 max_binlog_size = 100m //binlog每个日志文件大小 replicate_ignore_db=information_schema replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys
重启mysql使配置生效
systemctl restart mysqld
5、开启复制
5.1、查看主库binlog信息
登陆主库执行
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 154 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
执行之后可以获取目前主库binlog使用的文件及pos点
5.2、在从库上设置复制信息
根据上一步获取到的信息（Position, File）配置从库的复制信息，在从库上执行如下命令
CHANGE MASTER TO MASTER_HOST='192.168.10.243',MASTER_USER='repl',MASTER_PASSWORD='GrantT^Slave143',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=154;
注意：执行从库配置, 每次重启mysql均需要执行（如果没有开启自动启动主从）
参数解释：
解释：
// master的ip地址
mysql> change master to master_host=’192.168.10.243′,
// master授权的用户
master_user=’repl’,
// master的授权用户密码
master_password=’GrantT^Slave143′,
// master的binlog日志名称，这里使用上述命令搜索出来的为准
master_log_file=’edu-mysql-bin.00000x’,
// master的日志位置 这里使用上述“1.3”命令搜索出来的为准，不能带引号，必须是整型，否则会报错
master_log_pos=xx,
// 重试时间、单位秒，默认重试时间为 60s
master_connect_retry=30;
5.3、在从库上开启复制
从库复制信息配置完成后执行命令开启复制：
mysql> start slave;
5.4、查看复制状态
启动之后如果不报错即可执行如下命令查看复制的状态：
show slave status G;
主要查看下面两个参数状态，只要都是yes，表示主从通信正常。
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果均为yes则正常，否则需根据last_error信息进行调试
6、主从复制测试
# 创建数据库
create database mydb;
# 创建表
use mydb;
create table mytab1(id int(10),name varchar(50),depid int(11));
# 插入数据
insert into mytab1 (id, name, depid) values (1,’mcb’, 2),(2,’zhangsan’,3);
查看主库数据库，表，数据
查看从库数据库，表，数据
7、主从同步可能遇到的问题
基于局域网的Master/Slave机制在通常情况下已经可以满足“实时”备份的要求了。如果延迟比较大，可以从以下几个因素进行排查：
(1) 网络延迟；
(2) Master负载过高；
(3) Slave负载过高；
如果出现同步失败，可以根据提示处理错误，处理完成后，需要刷新同步配置：
先停止同步
mysql> stop slave;
清理掉之前的配置，防止同步已经同步了的数据
mysql> reset slave all;
然后重新连接主库，进行同步。
支付宝扫一扫
微信扫一扫
最新评论