同一台机器上的 MySQL 主从复制

已发表: 2022-03-11

MySQL 复制是一个过程，它可以将来自一个 MySQL 数据库服务器（主）的数据自动复制到一个或多个 MySQL 数据库服务器（从）。它通常用于在多个服务器上分散读取访问以实现可伸缩性，尽管它也可用于其他目的，例如故障转移或分析从属服务器上的数据以免使主服务器过载。

由于主从复制是一种单向复制（从主到从），写操作只使用主库，而读操作可能分布在多个从库上。这意味着如果使用主从复制作为横向扩展解决方案，您至少需要定义两个数据源，一个用于写入操作，第二个用于读取操作。

MySQL 开发人员通常只在一台机器上工作，并且倾向于在这台机器上拥有整个开发环境，逻辑上他们不依赖于网络或 Internet 连接。如果需要主从复制，例如，他们需要在开发环境中测试复制，然后在其他地方部署更改，他们必须在同一台机器上创建它。虽然单个 MySQL 实例的设置相当简单，但我们需要付出一些额外的努力来设置第二个，然后是主从复制。

在本分步教程中，我选择了 Ubuntu Linux 作为主机操作系统，并且提供的命令适用于该操作系统。如果要在其他操作系统上设置 MySQL 主从复制，则需要对其特定命令进行修改。但是，在同一台机器上设置 MySQL 主从复制的一般原则对于所有操作系统都是相同的。

安装第一个 MySQL 实例

如果您的机器上已经安装了一个 MySQL 数据库实例，则可以跳过此步骤。

在 Ubuntu 上安装 MySQL 的最简单方法是从终端提示符运行以下命令：

 sudo apt-get install mysql-server

在安装过程中，系统会提示您为 MySQL root用户设置密码。

设置`mysqld_multi`

为了有效地管理同一台机器上的两个 MySQL 实例，我们需要使用mysqld_multi 。

设置mysqld_multi的第一步是在现有的my.cnf文件中创建两个单独的[mysqld]组。 Ubuntu 上my.cnf文件的默认位置是/etc/mysql/ 。因此，使用您喜欢的文本编辑器打开my.cnf文件，并将现有[mysqld]组重命名为[mysqld1] 。这个重命名的组将用于第一个 MySQL 实例的配置，也将被配置为主实例。在 MySQL 主从复制中，每个实例都必须有自己唯一的server-id ，在[mysqld1]组中添加以下行：

 server-id = 1

由于我们需要一个单独的[mysqld]组用于第二个 MySQL 实例，因此复制具有所有当前配置的[mysqld1]组，并将其粘贴到同一个my.cnf文件中。现在，将复制的组重命名为[mysqld2] ，并在从站的配置中进行以下更改：

 server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1

要将第二个 MySQL 实例设置为从属，请将server-id设置为 2，因为它必须与主服务器的 server-id 不同。

由于两个实例将在同一台机器上运行，请将第二个实例的port设置为3307 ，因为它必须与第一个实例使用的端口不同，默认情况下为3306 。

为了使第二个实例能够使用相同的 MySQL 二进制文件，我们需要为socket 、 pid-file 、 datadir和log_error设置不同的值。

我们还需要启用relay-log以将第二个实例用作从属（参数relay-log 、 relay-log-index和relay-log-info-file ），以及设置master-info-file 。

最后，为了使从属实例只读，参数read_only设置为1 。您应该小心使用此选项，因为它不能完全阻止从站上的更改。即使read_only设置为1 ，也只允许拥有SUPER权限的用户进行更新。 MySQL 最近引入了新参数super_read_only以防止SUPER用户进行更改。此选项在版本 5.7.8 中可用。

