MySQL数据库多服务器实时同步

  • 时间：2013/08/29 19:24
  • 分类：MySQL

严格说来，MySQL的数据库同步不能叫“同步”，因为它是单向的，只能从主服务器（master）到从服务器（slave），官方文档称之为“replication（复制）”。

通过同步，可以自动备份数据库到另一台机器；可以负载均衡，把写操作放在主服务器，读操作分散在从服务器；还可以在主服务器故障时，临时进行切换，故障排除后再切回来……当然，本文涉及到的，只是如何设置同步。

1 系统环境
1.1 主服务器
Centos6，MySQL 5.1.57。 MySQL使用MyISAM存储引擎，关闭InnoDB存储引擎。
若用到InnoDB的数据库，其同步设置会有差异，本文不涉及。

mysql -u root -p

登入数据库，然后执行

SHOW TABLE STATUS FROM database;

查看database数据库使用何种存储引擎。

1.2 从服务器
Centos6，MySQL 5.1.57。 MySQL使用MyISAM存储引擎，关闭InnoDB存储引擎。
2 准备工作
2.1 主服务器设置
编辑/etc/my.cnf，在[mysqld]部分，根据下面内容添加或修改相应选项。

default-storage-engine = MyISAM
innodb = OFF
#bind-address = 127.0.0.1
skip-name-resolve
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = MIXED
expire_logs_days = 10
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

default-storage-engine = MyISAM
innodb = OFF
第一行，设置MyISAM为默认的存储引擎；
第二行，关闭InnoDB存储引擎。

#bind-address = 127.0.0.1

这一行要删除或注释掉，从服务器才能远程连接。

skip-name-resolve

当检查客户端连接时，不解析主机名。MySQL默认会根据接入连接的IP进行逆域名解析，然后再对逆域名解析获取的域名进行解析，如果此时获取的IP和接入连接的IP不符，会拒绝连接。

server-id = 1

用于同步时的服务器识别，主服务器和从服务器都必须设置此选项，且所有参与同步的服务器其数值不能相同。主服务器设置为1即可。

log_bin = /var/log/mysql/mysql-bin.log

启用二进制日志。MySQL的同步就是通过二进制日志实现的，二进制日志记录了对数据库的所有操作，从服务器根据二进制日志的记录，重现操作，即可保持数据库同步。等号后面是二进制日志的“基名”。所有二进制日志以此为基，后添加一个数字序号进行区别。此处为Debian默认，保持不变即可。

binlog_format = MIXED

二进制日志的格式，其决定了数据库同步方式，有三个选项：ROW、STATEMENT、MIXED。ROW和STATEMENT各有其优缺点，MIXED混合使用两者，在特定情况下自动切换。对大多数用户来说，MIXED是数据完整性和性能的最优组合。

expire_logs_days = 10

设置日志存放时长为10天

ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

在主服务器启用SSL传输。从上往下依次是SSL CA证书文件名，服务端SSL证书文件名，服务端SSL私钥文件名。迟些时候会另有文章说明如何生成这些证书文件。

2.2 从服务器设置

编辑/etc/my.cnf，在[mysqld]部分，根据下面内容添加或修改相应选项。

default-storage-engine = MyISAM
innodb = OFF
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = MIXED
relay-log = mysqld-relay-bin
slave-load-tmpdir = /var/spool/mysql
replicate-wild-do-table = typecho1.%
replicate-wild-do-table = typecho2.%
replicate-wild-do-table = typecho3.%
skip-slave-start

server-id = 2

需要再强调一次。这个值不能和主服务器相同，否则无法同步。

log_bin = /var/log/mysql/mysql-bin.log

从服务器其实可以不用启用二进制日志，但启用没坏处。如果主服务器出现问题需要修理，当修理好后可以把从服务器作为主服务器，给原主服务器同步数据。

relay-log = mysqld-relay-bin

设置从服务器用于同步的中继日志的基名为mysqld-relay-bin。由于MySQL默认中继日志的基名与服务器主机名有关，这样设置可以避免更改主机名可能产生的错误。

slave-load-tmpdir = /var/spool/mysql

从服务器的SQL线程同步LOAD DATA INFILE语句时，会把文件释放在临时目录，然后再加载到数据库表格中。这一选项用来指定此临时目录的位置，这一目录必须位于硬盘之上，且其中的文件不能由于机器重启而消失，否则同步会出现问题。上面的目录系统中没有，是我自己创建的。其所有者和用户组都是mysql，权限是700。

replicate-wild-do-table = typecho.%

指定需要从主服务器上同步的是哪个数据库。如果需要同步多个数据库，需要提供多个设置项。

