I run this family genealogy website using open source software called webtrees.

Webtrees recommends using MYSQL as its database.

I’ve spent so much time putting together the website that having to start all over again would be a total disaster.

Part of my backup routine is to backup the database every night. This is how I do it.

The assumption is that you’ve already setup a database with a user and password.

  • Database name: mysql-database-name
  • Database user: mysql-user-name
  • Database user password: mysql-user-password

Switch linux user’s home directory

cd /home/user

Create a file called *.my.cnf in the user’s home directory.*

sudo vim .my.cnf

Then type the following:

[client]

user = mysql-user-name

password = mysql-user-password

The benefit to creating .my.cnf is you don’t have to pass the user-name and user-password when executing a MYSQL command. Of course, there are security risks by using this method. But, since I’m the only one who has access to my server, I’m not that concerned unless my imaginary twin evil brother starts acting up again.

Since .my.cnf is a hidden file, you have to the following command to show it in the directory.

ls -a

To do a manual mysql backup, here’s the command:

mysqldump database-name > /directory of backup/mysql-backup-filename.sql

The code is run at the general linux command line. You do NOT run it within the MYSQL command line client.

Remember, this command only works if you’ve already created the .my.cnf. with the credentials.

Also, by creating the .my.cnf file, you get to the MYSQL command line by simply typing mysql at the linux command line prompt.

If there’s a LOCK TABLES ERROR when mysqldump is run, you need to add --single-transaction to mysqldump.

mysqldump --single-transaction database-name > /directory of backup/mysql-backup-filename.sql

Onto Webtrees.

Webtrees usually gets installed with its data folder in this location:

/var/www/html/webtrees/data

To backup webtrees, you have to backup the data folder and database.

So, I backup the database to the webtrees data folder. Then backup the entire webtrees data folder using duplicity. You can read how I use duplicity here.

The code to manually backup the database is:

mysqldump database name > /var/www/html/webtrees/data/mysql-backup-filename.sql

I’m a pretty lazy person, so I automated the process by creating a script and running it in cron. Of course, you can use whatever location to backup the database.

To automate:

  1. Switch to the root user

    sudo su

  2. Make a directory to store the jobs

    mkdir /home/username/cronjobs

  3. Change to the directory you just created

    cd /home/username/cronjobs

  4. Make the backup file that’ll hold the script

    vim backup-mysql.sh

  5. Add the following two lines to the backup script:

    #!/bin/bash

    mysqldump database-name > /var/www/html/webtrees/data/mysql-backup-filename.sql

I then added this as a cronjob as follows:

$ crontab -e

This will open the crontab editor/scheduler.

Add the backup script that you saved to the scheduler by typing:

5 1 * * * sh /home/username/cronjobs/backup-mysql.sh

Then save and close.

This code performs a daily backup at 1:05 A.M. The date/time that’s used is the time that’s on the server. This is likely different than the current time where you live. To see the time on the server, the command is:

date

You can go to CronTab Guru to figure out the schedule expression you want.

By the way, I wrote this so in six months when I need to do it again on another server, I’d have a reference.

To see what cronjobs are scheduled:

crontab -l

To make sure that cron will run in the background:

sudo systemctl enable cron

Documentation is Everything.

Visit Emlekezik.com