MySQL主从同步复制for Debian OS

MySQL复制简述

　　MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器上已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时，它通知主服务器定位到从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。（详细原理见官网和google、百度^_^，现在开工……）

1、测试环境说明
主DB服务器IP地址：192.168.113.9
从DB服务器IP地址：192.168.113.8
System OS: Debian 5.0.7-32bit
MySQL Vsertion: 5.0.51a-24+lenny5-log (Debian)
#apt-get install mysql-server mysql-client
2、主DB服务器上操作说明
(1)创建同步用户名和被同步库
mysql> grant replication slave on *.* to 'repuser'@'192.168.113.8' identified by '111111';
mysql> flush privileges;

mysql> create database testdb;
mysql> use testdb;
mysql> create table testuser(id int(4),name varchar(20));
mysql> insert into testuser values(1,'test1');
mysql> flush privileges;
mysql> select * from testuser;
+------+-------+
| id   | name  |
+------+-------+
|    1 | test1 |
+------+-------+  1 row in set (0.00 sec)
 

(2)备份被同步数据库到从DB服务器上
mysql>flush tables with read lock;  //设置读锁
mysql>show master status;           //得到binlog日志文件名和偏移量
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | testdb       | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>show master status;  //分行显示主数据库的状态
mysql>show master logs;   //显示主数据库日志
# mysqldump -uroot -p testdb >/home/testdb.sql //方法1：备份要同步的testdb数据库到从DB服务器上还原
#cd /var/lib/mysql                             //方法2：解压到从DB服务器/var/lib/mysql/
#tar zcvf testdb.tar.gz testdb/
#scp -22 testdb.tar.gz root@192.168.113.8:/home/ 
mysql> unlock tables;   //解锁
 

（3）配置主DB的my.cnf文件
#cd /etc/mysql
#cp my.cnf my.cnf_back
#vim my.cnf
bind-address            = 127.0.0.1  //找到后在其前添加 # 注释掉本行
//找到 #server-id 修改如下内容为：
server-id               = 1  //唯一标识，只要不和从服务器相同就行了
log_bin                 = /var/log/mysql/mysql-bin.log //必须开启这里，告诉主服务器以二进制日志,要mysql用户权限
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = testdb   //要同步的数据库，多个数据库可用逗号分隔或者可写多行
binlog_ignore_db       = mysql    //忽略不同步的数据库，多可写多行
#/etc/init.d/mysql restart

3、从DB服务器上操作说明
（1）mysql -u repuser -p -h 192.168.113.1   //测试主DB上创建的repuser账号时候可以远程连接主DB
（2）配置从DB的my.cnf文件
#cd /etc/mysql
#cp my.cnf my.cnf_back
#vim my.cnf      //找到 #server-id 修改如下内容为：
server-id               = 2
master-host             = 192.168.113.9   //主DB服务器IP地址
master-user             = repuser
master-password         = 111111
master-port             = 3306
master-connect-retry    = 15
replicate-do-db         = testdb   //要同步的数据库，多个数据库可用逗号分隔或者可写多行
replicate-ignore-db     = mysql
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
 

（3）调试从DB
#/etc/init.d/mysql restart
mysql> start slave; //启动复制线程，stop slave 停止复制线程
mysql> show processlist;
 State: Has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL
 //表示slave已经连接上master，开始接受并执行日志
mysql> show slave status\G;   //分行显示复制线程状态
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
//查看以上两项的值，均为Yes则表示状态正常。

4、常见问题
（1）（1）从库无法复制 Slave_SQL_Running 为No，Seconds_Behind_Master:Null
 原因：1）有程序可能在从DB上进行写操作；
      2）可能是从DB服务器重启，事物回滚造成；
 解决：1）mysql> slave stop;
               mysql> set GLOBAL SQL_SLAVE_AKIP_COUNTER=1;
                mysql> slave start;
           2）手动同步先停止master的写操作
                mysql>show master status;         
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | testdb       | mysql            |
+------------------+----------+--------------+------------------+
然后到从库上手动同步
mysql> change master to
>master_host=’192.168.113.9’,
>master_user=’’repuser,
>master_password=’111111’,
>master_porte=’3306’,
>master_log_file=’ mysql-bin.000001’,
>master_log_pos=98;
mysql> slave start

（2）从库无法复制 Slave_IO_Running为No，Seconds_Behind_Master:Null
 解决：1）测试与主DB网络3306端口的连通性

       2）重启主DB MySQL---mysql> show master status;---找到对应的File字段内容
       3）从DB上 mysql> stop slave;
       mysql> change master to master_host='192.168.113.9',master_user='repuser',master_password='111111',master_port=3306,Master_Log_File='mysql-bin.000001',master_Log_Pos=98;
       mysql> start slave;