I have a few Linux servers set up with a few databases running on them. It’s best practice to have databases backed up to separate backup files, and these backups should be uploaded from the server to another location.
The database technology I use is MySQL and there is a CLI tool available that can be used called mysqldump.
Try the command to verify that it’s available.
mysqldump
If it’s not available then install the MySQL server application using your system’s package manager.
sudo apt install mysql-server
If mysqldump is installed then you should get the following output.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
Create a data dump file
Use the below command to create a backup file from a MySQL database.
Replace [DATABASE-NAME] and [FILENAME] with the actual values.
mysqldump [DATABASE-NAME] > [FILENAME].sql
To import an existing dump file to a database use the mysql command.
mysql [DATABASE-NAME] < [FILENAME].sql
Typically a MySQL user is required to access the database so use the following to add in the user account details.
You will get prompted to enter the password as it shouldn’t be included in the command.
mysqldump -u [DATABASE-USER] -p [DATABASE-NAME] > [FILENAME].sql
To use a specific host and/or port use the following extra parameters.
mysqldump -h [HOST] -P [PORT] -u [DATABASE-USER] -p [DATABASE-NAME] > [FILENAME].sql