How’s to Replicate MySQL

Posted by Planet Malaysia on June 21, 2006


I’m creating “How’s to Replicate MySQL using master and slave in Linux” after successful implement on my server recently. I have existing MySQL running on port 3306 and the new copy of MySQL running on port 3307 with replicate master and slave

Introduction to Replication
MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster

In single-master replication, the master server writes updates to its binary log files and maintains an index of those files to keep track of log rotation. The binary log files serve as a record of updates to be sent to any slave servers. When a slave connects to its master, it informs the master of the position up to which the slave read the logs at its last successful update. The slave receives any updates that have taken place since that time, and then blocks and waits for the master to notify it of new updates.

*Download and unzip MySQL File
$tar xvfz mysql-x.x.xx.tar.gz

*Compile MySQL
$cd mysql-x.x.xx
$./configure –prefix=/usr/local/mysql –with-unix-socket-path=/usr/local/mysql –with-tcp-port=3307
$make install

*Initialise the database
$export MYSQL_UNIX_PORT=/usr/local/mysql/mysql.sock
$export MYSQL_TCP_PORT=3307
$/usr/local/mysql/bin/mysql_install_db –user=mysql –basedir=/usr/local/mysql –ldata=/usr/local/mysql/var

*Startup MySQL manually
/usr/local/mysql/bin/mysqld_safe –datadir=/usr/local/mysql/var –socket=/usr/local/mysql/mysql.sock –port=3307 &

*Login to MySQL
/usr/local/mysql/bin/mysql –port=3307 –socket=/usr/local/mysql/mysql.sock -u root -p

MySQL MASTER Server setting
-Create /usr/local/mysql/var/my.cnf

server-id = 1

-Create Replication User in MySQL
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’’ IDENTIFIED BY ‘slavepassword’;

-Grant the account the SUPER and RELOAD global privileges.
mysql> GRANT SUPER, RELOAD ON *.* TO ‘repl’@’’ IDENTIFIED BY ‘slavepassword’;

-Grant the SELECT privilege for all tables that you want to load. Any master tables from which the account cannot SELECT will be ignored by LOAD DATA FROM MASTER.
mysql> GRANT SELECT ON *.* TO ‘repl’@’’ IDENTIFIED BY ‘slavepassword’;

MySQL SLAVE Server Setting
-Edit MySQL configuration setting
$vi /usr/local/mysql/var/my.cnf

server-id = 2
master-host =
master-user = repl
master-password = slavepassword
master-port = 3307 #default MySQL already used port 3306)

-Show status information on essential parameters of the slave threads (Slave).

mysql> show slave status;
| Waiting for master to send event | | repl | 3307 | 60 | mysql-bin.000001 | 576 | slave-relay-bin.000001 | 544 | mysql-bin.000001 | Yes | Yes |

-Look at something similar as below and please make no error.

-Takes a snapshot of the master and copies it to the slave.

mysql> load data from master;

Gongratulation! You’re done and good luck!

Possibly Related Posts:


3 Responses to “How’s to Replicate MySQL”

  1. PlanetMalaysia Team on July 20th, 2006 12:28 pm

    Howtoforge have more details about this topic at URL:

  2. 2ge on November 17th, 2008 1:56 pm

    I think it is better to have master on port 3306.

  3. guide on September 12th, 2009 1:25 pm

    nice site, very informative, well designed, easy to use … what can i say ? i love it…

Leave a Reply

Planet Malaysia

  • Follow us on Twitter

  • web