Install MariaDB Master Slave Replication on Centos 7

1. Install MySQL / MariaDB

Once you have your MariaDB.repo entry, add it to a file under /etc/yum.repos.d/. (We suggest something like /etc/yum.repos.d/MariaDB.repo.)

An example MariaDB.repo file for CentOS 7 is:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Installing MariaDB is as simple as running just one command:

yum install MariaDB-server MariaDB-client

If the server already has the MariaDB-Galera-server package installed, you might need to remove it prior to installing MariaDB-server (with 'sudo yum remove MariaDB-Galera-server'). No databases are removed when the MariaDB-Galera-server rpm package is removed, but as with any upgrade, it is best to have backups.

And then start MySQL, now MariaDB:

systemctl start mariadb

Be sure that MySQL/MariaDB starts at boot:

systemctl enable mariadb

To check the status of MySQL/MariaDB:

systemctl status mariadb

To stop MySQL/MariaDB:

systemctl stop mariadb

Check the installation with the command client:

mysql

Secure your Database Server:

mysql_secure_installation

The first and foremost step is to allow mysql default port “3306” through Firewall or Router.

As we use CentOS 7, we can allow the port as shown below.

firewall-cmd --permanent --add-port=3306/tcp

Reload firewall rules using command:

firewall-cmd --reload

Note:

Master: 192.168.235.130
Slave: 192.168.235.131


2. DB TEST

Setting Up a Sample MySQL Database on Master

# git clone https://github.com/datacharmer/test_db
# cd test_db
# mysql < employees.sql

3. Configuring MySQL Server on Master
Edit file /etc/my.cnf.d/server.cnf

##############
log-error = /var/log/mysql/mysql.err
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
log_bin=binlog
max_connections = 1000
max_allowed_packet=32M
default-storage-engine=innodb
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2

key_buffer = 512M
table_cache = 1000
sort_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
tmp_table_size=20M
max_heap_table_size=20M
thread_cache_size = 64
innodb_buffer_pool_size = 512M
symbolic-links=0
##########################
#master
gtid_domain_id=1
server_id=1
binlog_format=ROW
log_slave_updates=1
expire_logs_days=7
# Allow server to accept connections on all interfaces.
#
bind-address=192.168.235.130

Create the user slave and assign the necessary grants:

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;

Take a snapshot of the employees database.

# mysqldump -u root -p employees > employees-dump.sql

After the dump is completed, connect to the database server again to unlock the tables and then exit:

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;

Copy the dump to the slave:

#scp employees-dump.sql root@192.168.0.19:/root/
rsync -avz employees-dump.sql hungnv@192.168.235.131:/home/hungnv/

Run the mysql_upgrade procedure to upgrade the system tables

# mysql_upgrade -u root -p

4. Configuring MySQL Server on Slave

Create the user and an empty database:

MariaDB [(none)]> CREATE DATABASE employees;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

Restore DATABASE:

# mysql -u root -p employees < employees-dump.sql

Edit file config: /etc/my.cnf.d/server.cnf

[mysqld]
log-error = /var/log/mysql/mysql.err
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
log_bin=binlog
max_connections = 1000
max_allowed_packet=32M
default-storage-engine=innodb
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2

key_buffer = 512M
table_cache = 1000
sort_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
tmp_table_size=20M
max_heap_table_size=20M
thread_cache_size = 64
innodb_buffer_pool_size = 512M
symbolic-links=0
############
#slave
gtid_domain_id=1
log_slave_updates=1
expire_logs_days=7
server_id=1001
binlog_format=ROW
slave_net_timeout=60
read_only = 1
# Allow server to accept connections on all interfaces.
#
bind-address=192.168.235.131

Restart the database server:

# systemctl restart mariadb

Run the mysql_upgrade procedure to upgrade the system tables (you will be prompted to enter the MariaDB root password):

# mysql_upgrade -u root -p

Start Slave:

MariaDB [(none)]>
CHANGE MASTER TO
MASTER_HOST='192.168.235.130',
MASTER_USER='slave',
MASTER_PASSWORD='SlavePassword',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=67980587,
MASTER_CONNECT_RETRY=10,
MASTER_USE_GTID=current_pos;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;

5. TEST
Add a record to the employees table in the Master server:

MariaDB [(none)]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (500000, '1983-07-12', 'Dave', 'Null', 'M', '2014-12-12');

Then verify that this change was replicated in the slave:

MariaDB [(none)]> USE employees;
MariaDB [(none)]> SELECT * FROM employees WHERE emp_no=500000;

6. Repair Replication
MASTER LOCK Read & Backup

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;
[root@db01 hungnv]# mysqldump -uroot -p employees > employees-dump.sql
scp employees-dump.sql hungnv@192.168.235.131:/home/hungnv/

Recreate the database in slave server:

MariaDB [(none)]>stop slave;
MariaDB [(none)]> drop database employees;
Query OK, 8 rows affected (0.56 sec)

MariaDB [(none)]> create database employees;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

Restore DB

[root@db02 hungnv]# mysql -uroot -p employees < employees-dump.sql

Start SLAVE

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.235.130',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='SlavePassword',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='binlog.000004',
    -> MASTER_LOG_POS=1154;
MariaDB [(none)]> START SLAVE;

Verify:

MariaDB [(none)]> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Unlock Master

MariaDB [(none)]>UNLOCK TABLES;