@cdmonkey 2014-11-14T01:19:37.000000Z 字数 8082 阅读 1328

MySQL主从同步

数据库 主从同步

一、备份数据的意义

作为运维工程师，主要做好两件事：第一就是维护好公司的数据，第二就是网站的7*24小时服务。
那么问题来了，对于丢失一部分数据和网站的7*24小时提供服务来说，哪个更为重要？我们知道都很重要，只是说在实际情况中相对来说哪个更重要，这主要是看企业性质以及具体的业务。例如银行、金融企业，数据是最重要的，一条都不能丢，相比之下，可能暂时宕机的影响就不是太大。再例如百度搜索、QQ的聊天记录，这些数据丢失几万条都不是太大的问题。

对于数据来讲，最为核心的就是数据库中的数据，当然也包括其他数据，而这里要讲解的是数据库中的数据。对于绝大多数企业来说，失去数据就等于失去商机、失去产品以及失去客户，直至公司倒闭，因此数据库的备份与恢复就显得十分重要了。

MYSQL主从同步的作用：

数据分布
负载平衡（load balancing）
备份
高可用性（high availability）和容错

MySQL的Replication是一种多个MySQL的数据库做主从同步的方案，特点是异步，广泛用在各种对MySQL有更高性能，更高可靠性要求的场合。与之对应的另一个技术是同步的MySQL Cluster，但因为比较复杂，使用者较少。

Replication原理
MySQL的Replication是一个异步的复制过程，从一个MySQL节点（称之为Master）复制到另一个MySQL节点（称之Slave）。在Master与Slave之间的实现整个复制过程主要由三个线程来完成，其中的两个线程（SQL线程和IO线程）在Slave端，另外一个线程（IO线程）在Master端。

要实现MySQL的Replication，首先必须打开Master端的Binlog功能，因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

二、配置主从同步

此处输入图片的描述

1、主服务器

特别提示：

一般常规做主从复制，主从服务器多数在不同的机器上，并且监听的端口均为默认的3306；
单台服务器上的多实例情况下的步骤和过程同上；

第一步：创建一个复制用户，具有replication slave权限。

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by 'oldboy123';
#这里也可以为具体的库和表进行授权。
mysql> flush privileges;
#查看一下刚刚创建的同步账号：
mysql> show grants for rep@'172.16.1.%' \G
*************************** 1. row ***************************
Grants for rep@172.16.1.%: GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.16.1.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515'

第二步：编辑配置文件

[root@MySQL-B ~]# vim /etc/my.cnf
#首先在配置文件中查找下面的参数，并按要求修改，如果确实不存在时需要手动添加，切记配置参数不能够重复出现。
[mysqld]
server-id = 4
#注意这里的ID号（默认都是1），主从之间的ID号不能相同。
#如果主从分别是单独的服务器，那么建议使用各自的IP地址的最后一段来作为ID号，当然，如果是多实例环境，则不能这样指定。
log-bin=mysql-bin    
#去掉注释，该行默认是被注释掉的，等号后面的字符串可以自行更改。
#上面两行参数一定要确保是在“mysqld”模块下。

第三步：重启服务并检查配置结果

#重新启动MySQL服务：
[root@MySQL-B ~]# /etc/init.d/mysqld restart
#检查配置后的结果：
[root@MySQL-B ~]# egrep "log-bin|server-id" /etc/my.cnf
server-id = 4
log-bin=mysql-bin
#查看是否生成了Binlog日志文件及其索引文件：
[root@MySQL-B ~]# ll /application/mysql/data/
...
-rw-rw----  1 mysql mysql      537 Nov  3 02:51 mysql-bin.000001
-rw-rw----  1 mysql mysql     4431 Nov  3 17:23 mysql-bin.000002
-rw-rw----  1 mysql mysql     1186 Nov  3 23:00 mysql-bin.000003
-rw-rw----  1 mysql mysql     1017 Nov  4 07:30 mysql-bin.000004
-rw-rw----  1 mysql mysql       76 Nov  3 23:00 mysql-bin.index
[root@MySQL-B ~]# mysql -uroot -h 127.0.0.1 -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

