MySQL Replication is incredibly simple to get up and running and this (short) post steps you through it.
MySQL allows you to build up complex replication hierarchies, such as multi-master, chains of read slaves, backup databases at a remote site or any combination of these. This post focuses on a simple single master to single slave topology – the more complex solutions are built from this basic building block.
This post also makes the assumption that the 2 MySQL Servers have been installed but that there is no existing data in the master that needs to be copied to the slave – it’s not complex to add that extra requirement and it will be covered in a future post.
Server “black” (192.168.0.31) is to be our master and “blue” (192.168.0.34) the slave.
Step 1: Edit the configuration files & start the MySQL Servers
The first step in setting up replication involves editing the “my.cnf” file on the servers that will serve as the master and slave. A default is provided with the MySQL installation but in case there is already a production MySQL database running on these servers, we provide local configuration files “master.cnf” and “slave.cnf” that will be used when starting up the MySQL servers.
At a minimum we’ll want to add two options to the [mysqld] section of the master.cnf file:
- log-bin: in this example we choose black-bin.log
- server-id: in this example we choose 1. The server cannot act as a replication master unless binary logging is enabled. The server_id variable must be a positive integer value between 1 to 2^32
[mysqld] server-id=1 log-bin=black-bin.log datadir=/home/billy/mysql/master/data innodb_flush_log_at_trx_commit=1 sync_binlog=1
Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options.
Next, you’ll need to add the server-id option to the [mysqld] section of the slave’s slave.cnf file. The server-id value, like the master_id value, must be a positive integer between 1 to 2^32, It is also necessary that the ID of the slave be different from the ID of the master. If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves.
[mysqld] server-id=2 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin datadir=/home/billy/mysql/slave/data
Now, start the MySQL servers using the service manager or directly from the command line if not being run as a service:
[billy@black ~]$ mysqld --defaults-file=/home/billy/mysql/master/master.cnf &
[billy@blue ~]$ mysqld --defaults-file=/home/billy/mysql/slave/slave.cnf&
Step 2: Create Replication User
Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege:
[billy@black ~]$ mysql -u root --prompt='master> ' master> CREATE USER firstname.lastname@example.org; master> GRANT REPLICATION SLAVE ON *.* TO email@example.com IDENTIFIED BY 'billy';
Step 3: Initialize Replication
We are now ready to initialize replication on the slave; issue a CHANGE MASTER command:
slave> CHANGE MASTER TO MASTER_HOST='192.168.0.31', -> MASTER_USER='repl_user', -> MASTER_PASSWORD='billy', -> MASTER_LOG_FILE='', -> MASTER_LOG_POS=4;
- MASTER_HOST: the IP or hostname of the master server, in this example blue or 192.168.0.31
- MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 2, in this example, “repl_user”
- MASTER_PASSWORD: this is the password we assigned to ”rep_user” in Step 2
- MASTER_LOG_FILE: is an empty string (wouldn’t be empty if there were existing writes to be picked up from the master)
- MASTER_LOG_POS: is 4 (would likely be different if there were existing writes to be picked up from the master)
Finally, start replication on the slave:
slave> start slave;
Step 4: Basic Checks
Now we are ready to perform a basic check to ensure that replication is indeed working. In this example we insert a row of data into the “simples” table on the master server and then verify that these new rows materialize on the slave server:
master> create database clusterdb; master> create table clusterdb.simples (id int not null primary key) engine=ndb; master> insert into clusterdb.simples values (999),(1),(2),(3); slave> select * from clusterdb.simples; +-----+ | id | +-----+ | 1 | | 2 | | 3 | | 999 | +-----+