centos6.3下的主从数据库同步

• 介绍

Mysql支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。MySQL主从复制的原理如下：

(1) Master将数据改变记录到二进制日志(binary log)中，并维护日志文件的一个索引以跟踪日志循环。这些日志就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)；
(2) Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)；
(3) Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据。

本文旨在讲述CentOS6.3下如何配置MySQL的主从同步。MySQL配置主从同步后，一方面主数据库的数据可以实时的写入从数据库，实现对数据的备份。

• 配置前准备

目的：保证主、从数据库同步，至少数据库、表结构一致。

主服务器地址：192.170.0.251

从服务器1地址：192.170.0.100

从服务器2地址：192.170.0.200

• MySQL主服务器配置

• 修改my.cnf配置文件（/etc/my.cnf）

在[mysqld]下添加如下配置：

server-id=1         #1表示主服务器

log_bin=mysql-bin   # 开启二进制日志功能，名字随便取

binlog_do_db=hss    #复制过滤：指定需要备份的数据库，输出binlog

binlog_ignore_db=mysql #复制过滤：不需要备份的数据库，不输出

binlog_ignore_db=test  #无需同步的数据库

binlog_ignore_db=information_schema #无需同步的数据库

## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true

重启MySQL数据库后登录，创建MySQL同步使用的账户并授权

Mysql> grant replication slave on *.* to 'slave'@'192.170.0.100' identified by 'sunkaisens';  #授权用户slave从192.170.0.100访问数据库

Mysql> flush privileges;   #刷新系统授权表

记录Mysql二进制文件的file和position值

• MySQL从服务器配置

• 修改my.cnf配置文件,并保存退出

server-id=2 #2表示从服务器，注意该id值全局唯一，不同从机的id值也不能相同

log-bin=mysql-bin           

master-host=192.170.0.251  #要同步的主服务器地址

master-user=slave          #同步用的用户名

master-password=sunkaisens  #密码

master-port=3306           #主服务器端口号

#这部分在mysql5.5版本内指令即可，my.cnf配置文件内不支持（配置会写入master.info文件中，见下方黄色部分）

mysql >

CHANGE MASTER TO   

MASTER_HOST='192.168.0.65',    

MASTER_PORT=3306,    

MASTER_USER='repl',    

MASTER_PASSWORD='123456',    

MASTER_LOG_FILE='master-bin.000001',    

MASTER_LOG_POS=353;

replicate-ignore-db=mysql

replicate-ignore-db=test

replicate-ignore-db=information_schema

replicate-do-db=hss  #需要同步的数据库

• 重启MySQL，登录后配置同步信息

指定主服务器IP地址：192.170.0.251

指定同步用账户的用户名和密码

指定同步用的主机二进制文件的file和position值

Mysql>  slave stop;    #停止slave同步进程

Mysql> change master to master_host='192.170.0.251', master_user='slave',  master_password='sunkaisens', master_log_file='mysql-bin.000016', master_log_pos=139239;     #执行同步语句

Mysql> slave start;    #开启slave同步进程

• 查看配置是否成功

若Slave_IO_Running与Slave_SQL_Running 字段都显示yes，则表示配置成功

• MySQL主服务器侧查看状态

• MySQL>SHOW PROCESSLIST\G;

如果出现Command: Binlog Dump,则说明配置成功

• MySQL主从不同步解决

• 主从不能同步:

show slave status;报错:Error xxx dosn't exist

且show slave status\G:

Slave_SQL_Running: NO

Seconds_Behind_Master: NULL

解决办法I：

MySQL>stop slave;

MySQL>set global sql_slave_skip_counter =1 ;

MySQL>start slave;

之后Slave会和Master去同步 主要看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master是否为0，0就是已经同步了

解决办法II：

1）首先停掉Slave服务：slave stop

2）到主服务器上查看主机状态：

记录File和Position对应的值。

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000020 | 135617781 | | |

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

1 row in set (0.00 sec)

3）到slave服务器上执行手动同步：

mysql> change master to

> master_host='master_ip',

> master_user='user',

> master_password='pwd',

> master_port=3307,

> master_log_file='mysql-bin.000020',

> master_log_pos=135617781;

1 row in set (0.00 sec)

mysql> slave start;

1 row in set (0.00 sec)

再次查看slave状态发现：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

Seconds_Behind_Master: 0

• 还需要做的一些优化与监视:

show full processlist; //查看mysql当前同步线程号

skip-name-resolve //跳过dns名称查询，有助于加快连接及同步的速度

max_connections=1000 //增大Mysql的连接数目，(默认100)

max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)

• 临时跳过MYSQL同步错误

确保数据一致的情况下临时的跳过这个错误操作如下，有可能要多次。

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

# vi /etc/my.cnf

[mysqld]
#slave-skip-errors=1062,1053,1146       #跳过指定error no类型的错误
#slave-skip-errors=all            #跳过所有错误