mysql数据库备份与恢复

最新推荐文章于 2024-07-25 16:50:23 发布

牛肉酱汉堡

最新推荐文章于 2024-07-25 16:50:23 发布

阅读量1.2k

点赞数

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

本文链接：https://blog.csdn.net/m0_71618402/article/details/126091925

版权

###mysql数据库备份与恢复

冷备份：数据库服务关闭，把数据库对于的数据目录下的数据文件拷贝一份物理备份

[root@localhost ~]# mkdir /tmp/mysql-back
[root@localhost ~]# mv /opt/data/*  /tmp/mysql-back/
[root@localhost ~]# ls /opt/data/

[root@localhost ~]# mysql -uroot -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

[root@localhost ~]# ls /opt/data/
[root@localhost ~]# mv /tmp/mysql-back/*   /opt/data/
[root@localhost ~]# ls /opt//data/
auto.cnf         ib_buffer_pool  localhost.localdomain.err  public_key.pem
ca-key.pem       ibdata1         mysql                      server-cert.pem
ca.pem           ib_logfile0     mysql.pid                  server-key.pem
client-cert.pem  ib_logfile1     performance_schema         sys
client-key.pem   ibtmp1          private_key.pem            WWW

[root@localhost ~]# mysql -uroot -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use WWW;
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 tb_course
    -> ;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> show tables ;
+------------------+
| Tables_in_WWW    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# ls /opt/data/WWW/
db.opt  tb_course.frm  tb_course.ibd  tb_students_info.frm  tb_students_info.ibd
[root@localhost ~]#

温备份：？？
热备份：数据库服务正常运行的时候，直接对数据库备份
###mysql备份工具mysqldump
语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

备份 tb_course 文件

[root@localhost ~]# mysqldump -uroot -p111 WWW tb_course > tb_course.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份数据库

[root@localhost ~]# mysqldump  --databases  WWW > WWW.sql
[root@localhost ~]# ls
anaconda-ks.cfg  mysql57-community-release-el7-11.noarch.rpm  pass  tb_course.sql  WWW.sql

整个数据库全备份

[root@localhost ~]# mysqldump  --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220729012739.sql  anaconda-ks.cfg                              pass           WWW.sql
all-.sql                mysql57-community-release-el7-11.noarch.rpm  tb_course.sql
[root@localhost ~]# mysqldump  --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220729012739.sql  anaconda-ks.cfg                              tb_course.sql
all-20220729013313.sql  mysql57-community-release-el7-11.noarch.rpm  WWW.sql
all-.sql                pass

###演示
####删除表中数据然后恢复两种方式

第一种

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> delete from tb_course where id = 5 or id = 6;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
+----+-------------+
4 rows in set (0.00 sec)

[root@localhost ~]# ls
all-20220729012739.sql  anaconda-ks.cfg                              tb_course.sql
all-20220729013313.sql  mysql57-community-release-el7-11.noarch.rpm  WWW.sql
all-.sql                pass
[root@localhost ~]# mysql -uroot -p111  WWW < tb_course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show tables;
+------------------+
| Tables_in_WWW    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select *from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

第二种
source + 文件路径 +文件 //恢复文件

mysql> delete  from tb_course where id = 5 or id = 6;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
+----+-------------+
4 rows in set (0.00 sec)

mysql> source tb_course.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

删除数据库并恢复的两种方式
第一种

mysql> drop database WWW;
Query OK, 2 rows affected (0.00 sec)

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

mysql> source WWW.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

第二种

mysql> drop database WWW;
Query OK, 2 rows affected (0.01 sec)

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

[root@localhost ~]# mysql -uroot -p111 < WWW.sql;
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

全备的恢复
先删除多个数据库的两种方式（只可以动自己创建的数据库）
第一种

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.02 sec)
mysql> drop database WWW;
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql> drop database sys;
Query OK, 101 rows affected, 2 warnings (0.04 sec)

