Implement MySQL Replication in 3 simple steps

For a project we need a second MySQL database for reporting, so whe can split the day to day business (forms) and the analytic reporting. Another thing is that the data must be very actual, so a nightly backup is not really an option.

Use the following steps to implement MySQL Replication:

1) Configure master and slave instances
2) Create Replication user on the master
3) Connect from the slave to the master

Step 1) Configure master- and slave instances

Every server need a unique server ID. We use for the master server-id=1 and for the slave server-id=2.

Binary logging must be enabled (log-bin=mysql-bin) on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1.

Find the MySQL configuration files (on Ubuntu it is located in /etc/mysql/my.cnf). Add the following lines in both the master- and slave configuration files.

[mysqld]
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

 

[mysqld]
server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Restart the master- and slave instances.

/etc/init.d/mysql restart

Step 2) Create Replication user on the master

Now we need to create a Replication user on the master that is used for the connection from the slave.

Logon on you’re master with the root user:

mysql -u root -p

and type in the MySQL prompt:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

Now we use the ‘show master status’ statement to determine the current binary log file name and position. Remember this carefully! In our example we get the following output.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000153 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Step 3) Connect from the slave to the master

To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information.

First logon on you’re slave with the root user:

mysql -u root -p

and type in the MySQL prompt:

mysql> CHANGE MASTER TO
-> MASTER_HOST='ip-number of master',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_LOG_FILE='mysql-bin.000153',
-> MASTER_LOG_POS=106;

And … the last command simple type:

mysql> START SLAVE;

… and you are running MySQL in with Replication! Try to make some changes in the master. For example make a new database and a table and fill it with some rows. Look in the slave and see that the changes are processed.

If there is existing data in the master then create a data dump with the ‘mysqldump’ command on the master. Once the data dump has been completed, you then import this data into the slave before starting the above Replication process.

More information MySQL Replication How To