数据备份
MySQL 中你可以使用 SELECT...INTO OUTFILE
语句来简单的导出数据到文本文件上。
使用 SELECT ... INTO OUTFILE
语句导出数据
以下实例中我们将数据表 hello
数据导出到 ./data.txt
文件中:
SELECT * FROM `hello` INTO OUTFILE './data.txt';
你可以通过命令选项来设置数据输出的指定格式,以下实例为导出 CSV 格式:
SELECT * FROM `passwd` INTO OUTFILE './data.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
在下面的例子中,生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用。
SELECT a,b,a+b INTO OUTFILE './data.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
SELECT ... INTO OUTFILE
语句有以下属性:
LOAD DATA INFILE
是 SELECT ... INTO OUTFILE
的逆操作,SELECT 句法。使用 SELECT ... INTO OUTFILE
,为了将一个数据库的数据写入一个文件;使用 LOAD DATA INFILE
,为了将文件读回数据库。
SELECT...INTO OUTFILE 'file_name'
形式的 SELECT 可以把被选择的行写入一个文件中。该文件
被创建到服务器主机上,因此您必须拥有 FILE 权限,才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE
不会起任何作
用。
在 UNIX 中,该文件被创建后是可读的,权限由 MySQL 服务器所拥有。这意味着,虽然你就
可以读取该文件,但可能无法将其删除
另外 Windows 下是有限制的,要打开 my.ini,然后找到 secure-file-priv 参数依据这个参数填入绝对路径,否则会出现权限错误。
数据恢复
MySQL 中提供了 LOAD DATA INFILE
语句来插入数据。 以下实例中将从当前目录中读取文件
dump.txt
,将该文件中的数据插入到当前数据库的 mytbl
表中。
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
如果指定 LOCAL 关键词,则表明从客户主机上按路径读取文件。如果没有指定,则文件
在服务器上按路径读取文件。
你能明确地在 LOAD DATA
语句中指出列值的分隔符和行尾标记,但是默认标记是定位符和
换行符。
两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的,但是如果两个同
时被指定,FIELDS 子句必须出现在 LINES 子句之前。
如果用户指定一个 FIELDS 子句,它的子句(TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和
ESCAPED BY) 也是可选的,不过,用户必须至少指定它们中的一个。
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n';
LOAD DATA
默认情况下是按照数据文件中列的顺序插入数据的,如果数据文件中的列与插
入表中的列不一致,则需要指定列的顺序。
如,在数据文件中的列顺序是 a,b,c,但在插入表的列顺序为 b,c,a,则数据导入语法如下:
LOAD DATA LOCAL INFILE 'dump.txt'
INTO TABLE mytbl (b, c, a);
代码范例:
MYSQL* mysql = new MYSQL();
MYSQL* pDB = mysql_init(mysql);
if (pDB == NULL) {
std::cout << "mysql_init failed" << std::endl;
return -1;
}
// 连接时设置允许加载本地文件
pDB = mysql_real_connect(pDB, "localhost", "root", "root", "mysql", 3306, NULL, CLIENT_LOCAL_FILES);
if (pDB) {
std::string sql = "CREATE DATABASE hello"; // SQL语句可替换为自己实际需要执行的语句
// 创建数据库
int ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
MYSQL_RES* res = mysql_use_result(mysql);
if (res) {
show_result(res);
mysql_free_result(res);
}
// 授予权限
sql = "GRANT ALL ON hello.* TO 'hello'@'localhost';"; // SQL语句可替换为自己实际需要执行的语句
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
res = mysql_use_result(mysql);
if (res) {
show_result(res);
mysql_free_result(res);
}
// 指定数据库
sql = "USE hello"; // SQL语句可替换为自己实际需要执行的语句
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "SET NAMES 'utf8';";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "SET CHARACTER SET utf8;";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
// 创建表
sql = "CREATE TABLE IF NOT EXISTS `hello` (`编号` NVARCHAR(16) PRIMARY KEY,";
sql += "`age` INT NOT NULL DEFAULT 18";
sql += ")ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "CREATE TABLE IF NOT EXISTS `teacher` (`编号` NVARCHAR(16) PRIMARY KEY,";
sql += "`age` INT NOT NULL DEFAULT 18";
sql += ")ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
// 日志表
sql = "CREATE TABLE `logs` (`Id` int(11) NOT NULL AUTO_INCREMENT, `log` varchar(255) DEFAULT NULL COMMENT \"日志说明\", PRIMARY KEY(`Id`) )ENGINE=InnoDB DEFAULT CHARSET = utf8mb4 COMMENT=\"日志\";";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "INSERT INTO `hello` (`编号`, `age`) VALUES(\"9527\", 99)";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "INSERT INTO `hello` (`编号`, `age`) VALUES(\"9528\", 23)";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "INSERT INTO `teacher` (`编号`, `age`) VALUES(\"9529\", 99)";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "INSERT INTO `teacher` (`编号`, `age`) VALUES(\"9530\", 100)";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "SELECT age FROM `hello` UNION SELECT age FROM `teacher`;";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
res = mysql_store_result(mysql);
if (res) {
show_result(res);
mysql_free_result(res);
}
// 数据备份建议数据导出时的文件名与表名相同
sql = "SELECT * FROM `hello` INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/hello.txt'\n \
FIELDS TERMINATED BY ',' \n \
LINES TERMINATED BY '\r\n'";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
sql = "DELETE FROM `hello`";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
// 5.0之前的版本可以执行成功 5.0之后的版本大概率失败
sql = "LOAD DATA LOCAL INFILE `C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/hello.txt` INTO TABLE `hello` \n \
FIELDS TERMINATED BY `,` \n \
LINES TERMINATED BY `\r\n`";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
// 失败后使用mysqlimport进行导入
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
std::string cmd = "mysqlimport -u root -proot --local hello ";
cmd += "\"C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/hello.txt\" ";
cmd += "--fields-terminated-by=\",\" --lines-terminated-by=\"\\r\\n\"";
system(cmd.c_str());
// 报错Error: 3948, Loading local data is disabled; this must be enabled on both the client and server sides, when using table: hello
// 控制台进入MySQL 执行 set global local_infile=1; 即可解决
}
// mysqlimport后会断开连接 重新连接
mysql_close(mysql);
mysql_init(mysql);
pDB = mysql_real_connect(mysql, "localhost", "root", "root", "hello", 3306, NULL, 0);
sql = "SELECT COUNT(*) FROM `hello`";
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
res = mysql_store_result(mysql);
if (res) {
show_result(res);
mysql_free_result(res);
}
// 删除数据库
sql = "DROP DATABASE hello"; // SQL语句可替换为自己实际需要执行的语句
ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
if (ret != 0) {
std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
return -1;
}
mysql_close(pDB);
std::cout << pDB << std::endl;
delete mysql;
}
mysql = nullptr;
return 0;