How to backup wordpress database automaticly everyday

Dumping wordpress database

Using following codes to dump your wordpress database and replacing bold words according to your environment.

mysqldump -uuser_name -ppassword wordpress_database_name > ./wordpress.sql;

You can find wordpress.sql in current directory, if no error occurs.

Dumping automaticly every day

Writing a script to automaticlly backup.

vi ~/.local/bin/mysql_backup.sh

Adding following codes.

#!/bin/bash
echo "starting to dumpwordpress.sql..."
dt=`date "+%y-%m-%d"`
mysqldump -uuser_name -ppassword wordpress_database_name > /directory_to_save_database/wordpress.sql_$dt;
echo "done! "

Adding excuting permition.

chmod +x ~/.local/bin/mysql_backup.sh

Configuring task scheduler.

crontab -e

Adding following codes.

0 2 * * * /absoultely_path/mysql_backup.sh

Codes above mean mysql_backup.sh will be excuted at 2:00 am everyday.

Backup to remote host(optional)

This method could be more safe.

Just add following scripts to remote host’s task scheduler.

Backup database

#!/bin/bash
echo "starting to dump wordpress.sql..."
ssh host_usrname:host_password@your_host_ip "mysqldump -udb_userbame -pdb_password wordpress > ./wordpress.sql;"
echo "starting copy wordpress.sql back..."
dt=`date "+%y-%m-%d"`
scp host_usrname:host_password@your_host_ip:~/wordpress.sql /directory_to_save_database/wordpress.sql_$dt
echo "done! "

Backup media datas

#!/bin/bash
echo "starting to backup wordpress media datas..."
rsync -r host_usrname:host_password@your_host_ip:/path_to_wordpress_root_dir/wp-content/uploads /directory_to_save_wordpress_media_datas/
echo "done! "

Restore MySQL from dumped file

mysql -udb_userbame -pdb_password wordpress < wordpress.sql