mysql 5.7 主从_MySQL5.7主从同步配置

主从同步，将主服务器(master)上的数据复制到从服务器(slave)。

应用场景

读写分离，提高查询访问性能，有效减少主数据库访问压力。

实时灾备，主数据库出现故障时，可快速切换到从数据库。

数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。

部署环境

​ 注：使用docker部署mysql实例，方便快速搭建演示环境。但本文重点是讲解主从配置，因此简略描述docker环境构建mysql容器实例。

数据库：MySQL 5.7.x (相比5.5，5.6而言，5.7同步性能更好，支持多源复制，可实现多主一从，主从库版本应保证一致)

操作系统：CentOS 7.x

容器：Docker 17.09.0-ce

镜像：mysql:5.7

主库：IP=192.168.10.212; PORT=3506; server-id=100; database=test; table=user

从库：IP=192.168.10.212; PORT=3606; server-id=110; database=test; table=user

配置约束

主从库必须保证网络畅通可访问

主库必须开启binlog日志

主从库的server-id必须不同

事前准备

关闭selinux

setenforce 0

vim /etc/sysconfig/selinux

SELINUX=disabled

# 若不关闭，使用docker启动mysql5.7镜像容器时启动不起来，查看日志会有如下错误显示：

ERROR: mysqld failed while attempting to check config

command was: "mysqld --verbose --help"

mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)

mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

ERROR: mysqld failed while attempting to check config

command was: "mysqld --verbose --help"

mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)

mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

安装并启动docker

yum install epel-release

yum -y install docker

systemctl start docker.service

创建目录

mkdir -p /datavol/mysql-100/{mysql,conf}

mkdir -p /datavol/mysql-110/{mysql,conf}

【主库】操作及配置

配置my.cnf

把该文件放到主库所在配置文件路径下：/datavol/mysql-100/conf

[client]

port = 3306

default-character-set = utf8mb4

[mysql]

port = 3306

default-character-set = utf8mb4

[mysqld]

##########################

# summary

##########################

#bind-address = 0.0.0.0

#port = 3306

datadir=/datavol/mysql/data #数据存储目录，根据实际情况而定，在docker中是指定其他目录了，这个目录没用使用，但是若不是docker的话则需要指定这个配置

##########################

# log bin

##########################

server-id = 100#必须唯一

log_bin = mysql-bin #开启及设置二进制日志文件名称

binlog_format = MIXED

sync_binlog = 1

expire_logs_days =7#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m

#max_binlog_cache_size = 512m

#max_binlog_size = 256M

binlog-do-db = test #要同步的数据库

binlog-ignore-db = mysql #不需要同步的数据库

binlog_ignore_db = information_schema

binlog_ignore_db = performation_schema

binlog_ignore_db = sys

##########################

# character set

##########################

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

安装启动主库

[root@localhost ~]# docker run -d -p 3506:3306 --name=mysql-100 -v /datavol/mysql-100/conf:/etc/mysql/conf.d -v /datavol/mysql-100/mysql:/var/lib/mysql -w /var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

5691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad

[root@localhost ~]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

5691bac538e6 mysql:5.7 "docker-entrypoint..." 44 seconds ago Up 43 seconds 33060/tcp, 0.0.0.0:3506->3306/tcp mysql-100

[root@localhost ~]# docker exec -it mysql-100 /bin/bash

root@5691bac538e6:/# mysql -u root -p

Enter password:

注：若不熟悉docker，可使用传统方式安装mysql，效果相同。

创建授权用户

连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

Query OK, 0 rows affected (0.00 sec)

log_bin是否开启

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set

查看master状态

mysql> show master status;

+------------------+----------+--------------+--------------------------------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+--------------------------------------------------+-------------------+

| mysql-bin.000001 | 154 | test | mysql,information_schema,performation_schema,sys | |

+------------------+----------+--------------+--------------------------------------------------+-------------------+

1 row in set

注意：mysql-bin.000001 跟154这俩参数从库会使用到，根据实际情况修改

【从库】配置及操作

配置my.cnf

把该文件放到主库所在配置文件路径下：/datavol/mysql-110/conf/

[client]

port = 3306

default-character-set = utf8mb4

[mysql]

port = 3306

default-character-set = utf8mb4

[mysqld]

##########################

# summary

##########################

#bind-address = 0.0.0.0

#port = 3306

