主从同步，主服务器增删改，从服务器查找

最新推荐文章于 2023-10-10 16:48:15 发布

原创最新推荐文章于 2023-10-10 16:48:15 发布

· 1.8k 阅读

0 ·

版权

文章标签：

#mysql #数据库 #java

商城项目专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍了MySQL主从同步的工作原理、优点、主要方式（SBR/RBR/MBR）、配置步骤以及注意事项，包括如何设置复制数据库、表，以及如何确保数据一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. mysql主从同步的定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）
因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器
通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表

2. 使用主从同步的好处

通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新和删除，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能
提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

3. 主从同步的方式

mysql是异步复制的，而MySQL Cluster是同步复制的。有很多种主从同步的方法，但核心的方法有两种，Statement Based Replication（SBR）基于SQL语句的复制，另一种是Row Based Replication（RBR）基于行的复制，也可以使用Mixed Based Replication（MBR）。在mysql5.6中，默认使用的是SBR。而mysql 5.6.5和往后的版本是基于global transaction identifiers(GTIDs)来进行事务复制。当使用GTIDs时可以大大简化复制过程，因为GTIDs完全基于事务，只要在主服务器上提交了事务，那么从服务器就一定会执行该事务。

SBR：当使用二进制日志时，主服务器会把SQL语句写入到日志中，然后从服务器会执行该日志，这就是SBR，在mysql5.1.4之前的版本都只能使用这种格式。使用SBR会有如下

长处：
1. 日志文件更小
2. 记录了所有的语句，可以用来日后审计
弊端：
1. 使用如下函数的语句不能被正确地复制：load_file(); uuid(), uuid_short(); user(); found_rows(); sysdate(); get_lock(); is_free_lock(); is_used_lock(); master_pos_wait(); rand(); release_lock(); sleep(); version();
2. 在日志中出现如下警告信息的不能正确地复制：[Warning] Statement is not safe to log in statement format.
3. 或者在客户端中出现show warnings
4. Insert … select语句会执行大量的行级锁表
5. Update语句会执行大量的行级锁表来扫描整个表
RBR：主服务器把表的行变化作为事件写入到二进制日志中，主服务器把代表了行变化的事件复制到从服务中，使用RBR的

长处：

所有的数据变化都是被复制，这是最安全的复制方式
更少的行级锁表

弊端：

日志会很大
不能通过查看日志来审计执行过的sql语句，不过可以通过使用mysqlbinlog
–base64-output=decode-rows --verbose来查看数据的变动

MBR：既使用SBR也使用RBR，默认使用SBR

4. 主从同步的机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致
在使用二进制日志时，主服务器的所有操作都会被记录下来(二进制日志)，然后从服务器会接收到该日志的一个副本(中继日志)。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。
每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。
主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

5.主从同步的配置

保持三个节点的数据库状态一致

准备三个服务器

node1   #master
node2   #slave
node3	#slave

5.1 配置主服务器

在主服务器备份数据库

在主服务器上，必须开启二进制日志机制和配置一个独立的id

#关闭主服务器的mysql服务,配置完成之后再重新开启
[root@node1 /etc/my.cnf]


[mysqld]
#开启二进制日志
log-bin=mysql-bin

#配置主服务器的id
server-id=1

主服务器查询master的二进制日志名称和偏移位置(File的值是当前使用的二进制日志的文件名，Position是该日志里面的位置信息)
```
show master status;
```

5.2 配置从服务器

从服务器上找到my.cnf配置文件，添加server-id
```
[root@node2 /etc/my.cnf]


[mysqld]

server-id=2
```

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql>
		CHANGE MASTER TO
        MASTER_HOST='10.10.10.21',
        MASTER_USER='root',
        MASTER_PASSWORD='root123ROOT...',
        MASTER_LOG_FILE='mysql-bin.000001',
        MASTER_LOG_POS=156;

还原从主服务器备份的数据文件(因为主服务器查询出来的postition是主服务器的最新的偏移量,必须先还原成和主服务器一致的状态)
启动slave同步进程
```
mysql>
	start slave;
```

查看slave状态

mysql>
	show slave status\G; 
    -- 出现   Slave_IO_Running: Yes   Slave_SQL_Running: Yes  才是启动成功

6. 主从同步的其他配置

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

-- 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
-- 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game

7. mysql主从复制的注意点

在mysql的主从复制过程中如果涉及到函数或者存储过程的复制,则需要在[mysqld]中配置

#开启 创建存储过程和函数 也生成事件日志
log_bin_trust_function_creators=true

linux的mysql中需要配置表的大小写忽略,要不有可能出现找不到表

#配置数据库和表名不区分大小写
lower_case_table_names=1

8. 上线新的数据库服务器

新服务器上找到my.cnf配置文件，添加server-id
```
[root@node3 /etc/my.cnf]


[mysqld]

server-id=3
```

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql>
		CHANGE MASTER TO
        MASTER_HOST='node1',
        MASTER_USER='rep',
        MASTER_PASSWORD='rep',
        MASTER_LOG_FILE='mysql-bin.000001',
        MASTER_LOG_POS=388;

启动slave同步进程
```
mysql>
	start slave;
```

查看slave状态

mysql>
	show slave status\G; 
    -- 出现   Slave_IO_Running: Yes   Slave_SQL_Running: Yes  才是启动成功

新上线的数据库服务器会根据配置的主服务器的二进制日志文件中的偏移量更新上线的从服务器到最新状态