Database Backup Procedures

Database Backup Procedures

Database Backup Procedures

Introduction

Welcome to the technical help article on taking MySQL database backups of Octeth.

It is important to regularly create backups of your database to ensure that you have a reliable copy of your data in case something goes wrong with your system.

In this article, we will cover two ways to take backups of the Octeth MySQL database:

Before we dive into the instructions, let's briefly go over the difference between the two backup methods.

A regular SQL dump is a file containing the SQL statements needed to recreate the database. This method is suitable for most use cases and is relatively straightforward to set up.

On the other hand, Innodb backups using Percona's xtrabackup utility is a more advanced option that allows you to create hot backups of your database while it is still running. This method is useful if you need to minimize downtime during the backup process.

⚠️
DISCLAIMER: Please note that although taking regular backups of your database is important for data protection, it is not a foolproof solution. There is always a risk that your backups could become corrupted or lost, or that they may not contain all of the necessary data to fully restore your system. It is important to regularly test your backups to ensure that they are functioning properly and that you are able to restore from them in case of an emergency. We do not accept any responsibility about instructions written on this article and any results that may occur unexpectedly. Please contact your system administrator for more details.

Prerequisites

In order to take regular SQL dumps of the Octeth database, you will need to have the mysqldump command line tool installed on your system. This tool is standard on many Linux distributions, but if it is not available on your system, you can install it by running the following command:

apt-get install mysql-client

To take an Innodb backup of the Octeth database using Percona's xtrabackup utility, you will need to install the utility and a few other dependencies on your system. Follow these steps to install and set up Percona's xtrabackup:

  1. Navigate to the root directory:
    2. cd /root/
  2. Download the latest version of the percona-release package:
    3. wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
  3. Install the percona-release package:
    4. dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
  4. Enable the tools and release repositories:
    5. percona-release enable-only tools release
  5. Update the package list:
    6. apt update
  6. If MySQL 5.7 server is running;
    1. Remove the percona-xtrabackup-80 package (if it is installed):
      2. apt remove percona-xtrabackup-80 -y
    2. Install the percona-xtrabackup-24 package:
      3. apt install -y percona-xtrabackup-24
  7. If MySQL 8 server is running;
    1. Install percona-xtrabackup-80 package:
      2. apt install -y percona-xtrabackup-80
  8. Install the qpress package:
    9. apt install -y qpress

Once you have completed these steps, you should be ready to take an Innodb backup of the Octeth database using Percona's xtrabackup utility.

Regular SQL dump of the database

To take a regular SQL dump of the Octeth database, you will need to use the mysqldump command line tool. Here are the steps to follow to create a script that you can use to take regular backups of the database:

  1. Create a directory for storing the backups:
    2. mkdir /root/mysql_backups
  2. Create a bash script file inside the mysql_backups directory:
    3. touch /root/mysql_backups/mysql_backup.sh
  3. Make the back script executable:
    4. chmod 0755 /root/mysql_backups/mysql_backup.sh
  4. Open the back script file in a text editor and add the following content:
    5. #!/bin/bash

fileName="oempro_mysql_$(date +%s).sql.gz"

echo "Target file: $fileName"

mysqldump -u oempro -p"XXXXXXX" oempro --single-transaction --quick --lock-tables=false | gzip -9 -c > /root/mysql_backups/$fileName

    Note: Be sure to replace XXXXXXX with the correct password for the oempro user.

To take a backup of the Octeth database using this script, simply run the following command:

/root/mysql_backups/mysql_backup.sh

Alternatively, you can set up a daily scheduled backup by adding the following line to your crontab:

0 1 * * * /root/mysql_backups/mysql_backup.sh

This will run the mysql_backup.sh script every day at 1:00 AM.

Innodb backup using Percona's xtrabackup utility

To take an Innodb backup of the Octeth database using Percona's xtrabackup utility, you will need to have the utility installed and configured on your system. Follow the instructions in the "Prerequisites" section to install and set up Percona's xtrabackup.

Once you have Percona's xtrabackup installed and set up, you can follow these steps to create a bash script for taking Innodb backups of the Octeth database:

  1. Create a MySQL credentials file at /root/.my.cnf
    2. cat <<"EOM" > /root/.my.cnf
[client]
user=root
password=XXXXXXX
host=127.0.0.1
EOM

    Note: Be sure to replace XXXXXXX with the correct password for the root user.

  2. Create a bash script file for taking Innodb backups:
    3. cat <<"EOM" > /root/mysql_backups/innodb_backup.sh
#!/bin/bash

DAYWEEK=`date +%u`
DAYMONTH=`date +%d`

fileName="innodb_backup.xbstream.gz"

echo "Target file: $fileName"

ulimit -n 1000000

xtrabackup  --backup  --no-server-version-check  --stream=xbstream  --parallel=4  --datadir=/opt/oempro/_dockerfiles/mysql/data/  --target-dir=./ 2> /tmp/backup |pigz  > /root/mysql_backups/$fileName
EOM
  3. Make the bash script executable:
    4. chmod 755 /root/mysql_backups/innodb_backup.sh

To take an Innodb backup of the Octeth database using this script, simply run the following command:

/root/mysql_backups/innodb_backup.sh

Alternatively, you can set up a daily scheduled backup by adding the following line to your crontab:

0 1 * * * /root/mysql_backups/innodb_backup.sh

This will run the innodb_backup.sh script every day at 1:00 AM.

Conclusion

In this technical help article, we covered two ways to take MySQL database backups of Octeth: a regular SQL dump and an Innodb backup using Percona's xtrabackup utility. Both methods have their own advantages and use cases, and you can choose the one that best fits your needs.

Regardless of which method you choose, it is important to regularly test your backups to ensure that they are functioning properly and that you are able to restore from them in case of an emergency. Your system administrator should be responsible for testing the backup and restore system and making sure that backups are being taken without any problems.

We hope this article has been helpful in guiding you through the process of taking MySQL database backups of Octeth. If you have any further questions or need additional assistance, please don't hesitate to ask.

💠
image

Homepage Customer Area Community Portal Contact Us

Footer Social Icons

©Copyright Octeth, Inc. All rights reserved.