MySQL主从同步配置（详细图解）

痛定思痛。 2022-04-23 14:42 196阅读 0赞

说明：该篇博客是博主一字一码编写的，实属不易，请尊重原创，谢谢大家！

**目录**

一丶叙述

二丶备份主服务器原有数据到从服务器

三丶配置主服务器master（192.168.4.63）

四丶配置从服务器slave（192.168.4.15）

五丶测试主从同步

--------------------

### 一丶叙述 ###

**1. 主从同步的定义**

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

**使用主从同步的好处：**

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

**2. 主从同步的机制**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70][]**

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的\[mysqld\]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

**3. 配置主从同步的基本步骤**

有很多种配置主从同步的方法，可以总结为如下的步骤：

1.  在主服务器上，必须开启二进制日志机制和配置一个独立的ID
2.  在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
3.  在开始复制进程前，在主服务器上记录二进制文件的位置信息
4.  如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
5.  配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

**4. 详细配置主从同步的方法**

**主和从的身份可以自己指定，博主将虚拟机Ubuntu（192.168.4.63）中MySQL作为主服务器，将Ubuntu（192.168.4.15）中的MySQL作为从服务器。 在主从设置前，要保证两台Ubuntu之间的网络连通。**

### 二丶备份主服务器原有数据到从服务器 ###

**1.**在主服务器Ubuntu（192.168.4.63）上进行备份：

*  **step1** 查看主服务器上的数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 1][]

*  **step2** cd到主服务器桌面，执行以下命令：

mysqldump -uroot -p --all-databases --lock-all-tables > ~/master_db.sql

**说明：\--all-databases**表示备份所有数据库（当恢复时就不需要再创建数据库了）**--lock-all-tables**表示在备份时对表数据进行上锁，意思是备份期间执行增删改操作需要等待

![20190317164152291.png][]

**2. **在从服务器Ubuntu（192.168.4.15）上进行数据还原

*  **step1** 在主服务器中通过scp命令将备份到桌面上的master\_db.sql文件推送到从服务器中

![2019031720002196.png][]

*  **step2** 查看从服务器上的数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 2][]

*  **step3** 在从服务器上执行以下命令

mysql –uroot –p < master_db.sql

![20190317200225518.png][]

*  **step4** 将主服务器备份的数据库恢复到从服务器后，此时查看从服务器的数据库中是否存在主服务器数据库中的jing\_dong，python\_01以及python\_test数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 3][]

### 三丶配置主服务器master（192.168.4.63） ###

**1.**编辑设置mysqld的配置文件，设置log\_bin和server-id，一般将IP地址尾数设置为server-id得值，必须保证主服务器和从服务器上的server-id的值不能相同

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

![20190317201117892.png][]

**2.**执行 sudo service mysql restart 命令，重启mysql服务

![20190317170632729.png][]

**3.**登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号

**说明：**\*.\*任意数据中所有表 flush privileges更新权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
    FLUSH PRIVILEGES;

![20190317171057839.png][]

**4.**获取主服务器的二进制日志信息

show master status;

**注：**File为使用的日志文件名字，Position为使用的文件位置，这两个参数须记下，配置从服务器时会用到

![20190317200655578.png][]

### 四丶配置从服务器slave（192.168.4.15）  ###

**1.**配置从服务器上的server-id

![20190317201325188.png][]

**2.**重启mysql服务

![2019031720143558.png][]

**4.**进入从服务器的mysql，设置连接到master主服务器

说明：master\_host 主服务器Ubuntu的地址，master\_user是在上面第三大步中在主服务器上配置给从服务器进行远程操作的账号，master\_log\_file也是在上面第三大步中主服务器的二进制日志信息

change master to master_host='192.168.4.63', master_user='slave', master_password='slave',master_log_file='mysql-bin.000007', master_log_pos=600;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 4][]

**5.**开启同步，查看同步状态

*  **step1** 启动复制

start slave;

*  **step2** 显示状态

**说明：****当IO线程与SQL线程显示yes正在运行表示此两台服务器同步成功**

start slave status;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 5][]

### 五丶测试主从同步 ###

**1.**在Ubuntu的MySQL中（主服务器）创建一个csdn\_test数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 6][]

**2.**在Ubuntu的MySQL中（从服务器）查看新建的数据库是否存在

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70]: /images/20220307/a04c6fbe1f24495685582d560c3855fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 1]: /images/20220307/1379707ecf764514be594b6c82c0ec79.png
[20190317164152291.png]: /images/20220307/6d05d8fd3e51449f907e96554c989823.png
[2019031720002196.png]: /images/20220307/42919715cd3046a5a6ffb8e2cfe80f8f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 2]: /images/20220307/b8fcfd66cb1947e387b74299bc09b53b.png
[20190317200225518.png]: /images/20220307/7207ee6300e141ec96275e5df274df06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 3]: /images/20220307/23687e7b37c14580a826c54ec2b4119b.png
[20190317201117892.png]: /images/20220307/ce9a8a56a93a4e3ab79d12e47af237da.png
[20190317170632729.png]: /images/20220307/7ce27802d2634e628bb16afbc0cd8235.png
[20190317171057839.png]: /images/20220307/e4413708f34446cb884b61cf06168c61.png
[20190317200655578.png]: /images/20220307/84b9caf1b7124860adc3a5963bc32fbc.png
[20190317201325188.png]: /images/20220307/fa5a29836b4f4eba94e884d65f0a678a.png
[2019031720143558.png]: /images/20220307/66776826559943df819bc3dc7aa6e698.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 4]: /images/20220307/61d15cd219fa4ddfbb53404f3ef6a2af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 5]: /images/20220307/4466722c64634de790e59655658acfef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 6]: /images/20220307/590db4986893439793511b25e483e46d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzgyNDI1_size_16_color_FFFFFF_t_70 7]: /images/20220307/1fa7b2e3bd4946efba67cf80338eb7dc.png