MySQL Master Slave Replication/Configuration
Here is what we are gonna learn in MySQL Master Slave Replication document,
– Setting up Master
– Create a replication user
– Setting up Slave
– Check Replication Status
– Just a few cool command about MySQL
[INFO]
This document is prepared for
– MySQL 5.x
– Centos 7.x
* bind-address the ip address of database.
* server-id is must be number.
* you have to specify mysql-bin.log file
Config On Master Side
Add this config to end of the /etc/my.cnf file
# Replication Config
bind-address = {master ip address}
server-id = 1
log_bin = /var/lib/mysql
After that config. You have to restart MySQL service for apply config.
systemctl restart mysqld
Login into the master mysql
mysql -u root -p
Create user for replication
CREATE USER ‘repl’@’{slaveip}’ IDENTIFIED BY ‘{password}’; GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’{slaveip}’;
After created slave user log out mysql via
exit
Create dump.sh fill the root and password with your own and run it.
MYSQL_CONN=”-uroot -ppassword”
MYSQLDUMP_OPTIONS=” --master-data=1--single-transaction --flush-privileges”
MYSQLDUMP_OPTIONS=”${MYSQLDUMP_OPTIONS} --routines --triggers {dbname}”
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
scp MySQLData.sql root@{slaveserverip}:/tmp
After we edit my.cnf, create replication user for slave db and dump we’re done in master.
Config On Slave Side
Add this config to end of the /etc/my.cnf file
# Replication Config
bind-address = {slave ip address}
server-id = 2
log_bin = /var/lib/mysql
After that config. You have to restart MySQL service to apply for apply it.
systemctl restart mysqld
Go tmp directory for import sql
cd /tmp
Import sql to database
mysql -u -p {dbname} < MySQLData.sql
Learn MASTER_LOG_FILE name and MASTER_LOG_POS
head -22 MySQLData.sql | tail -1
Everything is setting up, we only need to start slave.
Login into the slave MySQL
mysql -u root -p
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST=’{masterip}’,
MASTER_USER=’repl’,
MASTER_PASSWORD=’{repluserpassword}’,
MASTER_LOG_FILE=’{MASTER_LOG_FILE}’,
MASTER_LOG_POS={MASTER_LOG_POS};
START SLAVE;
Checks
SHOW SLAVE STATUS\G
Should look like this;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno:
Last_Error:
Login into the master mysql
mysql -u root -p
SHOW SLAVE HOSTS;
Output must contain your slave’s Server_id
DONE!