skip-slave-start

告诉从服务器，启动时不进行同步。一切设置好，并测试没有问题后，再把此选项去掉。

3 同步设置
3.1 主服务器

记当前终端为t1。登录mysql服务器。输入：

mysql -u root -p

创建用于同步的专用账户，并授予相应权限，斜体部分需要自行输入。

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'hostname' IDENTIFIED BY "password" REQUIRE SSL;

repl

用于同步的用户名。

hostname

连接此mysql服务器的客户端主机IP地址或域名，可使用通配符%。例如192.168.%.%表示允许192.168.0.0/16这整个网段所有IP以repl用户身份连接此服务器，其他IP段拒绝连接。域名与此类似，本文不涉及。

password

用户repl@hostname的密码。

REQUIRE SSL

此用户必须通过SSL才能连接。

刷新所有表并阻止其他写入：

FLUSH TABLES WITH READ LOCK;

不要退出执行 FLUSH TABLES 语句的客户端，以保持读锁有效(如果退出了，读锁就释放了)。

开启另一个终端（记为t2），登录mysql服务器，获取当前二进制日志的文件名及偏移位置：

SHOW MASTER STATUS;

屏幕上会显示类似下面的内容：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

记下File栏的内容mysql-bin.000014，此为当前二进制日志文件名；记下Position，此为日志偏移位置。设置从服务器要用到这些数据。

现退出mysql客户端，备份数据库。斜体字部分为需要同步的数据库名，如果要同步多个数据库，在此一一列出其名称，用空格隔开即可。

exit
mysqldump -u root -p --databases wordpress1 wordpress2 --lock-all-tables >dbdump.db

如果提示mysqldump命令不存在，可以执行下面的命令为mysqldump建立软链接:

ln -fs /usr/local/mysql/bin/mysqldump /usr/local/bin

然后再执行上面的命令。

现在我们返回t1终端，释放读锁并退出mysql客户端：

UNLOCK TABLES;
exit

3.2 从服务器

导入主服务器上的数据库备份：

mysql -u root -p < dbdump.db

使用CHANGE MASTER TO设置连接主服务器的相关参数：

mysql> CHANGE MASTER TO
MASTER_HOST='60.191.13.26',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=107,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/mysql/cacert.pem',
MASTER_SSL_CAPATH = '/etc/mysql/',
MASTER_SSL_CERT = '/etc/mysql/client-cert.pem',
MASTER_SSL_KEY = '/etc/mysql/client-key.pem';

MASTER_HOST=’60.191.13.26’

主服务器域名或IP地址。

MASTER_USER=’repl’
MASTER_PASSWORD=’password’

主服务器上用于同步的用户的用户名和密码。

MASTER_LOG_FILE=’mysql-bin.000008′
MASTER_LOG_POS=107

3.1节中记录的主服务器二进制日志名称及偏移地址。

MASTER_SSL=1

设置要通过SSL连接主服务器。

MASTER_SSL_CA = ‘/etc/mysql/cacert.pem’
MASTER_SSL_CAPATH = ‘/etc/mysql/’
MASTER_SSL_CERT = ‘/etc/mysql/client-cert.pem’
MASTER_SSL_KEY = ‘/etc/mysql/client-key.pem’

从上往下依次是SSL CA证书文件名，SSL CA证书所在目录，客户端SSL证书文件名，客户端SSL证书私钥文件名。

注意：使用CHANGE MASTER TO语句后，会在/var/lib/mysql目录下生成master.info文件，此文件优先级高于/etc/mysql/my.cnf，今后从服务器会从master.info读取连接主服务器的信息。因此，如果连接信息哪里设置不正确，建议使用CHANGE MASTER TO语句修改。

设置完成后，运行START SLAVE让从服务器开始同步：

START SLAVE;

使用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 | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+---------------------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 60.191.13.26 | repl        |        3306 |            60 | mysql-bin.000008 |              422070 | mysqld-relay-bin.000008 |        212508 | mysql-bin.000008      | Yes              | Yes               |                 |                     |                    |                        | typecho_1.%,typecho_2.%,typecho_3.% |                             |          0 |            |            0 |              422070 |          212664 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+---------------------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
1 row in set (0.00 sec)

确认同步正常后，修改/etc/my.cnf，把 skip-slave-start 那一行注释或删除掉。如果不正常，可执行

STOP SLAVE;

终止同步线程，然后检查并重新设置相关选项。

转载自菜包子博客 原文链接:http://caibaoz.com/blog/2013/08/13/mysql_replication_howto/

标签：数据库, mysql, 服务器, 同步

添加新评论