The ability to create replicated servers using MySQL is one of the most useful features. You can create slave servers for redundancy, load balancing, development or testing purposes, and MySQL takes care of keeping the servers in sync. I will review simple slave server setup using the binary log (transactional) method.
Assumptions
The procedures below will work in many environments, but I outline mine as a reference point. You may need to alter the commands to work in yours.
- Debian 9 or 10
- MySQL 8 or Percona Server 8
- SSH root access or su to root (I'm not Ubuntu; I do not include sudo in front of the commands, but you may need to.)
- Ability to install Percona toolkit from repositories
- Basic Linux/MySQL administration knowledge
- Perimeter security in place to allow uninhibited operations within environment
- I assume you know how to move/copy files from one instance to another if you're not connecting to both from the same machine.
You Will Need
- A server with a working directory and MySQL monitor. Preferably the master or slave.
- Access to your master server (SSH, MySQL monitor) from your working machine. Set up your tunnels, security groups, SSH, VPN, whatever voodoo you need to connect.
- A clean and compatible slave server with no existing schema except default MySQL databases.
- Access to your slave server (SSH, MySQL monitor) from your working machine. Set up your tunnels, security groups, SSH, VPN, whatever voodoo you need to connect.
- You will need filesystem access to the slave server to move the backup files into the mysql data directory location.
You may consult the following guides for help altering these instructions to suit your environment: https://www.percona.com/doc/percona-xtrabackup/8.0/howtos/setting_up_replication.html
Do Not Break Your Production Server After 10:00 P.M. or You Will Be Up All Night
With most server administration being done remotely, on laptops, wifi, etc, it's always a good idea to use the Linux tool screen for any long operations. If your connection to the server fails, your operation will proceed inside a screen session which you can reconnect to after reconnecting to the server.
In this tutorial, like all, I'm going to dispense with silly formatting niceties such as breaking commands into multiple lines and including redundant arguments. You're a server admin, so I'm just going to give you the basics and let you alter to your needs. I also trust you can read and edit a command line longer than 20 characters.
Contrary to most advice, the servers no longer must have apples to apples configuration or size, as long as the configuration is compatible enough to prevent replication errors. This is completely dependent on your individual use-case and tolerance for recoverable or unrecoverable errors. I often run smaller slaves as backup or development servers in unconventional configurations. You should be practical. There is no need to fuss over details that don't matter, but you also don't want to be slogging through replication errors, running manual queries and deciding how much slave integrity means to you at 3:00 a.m.
Let's Proceed
Create the Replication User
The slave server will remotely connect to the master server to read transactions from the binary logs (binlogs). Create a dedicated user on the master server that the slave server will use to authenticate.
CREATE USER 'replication-user'@'%' IDENTIFIED WITH mysql_native_password BY 'Pa$$w0rd';
Grant replication privileges to the replication user.
GRANT REPLICATION SLAVE ON *.* TO 'replication-user'@'%'
Create a Slave Server
Create a slave server with exact distribution and version of MySQL as Master. Ensure the same or similar configuration. Make sure the appropriate security groups or firewall rules are in place to allow the slave server to connect to the master server port 3306, MySQL.
Gather and Verify Your Tools
Install xtrabackup from Percona Toolkit if not already installed. This requires the Percona repositories to be installed on your server, or you will need to manually install according to instructions on their site. Percona tools will work with other flavors of MySQL; you are not limited to Percona Server.
Note: xtrabackup is a wrapper for the mysql executable. Unless your credentials are stored in a .my.cnf file in your home directory, you will need to pass user and password arguments to the xtrabackup executable so it can connect to the running server. Arguments such as --user, --password and --host are passed to the mysql child process unaltered. Consult the following link for help on options that may be passed to the xtrabackup executable: https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html
Run a Hot Backup on the Master Server
Long:
xtrabackup --backup --user=root --password='Pa$$w0rd' --host=localhost --target-dir=/temp
Short:
xtrabackup --backup --target-dir=/temp
You're going to see a lot of output to the console as xtrabackup makes a hot backup of your MySQL server, keeping track of binlog positions.
When finished, you will find the following similar output:
xtrabackup: Transaction log of lsn (11452351234) to (11452351264) was copied.
200413 03:03:20 completed OK!
Now you must prepare the backup for restoration to the new slave server. This generates a binary log position number, which defines the point in time which the new slave should start reading transactions and is later used to start the new slave.
Long:
xtrabackup --user=root --password='Pa$$w0rd' --prepare --target-dir=/temp
Short:
xtrabackup --prepare --target-dir=/temp
You'll notice the following similar output when finished:
Shutdown completed; log sequence number 11452351500
200413 03:09:10 completed OK!
You now have a complete point-in-time backup of your database server.
Populate the Slave Server
You may or may not want to copy the my.cnf file from the master to the slave, depending on how much you have customized your master configuration. I normally keep things close to default on most servers, so there is usually no need to copy. Even if you don't, you have to make this call yourself.
Move the Data to the New Slave
Move the backup you just created to the new slave server using rsync, scp, thumbdrive, move the physical disk or however you must. Your choice.
Stop the running MySQL server on the new slave server.
I usually backup and clear the existing MySQL data directory to prepare it to receive the new files, but you don't have to.
Now use xtrabackup to move the prepared backup to the configured MySQL data directory on the new slave server.
xtrabackup --move-back --target-dir=/temp
Make sure the entire MySQL data directory is recursively owned by the MySQL user and group, otherwise the server will not start or run correctly. Chown it to mysql:mysql and add the -R.
Start the MySQL server, connect to the MySQL monitor and view the schemas to make sure everything is in place.
Find the binary log filename and position that you will use to start replication on the new slave server. This is stored in a file called xtrabackup_binlog_info in the backup or data directory.
cat /var/lib/mysql/xtrabackup_binlog_info
Now you will need to open the MySQL monitor again and configure the slave. Replace the host, user, password, log file and log position to match your values.
CHANGE MASTER TO
MASTER_HOST='0.0.0.0',
MASTER_USER='replication-user',
MASTER_PASSWORD='Pa$$w0rd',
MASTER_LOG_FILE='bin.000001',
MASTER_LOG_POS=500;
Use the following command to verify slave configuration. Here's a tip if you haven't used MySQL on the command line much: terminate your commands with \G instead of ; to receive vertical output instead of tabular. You will not want to read the output of this command in a horizontal table.
SHOW SLAVE STATUS\G
Now start the replication process and allow the slave server to catch up transactions with the master.
START SLAVE;
At any time, you can use SHOW SLAVE STATUS\G to view replication status on the slave server. In a future article, I will delve into the output of this command, common errors, and how to fix things that go horribly wrong with replication. I will also explore replication setup using AWS RDS (Relational Database Service), and quick and dirty replication with native MySQL utilities.