1

I have mysql installed on my web server, which I am accessing using PHPMyAdmin. My client asked me following:

  1. Install PhpBB
  2. Setup MYSQL Scheduled backup

I have installed PhpBB successfully but hwo to do the 2nd one. I am using PhpMyAdmin to access mysql database, and I don't see any scheduling type of facility in phpmyadmin as well as in phpbb.

What should I do here? Please help me, Thanks!

6 Answers 6

4

Automation can be done by creating CRON jobs on your server. The following link has complete example code to backup the database automatically

http://atoztechnical.blogspot.com/2010/08/how-to-automate-database-backups.html

or

First write a script that uses mysqldump command to backup your database and store in specific directory.Now we are going to automate the process by executing this script at a particular time everyday.

Execute the following command as root user

[root@vasu /]# crontab -e

This allows you to edit the crontab file for a user. It has six fields they are

1.Minute

2.Hour

3.Day of Month

4.Month

5.Day of Week

6.Command to be executed

Add the following line to the crontab file and close it.

[root@vasu /]# crontab -e
39  19  *  *  *  backup.sh 
~    
~    
~    
~    
~    
ESC:x
crontab: installing new crontab
[root@vasu /]#

Now your backup.sh script will be executed 39 minutes, 19th hour everyday. If u want to see the list of cronjobs for a user, then execute

[root@vasu /]# crontab -l
39 19 * * * backup.sh

backup.sh :

mysqldump -h hostname -u userbane -ppassword dbname > path/to/directory/backupname.sql 
1
  • where would we store backup.sh file??
    – Sobhan
    Oct 5, 2020 at 10:24
3

this is good links to start :

10 Ways to Automatically & Manually Backup MySQL Database http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

How to setup and verify a backup solution for MySQL in 15 minutes - all using open source software http://www.zmanda.com/quick-mysql-backup.html

0
3

You might want to read the MySQL documentation on Database Backup Methods. PHPMyAdmin has an "Export" option you can use to backup your database, but there is no way to automate this. Automating a backup of MySQL usually requires creating a CRON Job that calls mysqldump. There are other ways to do it, such as with a file system snapshot, but using mysqldump will probably easier, and just as good for a PHPBB database. Also, if you're using shared hosting, you most likely will not be able to create a file system snapshot, making it a non-option. Creating a CRON Job can either be done by accessing your server over SSH, or some hosting providers provide an web interface for creating CRON Jobs

Also, you might want to check with your hosting provider. My hosting provider (dreamhost) automatically takes a backup every day, and stores the last 5 days of backups. They also provide simple options for downloading your backups, and for restoring one in case you need to.

Just a note, it's important that you regularly test that your backups to ensure they can actually be restored.

0

As a workaround you can use MySQLBackupFTP https://mysqlbackupftp.com It allows you to connect to your phpMyAdmin and set a backup schedule to backup your MySQL Server databases

0

One of the easiest and most common ways to schedule backups in Linux is using Cron jobs. Cron is a time-based job scheduler in Linux that allows you to schedule commands or scripts to run automatically at specific intervals. To schedule a backup using Cron, you need to create a backup script that includes the MySQL dump command to export all databases and save them to a file, and then add a Cron job to run the script at the desired interval. Additionally, you can add a shell script to upload the backup files to an AWS S3 bucket. By using both Cron jobs and shell scripts, you can automate the backup process and store the backups securely in the cloud.

This article shows how to schedule MySQL backups using cron jobs & simple shell scrpt.

0

An easy way to schedule a MySQL base is to install automysqlbackup (available among Debian packages for example). The backup are in /var/lib/automysqlbackup directory in a SQL format.

This script backups each base in /var/lib/automysqlbackup/ daily, weekly and monthly, and manage the retention.

Note that the privileges and passwords of a base are not saved with the base.

The following script can be handy to saves grants/password of the databases:

  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .