Azure & Office 365

Posts Tagged ‘Mysql Replication’

Hello Everyone,

Today we are doing mysql replication, for Mysql installation you can refer my previous blogs

for this setup we need to VM installed Mysql, server which contain primary DB will be “Master” & server which has replica of the DB know as a “Slave”

lets start the replication.

=====================================================================

Configure MySQL Master

1) Need to edit “/etc/my.cnf”

[mysqld]
server-id = 1
binlog-do-db=otrs #dbname
expire-logs-days=7
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = mysql-bin

2) Restart the mysql service

# service mysqld restart

3) create a Slave user and password. For instance

[root@OtrsMaster ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

4)

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘centos’@’%’ IDENTIFIED BY ‘centos’;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

5) Applied Read only lock on slave

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

5)
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 106 | otrs | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

6) Note down the file (mysql-bin.000001) and position number (106)

7) Backup Master server database

# mysqldump –all-databases –user=root –password –master-data > masterdatabase.sql

8) After taking the backup Again login to MySQL as root user and unlock the tables

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

9) Copy the masterdatabase.sql file to your Slave server using SCP
scp masterdatabase.sql [email protected]:/root

Configure MySQL Slave

1) Need to edit “/etc/my.cnf”
[mysqld]
server-id = 2
master-host=192.168.0.200
master-connect-retry=60
master-user=sk
master-password=centos
replicate-do-db=otrs
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = mysql-bin
max_allowed_packet=20M
query_cache_size=32M
innodb_buffer_pool_size = 256M
innodb_log_file_size = 512M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

2) Import the master database:

mysql -u root -p < /root/masterdatabase.sql

3) # service mysqld restart

4) Log in to the MySQL & run the below commands

mysql> SLAVE STOP;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.2.220′, MASTER_– USER=’centos’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=20249;
Query OK, 0 rows affected (0.03 sec)

mysql> SLAVE START;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.220
Master_User: centos
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 20249
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 4941
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: 20249
Relay_Log_Space: 5096
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:
1 row in set (0.00 sec)
mysql>

If your application & DB server are the same then installed the application on slave server before adding in to it Mysql Slave.

& rsync your application folders with slave server in scenario you will get Mysql replication as well as application  server

[root@OtrsMaster ~]# crontab -l
*/10 * * * * /usr/bin/rsync -avr –progress –delete //var/article/* [email protected]:/opt/var/article/ 

Fail-over Master to slave

1)flush log if server was master after booting you have to run below command
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

2)
mysql> SLAVE STOP;
Query OK, 0 rows affected (0.01 sec)
mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

3)
mysql>CHANGE MASTER TO MASTER_HOST=’192.168.0.220′;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 20425 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
mysql>exit

After doing all these settings restart the applications services

[root@OtrsMaster ~]# /etc/init.d/httpd restart
Stopping httpd: [ OK ]
Starting httpd: [ OK ]

To make failed server (after rebuilding) again MySQL slave please follow “Configure MySQL Slave” section.

–Sachin.