How to install and configure MySQL Master-Slave replication on a CentOS 7 VPS

Datetime:2016-08-22 23:56:50          Topic: MySQL  Centos           Share

In this tutorial we’ll explain how to install and configure MySQL Master-Slave replication on aCentOS 7 VPS . The following MySQL replication is based on binary log position, which means it is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database.

REQUIREMENTS

The following are MySQL replication system requirements for this tutorial:

MYSQL MASTER VPS

0. SSH TO YOUR MASTER CENTOS VIRTUAL SERVER

First thing to do is to login to your MySQL master virtual server via SSH and optionally fire up a screen/tmux session. For example:

ssh YOUR_MASTER_VPS_IP -p YOUR_MASTER_VPS_SSH_PORT
screen -U -S mysql-master-replication

1. UPDATE YOUR MASTER CENTOS VPS

Before proceeding further, it’s recommended to fully update yourvirtual server using the package manager i.e yum

yum clean all
yum update

2. INSTALL MYSQL ON MASTER VPS

Next, install the MySQL/MariaDB database server on your master hosting server using yum as in:

yum install mariadb-server vim

3. MASTER MYSQL SERVER CONFIGURATION

This MySQL master setup configuration has its data stored under /mnt/mysql-data on a separate partition, so let’s set its configuration up using the following:

of course feel free to tune the configuration to suit your needs

mv /etc/my.cnf{,.orig}
vim /etc/my.cnf
[mysqld]
bind-address = 0.0.0.0
auto_increment_increment        = 1
log-err             = /mnt/mysql-data/log/mysql.err
slow_query_log_file = /mnt/mysql-data/log/slow_queries.log
long_query_time     = 2
port                = 3306
max_connections     = 1200
datadir             = /mnt/mysql-data
tmpdir              = /dev/shm
skip-external-locking
skip-name-resolve
#skip-bdb
group_concat_max_len = 131072
character-set-server=utf8

## MASTER REPLICATION ##
#Server ID coming from SLAVE server
server-id=88
binlog-format   = mixed
log-bin=master-mysql-server.bin
binlog-ignore-db="mysql"
innodb_flush_log_at_trx_commit=1
sync_binlog=1
transaction-isolation = READ-COMMITTED
#read-only
expire_logs_days = 7

[mysqldump]
quick
max_allowed_packet      = 50M

[mysql]
max_allowed_packet      = 50M

[isamchk]
key_buffer = 512M

[myisamchk]
sort_buffer_size = 512M
read_buffer_size = 16M
write_buffer_size = 16M

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

4. SETUP MYSQL MASTER LOGS

mkdir -p /mnt/mysql-data/log
touch /mnt/mysql-data/log/{mysql.err,slow_queries.log}
chown mysql: -R /mnt/mysql-data/

5. START MYSQL SERVICE

Now, let’s s tart the MySQL master server and add it to yourvps startup using:

systemctl restart mariadb
systemctl enable mariadb

6. SETUP ~/.my.cnf (OPTIONAL)

This step is optional whether you like to setup a ~/.my.cnf in your user’s home directory. For example:

vim ~/.my.cnf

[client]
user=root
password=
socket=/mnt/mysql-data/mysql.sock

7. SETUP SYM LINK FOR MYSQL.SOCK

ln -s /var/lib/mysql/mysql.sock /mnt/mysql-data/mysql.sock

8. RUN POST INSTALLATION SCRIPT

mysql_secure_installation

9. CREATE NEW DATABASE

mysql -u root -p
mysql> create database replica;
mysql> \q

10. SETUP MYSQL USER USED BY SLAVE SERVER TO CONNECT TO MASTER

GRANT REPLICATION SLAVE ON *.* TO [email protected]

IDENTIFIED BY 'StrAsnd9d263gPASdt';

192.168.88.101 is our slave vps ip address

11. CREATE THE BACKUP FILE

Next, create the backup file with the binlog position. It will affect the performance of your database server, but it won’t lock the tables

mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > /mnt/dump.sql

or transfer it directly to a remote server via SSH:

mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A | ssh @ 'cat > /mnt/mysql-data/master-sqldump/dump.sql'

12. EXAMINE THE BIN LOG FILES

Now, examine the head of the log file file and write down the values for master_log_file and master_log_pos

head /mnt/dump.sql -n80 | grep "MASTER_LOG_POS"

-- CHANGE MASTER TO MASTER_LOG_FILE='master-mysql-server.000019', MASTER_LOG_POS=106;

MYSQL SLAVE VPS

0. SSH TO YOUR SLAVE CENTOS VIRTUAL SERVER

Ok, now login to your MySQL slave virtual server via SSH and optionally fire up a screen/tmux session. For example:

