Multiple DB restore in sequence

Question

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

Answer 1

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.

Answer 2

Thanks to all who provided inputs. I have got this to work for me by adding a delay of 60 seconds after the db1 restore by using the sleep command:

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

I chose 1 minute randomly, there is no real reasoning behind it.