mysql主从搭建

待我称王封你为后i 2022-12-31 01:22 114阅读 0赞

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][https_segmentfault.com_a_1190000020315036_utm_source_tag-newest]

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

Server-id相同时报错：

![20201227150520548.png][]

**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][https_dev.mysql.com_doc_refman_5.7_en_binary-log.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx_size_16_color_FFFFFF_t_70][]

（2）mysqlbinlog插件：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx_size_16_color_FFFFFF_t_70 1][]

**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][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机器的文件名及其位置。

![20201227150520580.png][]

（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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx_size_16_color_FFFFFF_t_70 2][]

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][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）重新建立主从关系

[https_segmentfault.com_a_1190000020315036_utm_source_tag-newest]: https://segmentfault.com/a/1190000020315036?utm_source=tag-newest
[20201227150520548.png]: /images/20221120/620782afc6b14016a73bb58b66cfb5e9.png
[https_dev.mysql.com_doc_refman_5.7_en_binary-log.html]: https://dev.mysql.com/doc/refman/5.7/en/binary-log.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201227150520650.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201227150520684.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_xingyunfashi_p_8431780.html]: https://www.cnblogs.com/xingyunfashi/p/8431780.html
[20201227150520580.png]: https://img-blog.csdnimg.cn/20201227150520580.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201227150520655.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI3MTQzNTUx,size_16,color_FFFFFF,t_70
[https_www.docs4dev.com_docs_zh_mysql_5.7_reference_show-slave-status.html]: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/show-slave-status.html