第四步：设置读锁，并得到Binlog日志文件名和偏移量。

mysql> flush table with read lock;
#为数据库加锁，不能写只能读。也可以在备份时使用“-x”选项。
#这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。
#使用此命令后一定不能退出会话，如果退出会话，所有表会自动解锁。
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000004 |     1017 |              | mysql,information-schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
#上面显示的信息要记录下来，因为从库导入全备文件后，和主库进行同步复制就是要从这个位置开始。
#查看主库当前正在使用的二进制日志及当前的位置。
mysql> show master logs;       
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       537 |
| mysql-bin.000002 |      4431 |
| mysql-bin.000003 |      1186 |
| mysql-bin.000004 |      1017 |
+------------------+-----------+

#注意：锁表语句会受到下面两个时间参数的影响，如果超时，将自动解锁。
interactive_timeout    #服务器关闭交互式连接前等待活动的秒数。
wait_timeout    #服务器关闭非交互连接之前等待活动的秒数（这两个超时时间默认都是8小时）。
#默认情况下的各种超时时间：
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+

第五步：备份要同步的数据库

#新开窗口进行全备操作
[root@MySQL-B ~]# mysqldump -uroot -h 127.0.0.1 -A -B --events --master-data=2 > /opt/rep.sql
#如果这里使用-F选项，则备份后Binlog文件会被刷新（切割），因此上面记录的位置点会产生变化，但由于两的文件的两个位置点之间实际并无任何操作，因此对于主从同步不会有任何的影响。
#另外，如果数据量很大，并且允许停库，则可以停库打包，而不再使用mysqldump指令了。
#接上面，在实际生产环境中，如果需要备份的数据量很大，可能的话就需要申请停库了打包了。当然也可也夜间进行打包。

第六步：解锁

mysql>unlock tables;

2、从服务器

第一步：编辑配置文件

[root@MySQL-E ~]# vim /etc/my.cnf
#要确保从库的ID和主库的不同，我们这里仍旧使用IP地址的最后一段作为ID号。
server-id = 20
#检查从库的配置：
[root@MySQL-E ~]# egrep "log-bin|server-id" /etc/my.cnf
server-id = 20
#log-bin=mysql-bin
#从库的Binlog日志功能无需开启。但是如果从库上仍然要进行备份，或者该从库要为后面的从库充当主库（级联），则需要开启。

第二步：重启服务
第三步：导入主库上的全备文件

[root@MySQL-E ~]# mysql -uroot -h 127.0.0.1 < /opt/rep.sql
#这时可以在从库上查看导入的备份数据：
mysql> show databases;

第四步：对从数据库进行相应设置
这里需要根据在主库上执行show master status所得到的Binlog日志文件名及位置点，在从库上执行CHANGE MASTER TO语句。

CHANGE MASTER TO
MASTER_HOST='172.16.1.4',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=1017;
#注意：等号两边一定不能出现空格。

可以查看下从库的master.info文件：

[root@MySQL-E ~]# ll /application/mysql/data/
...
-rw-rw----. 1 mysql mysql       79 Nov  5 07:08 master.info
[root@MySQL-E ~]# cat /application/mysql/data/master.info 
18
mysql-bin.000004
1017
172.16.1.4
rep
oldboy123
3306
...

第五步：开起同步开关

