MySQL主从配置，原来这么简单？

两双筷子 2021-04-21

6859

本篇笔记实际使用的是MariaDB数据库，但配置起来与MySQL配置方法无异，可正常套用：

MySQL的复制默认的异步的，主从复制至少需要两个MySQL服务，这些服务可以分布在不同服务器上，也可以在同一个台服务器上。测试环境使用虚拟机复制一台虚拟主机出来。

确保主从库安装了相同版本的数据库。因为主从库的角色可能会互换，同时减少出问题的概率，所以在可能的情况下推荐安装最新的稳定版本。
在主库上，设置一个复制使用的账户，并授予REPLICATION SLAVE
权限。这里创建一个复制用户rep1
，允许IP为192.168.0.33
的主机进行连接。

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@192.168.0.33 IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.203 sec)

修改主数据库文件的配置文件my.cnf
，开启BINLOG
，并设置server-id
的值。这两个参数的修改需要重启MySQL服务才会生效。

log-bin=mysql-bin
server-id   = 1

在主库上设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照：

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.106 sec)

然后得到主库上当前的二进制日志名和偏移量。这个操作的目的是为了在从数据库启动后从这个点开始进行数据恢复。

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 |      537 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

现在主数据库服务器已经停止更新操作，需要生成主数据库的备份数据，把备份下来的全部数据文件复制到从服务器上。（说白了就是这个时候主库和从库的数据要一样，由于我是直接复制的整个系统镜像，就不需要指这一步操作了）
主数据库备份完毕后，可以恢复写操作，剩下的操作只需要在从库上执行：

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.000 sec)

将主数据库备份的数据恢复到从数据库上。（意思就是把主库刚刚停止写入后的那个数据拿过来放在从库上，让主从库的数据一致）
修改从数据库的配置文件my.cnf
，设置server-id
参数，注意值是唯一的，不能与主库相同，如果有多个从数据库服务器，每一个从数据库服务器必须有自己唯一的server-id
值。（如果使用了GTID模式，同时删除datadir
目录下的auto.cnf
，避免与主库使用相同的UUID）

server-id   = 2

在从库上使用--skip-slave-start
选项启动从数据库，这样不会立即启动从数据库的复制进程，方便对数据库上的服务进行进一步配置：
对从数据库服务器作相应的配置，指定复制使用的用户、主服务器 IP、端口、以及开始执行复制的日志文件和位置等信息，写法如下：

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.22',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000016',
    -> MASTER_LOG_POS=537;
Query OK, 0 rows affected (0.125 sec)

在从库上启动slave
线程

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

这是slave
上执行show processlist
命令将看到以下的进程

MariaDB [test]> show processlist\G;
*************************** 1. row ***************************
      Id: 1
    User: system user
    Host:
      db: NULL
 Command: Daemon
    Time: NULL
   State: InnoDB purge coordinator
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 2
    User: system user
    Host:
      db: NULL
 Command: Daemon
    Time: NULL
   State: InnoDB purge worker
    Info: NULL
Progress: 0.000
......

这表明slave已经连接上master，开始接受并执行日志。主从复制服务配置完成。

MySQL主从异步复制是最常见和最简单的复制场景。数据的完整性完全依赖于主库的BINLOG的不丢失，只要主库的BINLOG不丢失，就算主库宕机了，我们还可以通过BINLOG把丢失部分的数据手动的同步到从库上去。

遇到的问题

配置完从库之后，主库修改数据并没有同步过来，经过排查，发现问题出在两个地方：

主库的服务器没有开放数据库端口，从库无法从外部访问主库：

设置Linux系统开放指定端口，允许外部可以访问。

注意：如果是云服务器的话，只能开内网端口，不要开云服务器的防火墙端口，端口暴露给公网，风险很大。

Slave_SQL_Running配置问题：

通过使用命令show slave status\G;
发现，Slave_SQL_Running
的配置项为NO
，需要修改为YES
，步骤如下：

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)

此时再次通过命令查看配置项正常，修改主库数据，从库同步也正常了。

auto.conf冲突：

备注：这个问题使用MySQL配置时可能会需要解决，本次配置使用MariaDB没有这个文件，不需要处理。

如果和我一样是直接复制了一个系统环境（如虚拟机复制，云服务器备份镜像等方法弄的从库服务器），那么数据目录的auto.cnf
文件理论上是一样的，会和主库冲突，解决办法是删除掉从库上面的auto.cnf
文件。

MySQL主从搭建的笔记大概就写完了，这只是最简单的一种主从配置方法，其他的方法，以后的笔记会出。

参考资料：《深入浅出MySQL：数据库开发、优化与管理维护（第三版）》

mysql

文章转载自两双筷子，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL主从配置，原来这么简单？

遇到的问题

评论