在Debian 8（Jessie）上使用MySQL设置master-master复制-系统运维-优客志

本教程介绍了具有2个节点的复制MySQL安装（Mater / Master复制），其中数据可以同时读取和写入两个节点。 MySQL会将数据复制到另一个节点，并确保主自动递增密钥不会相互冲突。

自版本5以来，MySQL自带内置的master-master复制支持，解决了自生产密钥可能发生的问题。在以前的MySQL版本中，主 - 主复制的问题是如果节点A和节点B在同一个表上插入自动递增密钥，则会立即产生冲突。 master-master复制在传统主从复制方面的优点是，您不必修改应用程序，只能对主机进行写入访问，而且更容易提供高可用性，因为如果主器件失败，你还有另一个主人。

1初步说明

在本教程中，我将展示如何将IP地址为192.168.1.101的服务器server1.example.com的数据库exampledb 复制到服务器server2.example.com ，IP地址为192.168.1.102 ，反之亦然。每个系统同时是另一个主机的从机和另一个从机的主机。两个系统都在运行Debian 8; 然而，配置应该适用于几乎所有的配置，很少或没有修改。

2安装MySQL 5.5

如果尚未在server1和server2上安装MySQL，请立即安装：

server1 / server2：

apt-get -y install mysql-server-5.5 mysql-client-5.5

为了确保复制可以工作，我们必须让MySQL在所有接口上监听，因此我们在/etc/mysql/my.cnf中注释出了bind-address = 127.0.0.1 ：

server1 / server2：

nano /etc/mysql/my.cnf

