-
Mysql Database Replication Master-Slave
The best advantage of master slave is to use master for all inserts and send some select queries to slave. This practice may increase speed of your application without going into optimization of all queries.
Master……………………………….>Slave
Suppose you have the following two Mysql servers :
Master Server >> IP : 10.0.0.4
Slave server >> IP : 10.0.0.5Configure The Master:
Edit /etc/my.cnf
#skip-networking
#bind-address = 127.0.0.1
log-bin = /var/lib/mysql/mysql-bin.log
binlog-do-db=DB_NAME
server-id=1# /etc/init.d/mysql restart
#Then we log into the MySQL database as root and create a user with replication privileges as following:mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user‘@’10.0.0.5‘ IDENTIFIED BY ‘Password-Here‘;
mysql> FLUSH PRIVILEGES;
mysql> USE DB_NAME;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;The last command will show something like this:
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.000001 | 750096359| DB_NAME | |
+——————+———–+————–+——————+mysqldump DB_NAME >/backup/DB_NAME.sql
mysql> UNLOCK TABLES;##move mysql dump file to DB Slave server (10.0.0.5):
rsync -avprP -e ssh /backup/DB_NAME.sql 10.0.0.5:/backup/Configure The Slave :
login to Mysql server and create the DB :
mysql> CREATE DATABASE DB_NAME;
mysql> quit;Then restore DB :
mysql -u root -proot_password DB_NAME< /backup/DB_NAME.sql
##Copy my.cnf from Master DB server and modify the following :
#server-id=1
server-id=2
/etc/init.d/mysql restartFinally :
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST=’10.0.0.4‘, MASTER_USER=’slave_user‘, MASTER_PASSWORD=’Password-Here‘, MASTER_LOG_FILE=’mysql-bin.000001‘, MASTER_LOG_POS=750096359;
mysql> START SLAVE;
mysql> show slave status G*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 842111916
Relay_Log_File: AKdb2-relay-bin.000002
Relay_Log_Pos: 92015810
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 842111916
Relay_Log_Space: 92015966
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>That is all what you have to do 🙂
Resize Xen Loop Disk Image Allow Pings (ICMP Echo Request) Through Your Windows Server 2008
Mysql Database Replication Master-Slave
Recent Posts
Categories
Archives
Comments are currently closed.