MySQL详解（八）：MySQL Replication

港控/mmm° 2022-05-18 05:35 283阅读 0赞

**MySQL Replication**

# **1.MySQL Replication概述** #

MySQL Replication是MySQL的一个非常重要的功能，主要用于主服务器和从服务器之间的数据复制操作。

MySQL复制是指从一个MySQL主服务器（master）将数据复制到另一台或者多台MySQL从服务器（slaves）的过程，将主数据库的DDL和DML操作通过二进制日志传递到复制服务器上，然后在服务器上对这些日志重新执行，从而使得主从服务器的数据保持同步；

在MySQL中，复制操作时异步进行的，slaves服务器不需要持续地保持连接接收master服务器的数据。

MySQL支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果MySQL主服务器的访问量比较大，可以通过复制数据，然后在从服务器上进行查询操作，从而降低主服务器的访问压力，同时从服务器作为主服务器的备份，可以避免主服务器因为故障数据丢失的问题；

MySQL数据库复制操作大致可以分为三个步骤：

（1）主服务器将数据改变记录到二进制日志（binary log）中；

（2）从服务器将主服务器的binary log events 复制到它的中继日志（relay log）中；

（3）从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步；

首先，主服务器会记录二进制日志，每个事务更新数据完成之前，主服务器将这些操作的信息记录在二进制日志里面，在事件写入二进制日志完成之后，主服务器通知存储引擎提交事务。

slave上面的I/O进程连接上Master，并发出日志请求，Master接收到来自Slave的I/O进程的请求之后，通过负责复制的I/O进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的I/O进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

Slave的I/O进程接收到信息之后，将接收到的日志内容依次的添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中。

Slave的SQL进程检测到relay-log中新增加了内容之后，会马上解析relay-log的内容成为Master端真实执行时候的那些可执行的内容，并在自身执行；

MySQL复制环境90%以上都是一个Master带一个或者多个Slave的架构模式。如果Master和Slave的压力不是太大的话，异步复制的延时一般都很少。尤其是Slave端的复制方式改成进程处理之后，更是减少了Slave端的延时；

提示：对于数据实时性要求不是特别严格的应用，只需要通过廉价的电脑服务器来扩展Slave的数量，将读压力分散到多台Slave的机器上面，即可解决数据库端读压力瓶颈。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型的网站也在使用类似的方案解决数据库的瓶颈；

# **2.Windows环境下的MySQL主从复制** #

## **1.复制前的准备工作** ##

在Windows环境下，如果想要实现主从复制，需要准备操作环境。

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#ed7d31;width:79.4pt;"> 角色 </td> 
 <td style="background-color:#ed7d31;width:92.25pt;"> IP </td> 
 <td style="background-color:#ed7d31;width:83.45pt;"> 操作系统 </td> 
 <td style="background-color:#ed7d31;width:171pt;"> MySQL版本 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:79.4pt;"> Master </td> 
 <td style="vertical-align:top;width:92.25pt;"> 192.168.0.208 </td> 
 <td style="vertical-align:top;width:83.45pt;"> Windows </td> 
 <td style="vertical-align:top;width:171pt;"> mysql-install-community-5.6.10.1.msi </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:79.4pt;"> Slave </td> 
 <td style="vertical-align:top;width:92.25pt;"> 192.168.0.206 </td> 
 <td style="vertical-align:top;width:83.45pt;"> Windows </td> 
 <td style="vertical-align:top;width:171pt;"> mysql-install-community-5.6.10.1.msi </td> 
 </tr> 
 </tbody> 
</table>

## **2.Windows环境下实现主从复制** ##

准备好两台安装MySQL5.6的计算机（也可以使用VMWare虚拟机）之后，即可实现MySQL服务器主从复制备份操作；

步骤：

（１）在windows操作系统下安装好两台主机的MySQL服务器，配置好两台主机的IP地址，实现两台计算机可以网络连通；

（2）配置Master的相关配置信息，在Master主机上开启binlog日志，首先，看下datadir的具体路径；

