四、数据库的备份与恢复

冷备份：停机备份数据库文件；

热备份：

使用mysqldump命令备份：

[root@linuxidc ~]# mysqldump -u root -p school teacher student> /mysql_backup/teacher.sql

备份整个数据库表，后面则不需要指定任何表：             

[root@linuxidc ~]# mysqldump -u root -p school> /mysql_backup/all_tables.sql

备份整个数据库：

[root@linuxidc ~]# mysqldump -u root -p -B school> /mysql_backup/all.sql

进入库恢复表或者恢复表：     

MariaDB [school]> source /mysql_backup/teacher.sql;

[root@linuxidc ~]# mysql -u root -p'redhat' < /mysql_backup/all.sql

将表数据保存到文件，修改备份目录的属主属组信息：

[root@linuxidc ~]# setfacl -m u:mysql:rwx /mysql_backup/

MariaDB [school]> select * from teacher into outfile '/mysql_backup/teacher_data'fields terminated by ',';

根据外部文件恢复表数据：

MariaDB [school]> load data infile '/mysql_backup/teacher_data' into table teacher fields terminated by ',';

mysqldump不能做增量备份