MYSQL 主从复制（NIOT）

一、主数据库操作设置（A）

1、修改配置文件，vim /etc/my.cnf，然后重启mysqld，/etc/init.d/mysqld restart

[mysqld]<要在mysqld中括号下修改>
server-id = 6
auto_increment_increment = 2
auto_increment_offset = 1
log-bin=mysql-bin
binlog_format=mixed
binlog-do-db=db_dns
binlog-ignore-db=mysql
binlog-ignore-db=data_center

下面二项需要在Master上设置：

　　Binlog_Do_DB:设定哪些数据库需要记录Binlog

　　Binlog_Ignore_DB:设定哪里数据库不需要记录Binlog

　　优点是Master端的Binlog记录所带来的Io量减少，网络IO减少，还会让slave端的IO线程,SQL线程减少，从而大幅提高复制性能,

　　缺点是mysql判断是否需要复制某个事件不是根据产生该事件的查询所在的DB,而是根据执行查询时刻所在的默认数据库（也就是登录时指定的库名或运行"use database"中指定的DB）,只有当前默认DB和配置中所设定的DB完全吻合时IO线程才会将该事件读取给slave的IO线程.所以,如果在默认DB和设定须要复制的DB不一样的情况下改变了须要复制的DB中某个Table中的数据,该事件是不会被复制到Slave中去的,这样就会造成Slave端的数据和Master的数据不一致.同样,在默认的数据库下更改了不须要复制的数据库中的数据,则会被复制到slave端,当slave端并没有该数据库时,则会造成复制出错而停止。

2、建立同步用的数据库账户，主库必须提供一个账户让从库通过此账户进行连接并进行同步，进入mysql后输入下面命令

#mysql -p

mysql>grant replication slave on *.* to 'mysync'@'%' identified by 'dump%0510';

3、锁住主库表，停止数据更新

mysql> flush tables with read lock;

此时所有数据库内的表只提供读的操作，不能再写入。

4、显示主库信息并记录

mysql> show master status;

5、备份数据库

#mysqldump –u root –p DB_name > /tmp/DB_name_bak.sql

Mysql>unlock tables;

传送数据到远端服务器

SCP /tmp/DB_name_bak.sql root@remote_IP:/tmp/

命令格式： scp local_file remote_username@remote_ip:remote_folder

二、从数据库操作设置（B）

1、修改配置文件，vim /etc/my.cnf，然后重启mysqld，/etc/init.d/mysqld restart

[mysqld]<要在mysqld中括号下修改>
server-id = 87
log-bin=mysql-bin
binlog_format=mixed
auto_increment_increment = 2
auto_increment_offset = 1
replicate-do-db=db_dns
replicate-ignore-db=data_center
replicate-ignore-db=mysql

replicate_wild_do_table=db_dns.%
replicate_wild_ignore_table=data_center.%
replicate_wild_ignore_table=mysql.%

slave-skip-errors=1146

master-host=123.x.x.x
master-user=mysync
master-password =q123456
master-port=3306

下面六项需要在slave上设置：

　　Replicate_Do_DB:设定需要复制的数据库,多个DB用逗号分隔

　　Replicate_Ignore_DB:设定可以忽略的数据库.

　　Replicate_Do_Table:设定需要复制的Table

　　Replicate_Ignore_Table:设定可以忽略的Table

　　Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置。

　　Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,可以带通配符。

　　优点是在slave端设置复制过滤机制,可以保证不会出现因为默认的数据库问题而造成Slave和Master数据不一致或复制出错的问题.

　　缺点是性能方面比在Master端差一些.原因在于:不管是否须要复制,事件都会被IO线程读取到Slave端,这样不仅增加了网络IO量,也给Slave端的IO线程增加了Relay Log的写入量。

　　注：在实际的生产应用中发现，在mysql5.0以前的版本，mysql的这个过滤设置几乎是形同虚设，不起作用：不管你在主库或是从库上设置了忽略某个数据库或是表，他依然会进行同步，所以在做5.0以前版本的主从同步时，一定保持主从数据库的一致性，主上有的库或是表从上一定要有，否则在同步的过程会出错。

2、调整权限
主库复制过来的数据库全部都是root权限，需要修改为mysql权限
#chown -R mysql:mysql /var/lib/mysql

3、重启mysql服务
权限调整完成后重启服务，之前重启会出现启动报错
#service mysql restart

4、输入同步复制参数

可将下面内容调整好后直接复制到mysql命令行下，这样不容易出错
设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position,就是在锁主库后要记录的那2个值
mysql>

CHANGE MASTER TO
MASTER_HOST='123.x.x.x',
MASTER_USER='mysync',
MASTER_PASSWORD='q123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000016',
MASTER_LOG_POS=107,
MASTER_CONNECT_RETRY=60;

5、恢复数据库

# mysql -u root -p DB_name < /tmp/DB_name.sql

6、开启同步服务

mysql>slave start;
所有配置工作完成

三、测试与排错

1、在Slave服务器查看同步状态会出来很多信息但是主要看这两个状态就行了如果都是 yes 就可以了

mysql>show slave statusG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2、如果出错，检查主服务器上的日志，进入mysql> 状态

'mysql-bin.000016'为 Master_Log_File，688为Exec_Master_Log_Pos，

show binlog events in 'mysql-bin.000016' from 688 G;

3、在slave上跳过错误，继续复制，进入mysql>状态

slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;

主服务器上的相关命令：

show processlist;
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}

从服务器上的相关命令:

slave start
slave stop
slave stop IO_THREAD //此线程把master段的日志写到本地
slave start IO_THREAD
slave stop SQL_THREAD //此线程把写到本地的日志应用于数据库
slave start SQL_THREAD
reset slave 执行前一定要停掉slave(stop slave) 。执行reset slave后，会清除复制相关的所有信息，包括：master.info, relay-log.info，及无条件删除所有的中继日志（relay logs). 注意是无条件的，也就是不管理你Slave SQL线程是否把所有的relay log重放完了。注意，stop slave后，先保存show slave status信息，然后再执行reset slave. 一般这个信息都会有用的。

set GLOBAL SQL_SLAVE_SKIP_COUNTER=40;
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志

****************************

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql>

CHANGE MASTER TO
MASTER_HOST='14.23.*.*',
MASTER_USER='mysync',
MASTER_PASSWORD='dump%0510',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000019',
MASTER_LOG_POS=28828127,
MASTER_CONNECT_RETRY=60;

________________________________________________________________________________

Got fatal error 1236 from master when reading data from binary log

'Slave can not handle replication events with the checksum that master

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.000011' at 120, the last event read from './mysql-bin.000011' at 120, the last byte read from './mysql-bin.000011' at 120.'

解决办法1：

master2的数据库换成master1相同版本的

解决办法2：

在master1中设置binlog_checksum =none；

完成。