2

I'm trying to implement master slave replication and It's works fine with no errors, database is replicated on slave, but when I close my servers and open them again and add something in masters database it's not replicated in slave , do I have to follow these steps every time I start my server?

Setup on Masters server

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20'    
IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

mysql> use mydb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> exit;

my.cnf file

[mysqld]
log-bin=mysql-bin
binlog-do-db=mydb
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

in terminal again

# service mysqld restart

mysql > SHOW MASTER STATUS;

# mysqldump -u root -p mydb > mydb.sql
# scp mydb.sql 192.168.1.20:/opt/

SETUP SLAVE 2 SERVER

 # vim /etc/my.cnf
 [mysqld]
 server-id=2
 replicate-do-db=mydb

 # /etc/init.d/mysqld restart
 # mysql -u root -p mydb < mydb.sql

 mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.10',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='secretpassword',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=107;

mysql> SLAVE START;
mysql> show slave status \G

Also it's is only replicating one database by dumping one database on slave server

 # mysqldump -u root -p mydb > mydb.sql

How can I dump all the databases from masters to slave server?

enter image description here

  • What does "close my servers" mean? – Rick James Dec 20 '15 at 23:20
  • I'm using putty! and with that two different servers what I meant is when I close down the putty / terminals – MIC Dec 21 '15 at 5:02
  • That won't stop mysqld (the server), only mysql (the client). – Rick James Dec 21 '15 at 5:14
  • yes sorry mys mistake! but when i close the client and open it again the replication isn't working anymore , it just shows "waiting for master to send event" – MIC Dec 21 '15 at 8:48
  • waiting for master to send event is usually a very good sign. Have you tried testing your replicaton by making modifications to the data on the master? – Craig Efrein Dec 21 '15 at 9:03
1

You need to rebuild the slave from a copy of the master. So take a full dump of the master:

mysqldump -uroot -p --single-transaction --master-data=2 > fulldump.sql

Restore the dump file on the slave server, reset slave and then execute CHANGE MASTER again. Make sure to remove replicate-do-db or any replication filters on slave my.cnf.

  • # mysqldump -u root -p --all-databases --master-data > /root/dbdump.db will this line work as well? and why do we have to remove replicate-do-db? shouldn't we add all the databases in cnf file? Isn't it necessary to specify each database name in master my.cnf file to do replication? – MIC Dec 20 '15 at 19:14
  • and what about losing connection when I close servers and then start it again? – MIC Dec 20 '15 at 19:16
  • @MIC using --master-data should work as well. if you don't require replication filtering just leave out replicate-do-db or replicate-wild-* variables. no need to add all databases on cnf file or specify each db name in the master's cnf file. – jerichorivera Dec 21 '15 at 5:53
  • @MIC if you just exit from the mysql client or close putty it does not stop replication, however, if you execute 'stop slave' on the slave server before exiting then that will of course stop replication – jerichorivera Dec 21 '15 at 5:56
  • no replication stops when i close putty! and I didn;t execute stop slave before executing – MIC Dec 21 '15 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.