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

Improve this question
12
  • What does "close my servers" mean?
    – Rick James
    Dec 20, 2015 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, 2015 at 5:02
  • That won't stop mysqld (the server), only mysql (the client).
    – Rick James
    Dec 21, 2015 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, 2015 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, 2015 at 9:03

1 Answer 1

Reset to default
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.

Improve this answer
10
  • # 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, 2015 at 19:14
  • and what about losing connection when I close servers and then start it again?
    – MIC
    Dec 20, 2015 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, 2015 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, 2015 at 5:56
  • no replication stops when i close putty! and I didn;t execute stop slave before executing
    – MIC
    Dec 21, 2015 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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