msql数据备份与还原

一数据库备份：

1.将单个表导出为csv文件

数据表导出csv语句：

select *from TSI_20140206_0_20120701 into outfile '/home/maysqldata/TSI_20140206_0_20120701.csv'  fields terminated by ',' optionally enclosed by'"' escaped by '"' lines terminated by '\r\n'; 

数据表导入csv语句：

导入的时候需要事先建好表结构，才能够导入。

mysql导出表结构的代码：

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

使用mysqldump命令
格式
mysqldump YourDatabaseName -uYourUserName -pYourPassword 
YourDatabaseName是你想处理的数据库名
YourUserName和YourPassword 对应你的授权口令
如果只需要导出表的结构,那么可以使用mysqldump的 -d 选项

导出整个库的表结构如下：

mysqldump -uroot -p -d databasename > createtab.sql，

如果只想导出 表 table1 table2 table3 的 表结构 和 数据呢？
该如何导出？

mysqldump -uroot -p -d databasename test1 test2 test3 > createtab.sql

-- 上面的是导出指定表结构，下面这个可以导出指定表结构和数据
mysqldump -uroot -p --tables databasename > createtab.sql

mysqldump -uroot -p -d databasename table1table2 table3 > createtab.sql

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

然后导入：

load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by'"' lines terminated by '\r\n';  

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.当数据表的个数很大，以至于手动备份不方便时（例如将数据库中所有表导出为csv文件），这里我用了一个比较笨的方法：

首先查询数据库中所有的表名，将表名称输出为csv文件。

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='BD_result_DB'into outfile '/mnt/80.50store/BD_mysql/BD_result_DB/tableName.csv';;

然后，自己编写了一个java小程序，读取这些表名称，循环遍历生成单表备份语句。

package cn.com.mysqlBack;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Locale;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

public class TestMain {
	public static Logger LOG = Logger.getLogger(TestMain.class);

	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		ResourceBundle bundle = ResourceBundle.getBundle("config",
				Locale.getDefault());
		String filePath = bundle.getString("inputfile");
		String outfile = bundle.getString("outfile");
		Operate operate = new Operate();
		String sqlDB = "use " + bundle.getString("DB");
		operate.executeQuery(sqlDB);
		String sql = bundle.getString("getTBNameSql");
		operate.executeQuery(sql);
		File file = new File(filePath);
		if (file.isFile() && file.exists()) {
			InputStreamReader read = new InputStreamReader(new FileInputStream(
					file));
			BufferedReader bufferedReader = new BufferedReader(read);
			String lineTxt = null;
			while ((lineTxt = bufferedReader.readLine()) != null) {
				String tableBackupSql = "select * from "
						+ lineTxt
						+ " into outfile "
						+ "\'/mnt/80.50store/BD_mysql/"
						+ bundle.getString("DB")
						+ "/"
						+ lineTxt
						+ ".csv\'"
						+ " FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' escaped by '\"'  lines terminated by  '\\r\\n';";
				System.out.println(tableBackupSql);
				// String tableBackupSql1
				// ="SELECT * FROM TSI_20140310_320500_0_20120701 INTO OUTFILE '/APP/niuxinzan/123.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
				// LINES TERMINATED BY '\n';

				// 直接执行sql语句，备份表
				operate.executeQuery(tableBackupSql);
				// 将sql语句保存到文件中，以备批量复制到mysql命令行中用。
				System.out.println(tableBackupSql);
				// File outfile1 = new File(outfile);
				// BufferedWriter bw = new BufferedWriter(new
				// FileWriter(outfile1,
				// true));
				// bw.write(tableBackupSql);
				// bw.write("\r\n");
				// bw.flush();
				// bw.close();
			}
			operate.close();
			LOG.info("------out to csv successful!-----");
		}

	}

}

最后将outfile的csv文件里面的语句全部复制到mysql命令行中，执行完毕即可。或者，不用复制到mysql命令行，而是直接在程序中执行mysql命令也可。

二数据库还原

package cn.com.mysqlLoad;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Locale;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import cn.com.mysqlBack.Operate;
import cn.com.mysqlBack.TestMain;

