MySQL主从

阿干tkl

已于 2022-11-03 17:59:51 修改

阅读量1.4k

点赞数 1

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

于 2022-07-03 17:54:24 首次发布

本文链接：https://blog.csdn.net/m0_58805648/article/details/125587023

版权

MySQL主从

1. 主从简介

mysql的主从是用来同步数据的，读写分离，如果公司里面数据量比较少，规模比较小，没有必要做主从。

当规模上到一定的程度时和数据量大，一台数据库的主机处理不了那么多的需求了，这时候需要分工。弄一台，两台，N台服务器来分工。哪些主机用来读，哪些用来写，一般情况下主负责写，从负责读。

1.2mysql主从的企业应用场景

应用场景：从服务器作为主服务器的实时数据备份

主从服务器的架构设置，可以大大加强MySQL数据库架构的健壮性。例如：当主服务器出现故障时，我们就可以设置自动却换到从服务器继续提供服务，从而不会中断业务，从服务器的数据和down机时的主数据库几乎是一致的。

举例子：

你有一辆越野车，你在开的时候有一个轮胎坏了，你的越野车后背箱有一个备胎，这时你就可用拿来用了。
这个备胎就相当于从数据库，当主数据库宕机之后它就会顶上去，保障业务还在运行。

1.3 主从作用

实时灾备，用于故障切换

也就是说主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。

读写分离，提供查询服务

意思就是你有两个数据库A数据库和B数据库，A数据库写，B数据库用来读。效率得到提高。

一般主数据库是用来写的，从数据库用来读。

备份，避免影响业务

这里的备份是指：当某一台数据库宕机后，另一台数据库替换上去，以确保业务不会中断。

1.4 主从形式

在这里插入图片描述

一主一从

一主一从：当主数据库宕机后，从数据库顶上去。

主主复制

比如有两个主数据库A和主数据库B

A主数据库宕机了，B数据库继续运行。它们是平级的，没有从数据库。

有缺陷：平时只有一个工作，资源浪费。

A数据库的数据放到它的本机，B数据库的数据也放到它的本机，它们之间的数据不相同。

主主复制的时候必须要用一个共享存储，来确保它俩的数据是一致的。

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

一主多从场景用法

写操作比较少时，读操作比较多时，你就用一主多从。

多主一从—5.7开始支持

跟一主多从场景相反
写操作比较多时，读操作比较少时，你就用多主一从。

联级复制

2. 主从复制原理

在这里插入图片描述

主从复制步骤：

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

3. 主从复制配置

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

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

环境说明：

数据库角色	IP	应用与系统版本	有无数据	名字
主数据库	192.168.229.129	centos8/redhat8 mysql-5.7	有数据	node1
从数据库	192.168.229.145	centos8/redhat8 mysql-5.7	无数据	node2

3.1 mysql安装

分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请看这里http://t.csdn.cn/anoYz和http://t.csdn.cn/onlCU 两篇文章。

3.2 mysql主从配置

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

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

# 先查看主库有哪些库
[root@node1 ~]# mysql -uroot -p -e 'show databases;'
Enter password: # 输入密码
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
+--------------------+

