MySQL Replication

 This guide is designed to help do the initial setup on a MySQL cluster in which multiple MySQL servers all serve the same content through the use of the replication function. We have successfully deployed this solution for multiple clients and it is a very good option for those needing a more powerful mysql solution. Carlos (theuruguayan) actually created this document so please direct any questions to carlos@totalserversolutions.com .

 Be sure your mysql servers are running the same version before starting this guide, yes, is possible to have a few combinations of master-slave versions, for more information about this you can check:

 http://dev.mysql.com/doc/refman/4.1/en/replication-compatibility.html

1 - Write down which is the setup you are going to do, which server is master and which server/s will be slave.

2 - Select your username/password for replications accounts. You can have one per server if you want, or one for all the mysql network.

3 - mysql> GRANT REPLICATION SLAVE ON *.*

    TO 'USERNAME'@'IPFROMTHESLAVE' IDENTIFIED BY 'PASSWORD';

    Username: mysql username
    IPfromtheslave: ip from the mysql server that will be the one replicating the master db.
    PASSWORD: the password for the replicator account.

 Just a few side notes.

            a) None of the passwords need to be root passwords.
            b) Is not recomend to use only 1 user for replication in all the network.

4) In the master server you need to Flush all the tables, this will prevent clients from writing the db so it will keep without change while we copy over.

 mysql> FLUSH TABLES WITH READ LOCK;

 5) Make sure that the [mysqld] section of the my.cnf file on the master host includes a log-bin option. The section should also have a server-id=master_id option, where master_id must be a positive integer value from 1 to 232 – 1. For example:

[mysqld]

log-bin=mysql-bin

server-id=1

 6) Login using another ssh client to the master server and lets create a snapshot.

mkdir /home/slave_db
rsync -vrplogDtH /var/lib/mysql /home/slave_db

You may not want to replicate the mysql  database if the slave server has a different set of user accounts from those that exist on the master. In this case, you should exclude it from the archive. When the rsync is finish, just login inside mysql and type:

SHOW MASTER STATUS;

Save this info in a txt file inside the slave_db folder that we will use them laster.  After you finish doing this, you can reenable the activity on the master: UNLOCK TABLES;

7)  Stop the server that is to be used as a slave server and add the following to its my.cnf file:

 [mysqld]

server-id=slave_id

 The slave_id value, like the master_id value, must be a positive integer value from 1 to 232 – 1. In addition, it is very important that the ID of the slave be different from the ID of the master. For example:

 [mysqld]

server-id=2

 Remember that server-id must be unique in all the mysql network. 

8) Copy the files over from the slave_db folder to the remote location. You can do this doing the following command:

rsync -e ssh -avz /home/slave_db/ root@REMOTESERVER:/var/lib/mysql

Check that all the permitions and correctly in the /var/lib/mysql folder.Remember files must be own by mysql:mysql

9) Start Mysql and enter to it, write the following changing the values that are needed: 

mysql> CHANGE MASTER TO

    ->     MASTER_HOST='master_host_name',

    ->     MASTER_USER='replication_user_name',

    ->     MASTER_PASSWORD='replication_password',

    ->     MASTER_LOG_FILE='recorded_log_file_name',

    ->     MASTER_LOG_POS=recorded_log_position;

10) type: START SLAVE;