Mysql Replication实现mysql主从库自动同步安装指南

在MySQL 3.23.15版本之后,MySQL提供了数据库复制的功能,可以实现两个数据库实时同步,增强了MySQ数据库的稳定性，而且可以在企业级应用的数据库层实现Cluster，不仅大大提高了mysql的安全性，同时还减轻了DBA大量的工作。下面mysql5.0.27版本为例，介绍Mysql Replication的安装配置全过程跟基本的错误处理。以下只是示例，具体细节请根据你的要求自行更改。

1.             安装环境

      操作系统：Redhat AS 4

      Mysql版本：5.0.27

      Mysql安装包：

    MySQL-server-standard-5.0.27-0.rhel4.i386.rpm

    MySQL-client-standard-5.0.27-0.rhel4.i386.rpm

    MySQL-devel-standard-5.0.27-0.rhel4.i386.rpm

    MySQL-shared-standard-5.0.27-0.rhel4.i386.rpm

可以只安装MySQL-server-standard-5.0.27-0.rhel4.i386.rpm

MySQL-client-standard-5.0.27-0.rhel4.i386.rpm。

但考虑到调试和开发方便，建议上述几个包都安装上。

注意安装perl-DBI-1.40-5.i386.rpm包。

2.             安装及配置

      下面以192.168.1.23和192.168.1.24两台数据库服务器的test库为例子，实现如下的目标：

Ø         两台数据库的双向同步复制。

Ø         在master与slave网络不通但过后再次恢复正常，master上的数据在slave上也可以得到更新，反之亦然.

在双向同步的情况下，两台数据库互为master和slave。系统初始化情况下，以192.168.1.23为master，192.168.1.24为slave。

2.1.     修改Selinux安全级别：

     

Kernel 2.6后引入了Security-Enhanced Linux （Selinux）以增强系统安全性。

      分别在两台数据库服务器上执行如下的操作：

          修改/etc/rc.d/init.d/mysql，在46行左右加入如下内容：

#test add to change selinux level

setenforce 0

 

如果只针对一次操作生效，可以用/usr/bin/setenforce 修改SELinux的实时运行模式。

           setenforce 0

           setenforce [ Enforcing | Permissive | 1 | 0 ]

获取当前selinux的状态

     

如果要彻底禁用SELinux 需要在/etc/sysconfig/selinux中设置参数selinux= disabled。

#     enforcing - SELinux security policy is enforced.

#     permissive - SELinux prints warnings instead of enforcing.

#     disabled - SELinux is fully disabled.

                               摘自/etc/sysconfig/selinux

修改完后，为了保证配置生效，请重起服务器。

 

2.2.     Mysql安装

1)       卸载原有的mysql：rpm -qa|grep -i mysql |xargs rpm –e

2)       删除/var/lib/mysql：rm –rf  /var/lib/mysql

3)       在两台数据库服务器上分别安装mysql。
    rpm –Uvh MySQL-server-standard-5.0.24-0.rhel4.i386.rpm
    rpm –Uvh MySQL-client-standard-5.0.24-0.rhel4.i386.rpm

    rpm –Uvh MySQL-devel-standard-5.0.24-0.rhel4.i386.rpm

    rpm –Uvh MySQL-shared-standard-5.0.24-0.rhel4.i386.rpm

      注意安装完MySQL-server-standard-5.0.24-0.rhel4.i386.rpm后，系统会自动执行启动mysql server的操作，此时可能不能正常启动。因为selinux限制其启动安全权限。

2.3.     配置mysql

1)     执行ls –al /etc/my.cnf参看是否存在my.cnf，如果不存在，执行步骤2)。否则执行步骤3)

2)       执行rpm -qa|grep -i mysql-server |xargs rpm -ql|grep my-medium.cnf得到my-medium.cnf路径，缺省为/usr/share/mysql/my-medium.cnf执行操作：

ln -s /usr/share/mysql/my-medium.cnf  /etc/my.cnf

3)       在192.168.1.23上修改/etc/my.cnf

a)       在[mysqld]中修改，以下配置该机为master

binlog-do-db=test

auto_increment_increment=4

auto_increment_offset= 1

