MySQL多实例、备份恢复、主从复制

最新推荐文章于 2023-05-30 09:46:06 发布

WanJiaBaoBao

最新推荐文章于 2023-05-30 09:46:06 发布

阅读量716

点赞数

分类专栏：开源服务文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/WanJiaBaoBao/article/details/127074526

版权

开源服务专栏收录该内容

28 篇文章 1 订阅

订阅专栏

文章目录

MySQL 多实例
MySQL备份和恢复
MySQL主从复制

MySQL 多实例

多实例介绍

数据库多实例：MySQL多实例就是在一台服务器上同时开启多个不同的服务端口，同时运行多个MySQL服务进程，这些服务进程通过不同的Socket监听不同的服务端口来提供服务。
多实例好处：可有效利用服务器资源。当单个服务器资源有剩余时，可以充分利用剩余资源提供更多的服务，且可以实现资源的逻辑隔离，节约服务器资源。
多实例弊端：存在资源互相抢占的问题。当某个数据库实例并发很高或者SQL查询慢时，会消耗大量的CPU、磁盘IO等资源，从而导致服务器其他数据库实例提供服务的质量下降。

MySQL多实例常见的配置方案

单一的配置文件、单一启动程序多实例部署方式。
- 耦合度太高，一个配置文件不好管理。
多配置文件、多启动程序部署方式。
- 针对每个实例都有独立的配置文件和目录，管理灵活，耦合度较低。

案例：多配置文件和多启动程序的MariaDB多实例

环境要求

一台服务器，操作系统为Rocky 8.6，关闭防火墙、selinux，同步时间。

实现步骤

安装MariaDB

[root@rpm-mariadb ~]# yum -y install mariadb-server

[root@localhost ~]# mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@localhost ~]# tree -d /mysql
/mysql
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

生成数据库文件

## MariaDB 多实例，数据文件生成
[root@rpm-mariadb ~]# mysql_install_db --user=mysql --datadir=/mysql/3306/data
Installing MariaDB/MySQL system tables in '/mysql/3306/data' ...
OK
[root@rpm-mariadb ~]# mysql_install_db --user=mysql --datadir=/mysql/3307/data
Installing MariaDB/MySQL system tables in '/mysql/3307/data' ...
OK
[root@rpm-mariadb ~]# mysql_install_db --user=mysql --datadir=/mysql/3308/data
Installing MariaDB/MySQL system tables in '/mysql/3308/data' ...
OK

准备配置文件

[root@rpm-mariadb ~]# vim /mysql/3306/etc/my.cnf
[root@rpm-mariadb ~]# cat /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid

## 拷贝配置文件
[root@rpm-mariadb ~]# sed 's/3306/3307/' /mysql/3306/etc/my.cnf  > /mysql/3307/etc/my.cnf
[root@rpm-mariadb ~]# sed 's/3306/3308/' /mysql/3306/etc/my.cnf  > /mysql/3308/etc/my.cnf

准备启动脚本

[root@rpm-mariadb ~]# vim /mysql/3306/bin/mysqld
[root@rpm-mariadb ~]# cat /mysql/3306/bin/mysqld
#!/bin/bash

port=3306     ## 数据库端口
mysql_user="root"     ## 数据库账号
mysql_pwd="123456"    ## 数据库密码
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf & 
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

重复创建3307、3308启动脚本，并赋予执行权限。

[root@rpm-mariadb ~]# chmod +x /mysql/3306/bin/mysqld
[root@rpm-mariadb ~]# cp -p /mysql/3306/bin/mysqld /mysql/3307/bin/mysqld
[root@rpm-mariadb ~]# cp -p /mysql/3306/bin/mysqld /mysql/3308/bin/mysqld

[root@rpm-mariadb ~]# sed -i 's/3306/3307/' /mysql/3307/bin/mysqld
[root@rpm-mariadb ~]# sed -i 's/3306/3308/' /mysql/3308/bin/mysqld

启动服务

## MariaDB
[root@rpm-mariadb ~]# chown -R mysql.mysql /mysql
[root@rpm-mariadb ~]# /mysql/3306/bin/mysqld start
[root@rpm-mariadb ~]# /mysql/3307/bin/mysqld start
[root@rpm-mariadb ~]# /mysql/3308/bin/mysqld start
[root@rpm-mariadb ~]# ss -ntlp
State     Recv-Q    Send-Q       Local Address:Port         Peer Address:Port    Process                                                                                                                          
LISTEN    0         80                       *:3306                    *:*        users:(("mysqld",pid=5150,fd=23))                                               
LISTEN    0         80                       *:3307                    *:*        users:(("mysqld",pid=5282,fd=23))                                               
LISTEN    0         80                       *:3308                    *:*        users:(("mysqld",pid=5413,fd=23)) 

## MySQL
[root@rpm-mysql ~]# mysqld --defaults-file=/mysql/3306/etc/my.cnf --user=mysql -D

登录实例

