Setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu
===================================================================
Our Testing Environment Consists of the Following Machines (Both are CentOS 6) :
Master: 192.168.72.220 ser1.kmi.com
Slave: 192.168.72.221 ser2.kmi.com
Step: 1. Bind Hosts File (Both Server) :
# vi /etc/hosts
192.168.72.220 ser1.kmi.com ser1
192.168.72.221 ser2.kmi.com ser2
-- Save & Quit (:wq)
Step: 2. Stop Iptables & Disabled Selinux (Both Server) :
For CentOS:
# service iptables stop
# chkconfig iptables off
For Ubuntu:
# service ufw stop
# vi /etc/sysconfig/selinux
SELINUX=disabled
-- Save & Quit (:wq)
Step: 3. Restart the Both Server :
# init 6
Step: 4. Install NTP :
# yum -y install ntp
# service ntpd restart
# chkconfig ntpd on
# ntpdate pool.ntp.org
Step: 5. Create MariaDB.repo on Both Server :
For CentOS:
# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB
# vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
-- Save & Quit (:wq)
For Ubuntu :
# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirrors.neusoft.edu.cn/mariadb/repo/10.1/ubuntu trusty main'
Step: 6. Install MariaDB on Both Servers :
For CentOS:
# yum -y install MariaDB-server MariaDB-client
For Ubuntu:
# apt-get update
# apt-get install mariadb-server
Step: 7. Start MariaDB Service & Set MariaDB Root Password on Both Master & Slave Server :
# service mysqld start
# chkconfig mysqld on
# mysql_secure_installation
Step: 8. Check MariaDB Version on Both Node :
# mysql -V
mysql Ver 15.1 Distrib 10.1.10-MariaDB, for Linux (x86_64) using readline 5.1
Step: 9. Configuring MariaDB Server on Master Node :
# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
server_id=1
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
-- Save & Quit (:wq)
Step: 10. Restart the MariaDB Service :
# service mysqld restart
Step: 11. Log on to MariaDB Master Node & Create a User Called "Slave" & Give Appropriate Permission :
# mysql -u root -proot_password
MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MySQL [(none)]> FLUSH PRIVILEGES;
MySQL [(none)]> FLUSH TABLES WITH READ LOCK;
MySQL [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 181 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Step: 12. Now Take Backup of Full Database :
# mysqldump -u root -proot_password employees > /tmp/employees.sql
Step: 13. After Taken Dump, Log on to MariaDB & Unlock Tables :
# mysql -u root -proot_password
MySQL [(none)]> UNLOCK TABLES;
MySQL [(none)]> exit;
Step: 14. Copy the Dump File to the Slave Node :
# scp -r /tmp/employees.sql root@192.168.72.221:/tmp/
Step: 15. Restore the Dump File on Slave Node :
# mysql -u root -predhat
MySQL [(none)]> create database employees;
MySQL [(none)]> exit;
# mysql -u root -proot_password employees < /tmp/employees.sql
Step: 16. Configuring MariaDB on Slave Node :
Note: It needs to be a Different Integer than 1, as we used 1 in the Master:
# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
server_id=2
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
-- Save & Quit (:wq)
Step: 17. Restart the MariaDB Server :
# service mysqld restart
Step: 18. Setting Up MariaDB Replication on Slave Node :
Note: Special attention to the "MASTER_LOG_FILE" & "MASTER_LOG_POS" Variables, which should match the Values returned by "SHOW MASTER STATUS" in STEP 10 of above.
# mysql -u root -proot_password
MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.72.220',
MASTER_USER='slave',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=181,
MASTER_CONNECT_RETRY=10;
Step: 19. Start the Slave & Check its Status :
MySQL [(none)]> START SLAVE;
MySQL [(none)]> SHOW SLAVE STATUS\G;
Note: If the SHOW SLAVE STATUS\G; command returns any Errors. Use those errors to troubleshoot & then run START SLAVE; to test again.
Step: 20. Test MariaDB Database Replication :
Add some Record to the "employees" Database in the Master Node :
MySQL [(none)]> use employees;
MySQL [(none)]> CREATE TABLE Guests1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);
Verify that this Change was Replicated in the Slave Node :
MySQL [(none)]> USE employees;
MySQL [(none)]> show tables;
As you can see, Replication is Working Correctly from Master to Slave.
Done...!!!
===================================================================
Our Testing Environment Consists of the Following Machines (Both are CentOS 6) :
Master: 192.168.72.220 ser1.kmi.com
Slave: 192.168.72.221 ser2.kmi.com
Step: 1. Bind Hosts File (Both Server) :
# vi /etc/hosts
192.168.72.220 ser1.kmi.com ser1
192.168.72.221 ser2.kmi.com ser2
-- Save & Quit (:wq)
Step: 2. Stop Iptables & Disabled Selinux (Both Server) :
For CentOS:
# service iptables stop
# chkconfig iptables off
For Ubuntu:
# service ufw stop
# vi /etc/sysconfig/selinux
SELINUX=disabled
-- Save & Quit (:wq)
Step: 3. Restart the Both Server :
# init 6
Step: 4. Install NTP :
# yum -y install ntp
# service ntpd restart
# chkconfig ntpd on
# ntpdate pool.ntp.org
Step: 5. Create MariaDB.repo on Both Server :
For CentOS:
# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB
# vi /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
-- Save & Quit (:wq)
For Ubuntu :
# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirrors.neusoft.edu.cn/mariadb/repo/10.1/ubuntu trusty main'
Step: 6. Install MariaDB on Both Servers :
For CentOS:
# yum -y install MariaDB-server MariaDB-client
For Ubuntu:
# apt-get update
# apt-get install mariadb-server
Step: 7. Start MariaDB Service & Set MariaDB Root Password on Both Master & Slave Server :
# service mysqld start
# chkconfig mysqld on
# mysql_secure_installation
Step: 8. Check MariaDB Version on Both Node :
# mysql -V
mysql Ver 15.1 Distrib 10.1.10-MariaDB, for Linux (x86_64) using readline 5.1
Step: 9. Configuring MariaDB Server on Master Node :
# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
server_id=1
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
-- Save & Quit (:wq)
Step: 10. Restart the MariaDB Service :
# service mysqld restart
Step: 11. Log on to MariaDB Master Node & Create a User Called "Slave" & Give Appropriate Permission :
# mysql -u root -proot_password
MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MySQL [(none)]> FLUSH PRIVILEGES;
MySQL [(none)]> FLUSH TABLES WITH READ LOCK;
MySQL [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 181 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Step: 12. Now Take Backup of Full Database :
# mysqldump -u root -proot_password employees > /tmp/employees.sql
Step: 13. After Taken Dump, Log on to MariaDB & Unlock Tables :
# mysql -u root -proot_password
MySQL [(none)]> UNLOCK TABLES;
MySQL [(none)]> exit;
Step: 14. Copy the Dump File to the Slave Node :
# scp -r /tmp/employees.sql root@192.168.72.221:/tmp/
Step: 15. Restore the Dump File on Slave Node :
# mysql -u root -predhat
MySQL [(none)]> create database employees;
MySQL [(none)]> exit;
# mysql -u root -proot_password employees < /tmp/employees.sql
Step: 16. Configuring MariaDB on Slave Node :
Note: It needs to be a Different Integer than 1, as we used 1 in the Master:
# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
server_id=2
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
-- Save & Quit (:wq)
Step: 17. Restart the MariaDB Server :
# service mysqld restart
Step: 18. Setting Up MariaDB Replication on Slave Node :
Note: Special attention to the "MASTER_LOG_FILE" & "MASTER_LOG_POS" Variables, which should match the Values returned by "SHOW MASTER STATUS" in STEP 10 of above.
# mysql -u root -proot_password
MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.72.220',
MASTER_USER='slave',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=181,
MASTER_CONNECT_RETRY=10;
Step: 19. Start the Slave & Check its Status :
MySQL [(none)]> START SLAVE;
MySQL [(none)]> SHOW SLAVE STATUS\G;
Note: If the SHOW SLAVE STATUS\G; command returns any Errors. Use those errors to troubleshoot & then run START SLAVE; to test again.
Step: 20. Test MariaDB Database Replication :
Add some Record to the "employees" Database in the Master Node :
MySQL [(none)]> use employees;
MySQL [(none)]> CREATE TABLE Guests1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);
Verify that this Change was Replicated in the Slave Node :
MySQL [(none)]> USE employees;
MySQL [(none)]> show tables;
As you can see, Replication is Working Correctly from Master to Slave.
Done...!!!
