siosios

Main steps



Note: Consider server1 as master and server2 as slave and replicating database as exampledb.

On Server 1(Master)

1) Configuring the Master


? To make sure that the replication can work, we must make MySQL listen on all interfaces on the master (server1), therefore we comment out the linebind-address = 127.0.0.1in/etc/mysql/my.cnf:


Eg: #bind-address = 127.0.0.1


? We have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the databaseexampledb


Eg:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = exampledb




2) Restart MySQL server


3) Set up a replication userslave_userthat can be used byserver2to access the MySQL database onserver1:


mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;


4) Find out the master status of the server1 and create a SQL dump of the database.


mysql> USE exampledb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;


In another shell,
Ex:
Shell> mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql


5) Unlock tables
mysql> UNLOCK TABLES;


On server 2(Slave)

1) Configure slave server by changing my.cnf file

Ex:
server-id=2
master-connect-retry=60
replicate-do-db=exampledb


2) Restart MySQL server


3) Import the SQL dump


Ex:
Shell> mysql -u root -pyourrootsqlpassword exampledb < snapshot.sq


4) Give slave parameters


Ex:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100',
MASTER_USER='slave_user', MASTER_PASSWORD='slave_password',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=19467;


5) Start slave


mysql> START SLAVE;

6) Check slave status (It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output. And also Slave_IO_State should be Waiting for master to send event)

mysql> SHOW SLAVE STATUS \G




Ex:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.100
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 19467
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: exampledb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 19467
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error: