MySQL主从复制(一主一从)部署

Alone-林

发布于 2023-03-17 10:55:26

1K0

发布于 2023-03-17 10:55:26

MySQL主从复制

1、MySQL主从复制概述

MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一；但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。

1.1 MySQL主从复制原理

MySQL主从复制是一个异步的复制过程，主库发送更新事件到从库，从库读取更新记录，并执行更新记录，使得从库的内容与主库保持一致。

主从复制架构图：

Mysql的主从复制中主要有三个线程：master(binlog dump线程)、slave(I/O 线程、SQL 线程)，Master一条线程和Slave中的两条线程。

主从复制步骤：

①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。

②salve从库连接master主库。

③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。

④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。

⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

1.2 MySQL主从复制的好处

实时备份，提高数据安全性，同时用于故障切换，避免影响业务。
读写分离（mysql-proxy），将读请求交给从服务器，实现负载分担。
数据库性能提高，通过增加从数据库组建集群，提高总性能。

1.3 MySQL主从复制的形式

一主一从：用来做主从复制。
主主复制：当作备份使用，当主服务器出现故障时，另一个主服务器会自动顶上。
一主多从：用来实现读写分离，当写操作较少时，读操作较多时使用，主服务器用来实现写操作，从服务器用来实现读操作。
多主一从：用来实现读写分离，当写操作较多时，读操作较少时使用，主服务器用来实现写操作，从服务器用来实现读操作。
联级复制

2、主从复制配置

2.1主从复制配置步骤

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

需求：

搭建两台MySQL服务器，一台作为主服务器(master)，一台作为从服务器(salve)，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	系统版本	有无数据
master	192.168.111.135	centos8	有
salve	192.168.111.138	centos8	无

2.2 MySQL主从配置

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

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

#先查看主库有哪些库
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| teacher            |
+--------------------+

#再查看从库有哪些库
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+


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

#备份主库并将备份文件传送到从库
[root@localhost ~]# mysqldump -uroot -pPasswd123! --all-databases > /opt/all-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/
all-database-2022-07-30-16-45-11.sql  data
[root@localhost ~]# scp /opt/all-database-2022-07-30-16-45-11.sql root@192.168.111.138:/opt/
root@192.168.111.138's password: 
all-database-2022-07-30-16-45-11.sql                 100%  857KB 110.7MB/s   00:00

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



#在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@localhost ~]# mysql -uroot -pPasswd123! < /opt/all-database-2022-07-30-16-45-11.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| teacher            |
+--------------------+

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

mysql> create user 'zhao'@'192.168.111.138' identified by 'Passwd123!';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'zhao'@'192.168.111.138';
Query OK, 0 rows affected (0.00 sec)

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

2.2.3配置主数据库

#在主服务器上配置主从复制，开启二进制日志，设置服务id
[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
log_bin=mysql-bin			//开启二进制日志
server_id=1				//设置从库的唯一标识符，主库的server-id值必须小于从库的该值

#重启从库的mysql服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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@localhost ~]# mysql -uroot -pPasswd123! -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 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2.2.4配置从数据库

#在主服务器上配置主从复制，开启二进制日志，设置服务id
[root@localhost ~]# vim /etc/my.cnf
server-id=2     //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin       //启用中继日志relay-log

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

#配置并启动主从复制
mysql> change master to
    -> master_host='192.168.111.135',
    -> master_user='zhao',
    -> master_password='Passwd123!',
    -> 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> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.111.135
                  Master_User: zhao
                  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		
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

如果遇到如图所示问题

导致错误原因可能有：

网络不通
防火墙
账户密码错误
主服务器mysql权限

2.2.5测试验证

#在主服务器的teacher库的teacher表中插入数据
mysql> select * from teacher;
+----+----------+------+------+--------+
| id | name     | age  | sex  | height |
+----+----------+------+------+--------+
|  1 | zhangsan |   25 | 男   |    168 |
|  2 | lisi     |   22 | 男   |    180 |
|  3 | wangwu   |   48 | 男   |    175 |
|  4 | zhaosi   |   29 | 男   |    165 |
+----+----------+------+------+--------+
4 rows in set (0.00 sec)

mysql> insert teacher(id,name,age,sex,height) values (5,'tom',23,'女',168),(6,'sean',20,'男',189);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from teacher;
+----+----------+------+------+--------+
| id | name     | age  | sex  | height |
+----+----------+------+------+--------+
|  1 | zhangsan |   25 | 男   |    168 |
|  2 | lisi     |   22 | 男   |    180 |
|  3 | wangwu   |   48 | 男   |    175 |
|  4 | zhaosi   |   29 | 男   |    165 |
|  5 | tom      |   23 | 女   |    168 |
|  6 | sean     |   20 | 男   |    189 |
+----+----------+------+------+--------+
6 rows in set (0.00 sec)



#在从数据库中查看数据是否同步
mysql> use teacher;
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 * from teacher;
+----+----------+------+------+--------+
| id | name     | age  | sex  | height |
+----+----------+------+------+--------+
|  1 | zhangsan |   25 | 男   |    168 |
|  2 | lisi     |   22 | 男   |    180 |
|  3 | wangwu   |   48 | 男   |    175 |
|  4 | zhaosi   |   29 | 男   |    165 |
|  5 | tom      |   23 | 女   |    168 |
|  6 | sean     |   20 | 男   |    189 |
+----+----------+------+------+--------+
6 rows in set (0.00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-07-30，如有侵权请联系 cloudcommunity@tencent.com 删除

sql