MySQL Databases: A Comprehensive Guide to Backup, Secure, and Restore

MySQL databases are among the most widely used relational database management systems (RDBMS). Their popularity stems from their ease of installation, robustness, and compatibility with modern web technologies.

In this article, we present a comprehensive guide to:

• Backing up your databases,
• Securing your data,
• Restoring your databases in case of issues.

How to Back Up Your MySQL Databases

Back Up a Single Database
Use the following command to back up a database to an SQL dump file:

mysqldump -u <username> -p <database_name> > dump.sql

Back Up Multiple Databases
To back up multiple databases, add their names to the command:

mysqldump -u <username> -p --databases <db1> <db2> > dump.sql

Back Up All Databases
If you want to back up all the databases on your MySQL server:

mysqldump -u root -p --all-databases > all_databases.sql

Common Options to Optimize Your Backups

Some options can help tailor your backups to your specific needs:

• --triggers: Includes triggers in the dump.
• --single-transaction: Avoids table locking during the backup. Ideal for large databases.
• --routines: Includes stored procedures and functions in the dump.

Restoring Your MySQL Databases

In case of data loss or corruption, here’s how to restore your databases from a dump file.

Reinstall MySQL (Optional)

If your MySQL server has been removed or corrupted, reinstall it using:

apt install mysql-server

Recreate Your Users and Privileges

After reinstalling MySQL, recreate your databases and users:

1. Open the MySQL terminal

mysql

2. Create a database

CREATE DATABASE `my_database`;

3. Create a new user

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

4. Grant privileges
   For all databases:

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';

For a specific database:

GRANT ALL PRIVILEGES ON <database_name>.* TO 'new_user'@'localhost';

5. Apply changes

FLUSH PRIVILEGES;

Restore a Database

Use the following command to restore a database from a dump file:

mysql -u <user> -p <database_name> < dump.sql

MySQL Cheatsheet: Useful Commands for Database Management

Show a user’s privileges:

SHOW GRANTS FOR 'username'@'localhost';

List MySQL users:

SELECT user, host FROM mysql.user;

List your databases:

SHOW DATABASES;

Display connected users:

SELECT current_user();

Revoke user privileges:

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

Delete a user:

DROP USER 'username'@'localhost';