MySQL数据库主从部署（详细教程+思维导图）

Hu_O&M

已于 2024-08-19 03:04:04 修改

阅读量544

点赞数 20

文章标签：数据库 mysql 运维服务器

于 2024-08-19 02:53:59 首次发布

本文链接：https://blog.csdn.net/2301_78088515/article/details/141308333

版权

主从介绍：

1. 主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。

想几个问题：

用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？

业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？

2. 主从作用

实时灾备，用于故障切换

读写分离，提供查询服务

备份，避免影响业务

3. 主从形式

1. 一主一从

2. 主主复制

3. 多主一从---5.7版本开始支持

4. 一主多从---扩展系统读取的性能，因为读是在从库读取的

5. 联级复制

4. 主从复制原理

主从复制步骤：

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程，从库生成两个线程，一个I/O线程，一个SQL线程。

I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中；SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的。

5. 主从复制配置

主从复制配置步骤：

1. 确保从数据库与主数据库里的数据一样

2. 在主数据库里创建一个同步账号授权给从数据库使用

3. 配置主数据库（修改配置文件）

4. 配置从数据库（修改配置文件）

需求：

搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作。

开始部署：

1. 为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中。

//先看主数据库有哪一些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xs                 |
+--------------------+
5 rows in set (0.00 sec)
 
//从数据库的表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

2. 全备主库

此时，全备主库需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致。

mysql> flush tables with read lock;    // 此锁表的终端必须在备份完成以后才能退出
Query OK, 0 rows affected (0.01 sec)
 
 
此时在另一个终端进行全备主库
[root@node1 ~]# mysqldump -uroot -p --all-databases > /opt/all-202408019.sql
Enter password: 
[root@node1 ~]# cd /opt/
[root@node1 opt]# ls
all-8060910.sql  data  software
// 已经做好备份
//将备份文件传送到从库

[root@node1 opt]# scp /opt/all-202408019.sql root@192.168.200.20:/opt/
The authenticity of host '192.168.200.20 (192.168.200.20)' can't be established.
ED25519 key fingerprint is SHA256:xo9bPX4Jdw8te4J7fvEtoC0MfdV7pifCiky5aULYSyI.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.200.20' (ED25519) to the list of known hosts.
root@192.168.200.20's password: 
all-202408019.sql                                                 100%  856KB 100.7MB/s   00:00    
[root@node1 opt]# 
  
 
//解锁主库锁表状态，直接退出交互式界面
 mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
 
mysql> exit
Bye
[root@node1 ~]#

3. 在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致。

[root@node2 ~]# mysql -uroot -p < /opt/all-202408019.sql 
Enter password: 
[root@node2 ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xs                 |
+--------------------+
[root@node2 ~]#

4. 在主数据库里创建一个同步账号授权给从数据库使用。

[root@node1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> create user 'repl'@'192.168.200.20' identified by 'linux';    //创建同步账号
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.200.20';    //设置从库权限
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;        // 刷新
Query OK, 0 rows affected (0.00 sec)

5. 配置主数据库，重启mysql服务，查看主库状态。

[root@node1 ~]# vim /etc/my.cnf 
[root@node1 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 1        //数据库服务器唯一标识符，主库的server-id值必须比从库的小
log-bin = mysql_bin
log-error=/var/log/mysqld.log
symbolic-links=0
[root@node1 ~]# 
[root@node1 ~]# systemctl restart mysqld.service 
[root@node1 ~]# ss -antl        //查看端口
State      Recv-Q     Send-Q         Local Address:Port           Peer Address:Port     Process     
LISTEN     0          128                  0.0.0.0:22                  0.0.0.0:*                    
LISTEN     0          128                     [::]:22                     [::]:*                    
LISTEN     0          80                         *:3306                      *:*                    
[root@node1 ~]# 
[root@node1 ~]# mysql -uroot -p123456 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

6. 配置从数据库。

[root@node2 ~]# vim /etc/my.cnf 
[root@node2 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 2            //设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin    //启用中继日志relay-log
symbolic-links=0
log-error=/var/log/mysqld.log
[root@node2 ~]# 
[root@node2 ~]# systemctl restart mysqld.service 
[root@node2 ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port           Peer Address:Port     Process     
LISTEN     0          128                  0.0.0.0:22                  0.0.0.0:*                    
LISTEN     0          80                         *:3306                      *:*                    
LISTEN     0          128                     [::]:22                     [::]:*                    
[root@node2 ~]#

7. 配置并启动主从复制，在从库上做。

mysql> change master to master_host='192.168.200.10',    // 指定主库
    -> master_user='repl',                               // 用户
    -> master_password='linux',                         // 密码
    -> master_log_file='mysql_bin.000001',               // 日志，这两个是查看主库的状态下的
    -> master_log_pos=154;                               // 日志
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;            // 启动从库
Query OK, 0 rows affected (0.00 sec)

mysql>

8. 查看从服务器状态。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes            //I/O线程启动成功（yes成功）
            Slave_SQL_Running: Yes            //SQL线程启动成功（yes成功）

测试验证：

在主库xs库的hyh表中插入数据，看从库是否同步成功。

mysql> insert into hyh values(3,'tom'),(4,'jok');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from hyh;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | tom      |
|  4 | jok      |
+----+----------+
4 rows in set (0.00 sec)
 
 
// 查看从数据库是否同步
mysql> select * from hyh;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | tom      |
|  4 | jok      |
+----+----------+
4 rows in set (0.00 sec)
//同步成功

查看binlog日志：

从BEGIN（开始）位置向下看，主库做了什么操作从库就跟着做什么操作。

mysql> show relaylog events in 'mysql-relay-bin.000001';
+------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name               | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-relay-bin.000002 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.37, Binlog ver: 4     |
| mysql-relay-bin.000002 | 123 | Previous_gtids |         2 |         154 |                                       |
| mysql-relay-bin.000002 | 154 | Rotate         |         1 |           0 | mysql_bin.000006;pos=1388             |
| mysql-relay-bin.000002 | 201 | Format_desc    |         1 |           0 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-relay-bin.000002 | 320 | Anonymous_Gtid |         1 |        1453 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-relay-bin.000002 | 385 | Query          |         1 |        1523 | BEGIN                                 |
| mysql-relay-bin.000002 | 455 | Table_map      |         1 |        1572 | table_id: 141 (xs.class)              |
| mysql-relay-bin.000002 | 504 | Write_rows     |         1 |        1631 | table_id: 141 flags: STMT_END_F       |
| mysql-relay-bin.000002 | 563 | Xid            |         1 |        1662 | COMMIT /* xid=465 */                  |
+------------------------+-----+----------------+-----------+-------------+---------------------------------------+
9 rows in set (0.00 sec)

思维导图：

Hu_O&M

关注

20
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库主从部署（详细教程+思维导图）

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程，从库生成两个线程，一个I/O线程，一个SQL线程。I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中；SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的。
复制链接

扫一扫