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