Setup MySQL Replication in 5 Minutes

Setup MySQL Replication in 5 Minutes

MySQL Replication is incredibly simple to get up and running and this post will instruct you through it. Simple Master -> Slave Replication 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 article focuses on a simple single master to single slave setup. This article also assumes that 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 “masterhost” (192.168.0.31) is to be our master and “slavehost” (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 inodecloud-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/inode/mysql/master/data innodb_flush_log_at_trx_commit=1 sync_binlog=1 Note: For the greatest possible durability and consistency in...