Simple MySQL / MariaDB Backup

There are many ways to back up a MySQL or MariaDB server. Some ways include using mysqldump, mydumper, LVM Snapshots or XtraBackup. However, any robust backup solution boils down to one key requirement:

The ability to restore the databases to a point-in-time.

So, for instance, if your server crashes, you would like to be able to restore to the point in time before the server crashed. If data was deleted accidentally or damaged in some other way, you need to restore the data to the point in time before it was deleted or damaged.

If you use AWS RDS this ability is provided out of the box. However, you can meet this requirement much more cost effectively by using a simple VPS (such as Linode or Ramnode) with a simple setup I will describe below.

This setup will perform the following:

Write a log of all transactions and back up this log every 5 minutes to a remote server
Create a full database backup with mysqldump every day and copy this to a remote server

The backup keeps the binary logs for two days and the dumps for two weeks. Thus, any data loss should be limited to 5 minutes and full database backups should allow restoring data from up to two weeks ago.

System Landscape

Database Server: Runs the MariaDB or MySQL instance you want to back up
Backup Server: Used as a remote location to store backups

(any Linux based server will do for these)

Step 1: Enable Binary Logs

On Database Server:

Edit your my.cnf file (e.g. under /etc/my.cnf or /etc/my.cnf.d/server.cnf). Assert the following lines:

log-bin=logs/backup
expire-logs-days=2
server-id=1

Create the folder logs in your MySQL data dir (e.g. /var/lib/mysql)

mkdir /var/lib/mysql/logs

Set owner to user mysql for folder logs

chown mysql:mysql /var/lib/mysql/logs

Restart MySQL server

sudo systemctl restart mysqld

Now a binary logs should be written into the logs folder in your MySQL data dir.

Step 2: Create Script Full Backups with MySQL dump

On Database Server:

Create the folder /var/lib/mysql/dumps
Create the script /usr/local/mysql_dump.sh and copy the contents of mariadb-backup.sh into this script.
Search for the line starting with dumpopts. In this line, provide your mysql username and password.
Make the script executable

sudo chmod +x /usr/local/mysql_dump.sh

Schedule the script to run once every day using cron or systemd

cron

30 3 * * * /usr/local/mysql_dump.sh

systemd

Create /etc/systemd/system/mysql_dump.service

[Unit]
Description=Dumps mysql databases to backup directory

[Service]
Type=oneshot
ExecStart=/usr/local/mysql_dump.sh

Create /etc/systemd/system/mysql_dump.timer

[Unit]
Description=Run MySQL dump once per day

[Timer]
OnCalendar=*-*-* 03:13:00
OnBootSec=60min
Unit=mysql_dump.service

And don’t forget to enable and start the timer:

sudo systemctl enable mysql_dump.timer
sudo systemctl start mysql_dump.timer

Step 3: Write Script to Backup Files to Remote Server

On the Backup Server:

Log into your Backup Server. Create a user mysqlbackup here:

useradd mysqlbackup

Change to mysqlbackup user

sudo su - mysqlbackup

Create directories logs and dumps

mkdir logs
mkdir dumps

On the Database Server:

Copy public key for root user from /root/.ssh/id_rsa.pub
If the public key for root does not exist, run:

sudo ssh-keygen -t rsa

On the Backup Server:

While being logged in as user mysqlbackup, assure the following file exists

~/.ssh/authorized_keys

Into this file, paste the public key for root on Server 1
Assure correct permissions for .ssh folder:

chmod 700 .ssh
chmod 600 .ssh/authorized_keys

On the Database Server:

Test access to the Backup Server (the sudo is important here, since you want to connect as the root user). Replace yourservername.com with the address/IP of Server 2.

sudo ssh mysqlbackup@yourservername.com

If the SSH does not work for some reason, check this guide for more information.
Create the script /usr/local/mysql_backup.sh. Replace yourserver.com with the address/IP of your server.

#!/bin/bash
rsync -avz --delete /var/lib/mysql/logs mysqlbackup@yourserver.com:/home/mysqlbackup
rsync -avz --delete /var/lib/mysql/dumps mysqlbackup@yourserver.com:/home/mysqlbackup

Make the script executable

sudo chmod +x /usr/local/mysql_backup.sh

Use crontab or systemd to schedule the job for execution every 5 minutes:

crontab

Add the following line to the crontab for the user root

*/5 * * * * /usr/local/mysql_backup.sh

systemd

Create the file /etc/systemd/system/mysql_backup.service

[Unit]
Description=Backs up Mysql binary logs and full backups to remote server

[Service]
Type=oneshot
ExecStart=/usr/local/mysql_backup.sh

Create the file /etc/systemd/system/mysql_backup.timer

[Unit]
Description=Run MySQL binlog backup and full backup sync every 5 minutes

[Timer]
OnCalendar=*:0/5
OnBootSec=5min
Unit=mysql_backup.timer

Enable and start the timer

sudo systemctl enable mysql_backup.timer
sudo systemctl start mysql_backup.timer

Set up MySQL Replication with Amazon RDS

Problem

You have an existing server that runs a MySQL database (either on EC2 or not) and you would like to replicate this server with a Amazon RDS MySQL instance.

After you follow the instructions from Amazon, your slave reports the IO status:

Slave_IO_State: Connecting to master

… and the replication does not work.

Solution

AWS provides very good documentation on how to set up the replication: Replication with a MySQL or MariaDB Instance Running External to Amazon RDS.

Follow the steps there but be aware of the following pitfall:

In step 6 `create a user that will be used for replication`: It says you should create a user for the domain ‘mydomain.com’. That will in all likelihood not work. Instead, try to find out the IP address of the Amazon RDS instance that should be the replication slave.

