Back up MySQL server

backupMySQL

I want to backup all databases, users and etc on our MySQL sever.

I have come across the command mysqldump and believe I can use it like this? (Trying to backup a REMOTE server to our LOCAL machine)

mysqldump -h web-server.com -u root -p root --all-databases > "/backups/mysql/`date +%Y%m%d`.sql"

However:

  1. I'm scared to run this with little knowledge of what's happening (we have a lot of data on the MySQL database and we can't afford to lose it!). So first question is: Will this do what I want? Log into the remote server (located at web-server.com for example) and backup all the mysql databases to the path /backups/mysql on the LOCAL machine?

  2. I'm not convinced that this will also keep all the user information for each database intact in the event of a MySQL server failure. Is user information just stored as another database in the MySQL databases? Or is it a function of the server itself? In this case is there a different way I can backup the database to include the user data too?

Best Answer

1) Yes, that will dump the contents of all databases as a series of INSERT sql commands so you can restore the whole lot with mysql < backup.sql. Purely for wieldability's sake, I'd recommend you do this per database rather than using --all-databases as you get one honking big file containing EVERYTHING and it's not easily searchable.

You may want to look at the mysqldump manual page to see if there are any other options you'd like to use.

2) User information and access control is stored in the mysql database; --all-databases does include that one.

Related Question