LINUX

mysql主从同步

mysql主从同步是通过从服务器获取主服务器的二进制日志保存的内容来进行同步。系统为CentOS，主服务器和从服务器mysql版本最好相同。

主服务器IP：192.168.1.100
从服务器IP：192.168.1.200

一、主服务器
1、创建用于同步的用户
该帐户必须给予replication slave权限。

进入mysql命令行后建立用户：

grant replication slave on *.* to 'syncuser'@'192.168.1.200' identified by '123456';
flush privileges;

建立一个名为syncuser用户，密码是123456，具有replication slave权限。flush privileges用于刷新mysql系统权限相关的表。

可以查询一下新建的用户：

select user,host,password from mysql.user;

2、修改my.cnf配置

vim /etc/my.cnf

在[mysqld]后添加：

server-id=1
log-bin=mysql-bin #开启二进制日志文件

3、重启mysql服务器

service mysqld restart

4、锁表，获取日志文件名和Position值（偏移量）
进入mysql命令行输入：

flush tables with read lock;
show master status;

显示如下：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |     1196 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

日志名为mysql-bin.000002，Position值为1198。

5、等待第一次同步成功后再解锁

unlock tables;

二、从服务器
1、修改my.cnf配置

vim /etc/my.cnf

在[mysqld]后添加：

server-id=2

2、重启mysql服务器

service mysqld restart

3、用change master语句指定同步位置
进入mysql命令行输入：

change master to master_host='192.168.1.100', master_user='syncuser', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=1196;

4、启动从服务器

start slave;

5、查看从服务器进程

show processlist\G;

显示如下：

*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 218
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 6
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 218
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

6、查看从服务器状态

show slave status\G;

看到有：

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

说明从服务器上的IO线程和SQL线程已经在运行了。可以在主服务器上解锁了。

三、其它
（一）
发现不能同步，查看从服务器状态中出现：

Slave_SQL_Running: No
Last_SQL_Error: Error Error ‘Table ‘test.name’ doesn’t exist’ on query.

原来是数据库本身不一样导致的。

0、首先从主服务器拷贝一份完整的数据库到从服务器。从服务器上需要重新手工同步。

1、从服务器上停掉slave服务

stop slave;

2、主服务器上查看日志文件名和Position值

flush tables with read lock;
show master status;

得到mysql-bin.000003 13492

3、从服务器上执行手工同步

change master to master_host='192.168.1.100', master_user='syncuser', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=13492;
start slave;
show slave status\G;

4、主服务器上解锁

unlock tables;

PS：热备份一旦从服务器出现sql_error，SQL线程就不运行了，备份是从获得日志偏移量开始之后的动作会同步，在同步前从服务器也要有和主服务器一模一样的数据库。

（二）
查看mysql错误日志发现有个警告：

tail -f /var/log/mysqld.log

[Warning] Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=mysqld-relay-bin’ to avoid this problem.

意思是不指定relay-log，如果修改了主机名，mysql从服务器同步会中断。

在从服务器的my.cnf中，[mysqld]后添加一句即可：

relay-log=mysqld-relay-bin

relay-log日志作用是从服务器I/O线程将主服务器的二进制日志读取过来，写到从服务器的本地文件，然后SQL线程会读取relay-log日志的内容并执行到从服务器。

（三）
mysql的二进制日志时间长了会很占空间，需要定期清理。
使用expire-logs-days参数，在主从服务器的my.cnf里，[mysqld]后添加：

expire-logs-days=7

mysql二进制日志的大小默认1G，在命令行使用show variables命令查看，或max_binlog_size参数设置日志大小。

参考资料：
http://www.mike.org.cn/articles/mysql-master-slave-sync-conf-detail/
http://www.mysqlops.com/2011/06/16/mysql-master-replication-slave.html
http://www.imfeng.com/linux-deploy-mysql-master-and-slave/
http://www.cbrother.com/html/2101.html