AINav

MySQL数据备份恢复

2024/7/15·1 views

数据备份

mysqldump

命令行下具体用法如下：

shell 复制代码 
mysqldump -u用戶名 -p密码 -d 数据库名 表名 > 脚本名;

例子

为保证所有数据都被写到磁盘（包括索引数据），可能需要在备份前使用flush tables 语句

shell 复制代码 
mysqldump -h localhost -uroot -p123456 database > dump.sql  #导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 -B database > dump.sql #导出整个数据库结构和数据[包含建库语句]
mysqldump -h localhost -uroot -p123456  database table > dump.sql # 导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456 -d database > dump.sql #导出整个数据库结构（不包含数据）
mysqldump -uroot -p --quick --databases db1 db2 > /data/sql/db.sql #导出多个库
mysqldump -uroot -p --quick --all-databases > /data/sql/db.sql #导出全部
mysqldump -h localhost -uroot -p123456 -d database table > dump.sql #导出单个数据表结构（不包含数据）
mysqldump -uroot -p db1 tb1 tb2 tb3 > /data/sql/db.sql #导出多个表
mysqldump -uroot -p im | mysql -uroot -proot test #使用管道符将im库中的表和数据导入到test中
mysqldump -uroot -p --set-gtid-purged=OFF database table --where 'id > 100' > /tmp/user.sql # --where 只写条件，不能联合查询 
mysqldump old_db [table] [-P<port>] [--socket=<socket>] -uroot -p<password> | mysql -P3307 [--socket=<socket>] -uroot -p <new_db>   # 现在将old_db数据复制到new_db

导出会将多条insert语句合并为一条，可以通过添加选项 --skip-extended-insert 来导出单条sql

通过SQL导出

复制代码 
mysql -uroot -p -e 'select * from user limit 10' > /tmp/user.sql

导出CSV

CSV格式，其要点包括：

  • 字段之间以逗号分隔，数据行之间以\r\n分隔；
  • 字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

准备

首先需要查看下一个变量

sql 复制代码 
SHOW VARIABLES LIKE "secure_file_priv";

secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

  • 当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
  • 当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
  • 当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

修改

  1. 把导入文件放入secure-file-priv目前的value值对应路径
  2. 把secure-file-priv的value值修改为准备导入文件的放置路径
  3. 去掉导入的目录限制。可修改mysql配置文件（Windows下为my.ini, Linux下的my.cnf），在[mysqld]下面，查看是否有: secure_file_priv = 这样一行内容，表示不限制目录，等号一定要有，否则mysql无法启动。

修改完配置文件后，重启mysql生效。

重启后：

复制代码 
service mysqld stop
service mysqld start

如果不修改可能会有如下报错

复制代码 
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

开始

sql 复制代码 
SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'F:/worksp/mysql/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

该语句在F:/worksp/mysql/目录下创建一个包含结果集，名称为cancelled_orders.csv的CSV文件。

CSV文件包含结果集中的行集合。每行由一个回车序列和由LINES TERMINATED BY '\r\n'子句指定的换行字符终止。文件中的每行包含表的结果集的每一行记录。

每个值由FIELDS ENCLOSED BY '"'子句指示的双引号括起来。 这样可以防止可能包含逗号(，)的值被解释为字段分隔符。 当用双引号括住这些值时，该值中的逗号不会被识别为字段分隔符。

将数据导出到文件名包含时间戳的CSV文件

我们经常需要将数据导出到CSV文件中，该文件的名称包含创建文件的时间戳。 为此，您需要使用MySQL准备语句。

以下命令将整个orders表导出为将时间戳作为文件名的一部分的CSV文件。

sql 复制代码 
SET @TS = DATE_FORMAT(NOW(),'_%Y%m%d_%H%i%s');

SET @FOLDER = 'F:/worksp/mysql/';
SET @PREFIX = 'orders';
SET @EXT    = '.csv';

SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
    "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
    "  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;

下面，让我们来详细讲解上面的命令。

  • 首先，构造了一个具有当前时间戳的查询作为文件名的一部分。
  • 其次，使用PREPARE语句FROM命令准备执行语句。
  • 第三，使用EXECUTE命令执行语句。

可以通过事件包装命令，并根据需要定期安排事件的运行。

使用列标题导出数据

如果CSV文件包含第一行作为列标题，那么该文件更容易理解，这是非常方便的。

要添加列标题，需要使用UNION语句如下：

sql 复制代码 
(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'F:/worksp/mysql/orders_union_title.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

如查询所示，需要包括每列的列标题。

处理NULL值

如果结果集中的值包含NULL值，则目标文件将使用“N/A”来代替数据中的NULL值。要解决此问题，您需要将NULL值替换为另一个值，例如不适用(N/A)，方法是使用IFNULL函数，如下：

sql 复制代码 
SELECT 
    orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
FROM
    orders INTO OUTFILE 'F:/worksp/mysql/orders_null2na.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n';

我们用N/A字符串替换了shippingDate列中的NULL值。 CSV文件将显示N/A而不是NULL值。

给导出文件添加列名

复制代码 
select * into outfile '/tmp/test1.csv' fields terminated by ',' escaped by '' optionally enclosed  by '' lisnes terminated by '\n' from (select 'col1','col2','col3','col4','col5' union select id,user,url,name,age  from test) b;

使用MySQL命令结合sed的方法

  • 使用-e参数执行命令，-s是去掉输出结果的各种划线
  • 利用sed将字段之间的tab换成，并且将NULL替换成空字符
  • 如果不想要标题行，可以使用-N参数
shell 复制代码 
mysql -uroot  -p密码  test -e "select * from test where id > 1" -s |sed -e  "s/\t/,/g" -e "s/NULL/  /g" -e "s/\n/\r\n/g"  > /tmp/test2.csv

使用mysqldump导出

shell 复制代码 
mysqldump -uroot  -p密码  -t -T/tmp/ test  test --fields-terminated-by=',' --fields-escaped-by='' --fields-optionally-enclosed-by=''
复制代码 
test（第一个test） ：导出的数据库；
test（第二个test）：导出的数据表；
-t ：不导出create 语句，只要数据；
-T 指定到处的位置，注意目录权限，注意这里只到目录，默认名字是table_name.txt；
–fields-terminated-by=’,’：字段分割符；
–fields-enclosed-by=’’ ：字段引号；

数据恢复

sql 复制代码 
mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql source .></s1.sql>
sql 复制代码 
load data infile '/mnt/d/sql.txt' into table <table>;

扩展

mysqlhotcopy #从一个数据库复制全部数据
select into outfile

参考文档

MySQL将表导出为CSV - MySQL教程 (yiibai.com)

MySQL :: MySQL 8.0 Reference Manual :: 9 Backup and Recovery

对这篇文章有意见或建议？提交反馈
快来和小猫聊天吧~