mysql> show variables like ‘%datadir%’;

（3）此时需要打开Mysql的配置文件my.ini，添加如下代码，开启**binlog**功能：

\[mysqld\]

log\_bin = “D:/MySQLlog/binlog”

expire\_log\_days = 10

max\_binlog\_size = 100M

提示：

此时我们需要在D盘下创建MySQLlog文件夹，binlog日志记录在该文件夹里面，该配置文件中其他的参数的含义如下：

1.  expire\_logs\_days  表示二进制日志文件删除的天数；
2.  max\_binlog\_size 表示二进制日志文件最大的大小；

（4）登陆MySQL之后，可以执行如下命令来测试log\_bin是否成功开启，命令语句执行如下：

mysql > show variables like ‘%log\_bin%’;

如果log\_bin参数的值为ON的话，那么表示二进制日志文件已经成功开启；如果为OFF的话，那么表示二进制日志文件开启失败；

（5）在master上配置复制所需要的账户，这里创建一个repl的用户，%表示任何远程地址的repl用户都可以连接master主机，语句执行如下所示：

mysql > grant replication slave on \*.\* to repl@’%’ indentified by ‘123’;

mysql > flush privileges;

（6）在my.ini 配置文件中配置Master主机的相关信息，如下所示：

\[mysqld\]

log\_bin=”D:/MySQLlog/binlog”

expire\_logs\_days = 10

max\_binlog\_size = 100M

server-id =1

binlog-do-db = test

binlog-ignore-db = mysql

这里配置语句的含义如下：

1.  \-server-id 表示服务器标识id号，master和slave主机的server-id不能一样
2.  \-binlog-do-db 表示需要复制的数据库，这里以test为数据库为例
3.  \-bin-ignore-db 表示不需要复制的数据库

（7）重启Master主机的MySQL5.6服务，然后输入show master status 命令查询Master主机的信息；

mysql > show master status \\G;

（8）将Master主机的数据备份出来，然后导入到Slave主机中去，具体的执行语句如下：  
C:Program Files\\MySQL\\MySQL Server 5.6 \\bin>mysqldump -u root -p -h localhost test > c:\\a.txt

将c:a.txt**复制**到Slave主机上面去，然后执行如下的操作：

C:\\Program Files\\MySQL\\MySQL Server 5.6\\bin>mysqldump -u root -p -h localhost test <c:\\a.txt

（9）配置Slave主机（192.168.0.206）。在C:Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.6目录下面的配置文件my.ini中，配置信息如下所示：

\[mysql\]

default-character-set=utf-8

log\_bin=”D:/MySQLlog/binlog”

expire\_logs\_days=10

max\_binlog\_size=100M

**\[mysqld\]**

**server-id =2**

提示：配置Slave主机my.ini文件的时候，需要将server-id写到\[mysqld\]的后面，另外，如果配置文件还有log\_bin的配置，可以将它注释掉，如下所示：

\# Binary Logging.

\# log-bin

\# log\_bin = “D:/MySQLlog/mysql-bin.log”

（10）重启Slave主机（192.168.0.206），在Slave主机（192.168.0.206）的MySQL中执行如下的命令，关闭slave服务，执行如下所示：

mysql > stop slave;

（11）设置Slave从机，实现复制相关的信息，命令如下：

mysql > change master to

\-> master\_host=’192.168.0.208’

\-> master\_user=’repl’

\-> master\_password=’123’

\->master\_log\_file=’binlog.000003’

\-> master\_log\_pos=120;

参数说明：

master\_host=’192.168.0.208’  表示实现复制的主机的IP地址；

master\_user=’repl’  表示实现复制的登陆远程主机的用户；

master\_password=’123’  表示实现复制的登陆远程主机的密码；

master\_log\_file=’binlog.000003’  表示实现复制的binlog日志文件；

master\_log\_pos=120;  表示实现复制的binlog日志文件的偏移量；

（12）继续执行操作，显示Slave从机的状况，如下所示：

mysql > start slave;

mysql > show slave status \\G;

执行完之后会有一些问题的，

