Setup MySQL master-slave replication

From ezUnix
Jump to: navigation, search
                                    pdf_icon.png Download this article as a single PDF document 

Introduction

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).


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.


  • Open the my.cnf file, usually /etc/my.cnf or /etc/mysql/my.cnf (i.e Debian).
  • Type in the following, somewhere below “[mysqld]”
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
  • Restart MySQL on the master server.
  • Create a new user on the master server with the “REPLICATION SLAVE” privelege.


You don’t need to assign any other privileges 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';
  • Execute ‘FLUSH TABLES WITH READ LOCK;’ on the master to prevent writing to the databases.
  • Execute ‘SHOW MASTER STATUS;’ on the master and note down the values because we’ll use these later.
  • Execute ‘UNLOCK TABLES;’ on the master.
  • Open the my.cnf on the slave server.
  • Enter somewhere below “[mysqld]” on the slave server:
server-id=2
  • Save the file and restart mysqld.
  • 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_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=98;


  • Execute the following on the slave server:
START SLAVE;
  • 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.


Links



That's all folks. Marcin


<comments />

Septi said ...

<comment date="2012-07-11T19:19:15Z" name="Septi"> Bin Li,The slave should udtape almost immediately after the master. It is constantly polling the master for udtapes. The data is sent binary (i think) from the master and allows it to udtape the database much faster than a regular query would. The slave will probably udtape in less than a second after the master has changes. You can probably find some benchmarks if you do some googling </comment>

Rozilene said ...

<comment date="2012-07-14T08:14:31Z" name="Rozilene"> Pretty good post. I just stumbled upon your blog and waetnd to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. </comment>

BellaNana said ...

<comment date="2012-10-13T09:43:31Z" name="BellaNana"> Personally I use `prompt mysql \D > ` which helps me know when certain qureies were executed and at what time (if you store the new line after the query finished. The reason? I like to be able to know compare qureies when testing and knowing what changes were done when you can easily go back in time to remember what you had done. </comment>

YazzY said ...

<comment date="2012-11-01T20:06:43Z" name="YazzY" signature="YazzY"> Thanks for the \D tip BellaNana. </comment>