MysqlDump导出导入操作步骤

MysqlDump导出导入操作步骤

导出数据

1. 导出备份文件

   $ mysqldump -h ${source_host} -u ${username} -p \
   --set-gtid-purged=OFF -c ${source_dbname} ${source_tablename} \
   --where="${where_conditions}" > ${path_filename}.dump
   

2. 修改备份文件

   sed -i '/^INSERT INTO `${source_tablename}`/INSERT INTO `${destination_dbname}.${destination_tablename}`/' ${path_filename}.dump
   sed -i '/^INSERT INTO/INSERT IGNORE INTO/' ${path_filename}.dump
   

   IGNORE : 忽略插入时主键冲突记录错误

3. 导入备份文件

   mysql -h ${destination_host} -u ${username} -p  ${destination_dbname} < ${path_filename}.dump
   

数据备份导出导入

1. 导出数据备份

   mysqldump -h ${source_host} -u ${username} -p --set-gtid-purged=OFF  ${source_dbname} $(mysql -h ${source_host} -u ${username} -p -D ${source_dbname} -Bse "SHOW TABLES LIKE 'some_partner'")
   

2. 修改数据备份

   修改DML头部语句，示例如下：

   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `source_tablename` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
   USE `source_tablename`;
   

   修改为

   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `destination_dbname` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
   USE `destination_dbname`;
   

3. 导入数据备份

   mysql -h ${destination_host} -u ${username} -p  ${destination_dbname} < ${path_filename}.dump