使用MySQL 5在Debian Etch上设置主 - 主复制

使用MySQL 5在Debian Etch上设置主 - 主复制 从版本5开始,MySQL自带了对master-master的内置支持 复制,...

使用MySQL 5在Debian Etch上设置Master-Master复制

版本1.0
作者:Falko Timme

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

我不会保证这将为您工作!

1初步说明

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

2安装MySQL 5.0

如果尚未在server1server2上安装MySQL 5.0,请立即安装:

server1 / server2:

apt-get install mysql-server-5.0 mysql-client-5.0

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

server1 / server2:

vi /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:

/etc/init.d/mysql restart

然后检查

server1 / server2:

netstat -tap | grep mysql

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

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

然后,为用户root @ localhost设置一个MySQL密码:

server1 / server2:

mysqladmin -u root password yourrootsqlpassword

接下来我们为root@server1.example.com创建一个MySQL密码:

server1:

mysqladmin -h server1.example.com -u root password yourrootsqlpassword

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

server1:

mysql -u root -p

在MySQL shell上,运行以下命令:

server1:

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

现在我们在server2上再次执行最后两步:

server2:

mysqladmin -h server2.example.com -u root password yourrootsqlpassword

mysql -u root -p

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

3一些注释

在下面我将假定数据库exampledb 已经存在server1上 ,并且它中包含有记录的表。 我们将把exampledb 复制server2 ,之后我们将exampledbserver2复制到server1

在开始设置复制之前,我们在server2上创建一个数据库exampledb

server2:

mysql -u root -p

CREATE DATABASE exampledb;
quit;

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:

vi /etc/mysql/my.cnf

搜索以[mysqld]开头的部分,并在其中放入以下选项(注释掉所有现有的冲突选项):

[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = 192.168.0.101
master-user = slave1_user
master-password = slave1_password
master-connect-retry = 60
replicate-do-db = exampledb

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
[...]

然后重启MySQL:

server1:

/etc/init.d/mysql restart

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

server2:

vi /etc/mysql/my.cnf

[...]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = 192.168.0.100
master-user = slave2_user
master-password = slave2_password
master-connect-retry = 60
replicate-do-db = exampledb

log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
[...]

server2:

/etc/init.d/mysql restart

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

server1:

mysql -u root -p

在MySQL shell上,运行以下命令:

server1:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

最后一个命令应该是这样的(请写下来,稍后我们需要)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |       98 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

现在不要离开MySQL shell,因为如果你离开它,数据库锁将被删除,这不是我们现在想要的,因为我们现在必须创建一个数据库转储。 当MySQL shell仍然打开时,我们打开一个第二个命令行窗口,我们创建SQL转储snapshot.sql并将其传输到server2 (使用scp):

server1:

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

之后,可以关闭第二个命令行窗口。 在第一个命令行窗口中,我们现在可以解锁数据库并离开MySQL shell:

server1:

UNLOCK TABLES;
quit;

server2上 ,我们现在可以导入SQL dump snapshot.sql,如下所示:

server2:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

之后,我们还要了解server2的主站状态,并写下来:

server2:

mysql -u root -p

USE exampledb;
FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      783 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

然后解锁表:

server2:

UNLOCK TABLES;

并运行以下命令使server2成为server1的从站( 重要的是您将以下命令中的值替换为从我们在server1上运行的SHOW MASTER STATUS;命令获得的值 ):

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='slave2_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=98;

最后启动Minion:

server2:

START SLAVE;

然后检查从站状态:

server2:

SHOW SLAVE STATUS;

重要的是, Slave_IO_RunningSlave_SQL_Running在输出中都具有值Yes (否则出现问题,您应该再次检查您的设置,并查看/ var / log / syslog以了解任何错误):

mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.100 | slave2_user |        3306 |            60 | mysql-bin.000009 |                  98 | slave-relay.000002 |           235 | mysql-bin.000009      | Yes              | Yes               | exampledb       |                     |                    |                        |                         |                             |          0 |            |            0 |                  98 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql>

之后,您可以将MySQL shell放在server2上

server2:

quit

现在,从server1server2的复制已经建立起来了。 接下来,我们必须配置从server2server1的复制。

为此,我们在server1上停止从站,并使其成为server2的从站:

server1:

mysql -u root -p

STOP SLAVE;

确保使用SHOW MASTER STATUS的值; 在以下命令中在server2上运行的命令:

server1:

CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=783;

然后在server1上启动从机:

server1:

START SLAVE;

然后检查从站状态:

server1:

SHOW SLAVE STATUS;

重要的是, Slave_IO_RunningSlave_SQL_Running在输出中都具有值Yes (否则出现问题,您应该再次检查您的设置,并查看/ var / log / syslog以了解任何错误):

mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.101 | slave1_user |        3306 |            60 | mysql-bin.000009 |                 783 | slave-relay.000002 |           235 | mysql-bin.000009      | Yes              | Yes               | exampledb       |                     |                    |                        |                         |                             |          0 |            |            0 |                 783 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql>

之后你可以离开MySQL shell:

quit

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