Your website is growing and you were looking into a way to squeeze some more performance out of your website, setup a database instance for reports, backups, etc.

A solution that you can use is MySQL Replication which will allow you to setup another database server instance of your master server that replications updates from your master server.

A few things you will need to do on your master server:

Step 1

Make sure you set the following in your mysql master server configuration file.

[mysqld]
log-bin=servername.mysql-bin
datadir=/dbdata/servernamedata

basedir=/locationofdatabaseserverfiles
serverid=1

Step 2

You will now want to create a user for the slave server to connect to.

CREATE USER ‘databasename_rep’@’10.0.0.%’ IDENTIFIED BY ‘yourreplicationpassword’;

GRANT REPLICATION SLAVE ON *.* TO ‘database_name’@’10.0.0.%’;

FLUSH PRIVILEGES;

Step 3

Now restart your master mysql server, login and then run the commands:

FLUSH TABLE WITH READ LOCK;

You will want to keep this session open while you setup the slave server.

Open another session to the master and run the following command:

SHOW MASTER STATUS;

This will give you the binary log coordinates that you will need for setting up your slave server.

From your terminal you will want to do a mysqldump and restore the data onto your slave database.

From Terminal:

#To to a backup of the databases
mysqldump -u root -h 10.0.0.5 –all-databases –lock-all-tables -p > /locationforsqldump.sql

#To restore the databases
#Login to the slave database server and run
source /locationforsqldump.sql

Once you have done this you can type in the following on the slave:

STOP SLAVE;

CHANGE MASTER TO
MASTER_HOST=’10.0.0.11′,
MASTER_USER=’databasename_rep’,
MASTER_PASSWORD=’yourreplicationuserpassword’,
MASTER_LOG_FILE=’Informationfrom showmasterstatuscommand’,
MASTER_LOG_POS=locationnumberfromshowmasterstatuscommand;

START SLAVE;

This should now start the replication process which you can check using:

SHOW SLAVE STATUS\G;

If you have any errors with the replication the SHOW SLAVE STATUS\G; command will let you know about them.