The Best Way to Setup MySQL Replication

In this case, I have 2 servers installed with MySQL 5 and running in CentOS 5. Assuming Server1 is master server and currently host a database called db1 and Server2 is slave server and have nothing inside.

Variable used in this case:

Server1 IP=192.168.1.1
Server2 IP=192.168.1.2
Database name=db1
Replication user=replicator
Replication user password=slavepass

Now we configure Server1 first. Login to the server and do following:

1. Login to Server1 and open MySQL configuration file, /etc/my.cnf and add following lines under [mysqld] section:

[mysqld]
server-id=100
log-bin=master-bin
									

2. Restart MySQL:

service mysqld restart
									

3. Create a user account so slave can use to connect to this master server:

mysql> GRANT REPLICATION SLAVE on *.* to 'replicator'@'%' IDENTIFIED BY 'slavepass';
									

4. Dump the database so we can bring the database to the slave server:

mysqldump -u[root] -p[password] --master-data --single-transaction db1 > db1.sql
									

5. Copy the dump files to the slave server via network or external hard disk

Lets configure Server2. Make sure the MySQL dump has been transferred to the slave server. In this case we put the dump file under /root/ directory.

1. Login to Server2 and open MySQL configuration file, /etc/my.cnf and add following lines under [mysqld] section:

[mysqld]
server-id=101
replicate-wild-do-table=db1.%
									

2. Restart the MySQL:

service mysqld restart
									

3. Load the database into slave and start the slave process:

mysql> CREATE DATABASE db1;
mysql> USE db1;
mysql> SOURCE /root/db1.sql;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='slavepass';
mysql> START SLAVE;
									

4. Check whether the slave is working or not:

mysql> SHOW SLAVE STATUS\G
									

Make sure you see something like below:

*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
									

Related Posts

• MySQL – Recover Data Using mysqlbinlog
• Linux: Duplicate MySQL Database
• Linux: Install and Configure PostgreSQL with pgAdmin
• CentOS: Increase MySQL Uptime with MySQL Proxy
• Create MySQL Database Backup Every Half an Hour
• Linux: Email Alert on MySQL Replication Failure
• Move MySQL Directory to Another Location
• MySQL General Security Guidelines
• MySQL Daily Backup and Transfer to other Server
• Install MySQL Replication and Cluster using Galera