使用MySQL在单台服务器上创建多个实例并实现读写分离

我们在参加面试和实际系统架构设计中总是听到要对数据库进行读写分离操作，那么为什么要对数据库的进行读写分离？以及具体要怎么去实现？本章内容就来详细解答以上问题（仅供学习和参考）。
一、为什么要对数据库进行读写分离？
面对生产环境的严峻挑战，尤其是高并发场景下的数据读、写操作，单一MySQL服务器常成为制约系统响应速度的瓶颈。写操作引发的锁机制不仅拖慢了写入速度，还间接阻碍了读取流程，导致用户体验受损。实施读写分离，旨在通过分离读写流量至不同实例，有效减轻单一数据库的压力，避免查询操作受写入锁影响，从而确保系统流畅运行。
二、配置MySQL读写分离
前期准备

• 运行环境： linux Ubuntu 22.04.4 + 宝塔面板-v8.0.6 + MySQL 5.7.43
• MySQL官方文档

• 注意我使用的是宝塔面板安装的MySQL，并没有自己手工编译安装。所以如果和我的运行环境不一样那么在配置过程中也会存在一些差别。

2.1 创建并运行不同Mysql实例和配置文件

• 每个MySQL实例都有对应的运行目录和配置文件，配置文件里面可以设置运行的端口号、数据存放目录、日志存放目录等。本次一共创建三个实例，分别运行在3307、3308、3309端口，为了便于区分以每个实例运行的端口号来命名对应目录名和配置名。

• 由于我只有一台服务器，所以就用一台服务器创建多个MySQL实例来模拟在多台MySQL服务器上的效果。如果有多台服务器的可以直接省略此步骤。

  • 创建运行目录和修改目录权限为mysql

  #创建目录 mkdir -p {3307,3308,3309}/data #修改目录权限为mysql chown mysql:mysql {3307,3308,3309} 

  

  • 分别创建每个实例的配置文件（*以3307举例，剩下的3308、3309按照下面步骤依次配置）

  #在对应3307目录下创建3307.cnf touch /www/server/data/3307/3307.cnf vim /www/server/data/3307/3307.cnf 

  将以下内容拷贝到3307.cnf文件中，注意对应的目录是否存在！并且datadir这个配置项的文件夹必须是一个空文件夹，否则启动会报错！ [mysqld] port=3307 datadir=/www/server/data/3307/data/ socket=/tmp/mysql3307.sock symbolic-links=0  [mysqld_safe] log-error=/www/server/data/3307/3307.log pid-file=/www/server/data/3307/3307.pid  [client] port=3307 socket=/tmp/mysql3307.sock 

  • 使用mysqld命令初始化不同的MySQL数据库实例

  /www/server/mysql/bin/mysqld --defaults-file=/www/server/data/3307/3307.cnf --initialize-insecure --user=mysql 

  --defaults-file：这个选项指定了MySQL服务器应该使用的配置文件路径。

  --initialize-insecure：这个选项告诉MySQL在初始化数据库时不设置默认的root用户的密码。这是不安全的（因此称为"insecure"），因为任何人都可以通过本地主机连接到MySQL服务器而无需密码。然而，在某些情况下，比如在受控的环境中或当管理员需要手动设置密码时，这个选项可能是有用的。一旦初始化完成，应该立即更改root用户的密码。

  --user： 这个选项指定MySQL服务器进程运行时所使用的系统用户账户。在这里，MySQL服务器将以名为mysql的用户身份运行。这通常是推荐的做法，因为它有助于隔离MySQL服务器的操作并限制其对系统其他部分的影响。这样做可以提高系统的安全性。

  • 修改mysql的主要配置文件/etc/my.cnf如下,建议在修改之前先备份此文件

  [mysqld_multi] mysqld=/www/server/mysql/bin/mysqld_safe mysqladmin=/www/server/mysql/bin/mysqladmin   [mysqld1] port=3307 socket=/tmp/mysql3307.sock datadir=/www/server/data/3307/data/ skip-external-locking log-bin=/www/server/data/3307/mysql-bin server-id=3307 user=mysql   [mysqld2] port=3308 socket=/tmp/mysql3308.sock datadir=/www/server/data/3308/data/ skip-external-locking log-bin=/www/server/data/3308/mysql-bin server-id=3308 user=mysql  [mysqld3] port=3309 socket=/tmp/mysql3309.sock datadir=/www/server/data/3309/data/ skip-external-locking log-bin=/www/server/data/3309/mysql-bin server-id=3309 user=mysql   [mysql] no-auto-rehash 

  • 启动多个mysql实例

    start后面可以跟上要启动的某个实例编号或者某个范围的实例，根据上面my.cnf配置文件中的[mysqld1]、[mysqld2]、[mysqld3]来确定编号，例如下面的命令同时启动：3307、3308、3309这3个实例。

  /www/server/mysql/bin/mysqld_multi start 1-3 #start后面不跟参数则启动所有实例 

  • 使用命令 netstat -lnpt | grep -E "3307|3308|3309" 查看是否启动成功，出现以下内容则表示启动成功

  root@ctl:/www/server/mysql/bin# netstat -lnpt | grep -E "3307|3308|3309" tcp6       0      0 :::3307                 :::*                    LISTEN      2684676/mysqld       tcp6       0      0 :::3309                 :::*                    LISTEN      2685459/mysqld       tcp6       0      0 :::3308                 :::*                    LISTEN      2684686/mysqld   

