centos7 MySQL实现主从同步

介绍

1、什么是mysql主从同步？

当master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。

2、主从同步有什么好处？

• 水平扩展数据库的负载能力。
• 容错，高可用。Failover(失败切换)/High Availability
• 数据备份。

3、简单介绍

不管是delete、update、insert，还是创建函数、存储过程，所有的操作都在master上。当master有操作的时候,slave会快速的接收到这些操作，从而做同步。

准备工作

• 两台服务器
  我这里准备了两台虚拟机
  配置 1G1H CentIOS7.6

• 安装好mysql
  在两台服务器上都安装好mysql
  推荐安装相同版本 Master.version <= Slave.version

• master
  192.168.10.244:3306
  version：5.7.37

• slave
  172.16.98.129:3306
  version：5.7.37

Master Version
SlaveVersion

创建数据库和表用于演示

• 分别登录master机和slave机的mysql

mysql –u root –p
# 回车输入密码，输入时密码不可见

• 创建一个数据库 用户演示效果

# 数据库名字可以自己定义
create database examples;

你也可以使用类似Navicat等数据库工具做上述操作。

设置master和slave

master

修改master机器中mysql配置文件my.cnf，该文件在/etc目录下

vi /etc/my.cnf

在[mysqld]配置段添加如下配置

server-id=1
log-bin=mysql-bin
log-slave-updates=1
#需要同步的数据库,不做此配置则同步所有的数据库
binlog-do-db=examples
#被忽略的数据库
binlog-ignore-db=mysql

在master mysql上为slave添加一个同步帐号

# 可以设置自己想要设置的密码
grant replication slave on *.* to 'exmacc'@'172.16.98.129' identified by 'Abc110.!.';

# 执行生效
flush  privileges;

重启master mysql服务

# 以下都能实现
systemctl restart mysqld

service mysqld restart

登录mysql 查看日志情况

show master status;

得到File和Position，在后面slave的配置时会用到

Slave

修改slave机中mysql配置文件

vi /etc/my.cnf

在[mysqld]字段下添加下面的配置

server-id=2
log-bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1
log-slave-updates=1
# 要同步的数据库,不做此配置则同步所有数据库
replicate-do-db=examples

保存后，重启mysql服务

systemctl restart mysqld

验证主机连接

mysql -h192.168.10.244 -uexmacc -p

# 回车输入之前配置的密码

由于是使用虚拟机在演示，可能会出现上述问题，在master执行下面命令

grant replication slave on *.* to 'exmacc'@'FrankdeMBP' identified by 'Abc110.!.';

再次验证主机

可以通信了

去Master 获取File和Position

show master status;

设置Slave复制，在slave mysql root用户下执行下面sql

CHANGE MASTER TO
MASTER_HOST='192.168.10.244',
MASTER_USER='exmacc',
MASTER_PASSWORD='Abc110.!.',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=448,
MASTER_CONNECT_RETRY=10;

执行完，启动Slave。

# 启动
start slave;

# 停止
stop slave

# 重置
reset master

查询slave状态

show slave status;

主要查看Slave_IO_Running和Slave_SQL_Running 两列是否都为Yes。

测试主从服务能否同步

进入master mysql操作

• 切换数据库

use examples;

• 创建一个表

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(3) NOT NULL,
  PRIMARY KEY (`id`)
);

插入数据

INSERT INTO `student` VALUES (1,'张三', 18);
INSERT INTO `student` VALUES (2,'李四', 28);
INSERT INTO `student` VALUES (3,'王五', 38);
INSERT INTO `student` VALUES (4,'赵六', 48);

进入slave mysql 查看数据是否同步过来

# 切换数据库
use examples;

# 查询表数据
select * from `student`;

Slave和Master上的数据一致，说明配置成功了。

常用启动配置选项

mysqld程序：镜像(主控镜像服务器)

配置	描述
server-id = n	给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。
log-bin = name	启用二进制日志功能。这种日志的文件名是filename.n或默认的hostname.n，其中的n是一个6位数字的整数(日志文件顺序编号)。
binlog-do-db = dbname	只把给定数据库里的变化情况记入二进制日志文件。
binlog-ignore-db = dbname	不把给定的数据库里的变化记入二进制日志文件。

mysqld程序：镜像(从属镜像服务器)

配置	描述
server-id = n	给服务器分配一个唯一的ID编号
log-slave-updates	启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)；这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作
master-host = hostname	主控服务器的主机名或IP地址。如果从属服务器上存在mater.info文件(镜像关系定义文件)，它将忽略此选项。
master-user = replicusername	从属服务器用来连接主控服务器的用户名。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-password = passwd	从属服务器用来连接主控服务器的密码。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-port = n	从属服务器用来连接主控服务器的TCP/IP端口(默认设置是3306端口)。
master-connect-retry = n	如果与主控服务器的连接没有成功，则等待n秒(s)后再进行管理方式(默认设置是60s)。如果从属服务器存在mater.info文件，它将忽略此选项。
master-ssl-xxx = xxx	对主、从服务器之间的SSL通信进行配置。
read-only = 0/1	0: 允许从属服务器独立地执行SQL命令(默认设置); 1: 从属服务器只能执行来自主控服务器的SQL命令。不过如果是超级用户依然可以对从数据库进行更新操作
read-log-purge = 0/1	1: 把处理完的SQL命令立刻从中继日志文件里删除(默认设置); 0: 不把处理完的SQL命令立刻从中继日志文件里删除。
replicate-do-table = dbname.tablename	与–replicate-do-table选项的含义和用法相同，但数据库和数据库表名字里允许出现通配符”%” (例如: test%.%–对名字以”test”开头的所有数据库里的所以数据库表进行镜像处理)。
replicate-do-db = name	只对这个数据库进行镜像处理。
replicate-ignore-table = dbname.tablename	不对这个数据表进行镜像处理。
replicate-wild-ignore-table = dbn.tablen	不对这些数据表进行镜像处理。
replicate-ignore-db = dbname	不对这个数据库进行镜像处理。
replicate-rewrite-db = db1name > db2name	把主控数据库上的db1name数据库镜像处理为从属服务器上的db2name数据库。
report-host = hostname	从属服务器的主机名; 这项信息只与SHOW SLAVE HOSTS命令有关–主控服务器可以用这条命令生成一份从属服务器的名单。
slave-compressed-protocol = 1	主、从服务器使用压缩格式进行通信–如果它们都支持这么做的话。
slave-skip-errors = n1, n2, …或all	即使发生出错代码为n1、n2等的错误，镜像处理工作也继续进行(即不管发生什么错误，镜像处理工作也继续进行)。如果配置得当，从属服务器不应 该在执行 SQL命令时发生错误(在主控服务器上执行出错的SQL命令不会被发送到从属服务器上做镜像处理); 如果不使用slave-skip-errors选项，从属服务器上的镜像工作就可能因为发生错误而中断，中断后需要有人工参与才能继续进行。必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本。

如果在配置文件里给出的某个选项是mysqld无法识别的，MySQL服务器将不启动。

注意：以上操作均在centos7.6 root账户下操作！