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

本文概述

• 第一个MySQL实例的安装
• 设置mysqld_multi
• 为第二个MySQL实例创建新文件夹
• AppArmor中的其他安全设置
• 第二个MySQL实例的安装
• mysqld_multi的附加配置
• 在启动时自动启动两个MySQL实例
• 设置主从复制
• 总结

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主从复制中一样, 每个实例必须具有自己的唯一服务器ID, 在[mysqld1]组中添加以下行：

server-id = 1

由于第二个MySQL实例需要一个单独的[mysqld]组, 因此请复制[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不同。

由于两个实例都将在同一台计算机上运行, ​​因此将第二个实例的端口设置为3307, 因为它必须与第一个实例使用的端口不同, 默认情况下为3306。

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

我们还需要启用中继日志, 以便将第二个实例用作从属实例(参数中继日志, 中继日志索引和中继日志信息文件), 以及设置主信息文件。

最后, 为了使从属实例为只读, 将参数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密码。请记住, 如果未指定host和port, 默认情况下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服务添加到默认运行级别：

update-rc.d mysqld_multi defaults

重新启动计算机, 并使用以下命令检查两个MySQL实例是否都在运行：

mysqld_multi report

设置主从复制

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

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

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主从复制。

总结

如果需要生产环境中的横向扩展解决方案, 则在开发环境中配置主从复制非常有用。这样, 你还将为读写操作配置了单独的数据源, 因此你可以在本地进行测试以确保一切正常, 然后再进行进一步部署。

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