MySQL导入、导出、数据库定时备份

时间：2024-03-07 10:22:35

　　本篇介绍MySQL的两种导出、四种导入。导出一种是mysqldump，一种是select ...info outfile高效导出，下面简称select导出；导入分别是source、>、load、mysqlimport。其中只有mysqldump能导出sql语句，只有source、>能导入sql。使用的MySQL版本是8.0.22，注意不同版本之间可能会有差异。

　　一、mysqldump导出操作

　　1）导出151机器上的数据库mydb1

/usr/bin/mysqldump -h192.168.43.151 -port3306 -uroot -p123456 mydb1 > /aa.sql

　　2）导出所有数据库

/usr/bin/mysqldump -uroot -p123456 --all-databases  > /aa.sql

　　3）导出数据库mydb1

/usr/bin/mysqldump -uroot -p123456 mydb1 > /aa.sql

　　4）导出数据库mydb1、mydb2

/usr/bin/mysqldump -uroot -p123456 --databases mydb1 mydb2 > /aa.sql

　　5）导出数据库mydb1的表t_test1

/usr/bin/mysqldump -uroot -p123456 mydb1 t_test1 > /aa.sql

　　6）导出数据库mydb1的表t_test1和t_test2

/usr/bin/mysqldump -uroot -p123456 mydb1 t_test1 t_test2> /aa.sql

　　7）导出数据库mydb1的book表的建表语句

/usr/bin/mysqldump -uroot -p123456 -d sltest book > book.sql

　　8）导出数据库mydb1的book表的插入语句

/usr/bin/mysqldump -uroot -p123456 -t sltest book > book.sql

　　9）导出数据库sltest的book表的建表语句和数据文件（生成book.sql和book.txt）

　　注意：关闭selinx，否则报没有权限，book.txt中是数据，字段数据以tab键分隔

/usr/bin/mysqldump -uroot -p123456 -T /usr/local/myroom/tmp/ sltest book

　　10）导出数据库sltest的book表，字段以|!?|分隔，行默认以回车(\n)分隔

/usr/bin/mysqldump -uroot -p123456 sltest book -t -T /usr/local/myroom/tmp/ --fields-terminated-by=\'|!?|\'

　　11）导出数据库sltest的book表，字段以|!?|分隔，行以@#$分隔

/usr/bin/mysqldump -uroot -p123456 sltest book -t -T /usr/local/myroom/tmp/ --fields-terminated-by=\'|!?|\' --lines-terminated-by=\'@#$\'

　　二、SELECT导出操作

　　注意：如果报The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
　　解决：https://blog.csdn.net/weixin_44595372/article/details/88723191

　　1）导出book表，字段以|!?|分隔，行默认以回车（\n）分隔

select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\';

　　2）导出book表，字段以|!?|分隔，行以回车分隔（同上）

select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\' lines terminated by \'\n\';

　　3）导出book表，字段以|!?|分隔，行以$#$分隔

select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\' lines terminated by \'$#$\';

　　三、SOURCE导入操作 

　　1）如果待导入文件中没有指定数据库，需要use指定数据库，否则导入当前use的库中

mysql> use sltest;
mysql> source /aa.sql;

　　2）如果不存在待导入的数据库，文件中也没有创建语句，需要手动创建，再导入

mysql> create database mydb1;
mysql> use mydb1;
mysql> set names utf8;
mysql> source /aa.sql;

　　3）如果待导入文件中有创建库语句，自动创建数据库后即指定该库，直接导入

mysql> source /aa.sql;

　　四、< 导入操作

　　1）如果待导入文件中没有创建库语句，导入命令中指定库

[root@localhost /]# mysql -uroot -p123456 mydb1 < /aa.sql

　　2）如果待导入文件中有创建库语句，自动创建数据库后即指定该库，直接导入

[root@localhost /]# mysql -uroot -p123456 < /aa.sql

　　五、LOAD导入操作

　　1）不指定行分隔符（或默认\n），下面任意两句的导出匹配任意两句的导入

mysql> select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\';
mysql> select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\' lines terminated by \'\n\';
mysql> load data infile "/usr/local/myroom/tmp/book.dat" into table book fields terminated by "|!?|";
mysql> load data infile "/usr/local/myroom/tmp/book.dat" into table book fields terminated by "|!?|" lines terminated by \'\n\';

　　2）指定行分隔符

mysql> select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\' lines terminated by \'$#$\';
mysql> load data infile "/usr/local/myroom/tmp/book.dat" into table book fields terminated by "|!?|" lines terminated by \'$#$\';

　　3）导入使用mysqldump导出的数据文件（只与文件格式有关）

/usr/bin/mysqldump -uroot -p123456 sltest book -t -T /usr/local/myroom/tmp/ --fields-terminated-by=\'|!?|\'
mysql> load data infile "/usr/local/myroom/tmp/book.txt" into table book fields terminated by "|!?|";
/usr/bin/mysqldump -uroot -p123456 sltest book -t -T /usr/local/myroom/tmp/ --fields-terminated-by=\'|!?|\' --lines-terminated-by=\'$#$\'
mysql> load data infile "/usr/local/myroom/tmp/book.txt" into table book fields terminated by "|!?|" lines terminated by \'$#$\';

　　六、mysqlimport导入操作 

　　1）查看并开启local_infile参数

mysql> show global variables like \'local_infile\';
mysql> set global local_infile = \'on\';

　　2）将数据book.txt导入到数据库sltest的book表

/usr/bin/mysqldump -uroot -p123456 sltest book -t -T /usr/local/myroom/tmp/ --fields-terminated-by=\'|!?|\' --lines-terminated-by=\'$#$\'
/usr/bin/mysqlimport -uroot -p123456 --local sltest /usr/local/myroom/tmp/book.txt --fields-terminated-by=\'|!?|\' --lines-terminated-by=\'$#$\'

　　3）将数据book.dat导入到数据库sltest的book表

mysql> select * from book into outfile \'/usr/local/myroom/tmp/book.dat\' fields terminated by \'|!?|\' lines terminated by \'$#$\';
/usr/bin/mysqlimport -uroot -p123456 --local sltest /usr/local/myroom/tmp/book.dat --fields-terminated-by=\'|!?|\' --lines-terminated-by=\'$#$\'

　　七、定时备份

　　1）写脚本（后面会单独写一个详细的，本篇不是重点，不再写了）

#!/bin/bash
mysqldump -uroot -p123456 mydb1 > /var/mysql/backup/mydb1_$(date +%Y%m%d_%H%M%S).sql

　　2）配置定时任务

crontab -e

　　定时计划如下，每天晚上23:30备份。

30 23 * * * sh /usr/local/myshell/mysql/database_backup.sh

　　定时任务相关知识，参考点击这里

　　 

相关文章