MySQL数据库备份三种方案：
热备：一般用于保证服务正常不间断运行，用两台机器作为服务机器，一台用于实际数据库操作应用,另外一台实时的从前者中获取数据以保持数据一致.如果当前的数据库当机了,备份的数据库立马取代当前的数据库继续提供服务。跟MySQL的主从里的一主一从一样，主数据库宕机之后，从数据库就会取代主数据库去维护业务不中断。
冷备：在数据库停止运行的时候进行备份，这种备份方式最为简单，只需要拷贝数据库里的数据。
温备：在数据库运行的时候进行备份的，但对当前数据库的操作会产生影响。
热备
在数据库运行时，直接对数据库进行备份，对正在运行的数据库没有影响。
[root@node1 ~]# ls
anaconda-ks.cfg
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| job |
| t_student |
+-------------------+
2 rows in set (0.00 sec)
# 备份正在运行的所有数据库
[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 ~]# mysqldump -uroot -ptkl9639@G --all-databases > all-$(date '+%Y-%m-%d:%H-%M-%S')
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# ls
all-2022-07-03:20-23-51 anaconda-ks.cfg
# 备份student库的job表
[root@node1 ~]# mysqldump -uroot -p student job > table-$(date '+%Y-%m-%d:%H-%M-%S')
Enter password:
[root@node1 ~]# ls
all-2022-07-03:20-23-51 anaconda-ks.cfg table-2022-07-03:20-26-52
# 模拟误操作，把student数据库误删了
[root@node1 ~]# mysql -uroot -ptkl9639@G -e 'drop database student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# mysql -uroot -ptkl9639@G -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 恢复整个数据库
[root@node1 ~]# mysql -uroot -ptkl9639@G < all-2022-07-03\:20-23-51
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# mysql -uroot -ptkl9639@G -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student 已恢复 |
| sys |
+--------------------+
# 查看里面的数据库
[root@node1 ~]# mysql -uroot -ptkl9639@G -e 'show tables from student'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_student |
+-------------------+
| job |
| t_student |
+-------------------+
[root@node1 ~]# mysql -uroot -ptkl9639@G -e 'select * from student.job'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 1 | 谭咏麟 | 男 |
| 2 | 张国荣 | 男 |
| 3 | 叶倩文 | 女 |
+----+-----------+-----+
冷备
在数据库停止运行的时候进行备份，这种备份方式最为简单，只需要拷贝数据库里的数据。
| 主机名 | 应用与系统版本 | 有无数据 | ip |
|---|
| node1 | centos8/redhat8 mysql-5.7 | 有数据 | 192.168.229.129 | | node2 | centos8/redhat8 mysql-5.7 | 无数据 | 192.168.229.130 |
备份要求：
将node01的MySQL数据库所有数据备份到node02的数据库服务器上，并能够确保备份数据在node02服务器上的可用性
# 查看node1 信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use student;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| job |
| t_student |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from job;
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 1 | 谭咏麟 | 男 |
| 2 | 张国荣 | 男 |
| 3 | 叶倩文 | 女 |
+----+-----------+-----+
3 rows in set (0.01 sec)
mysql> select * from t_student;
+----+--------+
| id | name |
+----+--------+
| 1 | tom |
| 2 | lisi |
| 3 | jerry |
| 4 | haha |
| 6 | meimei |
+----+--------+
5 rows in set (0.01 sec)
# 打包数据库备份文件
## 停止mysqld服务
[root@node1 ~]# systemctl stop mysqld
# 进入存放数据的目录进行打包
[root@node1 ~]# cd /opt/data/
[root@node1 data]# ls
auto.cnf client-cert.pem ibdata1 mysql node1.err public_key.pem student
ca-key.pem client-key.pem ib_logfile0 mysql-bin.000001 performance_schema server-cert.pem sys
ca.pem ib_buffer_pool ib_logfile1 mysql-bin.index private_key.pem server-key.pem
# 打包存放到/opt/下面
[root@node1 data]# tar zcf /opt/all-$(date '+%Y-%m-%d:%H-%M-%S').tar.gz *
[root@node1 data]# cd ..
[root@node1 opt]# ls
all-2022-07-03:20-56-35.tar.gz data
# 使用scp传到node2
[root@node1 ~]# scp /opt/all-2022-07-03:20-56-35.tar.gz root@192.168.229.130:/opt/
The authenticity of host '192.168.229.130 (192.168.229.130)' can't be established.
ECDSA key fingerprint is SHA256:mntQBTppC7e+5Uh8MyZHFW3FuKZzpoS46G0j2C+O8U4.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.229.130' (ECDSA) to the list of known hosts.
root@192.168.229.130's password:
all-2022-07-03:20-56-35.tar.gz 100% 1779KB 61.0MB/s 00:00
node2操作
# 查看
[root@node2 ~]# ls /opt/
all-2022-07-03:20-56-35.tar.gz data mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
# 停掉mysql服务
[root@node2 ~]# systemctl stop mysqld
# 进入存放数据目录data，删除原有数据
[root@node2 ~]# cd /opt/data/
[root@node2 data]# rm -rf *
#解压备份数据到存放目录date
[root@node2 ~]# tar xf /opt/all-2022-07-03\:20-56-35.tar.gz -C /opt/data/
[root@node2 ~]# cd /opt/data/
[root@node2 data]# ll
total 111520
-rw-r-----. 1 mysql mysql 56 Jul 3 15:11 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 3 15:11 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 3 15:11 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 3 15:11 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 3 15:11 client-key.pem
-rw-r-----. 1 mysql mysql 765 Jul 3 20:52 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 3 20:52 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 3 20:52 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 3 15:11 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 Jul 3 20:31 mysql
-rw-r-----. 1 mysql mysql 859587 Jul 3 20:52 mysql-bin.000001
-rw-r-----. 1 mysql mysql 19 Jul 3 16:41 mysql-bin.index
-rw-r-----. 1 mysql mysql 16069 Jul 3 20:52 node1.err
drwxr-x---. 2 mysql mysql 8192 Jul 3 15:11 performance_schema
-rw-------. 1 mysql mysql 1680 Jul 3 15:11 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 3 15:11 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 3 15:11 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 3 15:11 server-key.pem
drwxr-x---. 2 mysql mysql 92 Jul 3 20:31 student
drwxr-x---. 2 mysql mysql 8192 Jul 3 15:11 sys
# 启动mysql服务、并设置开机自启
[root@node2 ~]# systemctl enable --now mysqld
# 查看数据
[root@node2 ~]# mysql -uroot -ptkl9639@G -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
[root@node2 ~]# mysql -uroot -ptkl9639@G -e 'show tables from student'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| Tables_in_student |
+-------------------+
| job |
| t_student |
+-------------------+
[root@node2 ~]# mysql -uroot -ptkl9639@G -e 'select * from student.job'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 1 | 谭咏麟 | 男 |
| 2 | 张国荣 | 男 |
| 3 | 叶倩文 | 女 |
+----+-----------+-----+
|