MySQL Replication

http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

http://www.rackspace.com/knowledge_center/article/set-up-mysql-master-slave-replication

https://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html#step-3-configure-the-master-s-mysql-server

Set up MySQL master-slave replication

Master-slave data replication allows for replicated data to be copied to multiple computers for backup and analysis by multiple parties. Needed changes identified by a group member must to be submitted to the designated “master” of the node. This differs from Master-Master replication, in which data can be updated by any authorized contributor of the group.

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

master.cnf:

[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.

slave.cnf:

[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 repl_user@192.168.0.34;master> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.0.34 IDENTIFIED BY 'billy';

master> SHOW MASTER STATUS;

+——————+————————–+——————+

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+————————–+——————+

| master-bin.000004 |        4 |              | mysql            |

+——————+————————–+——————+

1 row in set (0.00 sec)


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;

Where:

  • 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)  Ex:-master-bin.000004
  • 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 |

+—–+