Automated Daily Backup of the mySQL Database

Linux 8 Comments

Now that I have my tikiwiki running on a Linux AS4 server and having felt the sting of having to recover it, I have incentive to create an automated daily backup of the mySQL database. I have decided to do the backup using a simple shell script and schedule it using the Linux crontab.

Here is the final version of the shell script (saved to “/root/sql_backup.sh”). This script will do a mySQL dump of one database to a gzip archive file in a backup directory, keep only the 10 most recent archive files (pruning the rest), and rsync the backup directory to a remote server.

#!/bin/sh
# MySQL backup script - do a backup and rsync to remote server

# mySQL
SQLROOTPASS=mysqlrootpassword
SQLDB=mysqldatabasename

# Backup Dirs
LOCALDIR=/home/backup/mysql
REMOTEHOST=myremoteserver
REMOTEDIR=/home/backup/mysql
NUMBACKUPS=10

# Create local dir
mkdir -p $LOCALDIR

# Do a mySQL backup
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
echo "Doing a database backup to $LOCALDIR/$SQLDB-$NOW.sql.gz"
# Note: The following mysqldump command should be one line only!
mysqldump -u root -h localhost -p$SQLROOTPASS $SQLDB | gzip -9 > $LOCALDIR/$SQLDB-$NOW.sql.gz