log-bin=/var/lib/mysql/master-bin.log

log-bin-index = /var/lib/mysql/master-log-bin.index

log-error = /var/lib/mysql/master-error.log

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

relay-log-info-file = /var/lib/mysql/slave-relay-log.info

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

set-variable=max_connections=500

解释:

Ø         server-id=1表示是本机的序号为1,一般来讲就是master的意思.

Ø         binlog-do-db=test表示需要备份的数据库是test这个数据库,

Ø         replicate-do-db=test 表示同步test数据库;

Ø         如果需要备份多个数据库，那么应该写多行,如下所示:

binlog-do-db=test1

binlog-do-db=test2

binlog-do-db=test3

Ø         log-bin表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提;

Ø         auto_increment_increment定义下一次AUTO_INCREMENT的步长

Ø         auto_increment_offset= 1定义AUTO_INCREMENT的起点值

这两个参数用于避免多master情况下，多个master同时存取字段类型为AUTO_INCREMENT的冲突。其它几个字段为日志文件配置信息

Ø         set-variable=max_connections=500 设定数据库最大连接数为500

 

b)       其次仍然在该区域修改, 配置为以该机为slave

master-host=192.168.1.24

master-user=testuser

master-password=password

master-port=3306

master-connect-retry=60     

replicate-do-db=test

log-slave-updates

 

说明：

Ø         master-host=192.168.1.24 表示本机做slave时的master为192.168.1.24;

Ø         master-user=testuser 这里表示master（192.168.1.24）上开放的一个有权限的用户,使其可以从slave连接到master并进行复制; 建议两台主机的授权用户和密码完全相同。

Ø         master-password=password 表示授权用户的密码;

Ø         master-port=3306 表示master（192.168.1.24）上MySQL服务Listen3306端口;

Ø         master-connect-retry=60 同步间隔时间;

Ø         log-slave-updates：从master读到的更新操作都记录到slave二进制日志中

 

 

4)     配置192.168.1.24上的my.cnf

在/etc/my.cnf下在[mysqld]中修改:

server-id=2

binlog-do-db=test

auto_increment_increment=4

auto_increment_offset= 2

log-bin=/var/lib/mysql/master-bin.log

log-bin-index = /var/lib/mysql/master-log-bin.index

log-error = /var/lib/mysql/master-error.log

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

relay-log-info-file = /var/lib/mysql/slave-relay-log.info

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

set-variable=max_connections=500

 

master-host=192.168.1.23

master-user=testuser

master-password=password

master-port=3306           

master-connect-retry=60  

replicate-do-db=test

log-slave-updates

 

解释:

Ø         server-id=2表示本机器的序号;

Ø         master-host=192.168.1.23 表示本机做slave时的master为192.168.1.23;

Ø         master-user=testuser 这里表示master上开放的一个有权限的用户,使其可以从slave连接到master并进行复制;

Ø         master-password=password 表示授权用户的密码;

Ø         master-port=3306 表示master上MySQL服务Listen3306端口;

Ø         master-connect-retry=60 同步间隔时间;

Ø         replicate-do-db=test 表示同步test数据库;

Ø         log-bin 打开logbin选项以能写到slave的 I/O线程; 注意有“=”号

Ø         binlog-do-db=test 表示别的机器可以同步本机的test数据库.

Ø         log-slave-updates：从master读到的更新操作都记录到slave二进制日志中

Ø         auto_increment_increment定义下一次AUTO_INCREMENT的步长

Ø         auto_increment_offset= 2定义AUTO_INCREMENT的起点值

这两个参数用于避免多master情况下，多个master同时存取字段类型为AUTO_INCREMENT的冲突。

 

5)     同步两数据库的基础库

在192.168.1.23上导入老系统的数据库数据

mysql  -uroot –p test

在192.168.1.24上导入数据库数据

mysql  -uroot –p test

           也可以采用如下方式：

a)       停止192.168.1.23和192.168.1.24的数据库：service mysql stop

b)       拷贝老系统的/var/lib/mysql数据文件打包并分别拷贝到192.168.1.23和192.168.1.24并解压缩：

cd /var/lib

tar xvf mysql-snapshot.tar

 

 

 

 

 

