MySQL数据库主从

基本环境

主数据库：192.168.2.222
从数据库：192.168.3.202
操作系统：centos 6.5

my.cnf配置文件 /etc/my.cnf
mysql数据库位置 datadir=/var/lib/mysql

设置主库

1、修改主库my.cnf,主要是设置个不一样的id和logbin（#这部可依具体环境而定，压力大的化可采用huge.cnf）

[root@localhost etc]#vi /etc/my.cnf
# 记住这部分一定要配置在[mysqld]后面，否则无法找到从节点，各个配置项的含义可自己查阅文档
[mysqld]
log-bin=mysql-bin # mysql日志，从数据库根据日志进行同步
server-id=1 # 与从数据库的值不能一样
binlog-ignore-db=information_schema # 不写入binlog的数据库
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=gyxshop # 写入binlog的数据库

2、主库用户授权

[root@localhost etc]service mysqld restart
[root@localhost etc]mysql -u root -p
mysql> grant all privileges on *.* to 'root'@'%' identified by '111111';  #赋予从库权限帐号，允许所有
flush privileges;

也可用下面命令，只授权读取日志

grant replication slave,file on *.* to 'root'@'%' identified by '111111';
flush privileges;

mysql> select user,host from mysql.user; #检查创建是否成功

3、锁主库表 (用于同步数据)

mysql> flush tables with read lock; #锁主库表，数据库只读

4、显示主库信息
记录File和Position，从库设置将会用到

mysql> show master status;

结果如下：

+------------------+----------+--------------+----------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 |
+------------------+----------+--------------+----------------------------------+
| mysql-bin.000005 |    35549 |              | information_schema,cluster,mysql |
+------------------+----------+--------------+----------------------------------+
1 row in set (0.00 sec)

说明，如果执行这个步骤始终为Empty set(0.00 sec)，那说明前面的my.cnf没配置对。

5、主库数据库复制到从库
这一步完成后，才可以解锁

6、主库解锁

mysql> unlock tables; 

设置从库

1、在202节点上修改从库my.cnf

[root@localhost etc]#vi /etc/my.cnf
# 记住这部分一定要配置在[mysqld]后面，否则无法找到从节点，各个配置项的含义可自己查阅文档

添加或修改为如下内容：

[mysqld]
log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=gyxshop #从binlog中同步哪些库
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
master-host=192.168.2.222
master-user=root
master-password=111111

2、在202节点从库上设置同步
设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position
注意下面第二条命令语句中的master_log_file=’mysql-bin.000005’, master_log_pos=35549;对应为前面在主库中执行的show master status;结果

mysql> slave stop;
mysql> change master to master_host='192.168.2.222',master_user='gyxshop',master_password='111111',master_log_file='mysql-bin.000005', master_log_pos=35549;
mysql> slave start;

3、进行测试
在主库上做些事务性（如：增删改）操作，看从库上是否同步

在从表中马上看到了效果，主从同步成功了；
进一步验证：

mysql> show slave status\G

可以查看相关信息

总结

1、MYSQL主从同步的原理
（1） 主服务器验证连接。
（2） 主服务器为从服务器开启一个线程。
（3）从服务器将主服务器日志的偏移位告诉主服务器。
（4） 主服务器检查该值是否小于当前二进制日志偏移位。
（5）如果小于，则通知从服务器来取数据。
（6）从服务器持续从主服务器取数据，直至取完，这时，从服务器线程进入睡眠，主服务器线程同时进入睡眠。
（7）当主服务器有更新时，主服务器线程被激活，并将二进制日志推送给从服务器，并通知从服务器线程进入工作状态。
（8）从服务器SQL线程执行二进制日志，随后进入睡眠状态。

2、第一次将数据从主库导入从库的步骤
（1）先锁主库，并记下master_log_file与master_log_pos的值
（2）将主库的数据导入从库
（3）主库解锁
从而当配置完成后，从库能根据master_log_file与master_log_pos的值与主库进行增量数据的同步

