MySQL主从复制

主从复制

Redis主从复制

img img

<mark>MySQL与Redis主从复制不同的是，MySQL是从接入点开始主从复制。</mark>

步骤：

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
2. slave将master的binary log events拷贝到它的中继日志（relay log）
3. slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

基本原则：

1. 每个slave只有一个master
2. 每个slave只能有一个唯一的服务器ID
3. 每个master可以有多个salve

复制的最大问题是存在延时。（需要IO读写日志）

配置搭建

一主一从

<mark>注意：修改任何文件时都记得备份。否则......</mark>

这里我把虚拟机复制了一份，也就拥有两个MySQL了。不过这样会导致两个Mysql的uuid是一样的，所以我们要先修改一下其中的一个uuid。

show variables like 'datadir';查看数据目录

然后进入到数据目录，修改vim auto.cnf将其中的server-uuid进行修改，保存重启MySQL即可。

show variables like '%server_uuid%';查看MySQL的uuid。

我打算将复制的虚拟机中的MySQL作为主机master，修改配置文件vim /etc/my.cnf在[mysqld]下添加

image-20201029160839914

server-id=1
log-bin=mysqlbin
binlog-ignore-db=mysql
binlog-do-db=mydb_kylin
binlog_format=STATEMENT

主服务器唯一ID： server-id=1

启用二进制日志:：log-bin=mysqlbin 名称为mysqlbin

设置不要复制的数据库：binlog-ignore-db=mysql

设置需要复制的数据库：binlog-do-db=需要复制的主数据库名字

设置logbin格式：binlog_format=STATEMENT（默认）

MySQL 5.5 中对于二进制日志 (binlog) 有 3 种不同的格式可选：Mixed,Statement,Row，默认格式是 Statement。总结一下这三种格式日志的优缺点。

MySQL Replication 复制可以是基于一条语句 (Statement Level) ，也可以是基于一条记录 (Row Level)，可以在 MySQL 的配置参数中设定这个复制级别，不同复制级别的设置会影响到 Master 端的 bin-log 日志格式。

1. Row
日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。

优点：在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题。

缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条 update 语句：

UPDATE product SET owner_member_id = 'b' WHERE owner_member_id = 'a'

执行之后，日志中记录的不是这条 update 语句所对应的事件 (MySQL 以事件的形式来记录 bin-log 日志) ，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多个事件。自然，bin-log 日志的量就会很大。尤其是当执行 alter table 之类的语句的时候，产生的日志量是惊人的。因为 MySQL 对于 alter table 之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。

2. Statement
每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。

优点：在 statement 模式下，首先就是解决了 row 模式的缺点，不需要记录每一行数据的变化，减少了 bin-log 日志量，节省 I/O 以及存储资源，提高性能。因为他只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点：在 statement 模式下，由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。另外就是，由于 MySQL 现在发展比较快，很多的新功能不断的加入，使 MySQL 的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement 中，目前已经发现的就有不少情况会造成 MySQL 的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行来记录的变化，所以不会出现类似的问题。

3. Mixed

从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。

在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。

新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

image-20201029161513680

修改完成都要重启mysql服务systemctl restart mysqld

接下来配置从机slave的配置文件。

image-20201029162032023

server-id=2 #服务id
relay-log=mysql-relay #中继日志名为mysql-relay

保存退出后，重启MySQL服务systemctl restart mysqld

记得打开主从机的3306端口或者防火墙。

接着在主机上建立帐户并授权slave。

GRANT REPLICATION SLAVE ON *.* TO '账户名'@'从机IP' IDENTIFIED BY '123456';

image-20201029163004829

接着查看master状态show master status;

image-20201029163232166

记住日志文件名File，和接入点Position

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

接着在从机slave上运行命令

CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='主机用户',MASTER_PASSWORD='主机密码',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;

image-20201029163645160

接着运行start SLAVE;启动从机复制功能

查看从机状态show slave status;

image-20201029163821439

如果出现错误会在下面出现Error提示。

如何停止从服务复制功能stop slave;

如何重新配置主从stop slave; reset master;

此时我们的主从复制已经搭建起来啦。我们在主机创建数据库CREATE DATABASE mydb_kylin;这里的数据库是配置中配置运行复制的数据库名。

image-20201029164221440

运行成功后，各自刷新一下主从机数据库。从机成功创建。主从复制搭建成功！！

CREATE TABLE mytb1(id int,name VARCHAR(256));INSERT INTO mytb1 VALUES(1,'zs');