下面的步骤是解决该问题的方法，具体操作步骤如下：

1、重启Master(192.168.0.208)主机，执行show  master status \\G命令，几下File和Position的值，后面Slave主机会使用到，命令如下：

mysql > show master status \\G;

2、 在Slave（192.168.0.206）主机上重新设置信息，命令如下：

mysql > stop slave;

mysql > change master to

\-> master\_log\_file=’binlog.000004’

\->master\_log\_pos = 120;

mysql > start slave;

mysql > show slave status\\G;

## **3.Windows环境下主从复制测试** ##

接下来可以进行Window环境下的主从复制的测试：

在Windows环境下测试主从复制操作，具体操作步骤如下：

（1）在Master主机的MySQL环境下，执行如下命令：

mysql > use test;

mysql > create tabel rep\_test(

data integer

);

mysql > insert into rep\_test values(2);

（2）在Slave主机的MySQL环境下，查看主机刚才添加的表和数据是否成功同步到从机上，命令执行如下所示：

mysql >  show tables;

mysql > use test;

mysql > select \* from rep\_test;

提示：在实际的生产环境中，MySQL架构可能会用到一主多从的架构；

# **3.Linux环境下的MySQL复制** #

## **1.下载并安装MySQL** ##

使用源码安装MySQL的过程可以非常方便的进行性能的优化，下面就源码安装过程中涉及到的优化项进行简单地介绍：

第一步：

下载mysql-5.6.10.tar.gz源文件。

第二步：

下载完安装包之后，创建MySQL安装程序目录和数据文件的目录。命令执行如下：

\[root@localhost ~\]\# mkdir -p /usr/local/mysql

\[root@localhost ~\]\# mkdir -p /usr/local/mysql/data

\[root@localhost ~\]\# groupadd mysql

\[root@localhost ~\]\# useradd -r -g mysql mysql

第三步：

解压缩MySQL源代码，这里使用的是cmake来编辑安装MySQL源代码。

\[root@localhost ~\]\# groupadd mysql

\[root@localhost ~\]\# useradd -r -g mysql mysql

\[root@localhost ~\]\# tar -zxvf mysql-5.6.10.tar.gz

\[root@localhost ~\]\# cd mysql-mysql-5.6.10

\[root@localhost mysql-5.6.10\]\# cmake .

\[root@localhost mysql-5.6.10\]\# make && make install

第四步：

修改MySQL安装程序中的目录权限，命令如下：

\[root@localhost ~\]\# chown -R mysql.mysql /usr/local/mysql

第五步：

安装MySQL 5.6的源代码：

\[root@localhost ~\]\# cd /usr/local/mysql/scripts

\[root@localhost scripts\]\# ./mysql\_install\_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

\[root@localhost ~\]\# cd /usr/local/mysql/support-files

\[root@localhost  support-files\]\# cp mysql.server /etc/rc.d/init.d/mysql

\[root@localhost  support-files\]\# cp my-default.cnf  /etc/my.cnf

\[root@localhost  ~\]\# chkconfig --add mysql

\[root@localhost  ~\]\# chkconfig mysql on

第六步：

\[root@localhost  ~\]\# service mysql start

第七步：

\[root@localhost  ~\]\# mysql

## **2.单机主从复制前的准备工作** ##

MySQL服务器可以采用主从机制进行备份，如果一对一进行备份对于生成环境而言比较浪费资源，主服务器把数据变化记录到主日志，然后从服务器通过I/O线程读取主服务器的日志，并将它写入到从服务器的中继日志中，接着SQL线程读取中继日志，并且在从服务器上重放，从而实现MySQL复制；

MySQL具有可以运行多个实例的功能，这个功能是通过mysqld\_multi实现的。当一台机器上需要运行多个MySQL服务器的时候，mysqld\_multi是管理多个mysqld的服务进程，这些服务进程程序用不同的unix socket或者是监听不同的端口，通过命令，可以启动、关闭和报告所管理的服务器的状态；

**下面介绍在一台服务器上使用mysqld\_multi****管理多个MySQL****服务进程**

