MySQL数据库实现主从同步(基于MySQL8.0.11)

柔光的暖阳◎ 2022-02-25 00:58 327阅读 0赞

**前言**  
众所周知，随着用户量的增多，数据库操作往往会成为一个系统的瓶颈所在，而且一般的系统“读”的压力远远大于“写”，因此我们可以通过实现数据库的读写分离来提高系统的性能。

**实现思路**  
通过设置主从数据库实现读写分离，主数据库负责“写操作”，从数据库负责“读操作”，根据压力情况，从数据库可以部署多个提高“读”的速度，借此来提高系统总体的性能。

**基础知识**

要实现读写分离，就要解决主从数据库数据同步的问题,在主数据库写入数据后要保证从数据库的数据也要更新。

**主从数据库同步的实现思路如图**：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70][]  
主服务器master记录数据库操作日志到Binary log，从服务器开启i/o线程将二进制日志记录的操作同步到relay log（存在从服务器的缓存中），另外sql线程将relay log日志记录的操作在从服务器执行。  
记住这张图，接下来基于这个图实际设置主从数据库。

**主从数据库设置的具体步骤**

**1.在MySQL安装根目录下找到配置文件my.ini**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 1][]

新增如下配置：

server-id=1
    log-bin=master-bin
    log-bin-index=master-bin.index

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 2][]

**2.然后重启MySQL服务**

mysql > net stop mysql;
    mysql > net start mysql;
    -------------------------------------------
    或者：
    service mysql restart;

**3.检查配置效果，进入主库查询：**

SHOW MASTER STATUS;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 3][]

这个file:master-bin.000002我们稍后从数据库的配置会用到

**4.配置从服务器的 my.ini**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 4][]

[mysqld]
    # 设置3307端口
    port=3307
    # 设置mysql的安装目录
    basedir=F:\MySql\mysql-8.0.11-winx64-slave
    # 设置mysql数据库的数据的存放目录
    datadir=F:\MySql\data-slave
    # 允许最大连接数
    max_connections=200
    # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
    max_connect_errors=10
    # 服务端使用的字符集默认为UTF8
    character-set-server=utf8
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    # 默认使用“mysql_native_password”插件认证
    default_authentication_plugin=mysql_native_password
    # 配置主从同步(从服务器)
    server-id=2
    relay-log-index=slave-relay-bin.index
    relay-log=slave-relay-bin
    [mysql]
    # 设置mysql客户端默认字符集
    default-character-set=utf8
    [client]
    # 设置mysql客户端连接服务端时默认使用的端口
    port=3307
    default-character-set=utf8

**5.配置完成之后同样重启一下从服务器**

**6.接下来配置数据库的关联**

mysql> create user rep;
    mysql> ALTER USER 'rep'@'主库IP' IDENTIFIED BY '123456';
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库IP';
    mysql> flush privileges;

这个配置的含义就是创建了一个数据库用户repl，密码是mysql, 在从服务器使用repl这个账号和主服务器连接的时候，就赋予其REPLICATION SLAVE的权限， \*.\* 表面这个权限是针对主库的所有表的，其中xxx就是从服务器的ip地址。  
进入从数据库后执行：

mysql> change master to master_host='主库IP',master_port=3306,master_user='rep',master_password='123456',master_log_file='master-bin.000002',master_log_pos=0;

这里面的xxx是主服务器ip，同时配置端口，repl代表访问主数据库的用户，

**7.上述步骤执行完毕后执行start slave启动配置：**

mysql > start slave;

停止主从的命令为

mysql > stop slave;

查看状态命令,\\G表示换行查看（\\G后面切记不能带“;”）

mysql > show slave status \G

至此，主从同步配置完毕，可以尝试从master库进行写入操作，然后从slave库查看刚刚在主库进行的操作结果。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70]: /images/20220225/797e9f1c928e446bbd989c0c41c7e1aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 1]: /images/20220225/109b766ed99941868a1527ce1d8a3630.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 2]: /images/20220225/29f04d2fd99e409c9f4bc76bf8bd680c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 3]: /images/20220225/cf1ab443036e4cf0b925266203b8465f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JpZ19IaXBwb18xMTIx_size_16_color_FFFFFF_t_70 4]: /images/20220225/8627988cb1bf426fb1a6074b9dd217ad.png