MySQL5.5各架构复制

原创

yooke 2012-08-09 13:20:40 博主文章分类：服务搭建 ©著作权

文章标签 mysql主从复制 mysql半同步 mysql复制过滤 msql ssl复制 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者yooke的原创作品，请联系作者获取转载授权，否则将追究法律责任

本文档将要介绍的内容：

1、mysql的主从复制的配置

2、配置半同步

3、基于SSL的复制

4、复制过滤

5、主主模型

一、系统环境

master: 192.168.56.101

slave: 192.168.56.102

基于MySQL-5.5.24

实现主服务器和从服务器之间的数据复制，关于mysql的安装这里不在重复叙述，如有需要可参考其他文档，这里假设两个节点的服务器已经安装完成并能够正常启动。

二、配置的mysql主从复制

1、配置主服务器

1.1、编辑MySQL主配置文件

[root@master ~]# vim /etc/my.cnf //编辑配置文件确保有以下两行

log-bin=mysql-bin //定义开启二进制日志

server-id = 1 //定义server-id，主从服务器的id不能一样

1.2、建立具有复制权限的用户

[root@master ~]# /usr/local/mysql/bin/mysql -uroot -p

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO cpmysql@'192.168.56.102' IDENTIFIED BY 'cpmysql'; //创建cpmysql用户并设置密码为cpmysql

mysql> FLUSH PRIVILEGES; //刷新授权表

查看当前使用的二进制文件和Posotions数值，后边在从服务器上要设置这两个值

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 358 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.05 sec)

2、配置从服务器

2.1、编辑MySQL主配置文件

[root@slave ~]# vim /etc/my.cnf

#log-bin=mysql-bin //可选，关闭二进制日志文件

server-id = 11 //定义server-id不能和主服务器相同

relay-log=mysql-relay //开启中继日志且名称为mysql-relay

[root@slave ~]# service mysqld restart //重新启动mysql以使新配置生效

[root@slave ~]# /usr/local/mysql/bin/mysql -u root -p //以root用户登录mysql

确认relay_log已开启：

mysql> SHOW GLOBAL VARIABLES LIKE 'relay_log';

+---------------+-------------+

| Variable_name | Value |

+---------------+-------------+

| relay_log | mysql-relay |

+---------------+-------------+

1 row in set (0.00 sec)

确认server_id配置生效其直不能与主服务器相同

mysql> SHOW GLOBAL VARIABLES LIKE 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 11 |

+---------------+-------+

1 row in set (0.00 sec)

2.2、配置主服务器相关参数

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.101',MASTER_USER='cpmysql',MASTER_PASSWORD='cpmysql',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=358;

说明：MASTER_LOG_FILE和MASTER_LOG_POS参数的值要和上边在主服务器上查询到的值一致。

启动从服务器进程

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

查询从服务器状态，能看到Slave_IO_Running和Slave_SQL_Running两个进程运行状态为Yes

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.101

Master_User: cpmysql

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 358

Relay_Log_File: mysql-relay.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

...

如果出现其他异常情况造成不能启动，可查看mysql的错误日志，里边有详细的错误信息，例如这里为/mysqldata/slave.err

3、测试主从复制配置是否成功

在主服务器上创建一个数据库testdb

[root@master ~]# /usr/local/mysql/bin/mysql -u root -p

mysql> CREATE DATABASE testdb;

Query OK, 1 row affected (0.01 sec)

在从服务器上查看是否已经成功复制过来

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| testdb |

+--------------------+

5 rows in set (0.05 sec)

可以看到已经成功复制。

4、一些建议配置

4.1、在主服务器上开启随时同步二进制日志至文件中，默认主服务器生成的二进制日志先放在缓存中，而从服务器需要靠主服务器上生成的二进制文件来保持和主服务器的一致，所以应该让主服务器产生二进制文件后立即同步至二进制日志文件中。

修改MySQL的主配置文件my.cnf添加如下行：

[root@master ~]# vim /etc/my.cnf

sync_binlog=1

[root@master ~]# service mysqld restart //重启mysql服务使配置生效

在主服务器上查询参数是否生效

mysql> SHOW GLOBAL VARIABLES LIKE 'sync_binlog';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sync_binlog | 1 |

+---------------+-------+

1 row in set (0.00 sec)

4.2、适用于innodb引擎，使没一次产生的事务日志同步到磁盘之后才返回成功，这样可以有助于保证事务日志的安全性，避免因为特殊原因造成事务日志的丢失，但这样也会降低在执行大量需要记录事务日志操作时的执行速度。

修改Mysql的主配置文件my.cnf添加如下行：

[root@master ~]# vim /etc/my.cnf