第一步：

初始化多个实例数据库，首先需要停止掉MySQL服务器，命令执行如下：

\[root@localhost ~\]\# service mysql stop

提示：此时可以使用netstart命令查看3306关闭了没有，如果没有查询出来结果，那么说明MySQL服务器已经成功的关闭掉了；

\[root@localhost ~\]\# ln -s /usr/local/mysql/bin/mysqld\_multi  /usr/bin/mysqld\_multi

\[root@localhost ~\]\# ln -s /usr/local/mysql/scripts/mysql\_install\_db  /usr/bin/mysql\_install\_db

第三步：

初始化3个数据目录并安装3个mysql服务，命令如下：

\[root@localhost ~\]\# cd /usr/local/mysql/

\[root@localhost mysql\]\# mkdir -p /usr/local/var/mysql1

\[root@localhost mysql\]\# mkdir -p /usr/local/var/mysql2

\[root@localhost mysql\]\# mkdir -p /usr/local/var/mysql3

\[root@localhost mysql\]\# ./scripts/mysql\_install\_db --datadir=/usr/local/var/mysql1 --user=mysql

\[root@localhost mysql\]\# ./scripts/mysql\_install\_db --datadir=/usr/local/var/mysql2 --user=mysql

\[root@localhost mysql\]\# ./scripts/mysql\_install\_db --datadir=/usr/local/var/mysql3 --user=mysql

第四步：

从MySQL的源码中把mysqld\_muil.server复制到/etc/init.d/目录下，命令执行如下：

\[root@localhost ~\]\# cd /usr/local/mysql/support-files/

\[root@localhost support-files\]\# cp ./mysqld\_multi.server /etc/init.d/mysql\_multi.server

第五步：

配置数据库配置文件，直接配置/etc/my.cnf，修改相应的属性，如下所示：

\[mysqld\_multi\]

mysqld =/usr/local/mysql/bin/mysql\_safe

mysqladmin =/usr/local/mysql/bin/mysqladmin

user =root

\[mysqld1\]

port =3306

\[mysqld2\]

port =3307

socket =/temp/mysql2.sock

datadir =/usr/local/var/mysql2

\[mysqld3\]

port =3308

socket =/temp/mysql3.sock

datadir =/usr/local/var/mysql3

\[mysqld\]

第六步：

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf report

此时会发现，MySQL服务器的状态，不能够打开所需要的文件，程序发生错误；

第七步：

使用mysqld\_multi启动MySQL服务器，命令执行如下：

