Import mysql database to RDS

Question

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)

I'm waiting for your help !

Edit 19/05/2018

head -n 10 import_file.sql

done

-- 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,

Answer 1

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.

1. Try importing a simple file that I'm providing below. Its optional step, but I would recommend you do it.

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', '[email protected]'),('Red1', 'Boy', '[email protected]'),('Red2', 'Boy', '[email protected]'),('Red4', 'Boy', '[email protected]');

2. 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.
   

3. 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
   

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.

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.

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