3、my.cnf配置文件注意
binlog-ignore-db=cluster #不写入文件的库
binlog-do-db=mysql #写入文件的库
replicate-ignore-db=mysql #不读取文件的库
replicate-do-db=gyxshop #读取文件的库
这里的配置生效是建立在连接上的验证，遇到跨库更新时将不生效。
所以在使用的时候，一定要严格遵循规范。

4、主从复制跳过错误
需要对错误进行分析，如果有少量数据错误，可以人工修整错误数据，然后跳过指定数量的事务；
如果数据错误数量较多，或者数据没法人工修整，则考虑重新配置MySQL主从或者主主。
（1）跳过指定数量的事务：

mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;        #跳过一个事务
mysql>slave start;

（2）修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf#修改配置文件

[mysqld]
#slave-skip-errors=1062,1053,1146#跳过指定error no类型的错误(慎用)
#注意1062为主键冲突
#slave-skip-errors=all#跳过所有错误(不要用)

5、MySql同步监控脚本

#/bin/sh
user=repl
passwd=123415
master_ip="192.168.1.2"
log="/data3/check_repl.log"
value()
{
 master=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h$master_ip -e "show master status\G;"|egrep "File|Position"`
 #mysql 4.0
 slave=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h127.0.0.1 -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_master_log_pos"`
 #mysql 5.0
 #slave=`mysql -u$user -p$passwd -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_Master_Log_Pos"`
 #取主库上的bin-log号及写入的当前日志位置   
 Master_Log=`echo $master |awk '{print $2}'|awk -F "." '{print $2}'`
 Master_Log_Pos=`echo $master |awk '{print $4}'`
 #取从库上当前同步主库的位置
 Relay_Master_Log_File=`echo $slave |awk '{print $2}'|awk -F "." '{print $2}'`
 Exec_Master_Log_Pos=`echo $slave |awk '{print $4}'`
 echo "Master_Log:"$Master_Log>>$log
 echo "Master_Log_Pos:"$Master_Log_Pos>>$log
 echo "Relay_Master_Log_File:"$Relay_Master_Log_File>>$log
 echo "Exec_Master_Log_Pos:"$Exec_Master_Log_Pos>>$log
}
for((i=1;i<=10;i++));
do
 echo "#################################">>$log
 value
 time=`date +"%Y-%m-%d %H:%M:%S"`
 if [ $Master_Log -eq $Relay_Master_Log_File ];then
       A=`expr $Master_Log_Pos - $Exec_Master_Log_Pos`
       if [ $A -lt 0 ];then
             A=`expr 0 - $A`
       fi
       echo $A>>$log
       if [ $A -lt 10000 ];then
             echo "$time Master-Slave is OK.">>$log
             #echo "$i"
             break
       else
             if [ $i ge 3 ];then              
                  echo "$time Warning:Slave-Master lag $A " >>$log
                  echo "$i"
             fi
             sleep 30
             continue
       fi
 else
       sleep 60
       fi
       if [ $i -eq 10 ];then
             echo "$i"
             echo "$time Error:Slave-Master must be check !" >>$log
       fi
done

参考：http://wangwei007.blog.51cto.com/68019/965575

几个常用命令

show master status\G # 查看状态
show binlog events in ‘binlog.000001’; #查看日志

待思考问题

1、主从数据库，如果某个重启了，怎么恢复
在做实验的过程中，发现配置好后的从库service mysqld stop;后无法启动，这里可能的原因是：数据已经不与主库一致了，需要重新进行数据的同步。

主主没有这个问题，当服务重启后，会自动进行同步，考虑是否是配置出现问题。

2、一主多从，主主备份的方式如何使用
MySQL主主见另外一片博客

3、如何从log-bin中恢复数据库
思路：先找到以前备份过的一个版本，然后将log-bin文件导出成sql文件，解决好编码问题后，执行sql语句。

mysqlbinlog  --start-date="2013-10-01 00:00:00" --stop-date="2013-12-12 12:00:00" E:\mysql-5.5.21-win32\data\mysql-bin.000067 > e:\67.sql
#将bin-log文件导出成sql文件，日期参数是导出这段时间内的数据