导入MySQL数据库到RDS [英] Import mysql database to RDS
问题描述
已经花费了12个小时，我尝试将sql文件导入RDS，我使用此命令:
It took already 12 hours, I try to import a sql file to RDS, I use this command :
mysqldump -u <user> -h <rds_server> -p <database> < filename.sql
但是没有任何效果，结果是我出现了这个错误
But nothing works and as result, I have this error
-- MySQL dump 10.13 Distrib 5.7.22, for Linux (i686)
--
-- Host: xxxxxxxxxx.us-east-2.rds.amazonaws.com Database: xxxxxxxxxx
-- ------------------------------------------------------
-- Server version 5.6.39-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('informagenie')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME': Lost connection to MySQL server during query (2013)
我正在等待您的帮助！
编辑19/05/2018
head -n 10 import_file.sql
完成
-- Database Manager 4.2.5 dump
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `wp_2_wpmm_subscribers`;
CREATE TABLE `wp_2_wpmm_subscribers` (
`id_subscriber` bigint(20) NOT NULL AUTO_INCREMENT,
推荐答案
我认为，您在命令中执行的操作根本上是错误的，或者甚至根本无法连接到RDS，这就是导入无法正常工作的原因.我在这里指出的步骤也将帮助您调试与connection failure或no connection at all相关的问题.
I think, you are doing something fundamentally wrong in your command, or you are not able to even connect to RDS at all, that's why your import is not working. I'm here by stating the steps that will also help you in debugging the problem related to connection failure or no connection at all.
- 尝试导入下面提供的简单文件.它是可选步骤，但我建议您这样做.
将以下sql语句保存到名称为 red_boy.sql
Save below sql statements to file with name as red_boy.sql
Create database test_red;
use test_red;
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50)
);
INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Red', 'Boy', 'red@example.com'),('Red1', 'Boy', 'red1@example.com'),('Red2', 'Boy', 'red3@example.com'),('Red4', 'Boy', 'red4@example.com');
-
执行以下命令.
Execute following command.
mysql -u <username> -p<password> -h <rds-name.aws-region.rds.amazonaws.com> < red_boy.sql
/**Replace <username> <password> and <rds-name.aws-region.rds.amazonaws.com> with full RDS URL.
如果成功执行了上述两个步骤，那么与RDS的连接/权限没有问题，您可以继续进行下去.
If above two steps executed successfully, then there are no issue with your connection/permission with RDS, you are good to proceed further.
mysql -u <username> -p<password> -h <rds-name.aws-region.rds.amazonaws.com> < filename.sql
以上所有步骤均在我的RDS上经过了完全验证，步骤3除外，并且可以正常工作.因此，如果对您不起作用，则下一步是查看文件'filename.sql'，看其中有什么问题.我可以尝试重新尝试答案.
All the above steps are fully verified on my RDS except step 3, and it works. Hence if doesn't work for you, then next step is to look into your files 'filename.sql' to see what is wrong in it. I could try re-attempt the answer.
在24/05/2018上进行编辑 根据@Doms注释，步骤3仍然失败，我看到一个或多个表或连接失败的问题，因为数据量可能很大.
EDIT on 24/05/2018 Based on @Doms comment, that step 3 is still failing, I see problem with one or multiple tables or over connection failure as size of data may be huge.
将以下内容保存在sh文件之一中并执行它.它将从源数据库中一张一张地导出表，并将其导入到目标数据库中.
Save following in one of sh files and execute it. It will do one by one table export from source database and import it into target database.
mysql -h source-database -u source_user -pSource_user_Password source_database --skip-column-names --execute='SHOW TABLES;' > tables.tmp
echo "Start!"
while read p; do
echo "Exporting" $p
mysqldump -h source-database -u source_user -pSource_user_Password source_database $p > $p.sql
echo "Importing" $p
mysql -h target-database -u Target_db_user -pTarget_db_password target_db_name < $p.sql
rm $p.sql
done <tables.tmp
rm tables.tmp
这篇关于导入MySQL数据库到RDS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