innodb_flush_logs_at_trx_commit=1

4.3、设置从服务器为只读，避免误操作造成主从服务器的数据不一致

[root@slave ~]# vim /etc/my.cnf

read_only=1

5、如果主服务器运行了一段时间，从服务器为新增的服务器时就不能根据以上方法来配置了，需要现在主服务器上备份：

先在主服务器上给mysql施加读锁

mysql> FLUSH TABLES WITH READ LOCK；

Query OK, 0 rows affected (0.00 sec)

通过逻辑卷创建快照备份mysql,这里假设mysql的数据目录的挂载的设备为/dev/mysqlvg/mysqllv

[root@master ~]# lvcreate -L 50M -s -p r -n mysql-snap /dev/mysqlvg/mysqllv

在逻辑卷上创建好快照之后查看当前mysqld的二进制文件及Position的数值，完成后立即释放之前施加的读锁

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 478 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

释放读锁：

mysql> UNLOCK TABLES；

Query OK, 0 rows affected (0.00 sec)

挂载刚创建的逻辑卷快照并备份至从服务器

[root@master ~]# mount /dev/mysqlvg/mysql-snap /mnt

[root@master ~]# tar cf allmysql.tar /mnt/ //如果数据文件过大就压缩

拷贝该归档文件至从服务器，把内容覆盖至从服务器mysql的数据目录，并移除逻辑卷快照

[root@master ~]# umount /mnt

[root@master ~]# lvremove /dev/mysqlvg/mysql-snap

其他步骤与上边的从服务器配置相同，参考-->2.2、配置主服务器相关参数，注意替换：MASTER_LOG_FILE和MASTER_LOG_POS参数的值为释放读锁时查看到的信息。

至此就可以实现主从复制了。

三、配置半同步复制

1、在主服务器端配置步骤

安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.08 sec)

开启插件

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;

Query OK, 0 rows affected (0.04 sec)

设置超时时间为1000ms

mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;

Query OK, 0 rows affected (0.00 sec)

验证是否开启成功

mysql> SHOW GLOBAL STATUS LIKE 'rpl%status';

+-----------------------------+-------------+

| Variable_name | Value |

+-----------------------------+-------------+

| Rpl_semi_sync_master_status | ON |

| Rpl_status | AUTH_MASTER |

+-----------------------------+-------------+

2 rows in set (0.00 sec)

如果想要永久生效需要在主配置文件my.cnf中添加如下行：

[mysqld] //在该选项中添加如下两行

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

2、在从服务器端配置步骤

安装插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.11 sec)

启用插件

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

Query OK, 0 rows affected (0.04 sec)

停止从服务进程

mysql> STOP SLAVE;

Query OK, 0 rows affected (0.00 sec)

启动从服务进程

mysql> START SLAVE;

Query OK, 0 rows affected (0.01 sec)

验证是否开启，如果状态为ON即成功开启

mysql> SHOW GLOBAL STATUS LIKE 'rpl%';

+----------------------------+-------------+

| Variable_name | Value |

+----------------------------+-------------+

| Rpl_semi_sync_slave_status | ON |

| Rpl_status | AUTH_MASTER |

+----------------------------+-------------+

2 rows in set (0.02 sec)

如果想要永久生效需在从服务器主配置文件my.cnf中添加如下行

[mysqld] //在该选项中添加如下行

rpl_semi_sync_slave_enabled=1

四、复制过滤

设置复制过滤可以让从服务器只复制主服务器上的某个数据库或者某个数据库中的某个表等极大的提高了复制的灵活性，复制过滤可以在主服务器上设置，也可以在从服务器上设置，但是不推荐在主服务器上设置，因为这样设置后造成任何与该设置了复制的数据库无关的写操作都不会被记录到二进制文件中，会对主服务器上其他数据库安全性造成极大影响，但是这样设置也减少了主从复制的内容大小，所以要根据具体的环境来考虑用何种设置。

在主服务器上设置：

在主配置文件my.cnf配置文件中添加如下行，根据需要选择：

[mysqld] //在该选项中添加

binlog-do-db=testdb //设置二进制日志文件只记录与testdb相关，如果需要复制多个可以继续添加如下：

binlog-do-db=test2db //添加test2db数据库

或者

binlog-ignore-db=testdb //设置除了testdb之外的所有，与do-db相反

在从服务器上设置：

修改从服务器的主配置文件my.conf中添加如下内容，根据需要选择：

[mysqld] //在该选项中添加

replicate-do-db=testdb //只复制testdb数据库

replicate_ignore_db=test2db //只忽略test2db数据库

