Export MySQL Or MariaDB Database

Taking database backup on a regular basis is one of the crucial steps after making the project live. The backup frequency could be either once in a day or week. The daily backup is preferred for the applications in which database changes are more frequent. In the case of high-availability databases, appropriate cluster setup must be considered instead of vertical scaling. This tutorial explains the usage of the mysqldump command to take the database backup. The same command can be automated to take scheduled backups.

Notes: This tutorial only covers the most common options used with the mysqldump command. You may also refer to the official documentation to explore all the possible options. The same commands can be used to take the backup of both MySQL and MariaDB database servers.

Local Backup

Backup - This section explains taking local backup in a file by exporting either single or multiple databases.

Permissions - mysqldump requires below listed privileges to export the database. Appropriate privileges must be assigned to the user account used to export the database.

1. SELECT - to dump tables
2. SHOW VIEW - to dump views
3. TRIGGER - to dump triggers
4. LOCK TABLES - in case --single-transaction option is not used
    

Command - Below mentioned is the usage of mysqldump command to take local backup for both Windows and Linux based systems including Ubuntu. The triple dots means multiple values can be provided.

# Regular command - Usage - Windows, Linux
mysqldump [options] db_name [tbl_name ...] > <file path>
mysqldump [options] --databases db_name ... > <file path>
mysqldump [options] --all-databases > <file path>

The examples to use the regular command are as mentioned below. These example commands are using the user enterprise to take backups of the database enterprise. There is no need to specify user and password in case the user account is using auth_socket algorithm to login to the MySQL server. The system user can directly access the database. You may refer How To Use Native Password With MySQL 5.7 and How To Install MySQL 8 on Ubuntu to learn more about password algorithms.

Notes: In case the user's password is not assigned, there is no need to use the option—p.

Below mentioned are the examples to back up a single database with username, host, and port. The password must not be provided with these commands for security purpose. You may specify the password for mysqldump in the config file which is considered as a secure option for cron jobs. Another option is to use the cat command to use an encrypted password(similar to how Plesk is using) as shown in the examples. The system will prompt for password in case it's not provided within the command. These commands can be used to either export the entire database or single or multiple tables.

# All tables - with username, without password - prompts for password
mysqldump -u enterprise -p enterprise > enterprise.sql 

# All tables - with username, with password - insecure
mysqldump -u enterprise -p'mypwd' enterprise > enterprise.sql 

# All tables - with username, with password - Plesk way
mysqldump -u enterprise -p`cat /etc/psa/.psa.shadow` dbname > dbname.sql

# All tables - with host, with port, with username, without password - prompts for password
mysqldump -h localhost -p 3306 -u enterprise -p enterprise > enterprise.sql

# Single table(user) - with username, without password - prompts for password
mysqldump -u enterprise -p enterprise [user] > enterprise.sql 

# Multiple tables(user, profile) - with username, without password - prompts for password
mysqldump -u enterprise -p enterprise user profile > enterprise.sql 
 

Below mentioned are the examples to back up multiple databases with username, host, and port.

# Multiple databases - with username, without password - prompts for password
mysqldump -u enterprise -p --databases enterprise vblog mshop > mydbs.sql 

# Multiple databases - with username, with password
mysqldump -u enterprise -p'mypwd' --databases enterprise vblog mshop > mydbs.sql 

Below mentioned is the example to back up all the databases with username, host, and port.

# All databases - with username, without password - prompts for password
mysqldump -u enterprise -p --all-databases > mydbs.sql 

# All databases - with username, with password
mysqldump -u enterprise -p'mypwd' --all-databases > mydbs.sql

The PowerShell users on Windows will need different command as mentioned below.

# PowerShell on Windows
shell> mysqldump [options] db_name [tbl_name ...] --result-file=<file path>
shell> mysqldump [options] --databases db_name ... --result-file=<file path>
shell> mysqldump [options] --all-databases --result-file=<file path>

# Example
shell> mysqldump -u enterprise -p enterprise --result-file=mydump.sql

The above-mentioned commands will export the selected database and tables to the file location specified in the command.

Database Backup

This section explains taking the database backup to another database used for backup purpose using the Shell Pipes. We can either export the database to the local file and then use this file to import to another database or directly export to another database using shell pipes.

The below-mentioned command can be used to export the database directly to another database. Also, make sure that you have access to both the servers before executing the command.

# Export database to another database - Same server
mysqldump -u enterprise -p enterprise | mysql enterprise_backup 

# Export database to another database - Different server
mysqldump -u enterprise -p enterprise | mysql -h host.example.com -p 3306 enterprise

This is how we can export or backup a database.