Configure Master and Slave database replication using mysqlnd plugin
Mysql replication is a process that allows you to maintain the multiple copy of mysql data. With the help of mysqlnd plugin we can divide the load of mysql. We can send select query to slave server and other queries to master server.
Server Details:
Web Server: 192.168.0.152 Master Server: 192.168.0.150 Slave Server: 192.168.0.151 MyDB: techoism
Step 1: First install below repository on master and slave server.
CentOS/RHEL 6, 32 Bit (i686): # rpm -Uvh http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.i686.rpm CentOS/RHEL 6, 32 Bit (i386): # rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm CentOS/RHEL 6, 64 Bit (x86_64): # rpm -Uvh http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm CentOS/RHEL 6, 64 Bit x86_64): # rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Step 2: Follow below steps on Master and Slave Server:
# vim /etc/my.cnf # #old_passwords=1
# /etc/init.d/mysql restart
Step 3: If we are using different server for database then we will follow below steps on webserver. We don’t need to follow these steps on any database server.
# yum remove php-mysql
# yum install php-mysqlnd --enablerepo=remi
# yum install php-pecl-apc --enablerepo=remi
# vim /etc/php.d/mysqlnd_ms.ini # mysqlnd_ms.config_file=/etc/mysqlnd_ms_php.ini # mysqlnd_ms.enable=1 # extension=mysqlnd_ms.so
#
{
"techoism_db_repl": {
"master": {
"master_0": {
"host": "master_db_IP",
"port": "3306"
}
},
"slave": {
"slave_0": {
"host": "slave_db_IP",
"port": "3306"
}
}
}
}
# /etc/init.d/httpd restart
Step 4: On Master Server
# mysql> CREATE USER 'new_user'@'%' IDENTIFIED BY 'secretpassword'; # mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
# mysql>GRANT REPLICATION SLAVE ON *.* TO 'new_user'@'Slave_Server_IP' IDENTIFIED BY 'Password'; # mysql>FLUSH PRIVILEGES;
# mysql> use techoism; # mysql>FLUSH TABLES WITH READ LOCK; # mysql>exit;
# vim /etc/my.cnf
[mysqld] binlog_format = row relay_log = mysql-relay-bin log_slave_updates = 1 log-bin=mysql-bin binlog-do-db=techoism server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1
# service mysqld restart
# mysql>SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 107 | techoism | | +------------------+----------+--------------+------------------+
The result is showing that the current binary file is mysql-bin.000002 and position is 107. Note down these values to use on slave server.
# mysqldump -u root -p techoism > techoism.sql # scp techoism.sql 192.168.0.151:/opt
# mysql>UNLOCK TABLES;
Step 5: On Slave Server
[mysqld] server-id=2 replicate-do-db=techoism
# service mysqld restart
# mysql -u new_user -p techoism < techoism.sql
# mysql -u root -p
# mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.150',
> MASTER_USER='new_user',
> MASTER_PASSWORD='secretpassword',
> MASTER_LOG_FILE='mysql-bin.000002',
> MASTER_LOG_POS=107;
# mysql> SLAVE START;
# mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.150
Master_User: new_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
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: techoism
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: 107
Relay_Log_Space: 107
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>
No Responses