Backup your database using mysqldump

[Andy]

Create backup.sql using mysqldump

1) Create a directory called 'mysql_backups' using cPanel and File Manager. This will be located in the following folder:

/home/path/mysql_backups

2) Log into your web site terminal. This is very easy to do on a macOS because all you have to do is go to the Terminal and enter the following:

ssh 123.123.123.123 -l root

The number is the IP address of your server.

3) Now enter the following command to create a backup.sql file, this will be the backup of your database:

mysqldump --opt --no-tablespaces -uroot database_name > /home/path/mysql_backups/backup.sql

Note: if your database collation is uft8mb4 you will need to use this command:

mysqldump --opt --no-tablespaces --default-character-set=utf8mb4 -uroot database_name > /home/path/mysql_backups/backup.sql

 

[Andy]

Restore backup.sql into new database

1) Using cPanel create a new database called 'new_database' or any name you want.

2) Log into your web site terminal.

3) Enter the following command to restore the backup.sql to your new database.

mysql new_database < /home/path/mysql_backups/backup.sql

 

[Andy]

Setting up a Cron Job in cPanel

1) Go to the Cron Jobs in cPanel.

2) Create a new Cron Job with the following settings:

Command: mysqldump --opt --no-tablespaces --default-character-set=utf8mb4 -u'username' -p'password' database_name > /home/path/mysql_backups/backup.sql

3) I suggest creating one for each day of the week with the following:

backup_sunday.sql
backup_monday.sql
backup_tuesday.sql
etc...