mysql> drop database  performance_schema;
Query OK, 87 rows affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> source all-20220729012739.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 50 rows affected (0.00 sec)
Records: 50  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 908 rows affected (0.02 sec)
Records: 908  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1803 rows affected (0.01 sec)
Records: 1803  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 659 rows affected (0.05 sec)
Records: 659  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
   
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
[root@localhost ~]# mysql_upgrade -uroot -p111 --force     //更新
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
WWW.tb_course                                      OK
WWW.tb_students_info                               OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@localhost ~]# service mysqld restart  //重启mysql
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: WWW

+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

第二种方式
如果是远程要加 -h指定远程服务器的IP地址授权

[root@localhost ~]# mysql -uroot -p111 < all-20220729012739.sql;
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

[root@localhost ~]# mysql_upgrade -uroot -p111 --force        //更新
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
WWW.tb_course                                      OK
WWW.tb_students_info                               OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@localhost ~]# service mysqld restart  //重启mysql
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: WWW

+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

###差异备份与恢复
####mysql差异备份

开启MySQL服务器的二进制日志功能
server-id=（这个地方如果是服务端就写小点，客户端就比服务端的大一点）

[root@localhost ~]# vi /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=10             //
log-bin=mysql_bin         //开启二进制日志功能，mysql_bin是二进制日志的名称可以自定义

差异备份里的全备（和全量备份不一样）
mysqldump --single-transaction //开启事务日志
–flush-logs //刷新日志
–master-data=2 //指定标识符为2
–all-databases
–delete-matser-logs //删除之前的日志

[root@localhost ~]# mysql_upgrade -uroot -p111 --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
WWW.tb_course                                      OK
WWW.tb_students_info                               OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysqldump  --single-transaction --flush-logs  --master-data=2  --all-databases  --delete-master-logs   >all-$(date '+%Y').sql
[root@localhost ~]# ls
all-20220729012739.sql  all-.sql                                     pass
all-20220729013313.sql  anaconda-ks.cfg                              tb_course.sql
all-2022.sql            mysql57-community-release-el7-11.noarch.rpm  WWW.sql

模拟修改添加数据

mysql> use WWW;
Database changed
mysql> show tables;
+------------------+
| Tables_in_WWW    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> insert tb_course(course_name) values('English');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_couses;
ERROR 1146 (42S02): Table 'WWW.tb_couses' doesn't exist
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
|  7 | English     |
+----+-------------+
7 rows in set (0.00 sec)

mysql> delete from tb_course where id = 5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  6 | HTML        |
|  7 | English     |
+----+-------------+
6 rows in set (0.00 sec)

###mysql差异备份恢复

模拟误删数据库

[root@localhost data]# mysqldump  --single-transaction --flush-logs  --master-data=2  --all-databases  --delete-master-logs   >all-$(date '+%Y').sql
[root@localhost ~]# ls
all-20220729012739.sql  all-.sql                                     pass
all-20220729013313.sql  anaconda-ks.cfg                              tb_course.sql
all-2022.sql            mysql57-community-release-el7-11.noarch.rpm  WWW.sql

mysql> select * from tb_course;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use WWW;
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 tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  6 | HTML        |
|  7 | English     |
+----+-------------+
6 rows in set (0.00 sec)
mysql> drop database WWW;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

###恢复完全备份

全量恢复

[root@localhost data]# mysql  -uroot  -p111  <  /root/all-2022.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use WWW;
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 tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 
mysql> show tables;
+------------------+
| Tables_in_WWW    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

###恢复差异备份

检查误删数据库的位置在什么地方两种方式
第一种

[root@localhost data]# ll
total 123940
-rw-r-----. 1 mysql mysql       56 Jul 27 03:22 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 27 03:22 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 03:22 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 03:22 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 03:22 client-key.pem
-rw-r-----. 1 mysql mysql      803 Jul 29 12:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 16:37 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 16:37 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 03:22 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 30 16:05 ibtmp1
-rw-r-----. 1 mysql mysql   133606 Jul 30 16:11 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 30 16:35 mysql
-rw-r-----. 1 mysql mysql      688 Jul 30 16:05 mysql_bin.000004
-rw-r-----. 1 mysql mysql      355 Jul 30 16:22 mysql_bin.000005
-rw-r-----. 1 mysql mysql   857978 Jul 30 16:35 mysql_bin.000006
-rw-r-----. 1 mysql mysql       57 Jul 30 16:22 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 30 16:05 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 12:04 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 12:04 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 03:22 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 03:22 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 03:22 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 03:22 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 12:04 sys
drwxr-x---. 2 mysql mysql      118 Jul 30 16:35 WWW

