mysqldump generates logical backups of MySQL/MariaDB databases.
A logical backup is a set of SQL statements that can be used to restore the data, such as CREATE DATABASE, CREATE TABLE and INSERT. The other type of backup is a physical backup which will not be discussed in this short tutorial.
mysqldump needs a user and a password to connect to the database. Most of the time you will want to run mysqldump in scheduled cron jobs where the (unencrypted) password is visible so you should make sure the MySQL user only has the minimum required privileges.
The next MySQL commands will create a MySQL user that has access to all local databases and the access is restricted to SELECT and LOCK TABLES privileges:
CREATE USER 'mrSQLBackup'@'localhost' IDENTIFIED BY 'passwordToChange';
GRANT SELECT, LOCK TABLES ON *.* TO 'mrSQLBackup'@'localhost';
FLUSH PRIVILEGES;
Now let’s backup the mydb database using the user mrSQLBackup and store the backup in: /home/backups/mydbBackup.sql (change these values to fit your needs):
mysqldump -u mrSQLBackup --password=passwordToChange --no-tablespaces mydb > /home/backups/mydbBackup.sqlIf the MySQL user does not have enough privileges, mysqldump will show the error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’. Other privileges may be required depending on your database. The SELECT and LOCK TABLES privileges should be enough for most simple databases (e.g. WordPress). If you want to skip the no-tablespaces option, you need to also grant the PROCESS privilege.
You can backup several databases at once using the –databases or –all-databases options:
mysqldump -u mrSQLBackup --password=passwordToChange --no-tablespaces --databases mydb myotherdb > /home/backups/someDatabasesBackup.sql
mysqldump -u mrSQLBackup --password=passwordToChange --no-tablespaces --all-databases > /home/backups/alMyDatabasesBackup.sql
After creating the backup, let’s restore it:
mysql -u root -p mydb < /mydbBackup.sql
The above command uses the root user to import the data. You can use any other user that has enough privileges instead of root.