[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
[...]

之后重启MySQL：

server1 / server2：

service mysql restart

然后检查

server1 / server2：

netstat -tap | grep mysql

MySQL在所有接口上都是真正的监听：

netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 15437/mysqld
server1:~#

现在我们设置一个可以由server2使用的复制用户slave2_user来访问server1上的MySQL数据库。

server1：

登录到MySQL shell：

mysql --defaults-file=/etc/mysql/debian.cnf

在MySQL shell上，运行以下命令：

server1：

GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'secretpassword';
FLUSH PRIVILEGES;
quit;

用您选择的安全密码替换“ secretpassword ”。现在我们在server2上再次执行最后两步：

server2：

mysql --defaults-file=/etc/mysql/debian.cnf

GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'secretpassword';
FLUSH PRIVILEGES;
quit;

在这里用安全密码替换“ secretpassword ” 一词。请稍后注意我们需要的密码。

3一些注释

在下面我将假设两个MySQL服务器是空的（不包含任何数据库，除了'mysql'数据库）。

如果您的服务器不是这样，那么您必须在第一台服务器上锁定和转储数据库，并在继续之前将其导入第二个服务器。在复制设置之前，请勿解锁数据库。下面的几个命令显示如何将所有数据库复制到新的服务器，以防您不能从“干净的”MySQL安装程序开始。

关于如何锁定MySQL数据库中的所有数据库表的示例。

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

关于如何将所有数据库转储到文件all_databases.sql的示例。

mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers --all-databases -r all_databases.sql

关于如何从文件all_databses.sql导入第二个服务器上的所有表的示例。

mysql --defaults-file=/etc/mysql/debian.cnf < all_databases.sql

4设置复制

现在我们在/etc/mysql/my.cnf中设置master-master 复制 。 master-master复制的关键配置选项是auto_increment_increment和auto_increment_offset ：

auto_increment_increment控制连续的AUTO_INCREMENT值之间的增量。
auto_increment_offset确定AUTO_INCREMENT列值的起始点。

假设我们有N个MySQL节点（在本例中为N = 2），那么auto_increment_increment在所有节点上都具有N值，并且每个节点对于auto_increment_offset （1,2，...，N）必须具有不同的值。

现在我们来配置我们的两个MySQL节点：

server1：

nano /etc/mysql/my.cnf

搜索以[mysqld]开头的部分，并在其中放入以下选项（注释掉所有现有的冲突选项）：

[...]
[mysqld]

# Unique Server ID
server-id = 1

# Do not replicate the following databases
binlog-ignore-db = mysql
replicate-ignore-db = mysql

# Auto increment offset
auto-increment-increment = 2

# Do not replicate sql queries for the local server ID
replicate-same-server-id = 0

# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset = 1

# Delete binlog data after 10 days
expire_logs_days = 10

# Max binlog size
max_binlog_size = 500M

# Binlog file path
log_bin = /var/log/mysql/mysql-bin.log

[...]

然后重启MySQL：

server1：

service mysql restart

现在在server2上做同样的事情：

server2：

nano /etc/mysql/my.cnf

[...]

# Unique Server ID
server-id = 2

# Do not replicate the following databases
binlog-ignore-db = mysql
replicate-ignore-db = mysql

# Auto increment offset
auto-increment-increment = 2

# Do not replicate sql queries for the local server ID
replicate-same-server-id = 0

# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset = 2

# Delete binlog data after 10 days
expire_logs_days = 10

# Max binlog size
max_binlog_size = 500M

# Binlog file path
log_bin = /var/log/mysql/mysql-bin.log

[...]

server2：

service mysql restart

接下来，我们将exampledb数据库锁定在server1上 ，了解server1的主状态，创建一个exampledb的SQL转储（我们将导入到server2中的exampledb ，以便两个数据库都包含相同的数据），并解锁数据库可以再次使用：

server2：

现在我们开始在Server 2上复制。打开MySQL shell：

mysql --defaults-file=/etc/mysql/debian.cnf

并执行以下SQL命令激活从server1到server2的复制：

CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_USER='repl', MASTER_PASSWORD='secretpassword';

将secretpassword替换为您在第2章中设置的repl MySQL用户的密码。

现在通过在MySQL shell中执行命令“show slave status \ G”检查从机状态。

show slave status\G

输出将类似于：

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 192.168.1.101
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 107
 Relay_Log_File: mysqld-relay-bin.000003
 Relay_Log_Pos: 253
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: No
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB: mysql
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 107
 Relay_Log_Space: 410
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
1 row in set (0.00 sec)

你应该检查的行是这些：

Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3306
Master_Log_File: mysql-bin.000001
Relay_Log_File: mysqld-relay-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No

现在使用这个命令在MySQL shell上启动复制：

start slave;

然后再次检查从站状态：

show slave status\G

以下两行应显示“是”：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

如果“Seconds_Behind_Master”不为0，则等待几秒钟再次检查状态。该字段显示主从是否同步。

对于下一步，我们需要知道“ 显示从站状态\ G”命令的“Master_Log_File”和“Read_Master_Log_Pos”的值。在我的情况下，这些是：

Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107

记下您在服务器上获得的值，我们需要它在服务器1的下一步。

之后你可以离开MySQL shell：

quit

server1：

我们继续在第一台服务器上，在server1上打开MySQL shell：

mysql --defaults-file=/etc/mysql/debian.cnf

并执行以下MySQL命令：

CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_USER='repl', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

你必须在上面的命令中替换几件事情：

IP地址必须是您的第二个MySQL服务器的IP。
密码“secretpassword”必须是您在第2章中为用户repl选择的密码。
MASTER_LOG_FILE和MASTER_LOG_POS必须是我们在最后一步中写下的值。

现在检查：

show slave status\G

在MySQL shell上，如果没有错误。

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 192.168.1.102
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 107
 Relay_Log_File: mysqld-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: No
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB: mysql
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 107
 Relay_Log_Space: 107
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 0
1 row in set (0.00 sec)

并启动Minion。

start slave;

再次检查从站状态：

show slave status\G

以下两行应显示“是”：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

之后你可以离开MySQL shell：

quit

如果没有出错，MySQL master-master复制现在应该正常工作。如果没有，请检查/ var / log / syslog在server1和server2上的MySQL错误。

5测试复制

现在是时候测试我们的复制设置了。我将在server1上创建数据库exampledb1，然后在server2上检查数据库是否已复制到第二个服务器：

server1：

登录到server1上的MySQL控制台并创建数据库：

mysql --defaults-file=/etc/mysql/debian.cnf

CREATE DATABASE exampledb1;

server2

现在登录到server2上的MySQL控制台，并检查现在是否存在exampledb1：

mysql --defaults-file=/etc/mysql/debian.cnf

show databases;

我们可以看到，新的数据库也显示在server2上。

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| exampledb1 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

接下来我将测试复制是否在另一方面工作。我们仍然在server2上登录并创建一个数据库exampledb2：

CREATE DATABASE exampledb2;

现在回到server1并在MySQL控制台中运行“show databases”

server1

show databases;

结果显示了我们的新数据库exampledb2，因此复制在两个方向都有效。

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| exampledb1 |
| exampledb2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)