Build MariaDB replication on CentOS

I have never configured mysql replication by myself. I have been free for these two days. I installed the free VMWare Workstation Player on my computer, then downloaded the latest version of CentOS and started to configure it. Only the commands used are recorded, right as a running account.

1 Base System#

1.1 system message##

# ip addr //View IP address

1.2 Install required software##

# yum searchifconfig
# yum installnet-tools
# yum installvim
# yum installtelnet
# yum installwget
# yum installgpm     //Mouse driver
# systemctlenable gpm
# systemctlstart gpm

1.3 Three server information##

Main server: 192.168.5.128/24

Slave server 1: 192.168.5.129/24

Slave server 2: 192.168.5.130/24

2 MariaDB

Mainly refer to the third edition of the book "High Performance MySQL". Mysql is introduced in the book, but CentOS integrates the YUM source of MariaDB by default, so I am too lazy to change it, just use MariaDB directly.

2.1 Database installation##

# yum installmariadb-server; mariadb-client
# vim/etc/my.cnf.d/server.cnf
[ mysqld]
init_connect =’SET collation_connection = utf8_unicode_ci’
init_connect =‘SET NAMES utf8’
character-set-server= utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
# vim/etc/my.cnf.d/client.cnf
[ client]default-character-set= utf8
# vim/etc/my.cnf.d/mysql-client.cnf
[ client]default-character-set= utf8
# systemctlstart mariadb
# systemctlenable mariadb  //Automatically start when the system starts//Initialize mysql instance
# mysql_secure_installation
//Allow root remote login
# mysql -uroot-p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY'123456';
mysql> flush privileges ;

2.2 Firewall enable database port##

//Centos7 firewall open port number
# firewall-cmd--zone=public--add-port=3306/tcp --permanent
# firewall-cmd--reload              //Restart firewall
# firewall-cmd--list-ports     //View open ports
# firewall-cmd--state          //View the default firewall status

2.3 MySQL configuration master-slave replication##

2.3.1 All servers create replication accounts###

# mysql -uroot-p
mysql> GRANTREPLICATION SLAVE,-> REPLICATION CLIENT ON *.*-> TO repl@'192.168.5.%' IDENTIFIEDBY 'password';

2.3.2 Main server configuration###

# vim/etc/my.cnf.d/server.cnf
log_bin =mysql-bin
server_id =128//Use the last 8 bits of the IP address directly
sync_binlog =1

2.3.3 Configuration from the server###

# vim/etc/my.cnf.d/server.cnf
# SQL replication slave settings
log_bin =mysql-bin
server_id =129//Use the last 8 bits of the IP address directly
relay_log =/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
read_only =1

2.3.4 Start replication from the server###

# mysql -uroot-p
//Set how to connect to the main library
mysql> CHANGEMASTER TO MASTER_HOST='192.168.5.128',-> MASTER_USER='repl',-> MASTER_PASSWORD='password',-> MASTER_LOG_FILE='mysql-bin.000001',-> MASTER_LOG_POS=0;//Start replication
mysql> startslave;//View replication status
mysql> showslave status \G;
Slave_ IO_Running: Yes
Slave_ SQL_Running: Yes
Seconds_ Behind_Master:0//Check whether the database of the main library has been copied
mysql> showdatabases;
mysql> use …;
mysql> showtables;