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-clientIf 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:
mysqlSecure your Database Server:
mysql_secure_installationThe 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/tcpReload firewall rules using command:
firewall-cmd --reloadNote:
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.sqlAfter 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.sqlEdit 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.sqlStart 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;
