MySql Master-Slave Replication, and post replication

============================= 
#MASTER: add lines to /etc/mysql/mysql.conf.d/mysqld.cnf
============================= 

##### Master Setting  ######
server-id  =1
log_bin = /var/log/mysql-bin.log
#### Data that need to be replicated ####
binlog-do-db=database_name_1 
binlog-do-db=database_name_2 
binlog-do-db=database_name_3 
Run all this query in MYSQL client:
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES; 
FLUSH TABLES WITH READ LOCK; 
Here you will need to open new window and use mysqldump to dump the database that need to replicate

UNLOCK TABLES; 
SHOW MASTER STATUS; 
output> file | Position | Binlog_Do_DB 
mysql-bin.000963 | 1570 database_name_1,database_name_2,database_name_3  

============================= 
SLAVE: add lines to my.cnf 
============================= 

# slave setting file in ubuntu 18.04  /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
relay-log =/var/log/mysql/mysql-relay-bin.log
log_bin =/var/log/mysql/mysql-bin.log
#  Database that need to replicated
replicate-do-db=database_name_1 
replicate-do-db=database_name_2 
replicate-do-db=database_name_3  

Run all this query in MySql client on Slave server

 SLAVE STOP; 
CHANGE MASTER TO MASTER_HOST='192.168.0.2',MASTER_USER='user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98;  
START SLAVE;  
SHOW SLAVE STATUS; 
 

Addition Info

  1. MASTER – restart the master after adding new database in binlog-do-db 
  2. SLAVE – Check the slave Database’s replication is up to date (SHOW SLAVE STATUS > Seconds_Behind_master). If not wait till it is
  3. MASTER – FLUSH TABLES WITH READ LOCK; To stop new data entering the master
  4. SLAVE – Check replication is fully caught up and no data is replicating (read_master_log_posand exec_master_log_pos should be the same and not changing (in show slave status;))
  5. SLAVE – STOP SLAVE; to stop any data replicating into the database once you unlock the master shortly.
  6. Run MySQLDUMP with --single-transaction option
  7. Once MySQLDUMP has started running unlock the master Database UNLOCK TABLES; In this way your systems can continue reading and writing to the master, hopefully keeping downtime to a minimum

note: . that MySQLDUMP may lock the database schema while it works depending on your system

Once MySQLDUMP has completed, import it into the slave Database

9) check the newly imported new-database looks correct

10) Restart the slave Database, after adding replicate-do-db=database4 to my.cnf file as in above

Once it comes back on, it should continue replicating from where it left off, but including the new database.

Post navigation

Leave a Reply

Your email address will not be published. Required fields are marked *