一、备份
1、进入mysql目录
cd /var/lib/mysql (进入mysql目录,根据安装情况会有差别)
2、备份
mysqldump -u root -p密码 数据库名 数据表名 > mysql.sql
然后就在/var/lib/mysql
目录下面生成了一个mysql.sql备份文件
备份语句:
mysqldump -h10.10.1.55 -uroot -proot ggsql > /home/beifen/kk.sql
二、还原
法一:
1、mysql -u root -p 回车,输入密码,进入MySQL的控制台"mysql>"
2、进入MySQL Command Line Client,输入密码,进入到“mysql>”,输入命令show databases;
,回车,看看有些什么数据库;建立你要还原的数据库,输入create database voice;
,回车;切换到刚建立的数据库,输入use voice;
,回车;导入数据,输入source voice.sql;
,回车,开始导入,再次出现"mysql>"并且没有提示错误即还原成功。
法二:
[root@localhost ~]# cd /var/lib/mysql
(进入到MySQL库目录,根据自己的MySQL的安装情况调整目录)
[root@localhost mysql]# mysql -u root -p voice<voice.sql
,输入密码即可。
备份代码:
/**
* 数据库定时备份
*/
@Scheduled(cron="${schedule.beifen.dbcron}")
public void Bf(){
//mysqldump -u root --password=root --database mysql > /home/kk.sql;
log.debug("========================数据库定时备份开始。。。=========================");
if(!backPath.endsWith(File.separator)){
backPath += File.separator;
}
File file = new File(backPath);
if(!file.exists()){
file.mkdirs();
}
//获取数据库名
int i = dbUrl.lastIndexOf("/");
int j = dbUrl.lastIndexOf("?");
String dbName = dbUrl.substring(i + 1, j);
String url = (dbUrl.split(":")[2]).substring(2);
//log.debug("数据库url是:" + url);
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String time = sdf.format(date);
if(!backPath.startsWith("/")){
backPath = "/" + backPath;
}
if(!backPath.endsWith("/")){
backPath = backPath + "/";
}
try {
//解密数据库密码
String pwd = AESCode.decrypt(password, AESCode.DKGGKEY);
//System.out.println("数据库密码是:" + pwd);
File shellpath = new File(backPath);
if(!shellpath.exists()){
shellpath.mkdirs();
}
String documentName = "beifen.sh";
File shellFile = new File(shellpath,documentName);
if(!shellFile.exists()){
log.debug("创建文件结果:"+String.valueOf(shellFile.createNewFile()));
Runtime.getRuntime().exec("chmod 777 " + backPath + "backup.sh");
}
BufferedWriter bw = new BufferedWriter(new FileWriter(backPath + "backup.sh"));
/* String line = "mysqldump -h" + url + " --secure_auth=off -u" + username + " -p" + pwd + " " +
dbName + " > " + backPath + time + "nias.sql";*/
String line = "mysqldump -h" + url + " -u" + username + " -p" + pwd + " " +
dbName + " > " + backPath + time + "nias.sql";
String line2 = "mysqldump -h" + url + " --secure_auth=off -u" + "****" + " -p" + "*******" + " " +
dbName + " > " + backPath + time + "nias.sql";
log.debug("数据库备份命令是:" + line);
bw.write(line);
bw.close();
String cmdStr = "sh " + backPath + "backup.sh";
/*System.out.println("执行命令是:" + cmdStr);*/
log.debug(cmdStr);
Runtime.getRuntime().exec(cmdStr);
log.debug("=========================数据库备份成功!!===========================");
File file2 = new File(backPath + "backup.sh");
if(file2.exists()){
file2.delete();
}
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}