ssh YOUR_SLAVE_VPS_IP -p YOUR_SLAVE_VPS_SSH_PORT
screen -U -S mysql-slave-replication

1. UPDATE YOUR SLAVE CENTOS VPS

Before proceeding further, it’s recommended to fully update yourvirtual server using the package manager i.e yum

yum clean all
yum update

2. INSTALL MYSQL ON SLAVE VPS

Next, install the MySQL/MariaDB database server on yourslave hosting server using yum as in:

yum install mariadb-server vim

3. SLAVE MYSQL SERVER CONFIGURATION

This MySQL slave setup configuration has its data stored under /mnt/mysql-data on a separate partition, so let’s set its configuration up using the following:

of course feel free to tune the configuration to suit your needs

mv /etc/my.cnf{,.orig}
vim /etc/my.cnf
[mysqld]
bind-address = 0.0.0.0
auto_increment_increment        = 1
log-err         = /mnt/mysql-data/log/mysql.err
port            = 3306
max_connections = 1200
datadir         = /mnt/mysql-data
tmpdir          = /dev/shm
skip-external-locking
skip-name-resolve
#slave-skip-errors = 1032
#skip-bdb
group_concat_max_len = 131072
character-set-server=utf8

## SLAVE REPLICATION ##
#Server ID coming from MASTER server
server_id=99
log-bin   = slave-mysql-server.bin
relay-log = slave-mysql-server-relay.bin
log-slave-updates = 1
read-only = 1
binlog-format=mixed
transaction-isolation = READ-COMMITTED
expire_logs_days = 7

[mysqldump]
quick
max_allowed_packet      = 50M

[mysql]
max_allowed_packet      = 50M

[isamchk]
key_buffer = 512M

[myisamchk]
sort_buffer_size = 512M
read_buffer_size = 16M
write_buffer_size = 16M

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

4. SETUP MYSQL SLAVE LOGS

mkdir -p /mnt/mysql-data/log
touch /mnt/mysql-data/log/{mysql.err,slow_queries.log}
chown mysql: -R /mnt/mysql-data/

5. START MYSQL SERVICE

Now, let’s start the MySQL slave server and add it to yourvps startup using:

systemctl restart mariadb
systemctl enable mariadb

6. SETUP ~/.my.cnf (OPTIONAL)

This step is optional whether you like to setup a ~/.my.cnf in your user’s home directory. For example:

vim ~/.my.cnf

[client]
user=root
password=
socket=/mnt/mysql-data/mysql.sock

7. SETUP SYM LINK FOR MYSQL.SOCK

ln -s /var/lib/mysql/mysql.sock /mnt/mysql-data/mysql.sock

8. RUN POST INSTALLATION SCRIPT

mysql_secure_installation

9. IMPORT THE DATABASE DUMP ON THE SLAVE SERVER

Next, import the transferred sql dump on the slavevirtual server using:

mysql < /path/to/dump.sql

10. SETUP MYSQL REPLICATION

mysql -u root -p
CHANGE MASTER TO MASTER_HOST='192.168.88.100',MASTER_USER='replicant',MASTER_PASSWORD='StrAsnd9d263gPASdt',MASTER_LOG_FILE='master-mysql-server.000005', MASTER_LOG_POS=245;

192.168.88.100 is our master vps ip address, also MASTER_LOG_FILE and MASTER_LOG_POS are the ones we got from the bin log file on the master server

now, start the replication by executing the following command in your MySQL shell:

START SLAVE;

11. CHECK REPLICATION STATUS

You can check replication status by running the following command in your MySQL shell:

SHOW SLAVE STATUS \G;

If all is well, Last_Error will be blank, and Slave_IO_State will report ‘Waiting for master to send event’. Look for Seconds_Behind_Master which indicates how far behind it is. It took me a few hours to accomplish all of the above, but the slave caught up in a matter of minutes

12. REPLICATION ERROR NOTES

Sometimes errors occur in replication. For example, if you accidentally change a row of data on your slave. If this happens, fix the data, then run:

STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;START SLAVE;

13. REMOVE SLAVE FROM REPLICATION

If for some you like to remove a slave from a running replication, run the following commands in your MySQL shell:

mysql -u root
mysql> STOP SLAVE;
mysql> RESET SLAVE;

Edit the my.cnf configuration file and remove any information (if present) which refers to “master-…” or “replicate-…” options. You may not have anything in the my.cnf, since replication can be setup dynamically as well. Restart the MySQL service for the changes to take effect.

If you’re one of ourLinux VPS Hosting customers we can help you install and configure MySQL Master-Slave replication on yourvirtual server for you free of charge. Justcontact us and some of our experts will complete your request immediately.





About List