参考文档:https://dev.mysql.com/doc/refman/5.7/en/replication.html
本文是对上述文档的关键步骤进行整理,有时间还是看看文档全面了解更好
一、Master 配置
在 my.cnf
或 my.ini
配置文件的 [mysqld]
部分添加如下配置:
[mysqld]
# 服务器 ID,在 1和(2^32)-1 之间的正整数,不能和其他 MySQL 服务器ID重复
server-id=1
# 启用二进制日志,值为日志文件的前缀
log-bin=mysql-bin
# 为了在使用InnoDB事务的复制设置中实现最大的持久性和一致性
# 您应该在主 文件中启用下面两项配置
innodb_flush_log_at_trx_commit=1
sync_binlog=1
上面最后两个配置可以提供最高的数据安全,但是会导致更高的磁盘 IO,因此 TPS 最低,实际使用时需要根据自己业务进行测试寻求更合适的配置。
参数详细说明:sysvar_innodb_flush_log_at_trx_commit
二、创建用于复制的用户
创建用户
CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';
三、获取二进制日志坐标
为了主从一致,需要先禁用主机的增删改操作,给所有表设置读锁。
在 master 执行:
FLUSH TABLES WITH READ LOCK;
警告
让发出FLUSH TABLES
语句的客户端保持运行状态,以使读锁保持有效。如果退出客户端,则会释放锁。
在 master 上打开一个新的会话,执行:
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
记录 File 和 Position 信息。
四、同步现有数据
如果 master 已有数据,需要先同步。同步方法如下。
4.1 使用 mysqldump
在命令行执行:
$ mysqldump --all-databases --master-data > dbdump.db
五、Slaves 配置
5.1 在 master 上释放锁
UNLOCK TABLES;
5.2 配置 slave 服务器
修改 my.cnf
或 my.ini
配置:
[mysqld]
server-id=2
多个 slave 时需要保证 server-id 不重复
slave 不需要开启 log-bin
,如果开启,就能通过二进制日志进行数据备份或崩溃恢复操作。同时还可以作为另一个 slave 的 master(主-从(主)-从
)。
5.3 设置 master 信息
执行下面的 SQL:
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name',
MASTER_USER='replication_user_name',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;
将上面参数 'xxx'
部分修改为对应的主机名(IP),复制用的用户名和密码,SHOW MASTER STATUS;
时看到的二进制文件名和 Position。
5.4 恢复 master 数据
如果在前面导出了现有数据,这里需要先将数据导入,执行下面的命令:
$ mysql -h master < dbdump.db
如果在 mysqldump 使用了
--single-transaction
,导入数据时会自动执行上述配置。
5.5 启动 Slave 线程
执行SQL:START SLAVE;
六、实践
简单尝试时可以通过 docker 快速部署两个容器用于学习。
参考:https://hub.docker.com/_/mysql
Docker 宿主机 IP:192.168.200.202
准备挂载目录结构如下:
/docker
└── mysql
├── master
│ ├── conf
│ │ └── my.cnf
│ └── data
└── slave
├── conf
│ └── my.cnf
└── data
6.1 Master
my.cnf
配置文件:
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
init-connect='SET NAMES utf8mb4'
max_connections=2000
lower_case_table_names=1
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
这里设置了字符集为 utf8mb4
通过 docker 启动服务:
docker run --name mysql-master \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306 \
-v /docker/mysql/master/conf:/etc/mysql \
-v /docker/mysql/master/data:/var/lib/mysql \
-d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
6.2 Slave
my.cnf
配置文件:
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
init-connect='SET NAMES utf8mb4'
max_connections=2000
lower_case_table_names=1
server-id=10
log-bin=mysql-bin
这里设置了字符集为 utf8mb4
通过 docker 启动服务:
docker run --name mysql-slave \
-e MYSQL_ROOT_PASSWORD=root \
-p 3307:3306 \
-v /docker/mysql/slave/conf:/etc/mysql \
-v /docker/mysql/slave/data:/var/lib/mysql \
-d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
6.3 测试
6.3.1. 准备数据
先在 master 执行 SQL:数据库结构.sql,这就是有现有数据的情况。
6.3.2. 备份 master
执行下面的 docker 命令备份数据:
$ docker exec mysql-master sh -c 'exec mysqldump --all-databases --master-data --single-transaction --include-master-host-port -uroot -proot' > /docker/mysql/dbdump.db
6.3.3. 还原 slave
执行下面的 docker 命令还原数据:
$ docker exec -i mysql-slave sh -c 'exec mysql -uroot -proot' < /docker/mysql/dbdump.db
6.3.4 创建复制用户
创建用户
CREATE USER 'slave'@'%' IDENTIFIED BY 'slave';
赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
提醒
第一次使用@'192.168.200.202'
出现了下面的 slave 状态Slave_IO_Running: Connecting
Slave_SQL_Running: Yes然后在 master 日志发现:
[Note] Access denied for user ‘slave’@‘172.17.0.1’ (using password: YES)
上面展示的是容器 IP没有访问权限,修改为
@'%'
后状态变为正常。
6.3.5 slave 配置 master
CHANGE MASTER TO
MASTER_HOST='192.168.200.202',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=15114;
其中 MASTER_LOG_FILE
和 MASTER_LOG_POS
值可以在上面备份 /docker/mysql/dbdump.db
的 SQL 语句中看到,也可以按照前文加锁保证备份时这两个参数不变。
6.3.6 修改 master 查看 slave 变化
随便增删改数据或增加数据库、表等查看效果。
七、小结
很早很早以前就做过主从方面的数据库配置,当初也是看别人文章学,如今发现不同文档配置有差异,因此看官方文档从头操作一遍,MySQL 官方文档很全面,当前只看了一小部分内容,等看全再补充,同时推荐看到这里的朋友看看官方文档了解更多细节。