mysql 主从与binlog

一 主从的原理

Mysql的 Replication 是一个异步的复制过程，从一个 Mysql instace(Master)复制到另一个Mysql instance(Slave)。 
 
在Master 与 Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master 端。

#slave  端，注意红色行

1. Slave_IO_State: Waiting for master to send event 
2.                   Master_Host: 192.168.57.73 
3.                   Master_User: dongnan
   
4.                   Master_Port: 3306 
5.                 Connect_Retry: 60 
6.               Master_Log_File: mysql-bin.000052 
7.           Read_Master_Log_Pos: 129990070 
8.                Relay_Log_File: zabbix-slave-relay-bin.000110 
9.                 Relay_Log_Pos: 129990215 
10.         Relay_Master_Log_File: mysql-bin.000052 
11.              Slave_IO_Running: Yes 
12.             Slave_SQL_Running: Yes 

#master 端，注意红色行

1. show processlist \G; 
2. *************************** 12. row *************************** 
3.      Id: 81 
4.    User: dongnan
   
5.    Host: 192.168.57.82:57965 
6.      db: NULL 
7. Command: Binlog Dump 
8.    Time: 872357 
9.   State: Has sent all binlog to slave; waiting for binlog to be updated 
10.    Info: NULL
    
11. 部分略
    