replicate_do_table=testdb.tb1 //只复制testdb数据库中的tb1表

replicate_ignore_table=testdb.tb1 //只忽略testdb数据库中的tb1表

replicate_wild_do_table=testdb.tb% //只复制testdb数据库中tb开头的表

replicate_wild_ignore_table=testdb.tb% //只忽略testdb数据库中tb开头的表

五、开启SSL复制

1、创建CA及自签证书，主从服务器的创建私钥申请证书和CA颁发证书的具体过程之前的文档已经有详细说明这里不再给出，详情请看《创建CA颁发证书》

这里假设CA的证书存放目录为：/usr/local/mysql/ssl/cacert.pem

主服务器相关证书和密钥：/usr/local/mysql/ssl/master.crt

/usr/local/mysql/ssl/master.key

从服务器相关证书和密钥：/usr/local/mysql/ssl/slave.crt

/usr/local/mysql/ssl/slave.key

2、编辑主服务器配置文件my.cnf添加下行：

[mysqld]

ssl

ssl-ca=/usr/local/mysql/ssl/cacert.pem

ssl-cert=/usr/local/mysql/ssl/master.crt

ssl-key=/usr/local/mysql/ssl/master.key

[root@master ssl]# service mysqld restart //重启服务至配置生效

验证配置是否生效：

mysql> SHOW VARIABLES LIKE '%ssl%';

+---------------+---------------------------------+

| Variable_name | Value |

+---------------+---------------------------------+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /usr/local/mysql/ssl/cacert.pem |

| ssl_capath | |

| ssl_cert | /usr/local/mysql/ssl/master.crt |

| ssl_cipher | |

| ssl_key | /usr/local/mysql/ssl/master.key |

+---------------+---------------------------------+

7 rows in set (0.00 sec)

可以看到have_ssl和have_openssl的状态由原来的DISABLE转化为YES

3、从服务器配置

从服务器不建议在配置文件中配置

停止从服务进程

mysql> STOP SLAVE;

Query OK, 0 rows affected (0.01 sec)

配置ssl相关参数

mysql> CHANGE MASTER TO MASTER_SSL=1,MASTER_SSL_CA='/usr/local/mysql/ssl/cacert.pem',MASTER_SSL_CERT='/usr/local/mysql/ssl/slave.crt',MASTER_SSL_KEY='/usr/local/mysql/ssl/slave.key';

Query OK, 0 rows affected (0.01 sec)

启动从服务进程

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

验证配置是否生效

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.101

Master_User: cpmysql

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000014

Read_Master_Log_Pos: 107

Relay_Log_File: mysql-relay.000005

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000014

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

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: 551

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: Yes

Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem

Master_SSL_CA_Path:

Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt

Master_SSL_Cipher:

Master_SSL_Key: /usr/local/mysql/ssl/slave.key

...

确保:Slave_IO_Running、Slave_SQL_Running和Master_SSL_Allowed三个的值为Yes

4、验证ssl

在从服务器上使用之前创建的复制用户cpmysql测试能否通过ssl登录主服务器

[root@slave ~]# /usr/local/mysql/bin/mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key -u cpmysql --host 192.168.56.101 -p

mysql> \s;

--------------

/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.5.24, for linux2.6 (i686) using readline 5.1

Connection id: 10

Current database:

Current user: cpmysql@192.168.56.102

SSL: Cipher in use is DHE-RSA-AES256-SHA

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.5.24-log MySQL Community Server (GPL)

Protocol version: 10

Connection: 192.168.56.101 via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

...

可以看到已经使用了SSL加密。

5、修改cpmysql用户使之强制必须使用ssl连接

在主服务器执行，添加REQUIRE SSL选项：

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO cpmysql@'192.168.56.102' IDENTIFIED BY 'cpmysql' REQUIRE SSL;

Query OK, 0 rows affected (0.03 sec)

刷新授权表

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

在从服务器上登录测试:

[root@slave ~]# /usr/local/mysql/bin/mysql -u cpmysql -p --host 192.168.56.101

Enter password:

ERROR 1045 (28000): Access denied for user 'cpmysql'@'192.168.56.102' (using password: YES)

从上边提示可以看到不指定ssl信息时连接被拒绝

使用ssl登录

[root@slave ~]# /usr/local/mysql/bin/mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key -u cpmysql -p -h 192.168.56.101

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 18

Server version: 5.5.24-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

登录成功！

六、主主模型的配置

主主模型既是互为主从关系，因此双方都需要在各自的服务器上开启二进制日志和中继日志，且都需要创建能够用来让对方复制数据的用户，由于主主模型本身存在很多问题且并不常用，配置也很简单这里只大概说明下配置过程：