MySQL backups are mostly created as .sql dump files. These are flexible and can be imported to another MySQL instance or transferred$ mysql -N -e ‘show databases’ | while read dbname; do mysqldump –complete-insert –routines –triggers –single-transaction “$dbname” > /var/backups/databases/”$dbname”.sql; done around for safe keeping.

The guide below will help you make backups of mysql

Using phpMyAdmin

If your server is running phpMyAdmin, you can use it to create backups as follows

1.Login to phpMyAdmin

2.In the left column, click the name of the database you wish to backup.

3.Click on the Export tab on the right.

4.Choose the tables you wish to backup, or click Select All to backup all of your tables.

5.Scroll down and ensure the Save as File box is checked off. Click the Go button found in the lower right corner. You will be prompted to find a place on your hard drive to store the backup file, and then the download will begin.

Using SSH

You can run mysqldump command to backup your database.

1.Login to the server via SSH as root user

2.Create a folder you want to save your backup files. In this case, we will call this folder /var/backups/databases

To create a backup for a single database, run this command

$ mysqldump database_name > database_name.sql

To create a backup of several databases, with each database having its own mysql file, run the command below

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/backups/databases/"$dbname".sql; done

You will see a list of .sql files created and saved on the specified folder.

Note: The commands above need to be run as root user. Otherwise, you would need to specify database usernames and passwords on the commands.

If you still get asked for a password, use the commands below and type in a password when prompted.

For single database backup:

$ mysqldump -uroot -p database_name > database_name.sql

For all databases, use this command

$ mysql -uroot -pPASS_HERE -N -e 'show databases' | while read dbname; do mysqldump -uroot -pPASS_HERE --complete-insert --routines --triggers --single-transaction "$dbname" > /var/backups/databases/"$dbname".sql; done

Replace PASS_HERE with your actual mysql password. This prevents MySQL prompting you for passwords since if databases are many, you will have to enter the passwords very many times which is not practical.