MySQL 数据主从同步

环境搭建

主服务器配置

[mysqld]
#base configuration
server-id=1
port=3306
#binlog-do-db=proxy_heart_beat // 设置指定数据库生成二进制文件
basedir=/usr/local/mysql
datadir=/data/dbfiles/mysql
tmpdir=/data/dbfiles/mysql/tmp
socket=/data/dbfiles/mysql/mysql.sock
pid-file=/data/dbfiles/mysql/mysql.pid
log-error=/data/dblogs/mysql/mysql.err

#log-bin  configuration
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format=mixed
log_bin_trust_function_creators=1
expire_logs_days=10

从服务器配置

[client]
port=3306
socket= /data/dbfiles/mysql/mysql.sock
[mysqld]
#base configuration
server-id=2
port=3306
// 两种思路 第一种主机设置生成二进制文件的库，第二种从库选择需要同步的库
// 推荐第二种，第一种有风险会导致其他库无法生成二进制。第二种从机有同步二进制但是没有执行
// 两种只要配置一种即可，不要主从都配置。本人测试会导致不生效。
#replicate-do-db    //设定需要复制的数据库 ，多个增加多条，不要用,分隔。
#replicate-ignore-db //设定需要忽略的复制数据库
#replicate-do-table  //设定需要复制的表
#replicate-ignore-table //设定需要忽略的复制表
#replicate-wild-do-table //同replication-do-table功能一样，但是可以通配符
#replicate-wild-ignore-table //同replication-ignore-table功能一样，但是可以加通配符
basedir=/usr/local/mysql
datadir=/data/dbfiles/mysql
tmpdir=/data/dbfiles/mysql/tmp
socket=/data/dbfiles/mysql/mysql.sock
pid-file=/data/dbfiles/mysql/mysql.pid
log-error=/data/dblogs/mysql/mysql.err

#log-bin  configuration
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format=mixed
log_bin_trust_function_creators=1
expire_logs_days=10

#slow_query_log 5 seconds for slow query
slow_query_log=1
long_query_time=5
slow_query_log_file=/data/dblogs/mysql/slow.log

#character set
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

#other configuration
lower_case_table_names=0

相关操作

１）基本思路

确保数据相同: 最好同一个sql文件导入，如果不限定指定数据库，最后完全一样

配置主服务器：启用binlog日志，授权用户，查看当前正在使用的日志

配置从服务器：设置server_id（唯一），指定主库信息

测试配置：客户端连接主库写入数据，在从库上也能查询到
2 ）操作
1.首先需要更新主从数据库配置文件到上面的配置，然后重新启动数据库 sudo service mysql restart

2.在主服务器上创建slave用户用于主从复制任务。并需要授权该用户到从服务器。
grant replication slave on . to ‘slave‘@此处是IP,或者”*号” identified by 此处是密码;
flush privileges; 刷新权限

3.show master status\G; 查看主机日志记录点。

4.如果主库里已经数据库数据了，那还得进行以下操作：
flush tables with read lock;

5.CHANGE MASTER TO MASTER_HOST = ‘此处是主机IP‘, MASTER_USER = ‘slave’,MASTER_PASSWORD = ‘此处是密码‘,MASTER_PORT = 3306,MASTER_LOG_FILE=’mysql-bin.000002’,MASTER_LOG_POS=65975595,MASTER_RETRY_COUNT = 60,MASTER_HEARTBEAT_PERIOD = 10000;// 设置从机同步主机的相关信息 MASTER_LOG_FILE 为上一步骤查看的文件名，MASTER_LOG_POS 为上一步骤查看的二进制节点。

6.如果是已经设置过 最好 在执行上一步骤前 stop slave; reset slave; 停止并重制下复制同步任务。

7.开始 start slave;

8.show slave status\G;查看同步任务状态 slave_id_running,slave_sql_running,如果都no,说明同步状态错误，需要重新检查下，如果都为Yes,说明同步任务开启，但是如果数据库不一致可以还是会导致同步无效。

9.最后回到主库解锁表　 unlock tables;

10.测试主机update 从机自动更新。 如果没有效果可以尝试把数据库表删除重新从主机导出导入。作者就是多次尝试最后才成功。

指定库同步

两种思路 第一种主机设置生成二进制文件的库，第二种从库选择需要同步的库
推荐第二种，第一种有风险会导致其他库无法生成二进制。第二种从机有同步二进制但是没有执行
两种只要配置一种即可，不要主从都配置。本人测试会导致不生效。
主库配置

#binlog-do-db=proxy_heart_beat 

从库配置

#replicate-do-db    //设定需要复制的数据库 ，多个增加多条，不要用,分隔。
#replicate-ignore-db //设定需要忽略的复制数据库
#replicate-do-table  //设定需要复制的表
#replicate-ignore-table //设定需要忽略的复制表
#replicate-wild-do-table //同replication-do-table功能一样，但是可以通配符
#replicate-wild-ignore-table //同replication-ignore-table功能一样，但是可以加通配符

爬坑指南

• Slave_IO_Running: Yes Slave_SQL_Running: Yes 查看日志可以发现 从库日志同步一切正常，但是有些语句不执行。例如：navcat 执行可以正常同步，但是直接dbname.table 形式语句无法生效。
  1)使用了replicate_do_db做配置，则执行语句必须带有use dbname;insert….
  2)不配置replicate_do_db相关参数，直接使用replicate_wild_do_table

  例: replicate_wild_do_table =proxy_heart_beat.%

  知识点

  “binlog-do-db, binlog-ignore-db, replicate-do-db, replicate-ignore-db” 这一类参数，并非想象中可靠！
  我们会以为设定了以上参数，MySQL的主从复制就会只对我们设定的数据库生效。但事实上，MySQL不是根据内容来判断的，而是很傻瓜的根据你执行了“use dbname”或在初始连接时指定的数据库来判断的。
  5.5以上，下面的这些表都建议过滤掉，只复制生产环境数据。
  replicate_wild_ignore_table =mysql.%
  replicate_wild_ignore_table =test.%
  replicate_wild_ignore_table =information_schema.%
  replicate_wild_ignore_table =performance_schema.%

本作品采用《CC 协议》，转载必须注明作者和本文链接