mysql主从

最新推荐文章于 2024-10-08 10:01:21 发布

摆烂cc

最新推荐文章于 2024-10-08 10:01:21 发布

阅读量41

点赞数

文章标签： mysql adb oracle

本文链接：https://blog.csdn.net/nmlg6665352/article/details/132749141

版权

mysql主从

主从简介

MySQL主从复制是一种常见的数据库复制技术，用于实现数据的冗余备份、读写分离和高可用性。它基于主节点（Master）将数据的更改操作同步到从节点（Slave）的机制。以下是MySQL主从复制的简介：

简介：

1.角色：

主节点（Master）：主节点是数据的源头，接收并处理所有写操作（INSERT、UPDATE、DELETE）。
从节点（Slave）：从节点复制主节点的数据变更，提供读取操作服务，并可以用作故障切换和数据备份。

2.复制过程：

主节点将数据更改操作记录在二进制日志（Binary Log）中，记录了数据库中发生的修改语句。
从节点连接到主节点，并请求复制主节点的二进制日志，将主节点的数据更改操作应用到自己的数据库中。
从节点通过读取主节点的二进制日志，按照相同的顺序执行其中的数据更改操作，实现与主节点的数据同步。

3.同步方式：

基于语句的复制（Statement-based
Replication）：主节点将数据更改操作记录为SQL语句，从节点执行相同的SQL语句来实现同步。
基于行的复制（Row-based
Replication）：主节点将数据更改操作记录为行级别的改变，从节点直接复制主节点上的数据行，以确保更高的精确性。
混合复制（Mixed Replication）：结合了基于语句的复制和基于行的复制，根据具体情况自动选择合适的方式进行复制。

4.读写分离：

从节点可以独立地处理读取操作，从而减轻主节点的负载。
应用程序可以将读取请求发送到从节点，提高数据库体的读取性能和响应速度。
5.高可用性和故障切换：

当主节点发生故障或不可用时，可以通过将从节点提升为新的主节点来实现快速的故障切换。
通过使用多个从节点，可以实现更高级别的冗余备份和系统的高可用性。
MySQL主从复制是一种可靠且常用的数据库复制技术，它提供了数据的冗余备份、读写分离和高可用性。通过配置主从复制，可以增加数据库的可扩展性、提高读取性能，并为故障恢复提供了解决方案。

2.主从复制配置：

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

需求：

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

环境说明：

数据库角色	ip	系统版本
主数据库	192.168.14.128	centos8/redhat8 mysql-5.7
从数据库	192.168.14.133	centos8/redhat8 mysql-5.7

2.1 mysql安装

2.2 mysql主从配置

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

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

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

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

[root@RHEL8 ~]# mysql -uroot -pRaol123.
//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> use rl;
Database changed
mysql> create table student(id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_rl |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into student(id,name,age) values(2,'jerry',23),(3,'rl',25),(4,'lisi',NULL);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show tables;
+-------------------+
| Tables_in_rl |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | jerry   |   23 |
|  3 | rl |   25 |
|  4 | lisi    | NULL |
+----+---------+------+
3 rows in set (0.00 sec)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> 
//此锁表的终端必须在备份完成以后才能退出

备份主库并将备份文件传送到从库

[root@RHEL8 ~]# mysqldump -uroot -pRaol123. --all-databases > /opt/all-20230907.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@RHEL8 ~]# ls /opt/
all-20230907.sql
[root@RHEL8 ~]# scp /opt/all-20230907.sql root@192.168.14.133:/opt/
The authenticity of host '192.168.14.133 (192.168.14.133)' can't be established.
ECDSA key fingerprint is SHA256:OgNuDfFJtB9mhg8ezO1FOIzsog9izo9/pUYxxS+HCjU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.14.133' (ECDSA) to the list of known hosts.
root@192.168.14.133's password: 
all-20230907.sql                 100%  869KB 121.8MB/s   00:00   

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

从库上面恢复主库

在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@node1 ~]# ls /opt/
all-20230907.sql  data
[root@node1 ~]# mysql -uroot -pRaol123. < /opt/all-20230907.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# mysql -uroot -pRaol123. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| rl            |
+--------------------+

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

mysql> create user 'repl'@'192.168.14.133' identified by 'Raol123.';
Query OK, 0 rows affected (0.01 sec)

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

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

2.2.3 配置主数据库

[root@RHEL8 ~]# vim /etc/my.cnf
//在[mysqld]这段的后面加上如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin   //启用binlog日志
server-id=10     //数据库服务器唯一标识符，主库的server-id值必须比从库的小

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启MySQL服务

[root@RHEL8 ~]# systemctl restart mysqld
[root@RHEL8 ~]# 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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.2.4 配置从数据库

[root@node1 ~]# vim /etc/my.cnf
//添加如下内容
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2     //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log=mysql-relay-bin       //启用中继日志relay-log

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

//重启从库的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:111        0.0.0.0:*            
LISTEN 0      32     192.168.122.1:53         0.0.0.0:*            
LISTEN 0      128          0.0.0.0:22         0.0.0.0:*            
LISTEN 0      5          127.0.0.1:631        0.0.0.0:*            
LISTEN 0      128        127.0.0.1:6010       0.0.0.0:*            
LISTEN 0      80                 *:3306             *:*            
LISTEN 0      128             [::]:111           [::]:*            
LISTEN 0      511                *:80               *:*            
LISTEN 0      128             [::]:22            [::]:*            
LISTEN 0      5              [::1]:631           [::]:*            
LISTEN 0      128            [::1]:6010          [::]:*

配置并启动主从复制

mysql> change master to
    -> master_host='192.168.198.130',
    -> master_user='repl',
    -> master_password='Zhouwei123!',
    -> 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.198.130
                  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
            Slave_SQL_Running: Yes

2.2.5 测试验证

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

mysql> use rl;
Database changed
mysql> create table home(id int not null,name varchar(100) not null,,age tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_rl |
+-------------------+
| home              |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> insert into home values (1,'xiaojing',18),(2,'zhouwei',23),(3,'dum',1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from home;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | xiaojing    |   18 |
|  2 | rl          |   23 |
|  3 | dum         |   1 |
+----+-------------+------+
3 rows in set (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| rl                 |
+--------------------+
5 rows in set (0.00 sec)

mysql> use rl;
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> show tables;
+-------------------+
| Tables_in_rl      |
+-------------------+
| home              |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from home;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | xiaojing    |   18 |
|  2 | zhouwei     |   23 |
|  3 | dum         |    1 |
+----+-------------+------+
3 rows in set (0.00 sec)
-----------------+
| home              |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from home;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | xiaojing    |   18 |
|  2 | zhouwei     |   23 |
|  3 | dum         |    1 |
+----+-------------+------+
3 rows in set (0.00 sec)