[root@rpm-mariadb ~]# mysql -h 127.0.0.1 -P3306
MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.3.35-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		8
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.3.35-MariaDB MariaDB Server
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			2 min 36 sec

停止数据库，需要先修改数据库密码

[root@rpm-mariadb ~]# mysqladmin -h 127.0.0.1 -P 3308 password 123456
或者：
[root@rpm-mariadb ~]# mysqladmin -u root -S /mysql/3307/socket/mysql.sock  password 123456

[root@rpm-mariadb ~]# /mysql/3308/bin/mysqld stop

案例：多配置文件和多启动程序的MySQL 8.0.26多实例

环境要求

一台服务器，操作系统为Rocky 8.6，关闭防火墙、selinux，同步时间。

实现步骤

安装MySQL 8.0.26

[root@rpm-mysql ~]# yum -y install mysql-common-8.0.26 mysql-server-8.0.26 mysql-8.0.26

[root@rpm-mysql ~]# mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@rpm-mysql ~]# tree -d /mysql
/mysql
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

生成数据库文件

[root@rpm-mysql ~]# mysqld --initialize-insecure --user=mysql --datadir=/mysql/3306/data
[root@rpm-mysql ~]# mysqld --initialize-insecure --user=mysql --datadir=/mysql/3307/data
[root@rpm-mysql ~]# mysqld --initialize-insecure --user=mysql --datadir=/mysql/3308/data

准备配置文件

[root@rpm-mysql ~]# vim /mysql/3306/etc/my.cnf
[root@rpm-mysql ~]# cat /mysql/3306/etc/my.cnf
[mysqld]
port=3306
mysqlx-port=33060
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid

## 拷贝配置文件
[root@rpm-mysql ~]# sed 's/3306/3307/' /mysql/3306/etc/my.cnf  > /mysql/3307/etc/my.cnf
[root@rpm-mysql ~]# sed 's/3306/3308/' /mysql/3306/etc/my.cnf  > /mysql/3308/etc/my.cnf

启动服务

[root@rpm-mysql ~]# chown -R mysql.mysql /mysql
[root@rpm-mysql ~]# mysqld --defaults-file=/mysql/3306/etc/my.cnf --user=mysql -D
[root@rpm-mysql ~]# mysqld --defaults-file=/mysql/3307/etc/my.cnf --user=mysql -D
[root@rpm-mysql ~]# mysqld --defaults-file=/mysql/3308/etc/my.cnf --user=mysql -D

端口查看

[root@rpm-mysql ~]# ss -ntlp
State     Recv-Q    Send-Q       Local Address:Port         Peer Address:Port    Process                                                                                                                          
LISTEN    0         128                      *:3306                    *:*        users:(("mysqld",pid=7845,fd=28))                                               
LISTEN    0         128                      *:3307                    *:*        users:(("mysqld",pid=8008,fd=27))                                               
LISTEN    0         128                      *:3308                    *:*        users:(("mysqld",pid=8060,fd=27))                                               
LISTEN    0         70                       *:33070                   *:*        users:(("mysqld",pid=8008,fd=25))                                                                                               
LISTEN    0         70                       *:33080                   *:*        users:(("mysqld",pid=8060,fd=25))                                               
LISTEN    0         70                       *:33060                   *:*        users:(("mysqld",pid=7845,fd=25))

连接mysql

[root@rpm-mysql ~]# mysql -S /mysql/3307/socket/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> status
--------------
mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution)

Connection id:		8
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.26 Source distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/mysql/3307/socket/mysql.sock
Binary data as:		Hexadecimal
Uptime:			2 min 5 sec

Threads: 2  Questions: 5  Slow queries: 0  Opens: 120  Flush tables: 3  Open tables: 36  Queries per second avg: 0.040
--------------

服务停止，待研究。

## 需要研究这两个脚本
[root@rpm-mysql ~]# vim /usr/lib/systemd/system/mysqld.service
[root@rpm-mysql ~]# vim /usr/libexec/mysql-scripts-common 
[root@rpm-mysql ~]# vim /usr/libexec/mysql-wait-stop

MySQL备份和恢复

备份类型

完全备份，部分备份
- 完全备份：整个数据集。
- 部分备份：只备份数据子集，如分库或表。
完全备份、增量备份、差异备份：增量备份和差异备份的基础、前提是完全备份。
- 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据块，备份较快，还原复杂。
- 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单。
冷、温、热备份
- 冷备：读、写操作均不可进行，数据库停止服务。
- 温备：读操作可执行；写操作不可执行。
- 热备：读、写操作均可执行。
- 注：MyISAM支持温备、不支持热备；InnoDB都支持。
物理备份和逻辑备份
- 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快。
- 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度。

备份内容

数据；
二进制日志、InnoDB的事务日志；
用户账号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）；
服务器的配置文件。

备份注意要点

能容忍最多丢失多少数据；
备份产生的负载；
备份过程的时长；
温备的持锁多久；
恢复数据需要在多长时间内完成；
需要备份和恢复哪些数据。

