设置MySQL数据库同步

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

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

1 系统环境

1.1 主服务器

Debian7,MySQL5.5。MySQL使用MyISAM存储引擎,关闭InnoDB存储引擎。

若用到InnoDB的数据库,其同步设置会有差异,本文不涉及。mysql -u root -p登入数据库,然后执行SHOW TABLE STATUS FROM database查看database数据库使用何种存储引擎。

1.2 从服务器

Debian7,MySQL5.5。MySQL使用MyISAM存储引擎,关闭InnoDB存储引擎。

2 准备工作

2.1 主服务器设置

编辑/etc/mysql/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
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存储引擎。WordPress的数据库使用MyISAM存储引擎,所以我没必要让一个用不到的存储引擎占用系统资源。
#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是数据完整性和性能的最优组合。
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/mysql/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 = wordpress.%
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 = wordpress.%
指定需要从主服务器上同步的是哪个数据库。如果需要同步多个数据库,需要提供多个设置项。
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才能连接。

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

mysql> FLUSH TABLES WITH READ LOCK;

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

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

mysql> SHOW MASTER STATUS;

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

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

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

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

mysql> exit
$ mysqldump -u root -p --databases wordpress --lock-all-tables >dbdump.db

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

mysql> UNLOCK TABLES;
mysql> exit

3.2 从服务器

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

$ mysql -u root -p < dbdump.db

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

mysql> CHANGE MASTER TO
MASTER_HOST='caibaoz.com',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000014',
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='caibaoz.com'
主服务器域名或IP地址。
MASTER_USER='repl'
MASTER_PASSWORD='password'
主服务器上用于同步的用户的用户名和密码。
MASTER_LOG_FILE='mysql-bin.000014'
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让从服务器开始同步:

mysql> START SLAVE;

使用SHOW SLAVE STATUS检查同步状态,如果一切正常,其显示类似下面的样子:

mysql> SHOW SLAVE STATE\G
*************************** 1. row ***************************
               Slave_IO_State: Checking master version
                  Master_Host: caibaoz.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 4601433
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: wordpress.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4601433
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/cacert.pem
           Master_SSL_CA_Path: /etc/mysql/
              Master_SSL_Cert: /etc/mysql/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/client-key.pem
        Seconds_Behind_Master: 0
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)

确认同步正常后,修改/etc/mysql/my.cnf,把skip-slave-start那一行注释或删除掉。如果不正常,可执行STOP SLAVE终止同步线程,然后检查并重新设置相关选项。

4 参考文档

4.1 MySQL 5.5 Reference Manual

4.2 MySQL同步

点击量:82

发表评论

电子邮件地址不会被公开。 必填项已用*标注

5 × 4 =