Restore multiple tables in multiple databases from sql file with multiple tables in multiple databases

[nichemtktg]

I created a sql file that contains three databases, with multiple tables,with mysqldump, called: theBs_170410.sql.

Then I backed up the tables in each database as a separate sql file with mysqldump (I know I can get them back with these files).

Then I dropped those three databases to simulate some disaster.

Then I created the three databases I just dropped without the tables.

Then I tried to restore the tables, in each database, with mysql.exe targeting each db by name, but no joy.

Here’s my code:

shell_exec("/wamp/bin/mysql/mysql5.6.17/bin/mysql.exe -h localhost -u root branding,buckets,business_crrt   < e:/num6_bu/sql/theBs_170410.sql")

Is this even possible?

If so, How?

Thanks Always,

Niche

[Lemon_Juice]

I don’t think it’s possible. You can’t specify multiple databases - how would mysql know which query to send to which database?

If you backup multiple databases to a single file then mysqldump writes a USE db_name statement before data from each database. Then you restore the file without specifying any database and mysql will read the USE statements to determine which database to send the queries to. I believe if you specify a database in mysql.exe it will have no effect in this case because the choice will be overridden by the USE statements in the file.

Alternatively, you can backup each database to a separate file without the USE statement - in that case you must specify the db name in mysql.exe.

[nichemtktg]

Are you saying that a multiple database dump can’t be loaded?

If not, what key words would load it?

[Lemon_Juice]

Of course, they can, I explained how in my previous post. You don’t need any keywords in mysql.exe but you need USE statements in you sql dumps so you need to export the databases with those statements. When you add --databases option to mysqldump the file will contain USE statements and you are good to go.

[nichemtktg]

Never used USE statements. Thanks for the tip.