Methods to make your data redundant is an good practice in production environments with High Availability. MySQL Replication can support this kind of HA. In my blog post I want to explain how I build replication between an Master and Slave node. In my example I have run MySQL on Windows Server but this will also work with Linux hosts.

Configure the Master node

Before we can replicate MySQL the server needs to be accessible on the network the slave nodes are running. To keep it simple for now we would assume they are both in the same network subnet, as usually the case. Now we check if MySQL is bind to the network port accessible for the outside by netstat:

netstat -p tcp
ipv4
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING InHost
ipv6
TCP [::]:3306 [::]:0 LISTENING InHost

With the server listening on 0.0.0.0:3306 or [::]:3306 we are sure it’s advertising port 3306 on all internal network adapters. Next to check is your firewall that can block this port, if so make sure you open port 3306 here first.

Now go to the config file “my.ini”, for Windows this can be found on this default path “C:\ProgramData\MySQL\MySQL Server 5.7\”. Open this file in notepad and change the config with the following lines;

[mysqld]

# Binary Logging.
log-bin=mysql-bin

# Server Id.
server-id=1

Restart the MySQL server after we changed the ini file to make it active. We will start on the master node with creating an user account for replication. Next to creating the replication account we will give the right permissions. Start on the server the mysql binary;

C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe --host localhost --user root --password

And run the following SQL commands:

create user 'replication' identified by 'secretpassword';
grant replication slave on *.* to 'replication';

Consistent backup from Master

Before we can couple the slave node we need to make an consistent backup from our Master. This makes sure that the replication on the slave starts at the correct point with replicating. First we will run the following command on the Master database;

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note; The first command will do the following.

Closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executing UNLOCK TABLES.

The second command will give us the position and name of the bin log file, this one is needed later so safe it! Leave the command screen open so the read lock stays active.

Now make an backup of your MySQL master databases, in this example we assume we want to replicate everything. Start an SQL dump in an new command and transfer the output to the slave server:

"\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --host localhost --user root --password --all-databases > mysql_master.sql

While running the MySQL dump command we can unlock the tables in the first command screen:

UNLOCK TABLES;

Configure the Slave node

Now on the MySQL slave node we will import the output from the Master node MySQL dump created previous. Login on the slave node and open an command;

"\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" --host localhost --user root --password < mysql_master.sql

Wait till the import is finished and open the MySQL command on the slave node:

"\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" --host localhost --user root --password

Now we will configure the settings to connect to the Master node. This is done with the following SQL query where you have to set your own IP from the master node and position and file name from the bin log;

CHANGE MASTER TO
MASTER_HOST='10.0.0.2',
MASTER_PORT=3306,
MASTER_USER='replication',
MASTER_PASSWORD='secretpassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=123;

Now we can start the slave on the slave node. Again in the SQL prompt:

START SLAVE;

Now the replication is started we can check if everything works. With the following SQL command on the slave node you can check if errors occur or if there are other things wrong:

SHOW SLAVE STATUS\G

If everything is running fine you will see the status “Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates” in the output. Now you MySQL node is replicated and changes will be synchronized to the slave node.