mysql> show binlog events in 'mysql_bin.000004';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000004 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000004 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000004 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000004 | 219 | Query          |        10 |         290 | BEGIN                                 |
| mysql_bin.000004 | 290 | Table_map      |        10 |         344 | table_id: 142 (WWW.tb_course)         |
| mysql_bin.000004 | 344 | Write_rows     |        10 |         392 | table_id: 142 flags: STMT_END_F       |
| mysql_bin.000004 | 392 | Xid            |        10 |         423 | COMMIT /* xid=482 */                  |
| mysql_bin.000004 | 423 | Anonymous_Gtid |        10 |         488 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000004 | 488 | Query          |        10 |         559 | BEGIN                                 |
| mysql_bin.000004 | 559 | Table_map      |        10 |         613 | table_id: 142 (WWW.tb_course)         |
| mysql_bin.000004 | 613 | Delete_rows    |        10 |         657 | table_id: 142 flags: STMT_END_F       |
| mysql_bin.000004 | 657 | Xid            |        10 |         688 | COMMIT /* xid=485 */                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.00 sec)

mysql> show binlog events in 'mysql_bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000005 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000005 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000005 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000005 | 219 | Query          |        10 |         308 | drop database WWW                     |
| mysql_bin.000005 | 308 | Rotate         |        10 |         355 | mysql_bin.000006;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.00 sec)

第二种

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000004 > /opt/mysql_bin004.txt
[root@localhost data]# less /opt/mysql_bin004.txt 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220729 12:15:16 server id 10  end_log_pos 123 CRC32 0x3f35c9d0         Start: binlog v 4, server v 5.7.37-log created 220729 12:15:16
# at 123
#220729 12:15:16 server id 10  end_log_pos 154 CRC32 0x656c1dcf         Previous-GTIDs
# [empty]
# at 154
#220729 12:22:38 server id 10  end_log_pos 219 CRC32 0x77233b25         Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#220729 12:22:38 server id 10  end_log_pos 290 CRC32 0xd0434009         Query   thread_id=2
     exec_time=0     error_code=0
SET TIMESTAMP=1659068558/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, 
@@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549120/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_s
erver=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 290
#220729 12:22:38 server id 10  end_log_pos 344 CRC32 0xe89919ff         Table_map: `WWW`.`tb_
course` mapped to number 142
# at 344
#220729 12:22:38 server id 10  end_log_pos 392 CRC32 0x901084ef         Write_rows: table id 
142 flags: STMT_END_F
### INSERT INTO `WWW`.`tb_course`
### SET
###   @1=7
###   @2='English'
# at 392
#220729 12:22:38 server id 10  end_log_pos 423 CRC32 0xfc1e9869         Xid = 482
COMMIT/*!*/;
# at 423
#220729 12:24:42 server id 10  end_log_pos 488 CRC32 0xa02c28c5         Anonymous_GTID  last_
committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 488
#220729 12:24:42 server id 10  end_log_pos 559 CRC32 0x8105b107         Query   thread_id=2
     exec_time=0     error_code=0
SET TIMESTAMP=1659068682/*!*/;
BEGIN
......

恢复

[root@localhost data]# mysqlbinlog --stop-position=769 /opt/data/mysql_bin.000004 | mysql -uroot -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WWW                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use WWW;
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_WWW    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  6 | HTML        |
|  7 | English     |
+----+-------------+
6 rows in set (0.00 sec)

mysql>

数据库备份的方式手段
- 全量备份：（全体备份优点：确保每次的数据时最新最全的 | 缺点：备份速度慢）
- 增量备份：（增量备份是基于全备的基础之上的，增倍是对全备之后修改的东西进行备份优点：备份速度快并且都是最新的 | 缺点：恢复数据难）
- 差异备份：（差异备份是基于全备的基础之上的，第一次备份时全备，备份只备份修改的部分同步到全备里面有点：备份同步，不会重复备份）