部署主从数据库

主从数据库

一、什么是主从数据库

​ 主从数据库是一种数据库架构，在这种架构中，一个数据库服务器被配置为主服务器（Master），一个或多个数据库服务器被配置为从服务器（Slave）。主从数据库的架构主要用于数据复制、负载均衡、读写分离以及数据冗余等目的。

  1. 主服务器（Master）:
    • 写操作的中心: 主服务器是处理数据库写操作的主要节点，如 INSERT、UPDATE、DELETE 等语句。
    • 数据复制源: 主服务器将其所有的数据变更（包括插入、更新和删除操作）通过二进制日志（Binary Log）记录下来，并传送给从服务器。
  2. 从服务器（Slave）:
    • 读操作的分担者: 从服务器主要负责处理数据库的读操作，如 SELECT 查询。多个从服务器可以通过负载均衡机制分担读操作的压力，从而减轻主服务器的负担。
    • 数据复制目的地: 从服务器通过读取主服务器的二进制日志，将主服务器上的数据变更应用到自己的数据库中，以保持与主服务器数据的一致性。

二、主从数据库的作用

​ 主从数据库（Master-Slave Database）的架构是一种常见的数据库复制和负载均衡方案，主要用于提高数据库的可用性、扩展性和数据冗余性。以下是主从数据库架构的主要作用：

  1. 数据冗余与高可用性

    • 数据备份: 主数据库（Master）上的数据会实时复制到从数据库（Slave），提供了数据的冗余备份。当主数据库出现故障时，从数据库可以接管，保证系统的可用性。
    • 故障切换: 如果主数据库发生故障，从数据库可以被提升为主数据库（这一过程可以手动或通过自动化脚本完成），从而保证系统的持续运行。

  2. 读写分离

    • 性能优化: 在读写分离的架构中，主数据库处理写操作（INSERT、UPDATE、DELETE），而从数据库处理读操作（SELECT）。这样可以减轻主数据库的负担，提高整体系统的性能。
    • 负载均衡: 多个从数据库可以分担读取请求的压力，通过负载均衡机制，系统可以处理更多的并发读请求，进一步提升性能。

  3. 数据分析与报表生成

    • 业务分离: 从数据库可以用于数据分析、报表生成等任务，而不会影响主数据库的性能和响应时间。这使得复杂的查询和报表生成不会拖慢主数据库的响应速度。

  4. 地理分布与容灾

    • 跨地域部署: 主从数据库可以部署在不同的地理位置，从而实现跨地域的数据备份和容灾。一旦某个区域的数据中心出现问题，其他区域的从数据库可以接管业务，减少灾难带来的影响。

  5. 在线备份

    • 热备份: 从数据库可以用于在线备份，不需要停止主数据库的运行即可进行完整的数据备份，确保业务的连续性和数据的安全性。

  6. 数据恢复

    • 快速恢复: 在数据意外丢失或损坏时，可以从从数据库中恢复数据，减少数据丢失的风险。

​ 主从数据库架构是实现高可用、高性能和高可靠性的重要手段，特别是在大规模系统中，通过这种架构可以有效地提升数据库系统的整体性能和稳定性。

三、MySQL 主从配置

IP 地址1(内网) IP 地址2(外网)
master 192.168.92.150 192.168.75.10
slave 192.168.92.151 192.168.75.20

1. 在两台机器上都部署 MySQL

1.1 安装 MySQL

​ 将二进制免编译包上传到 /usr/local/src 目录下，然后在两台机器上都进行如下操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ cd /usr/local/src/ #自己通过源码包配置安装的软件包都推荐放到这个位置

# 解压后挪动位置
$ sudo tar -zxf mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz
$ sudo mv mysql-5.6.49-linux-glibc2.12-x86_64 /usr/local/mysql

# 创建一个不能用于登录的账户 mysql，因为启动 MySQL 需要用到此账户
$ sudo useradd -s /sbin/nologin mysql

# 创建 MySQL 数据目录，然后授予权限
$ sudo mkdir -p /data/mysql
$ sudo chown -R mysql:mysql /data/mysql/
$ sudo chmod -R 755 /data/mysql

