mysql主从搭建

2020年12月13日

17:06

搭建：

1、mysqld.cnf 配置文件区分master和slave

mysql服务器唯一标识设置：

server-id=1

作用：

server-id用于标识数据库实例，防止在链式主从、多主多从拓扑中导致SQL语句的无限循环：

- （1）标记binlog event的源实例
- （2）过滤主库binlog，当发现server-id相同时，跳过该event执行，避免无限循环执行。
- （3）如果设置了replicate-same-server-id=1，则执行所有event，但有可能导致无限循环执行SQL语句。

https://segmentfault.com/a/1190000020315036?utm_source=tag-newest

取值范围：1到232–1之间的一个正整数值

Server-id相同时报错：

binglog的名字设置：

log-bin=mysql-bin

存放位置：/var/lib/mysql 宿主机上在：/opt/center/data/mysql1/

mysql查看binlog：

（1）sql命令行

mysql> show binlog events; #只查看第一个binlog文件的内容

mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容

mysql> show binary logs; #获取binlog文件列表

mysql> show master status；#查看当前正在写入的binlog文件

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

（2）mysqlbinlog插件：

binlog格式设置：

binlog_format=ROW

- ① STATEMENT模式（SBR）
- 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
- ② ROW模式（RBR）
- 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志：比如update语句执行之后，日志中记录的不是这条 update 语句所对应的事件，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多个事件。自然，bin-log 日志的量就会很大。尤其是当执行 alter table 之类的语句的时候，产生的日志量是惊人的。
- ③ MIXED模式（MBR）
- 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

来自<https://www.cnblogs.com/xingyunfashi/p/8431780.html>

警惕：mixed模式可能会发生主从不一致的情况，推荐使用row模式！

自增主键值设置

auto-increment-increment=2

auto-increment-offset=2

- auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
- auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

同步库表设置

binlog-do-db=center #只同步center库

replicate-wild-ignore-table=center.flyway_schema_history #忽略的表

忽略同步异常

slave-skip-errors = 1032,1062

1062：错误是指一些主键重复的错误

1032：错误是更新/删除数据时找不到行

2、主从搭建（两台互为主从）：

（1）我们需要授权root用户的replication及slave权限。如果不授权的话，从库是不能连接主库的。

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%';

（2）看一下master的日志文件及位置。我们看到了master机器的文件名及其位置。

（3）我们在从库里边执行如下命令。里边配置设置了master的主机名，主机端口号，主机用户名，密码，主机器的文件名字以及位置。

change master to master_host='mysql_master',master_port=3306, master_user='root',master_password='root',master_log_file='mysql-bin.000005',master_log_pos=154;

（4）接下来我们来启动slave，并且查看slave的线程命令。

start slave;

show slave status \G;