MySQL replication

From Wiki
Jump to: navigation, search

Update mysqld section /etc/my.cnf.

[mysqld]
server-id = 1
log-bin
ssl

SSL is optional but recommended.

============================= 
MASTER: add lines to my.cnf 
============================= 
binlog-do-db=database_name_1 
binlog-do-db=database_name_2 
binlog-do-db=database_name_3 
============================= 
MASTER: SQL SYNTAX 
============================= 
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES; 
FLUSH TABLES WITH READ LOCK; 
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 
============================= 
replicate-do-db=database_name_1 
replicate-do-db=database_name_2 
replicate-do-db=database_name_3 
============================= 
SLAVE: SQL SYNTAX 
============================= 
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; 

NOTE: 

MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98; is displayed when you run the SQL command from the master: cmd mysql#> SHOW MASTER STATUS; 

ALSO: 

When you run #> SHOW SLAVE STATUS; 
make sure you see: Slave_IO_Running | Slave_SQL_Running 
Yes Yes

Another example.

CHANGE MASTER TO
 MASTER_HOST='master2.mycompany.com',
 MASTER_USER='replication',
 MASTER_PASSWORD='bigs3cret',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='master2-bin.001',
 MASTER_LOG_POS=4,
 MASTER_CONNECT_RETRY=10;

Reference: https://dev.mysql.com/doc/refman/5.0/en/change-master-to.html