Data is valuable, and owners want to protect it against loss. The first choice is usually a backup; however, the disadvantage of the classic periodic backup is that data that has changed after the last backup is lost. A potential solution is replication – that is, transferring all the changes to a storage location that is a copy of the working infrastructure. This approach also supports load balancing if the second system is made available to users for read-only access. To define the terms, I'll conduct a brief tour through the different types of replication (Figure 1).

Figure 1: The different types of standby solutions depend on whether and to what extent the second node can be used.

Variants

In a "standalone" instance, nothing is replicated, and "cold standby" means that the backup system only starts up after its counterpart has failed. Here, too, it is not absolutely necessary to replicate; it is sufficient if the backup system can access a storage medium that it shares with the primary system.

A "warm standby" has a primary machine that handles all the write and read operations, with a secondary machine also running and continuously receiving replicas. The secondary machine can jump in at any time without losing data. If it also handles some of the primary machine's read workload, the setup is referred to as "hot standby." When both sides read and write at the same time and reciprocally sync the changes, it is known as a "master-master" setup.

A classic high-availability scenario comprises at least three computers so that operations do not depend on a single machine should one fail. Because load distribution also often plays a major role, replication kills two birds with one stone.

Duplicating Data

MySQL has had an easy-to-install replication feature for more than 15 years. Today, three companies are officially working on its further development: MySQL itself, which was acquired by Oracle in 2009; MariaDB, which some original and core developers of MySQL in Finland founded after the MySQL takeover by Oracle; and other former MySQL employees in the United States who founded Percona, which also deals with issues such as backup, high availability, and replication in Oracle's MySQL and MariaDB.

For this article, I used MariaDB 10.1 on Ubuntu 16.04 and XtraBackup 2.4 by Percona, which also works on other Linux distributions and with MySQL.

A prerequisite for replication operations – whether warm or hot – is that you have identical data available on both the primary computer, or master, and the secondary computer, or slave. This parity can be achieved, for example, by copying the database directory or – the better way described here – using a backup.

Caution is advisable with simple copies. Some tools try to duplicate the data and log directory during operation without taking into account the cache and active transactions. Such filesystem backups usually do not let you recover the database system. Other tools stop the database system, back up the filesystem, and boot the database again, which may guarantee a restorable backup; however, stopping a system in 24/7 operation is not acceptable for many applications. Tools that use dumps cost a huge amount of performance, and, at the most, the system can only be restored up to the time of the dump.

The XtraBackup system by Percona is different: With its help, you can restore a MariaDB/MySQL database up to a point later than the last regular backup (point in time recovery). The only condition is that you need the required log data. XtraBackup is also well suited to set up a new replication node (slave) quickly and easily, without shutting down the master or an existing slave.

Adjusting the Settings

State-of-the-art Linux systems use the UTF-8 character set by default. Although clients running on the console (e.g., the MariaDB/MySQL command-line client) and the commands from XtraBackup may speak UTF-8, the Latin-1 character set is commonly the default in MySQL and MariaDB. The mariadb.cnf configuration file located on Ubuntu 16.04 under /etc/mysql/conf.d has the UTF-8 character set settings commented out but in place. I advise you to change the client (slave) as well as the complete server (master) to UTF-8 (Listing 1).

[...]
# MariaDB-specific config file.
# Read by /etc/mysql/my.cnf
[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8
[mysqld]
#
# * Character sets
#
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
character-set-server   = utf8
collation-server       = utf8_general_ci
character_set_server   = utf8
collation_server       = utf8_general_ci
[...]

A configuration file, /etc/mysql/conf.d/mysql_safe_syslog.cnf, is also created during the installation, which prevents an error log from being written. To enable the error log, you need to comment out skip_log_error and then configure the path (Listing 2). If you will be running the instance as a master later on, make sure the bind-address parameter in /etc/mysql/my.cnf does not point to localhost (127.0.0.1).

[...]
[mysqld_safe]
#skip_log_error
log-error = /var/log/mysql/error.log
syslog
[...]

To write error messages to the log, you need to configure the path for log-error in my.cnf. The log_warnings parameter determines what to log. The value   means that logging is disabled. A value greater than 1 additionally logs aborted connections.

In a replication setup, each node requires a unique positive integer for the server-id. A popular option is to number the machines incrementally; alternatively, you can use the last digits of the IPv4 address. For XtraBackup – just as for the replication master – you need to enable the binary log (binlog) and set the appropriate path. The system indexes the binlog; the index file is specified with log_bin_index in my.cnf.