除了[mysqld1]和[mysqld2]组之外，我们还需要在my.cnf文件中添加一个新组[mysqld_multi] ：

 [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass

一旦我们安装了第二个 MySQL 实例并启动了这两个实例，我们将为multi_admin用户提供适当的权限，以便能够关闭 MySQL 实例。

为第二个 MySQL 实例创建新文件夹

在上一步中，我们为第二个 MySQL 实例准备了配置文件。在该配置文件中，使用了两个新文件夹。应使用以下 Linux 命令来创建具有适当权限的文件夹：

 mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave

AppArmor 中的其他安全设置

在某些 Linux 环境中，需要 AppArmor 安全设置才能运行第二个 MySQL 实例。至少，它们在 Ubuntu 上是必需的。

要正确设置 AppArmor，请使用您喜欢的文本编辑器编辑/etc/apparmor.d/usr.sbin.mysqld文件，添加以下行：

 /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,

保存文件后，重新启动计算机以使这些更改生效。

安装第二个 MySQL 实例

安装第二个 MySQL 实例可以采用几种不同的方法。本教程中介绍的方法使用与第一个相同的 MySQL 二进制文件，第二个安装需要单独的数据文件。

由于我们在前面的步骤中已经准备好配置文件和必要的文件夹和安全更改，所以第二个 MySQL 实例的最后安装步骤是 MySQL 数据目录的初始化。

执行以下命令以初始化新的 MySQL 数据目录：

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

初始化 MySQL 数据目录后，您可以使用mysqld_multi服务启动两个 MySQL 实例：

 mysqld_multi start

使用mysqladmin和适当的主机和端口设置第二个 MySQL 实例的root密码。请记住，如果未指定主机和端口， mysqladmin将默认连接到第一个 MySQL 实例：

 mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

在上面的示例中，我将密码设置为“rootpwd”，但建议使用更安全的密码。

`mysqld_multi`的附加配置

在“设置 mysqld_multi”部分的最后，我写道，稍后我们将为multi_admin用户提供适当的权限，所以现在是时候了。我们需要在这两个实例中给这个用户适当的权限，所以让我们首先连接到第一个实例：

 mysql --host=127.0.0.1 --port=3306 -uroot -p

登录后，执行以下两条命令：

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

从 MySQL 客户端退出，并连接到第二个实例：

 mysql --host=127.0.0.1 --port=3307 -uroot -p

登录后，执行与上面相同的两个命令：

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

退出 MySQL 客户端。

启动时自动启动两个 MySQL 实例

设置mysqld_multi的最后一步是在init.d中安装自动引导脚本。

为此，请在/etc/init.d中创建名为mysqld_multi的新文件，并为其赋予适当的权限：

 cd /etc/init.d touch mysqld_multi chmod +x /etc/init.d/mysqld_multi

使用您喜欢的文本编辑器打开这个新文件，然后复制以下脚本：

 #!/bin/sh ### BEGIN INIT INFO # Provides: scriptname # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO bindir=/usr/bin if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi"; exit; fi case "$1" in 'start' ) "$mysqld_multi" start $2 ;; 'stop' ) "$mysqld_multi" stop $2 ;; 'report' ) "$mysqld_multi" report $2 ;; 'restart' ) "$mysqld_multi" stop $2 "$mysqld_multi" start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac

使用以下命令将mysqld_multi服务添加到默认runlevels ：

 update-rc.d mysqld_multi defaults

重新启动您的机器，并使用以下命令检查两个 MySQL 实例是否正在运行：

 mysqld_multi report

设置主从复制

现在，当我们在同一台机器上运行两个 MySQL 实例时，我们将第一个实例设置为主实例，将第二个实例设置为从属。

部分配置已在“设置 mysqld_multi”一章中执行。 my.cnf文件中唯一剩下的更改是在主服务器上设置二进制日志记录。为此，请编辑my.cnf文件，在[mysqld1]组中进行以下更改和添加：

 log_bin = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW

重新启动主 MySQL 实例以使这些更改生效：

 mysqld_multi stop 1 mysqld_multi start 1

为了让从服务器以正确的复制权限连接到主服务器，应该在主服务器上创建一个新用户。使用具有适当主机和端口的 MySQL 客户端连接到主实例：

 mysql -uroot -p --host=127.0.0.1 --port=3306

为复制创建一个新用户：

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

退出 MySQL 客户端。

执行以下命令以创建主数据的转储：

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

在这里，我们使用选项--master-data=2以便在备份文件中添加包含CHANGE MASTER语句的注释。该注释表示备份时的复制坐标，稍后我们将需要这些坐标来更新从属实例中的主信息。这是该评论的示例：

 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;

将您在上一步中创建的转储导入从属实例：

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

最后，为了从属实例连接到主实例，需要使用适当的连接参数更新从属实例的主信息。

使用具有适当主机和端口的 MySQL 客户端连接到从属实例：

 mysql -uroot -p --host=127.0.0.1 --port=3307

执行以下命令以更新主信息（从转储文件replicationdump.sql中获取复制坐标，如上所述）：

 mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_USER='replication', -> MASTER_PASSWORD='replication', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=349;

执行以下命令以启动从站：

 mysql> START SLAVE;

执行以下命令以验证复制是否已启动并正在运行：

 mysql> SHOW SLAVE STATUS \G

恭喜。您的 MySQL 主从复制现在已成功设置在同一台机器上。

包起来

如果您需要将主从复制用于生产环境中的横向扩展解决方案，则在开发环境中配置主从复制非常有用。这样，您还将为写入和读取操作配置单独的数据源，以便在进一步部署之前在本地测试一切是否按预期工作。

此外，您可能希望在同一台机器上配置多个从属实例，以测试将读取操作分配给多个从属的负载均衡器。在这种情况下，您可以使用同一手册通过重复所有相同的步骤来设置其他从属实例。