How to Setup MySQL Replication in 11 Easy Steps

I've provided a link few weeks ago to a great complete guide for MySQL replication. Yet, since then I got many requests to provide a short and east guide for a new MySQL replication setup.

Well, Fasten Your Seat-belts

1. Install a MySQL instance that will serve as a Master
2. Install a MySQL instance that will serve as a Slave
3. Configure the Master my.cnf file (located at /etc/ at CentOS) with the server id and the log file name: 
1. [mysqld]
2. server­-id = 1
3. log­-bin   = master­-bin.log
4. Configure the Slave my.cnf with the server id, reply logs and key configuration databases;
1. server­-id = 2 
2. relay­-log-­index = slave-­relay-­bin.index
3. relay­-log = slave­-relay­-bin
4. replicate-wild-ignore-table=mysql.%
5. replicate-wild-ignore-table=information_schema.%
6. replicate-wild-ignore-table=performance_schema.%
5. For MySQL 5.6: Delete the auto.cnf from your MySQL directory slave> rm -rf /var/lib/mysql/auto.cnf
6. Restart the MySQL daemons on both servers to apply the my.cnf changes.
7. If both servers were just installed there is no need to sync their data files (since they  should have the same data files). O/w you should either stop the slave and master and copy the data files using SCP or perform just perform a mysqldump. Notice! Before copying the files, get the Master location describe below.
8. Get the master location, so we can sync the slave to it:
1. master> FLUSH TABLES WITH READ LOCK;
2. master> SHOW MASTER STATUS;
3. +-------------------+----------+--------------+------------------+
4. | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
5. +-------------------+----------+--------------+------------------+
6. | master-bin.000013 |  1233453 |              |                  |
7. +-------------------+----------+--------------+------------------+
9. master> UNLOCK TABLES;
9. Provide the slave with replication permissions:
1. master> GRANT REPLICATION SLAVE ON *.* to `repl`@`%` IDENTIFIED BY 'slavepass';
2. master> FLUSH PRIVILEGES;
10. Setup the slave using the chosen user/password and the master location we found before:
1. slave> CHANGE MASTER TO
2.     ->     MASTER_HOST='10.17.16.253',
3.     ->     MASTER_PORT=3306,
4.     ->     MASTER_USER='repl',
5.     ->     MASTER_PASSWORD='slavepass',
6.     ->     MASTER_LOG_FILE='master-bin.000013',
7.     ->     MASTER_LOG_POS=1233453;
11. Now, start the slave and verify it's running: 
1. slave> start slave;show slave status\G
12. If everything is Okay, verify the replication really works by:
1. Creating a table and inserting values to it in the master:
1. master> CREATE DATABASE a;
2. master> CREATE TABLE a.b (c int);
3. master> INSERT INTO a.b (c) VALUES (1);
2. Verifying that SLAVE/MASTER> SELECT * FROM a.b; return the same values in the master and slave;
3. Dropping the database in the master: master> DROP DATABASE IF EXISTS a;

P.S If you are interested in implementing the replication using SSL, follow these instructions.

Bottom Line

Only few minutes and you got a running MySQL replication. Even these kind of tasks can be so simple and smooth...  and leaving you time to drink a cup of coffee!

Keep Performing,

Moshe Kaplan