6)     分配权限

在192.168.1.23和192.168.1.24上使用mysql登陆,分别操作如下操作:

mysql>grant all privileges on *.* to ‘testuser’@’%’ identified by ‘password’;

mysql>flush privileges;

具体权限的设定和安全考虑，请根据现场需要调整。

建议至少赋予FILE,SELECT,REPLICATION SLAVE权限。例如：

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO  rep@rep2 IDENTIFIED BY 'rep';

在192.168.1.23和192.168.1.24上分别测试所添加用户能否正常登陆另外一台服务器。

      在192.168.1.24上：mysql –utestuser –p –h192.168.1.23

      在192.168.1.23上：mysql –utestuser –p –h192.168.1.24

如果通信有问题，请确认两台服务器上iptables的3306端口已经开通，以保证通信正常。

 

 

7)     重起mysql （使replication生效）

删除192.168.1.23和192.168.1.24的/var/lib/mysql/master.info 

和/var/lib/mysql/slave.info，然后重新启动192.168.1.23和192.168.1.24两台机器的

mysql：

rm –rf /var/lib/mysql/master. *

rm –rf /var/lib/mysql/slave. *

service mysql restart

注意：对于my.cnf进行任何修改操作后，请首先删除/var/lib/mysql/master.info和/var/lib/mysql/slave.info，然后执行重起操作。

 

8)       测试同步

a)       在master(192.168.1.23)上新建一个test数据库

mysql>create database test;

b)       新建一个表:

mysql>create table test (id int(10),name varchar(20));

mysql>insert into test(id,name) values(1,’test1’);

c)        查看192.168.1.24上的mysql;

mysql>use test;

mysql>show tables;

mysql>desc test;

mysql>select * from test;

如果看到与master相同的信息,则可以证明是成功的.

同时可以改动已有的数据库来判断是否已经达到同步，都差不多。只要证明数据库同步就可以了。这时不分master/slave,在master上改动slave上会更新,而在slave上改动，master上也可以得到更新.

9)       查看工作状态

在192.168.1.23和192.168.1.24两台主机上分别执行如下的命令：

mysql> show slave status;

mysql>show processlist;

mysql> show processlist;

3.             replication数据库维护规则

3.1.      查看数据库的replication状态

1)     Shell>mysql –h host –u user –ppassword  database

2)     Mysql> show slave status;

3)     如果Slave_IO_Running=YES，Slave_SQL_Running=YES，Last_Errno=0

那么，说明数据库已经正常启动到replication状态。

            否则，执行 3.2

          

3.2.     无法启动replication状态，该怎么办

先查看数据库的replication状态

1） 如果：Slave_IO_Running=NO，Slave_SQL_Running=YES，Last_Errno=0 

解决方法：

1、1）    mysql> slave stop ; 

1、2）    mysql> slave start;  

1、3）    mysql> show slave status ;

1、4）    如果Slave_IO_Running=YES，Slave_SQL_Running=YES，Last_Errno=0

那么，数据库的replication已经成功启动。 

否则，再执行1、2） ，1、2），1，3），1、4） 

 

2） 如果： Slave_IO_Running=YES，Slave_SQL_Running=NO，Last_Errno>0  

这个问题，基本上是因为数据库的replication的状态文件被删除引起的。 

解决方法：

2、1）      mysql> show slave status ;  

2、2）      查看Last_Error，的值（基本上是一个replication产生的SQL语句）

2、3）      如果你确定ERROR里面的SQL对数据没有太大的影响，执行下面的步骤，否则，请跟相关人员确定这个SQL对数据库的影响有多大。

2、4）      mysql> slave stop ;  

2、5）      mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 

2、6）      mysql > slave start ;  

2、7）      mysql > show slave status ;

2、8）      如果 Slave_IO_Running=YES，Slave_SQL_Running=YES，

Last_Error=0 。

那么数据的replication状态已经恢复。

否则，从2、1 ） 重新开始做。直到replication状态恢复。

 