要实现 MySQL 的 Replication ，首先必须打开 Master 端的BinaryLog(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。 
打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用“--log-bin” 参数选项，或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加“log-bin” 参数项。

#my.conf 文件

1. grep -E '(mysql-bin)|(id)' /etc/my.cnf | grep -v '^#' 

2. log-bin=mysql-bin 

3. server-id   = 1 

4. log-bin=/usr/local/mysql/var/mysql-bin.000001 

 
MySQL 复制的基本过程如下：

1. Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 BinaryLog 中的位置;

#master 端 mysql-bin.index 文件

1. mysql的binlog记录了数据库的所有操作，mysql-bin.index记录了所有mysql-bin的名字。
2. # tail /usr/local/mysql/var/mysql-bin.index  
3. /usr/local/mysql/var/mysql-bin.000043 
4. /usr/local/mysql/var/mysql-bin.000044 
5. /usr/local/mysql/var/mysql-bin.000045 
6. /usr/local/mysql/var/mysql-bin.000046 
7. /usr/local/mysql/var/mysql-bin.000047 
8. /usr/local/mysql/var/mysql-bin.000048 
9. /usr/local/mysql/var/mysql-bin.000049 
10. /usr/local/mysql/var/mysql-bin.000050 
11. /usr/local/mysql/var/mysql-bin.000051 
12. /usr/local/mysql/var/mysql-bin.000052 

 
3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的RelayLog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master,需要从某个bin-log的哪个位置开始往后的日志内容

#slave io 操作

1. mysql> show slave status \G; 
2. *************************** 1. row *************************** 
3.                Slave_IO_State: Waiting for master to send event
4.                   Master_Host: 192.168.57.73 
5.                   Master_User: dongnan 
6.                   Master_Port: 3306 
7.                 Connect_Retry: 60 
8.               Master_Log_File: mysql-bin.000052 
9.           Read_Master_Log_Pos: 132841140 
10.                Relay_Log_File: zabbix-slave-relay-bin.000110
11.                 Relay_Log_Pos: 132841285 
12.         Relay_Master_Log_File: mysql-bin.000052 
13.              Slave_IO_Running: Yes 
14.             Slave_SQL_Running: Yes 

# slave 端 master.info 文件

1. # cat /usr/local/mysql/var/master.info  
2. 15 
3. mysql-bin.000052 
4. 137208732 
5. 192.168.57.73 
6. dongnan
7. password
   
8. 3306 
9. 60 
10. 0 
11.  
12.  
13.  
14.  
15.  
16. 0 

4.Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query

默认中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。 用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为 host_name-relay-bin.index。在默认情况，这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同，并且可以用 mysqlbinlog读取。当SQL线程执行完中继日志中的所有事件后，中继日志将会被自动删除。

# slave 端 relay-log.info 文件

1. # cat /usr/local/mysql/var/relay-log.info  
2. ./zabbix-slave-relay-bin.000110   #slave偏移值
3. 137542772 
4. mysql-bin.000052 
5. 137542627 

# slave 端 relay-bin.index 文件

1. # cat /usr/local/mysql/var/zabbix-slave-relay-bin.index  
2. ./zabbix-slave-relay-bin.000109 
3. ./zabbix-slave-relay-bin.000110 

二 主从的配置

1 master 端 编辑 /etc/my.cnf

1. my.cnf 
2. server-id　=　1　 
3. log-bin=/usr/local/mysql/var/mysql-bin.000001　 
4. #binlog-do-db　=　zabbix　 
5. binlog-ignore-db　=　mysql　 
6. binlog-ignore-db　=　test　 
7. binlog-ignore-db　=　information_schema 
8.  
9. mysql>grant replication slave on *.* to 'rep'@'%' identified by 'password';   #授权用户 
10. mysql> show master status\G;                                                 #binlog-id,pos号码 
11. File: mysql-bin.000011 
12.         Position: 490 

2 slave 端

1. my.conf       #编辑my.cnf 
2. server-id　=　2 
3.  
4. mysql>change master to master_host='192.168.56.10',master_user='rep',master_password='password',master_log_file='mysql-bin.000011',master_log_pos=490; 
5. mysql> start slave; 
6. mysql> show slave status\G; 
7.             Slave_IO_Running: Yes 
8.             Slave_SQL_Running: Yes 

注：如果主库已经有数据了，那么可能需要打包数据到从库上，再执行第二步，如下！

1. 主库相关操作 
2. 1 flush tables with read lock;    //主库上锁表 
3. 2 show master status;             //记录 master log file及file position 
4. +--------------------------+----------------+-------------------+------------------------+ 
5. | File                          | Position       | Binlog_Do_DB | Binlog_Ignore_DB | 
6. +--------------------------+----------------+--------------------+-----------------------+ 
7. | xxxx-log.000031     | 199039244     | xxxx                   |                              |  
8. +--------------------------+----------------+--------------------+-----------------------+ 
9.  
10. 3 另一个窗口
11. tar -czvf zabbix.tar.gz zabbix/  //打包数据文件 
12.  
13.  
14. 从库相关操作 
15. 1.停止从库，将主库数据库文件copy到从库中 ,并解压缩 tar xzf /root/zabbix.tar.gz 
16. 2.启动从库 
17. 3.stop slave; 
18. 4.reset slave; 
19. 5.change master to master_host='192.168.6.53', master_user='dongnan', master_password='password', master_port=3306, master_log_file='mysql-bin.000013', master_log_pos=772985;  
20. 5.start slave; 
21.  
22. 主库再次操作 
23. unlock tables;   //主库表解锁
    
24.  
25.  
26.  
27. 验证主从： 
28. //登陆从库 
29. slave status\G; 
30.  
31. Slave_IO_Running: Yes 
32. Slave_SQL_Running: Yes 
33. 上面2项都为'Yes'，表示slave正常 

设置主从需要的一些命令

1. start slave；  #启动复制线程 
2.  
3. stop slave；   #停止复制线程 
4.  
5. reset slave；  #重置复制线程 
6.  
7. change master to； #动态改变到主服务器的配置
   
8. 
   
9. show master status \G;  #显示master 状态
   
10. 
    
11. show slave status \G;   #显示slave 状态
    
12. 
    
13. show processlist \G;    #显示进程列表 

#update 20120910 打包多个数据库

1. mkdir rep && cd rep
2. mysql -uroot -ppassword -e 'show databases;' | awk '$1 !~ /Database|information_schema|test|mysql/ {print}' |\
3. while read line;do tar czfP $line.tar.gz -C /usr/local/mysql/var/ "$line" && sleep 1;done 

1. ls 
2. 1javacms.tar.gz  cactidb.tar.gz  cacti.tar.gz  nconf.tar.gz  phpwind.tar.gz  ultrax.tar.gz  wordpress.tar.gz 

#update 20120925 打包多个数据库，不同的sql 引擎

//例如 myisam 与 innodb

//适合小型数据库<=10GB

1. mysql -uroot -ppassword -e 'show databases;' | awk '$1 !~ /Database|information_schema|test|mysql/ {printf $1 " "} END {printf "\n"}' |\
2. while read line; do mysqldump -uroot -ppassword --opt -e -x --master-data=2 --add-drop-database --database $line > segment.sql;done 

//sql文件中包含了 master_log_file 与 master_log_pos 这两个我们最关心的参数。

//这归功于 --master-data=2 参数 ,-x 锁住所有表 --database 要导出的库

1. awk '/CHANGE/' segment.sql  

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1464102;

本文转自 dongnan 51CTO博客，原文链接：http://blog.51cto.com/dngood/711031