public class loadCSVToMysql {
	public static Logger LOG = Logger.getLogger(loadCSVToMysql.class);

	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		// System.out.println("lines terminated by '\\r\\n';");
		ResourceBundle bundle = ResourceBundle.getBundle("config",
				Locale.getDefault());
		String filePath = bundle.getString("loadinputfile");
		System.out.println("filePath: " + filePath);
		Operate operate = new Operate();
		String sqlDB = "use " + bundle.getString("DBofload");
		System.out.println("sqlDB: " + sqlDB);
		operate.execute(sqlDB);
		File file = new File(filePath);
		if (file.isFile() && file.exists()) {
			InputStreamReader read = new InputStreamReader(new FileInputStream(
					file));
			BufferedReader bufferedReader = new BufferedReader(read);
			String lineTxt = null;
			while ((lineTxt = bufferedReader.readLine()) != null) {
				String DROPtable = "";
				String createtable1 = "";
				if (lineTxt.contains("BOTTLENECK")) {
					DROPtable = "DROP TABLE IF EXISTS " + lineTxt;
					createtable1 = "CREATE TABLE "
							+ lineTxt
							..................
							+ " KEY idx_linkID (linkID)) ENGINE=myisam DEFAULT CHARSET=utf8;";

					System.out.println("create table sqlString: "
							+ createtable1);
				}
				if (lineTxt.contains("PASSABLEROUTEDETAIL")) {
					DROPtable = "DROP TABLE IF EXISTS " + lineTxt;
					createtable1 = "CREATE TABLE "
							+ lineTxt
							+ "("
							.............
							+ ") ENGINE=myisam DEFAULT CHARSET=utf8;";
				}
				if (lineTxt.contains("RTIC_INFO")) {
					DROPtable = "DROP TABLE IF EXISTS " + lineTxt;
					createtable1 = "CREATE TABLE "
							.................

					System.out.println("create table sqlString: "
							+ createtable1);
				}
				if (lineTxt.contains("RTIC_ROAD")) {

					DROPtable = "DROP TABLE IF EXISTS " + lineTxt;
					createtable1 = "CREATE TABLE " + lineTxt + "("
							
+..........
+ ") ENGINE=myisam DEFAULT CHARSET=utf8;";System.out.println("create table sqlString: "+ createtable1);}if (lineTxt.contains("RTICLINKINFO")) {DROPtable = "DROP TABLE IF EXISTS " + lineTxt;createtable1 = "CREATE TABLE " + lineTxt + "(".........................+
 ") ENGINE=myisam DEFAULT CHARSET=utf8;";System.out.println("create table sqlString: "+ createtable1);}if (lineTxt.contains("TRAFFICVOLUME")) {DROPtable = "DROP TABLE IF EXISTS " + lineTxt;createtable1 = "CREATE TABLE "+ lineTxt............+ ") ENGINE=myisam
 DEFAULT CHARSET=utf8;";System.out.println("create table sqlString: "+ createtable1);}if (lineTxt.contains("TSI_")) {DROPtable = "DROP TABLE IF EXISTS " + lineTxt;createtable1 = "CREATE TABLE "+ lineTxt+ "("..................+ ") ENGINE=myisam DEFAULT CHARSET=utf8;";System.out.println("create
 table sqlString: "+ createtable1);}if (lineTxt.contains("user_info")) {DROPtable = "DROP TABLE IF EXISTS " + lineTxt;createtable1 = "CREATE TABLE " + lineTxt + "("+ "id int(11) NOT NULL AUTO_INCREMENT,".............+ ") ENGINE=myisam DEFAULT CHARSET=utf8;";System.out.println("drop
 table sqlString: " + DROPtable);System.out.println("create table sqlString: "+ createtable1);}operate.execute(DROPtable);operate.execute(createtable1);String tableBackupSql = "load data infile "+ "\'/mnt/80.50store/BD_mysql/"+ bundle.getString("DBofcsv")+
 "/"+ lineTxt+ ".csv\'"+ " into table "+ lineTxt+ " fields terminated by ',' optionally enclosed by '\"' escaped by'\"' lines terminated by '\\r\\n';";System.out.println("load table sqlString:" + tableBackupSql);operate.execute(tableBackupSql);}operate.close();LOG.info("------out
 to csv successful!-----");}}}