主从数据库

一、什么是主从数据库

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

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

二、主从数据库的作用

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

数据冗余与高可用性
- 数据备份: 主数据库（Master）上的数据会实时复制到从数据库（Slave），提供了数据的冗余备份。当主数据库出现故障时，从数据库可以接管，保证系统的可用性。
- 故障切换: 如果主数据库发生故障，从数据库可以被提升为主数据库（这一过程可以手动或通过自动化脚本完成），从而保证系统的持续运行。
读写分离
- 性能优化: 在读写分离的架构中，主数据库处理写操作（INSERT、UPDATE、DELETE），而从数据库处理读操作（SELECT）。这样可以减轻主数据库的负担，提高整体系统的性能。
- 负载均衡: 多个从数据库可以分担读取请求的压力，通过负载均衡机制，系统可以处理更多的并发读请求，进一步提升性能。
数据分析与报表生成
- 业务分离: 从数据库可以用于数据分析、报表生成等任务，而不会影响主数据库的性能和响应时间。这使得复杂的查询和报表生成不会拖慢主数据库的响应速度。
地理分布与容灾
- 跨地域部署: 主从数据库可以部署在不同的地理位置，从而实现跨地域的数据备份和容灾。一旦某个区域的数据中心出现问题，其他区域的从数据库可以接管业务，减少灾难带来的影响。
在线备份
- 热备份: 从数据库可以用于在线备份，不需要停止主数据库的运行即可进行完整的数据备份，确保业务的连续性和数据的安全性。
数据恢复
- 快速恢复: 在数据意外丢失或损坏时，可以从从数据库中恢复数据，减少数据丢失的风险。

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

三、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
$ cd /usr/local/src/ #自己通过源码包配置安装的软件包都推荐放到这个位置
2

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

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

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

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

检查是否安装成功：
如果向上面一样看到了两个 OK 且在 /data/mysql 下看到了生成的文件和目录，说明执行正确
或者是在执行完最后一条命令江批，马上执行 echo $? 看输出的结果为 0，说明执行正确

1.2 配置 MySQL

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

1
$ cp support-files/my-default.cnf /etc/my.cnf
2
cp：是否覆盖"/etc/my.cnf"？ y
3
# 这里提示是否覆盖是因为系统内默认就有 /etc/my.cnf，直接按y就行
4

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

7
# 替换成以下内容
8
[mysqld]
9

10
innodb_buffer_pool_size = 128M #第11行
11

12
log_bin = moka #第15行
13

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

20
## 关于内存的配置参数，保持默认即可
21
join_buffer_size = 128M #第27行
22
sort_buffer_size = 2M #第28行
23
read_rnd_buffer_size = 2M #第29行

备注：嵌套字地址：在 Linux 系统下，很多服务不仅可以监听一个端口（通过 TCP/IP 的方式通信），也可以监听 socket，两个进程就可以通过这个 socket 文件通信

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

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

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

1
$ vi /etc/init.d/mysqld
2

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

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

