Setting Replication on the Master

Setting Master Database

Enable binary logging. Replication requires that binrary logging is enabled.

Enable Binary Logging

Enable binary logging in the options file in /etc/mysql/my.cnf

[mysqld]
 log_bin

Assign a master-id for the server

The server needs to have a master-id together with binary logging enabled to work. If you enable binary logging and do not assign the master-id the server will not start.

[mysqld]
 master-id = 1

Usually you give the master id of 1 for the master database.

Enable Specific Database for Replication

By default the master will log all databases to the binary log. To enable only a specific database for binary logging, use the binlog_do_db option.

[mysqld]
 binlog_do_db = employees

Service is listening on an interface not localhost

Make sure the server is reachable and TCP/IP is enabled.

mysql> show variables like 'bind_address';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| bind_address | * |

+---------------+-------+

Or should return an ip address not 127.0.0.1. Replication does not use the Unix socket, it requires TCP/IP.

Check skip_networking is OFF

Check if networking is enabled, the default value of skip_networking should be OFF

mysql> show variables like '%networking%';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| skip_networking | OFF |

+-----------------+-------+

Create a dedicated replication user account

This step is not necessary unless you want a separate use for replication. The user needs to have the REPLICATION SLAVE privilege.

CREATE USER 'slave_user'@'%' identified by 'secure_password';

Grant permission for REPLICATION SLAVE

GRANT REPLICATION SLAVE ON *.*  to 'slave_user'@'%';

Flush the privileges cache tables for the new login to take effect.

FLUSH PRIVILEGES;

Locking InnoDB tables to prevent writes

In order to take a successful database dump without corrupting the backup by new writes, the database tables needs to be locked.

Change to the new database and lock the tables

use employees;
FLUSH TABLES WITH READ LOCK;

Record position of binary log

This is not necessary if we are going to use master auto position, but in case record it.

Show the master status to find the position in the binary log

mysql> show master status;

+-------------------+----------+------------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+------------------+------------------+-------------------+

| master-bin.000008 | 154 | world, employees | | |

+-------------------+----------+------------------+------------------+-------------------+

Record the position and take note of the name of the binary log.

Take a database dump of the databases;

There are several ways to take a dump of the database. For InnoDB, the recommened way is to use a mysqldump.

ubuntu@master:~$ mysqldump --master-data -u root -p world > world-backup.sql

with the --master-data option set, the mysqldump will write the the CHANGE TO MASTER line in the dump file.

In my case in the world-backup.sql the statement was

CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000008', MASTER_LOG_POS=154;

Unlock the Tables and Setup the Slave Database

mysql> unlock tables;