We can backup our databases using different methods, Here is the shell script used for automatic backup MySQL databases using mysqldump in your cpanel server. The following instruction can be used to backup any database. The only requirement is that you are using a Linux server to host your files and mysql user privileges. Use this Database Automatic backup Shell script  via cron job.

Database Auto Backup Solution 1

Login your cPanel – > Click Cron Job icon

mysqldump –opt -Q -u dbusername –password=dbpassword dbname | gzip > /backup/db_backup.sql.gz

Replace dbusername with the database user
Replace dbpassword with the database user password
Replace dbname with the database that you are backing up
Replace the /backup folder to the file path in your server where you want to save the backup

Database Auto Backup Solution 2

AutoMySQLBackup with a basic configuration will create Daily, Weekly and Monthly backups of one or more of your MySQL databases from one or more of your MySQL servers.

Other Features include:
– Email notification of backups
– Backup Compression and Encryption
– Configurable backup rotation
– Incremental database backups
Please make sure you haven’t any corrupted database tables. Because it will not backup properly when you run this script.

How to install Database Automatic backup Shell script

Download automatica mysql database backup script

#cd /usr/local/src/

#wget
http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz

#tar -zxvf automysqlbackup-v3.0_rc6.tar.gz

Run the install.sh script.

#./install.sh

Edit the /etc/automysqlbackup/myserver.conf file and remove # to update database user and passwor settings, Backup directory location.

vi /etc/automysqlbackup/automysqlbackup.conf

# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username='root'
# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password='password'

# Backup directory location e.g /backups
CONFIG_backup_dir='/var/backup/db'

Run backup script via command line simply by running the following command.

#automysqlbackup /etc/automysqlbackup/automysqlbackup.conf

Done!! You can check DB backup folder.

Automysqlbackup can be run a number of ways, you can choose which is best for you.

1. Create a script as below called runmysqlbackup using the lines below:

vi runmysqlbackup.sh

#!/bin/sh

/usr/local/bin/automysqlbackup /etc/automysqlbackup/automysqlbackup.conf
chown root.root /backup/db* -R
find /backup/db* -type f -exec chmod 400 {} \;
find /backup/db* -type d -exec chmod 700 {} \;

2. Save it to a suitable location or copy it to your /etc/cron.daily folder.

3. Make it executable, i.e. chmod +x /etc/cron.daily/runmysqlbackup.