mysql主从复制配置小记

mysql主从数据库备份配置过程
注意：
1.mysql版本一样
2.master和slave的hostname不能相同
3.两台服务器的时间同步
4.mysql内网备份，建议关闭iptables
5.一定要打开mysql的log-bin
6.主从切换比较危险，不到万不得已不推荐执行
7.在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件


环境：centos 
数据库目录及其它
my.cnf配置文件  /usr/local/mysql/my.cnf
MySQL数据库位置  /usr/local/mysql/data/
主数据库：192.168.4.191
从数据库：192.168.4.192




1.安装略过


一、设置主库
1.修改my.cnf主要是设置个不一样的id和log-bin
vi my.cnf


expire_logs_day=10 #定时清理binlog
log-bin=binlog  #启动二进制日志系统


#binlog-do-db=game1  #二进制需要同步的数据库名
binlog-ignore-db=mysql  # 避免同步mysql用户配置，以免不必要的麻烦
log-slave-updates
slave-skip-errors #是跳过错误，继续执行复制操作  
server-id = 1  #本机数据库ID 标示为主


2、启动主库生效，并赋予从库权限帐号，允许用户在主库上读取日志
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'slave.123'; 
flush privileges;
select host,user,password from mysql.user;


3.锁主库表
flush tables with read lock;


4.记录主库信息
记下file和position，这个界面不要关闭
show master status;


5.另开一个终端，准备备份
mysqldump -uroot -p --master-data=1 --databases xxx xxx xxx > all.sql


6.解锁
unlock tables


7.将备份的数据库scp到从服务器上
scp *.sql root@192.168.7.192:~/root/mysqlback


二。设置从库
1.修改从库my.cnf
vi my.cnf
[mysqld]  
port            = 3306
socket          = /tmp/mysql3306.sock
user=mysql
datadir=/home/salve_data/
pid-file=/home/salve_data/mysql.pid
log-bin=mysql-bin-1
binlog_format=mixed
log-error=/home/salve_data/error-67.log
expire_logs_day=10
slave-skip-errors


server-id      = 2(不能与主服务器id相同,建议为末位IP)
master-host     = 192.168.4.191 (主服务器IP)
master-user     = slave
master-password = slave.123
master-port     = 3306
master-connect-retry=60
master-info-file=master-1.info
#replicate-do-db=game1  #多个DB用逗号隔开
replicate-ignore-db=mysql,information_schema
log-slave-updates


2.重启数据库
/etc/init.d/mysqld restart


3.验证连接主库
mysql -h 192.168.4.191 -uroot  -p
show grants for slave@192.168.4.191;


4.给scp过来的sql文件权限
chown -R mysql.mysql /root/mysqlback


5.同步
stop slave;
change master to master_host='192.168.4.191',master_user='slave',master_password='slave.123',master_port=3306,master_log_file='binlog.000004',master_log_pos=98;
注：master_log_file='binlog.000003',master_log_pos=98根据刚才主服务器记录的数据修改一下
start slave;
show slave status\G;
如果Slave_IO_Running、Slave_SQL_Running状态为Yes则表明设置成功。


6.测试
 create database hi_db;
 use hi_db;
 create table hi_tb(id int(3),name char(10));
 insert into hi_tb values(001,'bobu');
 show databases;
在从表中马上看到了效果，主从同步成功了；
为了更进一步验证在从库上输入
show slave status\G;
可以查看Slave_IO_Running、Slave_SQL_Running状态是否为yes


show full processlist；观察其状态


三。常见问题处理方法
1：在从库上面show slave status\G;出现下列情况，
          Slave_IO_Running: Yes
          Slave_SQL_Running: No
          Seconds_Behind_Master: NULL
 
原因：
a.程序可能在slave上进行了写操作
b.也可能是slave机器重起后，事务回滚造成的.
备注：在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件
 
解决方法：
进入master
mysql> show master status;
然后到slave服务器上执行手动同步
slave stop;
change master to 
master_host='192.168.1.191',
master_user='slave',
master_password='slave.123',
master_port=3306,
master_log_file='mysql-bin.000040',
master_log_pos=324;
slave start;
show slave status\G;


2、现象：从数据库无法同步，show slave status显示Slave_IO_Running为No,Seconds_Behind_Master
为null.出现此问题，必须要要重启master数据库
 
       解决：重启主数据库
 
             service mysql restart 
 
             mysql> show master status;
在从库上  
slave stop;
change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98
slave start;
或是这样：
stop slave;
set global sql_slave_skip_counter =1;
start slave;  


3.如果主MySQL发生错误，如何做主从切换呢？
1、保证所有从数据库都已经执行了relay log中的全部更新，在从服务器中执行stop slave io_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成
stop slave io_thread;
#确保Has read allrelay log
show processlist;
2、在从服务器上执行stop slave，reset master命令，重置成主数据库
stop slave;
reset master;
3、删除新的主服务器数据库目录中的master.info和relay-log.info文件，否则下次重启时还会按照从服务器来启动


4.：“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'”等或由于清数据导致主从库不同步了，解决办法如下： 
先进入slave中执行:"slave stop;"来停止从库同步； 
再去master中执行:"flush logs;"来清空日志； 
然后在master中执行:"show master status;"查看下主库的状态，主要是日志的文件和position； 
然后回到slave中，执行:"CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000004',MASTER_LOG_POS=106;"，文件和位置对应master中的; 
最后在slave中执行:"slave start;"来启动同步。


5、在master上删除一条记录，在slave上找不到。
Last_SQL_Error.Could not execute Delete_rows event on table ….Error_code:1032……
解决： master要删除一条记录，而slave上找不到相应记录而报错。Master上已经删除，slave 并没有改记录，可以直接跳过：
Stop slave ; set global sql_slave_skip_counter=1;start slave;


6、主键冲突，在slave已经有该记录，     又在master上插入了同一条记录
Last_SQL_Error.Could not execute Write_rows event on table…… Error_Code :1062……
解决：对于重复的记录，需要删除重复的键值。！


7、在master上更新一条记录，在slave上找不到数据
Last_SQL_Error.Could not execute Update_rows event …… Error_Code: 1032…
解决：master更新的记录，在slave上找不到。通过 Mysqlbinlog 分析出真实的语句，将slave缺少的记录进行手动填充。！


8、slave中继日志损坏
Last_SQL_Error:Error initializing relay log postion: I/O error reading the header from the binary log
Last_SQL_Error:Error initializing relay log positon:Binlog has bad magic number:it’s not a binary log file that can be used by this version of MySQL.
Slave 宕机或非法关机，电源故障、硬件故障，造成中继日志损坏
找到salve 同步执行到master的位置。重新做同步。
        查找变量：
        Relay_Master_Log_File:
        Exec_Master_Log_Pos:
        从这两个位置重新同步。