Planet Malaysia Blog

I’m creating “How’s to [tag]Replicate[/tag] MySQL using master and slave in [tag]Linux[/tag]“ after successful implement on my server recently. I have existing MySQL running on port 3306 and the new copy of [tag]MySQL[/tag] 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
$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

log-bin=mysql-bin
binlog-do-db=replDB
server-id = 1

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

-Grant the account the SUPER and RELOAD global privileges.
mysql> GRANT SUPER, RELOAD ON *.* TO ‘repl’@'%.mydomain.com’ 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’@'%.mydomain.com’ IDENTIFIED BY ’slavepassword’;

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

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

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

mysql> show slave status;
| Waiting for master to send event | master.server.com | 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:

• Setup a Linux Highly Availability NFS servers
• I decided to CHANGE
• How to find empty folders on Linux
• How to create full right permission on Samba share
• SSH Remote Access Authentication Tips