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:
-
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? -
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.