通用方法

  1. #0.主库创建主从备份用户
  2. grant replication slave on *.* to rep@'%' identified by '123';
  4. #1.打点备份主库
  5. mysqldump -A -R --triggers --master-data=2 --single-transaction |gzip > /tmp/full.sql.gz
  7. #2.查看打点位置
  8. zcat /tmp/full.sql.gz |head -25|grep -i 'change master to'
  10. #3.传输数据到从库机器
  11. scp /tmp/full.sql.gz 10.0.0.53:/tmp/
  13. #4.从库导入数据(10.0.0.53)
  14. zcat /tmp/full.sql.gz |mysql -uroot -p
  16. #5.开启主从同步
  17. CHANGE MASTER TO
  18. MASTER_LOG_FILE='mysql-bin.000001',
  19. MASTER_LOG_POS=317,
  20. MASTER_HOST='10.0.0.51',
  21. MASTER_USER='rep',
  22. MASTER_PASSWORD='123';

GTID实现主从复制

  1. #0.创建主从账户
  2. grant replication slave on *.* to rep@'%' identified by '123';
  4. #1.修改配置文件
  5. vim /etc/my.cnf
  6. [mysqld]
  7. gtid_mode=ON
  8. enforce_gtid_consistency
  9. log-slave-updates
  10. log-bin=mysql-bin
  12. #2.重启mysql服务
  13. /etc/init.d/mysqld restart
  15. #3.从库上执行主从命令
  16. change master to
  17. master_host='10.0.0.51',
  18. master_password='123',
  19. master_user='rep',
  20. master_auto_position=1,
  21. start slave;