还原要点

做还原测试，用于测试备份的可用性。
做还原演练，熟练恢复流程，并写成规范的技术文档。

备份工具

‘cp’、‘tar’ 等复制归档工具：物理备份工具，适用于所有存储引擎，仅支持冷备，可以完全或部分备份。
LVM的快照：借助文件系统工具进行备份；先加读锁，做快照后解锁，几乎热备。
mysqldump：逻辑备份工具，适用所有存储引擎。对MyISAM存储引擎进行温备，支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog日志实现增量备份。
xtrabackup：由Percona提供支持，对InnoDB做热备份（物理备份） 的工具，支持完全备份、增量备份。
MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现。
mysqlbackup：热备份，MySQL企业版组件。
mysqlhotcopy：perl语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES 和 ‘cp’ 或 ‘scp’ 来快速备份数据库。

拷贝备份

案例：数据库冷备份和还原

MySQL 8.0.26进行冷备份

## 查看当前数据库和表
mysql> select database();
+------------+
| database() |
+------------+
| hellodb    |
+------------+
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

停止数据库进行冷备份

[root@rpm-mysql ~]# systemctl stop mysqld
## 进行数据目录备份
### 备份数据最好放置在其他主机上，可使用rsync进行远程备份。
### 如果配置及二进制文件有做设置，也需要进行备份
[root@rpm-mysql ~]# cp -a /var/lib/mysql .
[root@rpm-mysql ~]# cp -a /etc/my.cnf /etc/my.cnf.d/* .

还原备份

## 还原配置文件
[root@rpm-mysql ~]# cp -p my.cnf /etc
## 还原数据目录
[root@rpm-mysql ~]# cp -a mysql/* /var/lib/mysql/

启动数据库进行数据库、表和数据的查看

[root@rpm-mysql ~]# systemctl start mysqld
[root@rpm-mysql ~]# ss -ntlp
State     Recv-Q    Send-Q       Local Address:Port         Peer Address:Port    Process                                                                                                                           
LISTEN    0         128                      *:3306                    *:*        users:(("mysqld",pid=10714,fd=35)) 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.02 sec)

mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)

mysqldump备份工具

说明

逻辑备份工具：mysqldump、mydumper、phpMyAdmin。
- Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件。
- mysqldump 是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份。
mysqldump命令格式：

## 支持指定数据库和指定多表的备份，但数据库本身的定义不备份。
mysqldump [OPTIONS] database [tables]

## 支持指定数据库备份，包含数据库本身的定义也会备份。
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

## 备份所有数据库，包含数据库本身的定义也会备份。
mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump常见选项：

-u,–user
-p

## 备份所有的数据库，包含’create database’ 语句的定义。
-A,–all-databases

## 指定备份的数据库，包含’create database’ 语句的定义。
-B,–databases

## 备份相关的所有 ‘event scheduler’。
-E,–events

## 备份所有存储过程和自定义函数。
-R,–routines

## 备份表相关触发器，默认启用，用’–skip-triggers’，不备份触发器。
–triggers

## 指定字符集，默认utf8mb4
–default-character-set=utf8

## ‘1’：表示所备份的数据之前加一条记录为’CHANGE MASTER TO’ 语句，非注释，默认为 ‘1’，适合于主从复制多机使用，此选项须启用二进制日志。
## ‘2’：表示所备份的数据记录’CHANGE MASTER TO’语句，并注释其语句，适合于单机使用，用于备份还原，此选项会自动关闭’-l,–lock-tables’，自动开启’-x,–lock-all-tables’ 功能（除非开启’–single-transaction’）。
### 注意：MySQL 8.0.26版本以后，此选项变为 ‘–source-data’。
–master-data=[num]

## 备份前滚动日志，锁定表完成后，执行flush logs命令，生成新的二进制日志文件。
### 注意：配置 -A 或者 -B 选项时，会导致刷新多次数据库；建议在同一时刻执行转储和日志刷新，可通过和 ‘–single-transaction’ 或 ‘-x’ ，‘–master-data’ 一起使用实现，此时只刷新一次二进制日志。
-F,–flush-logs

## 去掉注释，适合调试，节约备份占用的空间。
–compact

生产环境备份策略

InnoDB建议备份策略

[root@rpm-mysql mysql]# mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob

MyISAM建议备份策略

[root@rpm-mysql mysql]# mysqldump -uroot -p -A -F -E -R -x --triggers --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob

案例：完全备份之分库备份、压缩并恢复

备份并压缩

[root@rpm-mysql mysql]# mysql -u root -p123456 -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+

[root@rpm-mysql mysql]# for db in \
`mysql -uroot -p123456 -e 'show databases' | grep -Ev '^(Database|information_schema|performance_schema|sys)$'`;\
do mysqldump -uroot -p123456 -B $db | gzip > /backup/$db-$(date +'%F_%H-%M').sql.gz;done

