When you have a VPS or dedicated server to manage, typically you’d have a bunch of different mysql users granted the privileges of every particular database for the sake of security. While this works well in segregating the privileges and preventing hackers from gaining access to all databases upon the compromise of only one mysql user account, it means much more work when you are backing up all those databases on the server.
The solution is to use the mysqldump command as root. To backup all databases on the mysql server no matter which users they belong to, try the following via SSH:
mysqldump -u root -p --all-databases > all.sqlIt’d then prompt you for root password. After successful authentication, the mysql server will start dumping all databases into a text SQL file: all.sql. After it’s done, you can compress (“tar zcf all.tar.gz all.sql”) and put all.sql somewhere safe for backup.
To restore all.sql into the entire MySQL:
mysql -u root -p < all.sqlCan’t be any simpler.