3.3.      如果出现网络中断，或者两天数据库没法通信的情况

            只需要让两台服务器，再次保持联系就可以了，不需要任何对数据的操作。

           但是，为了保险起见，请用如下的方法观察数据库的replication情况。

          

          方法如下：

            1)  mysql> show slave status ;   

            2) 如果Slave_IO_Running=YES，Slave_SQL_Running=YES，Last_Errno=0

                那么数据库的replication恢复正常。

                 否则，执行 规则3.2 

            

3.4.     如果数据库需要重启，那该怎么做

         如果再数据库的配置没有改变的情况下，不需要对数据库进行多于的操作

         只要正常的执行：

                 Shell>service mysql restart    

       

       为了保险起见，在数据库重启后，请执行  规则3.1 

          

          

3.5.     如果，修改数据库replication用户名、密码该怎么做

如果要修改数据库的replication的用户名，密码，请执行如下操作：

1）   停止数据库

        Shell >service mysql stop  

2）   按照 2.3 提供的方法，修改 /etc/my.cnf的用户名、密码。

master-user=数据库的用户名

master-password=数据库的密码  

          注： 请确保 该用户名，密码对数据库有访问权限。

3）   修改/var/lib/mysql/master.info 的信息

方法如下：

1）   找到原先的用户名在/var/lib/mysql/master.info位置，用新用户名替换

旧的用户名。（注意：不要坏master.info的行结构，行末尾不要加空格）

2）   用上一步的方法修改密码 。

4）   启动数据库

              Shell >service mysql start

        5） 用   规则3.1 查看数据库的replication状态

 

切记：

/var/lib/mysql/master.info这个文件有replication的同步信息，不能随便删除

       

 

FAQ

1)    性能问题

（摘自）

MySQL复制能够何时和多大程度提高系统性能？

A：MySQL复制对于频繁读和频繁写的系统具有最大好处。理论上，通过使用单个主服务器/多从服务器设置，可以通过添加更多的从服务器来扩充系统，直到用完网络带宽，或者你的更新负载已经增长到主服务器不能处理的点。

在获得的收益开始吃平之前，为了确定可以有多少从服务器，以及可以将你的站点的性能提高多少，需要知道查询模式，并且要通过基准测试并根据经验确定一个典型的主服务器和从服务器中的读取（每秒钟读取量，或者max_reads）吞吐量和写（max_writes）吞吐量的关系。通过一个假设的带有复制的系统，本例给出了一个非常简单的计算结果。

假设系统负载包括10%的写和90%的读取，并且我们通过基准测试确定max_reads是1200 –2 × max_writes。换句话说，如果没有写操作，系统每秒可以进行1,200次读取操作，平均写操作是平均读操作所用时间的两倍，并且关系是线性的。我们假定主服务器和每个从服务器具有相同的性能，并且我们有一个主服务器和N个从服务器。那么，对于每个服务器（主服务器或从服务器），我们有：

reads = 1200 – 2 × writes

reads = 9 × writes / (N + 1) (读取是分离的, 但是写入所有服务器)

9 × writes / (N + 1) + 2 × writes = 1200

writes = 1200 / (2 + 9/(N+1))

最后的等式表明了N个从服务器的最大写操作数，假设最大可能的读取速率是每分钟1,200次，读操作与写操作的比率是9。

如上分析可以得到下面的结论：

·如果N = 0（这表明没有复制），系统每秒可以处理大约1200/11 = 109个写操作。

·如果N = 1，每秒得到184个写操作。

·如果N = 8，每秒得到400个写操作。

·如果N = 17，每秒得到480个写操作。

·最后，当 N 趋于无穷大（以及我们预算的负无穷大）时，可以得到非常接近每秒60个写操作，系统吞吐量增加将近5.5倍。然而，如果只用8个服务器，增加接近4倍。

请注意，这些计算假设网络带宽无穷大并忽略掉了其它一些因素，那些因素可能对系统产生重要的影响。在许多情况下，不能执行与刚才类似的计算，即如果添加N台复制从服务器，应该准确预报系统将发生哪些影响。回答下面的问题应能够帮助你确定复制是否和在多大程度上能够提高系统的性能：

·系统上的读取/写比例是什么?

·如果减少读取操作，一个服务器可以多处理多少写负载？

·网络带宽可满足多少从服务器的需求?

 

参考资料