datadir=/datavol/mysql/data #数据存储目录，根据实际情况而定，在docker中是指定其他目录了，这个目录没用使用，但是若不是docker的话则需要指定这个配置

##########################

# log bin

##########################

server-id = 110

##########################

# character set

##########################

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

安装启动从库

[root@localhost ~]# docker run -d -p 3606:3306 --name=mysql-110 -v /datavol/mysql-110/conf:/etc/mysql/conf.d -v /datavol/mysql-110/mysql:/var/lib/mysql -w /var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7

[root@localhost ~]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

19e93b6d93ca mysql:5.7 "docker-entrypoint..." 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3606->3306/tcp mysql-110

[root@localhost ~]# docker exec -it mysql-110 /bin/bash

root@19e93b6d93ca:/# mysql -u root -p

Enter password:

设置主库信息

登录【从数据库】，进入mysql命令行。

mysql> stop slave;

Query OK, 0 rows affected

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.212',

MASTER_PORT=3506,

MASTER_USER='slave',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000001', # 主库信息

MASTER_LOG_POS=154; # 主库信息

Query OK, 0 rows affected

mysql> start slave;

Query OK, 0 rows affected

stop slave; //停止同步

start slave; //开始同步

//必须和【主数据库】的信息匹配。

CHANGE MASTER TO

MASTER_HOST='192.168.10.212', //主库IP

MASTER_PORT=3506, //主库端口

MASTER_USER='slave', //访问主库且有同步复制权限的用户

MASTER_PASSWORD='123456', //登录密码

//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果

MASTER_LOG_FILE='mysql-bin.000001',

//【关键处】从文件中指定位置开始读取，主库show master status返回结果

MASTER_LOG_POS=154;

查看同步状态

# 注意\G就已经表示结束，不用再加分号(;)了，如果加上分号的话执行后在执行结果最后会有一个错误提示：

ERROR:

No query specified

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.212

Master_User: slave

Master_Port: 3506

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 2756

Relay_Log_File: f947643ca441-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql,information_schema,performation_schema,sys

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2756

Relay_Log_Space: 534

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 100

Master_UUID: 1c0ec77d-dcee-11e8-bd51-0242ac11000a

Master_Info_File: /datavol/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的。

如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。

mysql> show variables like 'log_error%';

+---------------------+--------+

| Variable_name | Value |

+---------------------+--------+

| log_error | stderr |

| log_error_verbosity | 3 |

+---------------------+--------+

2 rows in set

配置完成，则主从数据库开始自动同步。

验证数据同步

建库

使用root账号登录【主服务器】，创建test数据库

mysql> CREATE DATABASE test;

Query OK, 1 row affected (0.00 sec)

mysql> USE test;

Database changed

建表

在【主库】中创建user表

CREATE TABLE `user` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,

`age` tinyint(3) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

新增

在【主库】中向user表插入一条数据：

mysql> use test;

Database changed

mysql> INSERT INTO user (id, name, age) VALUES (1, '张三', 18);

Database changed

mysql> SELECT * FROM user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 张三 | 18 |

+----+------+-----+

1 row in set

在【从库】中查询user表数据：

mysql> use test;

Database changed

mysql> SELECT * FROM user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 张三 | 18 |

+----+------+-----+

1 row in set

新增记录同步成功。

更新

在【主库】中修改刚才插入的数据：

# 问题：在docker环境中登陆mysql，输入中文的话不显示，就比如下面这个，复制后粘贴到MySQL命令行中执行，李四这俩字不显示，是空的，有待进一步研究

mysql> UPDATE user SET name='李四' where id=1;

Database changed

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 李四 | 18 |

+----+------+-----+

1 row in set

在【从库】中查询user表数据：

mysql> SELECT * FROM user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 李四 | 18 |

+----+------+-----+

1 row in set

更新记录同步成功。

删除

在【主库】中删除刚才更新的数据：

mysql> DELETE FROM user WHERE id=1;

Database changed

mysql> SELECT * FROM user;

Empty set

在【从库】中查询user表数据：

mysql> SELECT * FROM user;

Empty set

删除记录同步成功。

补充：

如果【主服务器】重启mysql服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。

如果某段时间内，【从数据库】服务器异常导致同步中断(可能是同步点位置不匹配)，可以尝试以下恢复方法：进入【主数据库】服务器(正常)，在bin-log中找到【从数据库】出错前的position，然后在【从数据库】上执行change master，将master_log_file和master_log_pos重新指定后，开始同步。