Mysql主从平滑过度

一:首次布置主从库

1,主库开启bin-log日志

主库配置:

server-id = 1
log-bin=mysql-bin  ##  -间隔或_间隔最终在数据库都是形成下划线(_)
binlog_format=mixed
slave_skip_errors = all
max_binlog_size=100m
binlog_cache_size = 4m
max_binlog_cache_size= 128m
expire_logs_days = 10
#binlog_do_db=liuxuekw_application_db  #需要记录日志的数据库,建议此参数不写,只写不需要记录日志的数据库
#binlog_do_db=liuxuekw_market_db

binlog_ignore_db=mysql  #不需要记录日志的数据库
binlog_ignore_db=performance_schema

binlog_ignore_db=information_schema

mysql -uroot -p1

reset master;  ##重置日志,把之前的日志清除,减少从库跟踪日志pos点的消耗,从库默认是从第一份日志开始跟踪

首次布置主从,把所有需要同步的库导出sql,并刷新日志

mysqldump -uroot -p1 --lock-all-tables -F -B test huati>e:\weibo.sql  

##F刷新日志到mysql-bin.000002,--lock-all-tables 加上只刷新一次日志mysqldump默认锁表导出,详细说明请相看相关文档

#通过FTP或scp把data.sql移动到从库服务器上

#进入主库

mysql -uroot -p1

->show binary logs;  #查看所有日志

->show master status; #查看当前日志索引

2.从库导入数据库

首先创建test数据库

use test;

source data.sql #导入数据库

2.修改从库上mysql配置

my.cnf的配置

server-id       = 2
slave-skip-errors = all
#replicate-do-db = liuxuekw_application_db  ## 需要同步的数据库,建议此参数不写,只写不需要同步数据库参数,有一个重点,mysql5.6后建议只写这个参数,只写需要同步的数据库,更好维护不用去管后续添加数据库问题,replicate-do-db与replicate-ignore-db 参数存在互斥关系,如果两个参数都填replicate-do-db的优先级更高会自动忽略掉replicate-ignore-db参数
#replicate-do-db = liuxuekw_crm_db

replicate-ignore-db =performance_schema  #不需要同步的数据库

replicate-ignore-db = information_schema

replicate-ignore-db = mysql

##mysql5.5版本后不能直接在配置里设置master-host,user,password等参数,否则启动mysql时会报错

启动从库mysql服务器

mysql -uroot -p1

>change master to master_host='10.161.130.230',master_user='test', master_password='1',master_log_file='mysql-bin.000001',master_log_pos=1;

test 用户必须要有replication_client,replication_slave,super权限才能跟踪同步日志

->start slave;  #开启从库

->show slave status \G;

 Slave_IO_Running: Yes  ##两项为yes同步成功,IO线程负责读取主库的日志文件,
Slave_SQL_Running: Yes #SQL线程负责执行日志文件中的SQL语句,mysql5.6后是多线程执行sql

->stop slave ;　#停止从库

->reset slave  # 重置从库,重新使用change master to配置主库信息,配置错乱时建议重置

注:在主库新增加数据库时一定要先告知在从库配置中增加replicate-ignore-db该数据库并重启重载配置,

或主库不告知从库的情况下添加replicate-ignore-db,不记录该库日志,建议尽量只操作主库或从库配置,不要两边操作配置,减少对两个库配置文件的操作带来的重启重载

二: 在主从热机时新增同步数据库:

1.进入从库服务器:

->stop slave;  ##停止同步

2.修改my.cnf配置文件去掉该库

#replicate-ignore-db = new_db

3.从主库导出新增的要同步的数据库

mysqldump -uroot -p1 --routines --single_transaction --master-data=2 -B new_db>e:\new_db.sql   #r把new_db导出

-B参数：连接多个库，使用它可导出多个库，并且会导出创建db，use db等语句 

--routines：导出存储过程和函数

--single_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。

--master-data：默认等于1，将dump起始（change master to）binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。

4. 导入new_db数据库

mysql -uroot -p1

->source new_db.sql

5.重新启动从库

->start slave;

->show slave status; #查看是否正常跟踪日志

注:这里可能出现一个问题,在停止从库跟踪到重新启动从库可能会出现日志跟踪日志节点差

当stop slave时,主库日志节点是100, 当从主库导出新增的new_db时日志点是150, 这时有50节点差值,若刚好这50点差值当中有对new_db操作新增数据,则重新start slave时,可能会由于new_db上已经有存在的数据产生insert_into数据冲突,这时建议手动删除下已经存在的数据或者使用mysqlbinlog命令导出这50点差日志手动执行sql然后手动在slave中执行

或者在从库中加入slave-skip-errors = all  跳过错误

->start slave until MASTER_LOG_FILE="mysql-bin.000023", MASTER_LOG_POS=8220537;

->start slave; ## 一定要