mysql复制--主从复制配置

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。

如下图所示：

参考：mysql复制--主从复制配置

复制功能：

• 数据分布
• 负载均衡（读）
• 备份
• 高可用和故障切换
• MySQL升级测试

复制方式：

1、主–从复制

2、主-主复制（Mysql主主同步方案）

3、半同步复制

主从同步复制原理

在开始之前，我们先来了解主从同步复制原理。

复制分成三步：

1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

2. slave将master的binary log events拷贝到它的中继日志(relay log)；

3. slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了这一过程：

 

复制过程：

1. 主节点必须启用二进制日志，记录任何修改数据库数据的事件。
2. 从节点开启一个线程（I/O Thread)把自己扮演成mysql的客户端，通过mysql协议，请求主节点的二进制日志文件中的事件
3. 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
4. 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具哪个二进制日志文件的哪个位置。
5. 从节点启动另外一个线程（sql Thread ），把replaylog中的事件读取出来，并在本地再执行一次。

复制中线程的作用：

从节点：

• I/O Thread:从Master请求二进制日志事件，并保存于中继日志中。
• Sql Thread:从中继日志中读取日志事件，在本地完成重放。

主节点：

• Dump Thread:为每个Slave的I/O Thread启动一个dump线程，用于向从节点发送二进制事件。

思考：从节点需要建立二进制日志文件吗？ 
看情况，如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件。

Mysql复制特点：

1. 异步复制：主节点中一个用户请求一个写操作时，主接点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成，在响应用户，主机点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主服务，可以使用（很多软件来检查是否落后）
2. 主从数据不一致。

主从复制配置过程：

主节点：

1. 启用二进制日志。
2. 为当前节点设置一个全局唯一的server_id。
3. 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

1. 启动中继日志。
2. 为当前节点设置一个全局唯一的server_id。
3. 使用有复制权限的用户账号连接至主节点，并启动复制线程。

配置演示：

测试环境： 
主节点：192.168.0.196 
从节点：192.168.0.198 
mysql版本：mysql-5.5.17 
linux：centos6.5

1：编辑主节点配置文件

vi /etc/my.cnf

[mysqld]节点添加如下内容（启用二进制日志文件）：

#设置服务器id，为1表示主服务器,实例唯一ID，每个节点不可相同
server_id=1
#启动MySQ二进制日志系统
log-bin=mysql-bin
#选择row模式 （有三种：statement/row/mixed）
binlog-format=ROW
#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
binlog-do-db=mydb
#不同步mysql系统数据库
binlog-ignore-db=mysql

2 启动mysql服务，并连接mysql 

3 查看日志信息 
1:)查看二进制日志是否开启 

msql > SHOW GLOBAL VARIABLES LIKE '%log%';

2:)查看主节点二进制日志列表 

msql > SHOW MASTER LOGS;

3:)查看主节点的server id 

msql > SHOW GLOBAL VARIABLES LIKE '%server%';

4 在主节点上创建有复制权限的用户。

msql > grant replication slave on *.* to '用户名'@'%' identified by '任意密码';

5 刷新 

msql > FLUSH PRIVILEGES;

主服务器配置完成。 
注意：确保主服务器的iptables没有阻断3306的访问端口。

6 编辑从节点配置文件 

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

[mysqld] 节点下添加如下内容（开启中继日志）

#关闭二进制日志功能（从节点可以不需要二进制日志功能）

skip-log-bin
relay-log=relay-log
relay-log-index=relay-log.index
server-id=2

#开启独立表空间
innodb_file_per_table=ON

注意：如果是通过虚拟机拷贝，mysql服务，需要将数据库data文件夹下的auto.cnf文件，删除否则：

 

7 启动从节点mysql服务，并登陆mysql

 
8查看从节点日志信息 
1:)查看中继日志是否开启 

msql > SHOW GLOBAL VARIABLES LIKE '%log%';

 

2:)查看server 相关信息 

msql > SHOW GLOBAL VARIABLES LIKE '%server%';

