备份数据库（myqldump）

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

mysql> create database a;
Query OK, 1 row affected (0.00 sec)

mysql> use a;
Database changed
mysql> create table a1(name char(20),age int(10));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into  a1(name,age) values('lishi',19);
Query OK, 1 row affected (0.06 sec)

mysql> select * from a1;
+-------+------+
| name  | age  |
+-------+------+
| lishi |   19 |
+-------+------+
1 row in set (0.00 sec)

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

备份某一个库。

[root@localhost ~]# mysqldump -uroot -p123 a > 1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls 1.sql -l
-rw-r--r-- 1 root root 1798 1月  16 09:55 1.sql

恢复到a2库。

[root@localhost ~]# mysql -uroot -p123 a2 < 1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'a2'
a2库必须事先存在

mysql> create database a2;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123 a2 < 1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| a2                 |
| mingongge          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use a2;
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_a2 |
+--------------+
| a1           |
+--------------+
1 row in set (0.00 sec)

mysql> select * from a1;
+-------+------+
| name  | age  |
+-------+------+
| lishi |   19 |
+-------+------+
1 row in set (0.00 sec)

备份其中的一个表

mysql> create database b;
Query OK, 1 row affected (0.00 sec)

mysql> use b
Database changed
mysql> create table b1(name char(20),age int(10));
Query OK, 0 rows affected (0.06 sec)

[root@localhost ~]# mysqldump -uroot -p123 b b1 > 4.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls 4.sql 
4.sql

还原一张表

[root@localhost ~]# mysql -uroot -p123 yu < 4.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'yu'

mysql> create database yu;
Query OK, 1 row affected (0.00 sec)

[root@localhost ~]# mysql -uroot -p123 yu < 4.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| a2                 |
| b                  |
| mingongge          |
| mysql              |
| performance_schema |
| sys                |
| yu                 |
+--------------------+
9 rows in set (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_yu |
+--------------+
| b1           |
+--------------+
1 row in set (0.00 sec)

同时备份多个数据库

[root@localhost ~]# mysqldump -uroot -p123 --databases a b > 10.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls 10.sql -l
-rw-r--r-- 1 root root 2576 1月  16 10:15 10.sql

恢复。

[root@localhost ~]# mysql -uroot -p123 < 10.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

备份

[root@localhost ~]# mysqldump -uroot -p -B a b mysql|gzip >/root/a_mysql_$(date +%F).sql.gz
Enter password: 
[root@localhost ~]# ls -l a_mysql_2020-01-16.sql.gz 
-rw-r--r-- 1 root root 228989 1月  16 10:23 a_mysql_2020-01-16.sql.gz
[root@localhost ~]# mysqldump -uroot -p -B --events a b mysql|gzip >/root/a_mysql0_$(date +%F).sql02.gz
Enter password: 
[root@localhost ~]# ls -l a_mysql*
-rw-r--r-- 1 root root 229027 1月  16 10:25 a_mysql0_2020-01-16.sql02.gz
-rw-r--r-- 1 root root 228989 1月  16 10:23 a_mysql_2020-01-16.sql.gz