# Prune old backups
FILES=$(ls -t $LOCALDIR/*.gz)
for FILE in $FILES
do
if [ $NUMBACKUPS -gt 0 ]
then
   echo Keeping $FILE
else
   echo Deleting $FILE
   rm $FILE
fi
NUMBACKUPS=$(expr $NUMBACKUPS - 1)
done

# Create remote dir
ssh $REMOTEHOST mkdir -p $REMOTEDIR

# Sync from local dir to remote dir
echo "RSyncing to $REMOTEHOST.."
rsync -rt --delete $LOCALDIR/ $REMOTEHOST:$REMOTEDIR/

Rather than explain the whole script, I’ll just point out some parts that may require more attention:

  • Unfortunately, this script does contain the mySQL root password in the clear. I haven’t figured out how to do a mysqldump as a non-root SQL user yet.
  • To avoid the need to include the remote server’s root password, this script will require that you have already established trust with the remote server. This avoids the need to input a password when using RSync, SSH, or SCP. See below for instructions on how to establish this trust.
  • When doing an assignment to a variable in the script, don’t insert spaces after the variable name. For example, “TEMPVAR=Hello” works but “TEMPVAR =Hello” won’t.
  • The format “TEMPVAR=$(…)” tells the script to execute the command inside and assign the result to the variable. This is equivalent to the format “TEMPVAR=`…`” using backward single quotes.
  • In order to configure RSync to delete files on the remote system (otherwise we’ll use up all the hard drive space), we must use the -r flag (recurse into subdirectories), –delete flag, and use a directory (“$LOCALDIR/” ending with a slash) as the source. The -t flag (persist file modification time) is necessary for RSync to do delta updates.

To establish trust with a remote server, do the following:

  1. Log into your server as the root user.
  2. Look for this file “/root/.ssh/id_rsa.pub”.
  3. If the file or directory doesn’t exist, then you will need to generate the RSA public/private key:
    ssh-keygen -t rsa

    Just take the default inputs and your RSA keys will be generated.

  4. Copy the public key “id_rsa.pub” to the remote server’s “/root/.ssh” directory.
    scp /root/.ssh/id_rsa.pub myremoteserver:/root/.ssh/my_id_rsa.pub

    If that directory does not exist, you may wish to generate the RSA public/private keys there also.

  5. You will need to add the contents of “my_id_rsa.pub” to the end of the remote server’s “/root/.ssh/authorized_keys” file (it contains keys from other servers which the remote server should trust). If the file doesn’t exist, then create it as an empty file.
    cp authorized_keys authorized_keys.original
    cat authorized_keys my_id_rsa.pub > authorized_keys
    rm my_id_rsa.pub
  6. You can test it out by making an SSH connection to the remote server.
    ssh root@myremoteserver

    If you are not prompted for the password, then the trust was successfully established.

Finally, let us schedule the mySQL backup to run every day at 3am using the Linux crontab:

crontab -e
0 3 * * * sh /root/sql_backup.sh > /tmp/sql_backup.output 2>&1

Some hints about using the crontab:

  • “crontab -e” will allow you to edit the crontab contents.
  • The crontab line format is:
    <minute=0-59> <hour=0-23> <day of month=1-31> <month=1-12> <day of week=0-6 (sun-sat)> <command>
  • Precede the crontab line with a # (pound) character to indicate a comment which crontab will ignore.
  • The “2>&1” will redirect STDERR to STDOUT, which in turn is redirected to “/tmp/sql_backup.output” above. (0=STDIN, 1=STDOUT, 2=STDERR)
  • “crontab -l” will list the crontab contents.
8 Comments

Restoring Tikiwiki 1.8.5 on Red Hat Linux AS4

Linux No Comments

redhatRecently, I had to rebuild a tikiwiki server (on a Linux machine) with only a mysql database backup image. I quickly found that the backup image of a tikiwiki does not contain the version of the tikiwiki software, which is very necessary; otherwise, the database schema won’t match. Fortunately, I managed to find that the tikiwiki version was 1.8.5 from some old notes.

Here is the resulting software stack after I finished.

  • Red Hat Enterprise Linux AS Release 4
  • mySQL Server 4.1.20
  • Apache HTTP Server 2.0.52
  • PHP 4.3.9
  • Tikiwiki 1.8.5 Polaris

Here’s how I went about restoring the tikiwiki (assuming that the machine already has Redhat AS4 installed):

  1. Install PHP, HTTP Daemon (httpd), and supporting libraries:
    yum list | grep -i php
    yum install php.i386
  2. Install mySQL server (mysqld) and supporting libraries:
    yum list | grep -i mysql
    yum install mysql-server.i386
  3. Install mySQL client and the PHP-mySQL libraries:
    yum list | grep -i php-mysql
    yum install php-mysql.i386
  4. Start both mysqld and httpd and configure them to start on reboot:
    chkconfig mysqld on
    service mysqld start
    chkconfig httpd on
    service httpd start
  5. Configure mysqld and setup database:
    (Set mySQL root password)
    mysqladmin -u root password 'mypassword'

    (Create a database)
    mysqladmin -u root -p create mydatabase

    (Login as mySQL root user)
    mysql -u root -p

    (Use main mySQL database)
    mysql> use mysql;

    (Give localhost permission to access all databases)
    mysql> insert into host(host, db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) values('localhost', '%', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

    (Create a user)
    mysql> insert into user (host, user, password) values('localhost', 'myuser', password('mypassword'));

    (Give that user access to the created database from localhost)
    mysql> insert into db (host, db, user, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) values ('localhost', 'mydatabase', 'myuser', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

    (Quit from mySQL)
    mysql> quit;

    (Reload mySQL server)
    mysqladmin -u root -p reload
  6. Reload the HTTP Server just in case:
    service httpd reload
  7. Make sure that PHP is working by copying this testphp.php file to “/var/www/html” and browsing to “http://localhost/testphp.php”:
    <html>
    <head>
    <title>PHP Info Script</title>
    </head>
    <body>
    <?php
    phpinfo();
    ?>
    </body>
    </html>
  8. Make sure that PHP-mySQL is working by copying this testmysql.php file to “/var/www/html”, replacing the text ”mysql_root_password” with your real password, and browsing to “http://localhost/testmysql.php”:
    <html>
    <title>mySQL Test Script</title>
    <body>
    <?php
    // connect and select a database
    $link = mysql_connect("localhost:3306", "root", "mysql_root_password")
    or die ("Couldn&#39;t connect:  Check to make sure that:<br>" .
            "<ul><li>your MySQL server is running</li>" .
            "<li>you used the correct hostname (<tt>vergil/ovid</tt>)</li>" .      
            "<li>you added a colon with your port number after the hostname</li>" .
            "<li>you used the username &#39;root&#39;</li>" .      
            "<li>you used the correct root password</li>" .
            "<li>you didn&#39;t forget to close a set of quotation marks</li><br><br>");
    print "Connected successfully.<br>";
    $db = "mysql";
    mysql_select_db($db) or die("Could not select the database '" . $db . "'.  Are you sure it exists?");

    // perform an SQL query
    $query = "SELECT * FROM user";
    $result = mysql_query($query) or die("Query failed");

    // print the result of the first row (row counting starts at zero)
    printf("Host: %s<br>\n", mysql_result($result, 0, "Host"));                  
    printf("User: %s<br>\n", mysql_result($result, 0, "User"));
    printf("Grant privilege: %s<br>\n", mysql_result($result, 0, "Grant_priv"));

    // free result set
    mysql_free_result($result);
    // close the connection
    mysql_close($link);
    ?>
    </body>
    </html>
  9. Unzip the tikiwiki 1.8.5 archive to the “/var/www/html” directory and run the setup script:
    chmod u+x setup.sh
    ./setup.sh
    chmod u-x setup.sh
  10. Configure the tikiwiki by browsing to ”http://localhost/tiki-install.php” and inputting the tikiwiki database name, user, and password.
  11. Restore the database from the backup image:
    mysql -u root -p
    mysql> use tikiwiki;
    mysql> source tiki_backup.sql
  12. And we are done!

Some info above was provided by Linux Help – mySQL setup Guide.

No Comments