2.2 配置读写分离

MySQL实现读写分离的原理，如下图：

• 1.Master主库将对数据库的操作记录到二进制日志文件中。
• 2.Slave从库的I/O thread将主库的日志内容写入到Relay log (中继日志) 中。
• 3.Slave的SQL thread读取Relay log中的内容，并执行。

以3307这个实例作为主库，其它的作为从库。

• 配置3307主库，分别创建两个从库的数据库账号，并设置好对应的权限

  root@ctl: mysql -uroot -p -P3307 -h127.0.0.1 Enter password: #密码为空直接敲回车即可 mysql> show variables like 'port';#查看运行的端口是否正确 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port          | 3307  | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'log_bin';#查看是否开启了log_bin +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin       | ON    | +---------------+-------+ 1 row in set (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)  mysql> create user '3308'@'%' identified by '3308'; #创建从库3308的数据库账号 Query OK, 0 rows affected (0.01 sec)  mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to "3308"@"%";#配置权限 Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 |     5731 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 

  执行语句说明：

  • FLUSH PRIVILEGES; 它的主要作用是重新加载权限表。权限表存储在MySQL的mysql数据库中，其中包含所有用户的账号信息、权限以及资源限制等。当在MySQL中执行了任何与用户权限相关的更改，如新增用户、修改密码、更新权限等，都需要通过执行 FLUSH PRIVILEGES; 命令来确保这些更改立即生效。

  • create user '3308'@'%' identified by '3308';

    create user 这是创建新用户的SQL语句的开始，它告诉MySQL服务器你打算创建一个新的用户账户。

    '3308'@'%'

    • '3308' 是创建的用户名
    • @'%'指定了用户可以连接的主机范围。这里的百分号（%）是一个通配符，表示任何主机。这意味着创建的用户可以从任何IP地址连接到数据库服务器，只要他们知道用户名和密码。如果只想让用户从特定的主机或网络连接，可以将%替换成具体的主机名或IP地址。

    identified by '3308' 子句指定了用户登录数据库时需要的密码,'3308' 是为该用户设置的密码。

  • grant replication slave on *.* to "3308"@"%";

    grant 这是MySQL中用于授予用户权限的关键字。

    replication slave 这个权限类型允许用户从主服务器复制数据。在MySQL的主从复制架构中，从属服务器（slave）接收来自主服务器（master）的日志文件，并应用这些日志中的更改，以保持数据同步。replication slave权限是执行从属服务器角色所必需的。
    on *.* 是一个通配符，表示所有的数据库和所有的表。这意味着正在授予此用户在所有数据库和表上进行复制的权限。如果只想要在特定的数据库或表上进行复制，可以将*.*替换为具体的数据库和表名，例如’database_name’.‘table_name’。
    to "3308"@"%"'3308' 是用户名，@'%' 指定用户可以从任何主机（%是通配符，表示任意主机）连接并使用所授予的权限。

  • show master status; 主要用于显示当前MySQL服务器作为主服务器（Master）时的复制状态信息。这条命令在设置和监控MySQL的主从复制（Replication）时非常有用。它会返回以下几项关键信息：

    • File 这列显示当前正在写入的二进制日志文件的名称。在MySQL中，所有数据库更改都被记录在二进制日志文件中，从属服务器（Slave）就是通过读取这些日志来复制数据的。
    • Position 这列显示在当前二进制日志文件中的位置。这个位置是基于字节的偏移量，指示了最后一个事件在日志文件中的结束位置。从属服务器在进行数据同步时，会读取到这个位置为止的全部日志事件。
    • Binlog_Do_DB 这列显示了在复制过程中被包含的数据库列表。如果这个字段不为空，那么只有列出的数据库的更改会被复制到从属服务器。
    • Binlog_Ignore_DB 这列显示了在复制过程中被排除的数据库列表。如果这个字段不为空，那么列出的数据库的更改将不会被复制到从属服务器。
    • Executed_Gtid_Set 当使用全局事务ID（GTID）模式时，这个字段显示已经执行的GTID集合。GTID是一种更高级的复制机制，可以跟踪每个事务的唯一标识符，使得复制过程更简单、更可靠。

