MySQL详解（八）：MySQL Replication

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环境下，如果想要实现主从复制，需要准备操作环境。

角色	IP	操作系统	MySQL版本
Master	192.168.0.208	Windows	mysql-install-community-5.6.10.1.msi
Slave	192.168.0.206	Windows	mysql-install-community-5.6.10.1.msi

 

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重置成主数据库；

mysql > stop slave;

mysql > reset master;

 

此时会发现报错Binlog没有位置，不能够执行reset master 命令，下面关闭数据库服务，然后修改/etc/my.cnf，在[mysql2]后面的配置选项添加log-bin选项，修改如下所示：

[mysqld2]

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

 

配置完成之后，登陆数据库B，然后执行如下命令开启主数据库功能；

mysql > stop slave;

mysql > reset master;

 

此时从数据库B已经成功的切换成主数据库，下面接着设置从数据库；

 

 

第3步：

         在从数据库B上添加具有replication 权限的用户repl，查询主数据库的状态，命令执行如下：

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

mysql > show master status;

第4步：

         在从数据库C上配置复制的参数，具体配置如下：

mysql > change master to

-> master_host=’127.0.0.1’,

-> master_user=’repl’,

-> master_password=’123’,

-> master_port=3307,

-> master_log_file=’mysql-bin.000002’,

-> master_log_pos=98;

 

mysql > start slave;

第5步：

         在从数据库C上执行show slave status 命令查看从数据库服务是否开启成功。

mysql > show slave status \G;

 

第6步：

         在主数据库B和从数据库C上面测试数据库是否成功设置复制功能，首先查看主数据库B中test库中表的情况，命令执行如下:

mysql > use test;

mysql > show tables;

 

查询从数据库C中test库中表的情况，命令执行如下：

mysql > use test;

mysql > show tables;

 

第7步：

在主数据库B中增加表rep_t3，命令如下：

mysql > create table rep_t3(data int);   

第8步：

在从数据库C中查询，看表是否成功复制到从数据库，命令执行如下：

mysql > show tables;

 

至此，主从数据库成功的发生切换，最后如果主数据A可以修复的话，可以考虑采用以上的方法将A数据库配置成为B数据库的从数据库；