mysql主从同步 追数据_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 协议》，转载必须注明作者和本文链接