mysql 主从与binlog

mysql 主从与binlog
2011-11-10 14:50:25
标签：linux binlog mysql 主从 数据库
原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://dngood.blog.51cto.com/446195/711031
一 主从的原理
Mysql的 Replication 是一个异步的复制过程，从一个 Mysql instace(Master)复制到另一个Mysql instance(Slave)。 
 
在Master 与 Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master 端。
#slave  端，注意红色行
Slave_IO_State: Waiting for master to send event 
                  Master_Host: 192.168.57.73 
                  Master_User: dongnan 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000052 
          Read_Master_Log_Pos: 129990070 
               Relay_Log_File: zabbix-slave-relay-bin.000110 
                Relay_Log_Pos: 129990215 
        Relay_Master_Log_File: mysql-bin.000052 
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes 
#master 端，注意红色行
show processlist \G; 
*************************** 12. row *************************** 
     Id: 81 
   User: dongnan 
   Host: 192.168.57.82:57965 
     db: NULL 
Command: Binlog Dump 
   Time: 872357 
  State: Has sent all binlog to slave; waiting for binlog to be updated 
   Info: NULL 
部分略


要实现 MySQL 的 Replication ，首先必须打开 Master 端的BinaryLog(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。 
打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用“--log-bin” 参数选项，或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加“log-bin” 参数项。
#my.conf 文件
grep -E '(mysql-bin)|(id)' /etc/my.cnf | grep -v '^#' 
log-bin=mysql-bin 
server-id   = 1 
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 文件
mysql的binlog记录了数据库的所有操作，mysql-bin.index记录了所有mysql-bin的名字。
# tail /usr/local/mysql/var/mysql-bin.index  
/usr/local/mysql/var/mysql-bin.000043 
/usr/local/mysql/var/mysql-bin.000044 
/usr/local/mysql/var/mysql-bin.000045 
/usr/local/mysql/var/mysql-bin.000046 
/usr/local/mysql/var/mysql-bin.000047 
/usr/local/mysql/var/mysql-bin.000048 
/usr/local/mysql/var/mysql-bin.000049 
/usr/local/mysql/var/mysql-bin.000050 
/usr/local/mysql/var/mysql-bin.000051 
/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 操作
mysql> show slave status \G; 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  Master_Host: 192.168.57.73 
                  Master_User: dongnan 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000052 
          Read_Master_Log_Pos: 132841140 
               Relay_Log_File: zabbix-slave-relay-bin.000110 
                Relay_Log_Pos: 132841285 
        Relay_Master_Log_File: mysql-bin.000052 
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes 
# slave 端 master.info 文件
# cat /usr/local/mysql/var/master.info  
15 
mysql-bin.000052 
137208732 
192.168.57.73 
dongnan
password 
3306 
60 
0 
 
 
 
 
 
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 文件
 
# cat /usr/local/mysql/var/relay-log.info  
./zabbix-slave-relay-bin.000110   #slave偏移值
137542772 
mysql-bin.000052 
137542627 
# slave 端 relay-bin.index 文件
# cat /usr/local/mysql/var/zabbix-slave-relay-bin.index  
./zabbix-slave-relay-bin.000109 
./zabbix-slave-relay-bin.000110 
 
二 主从的配置
1 master 端 编辑 /etc/my.cnf
my.cnf 
server-id　=　1　 
log-bin=/usr/local/mysql/var/mysql-bin.000001　 
#binlog-do-db　=　zabbix　 
binlog-ignore-db　=　mysql　 
binlog-ignore-db　=　test　 
binlog-ignore-db　=　information_schema 
 
mysql>grant replication slave on *.* to 'rep'@'%' identified by 'password';   #授权用户 
mysql> show master status\G;                                                 #binlog-id,pos号码 
File: mysql-bin.000011 
        Position: 490 
2 slave 端
my.conf       #编辑my.cnf 
server-id　=　2 
 
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; 
mysql> start slave; 
mysql> show slave status\G; 
            Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes 
注：如果主库已经有数据了，那么可能需要打包数据到从库上，再执行第二步，如下！
主库相关操作 
1 flush tables with read lock;    //主库上锁表 
2 show master status;             //记录 master log file及file position 
+--------------------------+----------------+-------------------+------------------------+ 
| File                          | Position       | Binlog_Do_DB | Binlog_Ignore_DB | 
+--------------------------+----------------+--------------------+-----------------------+ 
| xxxx-log.000031     | 199039244     | xxxx                   |                              |  
+--------------------------+----------------+--------------------+-----------------------+ 
 
3 另一个窗口 
tar -czvf zabbix.tar.gz zabbix/  //打包数据文件 
 
 
从库相关操作 
1.停止从库，将主库数据库文件copy到从库中 ,并解压缩 tar xzf /root/zabbix.tar.gz 
2.启动从库 
3.stop slave; 
4.reset slave; 
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;  
5.start slave; 
 
主库再次操作 
unlock tables;   //主库表解锁 
 
 
 
验证主从： 
//登陆从库 
slave status\G; 
 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
上面2项都为'Yes'，表示slave正常 
 
设置主从需要的一些命令
start slave；  #启动复制线程 
 
stop slave；   #停止复制线程 
 
reset slave；  #重置复制线程 
 
change master to； #动态改变到主服务器的配置 


show master status \G;  #显示master 状态 


show slave status \G;   #显示slave 状态 


show processlist \G;    #显示进程列表 
 
#update 20120910 打包多个数据库
mkdir rep && cd rep
mysql -uroot -ppassword -e 'show databases;' | awk '$1 !~ /Database|information_schema|test|mysql/ {print}' |\
 while read line;do tar czfP $line.tar.gz -C /usr/local/mysql/var/ "$line" && sleep 1;done 


ls 
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
mysql -uroot -ppassword -e 'show databases;' | awk '$1 !~ /Database|information_schema|test|mysql/ {printf $1 " "} END {printf "\n"}' |\
 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 要导出的库
awk '/CHANGE/' segment.sql  
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1464102;
 
 
本文出自 “dongnan” 博客，请务必保留此出处http://dngood.blog.51cto.com/446195/711031