Mysql备份和还原数据库



# 备份现有数据库
mysqldump -h host -u user -p production_old > production_old.sql
# 方法二
mysqldump database_name -uroot -pyour_password >production_old.sql

# 还原至新数据库
mysql -h host -u user -p production_new < production_old.sql

          

备份数据库：
/usr/local/kkmail/service/mysql/bin/mysqldump -u kkmail -p --databases kkmail |gzip>20180309.sql.gz

还原数据库：
停止和数据库相关的服务、解压备份的sql数据库文件、还原数据库、最后开启这些服务：
for i in kkmail_app kkmail_nginx kkmail_apache kkmail_dovecot kkmail_redis kkmail_postfix;do /etc/init.d/$i stop;done
gunzip 20180309.sql.gz
/usr/local/kkmail/service/mysql/bin/mysql -ukkmail -p kkmail <20180309.sql
for i in kkmail_app kkmail_nginx kkmail_apache kkmail_dovecot kkmail_redis kkmail_postfix;do /etc/init.d/$i start;done
思考：备份的时候是否要锁表。


				
转载于:https://blog.51cto.com/net881004/2084578

1.备份数据库

备份数据库到本地服务器：

-- 备份数据库到本地服务器：
mysqldump -u root -pPassword123@mysql fit2cloud  >  fit2cloud20201208.sql

-- 备份数据库中的表：
mysqldump -u username -p123 database table1 table2 ...-> BackupName.sql

-- username:连接数据库用户名

-- -p123:-p表示后面输入的密码，123为数据库密码

-- database:替换为数据库名称，例如fit2cloud

-- table1：为备份的表名，如果是多张表，以空格分开


 

备份数据库到远程服务器:

mysqldump --u  root  -h 101.3.20.33 -pPassword123@mysql  -P3306 fit2cloud > 2020_10_23_fit2cloud_backup.sql

 

2.还原

还原使用mysqldump命令备份的数据库：

mysql -u root -p [dbname] < backup.sq

示例：

mysql -u root -p < /home/backup.sql

还原直接复制目录的备份

通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。对于MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。

public class TestDBOperate {


    /**
     * 备份数据库db
     * @param backPath
     * @param backName
     * @return code 0:备份成功 1：备份失败
     */
    @Test
    public static void DBBackUp(String root,String pwd,String backPath,String backName){
        int  code = 0;
        String pathSql = backPath+backName;
        try {
            File fileSql = new File(pathSql);
            if (!fileSql.exists()){
                fileSql.createNewFile();
            }
            //整库备份
            StringBuffer sb = new StringBuffer();
            sb.append("mysqldump");
            sb.append(" -h127.0.0.1");
            sb.append(" -u"+root);
            sb.append(" -p"+pwd);
            sb.append(" jxh >");//数据库名称
            sb.append(pathSql);
            System.out.println("===="+sb.toString());

            //部分表备份备份
            StringBuffer sb1 = new StringBuffer();
            sb1.append("mysqldump");
            sb1.append(" -h127.0.0.1");
            sb1.append(" -u"+root);
            sb1.append(" -p"+pwd);
            sb1.append(" jxh  job>");
            sb1.append(backPath+"jxh_job.sql");
            System.out.println("jxh_job ： "+sb1.toString());

            Runtime runtime = Runtime.getRuntime();
            String os = System.getProperty("os.name");
            System.out.println(os);
            if (os.toLowerCase().startsWith("win")) {//windows环境
                runtime.exec("cmd /c " + sb.toString());
                runtime.exec("cmd /c " + sb1.toString());
            } else {//linux环境
                String[] sh = new String[]{"/bin/sh", "-c", sb.toString()};
                runtime.exec(sh);//"/ -c " +
                String[] sh1 = new String[]{"/bin/sh", "-c", sb1.toString()};
                runtime.exec(sh1);
            }
            System.out.println("备份成功!");
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("数据库备份错误");
            code = 1;
        }
//        return code;
    }

    /**
     * 恢复数据库
     * @param root
     * @param pwd
     * @param filePath
     * @return code 0:备份成功 1：备份失败
     * mysql -hlocalhost -uroot -p123456 db < /home/back.sql
     */
    @Test
    public static void DBRestore(String root,String pwd,String filePath){
        int code = 0;
        StringBuilder sb = new StringBuilder();
        sb.append("mysql");
        sb.append(" -h127.0.0.1");
        sb.append(" -u"+root);
        sb.append(" -p"+pwd);
        sb.append(" jxh <");
        sb.append(filePath);
        System.out.println("命令为："+sb.toString());
        try {
            Runtime runtime = Runtime.getRuntime();
            System.out.println("开始还原数据");
            String os = System.getProperty("os.name");
            if(os.toLowerCase().startsWith("win")){
                runtime.exec("cmd /c "+sb.toString());
            }else{
                String[] sh = new String[]{"/bin/sh", "-c", sb.toString()};
                Process process = runtime.exec(sh);
                process.waitFor();
                filePath = filePath.substring(0,filePath.lastIndexOf("/")+1)+"nrm_recovery.sql";
                StringBuilder sb1 = new StringBuilder();
                sb1.append("mysql");
                sb1.append(" -h127.0.0.1");
                sb1.append(" -u"+root);
                sb1.append(" -p"+pwd);
                sb1.append(" jxh <");
                sb1.append(filePath);
                System.out.println(sb1.toString());
                String[] sh1 = new String[]{"/bin/sh", "-c", sb1.toString()};

                runtime.exec(sh1);
            }
            System.out.println("还原成功！");
        } catch (Exception e) {
            System.out.println("数据库还原失败");
            e.printStackTrace();
            code = 1;
        }
//        return code;

    }



    public static void main(String[] args) throws Exception {

        String backName = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss").format(new Date())+".sql";
        TestDBOperate.DBBackUp("root","123456","D:/lcc",backName);
//        TestDBOperate.DBRestore("root","123456","D://2020-06-12-18-23-29.sql");
//        String filePath = "D://2020/-06-12-18-23-29.sql";
//        filePath = filePath.substring(0,filePath.lastIndexOf("/")+1);
//        System.out.println(filePath);
    }
}


参考文章 ：

mysql_用命令行备份数据库   ： https://www.cnblogs.com/hellangels333/p/9059770.html 

https://www.cnblogs.com/kise-ryota/p/11291491.html

https://blog.csdn.net/qq_33661577/article/details/78203250