mysql 基于库同步_基于mysql5.6版本的主从库同步

系统：centos6.4

mysql版本：5.6.17

主库：192.168.31.111 从库：192.168.31.235

主库操作：

1、配置my.cnf文件开启二进制日志

log_bin = on

server_id = 1

2、建立用于同步数据库的账号rep

grant replication slave on *.* to 'rep'@'192.168.31.%' identified by 'redhat';

select user,host,password from mysql.user;

3、将主库进行锁表只读，完全备份主库

mysql>flush tables with read lock;

mysql -uroot -p -e "show master status;" > /backup/mysql_bak.log：记录二进制日志文件的位置

接下来就是完全备份数据库：

mysqldump -uroot -p -A -B |gzip > /backup/mysql_bak.`date +%F`.sql.gz

然后接触表的锁定

mysql>unlock tables;

######################################################################################

从库操作：

1、将备份的主库数据用scp传到从库的/tmp目录下

scp /backup/mysql_bak.`date +%F`.sql.gz 192.168.31.235:/tmp/

2、设置从库的server_id

server_id = 2

3、解压数据并将数据进行还原到从库上

gzip -d mysql_bak.`date +%F`.sql.gz

mysql

4、然后执行master语句

mysql>mysql>CHANGE MASTER TO MASTER_HOST='192.168.31.111',MASTER_USER='rep',MASTER_PASSWORD='redhat',MASTER_LOG_FILE='on.000004',MASTER_LOG_POS=120;

mysql>start slave;

mysql>show slave status\G

当从库的状态信息中出现了以下两个yes：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

那么主从同步也基本成功了，附上成功截图：

另：附上前几次次的失败的日志:cat /data/$hostname.err

2016-04-17 10:38:40 33785 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2016-04-17 10:38:40 33785 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2016-04-17 10:38:40 33785 [Note] Slave SQL thread initialized, starting replication in log 'on.000004' at position 382, relay log './nginx-relay-bin.000001' position: 4

2016-04-17 10:38:40 33785 [Note] Slave I/O thread: connected to master 'rep@192.168.31.111:3306',replication started in log 'on.000004' at position 382

2016-04-17 10:38:40 33785 [ERROR] Error reading packet from server: Misconfigured master - server_id was not set ( server_errno=1236)

2016-04-17 10:38:40 33785 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server_id was not set', Error_code: 1236

2016-04-17 10:38:40 33785 [Note] Slave I/O thread exiting, read up to log 'on.000004', position 382

由上面日志错误信息知道Misconfigured master - server_id was not set，server_id没有设置，于是检查主从库的server_id

于是进行第二次尝试结果还是报错：

2016-04-17 11:00:03 33785 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'on.000004' at 330, the last event read from './on.000004' at 330, the last byte read from './on.000004' at 349. ( server_errno=1236)

2016-04-17 11:00:03 33785 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'on.000004' at 330, the last event read from './on.000004' at 330, the last byte read from './on.000004' at 349.', Error_code: 1236

2016-04-17 11:00:03 33785 [Note] Slave I/O thread exiting, read up to log 'on.000004', position 330

由日志信息了解到二进制日志的position位置不对导致，于是在主库上重新来一次

show master status;得到正确的position

然后再次启动start slave;

接下来就成功了

注意：当尝试不成功时，一定要查看错误日志信息，error的地方