mysql 主从复制简单实现

简介

https://dev.mysql.com/doc/refman/5.7/en/replication.html

mysql 的复制，是使一个 mysql 数据库（主库）的数据，复制到一个或多个 mysql 数据库（从库）。基于复制，可以实现很多方案，比如说：

读写分离：将负载分散到多个从库上来提高性能，增删改操作在主库上执行，而查询操作则由一个或多个从库来完成。
数据安全：因为数据会复制到从库，从库可以暂停复制过程，所以可以在从库上运行备份服务，而不会影响到主库。
数据分析：在主库上创建数据，而分析可以在从库进行，就不会影响到主库的性能。
远程数据分发：可以创建远程数据库的本地副本，就不用一直访问远程站点了。
更多

传统的方法是使用二进制日志来进行复制，不过5.7有一种新的方法，使用全局事务标识符（GTIDs），这种方法和二进制日志比起来会方便很多，这次复制使用二进制日志完成。

环境

主库
ubuntu 14.04
mysql 5.7.18
从库
ubuntu 16.04
mysql 5.7.18

基本配置

主库的设置

主库必须启用二进制日志，并且设置server-id，server-id的范围是1到2³²-1。

1
2
3


[mysqld]
log-bin=mysql-bin
server-id=1

tips:

如果不设置server-id，或者设置为0（默认值），主库会拒绝任何从库的连接。
InnoDB应该设置innodb_flush_log_at_trx_commit=1和sync_binlog=1
不要启用skip-networking

建立用户

从库使用mysql的用户名和密码来连接主库，所以主库需要一个账户给从库使用，任何具有REPLICATION SLAVE权限的用户都可以。但是鉴于用户名和密码都是明文储存在从库中，所以还是新建一个只具有REPLICATION SLAVE权限的用户比较好。

1
2


mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

获取主库二进制文件位置

在主库中执行：

1

mysql> FLUSH TABLES WITH READ LOCK;

这条语句会阻塞写语句，关闭回话会自动释放，参考 https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-with-read-lock

然后执行SHOW MASTER STATUS;

1
2
3
4
5
6


mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

从库的设置

从库也需要设置server-id，需要保证与主库不同。

从库的操作

首先，如果主库已经有数据了，就要先将主库的数据复制到从库中，之后执行CHANGE MASTER TO：

1
2
3
4
5
6


mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'host_name',
    -> MASTER_USER = 'user_name',
    -> MASTER_PASSWORD = 'password',
    -> MASTER_LOG_FILE = 'master_log_name',
    -> MASTER_LOG_POS = master_log_pos;

一般这五个参数就可以了，详细参数参考 https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

之后执行

1

mysql> START SLAVE;

如果没什么意外，之后在主库进行的操作，就会同步到从库中了。

查看从库状态可以使用

1

show slave status\G

一些设置

设置需要好好的看手册，不然数据库的行为会很奇怪。

binlog-do-db=db_name

这个设置用来设置同步哪些库，与不同的binlog-format结合会产生不同的效果，如果需要同步多个库，需要把这条语句写多次，而不是用逗号分割，因为mysql的库名是允许逗号的。参考 https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

binlog-ignore-db=db_name

与binlog-do-db类似，是忽略哪些库，参考 https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_binlog-ignore-db

binlog-format={ROW|STATEMENT|MIXED}

参考 https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html

其它

复制相关的设置有很多，在使用之前应该先去查一下手册，避免不必要的麻烦和诡异现象。

常见问题

这里列举了一些比较常见的问题：https://dev.mysql.com/doc/refman/5.7/en/faqs-replication.html

结尾

mysql的复制，提供了强大和复杂的功能，除了本文之外，还可以实现一主多从，互为主从等等，我们可以用这个功能搞很多事。

这篇文章只是草草的记录了一下自己尝试的过程。实现同步已经过去好几天了，可能有些细节有问题。下一次可以试试5.7新的GTIDs方式同步，也可以试试读写分离。

越看mysql的文档，越觉得组织方式的优点很明显，合理的目录保证快速定位，段落简洁和简短的介绍保证快速浏览，大量的链接保证快速跳转。

文章目录

简介

环境