mysql> start slave;
#这时可以查看下线程状态，关于显示结果的说明，请参见相关文档。
mysql> show processlist\G

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event    #从库IO线程目前的状态。
                  Master_Host: 172.16.1.4
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004    #从库IO线程当前正在读取的主库二进制日志文件的名称。
          Read_Master_Log_Pos: 1772    #在当前的主库二进制日志中，IO线程已经读取的位置。
               Relay_Log_File: MySQL-E-relay-bin.000002    #从库SQL线程当前正在读取和执行的中继日志文件的名称。
                Relay_Log_Pos: 1008    #在当前的中继日志中，SQL线程已读取和执行的位置。
        Relay_Master_Log_File: mysql-bin.000004    #由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。
             Slave_IO_Running: Yes     #从库的IO线程是否被启动并成功地连接到主服务器上。
            Slave_SQL_Running: Yes     #从库的SQL线程是否被启动。
              Replicate_Do_DB:      #复制的库。
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1772
              Relay_Log_Space: 1166    #当前所有的中继日志的总大小。
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0       #从库落后于主库的时间（秒数），但也只是参考值，并不十分准确。
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 4

三、解读Binlog日志

查看当前Binlog日志的结尾内容：

[root@MySQL-B ~]# mysqlbinlog /application/mysql/data/mysql-bin.000004
# at 1772
# 141104  9:13:08 server id 4  end_log_pos 1853  Query  thread_id=12   exec_time=0  error_code=0
SET TIMESTAMP=1415063588/*!*/;
create database ok2
/*!*/;
#上面显示的是该二进制日志的结尾部分。
#同时我们查看下该日志的信息：
[root@MySQL-B ~]# ll /application/mysql/data/mysql-bin.000004
-rw-rw---- 1 mysql mysql 1853 Nov  4 09:13 /application/mysql/data/mysql-bin.000004
#可以看到，日志文件终止时的大小（字节数）就是最后一个事件的结束位置。

第一行的1772代表这个事件（Event）的起始位置；

第二行的141104 9:13:08代表这个事件开始执行的时间（对于主从同步来说，无论在主库的Binlog中还是从库的Relay-log（中继日志）中，值是相同的，都是指在主库上开始执行的时间），141104是指14年11月04；

接着是主库的server id；

end_log_pos指的是事件的终点（下一个事件的起始位置），注意这个值如果是在从库中的中继日志中，那这个值是直接从主库处照搬过来的，因此对于中继日志来说有可能是不准的；
注意：事件结束位置其实指的就是日志终止时的字节数。

thread_id就是执行这个Event的线程ID；

exec_time对于主库的Binlog来说是执行这个Event所花费的时间，而对于从库的中继日志来说，这个值是从库执行这个Event结束的时间减去主库开始执行这个Event的时间，因此在一些情况下，我们还能根据这个值大概分析出从库落后于主库的时间；

error_code为错误码。

实例（同上）：

#当前
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000004 |     1853 |              | mysql,information-schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
#同时查看下该日志文件本身的大小：
[root@MySQL-B ~]# ll /application/mysql/data/mysql-bin.000004   
-rw-rw---- 1 mysql mysql 1853 Nov  4 09:13 /application/mysql/data/mysql-bin.000004
#其实可以看到，主库上所谓的位置（Position），其实就是当前日志的大小，之后所有操作的起始点也就是这个数值。
#此时查看下Binog日志的结尾部分：
[root@MySQL-B ~]# mysqlbinlog /application/mysql/data/mysql-bin.000004
# at 1772
# 141104  9:13:08 server id 4  end_log_pos 1853  Query  thread_id=12   exec_time=0  error_code=0
SET TIMESTAMP=1415063588/*!*/;
create database ok2
/*!*/;
DELIMITER ;
# End of log file

注意：上面日志中最后一个事件的end_log_pos即表示日志结束时的大小，也就是下个事件的起始位置。

四、主从同步的要点

主从同步要点：

采用异步方式同步；

逻辑同步模式，多种模式，默认是通过SQL语句执行；

主库通过记录Binlog实现对从库的同步；

主库一个线程，从库两个线程；

从库关键文件中除了中继日志（hostname-relay-bin.00000x）外，还包括master.info以及relay-log.info两个重要的信息文件。

另外需要注意，如果从库还想做级联，就需要打开Binlog功能，同时还要添加下面的参数：

log-slave-update

测试