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
$./configure –prefix=/usr/local/mysql –with-unix-socket-path=/usr/local/mysql –with-tcp-port=3307
*Initialise the database
$/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
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
server-id = 2
master-host = master.server.com
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 | 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:
- How to Install Webmin on OpenFiler
- lppasswd: Unable to open passwd file: Permission denied
- Missing /var/log/lastlog
- Telnet service_limit error
- How To Capture PUTTY Session Log