MySQL Replication

MySQL is the relational database system of choice for open sourcers. Replication is the process of replicating data from one MySQL database server (the master) into another (the slave). We’ll go into why you would want to replicate a MySQL database in another article.

MySQL Replication
Using the master-slave configuration mentioned above, only the changes made to the master are replicated in the slave. Changes made to the slave do not affect the master.

If you follow the steps below, you can set up MySQL replication in a matter of minutes.

1.Open the my.cnf or my.ini file (depending on whether you are running Linux or Windows).
2.Type in the following, somewhere below “[mysqld]”

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

3.Restart MySQL on the master server.
4.Create a new user on the master server with the “REPLICATION SLAVE” privelege. You don’t need to assign any other priveleges to this user. In the following commands, replace X.X.X.X with the IP address of the slave server.

CREATE USER ‘user’@ ‘X.X.X.X’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON *.* TO ‘user’@'X.X.X.X’ IDENTIFIED BY ‘password’;

5.Execute ‘FLUSH TABLES WITH READ LOCK;’ on the master to prevent writing to the databases.
6.Execute ‘SHOW MASTER STATUS;’ on the master and note down the values because we’ll use these later.
7.Execute ‘UNLOCK TABLES;’ on the master.
8.Open the my.cnf or my.ini on the slave server.
9.Enter somewhere below “[mysqld]” on the slave server:

server-id=2

10.Save the file and restart mysqld.
11.Execute the following on the slave server (adjust values according to user setup in step 4 and values retrieved from step 6):

CHANGE MASTER TO
MASTER_HOST=’X.X.X.X’,
MASTER_USER=’user’,
MASTER_PASSWORD=’password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000001?,
MASTER_LOG_POS=98,
MASTER_CONNECT_RETRY=10;

12.Execute the following on the slave server:

START SLAVE;

13.Check the mysql log on the slave to ensure that the connection to the master has been successful. You should see a line similar to the following:
091104 8:42:02 [Note] Slave I/O thread: connected to master ‘root@X.X.X.X:3306?, replication started in log ‘mysql-bin.000001? at position 98

Now you should have successfully configured master-slave MySQL replication.

Something you might want to consider is limiting the binary log files retentions with the expire_logs_days on the master, otherwise the replicated date will just grow and grow
 

Src: http://www.hackosis.com/how-to-setup-mysql-replication/