# 初始化 MySQL 数据目录：指定 MySQL 服务的用户是 mysql (创建的文件和目录的 owner 都会是 mysql)，，并指定 MySQL 数据目录
$ cd /usr/local/mysql
# 安装必要环境
$ sudo yum install -y perl-Module-Install
# 开始编译
$ sudo ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

  • 检查是否安装成功：

    ​ 如果向上面一样看到了两个 OK 且在 /data/mysql 下看到了生成的文件和目录，说明执行正确

    ​ 或者是在执行完最后一条命令江批，马上执行 echo $? 看输出的结果为 **0**，说明执行正确
    ​ 3.1.1

1.2 配置 MySQL

​ 首先复制配置文件，然后打开配置文件并把配置文件改成如下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ cp support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
# 这里提示是否覆盖是因为系统内默认就有 /etc/my.cnf，直接按y就行

$ sudo vi support-files/my-default.cnf

# 替换成以下内容
[mysqld]

innodb_buffer_pool_size = 128M #第11行

log_bin = moka #第15行

basedir = /usr/local/mysql #第18行，是MySQL包的所在地
datadir = /data/mysql #第19行，存放数据的地方
port = 3306 #第20行，默认端口号就是3306
server_id = 128 #第21行，服务的ID号
socket = /tmp/mysql.sock #第22行，MySQL服务监听的嵌套字地址

## 关于内存的配置参数，保持默认即可
join_buffer_size = 128M #第27行
sort_buffer_size = 2M #第28行
read_rnd_buffer_size = 2M #第29行
  • 备注：
    嵌套字地址：在 Linux 系统下，很多服务不仅可以监听一个端口（通过 TCP/IP 的方式通信），也可以监听 socket，两个进程就可以通过这个 socket 文件通信

​ 然后复制启动脚本并修改其属性

1
2
$ sudo cp support-files/mysql.server /etc/init.d/mysqld
$ sudo chmod 755 /etc/init.d/mysqld

​ 然后修改启动脚本，并把启动脚本加入系统服务项，然后设置开机启动并启动 MySQL

1
2
3
4
5
6
7
8
$ vi /etc/init.d/mysqld

# 修改的内容
datadir=/data/mysql #第47行

$ sudo chkconfig --add mysqld #把mysqld服务加入系统服务列表中
$ sudo chkconfig mysqld on #设置开机启动
$ sudo service mysqld start #启动服务

  • 备注：

    ​ 如果启动不了，就到 data/mysql 目录下查看错误日志，这个日志名通常是 [主机名].err
    检查 MySQL 是否启动的命令为：

    1
    2
    3
    4
    5
    6
    7
    8
    # 检查 mysqld 服务的状态
    $ sudo service mysqld status

    # 查看 mysqld 相关进程（结果应该大于2行）
    $ sudo ps aux | grep mysqld

    # 看看有没有监听3306端口
    $ sudo netstat -lnp | grep 3306
    • 最后一个要是提示 -bash: netstat: 未找到命令 的，就是用命令 sudo yum install -y net-tools ，安装之后就可以用了

2. 主服务器配置（master）

2.1 基础配置

​ 编辑配置文件

1
2
3
$ sudo vi /usr/local/mysql/support-files/my-default.cnf
# 修改此行
server_id = 13 #第21行

3.2.1_1

​ 重启 mysqld 服务

1
2
3
$ sudo /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

​ 加入环境变量后备份 mysql 库

1
2
3
4
5
6
# 加入环境变量
$ sudo echo "export PATH=$PATH:/usr/local/mysql/bin/"  >> /etc/profile
$ sudo source /etc/profile

# 备份 mysql 数据库
$ mysqldump -uroot mysql > /tmp/mysql.sql

3.2.1_2

​ 创建一个库保存数据

1
$ mysql -uroot -e 'CREATE DATABASE kei'

​ 将 mysql 库恢复成新建的库，作为测试数据

1
$ mysql -uroot kei < /tmp/mysql.sql

3.2.1_3

2.2 数据库配置

​ 进入数据库(没有密码)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

​ 创建用作同步数据的用户并赋予权限

1
2
mysql> GRANT REPLICATION slave on *.* TO 'repl' @192.168.92.% IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

​ 把表锁住，保持表内数据不变

1
2
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

3.2.2_1

​ 显示主机状态

1
2
3
4
5
6
7
mysql> SHOW master STATUS;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| moka.000001 |      120 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.2.2_2

在这步的时候遇到了这个问题

1
mysql> SHOW master STATUS;                                                           Empty set (0.00 sec)

需要确认配置文件中配置了 log-bin 选项

确认之后发现之前配置了

