Backup and restore a MySQL SERVER

[riahc3]

Hey

I want to make a backup and restore of a MySQL SERVER to another machine. Please note the SERVER part; I want to backup/restore datebases, tables, procedures, users, configuration, etc....EVERYTHING

What is the best way to do this? Thank you

[pritaeas]

You can use mysqldump to export everything to an sql script file. On the other server you can use mysql to import it again. Both are command line tools. Note that users are never exported, you will have to recreate these manually.

[smantscheff]

You can export the system database (users, rights etc.) with mysqldump mysql and import it again, but this may lead to all sort of trouble.

[riahc3]

Note that users are never exported, you will have to recreate these manually.

No way to do this? Third party tool?

[smantscheff]

What you can do:
Identify all tables in the database mysql which have a field called User. Export those tables with mysqldump. Feed them into the new database. Make sure that you run mysql with admin privileges (usually as user root). Restart the mysql server with the new database after the import. And hope for the best.

[sathish_nadu]

I use SQLyog for this where we can connect to the remote server and take a back up with this tool.

[Manish02]

i think SQLYOg is best option for him