备注：
如果启动不了，就到 data/mysql 目录下查看错误日志，这个日志名通常是 [主机名].err 检查 MySQL 是否启动的命令为：
Terminal window
```
1
# 检查 mysqld 服务的状态
2
$ sudo service mysqld status
3

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

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

2. 主服务器配置（master）

2.1 基础配置

编辑配置文件

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

3.2.1_1

重启 mysqld 服务

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

加入环境变量后备份 mysql 库

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

5
# 备份 mysql 数据库
6
$ 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
$ mysql -uroot
2
Welcome to the MySQL monitor.  Commands end with ; or \g.
3
Your MySQL connection id is 1
4
Server version: 5.6.49 MySQL Community Server (GPL)
5

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

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

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

14
mysql>

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

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

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

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

3.2.2_1

显示主机状态

1
mysql> SHOW master STATUS;
2
+-------------+----------+--------------+------------------+-------------------+
3
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4
+-------------+----------+--------------+------------------+-------------------+
5
| moka.000001 |      120 |              |                  |                   |
6
+-------------+----------+--------------+------------------+-------------------+
7
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
所以解决方法：
Terminal window
1
# 首先复制原本配置好的配置文件到新配置文件
2
$ sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
3
# 然后重启 mysqld 服务
4
$ sudo systemctl restart mysqld
然后再进入 MySQL 中输入那个命令，就会正常显示

3. 从服务器配置（slave）

3.1 基础配置

编辑配置文件

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

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

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

3.3.1_1

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

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

11
$ ls -l /tmp/mysql.sql
12
-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
$ sudo echo "export PATH=$PATH:/usr/local/mysql/bin/"  >> /etc/profile
3
$ sudo source /etc/profile
4

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

3.3.1_4

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

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

3.2 数据库配置

进入数据库

1
$ mysql -uroot
2
Welcome to the MySQL monitor.  Commands end with ; or \g.
3
Your MySQL connection id is 3
4
Server version: 5.6.49-log MySQL Community Server (GPL)
5

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

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

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

14
mysql>

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

1
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;
2
Query OK, 0 rows affected, 2 warnings (0.03 sec)

在 master 上执行解锁表

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

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

主从同步认证

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

1
mysql> SHOW SLAVE STATUS\G;
2
*************************** 1. row ***************************
3
               Slave_IO_State: Waiting for master to send event
4
                  Master_Host: 192.168.92.150
5
                  Master_User: repl
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File: moka.000001
9
          Read_Master_Log_Pos: 408
10
               Relay_Log_File: slave-relay-bin.000004
11
                Relay_Log_Pos: 278
12
        Relay_Master_Log_File: moka.000001
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes
15
              Replicate_Do_DB:
16
          Replicate_Ignore_DB:
17
           Replicate_Do_Table:
18
       Replicate_Ignore_Table:
19
      Replicate_Wild_Do_Table:
20
  Replicate_Wild_Ignore_Table:
21
                   Last_Errno: 0
22
                   Last_Error:
23
                 Skip_Counter: 0
24
          Exec_Master_Log_Pos: 408
25
              Relay_Log_Space: 609
26
              Until_Condition: None
27
               Until_Log_File:
28
                Until_Log_Pos: 0
29
           Master_SSL_Allowed: No
30
           Master_SSL_CA_File:
31
           Master_SSL_CA_Path:
32
              Master_SSL_Cert:
33
            Master_SSL_Cipher:
34
               Master_SSL_Key:
35
        Seconds_Behind_Master: 0
36
Master_SSL_Verify_Server_Cert: No
37
                Last_IO_Errno: 0
38
                Last_IO_Error:
39
               Last_SQL_Errno: 0
40
               Last_SQL_Error:
41
  Replicate_Ignore_Server_Ids:
42
             Master_Server_Id: 13
43
                  Master_UUID: 0312dd12-6509-11ef-95b0-000c2957422b
44
             Master_Info_File: /data/mysql/master.info
45
                    SQL_Delay: 0
46
          SQL_Remaining_Delay: NULL
47
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48
           Master_Retry_Count: 86400
49
                  Master_Bind:
50
      Last_IO_Error_Timestamp:
51
     Last_SQL_Error_Timestamp:
52
               Master_SSL_Crl:
53
           Master_SSL_Crlpath:
54
           Retrieved_Gtid_Set:
55
            Executed_Gtid_Set:
56
                Auto_Position: 0
57
1 row in set (0.00 sec)

3.4

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

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

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

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

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

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

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

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

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

5. 测试主从服务器

5.1 在 master 服务器上

1
# 进入数据库
2
$ mysql -uroot
3
Welcome to the MySQL monitor.  Commands end with ; or \g.
4
Your MySQL connection id is 3
5
Server version: 5.6.49-log MySQL Community Server (GPL)
6

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

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

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

15
mysql>
16

17
mysql> USE kei;
18
Database changed
19
mysql> SELECT COUNT(*) FROM db;
20
+----------+
21
| COUNT(*) |
22
+----------+
23
|        2 |
24
+----------+
25
1 row in set (0.00 sec)

3.5_1

5.2 在 slave 服务器上

1
# 进入数据库
2
$ mysql -uroot
3
Welcome to the MySQL monitor.  Commands end with ; or \g.
4
Your MySQL connection id is 3
5
Server version: 5.6.49-log MySQL Community Server (GPL)
6

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

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

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

15
mysql>
16

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

21
Database changed
22
mysql> SELECT COUNT(*) FROM db;
23
+----------+
24
| COUNT(*) |
25
+----------+
26
|        2 |
27
+----------+
28
1 row in set (0.01 sec)

3.5_2

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

Thanks for reading!

部署主从数据库

周一 8月 26 2024

3252 字 · 18 分钟

Documentation 云运维工坊 MySQL 基础内容