## 其他写法
[root@rpm-mysql mysql]# mysql -uroot -p123456 -e "show databases" | \
grep -Ev '^(Database|information_schema|performance_schema|sys)$' | \
sed -rn 's#(.*)#mysqldump -uroot -p123456 -B \1 | \
gzip > /backup/\1_$(date +'%F_%H-%M')#p'

mysqldump -uroot -p123456 -B hellodb | gzip > /backup/hellodb_$(date +%F_%H-%M)
mysqldump -uroot -p123456 -B mysql | gzip > /backup/mysql_$(date +%F_%H-%M)
mysqldump -uroot -p123456 -B test1 | gzip > /backup/test1_$(date +%F_%H-%M)

[root@rpm-mysql mysql]# mysql -uroot -p123456 -e "show databases" | \
grep -Ev '^(Database|information_schema|performance_schema|sys)$' | \
sed -rn 's#(.*)#mysqldump -uroot -p123456 -B \1 | \
gzip > /backup/\1_$(date +'%F_%H-%M')#p' | bash

数据库恢复

## 解压备份的数据
[root@rpm-mysql backup]# gunzip hellodb-2022-09-28_16-33.sql.gz

## 登录数据库，删除数据库来模拟 'hellodb'损坏。
mysql> drop database hellodb;
Query OK, 7 rows affected (0.65 sec)

## 临时关闭binlog日志记录，前提是binlog日志已经为开启状态。
mysql> set sql_log_bin=off;

## 恢复数据库'hellodb'
mysql> source /backup/hellodb-2022-09-28_16-33.sql
## 查看恢复情况

## 开启binlog日志记录
mysql> set sql_log_bin=on

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

案例：恢复误删除的表（利用完全备份和二进制日志恢复）

模拟说明：在全备之后，后续的操作过程中失误删除了数据库，这个错误并没有立即发现问题。
首先确保’log_bin’日志开启。

## 两个都需要为ON，'sql_log_bin'支持动态修改，'log_bin'不支持动态修改。
mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)

## 若没有开启，可以通过配置文件开启
### 指定文件保存路径并指定文件前缀。
[root@rpm-mysql backup]# cat /etc/my.cnf
[mysqld]
log_bin=/data/mysqlbinlog

进行全量备份

## 目前的log_bin文件信息如下
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   1213552 | No        |
| binlog.000002 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