\[root@localhost mysql1\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf stop

\[root@localhost mysql1\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf start

\[root@localhost mysql1\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf report

第八步：

测试MySQL服务器的状态，命令执行如下；

\[root@localhost ~\]\# netstart -an|grep 330

此时发现端口同时开启3306、3307、3308端口，进程里面可以发现同时开启了两个mysql\_safe进程

第九步：

登陆查看MySQL数据库：

\[root@localhost data\]\# mysql -u root -p -P 3306

\[root@localhost data\]\# mysql -u root -p -P 3307

\[root@localhost data\]\# mysql -u root -p -P 3308

此时可以发现顺利的登陆到数据库，此时也可以通过ps命令发现后台产生了3个mysqld进程的实例；

第十步：

直接登陆到MysQL服务器，执行show variables命令发现三个MySQL服务器的pid\_file，socket参数都一样，命令执行如下：  
mysql > show variables like ‘socket’;

mysql > show variables like ‘pid%’;

此时，通过登陆MySQL服务器自带参数，可以解决以上的问题。命令执行如下所示：

\[root@localhost ~\]\# mysql -u root -S /tmp/mysql2.sock

从测试结果可以看出：问题已经解决了，接下来启动三个数据库，可以直接使用了

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf stop 1-3

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf start  1-3

## **3.mysqld\_multi实现单机主从复制** ##

MySQL的复制至少需要两个MySQL服务，这些MySQL服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务；

MySQL的复制（Replication）是一个异步的过程，从Master复制到Slave。整个复制的过程由三个线程完成，Master端的IO线程；Slave端的SQL线程和IO线程；

如果想要实现复制的过程，Master必须打开Binary Log功能，复制过程其实就是Slave从Master端获取bin日志，然后在自己服务器上完全顺序执行日志中所记录的各种操作；

**下面采用mysqld\_multi****实现单机MySQL****服务器主从复制**

步骤一：

使用mysqld\_multi开启上一节已经设定好的三个MySQL服务器，命令执行如下：

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf start  1-3

\[root@localhost ~\]\# netstart -an|grep 330

步骤二：

登陆Master 主服务器，设置一个复制使用的账户，并授予REPLICATION SLAVE         权限。

这里创建一个复制用户repl；

\[root@localhost ~\]\# mysql -u root -p -P3306

mysql > grant replication slave on \*.\* to ‘repl’@’localhost’ identified by ‘123‘;

mysql > grant replication slave on \*.\* to ‘repl’@’%’ identified by ‘123’;

步骤三：

修改Master主数据库服务器的配置文件my.cnf，开启BINLOG ,并设置server-id的值。需要重启服务器之后才能生效；

\[root@localhost ~\]\# vi /etc/my.cnf

\[mysqld\]

port = 3306

log-bin = /usr/local/var/mysql1/mysql-bin

server-id = 1

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf stop 1-3

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf start  1-3

步骤四：

在Master主服务器上，设置度锁定有效，这个操作为了确保没有数据库操作，以便获得一致性的快照；

\[root@localhost ~\]\# mysql -u root -P 3306 -S /tmp/mysql.sock

mysql > flush tables with read lock;

步骤五：

使用show master status 命令查看日志情况，查询得到主服务器上当前的二进制日志名和偏移量值。这个操作的目的是为了从数据库启动之后，从这个点开始进行数据的恢复；

mysql > show master status \\G;

步骤六：

主数据库服务器此时可以做一个备份，可以在服务器停止的情况下直接使用系统复制命令；

\[root@localhost mysql1\]\# tat -cvf data.tar data

步骤七：

主数据库备份完成之后，主数据库恢复写操作，命令执行如下：

mysql > unlock tables;

提示：Master主服务器的配置已经成功，如果my.cnf的mysqld选项设置server-id参数，但从服务器没有设置server-id，那么启动从服务器会发生错误：

mysql > start slave;

步骤八：

接下来继续编辑/etc/my.cnf文件，具体的配置如下：

\[mysqld\_multi\]

mysqld =/usr/local/mysql/bin/mysql\_safe

mysqladmin =/usr/local/mysql/bin/mysqladmin

user =root

\[mysqld1\]

port =3306

log-bin = /usr/local/var/mysql1/mysql-bin

server-id =1

\[mysqld2\]

port =3307

socket =/temp/mysql2.sock

datadir =/usr/local/var/mysql2

log-bin = /usr/local/var/mysql1/mysql-bin

server-id =2

\[mysqld3\]

port =3308

socket =/temp/mysql3.sock

datadir =/usr/local/var/mysql3

log-bin = /usr/local/var/mysql1/mysql-bin

server-id =3

\[mysqld\]

步骤九：

重启Master主服务器，命令如下：

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf stop 1-3

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf start  1-3

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file=/etc/my.cnf report

步骤十：

对数据库服务器做相应的设置，此时需要制定复制使用的用户，主数据的IP地址，端口以及开始复制的日志文件和位置等，具体设置如下：

\[root@localhost ~\]\# mysql -u root -p -P 3307 -S /temp/mysql2.sock

mysql > show variables like ‘%log\_bin%’;

mysql > stop slave;

mysql > change master to

\-> master\_host=’127.0.0.1’

\-> master\_user=’repl’

\->master\_password=’123’

\->master\_log\_file=’mysql-bin.000001’

\->master\_log\_pos=120;

步骤十一：

在从服务器上执行show slave status\\G命令查询从服务器的状态，命令执行如下：

mysql > start slave;

mysql > show slave status \\G;

步骤十二：

此时发现服务器已经成功设置，此时也可以执行show processlist \\G 命令查询从服务器的进程状态，命令执行如下：

mysql > show processlist \\G;

步骤十三：

此时可以测试复制服务的正确性，在Master主数据库上执行一个更新操作，观察是否在从服务器上同步。下面在主数据库的test库上创建一个测试表，然后插入数据，命令执行如下：  
\[root@localhost ~\]\# mysql -u root -p -P 3306 -S /tmp/mysql.sock

mysql > use test;

mysql > show tables;

mysql >create table repl\_test(id int);

mysql > insert into repl\_test values(1),(2);

步骤十四：

在从服务器上检测新的表是否被创建，数据是否同步，执行命令如下：

\[root@localhost ~\]\# mysql -u -p 3307 -S /temp/mysql.sock

mysql >use test;

mysql > show tables ;

mysql > select \* from repl\_test;

至此端口为3306的Master主机上的数据已经可以正确地同步到端口为3307的Salve主机上的数据库上，复制服务配置成功完成。另外一个端口为3308的从机的配置和端口为3307的一样操作；

## **4.不同服务器之间实现主从复制** ##

在大多数情况下，采用不同的MySQL主从复制比较常见，不同IP地址的服务器上的MySQL服务器实现一对一复制跟上一节比较相似，具体的配置步骤如下：

步骤一：

确保主从服务器上安装了相同版本的数据库，设定主服务器的IP是192.168.1.100，从服务器的IP为192.168.1.101；

步骤二：

登陆主服务器，设置一个复制使用的账户，并授予REPLICATION SLAVE 权限。这里创建一个复制用户repl；

mysql > grant replication slave on \*.\* to ‘repl’@’192.168.1.101’ identified by ‘123’;

步骤三：

修改主数据库服务器的配置文件my.cnf，开启BINLOG ，并设置server-id的值，需要重启服务器之后才能生效；

my.cnf中修改

\[mysqld\]

log-bin =/usr/local/var/mysql1/mysql-bin

server-id = 1

步骤四：

在主服务器上，设置读锁有效，这个操作为了确保没有数据库操作，以便获得一致性的快照；

mysql > flush tables with read lock;

步骤五：

查询主服务器上的当前的二进制日志名和偏移值，这个操作的目的是为了在从数据库启动之后，从这个点进行数据库的恢复；

mysql > show master status;

步骤六：

主数据库停止更新操作，需要生成数据库的备份，可以通过mysqldump导出数据或者使用ibbackup工具进行数据库的备份，如果数据库停止，那么可以直接使用cp复制全部数据文件到从数据库服务器上。

主数据库备份完成之后，主数据库恢复些操作，命令执行如下：

mysql > unlock tables;

步骤七：

修改从服务器的配置文件my.cnf，增加server-id参数。Server-id参数是唯一的，不能和主数据库的配置相同，如果有多个从数据库，每个从数据库都必须有自己唯一的server-id值；

my.cnf

\[mysqld\]

server-id = 2

步骤八：

启动从数据库

\[root@localhost ~\]\# mysqld\_safe --skip-slave-start &

步骤九：

对从数据库服务器做相应的设置，指定复制使用的用户、主数据库服务器的IP、端口以及开始执行复制的日志文件和位置，命令执行如下：

mysql > stop slave;

mysql > change master to

\-> master\_host=’192.168.1.100’,

\-> master\_user=’repl’,

\-> master\_password=’123’,

\->master\_log\_file=’mysql-bin.000029’

\-> master\_log\_pos=109;

步骤十：

在从服务器上，启动slave线程；

mysql > start slave;

步骤十一：

在从服务器上执行show slave status\\G命令查询从服务器的状态。

mysql > show slave status \\G;

此时也可以执行show processlist \\G命令查询从服务器的进程状态；

mysql > show processlist \\G;

接下来可以测试复制服务的正确性，在主数据库上执行一个更新的操作，观察是否在从数据库上同步。

## **5.MySQL主要复制启动选项** ##

MySQL安装配置的时候，已经介绍了几个启动时的常用参数，其中包括MASTER\_HOST、MASTER\_PORT、MASTER\_USER、MASTER\_PASSWORD 、MASTER\_LOG\_FILE、   MASTER\_LOG\_POS

下面几个参数需要在从服务器上配置，下面介绍几个常用的启动选项，如log-slave-updates、master-connect-retry、read-only和salve-skip-errors等

【1】log-slave-updates：

log-slave-updates参数主要是用来配置从服务器的更新是否写入到二进制日志，该选项默认是不打开的，如果这个从服务器同时也作为其他服务器的主服务器，搭建一个链式的复制，那么就需要开启这个选项，这样他的从服务器才能获取它的二进制日志进行同步操作；

【2】master-connect-retry：

master-connect-retry参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认为60秒。

【3】read-only：

read-only是用来限制用户对从服务器的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作。如果主数据库创建了一个普通用户，在默认的情况下，该用户是可以更新从数据库中的数据的，如果使用read-only选项启动从数据库以后，该用户对从数据库的更新会提示错误；

使用read-only选项启动语法如下：

\[root@localhost ~\]\# mysqld\_safe -read-only&

【4】salve-skip-errors：

在复制的过程中，从服务器可能会执行BINLOG 中的错误的SQl语句，此时如果不忽略错误，从服务器将会停止复制进程，等待用户处理错误。这种错误如果不能及时的发现，将会对应用或者备份产生影响。slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，设置该参数之后，MySQL会自动的跳过所配置的一系列错误，直接执行后面的SQL语句，该参数可以定义多个错误号，如果设置成all，则表示跳过所有的错误，具体语法如下：

vi /etc/my.cnf

slave-skip-errors=1007,1051,1062

如果从数据库主要是作为主数据库的备份，那么就不应该使用这个启动参数，设置不当，很可能造成主从数据库的数据不同步。如果从数据库仅仅是为了分担主数据库的查询的压力，并且对数据的完整性要求不是很严格，那么这个选项可以减轻数据库管理员维护从数据库的工作量；

## **6.指定复制的数据库或者表** ##

MySQL数据库可以指定需要复制到从数据库上的数据库或者表，有时候只需要将主数据库中的某些关键表复制到从服务器上，或者只需要将某些提供查询的表复制到主数据库上，经常可以使用replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或者replicate-while-do-table指定复制的数据或者表；

这些配置项均是在my.cnf配置文件中添加的

【1】replicate-do-db：

表示从服务器可以复制的数据库的名字，如果有多个数据库，那么可以重复写多个replicate-do-db配置，replicant-ignore-db表示从服务器复制过程中忽略复制该配置设置的数据库名称；

【2】replicate-do-table：

指定复制的表

【3】replicate-ignore-db：

指定不被复制的数据库

【4】replicate-ignore-table：

指定不被复制的表

【5】replicate-while-do-table：

启动主从数据库服务器：

\[root@localhost ~\]\# mysqld\_multi --defaults-extra-file =/etc/my.cnf start 1-3

登录端口为3306的数据库

\[root@localhost ~\]\# mysql -u root -P 3306 -S /tmp/mysql.sock

# **4.查看Slave的复制进度** #

很多情况下，用户都想知道从服务器复制的进度，从而判断从服务器上复制数据的完整性，同时判断是否需要手工来做主从的同步工作。事实上，用户可以通过SHOW PROCESSLIST列表中的Slave\_SQL\_Running线程的Time值得到，它记录了从服务器当前执行的SQL时间戳与系统时间之间的差距；

第一步：

在主服务器上插入一个包含当前时间戳的记录，命令执行如下：

mysql > alter table rep\_t3 add column createtime datetime;

mysql > insert into rep\_t3 values(1,now());

第二步：

让从服务器的I/O线程停止下来，使得从数据库服务器暂时不写中继日志，停止时候执行的SQL就是最后执行的SQL，命令如下：

mysql > stop slave;

mysql > select \* from rep\_t3;

mysql > select now();

第三步：

从数据库服务器上执行show processlist查看SQL线程的时间，这个时间说明了主服务器最后执行的更新操作大概是主服务器46秒前的更新操作，命令执行如下；  
mysql > stop slave io\_thread;

mysql > show processlist \\G;

# **5.日常管理和维护** #

数据复制环境配置完成之后，数据库管理员需要进行日常的监控和管理维护工作，以便能够及时的发现问题和解决问题，以此来保证主从数据库能够正常的工作。有时候因为主服务器的更新过于频繁，造成了从服务器更新速度较慢，当然问题是多种多样，有可能是网络搭建的结构不好或者硬件的性能较差，从而使得主从服务器之间的差距越来越大，最终对某些应用产生了影响，在这种情况下，用户需要定期进行主从服务器的数据同步操作；

## **1.了解服务器的状态** ##

一般使用show slave status命令检查从服务器：

mysql > show salve status \\G;

在查看从服务器的信息中，首先需要查看“**Slave\_IO\_Running**”和“**Slave\_SQL\_Running**”这两个进程状态是否是“yes“，Slave\_IO\_Running表名，是否此进程能够由从服务器到主服务器正确的读取BINLOG日志，并写入到从服务器的中继日志中。Slave\_SQL\_running则表名能够读取并执行中继日志中的BINLOG信息；

## **2.服务器复制出错的原因** ##

在某些情况下，会出现从服务器更新失败，此时，首先需要确定是否是主从服务器的表不同造成的。如果是表结构不同导致的，则修改从服务器上的表与主服务器上的表一致，然后重新执行START SLAVE命令。服务器复制出错的常见问题如下：

【问题一：出现“log event entry exceeded max\_allowed\_pack”错误】

如果在应用中出现大的BLOG列或者长字符串，那么在从服务器上复制的时候，可能会出现“log event entry exceeded max\_allowed\_pack”的错误，这是因为含有大文本的记录无法通过网络进行传输而导致的错误，解决方法是在主从服务器上添加max\_allowed\_packet 参数，该参数默认的设置是1MB；

mysql > show variables like ‘MAX\_ALLOWED\_PACKET’;

mysql > set @@global.max\_allowed\_packet=16777216;

同时在my.cnf中，设置max\_allowed\_packet=16MB，数据库重新启动之后该参数将有效；

【问题二：多主复制的自增长变量冲突问题】

大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下，可能会存在多个服务器配置为复制主服务器，使用auto\_increment的时候应该采用特殊的步骤防止键值冲突，否则插入行的时候多个服务器会视图使用相同的anto\_increment值；

服务器变量auto\_increment\_increment和auto\_increment\_offset可以协调多主服务器复制和auto\_increment列；

在多主服务器复制到从服务器的过程中，迟早会发生主键冲突，为了解决这种情况，将不同的主服务器的这两个参数重新设置，可以将A数据库服务器设置为auto\_increment\_increment=1和auto\_increment\_offset=1，此时B数据库服务器设置为auto\_increment\_increment=1和auto\_increment\_offset=0；

参数说明：

auto\_increment\_increment：每次增加的量；

auto\_increment\_offset：每次增加之后的偏移量，也就是在每次增加的量之后，还需要再增加的偏移量；

# **6.切换主从服务器** #

在实际工作环境中，有时候会遇到这样的问题，有一个这样的工作环境，一个主数据库服务器A，两个从数据库服务器B、C同时指向主服务器数据库，当主数据库服务器A发生故障的时候，需要将其中的一个从数据库B服务器切换成主数据库，同时修改数据库C服务器的配置，使其指向新的主数据库B。

**切换主从服务器的具体执行步骤：**

**第1****步：**

首先需要确保所有的从数据库都已经执行了relay log 中的全部更新，看从数据库的状态是否是Has read all relay log，是否更新都已经执行完成；

mysql > stop slave IO\_THREAD;

mysql > show processlist \\G;

**第2****步：**

在从数据库B上停止slave服务，然后执行reset master重置成主数据库；