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
1 2 3 4 | # Replication Configbind-address = {master ip address}server-id = 1log_bin = /var/lib/mysql |
After that config. You have to restart MySQL service for apply config.
1 | systemctl restart mysqld |
Login into the master mysql
1 | mysql -u root -p |
Create user for replication
1 | CREATE USER ‘repl’@’{slaveip}’ IDENTIFIED BY ‘{password}’; GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’{slaveip}’; |
After created slave user log out mysql via
1 | exit |
Create dump.sh fill the root and password with your own and run it.
1 2 3 4 5 | 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.sqlscp 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
1 2 3 | bind-address = {slave ip address}server-id = 2log_bin = /var/lib/mysql |
After that config. You have to restart MySQL service to apply for apply it.
1 | systemctl restart mysqld |
Go tmp directory for import sql
1 | cd /tmp |
Import sql to database
1 | mysql -u -p {dbname} < MySQLData.sql |
Learn MASTER_LOG_FILE name and MASTER_LOG_POS
1 | head -22 MySQLData.sql | tail -1 |
Everything is setting up, we only need to start slave.
Login into the slave MySQL
1 2 3 4 5 6 7 8 9 10 11 | mysql -u root -pSTOP SLAVE;CHANGE MASTER TOMASTER_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;
1 2 3 4 | Slave_IO_Running: YesSlave_SQL_Running: YesLast_Errno:Last_Error: |
Login into the master mysql
1 2 | mysql -u root -pSHOW SLAVE HOSTS; |
Output must contain your slave’s Server_id
DONE!