在Debian 8(Jessie)上使用MySQL设置master-master复制

本教程描述了一个复制的MySQL设置(Mater / Master复制),有2个节点,可以在同一个节点上读取和写入数据。

本教程介绍了具有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

如果尚未在server1server2上安装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_incrementauto_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;

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

  1. IP地址必须是您的第二个MySQL服务器的IP。
  2. 密码“secretpassword”必须是您在第2章中为用户repl选择的密码。
  3. 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 / syslogserver1server2上的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)