One way to do this is as follows:

Create the ‘repl_user’ for the domain ‘%’, e.g.:

CREATE USER 'repl_user'@'%' IDENTIFIED BY '<password>';

Also do the grants for this user

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY '<password>';

Open port 3306 on your server for any IP address.
Then the replication should work.
Go to your master and run the following command:

SHOW PROCESSLIST;

Find the process with the user repl_user and get the IP address from there. This is the IP address for your Amazon RDS slave server.
Delete the user ‘repl_user’@’%’ on the master
Create the user ‘repl_user’@'[IP address of slave]’ on the master
Modify your firewall of your master to only accept connections on port 3306 from the IP address of the slave.
Restart replication with

call mysql.rds_stop_replication;
call mysql.rds_start_replication;

And check the status with

show slave status\G

The slave IO status should now be “Waiting for master to send event”.

Upgrade MySQL 5.5 to 5.6 on EC2/CentOS/RHEL

Problem

You would like to upgrade MySQL 5.5 to MySQL 5.6 on an existing server that uses the YUM software package manager.

Solution

Just enter the following few simple commands and you should be good to go. But, please, do a thorough full backup of your system before you do the upgrade just in case.

[1] Create a MySQL dump from which you will load the data into the upgraded server:

mysqldump -u root -p –add-drop-table –routines –events –all-databases –force > data-for-upgrade.sql

[2] Stop your MySQL server

sudo service mysqld stop

[3] Remove MySQL 5.5

yum remove mysql55-server mysql55-libs mysql55-devel mysql55-bench mysql55

[4] Clear the MySQL data directory

sudo rm -r /var/lib/mysql/*

[5] Install MySQL 5.6

sudo yum install mysql56 mysql56-devel mysql56-server mysql56-libs

[6] Start MySQL server

sudo service mysqld start

[7] Set the root password

/usr/libexec/mysql56/mysqladmin -u root password ‘xxx’

[8] Import your data

mysql -u root -p –force < data-for-upgrade.sql

[9] Verify all tables will work in 5.6

sudo mysql_upgrade -u root -p –force

All done!

Notes

Upgrade to 5.7 should work in a similar way, once 5.7 is available on your RPM repos (it isn’t by the time of the writing for the Amazon Linux Repo).

Sources

Delete All Binary Logs for MySQL

Today I discovered that one of my servers mysteriously ran out of disk space.

I ran the following Linux command to find all the biggest files and folders on the server:

sudo du -a / | sort -n -r | head -n 100

… and found that it was the binary logs used for MySQL replication that were gobbling up all the disk space:

7375152	/
4691636	/var
4324880	/var/lib
4284952	/var/lib/mysql
1079420	/usr
1048588	/var/lib/mysql/mysql-bin.000004
1048584	/var/lib/mysql/mysql-bin.000006
1048584	/var/lib/mysql/mysql-bin.000003
802356	/var/lib/mysql/mysql-bin.000007

Now I first found some advise that using the PURGE BINARY LOGS should be the way to go. That is true if you want to delete the logs without hurting your ongoing MySQL replication.

However, I was just interested in deleting all the binary logs and the way to do that is by logging into your server with a user with SUPER privileges and executing the following command:

RESET SQL;

Now all those pesky ‘mysql-bin.*’ files should have disappeared!

MySQL ERROR 2026 (HY000): SSL connection error – Some Troubleshooting Ideas

I just spent a fair amount of time setting up MySQL replication between two servers encrypted by SSL (using MySQL 5.1.73).

I struggled with fixing a nasty error displayed only as ‘ERROR 2026 (HY000): SSL connection error‘.

In the following, I have collected a few possible strategies for resolving this error:

Is the password for the user on your server shorter than 36 characters?
Do the *.pem files on the server and client have the right file permissions?
Do your client and server certificates use a different COMMON NAME?
Have you tried a basic SSL setup with only certificate authority certificate (e.g. ca-cert.pem), server certificate (e.g. server-cert.pem) and server key (e.g. server-key.pem) (see). In theory, client certificates are not required for a basic setup.
Have you tested your certificates with a simple openssl HTTP server (see)?
Is your private key in the PKCS#1 format (file starts with ‘—–BEGIN RSA PRIVATE KEY—–‘)? (see, see)
Did you generate your certificates with TinyCA with the default settings?
Did you try connecting to the server WITHOUT using the certificate authority certificate (e.g. ca-cert.pem) BUT WITH specifying a client certificate and key?
- mysql –ssl –ssl-cert=[client_cert] –ssl-key=[client_key] -u[ssluser] -h[server] -p[ssluser psw]
Is your certificate ‘simple enough’ for the MySQL SSL implementation e.g. not a chained certificate tree? (see) Or did you use a wildcard certificate (which are not supported) (see).

Good luck 🙂

Resources

Check if MySQL User Exists on Command Line (and in Puppet)

If you are using Linux, there is simple way to check if a user exists in MySQL:

echo `mysql -u root -p[your root password] -e ‘use mysql; SELECT user FROM user;’` | grep ‘[user name]‘

Executing this command will exit with return code 0 if the user exists and otherwise exit with 1.

This is very useful for building puppet scripts. The following execution will create a user if it doesn’t exist.

exec { “add user if not exist”:

unless => “echo `mysql -u root -p[psw] -e ‘use mysql; SELECT user FROM user;’` | grep ‘[username]‘”,
path => [“/bin”, “/usr/bin”],
command => “mysql -u root -p$mysql_password -e \”[Create User/Grant Rights Here]\””,
require => Service[“mysqld”],
}