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:         从这两个位置重新同步。