mysql主从搭建

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;

 

Slave_IO_Running   ： io_thread 负责与主库建立连接

 

Slave_SQL_Running ： sql_thread 读取同步过来的日志，解析出日志里的命令，并执行

 

 Seconds_Behind_Master:   主备延迟时间

 

https://www.docs4dev.com/docs/zh/mysql/5.7/reference/show-slave-status.html

 

 

3、常见故障恢复：

 

Slave_IO_Running 为NO、Slave_SQL_Running为No

（1）重启mysql服务

（2）重启slave服务，跳过一次事务

docker exec -i center-mysql mysql -uroot -p1q2w3e@4R -e "STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;START SLAVE;"

（3）重新建立主从关系