• 配置3308从库，剩下的3309从库参照此步骤进行配置

  在配置从库时需要将主库的数据先导入，这样才能确保两边起始数据都是一致的。

  root@ctl:mysql -uroot -p -P3308 -h127.0.0.1  Enter password:  #密码为空直接敲回车即可  mysql> show variables like 'server_id';  +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | server_id     | 3308  |  +---------------+-------+  1 row in set (0.01 sec)  mysql> CHANGE MASTER TO MASTER_HOST="127.0.0.1",MASTER_PORT=3307,MASTER_USER="3308",MASTER_PASSWORD="3308",MASTER_LOG_FILE="mysql-bin.000003",MASTER_LOG_POS=5731;  Query OK, 0 rows affected, 1 warning (0.03 sec)  mysql> show slave status\G; #显示主从复制信息  *************************** 1. row ***************************                 Slave_IO_State:                     Master_Host: 127.0.0.1                    Master_User: 3308                    Master_Port: 3307                  Connect_Retry: 60                Master_Log_File: mysql-bin.000003            Read_Master_Log_Pos: 5731                 Relay_Log_File: ctl-relay-bin.000001                  Relay_Log_Pos: 4          Relay_Master_Log_File: mysql-bin.000003               Slave_IO_Running: No              Slave_SQL_Running: No                Replicate_Do_DB:             Replicate_Ignore_DB:              Replicate_Do_Table:          Replicate_Ignore_Table:         Replicate_Wild_Do_Table:     Replicate_Wild_Ignore_Table:                      Last_Errno: 0                     Last_Error:                    Skip_Counter: 0            Exec_Master_Log_Pos: 5391                Relay_Log_Space: 154                Until_Condition: None                 Until_Log_File:                   Until_Log_Pos: 0             Master_SSL_Allowed: No             Master_SSL_CA_File:              Master_SSL_CA_Path:                 Master_SSL_Cert:               Master_SSL_Cipher:                  Master_SSL_Key:           Seconds_Behind_Master: NULL  Master_SSL_Verify_Server_Cert: No                  Last_IO_Errno: 0                  Last_IO_Error:                  Last_SQL_Errno: 0                 Last_SQL_Error:     Replicate_Ignore_Server_Ids:                Master_Server_Id: 3307                    Master_UUID: ae6dd756-470d-11ef-899f-fa163e30f76c               Master_Info_File: /www/server/data/3308/data/master.info                      SQL_Delay: 0            SQL_Remaining_Delay: NULL        Slave_SQL_Running_State:              Master_Retry_Count: 86400                    Master_Bind:         Last_IO_Error_Timestamp:        Last_SQL_Error_Timestamp:                  Master_SSL_Crl:              Master_SSL_Crlpath:              Retrieved_Gtid_Set:               Executed_Gtid_Set:                   Auto_Position: 0           Replicate_Rewrite_DB:                    Channel_Name:              Master_TLS_Version:   1 row in set (0.00 sec)    ERROR:   No query specified  mysql> start slave; #启动主从复制进程  Query OK, 0 rows affected (0.00 sec) 

  执行语句说明：

  • CHANGE MASTER TO MASTER_HOST="127.0.0.1",MASTER_PORT=3307,MASTER_USER="3308",MASTER_PASSWORD="3308",MASTER_LOG_FILE="mysql-bin.000003",MASTER_LOG_POS=5731;

    CHANGE MASTER TO用于配置从属服务器（Slave）以连接到主服务器（Master），并指定复制的起点和相关参数。
    MASTER_HOST="127.0.0.1" 指定了主服务器的IP地址。
    MASTER_PORT=3307 指定了主服务器上MySQL监听的TCP/IP端口号。
    MASTER_USER="3308" 指定了用于从属服务器连接到主服务器的用户名。
    MASTER_PASSWORD="3308" 指定了MASTER_USER用户连接到主服务器时的密码。
    MASTER_LOG_FILE="mysql-bin.000003" 这指定了从属服务器开始复制的二进制日志文件名(使用show master status命令在主库里面查看，对应File的值)。
    MASTER_LOG_POS=5731 这指定了在指定的二进制日志文件中的起始位置（以字节为单位）。从属服务器将从这个位置开始读取和复制日志事件(使用show master status命令在主库里面查看，对应Position的值)。

  完成以上配置后，这时主库的数据库相关操作从库也会执行，具体情况自行测试。如果从库并没有执行，很有可能中途报错了，可以使用命令 show slave status\G 查看错误或者查看日志（日志位于/www/server/data/3308/data/ctl.err,具体文件名可能会发生变化，找到 .err结尾的文件）进行分析。

    #.err文件内容如下：   2024-07-21T09:08:43.677113Z 14 [Note] Slave I/O thread for channel '': connected to master '3308@127.0.0.1:3307',replication started in log 'mysql-bin.000003' at position 5731   2024-07-21T09:08:43.677715Z 15 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0   2024-07-21T09:08:43.677750Z 15 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000003' at position 5731, relay log './ctl-relay-bin.000001' position: 4 

  使用 show slave status\G 命令查看，报错时显示如下：

      mysql> show slave status\G;   *************************** 1. row ***************************                  Slave_IO_State: Waiting for master to send event                     Master_Host: 127.0.0.1                     Master_User: 3308                     Master_Port: 3307                   Connect_Retry: 60                 Master_Log_File: mysql-bin.000003             Read_Master_Log_Pos: 5391                  Relay_Log_File: ctl-relay-bin.000002                   Relay_Log_Pos: 320           Relay_Master_Log_File: mysql-bin.000003                Slave_IO_Running: Yes               Slave_SQL_Running: No                 Replicate_Do_DB:              Replicate_Ignore_DB:               Replicate_Do_Table:           Replicate_Ignore_Table:          Replicate_Wild_Do_Table:      Replicate_Wild_Ignore_Table:                       Last_Errno: 1049                      Last_Error: Error 'Unknown database 'master'' on query. Default database: 'master'. Query: 'CREATE TABLE `user` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `name` VARCHAR ( 255 ) DEFAULT NULL, `age` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` )  ) ENGINE = INNODB DEFAULT CHARSET = utf8'                    Skip_Counter: 0             Exec_Master_Log_Pos: 2630                 Relay_Log_Space: 3286                 Until_Condition: None                  Until_Log_File:                    Until_Log_Pos: 0              Master_SSL_Allowed: No              Master_SSL_CA_File:               Master_SSL_CA_Path:                  Master_SSL_Cert:                Master_SSL_Cipher:                   Master_SSL_Key:            Seconds_Behind_Master: NULL   Master_SSL_Verify_Server_Cert: No                   Last_IO_Errno: 0                   Last_IO_Error:                   Last_SQL_Errno: 1049                  Last_SQL_Error: Error 'Unknown database 'master'' on query. Default database: 'master'. Query: 'CREATE TABLE `user` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `name` VARCHAR ( 255 ) DEFAULT NULL, `age` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` )  ) ENGINE = INNODB DEFAULT CHARSET = utf8'     Replicate_Ignore_Server_Ids:                 Master_Server_Id: 3307                     Master_UUID: ae6dd756-470d-11ef-899f-fa163e30f76c                Master_Info_File: /www/server/data/3308/data/master.info                       SQL_Delay: 0             SQL_Remaining_Delay: NULL         Slave_SQL_Running_State:               Master_Retry_Count: 86400                     Master_Bind:          Last_IO_Error_Timestamp:         Last_SQL_Error_Timestamp: 240721 16:49:34                  Master_SSL_Crl:               Master_SSL_Crlpath:               Retrieved_Gtid_Set:                Executed_Gtid_Set:                    Auto_Position: 0            Replicate_Rewrite_DB:                     Channel_Name:               Master_TLS_Version:    1 row in set (0.00 sec) 

延伸内容：

• 如何使用数据库中间件来实现读写分离，例如：MyCat
• 在代码层面如何使用主从数据库？