问题汇总
show master status没有结果, binglog没有开关或生效log_bin serverid 步骤1.准备多实例环境2.配置my.cnf3登录主库增加用于从库连接主库的同步账号rep并且授权replication slave同步的权限4登录主库。整库锁表flush table with read lock (窗口关闭失效,超时参数达到也失效),然后show master status 查看binlog的位置状态5新开窗口linux命令行导出sql拷贝到从库所在的及其6解锁及其7把主库导出的sql恢复到从库中8根据主库的show master status查看binlog的位置状态在从库执行change master to9从库开启开关start slave10 show slave status查看同步状态要点异步方式同步逻辑同步模式,多种模式,默认是通过sql语句执行主库通过记录binlog实现对从库的同步,binlog记录数据库的跟新语句主库一个线程,从库由1个io线程和一个sql线程来完成从库关键文件master。info,relay-log,relay info功能若想完成级联 需要打开logbin和log-slave-updates;生产环境快速配置mysql主从复制的方案安装好配置从库的数据库,配置好log-bin和server-id参数无需登录配置主库my.cnf,主库的log-bin和server-id默认配置好的登录主库用于从库连接主库同步的账号使用mysqldump 带--master-data=1无需binlog文件及对应的位置点从库开启同步开关start slave;从库show slave status检查同步状态跟新测试mysql> show processlist;+----+------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist || 11 | root | 192.168.88.172:40311 | NULL | Binlog Dump | 3190 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |+----+------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+2 rows in set (0.00 sec)mysql> show processlist;+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist || 4 | system user | | NULL | Connect | 3241 | Waiting for master to send event | NULL || 5 | system user | | NULL | Connect | 3037 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+3 rows in set (0.00 sec)my.cnfmaster端--binlog-do-db二进制日志记录数据库多个数据库用,分割--binlog-ignore-db二进制日志胡月数据库多个数据库用,分割replication的slave端六个参数--replication-do-db需要制定复制的数据库--replication-ignore-db设定胡月的数据库=db--replication-do-table--replication-ignore-table--replication-wild-do-table--replication-wild-ignore-table生产授权方案主库 select insert delete update从库 回收insert delete update通过readonlysuper不限制[mysqld] readonlyslave-iostatus yesslave sql running nosecond sql behindmaster 是否为0 0为同步方法一stop slaveset global sql_slave_skip_counter=10start slave;方法二根据错误号跳过制定的错误slave-skip-errors=1032,1062;让mysqwl记录binlog1级联2把从库作为备份的服务器log-slave-updates只加这一句话[mysqld]expire_log_days=7保留七天的binlog主库down机show processlist;查看所有从库的master info看哪个的跟大选个pos最大的作为主库1确保所有的relay log全部更新完毕在每个从库上执行stop slave io_thread;show processlist;直到看到has readall relay log2登录到主库stop slave;reset ;quit3进到数据库数据目录,删除master。info rtelay——log info功能cd /data/3306/datarm -f master.info relay-log.info检查授权表readonly参数4 开启binlog注释 slave-log readonlyrestart其他冲库stop slave;change master 只改ip解决主键自增长id错误master1;auto_increament_increament=2 --1,3,5auto_increament_offset=1起始位置master2auto_increament_increament=2 --2,4,6auto_increament_offset=2使用主主的前提,主键自增