MySQL – Add or Edit SlaveDB without Change the Master-Server

Red = Master Server

Blue = Slave Server

 

1. Dumping and transfer dumped file from Master-Server to Slave-Server

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --routines --master-data=2 -A -uroot -p> /home/myteam/database/all_db.sql
or 
you can change specific database name:
mysqldump --skip-lock-tables --single-transaction --flush-logs --triggers --hex-blob --routines --master-data=2 --databases SpecificDB -uroot -p > /home/myteam/database/SpecificDB.sql

2

2. Change or Edit MySQL Config at "/etc/mysql/my.cnf"
server-id = 102
binlog-format = mixed
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
#make daisy chain-slave
log_slave_updates = 1
expire_logs_days = 10
max_binlog_size = 100M
##################################
# Allowed database to replicated
###################################
replicate_do_db = augipt_dashboard
replicate_do_db = augipt_goltogel
replicate_do_db = augipt_gengtogel
replicate_do_db = augipt_logs
###################################
 
i718cimgpsh_orig

3. Read MASTER_LOG_POS and MASTER_LOG_FILE from dumped file in Slave DB
1

4. Import dumped file to Slave DB
1
5. Change MASTER_LOG_FILE AND MASTER_LOG_POS
CHANGE MASTER TO 
MASTER_HOST='192.168.36.22', 
MASTER_PORT=3306,
MASTER_USER='replicator', 
MASTER_PASSWORD='goodgames', 
MASTER_LOG_FILE='mysql-bin.000018', 
MASTER_LOG_POS=107;

1

6. START SLAVE;

7. SHOW SLAVE STATUS\G

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.