hostgliders
New member
MySQL master slave replication gives you two copies of your database: the “live” one and the backup one. You always write your data to your master and read from the master too, but you will always have an up-to-date copy on your slave.
Setting up the master Server
Make sure you have updated packages and MYSQL server isntalled in the server
#
#
Open the my.cnf file, which contains MYSQL database configuration
Add following lines
Restart mysql service
Login to MYSQL with MYSQL root password
Grand Access to your slave server
[/B]
Replace the IP address (192.168.1.60)with your slave’s IPv4 address and replace ‘yourpassword’ with a strong password. Execute the query. It should say ‘Query OK’.
Check the current binary log file name (File) and current offset (Position) value using following command.
Please note the filename (‘File’) and number (‘Position’). Remember these or write them down. You will use this to start replication on the slave.
Take a backup of database and copy it to slave mysql server.
Setup MySQL Slave Server
Make sure you have updated packages and MYSQL server isntalled in the server
Edit salve mysql configuration file and add following values under [mysqld] section.
Restart mysql service
Restore database backup taken from master server.
Setup option values on slave server using following command.
Finally start the slave thread
Check the status of slave server.
MySQL Master-slave Replication has been configured successfully on your system . You can test the same by creating a test datatabse in Master server, so it will automatically copied to slave server.
Setting up the master Server
Make sure you have updated packages and MYSQL server isntalled in the server
#
Code:
yum update
Code:
yum install install mysql-serverOpen the my.cnf file, which contains MYSQL database configuration
Code:
[B]vi /etc/my.cnf[/B]Add following lines
Code:
[B][mysqld]
log-bin=mysql-bin
binlog-do-db=mydb1
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1[/B]Restart mysql service
Code:
[B]service mysqld restart[/B]Login to MYSQL with MYSQL root password
Code:
[B]mysql -u root -p[/B]Grand Access to your slave server
Code:
[B]GRANT REPLICATION SLAVE ON *.* to ‘replication’@192.168.1.60 IDENTIFIED BY ‘yourpassword’;
mysql> FLUSH PRIVILEGES;Replace the IP address (192.168.1.60)with your slave’s IPv4 address and replace ‘yourpassword’ with a strong password. Execute the query. It should say ‘Query OK’.
Check the current binary log file name (File) and current offset (Position) value using following command.
Code:
[B]mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000109 | 187 | mydb1 | |
+------------------+----------+--------------+------------------+[/B]Please note the filename (‘File’) and number (‘Position’). Remember these or write them down. You will use this to start replication on the slave.
Take a backup of database and copy it to slave mysql server.
Code:
[B] mysqldump -u root -p mydb > mydb1.sql[/B]
# [B]scp mydb1.sql 192.168.1.60:/opt/[/B]Setup MySQL Slave Server
Make sure you have updated packages and MYSQL server isntalled in the server
Code:
# yum update
#yum install install mysql-serverEdit salve mysql configuration file and add following values under [mysqld] section.
Code:
[mysqld]
server-id=2
replicate-do-db=mydb1Restart mysql service
Code:
service mysqld restartRestore database backup taken from master server.
Code:
# mysql -u root -p mydb < mydb.sqlSetup option values on slave server using following command.
Code:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.20',
-> MASTER_USER='replication',
-> MASTER_PASSWORD='secretpassword',
-> MASTER_LOG_FILE='mysql-bin.000109',
-> MASTER_LOG_POS=187;Finally start the slave thread
Code:
mysql> SLAVE START;Check the status of slave server.
Code:
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.60
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000109
Read_Master_Log_Pos: 187
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: mydb
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: 187
Relay_Log_Space: 187
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: NULL
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>MySQL Master-slave Replication has been configured successfully on your system . You can test the same by creating a test datatabse in Master server, so it will automatically copied to slave server.
