mysql设置主从数据同步（5.7版本）

参考

官方文档：https://dev.mysql.com/doc/refman/5.7/en/replication.html
配置参数文档：https://dev.mysql.com/doc/refman/5.7/en/replication-options.html
主从同步原理：https://www.jb51.net/article/220881.htm，https://www.jb51.net/article/202663.htm

环境

centos7+mysql5.7

数据同步

配置之前一般需要先手动同步一下数据，这里使用mysql自带的mysqldump工具，根据具体场景可以有三种导出思路：

1.备份所有库的数据
mysqldump --host=127.0.0.1 --user=主库用户名 --password=主库密码 --port=主库端口号 --events --routines --triggers --default-character-set=utf8 --quick --flush-logs --lock-all-tables --add-drop-database --all-databases > "导出文件名"

2.备份指定库的数据
mysqldump --host=127.0.0.1 --user=主库用户名 --password=主库密码 --port=主库端口号 --events --routines --triggers --default-character-set=utf8 --quick --flush-logs --lock-all-tables --add-drop-database --databases db1 db2 db3 > "导出文件名"

3.有时候只想排除指定的个别库，备份剩下所有的库，如果库数量比较多的话一个个指定显然不是个好办法，可以通过linux中的管道实现排除指定的库来备份，这种方式需要再次输入一下数据库密码
mysql -u 主库用户名 -p -e "show databases" | grep -Ev "Database|information_schema|mysql|performance_schema|sys" | xargs mysqldump --host=127.0.0.1 --user=主库用户名 --password=主库密码 --port=主库端口号 --events --routines --triggers --default-character-set=utf8 --quick --flush-logs --lock-all-tables --add-drop-database --databases > "导出文件名"

将上面导出的数据同步到从库，也是需要再次输入一下数据库密码
mysql -u 从库用户名 -p -e "备份的文件名"

mysqldump参数说明：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_databases

主从配置

1.mysql的主从同步主要是通过修改两库的配置文件my.cnf实现的，按照官方文档给的步骤，首先配置主从库的my.cnf配置文件，一般在/etc/my.cnf，找不到的话也可以通过find / -name my.cnf搜索一下。

修改主库的配置文件，在[mysqld]下面添加下面配置：

log-bin=mysql-bin		# 开启binlog日志，必须
server-id=1				# 配置主库机器id，必须，值为大于0小于等于2^32-1的整数

binlog-do-db=db_name	# !!!注意：这里指的是在配置的db中进行的sql操作才会被binlog同步，不管操作的是具体哪个数据库，而不是只同步配置的db的意思，省略时默认全部，一般省略默认就行。
1
2
3
4

修改从库的配置文件，在[mysqld]下面添加下面配置：

log-bin=mysql-bin		# 开启从库的binlog日志，可选
server-id=2				# 配置从库机器id，必须，在取值范围内和主库server-id不同即可
1
2

配置完成后通过service mysqld restart分别重启一下主从库使配置文件生效。

2.在主库中为数据同步单独创建并赋权一个用户(可选，只要是在mysql.user表中具有Repl_slave_priv权限和Host的值包含从机ip的用户都行)：

CREATE USER 'repl'@'slave-host' IDENTIFIED BY 'password';		# @后面的地址为从机的地址
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave-host';			# 将主库所有库中所有表的同步权限赋给新建的用户
flush privileges;
1
2
3

3.主库中查看binlog日志记录到的位置：

SHOW MASTER STATUS;
1

我的是

4.在从库中设置要同步的主库信息：

CHANGE MASTER TO
		MASTER_HOST='xxx',					# 主机ip
		MASTER_USER='xxx',					# 新建用户的用户名
		MASTER_PASSWORD='xxx',				# 新建用户的密码
		MASTER_PORT = xxx，					# 主库端口号
		MASTER_LOG_FILE='xxx',				# 上面File字段的值
		MASTER_LOG_POS=xxx;					# 上面Position字段的值
1
2
3
4
5
6
7

5.最后在从库中启动同步：

START SLAVE;
1

配置完成。

验证

验证同步配置是否成功，可以在从库中执行下面语句：

show slave status;
1

Slave_IO_Running和Slave_SQL_Running都为yes即为配置成功。