# 再查看从库有哪些库
[root@node2 ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+



# 全备主库
# 全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
# 此锁表的终端必须在备份完成以后才能退出

# 备份主库并将备份文件传送到从库
[root@node1 ~]# mysqldump -uroot -ptkl9639@G --all-databases > /opt/all-$(date '+%Y-%m-%d:%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 查看/opt/
[root@node1 ~]# ls /opt/
all-2022-07-03:16-15-40.sql
[root@node1 ~]# scp /opt/all-2022-07-03\:16-15-40.sql root@192.168.229.145:/opt/
all-2022-07-03:16-15-40.sql                100%  856KB  72.1MB/s   00:00


# 解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye


# 在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@node2 ~]# mysql -uroot -p < /opt/all-2022-07-03\:16-15-40.sql
Enter password: # 输入你的密码

[root@node2 ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
+--------------------+

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

# 创建用户tom
mysql> CREATE USER 'tom'@'192.168.229.145' IDENTIFIED BY 'tom123';
Query OK, 0 rows affected (0.00 sec)

# 授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'tom'@'192.168.229.145';
Query OK, 0 rows affected (0.00 sec)

# 刷新授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.2.3 配置主数据库

# 在[mysqld]这段的后面加上如下内容
[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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin = mysql-bin  # 启用binlog日志
server-id = 10  # 数据库服务器唯一标识符，主库的server-id值必须比从库的小


# 重启mysql服务
[root@node1 ~]# systemctl restart mysqld
[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      80                  *:3306              *:*
LISTEN 0      128              [::]:22             [::]:*



# 查看主库的状态
[root@node1 ~]# mysql -uroot -ptkl9639@G -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 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3.2.4 配置从数据库

# 在配置从数据库之前可以先关闭防火墙和SElinux
[root@node2 ~]# systemctl disable --now firewalld

# 关闭SElinux
[root@node2 ~]# head -7 /etc/selinux/config | tail -1
SELINUX=disabled

# 添加如下内容
[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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id = 20 # 设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin # 启用中继日志relay-log
            # 这个名字没有要求，随便mysql-relay-bin
  


# 重启从库的mysql服务
[root@node2 ~]# systemctl restart mysqld
[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                  [::]:*


## 进去从库里配置主从
# 配置并启动主从复制
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.229.129', 主库ip
MASTER_USER='tom',
MASTER_PASSWORD='tom123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.33 sec)

# 启动主从
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)


# 查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.229.129
                  Master_User: tom
                  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   # 此处必须为Yes
            Slave_SQL_Running: Yes   # 此处必须为Yes
            ......省略N

3.2.5 测试验证

在主服务器的student库的t_student表中插入数据：

mysql> select * from t_student;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | lisi  |
|  3 | jerry |
+----+-------+
3 rows in set (0.00 sec)

# 插入数据
mysql> insert t_student(name) values('haha'),('laipigou'),('bapi');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 查看数据
mysql> select * from t_student;
+----+----------+
| id | name     |
+----+----------+
|  1 | tom      |
|  2 | lisi     |
|  3 | jerry    |
|  4 | haha     |
|  5 | laipigou |
|  6 | bapi     |
+----+----------+
6 rows in set (0.00 sec)

在从数据库中查看数据是否同步：

# 进入数据库
mysql> use student;

# 查看多哪些表
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| t_student         |
+-------------------+
1 row in set (0.00 sec)


# 查看t_student数据
mysql> select * from t_student;
+----+----------+
| id | name     |
+----+----------+
|  1 | tom      |
|  2 | lisi     |
|  3 | jerry    |
|  4 | haha     |
|  5 | laipigou |
|  6 | bapi     |
+----+----------+
6 rows in set (0.00 sec)

在主库上删除、更改，建表

# 更改
mysql> update t_student set name = 'meimei' where id = 6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 删除
mysql> delete from t_student where id = 5 ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | tom    |
|  2 | lisi   |
|  3 | jerry  |
|  4 | haha   |
|  6 | meimei |
+----+--------+
5 rows in set (0.00 sec)

# 新建表
mysql> create table job(
    -> id int not null primary key auto_increment,
    -> name varchar(40) not null,
    -> sex char(8) not null
    -> )charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert job(name,sex) values('谭咏麟','男'),('张国荣','男'),('叶倩文','女');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from job;
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 | 谭咏麟    | 男  |
|  2 | 张国荣    | 男  |
|  3 | 叶倩文    | 女  |
+----+-----------+-----+
3 rows in set (0.00 sec)

从库查看是否同步数据

mysql> select * from job;
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 | 谭咏麟    | 男  |
|  2 | 张国荣    | 男  |
|  3 | 叶倩文    | 女  |
+----+-----------+-----+
3 rows in set (0.00 sec)

mysql> select * from t_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | tom    |
|  2 | lisi   |
|  3 | jerry  |
|  4 | haha   |
|  6 | meimei |
+----+--------+
5 rows in set (0.00 sec)