Copy database

mikerh9

I have two databases PHOTO and TEST.
So when I do something I switch to TEST database. The TEST databse is outdated. So I want to make copy of PHOTO database and call it TEST.

How that's can be done?

Thanks

AstroTeg

What database are you running?

mikerh9

mysql 3.23.58

AstroTeg

Depends on what you have available. The phpmyadmin tool works great on exporting the data and structure into SQL. It would be just a matter of replaying the SQL back, but instead rename the database name in the SQL.

Another approach I've done on Windows is just copy the MySQL database directory to another directory. This works well when you have some monster sized database.

I would bet there's a MySQL command you could issue, but I haven't looked into it...

Drakla

If you want to do it via a script, if you've got shell access you could use backticks to do a mysqldump, then use mysql to load it into the other table. Do something like

/path/to/mysql/bin/mysqldump --add-drop-table -e -u username -h host -ppassword PHOTO > dump.sql

Then follow that with

/path/to/mysql/bin/mysql -u user -ppassword -h host TEST < dump.sql

And delete the dump file.

mysql does have table backup and restore commands, but make sure you drop a table before you restore it.

Or you could write your own routine.

mikerh9

Originally posted by Drakla
If you want to do it via a script, if you've got shell access you could use backticks to do a mysqldump, then use mysql to load it into the other table. Do something like

/path/to/mysql/bin/mysqldump --add-drop-table -e -u username -h host -ppassword PHOTO > dump.sql

Then follow that with

/path/to/mysql/bin/mysql -u user -ppassword -h host TEST < dump.sql

And delete the dump file.

mysql does have table backup and restore commands, but make sure you drop a table before you restore it.

Or you could write your own routine.

Thanks it worked for me.

mikerh9

Originally posted by Drakla
If you want to do it via a script, if you've got shell access you could use backticks to do a mysqldump, then use mysql to load it into the other table. Do something like

/path/to/mysql/bin/mysqldump --add-drop-table -e -u username -h host -ppassword PHOTO > dump.sql

Then follow that with

/path/to/mysql/bin/mysql -u user -ppassword -h host TEST < dump.sql

And delete the dump file.

mysql does have table backup and restore commands, but make sure you drop a table before you restore it.

Or you could write your own routine.

Why it is necessary to delete dump file?

Thanks