然后发现 MySQL 的默认读取的配置文件是 /etc/my.cnf 而不会读取 /usr/local/mysql/support-files/my-default.cnf

所以解决方法：

1
2
3
4
# 首先复制原本配置好的配置文件到新配置文件
$ sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
# 然后重启 mysqld 服务
$ sudo systemctl restart mysqld

然后再进入 MySQL 中输入那个命令，就会正常显示

3. 从服务器配置（slave）

3.1 基础配置

​ 编辑配置文件

1
2
3
4
5
6
7
8
9
10
11
12
# 吸取上面的教训，先复制配置文件
$ sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y

$ sudo vi /usr/local/mysql/support-files/my-default.cnf
# 修改此行
server_id = 13 #第21行（注：

# 然后重启 mysqld 服务
$ /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

3.3.1_1

​ 在 master 上将文件拷贝到 slave 上，并在 slave 上查看文件大小是否一致

1
2
3
4
5
6
7
8
9
10
11
12
# 在 master 节点上
$ scp /tmp/mysql.sql [email protected]:/tmp/
The authenticity of host '192.168.92.151 (192.168.92.151)' can't be established.
ECDSA key fingerprint is SHA256:LVuXc2s5PJkwqgsTC1Z370zHUtAzwxR2wvcJOfVONAY.
ECDSA key fingerprint is MD5:e4:84:3c:84:a3:ee:73:1b:c8:fb:d2:c9:7a:1e:05:d2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.92.151' (ECDSA) to the list of known hosts.
[email protected]'s password: 
mysql.sql                                                                       100%  692KB  27.4MB/s   00:00

$ ls -l /tmp/mysql.sql 
-rw-r--r-- 1 root root 708581 8月  28 15:26 /tmp/mysql.sql

3.3.1_2

3.3.1_3

​ 加入环境变量后创建一个和 master 一样的库

1
2
3
4
5
6
# 加入环境变量
$ sudo echo "export PATH=$PATH:/usr/local/mysql/bin/"  >> /etc/profile
$ sudo source /etc/profile

# 创建一个和 master 一样的库
$ mysql -uroot -e 'CREATE DATABASE kei'

3.3.1_4

​ 将文件内容导入到 mysql 数据库

1
$ mysql -uroot kei < /tmp/mysql.sql
3.2 数据库配置

​ 进入数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

​ 在 slave 服务器上配置 master 服务器的信息，以便正常通讯

1
2
mysql> CHANGE master TO master_host='192.168.92.150',master_user='repl',master_password='123456',master_log_file='moka.000001',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

​ 在 master 上执行解锁表

1
2
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

4. 主从同步及相关配置参数

​ 主从同步认证

​ 在 slave 服务器上操作并执行命令（需要提前关闭防火墙）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.92.150
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: moka.000001
          Read_Master_Log_Pos: 408
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 278
        Relay_Master_Log_File: moka.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 408
              Relay_Log_Space: 609
              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: 0
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: 13
                  Master_UUID: 0312dd12-6509-11ef-95b0-000c2957422b
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

3.4

​ 若出现 YES，像上图一样，即表示主从配置正常

​ 其中，master 主要配置参数如下所示：

1
2
3
4
5
6
7
8
$ vi /etc/my.cnf
# 在 [mysqld] 的下面配置

binlog-do-db=kei #这里如果填写上，就是表示仅同步指定的数据库
binlog-ignore-db= #这里如果填写上，就是表示忽略指定的数据库

# 然后重启 MySQL 服务使其生效
$ sudo systemctl restart mysqld

​ 其中，slave 主要配置参数如下所示

1
2
3
4
5
6
7
$ vi /etc/my.cnf
# 在 [mysqld] 的下面配置

replicate-do-db=kei    #这里如果填写上，就是表示只同步指定的数据库

# 然后重启 MySQL 服务使其生效
$ sudo systemctl restart mysqld

5. 测试主从服务器

5.1 在 master 服务器上
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 进入数据库
$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> USE kei;
Database changed
mysql> SELECT COUNT(*) FROM db;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

3.5_1

5.2 在 slave 服务器上
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 进入数据库
$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> USE kei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT COUNT(*) FROM db;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

3.5_2

​ 至此，主从数据库配置完成

#MariaDB
部署主从数据库
https://moka.anitsuri.top/2024/08/26/Primary-Replica_Database/
作者
アニつり
发布于
2024年8月26日
许可协议