MySQL主从复制实践

多个数据库间主从复制与读写分离的模型

​ 在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化；业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行

​ Mysql 主从复制的实现原理图大致如下

​ MySQL 之间数据复制的基础是二进制日志文件（binary log file）

​ 一台 MySQL 数据库一旦启用二进制日志后，其作为 master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为 slave 通过一个I/O 线程与主服务器保持通信，并监控 master 的二进制日志文件的变化，如果发现 master 二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave 的一个 SQL 线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制

配置主从复制

master 服务器配置

​ 修改 master 数据库配置文件（my.cnf）

[mysqld]
# 开启二进制日志
log-bin=mysql-bin 
# 设置 server-id
server-id=1 

​ 重启 master 数据库

service mysql restart

​ 通过mysql -hlocalhost -uname -ppassword命令进入 master 数据库，创建一个用于和 slave 通信的用户账号

# 创建用户
CREATE USER 'username'@'slave ip' IDENTIFIED BY 'password';
# 分配权限
GRANT REPLICATION SLAVE ON *.* TO 'username'@'slave ip';
# 刷新权限
flush privileges;   

​ 查看 master 数据库二进制日志文件名及位置（File 列记录的是二进制文件名，Position 记录的是位置）

mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000074 | 156968749 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

slave 服务器配置

​ 修改 slave 数据库配置文件（my.cnf）

[mysqld]
# 设置 server-id,必须唯一
server-id=2

​ 重启 slave 数据库，并通过以下 sql 配置 master 信息

CHANGE MASTER TO
MASTER_HOST='master ip',
MASTER_USER='上面创建的账号名称',
MASTER_PASSWORD='上面创建的账号密码',
MASTER_LOG_FILE='master 二进制日志文件名',
MASTER_LOG_POS='master 二进制日志文件记录的位置';

​ 启动 slave 同步进程（停止同步进程：stop slave;）

mysql>start slave;

启动 slave 同步进程前需要将 master 数据库的数据迁移到 salve 数据库

​ 查看同步状态

mysql> show slave status\G;

​ 当 Slave_IO_Running 和 Slave_SQL_Running 都为 YES 的时候就表示主从同步设置成功了

​ 接下来就可以进行一些验证了，比如在 master 数据库一张表中插入一条数据，在 slave 的相同数据表中查看是否有新增的数据，还可以关闭 slave，然后再修改 master，看 slave 是否也相应修改（停止 slave 后，master 的修改不会同步到 slave）

​ 扩展，还可以用到的其他相关参数：

​ master 开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在 mysql 配置文件的 [mysqld] 可添加修改如下选项：

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game

​ 参考文档：https://www.cnblogs.com/gl-developer/p/6170423.html