9 在从节点配置访问主节点的参数信息 
添加 主节点主机，访问主节点的用户名及密码，主节点二进制文件信息。 
注意：主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。 

msql > CHANGE MASTER TO MASTER_HOST='192.168.0.196',MASTER_USER='上文的用户名',MASTER_PASSWORD='上文的密码', MASTER_LOG_FILE='mysql-bin.000042',MASTER_LOG_POS=362;

•  

12：查看从节点的状态信息 

msql >  show slave status\G;

因为没有启动 从节点的复制线程，IO线程 和 SQL 线程都为NO. 

13：启动复制线程 

msql >  START SLAVE ;

START SLAVE 可以指定线程类型：IO_THREAD ,SQL_THREAD, 如果不指定，则两个都启动。 

14:再次查看从节点状态信息 

至此，mysql的主—-从复制配置完成。 

注意：

Slave_IO_Running: Yes //此状态必须YES

Slave_SQL_Running: Yes //此状态必须YES

全变YES说明主从配置成功，当MySQL主服务器有更新变化时，从服务器会根据具体配置做相应的同步。

15：测试 
1：）在主节点创建数据库，并查看主节点二进制日志信息 

2：）在从节点查找二进制日志信息，并查看mydb数据库是否复制成功 

 

 

主从复制架构中应注意的问题：

从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

1：限制从服务器为只读 
在从服务器上设置： 
read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。 
阻止所有用户： 
mysq>FLUSH TABLES WITH READ LOCK;

2:如何保证主从复制时的事物安全？ 
1：在主节点设置参数 
sync_binlog=1： Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃时，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。 
如果用到innode 存储引擎: 
innodb_flush_logs_at_trx_commit=ON（刷写日志：在事务提交时，要将内存中跟事务相关的数据立即刷写到事务日志中去。） 
innodb_support_xa=ON (分布式事务：基于它来做两段式提交功能) 
sync_master_info=1：每次给从节点dump一些事件信息之后，主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。 

2：在每个slave节点 
skip_slave_start =ON (跳过自动启动，使用手动启动。) 
relay_log也会在内从中先缓存，然后在同步到relay_log中去，可以使用下面参数使其立即同步。 
sync_relay_log =1 ，默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。 
sync_relay_log_info=1每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新

注： 在从节点中 master.info是记录在主节点复制位置的文件。  

relylog_info: 本地将来至于主节点的哪一个二进制文件中position并且保存文本地哪一个中继日志中的哪一个postion. 从节点启动时也需要根据relay-log.info定位本地relay-log. 

 

mysql主从错误断开 怎样恢复

mysql主从同步常见异常及恢复方法

1. 一般的异常只需要跳过一步即可恢复

mysql> stop slave;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> slave start;

 

2.断电导致主从不能同步时，通主库的最后一个bin-log日志进行恢复

在主库服务器上，mysqlbinlog mysql-bin.xxxx > binxxxx.txt

tail -n 100000  binxxxx.txt > tail-binxxxx.txt

vim tail-binxxxx.txt 打开tail-binxxxx.txt文件找到最后一个postion值

然后在从库上，change host to 相应正确的值

mysql> stop slave;

mysql> change master to master_host='ip', master_user='username', master_password='password', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx;

mysql> slave start;

mysql> show slave status\G

 

3.主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql主配置文件my.cnf或my.ini文件指定

略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断

[mysqld]

slave-skip-errors = 1062,1032,1060

 

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

从理想角度看，主从数据库应该无故障的运转下去，可以有时候还是会出现一些莫名其妙的问题，比如说即便从未在从服务器上手动更新过数据，但还是可能遇到“Error: 1062 Duplicate entry”错误，具体原因不详，可能是MySQL本身的问题。遇到这类问题的时候，从服务器会停止复制操作，我们只能手动解决问题，具体的操作步骤如下：
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;

同样的操作可能需要进行多次，也可以设置自动处理此类操作，格式：slave-skip-errors = 错误代码

在从服务器的my.cnf里设置：
slave-skip-errors = 1062

 

更多问题可以参考

MySQL主从复制 + Mycat实现读写分离