restore multiple databases with command line

[jgentes]

On my site I do a weekly backup of our server and the mysql databases. The problem I am having is restoring / updating the backup server's mysql data. There are 2 databases that I need to restore / update on the new server, but only 1 sql file, and I havent been able to find a tutorial on how to restore both databases with the command line.

and I dont think this makes a difference, but the server is running on a Ubuntu Linux Distro.

[jgentes]

anyone have an idea of how to accomplish this?

[Atli]

Hi.

You have posted this in the MySQL Articles section. I have moved it over to the MySQL Forums where our members will find it.

Please try to avoid posting questions in the Articles section in the future.

Moderator

[jgentes]

can anyone help with this? please? thanks

I have looked all over and I cannot seem to find out how to do this

[Atli]

Hi.

How exactly are you creating your backups?

[jgentes]

How exactly are you creating your backups?

/usr/bin/mysqldump --all-databases --password=_pass_ > \_path_/file.sql
 

[mwasif]

You want to backup all the database in SQL file but update only 2 of them to the other server. Right? Is it not possible for you to make 2 backup sql files?

I can't see any method to restore only selective databases from the SQL file.

[jgentes]

there are only 2 databases to update. the reason I chose to dump them all is because its less code, and I figured there would be a way to import the whole sql file to the mirrored server since this isnt some miracle "hack" I devised to trick the system.

maybe it would be better just to dump each database separately I dont know :-s what do you guys think?

[mwasif]

the reason I chose to dump them all is because its less code

How many databases you have on the actual server that you are dumping? If you have only 2 databases, then you can use the single sql file to update both databases on the other server. Simply use the mysql command on the shell to update the database on the other server, it will update the other database as well.

mysql db_name < dumped.sql

Because sql file contains

USE db_name;

which use the appropriate database to update the data.

[jgentes]

okay I did that and now I am getting

ERROR 1217 (23000) at line 243: Cannot delete or update a parent row: a foreign key constraint fails
 

line 243: DROP TABLE IF EXISTS 'downloads';

I checked on the mysql dev site and I cannot seem to figure out how to solve the problem? maybe it's just my lack of expertise in this area?

Thanks for any help in advance.