## 进行全量备份
[root@rpm-mysql backup]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +'%F_%T'`.sql

## 在次查看log_bin文件信息
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   1213552 | No        |
| binlog.000002 |       200 | No        |
| binlog.000003 |       156 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

## 全量备份的文件中使用'--master-data=2' 记录了当前全量备份的结束点位置。
......(省略)
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=156;
......（省略）

继续在表中新增记录

## 新增两条记录，模拟数据产生
mysql> insert into hellodb.students(name,age,gender) values('rose',20,'f'),('jack',22,'M');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

模拟误操作，删除了’students’ 表。

mysql> drop table hellodb.students;
Query OK, 0 rows affected (0.03 sec)

误操作之后，还未发现错误，其他表也有新数据产生，并且’log_bin’日志被刷新。

mysql> insert into hellodb.teachers(name,age,gender) values('lisi',30,'M'),('wangwwu',80,'M');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into hellodb.teachers(name,age,gender) values('hehe',40,'M');
Query OK, 1 row affected (0.02 sec)

发现错误，进行数据还原

## 注意：在还原时，需要拒绝用户访问数据库，避免数据再次写入，可通过防火墙进行拒绝连接

## 在恢复时，先在测试数据库中进行恢复，避免直接操作生产数据库。
### 从完全备份中，找到二进制位置
[root@rpm-mysql backup]# grep -i -- '-- CHANGE MASTER' /backup/allbackup_2022-09-28_18\:54\:36.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=156;

### 使用mysqlbinlog工具查看二进制日志，备份从完全备份后所有的二进制日志。
### 注意：二进制日志记录位置需要查看配置文件。
[root@rpm-mysql backup]# ls -l /var/lib/mysql/binlog.*
-rw-r----- 1 mysql mysql 1213552 Sep 28 18:42 /var/lib/mysql/binlog.000001
-rw-r----- 1 mysql mysql     200 Sep 28 18:54 /var/lib/mysql/binlog.000002
-rw-r----- 1 mysql mysql    1035 Sep 28 19:09 /var/lib/mysql/binlog.000003
-rw-r----- 1 mysql mysql     450 Sep 28 19:10 /var/lib/mysql/binlog.000004
-rw-r----- 1 mysql mysql      64 Sep 28 19:09 /var/lib/mysql/binlog.index

[root@rpm-mysql backup]# mysqlbinlog --start-position=156 /var/lib/mysql/binlog.000003 > /backup/inc.sql
[root@rpm-mysql backup]# mysqlbinlog /var/lib/mysql/binlog.000004 >> /backup/inc.sql

合并所有全备之后产生的’log_bin’ 文件后，找到误操作的语句，将语句修改正确。

## 注意：尽量避免直接打开文件取修改，以免文件过大，导致崩溃
[root@rpm-mysql backup]# sed -n '/^DROP TABLE/p' /backup/inc.sql 
DROP TABLE `hellodb`.`students` /* generated by server */
[root@rpm-mysql backup]# sed -i.bak '/^DROP TABLE/d' /backup/inc.sql 
[root@rpm-mysql backup]# ls
allbackup_2022-09-28_18:54:36.sql  inc.sql  inc.sql.bak

利用完全备份和修改过的二进制日志进行还原。

## 注意，尽量在测试环境中先还原。
## 先查看'log_bin' 和 'sql_log_bin' 是否开启，若开启，则需要临时关闭'sql_log_bin'，不让二进制文件进行记录。
[root@rpm-mysql mysql]# mysql -S /mysql/3307/socket/mysql.sock
mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

## 开始进行完全备份恢复和二进制日志恢复还原
mysql> source /backup/allbackup_2022-09-28_18:54:36.sql
mysql> source /backup/inc.sql

恢复完成后进行数据验证。


mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

mysql> select * from hellodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | rose          |  20 | F      |    NULL |      NULL |
|    27 | jack          |  22 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | lisi          |  30 | M      |
|   6 | wangwu        |  80 | M      |
|   7 | hehe          |  40 | M      |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)

将临时修改的’sql_log_bin’ 还原

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.01 sec)

xtrabackup备份工具

percona提供的mysql数据库备份工具，唯一开源的能够对InnoDB 和 xtradb数据库进行热备份的工具。

注意：

xreabackup 是用来备份 InnoDB表的，不能备份非InnoDB表，和MySQL Server没有交互；
Innobackupex 脚本用来备份非InnoDB表，同时会调用xtrabackup命令来备份InnoDB表，还会和MySQL Server发送命令进行交互。

xtrabackup安装

安装包在epel源中，centos 8 没有提供。

[root@k8s-master01 ~]# yum info percona-xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
Available Packages
Name        : percona-xtrabackup
Arch        : x86_64
Version     : 2.3.6
Release     : 1.el7
Size        : 4.6 M
Repo        : epel/x86_64
Summary     : Online backup for InnoDB/XtraDB in MySQL, Percona Server and MariaDB
URL         : http://www.percona.com/software/percona-xtrabackup/
License     : GPLv2
Description : Online backup for InnoDB/XtraDB in MySQL, MariaDB and Percona Server.

官方下载最新版本。下载地址

[root@k8s-master01 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.29-22/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.29-22.1.el8.x86_64.rpm
[root@rpm-mysql ~]# yum -y install percona-xtrabackup-80-8.0.29-22.1.el8.x86_64.rpm

xtrabackup用法

xtrabackup工具备份和还原，需要三步实现：
- 备份：对数据库做完全或增量备份；
- 预准备：还原前，先对备份的数据整理至一个临时目录；
- 还原：将整理好的数据，复制回数据库目录中。

命令用法：[xtrabackup [--defaults-file=#] --backup | xtrabackup [--defaults-file=#] --prepare] [OPTIONS]

选项：
--databases		##指定数据库名，多个库名用空格' '隔开，如"db1 db2"；也可以指定其中的某张表，如"db1.tb1"。该选项对InnoDB引擎于晓，还是会备份所有innodb表。
--defaults-file		##指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置。
--incremental		##表示创建一个增量备份，需要指定'--incremental-basedir'
--incremental-basedir	##指定为前一次全备份或增量备份的目录，与'--incremental'同时使用。
--incremental-dir	##表示还原时增量备份的目录。
--include=name		##指定表名，格式：databasename.tablename。

案例：利用xtrabackup实现完全备份及还原

xtrabackup 版本为：

xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)

在原主机做完全备份至’/backup’目录

## 注意：'/backup'目录必须先存在，子目录'base' 会自动创建。
[root@rpm-mysql ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base

## 可以将备份完成的数据复制至远程主机
[root@rpm-mysql ~]# scp -r /backup/ 目标主机:/

在目标主机上还原

## 预准备：确保数据一致，提交完成的事务，回滚未完成的事务。
[root@rpm-mysql ~]# xtrabackup --prepare --target-dir=/backup/base

## 复制到数据目录
### 注意：数据目录必须为空，MySQL服务不能启动。
[root@rpm-mysql ~]# xtrabackup --copy-back --target-dir=/backup/base --datadir=/mysql/3307/data

## 还原属性
[root@rpm-mysql ~]# chown -R mysql.mysql /mysql/3307/data

启动恢复完成的MySQL服务（恢复到了同版本的MySQL多实例数据库上）

[root@rpm-mysql ~]# mysqld --defaults-file=/mysql/3307/etc/my.cnf -D --user=mysql
[root@rpm-mysql ~]# ss -ntlp
State     Recv-Q    Send-Q       Local Address:Port         Peer Address:Port    Process                                                                                                                       
LISTEN    0         128                      *:3307                    *:*        users:(("mysqld",pid=12953,fd=33))

查看数据库、表和数据

[root@rpm-mysql ~]# mysql -uroot -p123456 -S /mysql/3307/socket/mysql.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.01 sec)

mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| toc               |
+-------------------+
6 rows in set (0.01 sec)

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | lisi          |  30 | M      |
|   6 | wangwu        |  80 | M      |
|   7 | hehe          |  40 | M      |
+-----+---------------+-----+--------+
7 rows in set (0.01 sec)

案例：利用xtrabackup实现完全备份、增量备份及还原

xtrabackup 版本为：

xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)

先进行完全备份

[root@rpm-mysql ~]# mkdir -p /backup
## 注意：默认备份的MySQL数据家目录'/var/lib/mysql'。
[root@rpm-mysql ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base

备份完成后进行第一次数据修改，并做第一次增量备份。

[root@rpm-mysql ~]# mysql -u root -p123456
mysql> insert into hellodb.teachers values(null,'increment_1',100,'M');
Query OK, 1 row affected (0.02 sec)

## 进行第一次增量。
[root@rpm-mysql ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

第二次数据修改，并做第二次增量备份。

[root@rpm-mysql ~]# mysql -u root -p123456
mysql> insert into hellodb.teachers values(null,'increment_2',10,'F');
Query OK, 1 row affected (0.02 sec)

## 进行第二次增量
[root@rpm-mysql ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

还原过程

## 注意：可以将备份的数据远程拷贝到远程主机，备份上面生成的三个文件。
## 预准备完成备份，'--apply-log-only'，阻止回滚未完成的事务。
[root@rpm-mysql ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base

## 合并第一次增量备份到完全备份。
[root@rpm-mysql ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

## 合并第二次增量备份到完全备份，最后一次还原不需要加选项'--apply-log-only'，需要回滚未完成的事务。
[root@rpm-mysql ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

## 复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动。
[root@rpm-mysql ~]# xtrabackup --copy-back --target-dir=/backup/base/ --datadir=/mysql/3307/data

还原属性并启动服务

[root@rpm-mysql ~]# chown -R mysql.mysql /mysql/3307/data
[root@rpm-mysql ~]# mysqld --defaults-file=/mysql/3307/etc/my.cnf -D --user=mysql

连接数据库查询数据

[root@rpm-mysql ~]# mysql -S /mysql/3307/socket/mysql.sock -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.01 sec)

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | lisi          |  30 | M      |
|   6 | wangwu        |  80 | M      |
|   7 | hehe          |  40 | M      |
|   8 | increment_1   | 100 | M      |
|   9 | increment_2   |  10 | F      |
+-----+---------------+-----+--------+
9 rows in set (0.01 sec)

MySQL主从复制

复制的作用

读写分离。每个节点都有想听的数据集，向外扩展，基于二进制日志的单向复制。
负载均衡读操作
备份
高可用和故障切换
数据分布
MySQL升级

复制架构

一主一从复制架构
一主多从复制架构
级联复制（主->从-->从）
双主（会出现冲突，比如：比如主键冲突）
一从多主：适用于多个不同数据库
环状复制

复制需要考虑二进制日志时间记录格式

语句型’STATEMENT’（5.0之前），MariaDB 5.5默认使用此格式，不推荐使用；
行型’ROW’（5.1之后），MySQL 8.0默认使用此格式，推荐；
混合型’MIXED’：MariaDB 10.3默认使用此格式，不推荐。

实现主从复制相关配置

主节点配置文件相关设置

[mysqld]
server_id=<NUM>   ##MySQL8.0 默认值1；\
				  ##MariaDB >=10.2.2，默认为1，取值范围：1-4294967295，\
				  ##MariaDB <=10.2.1，默认为0，取值范围0-4294967295，如果从节点为0，所有master都将拒绝此slave的连接。
log_bin=[path]    ##指定二进制日志存放路径，并指定二进制日志文件名前缀。

从节点配置文件相关设置

server_id=<NUM>
log_bin=[path]
read_only=ON		##设置数据库只读，针对管理员账号无效（root）。
relay_log=<path>	##relay log（中继日志）的文件路径，默认值`hostname`-relay-bin
relay_log_index=relay-log.index		##默认值`hostname`-relay-bin.index

主从相关信息说明

主节点信息

## 查看二进制日志记录（bin log日志）是否开启
### ‘log_bin’需要开启，不能够动态修改；‘sql_log_bin’也需要开启，能够动态修改。需要两者都开启时二进制日志记录才真正开启。
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------+
| Variable_name                   | Value                 |
+---------------------------------+-----------------------+
| log_bin                         | ON                    |
| log_bin_basename                | /data/mysql-bin       |
| log_bin_index                   | /data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                   |
| log_bin_use_v1_row_events       | OFF                   |
| sql_log_bin                     | ON                    |
+---------------------------------+-----------------------+
6 rows in set (0.01 sec)


### Id 为 16、17表示主节点有两个从节点连接（有两个Dump线程）
mysql> show processlist;
+----+-----------------+-------------------+---------+-------------+--------+-----------------------------------------------------------------+------------------+
| Id | User            | Host              | db      | Command     | Time   | State                                                           | Info             |
+----+-----------------+-------------------+---------+-------------+--------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost         | NULL    | Daemon      | 765791 | Waiting on empty queue                                          | NULL             |
| 15 | root            | localhost         | hellodb | Query       |      0 | init                                                            | show processlist |
| 16 | repluser        | 172.25.3.82:59774 | NULL    | Binlog Dump |    120 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 17 | repluser        | 172.25.3.80:41974 | NULL    | Binlog Dump |     68 | Source has sent all binlog to replica; waiting for more updates | NULL             |
+----+-----------------+-------------------+---------+-------------+--------+-----------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

### 查看master二进制日志信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1051 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从节点信息

## 从节点确保只读开启。
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.01 sec)

## bin log日志可以开启
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

## 复制线程是否启动。
### Id为5表示启动了IO线程；Id为6表示启动了SQL线程
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                                                    | Info             |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
|  5 | system user     | connecting host | NULL | Connect |  500 | Waiting for source to send event                         | NULL             |
|  6 | system user     |                 | NULL | Query   |  500 | Replica has read all relay log; waiting for more updates | NULL             |
|  7 | event_scheduler | localhost       | NULL | Daemon  |  500 | Waiting on empty queue                                   | NULL             |
| 10 | root            | localhost       | NULL | Query   |    0 | init                                                     | show processlist |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

## 从节点复制信息（部分）
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.25.3.84   ##复制哪个主节点的数据
                  Master_User: repluser    ##复制账号
                  Master_Port: 3306    ##连接的端口哦号
                Connect_Retry: 60     ##连接重试时间
              Master_Log_File: mysql-bin.000002    ##从主节点哪个二进制日志开始复制
          Read_Master_Log_Pos: 1051    ##读取主节点二进制日志文件的起始点
               Relay_Log_File: mysql-slave-relay-bin.000005   ##从节点中继日志（relay log）
                Relay_Log_Pos: 324    ##中继日志位置
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes     ##IO线程是否开启
            Slave_SQL_Running: Yes     ##SQL线程是否开启
                   Last_Errno: 0      ## 复制错误的ID号
                   Last_Error:      ##复制错误的信息
                 Skip_Counter: 0     ##跳过错误计数
        Seconds_Behind_Master: 0     ## 复制的延时差

案例：实现主从复制架构

主节点

[root@rpm-mysql ~]# mkdir -p /data
[root@rpm-mysql ~]# chown -R mysql.mysql /data
[root@rpm-mysql ~]# vim /etc/my.cnf
[mysqld]
server_id=84
log_bin=/data/mysql-bin

[root@rpm-mysql ~]# systemctl restart mysqld
[root@rpm-mysql ~]# ls -l /data
total 8
-rw-r----- 1 mysql mysql 156 Sep 29 14:44 mysql-bin.000001
-rw-r----- 1 mysql mysql  23 Sep 29 14:44 mysql-bin.index

创建复制用户并授权

mysql> create user 'repluser'@'172.25.3.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave on *.* to 'repluser'@'172.25.3.%';
Query OK, 0 rows affected (0.02 sec)

进行完全备份

[root@rpm-mysql ~]# mysqldump -uroot -p123456 -A -F --master-data=1 --single-transaction > all.sql

测试下全备之后继续写入数据，看看之后能否数据同步。

mysql> insert into hellodb.teachers(name,age,gender) values('zhangshan',5,'M');
Query OK, 1 row affected (0.01 sec)

将备份的数据复制到从节点

[root@rpm-mysql ~]# scp -p all.sql 172.25.3.82:/root

配置从节点

[root@mysql-slave ~]# cat /etc/my.cnf
[mysqld]
server-id=82
read-only
[root@mysql-slave ~]# systemctl restart mysqld

修改全量备份的数据文件，并将数据恢复至从节点上

## 修改全量备份文件，找到'CHANGE MASTER TO'信息，修改并设置为如下。
[root@mysql-slave ~]# vim all.sql
CHANGE MASTER TO 
MASTER_HOST='172.25.3.84',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;

## 在从节点上恢复备份数据
mysql> set sql_log_bin=0;   ##临时关闭log-bin日志记录。
Query OK, 0 rows affected (0.00 sec)
mysql> source /root/all.sql;
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

查看从节点的slave 状态

## 目前主服务器信息正确，IO线程和SQL线程未开启。
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.25.3.84
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 156
               Relay_Log_File: mysql-slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 156
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

mysql> show processlist;
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
| Id | User            | Host      | db    | Command | Time | State                  | Info             |
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL  | Daemon  |  793 | Waiting on empty queue | NULL             |
| 10 | root            | localhost | test1 | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
2 rows in set (0.01 sec)

启动slave，并再次才看slave 状态和进程状态

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.25.3.84
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 455
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 623
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 455
              Relay_Log_Space: 838
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 84
                  Master_UUID: d2ef4694-3d66-11ed-b3db-52545b4ca9b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

mysql> show processlist;
+----+-----------------+-----------------+-------+---------+------+----------------------------------------------------------+------------------+
| Id | User            | Host            | db    | Command | Time | State                                                    | Info             |
+----+-----------------+-----------------+-------+---------+------+----------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL  | Daemon  |  844 | Waiting on empty queue                                   | NULL             |
| 10 | root            | localhost       | test1 | Query   |    0 | init                                                     | show processlist |
| 11 | system user     | connecting host | NULL  | Connect |   36 | Waiting for source to send event                         | NULL             |
| 12 | system user     |                 | NULL  | Query   | 9402 | Replica has read all relay log; waiting for more updates | NULL             |
+----+-----------------+-----------------+-------+---------+------+----------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

查看全备之后新增加的数据是否同步

## 同步成功
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | lisi          |  30 | M      |
|   6 | wangwu        |  80 | M      |
|   7 | hehe          |  40 | M      |
|   8 | increment_1   | 100 | M      |
|   9 | increment_2   |  10 | F      |
|  10 | zhangshan     |   5 | M      |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)

在主节点上插入数据查看是否能够同步成功

## 主节点插入数据
mysql> insert into hellodb.teachers(name,age,gender) values('wangwu',50,'F');
Query OK, 1 row affected (0.02 sec)

## 从节点查看数据
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | lisi          |  30 | M      |
|   6 | wangwu        |  80 | M      |
|   7 | hehe          |  40 | M      |
|   8 | increment_1   | 100 | M      |
|   9 | increment_2   |  10 | F      |
|  10 | zhangshan     |   5 | M      |
|  11 | wangwu        |  50 | F      |
+-----+---------------+-----+--------+
11 rows in set (0.00 sec)

案例：master服务器宕机，提升slave为新的master

目前架构为一主两从

mysql> show processlist;
+----+-----------------+-------------------+---------+-------------+--------+-----------------------------------------------------------------+------------------+
| Id | User            | Host              | db      | Command     | Time   | State                                                           | Info             |
+----+-----------------+-------------------+---------+-------------+--------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost         | NULL    | Daemon      | 765791 | Waiting on empty queue                                          | NULL             |
| 15 | root            | localhost         | hellodb | Query       |      0 | init                                                            | show processlist |
| 16 | repluser        | 172.25.3.82:59774 | NULL    | Binlog Dump |    120 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 17 | repluser        | 172.25.3.80:41974 | NULL    | Binlog Dump |     68 | Source has sent all binlog to replica; waiting for more updates | NULL             |
+----+-----------------+-------------------+---------+-------------+--------+-----------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

开始提升一个从节点为主节点。

## 如果有多个从节点，找到处于所有从节点中数据最新的从节点，让他成为新master。
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.25.3.84
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1051    ##查看读取日志的位置
               Relay_Log_File: mysql-slave-relay-bin.000005
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000002


## 新master修改配置文件，关闭read-only 配置
[root@mysql-slave ~]# vim /etc/my.cnf
[mysqld]
server-id=82
read-only=OFF

## 在新master上清除旧复制信息
mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.42 sec)

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.17 sec)

## 在新master上完全备份
[root@mysql-slave ~]# mysqldump -u root -p123456 -A --single-transaction --master-data=1 -F > new_master.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-slave ~]# systemctl restart mysqld
[root@mysql-slave ~]# scp new_master.sql root@172.25.3.80:/root
### 可以在分析旧的master的二进制日志，将未同步到新master的二进制日志导出，恢复到新master，尽可能恢复数据。
使用mysqlbinlog工具查找未及时复制的。

##其他所有slave重新还原数据库，指向新的master。
[root@slave02 ~]# vim new_master.sql
CHANGE MASTER TO
MASTER_HOST='172.25.3.82',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=156;

mysql> stop slave;
mysql> reset slave all;
mysql> set sql_log_bin=off;
mysql> source /root/new_master.sql;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.25.3.82
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 156
               Relay_Log_File: slave02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No

mysql> set sql_log_bin=on;
mysql> start slave;

WanJiaBaoBao

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL多实例、备份恢复、主从复制

数据库多实例：MySQL多实例就是在一台服务器上同时开启多个不同的服务端口，同时运行多个MySQL服务进程，这些服务进程通过不同的Socket监听不同的服务端口来提供服务。多实例好处：可有效利用服务器资源。当单个服务器资源有剩余时，可以充分利用剩余资源提供更多的服务，且可以实现资源的逻辑隔离，节约服务器资源。多实例弊端：存在资源互相抢占的问题。当某个数据库实例并发很高或者SQL查询慢时，会消耗大量的CPU、磁盘IO等资源，从而导致服务器其他数据库实例提供服务的质量下降。
复制链接

扫一扫