Mariadb – Multiple DB restore in sequence

amazon-rdsmariadbrestore

I am restoring several MariaDB databases into a Amazon AWS RDS MariaDB instance from an AWS EC2 machine in the same VPC / region etc.

I am using a shell script like so:

mysql -u root -h hostname.amazonaws.com -ppassword < db1.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db2.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db3.sql
...

The sequence is important since the later databases have VIEWS and JOINS that reference earlier databases. I have figured that part out and doing the DBs one at a time manually works as expected.

The issue is, when I chain the DBs in a single shell script, the later restore for, say DB2, complains that some table in DB1 does not exist. The exact error message is below:

ERROR 1146 (42S02) at line 482: Table 'db1.table_name' doesn't exist

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

Is there any way I can get the earlier SQL files to block till it completes successfully or fail and break subsequent SQL files from executing?

Client and server info below:

mysql client  Ver 15.1 Distrib 10.0.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Server version: 10.0.31-MariaDB MariaDB Server

Best Answer

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

That is not the problem.

The problem is, mysqldump only takes care of this, automatically, if you use it as intended:

mysqldump [options] --databases db1 db2 db3

Otherwise, it essentially assumes any dependencies will already have been met on the server when you restore a dump file, so it does nothing to resolve them except within the individual db being restored.

If you create a single dump file, like this, specifying all the databases you need but using the --no-data option, you'll get a small file with all your tables and views, but no data. Restore that first, and then your 3 individual files.

Related Question