By MIC


2015-12-20 09:39:31 8 Comments

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

1 comments

@jerichorivera 2015-12-20 12:31:57

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.

@MIC 2015-12-20 19:14:29

# 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 2015-12-20 19:16:28

and what about losing connection when I close servers and then start it again?

@jerichorivera 2015-12-21 05:53:59

@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 2015-12-21 05:56:29

@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

@MIC 2015-12-21 08:41:39

no replication stops when i close putty! and I didn;t execute stop slave before executing

@jerichorivera 2015-12-21 10:24:22

@MIC what does 'show slave status\G' output say? if slave SQL and IO is running (yes) then replication is working. i wonder what your exact commands are just before exiting putty or after reconnecting to the servers.

@MIC 2015-12-21 18:46:05

sometimes the IO is yes but above it, is waiting for master to send event and sometimes IO is no and above is Connecting to master. There are no exact commands I just shut it down. my master and slave mysql versions are different can it be the cause of all this?

@jerichorivera 2015-12-22 09:58:16

@MIC master should be lower version than slave. you have it all wrong. mysql supports when master is 5.5 and slave is 5.6. that is why sometimes you see replication broken when you log in...

@MIC 2015-12-22 10:15:03

but it's clearly stated in documentation that both master and slave should have the same version. I re installed mysql 5.5.46 version on both master and slave. Anyway my replication is working fine for now :)

@jerichorivera 2015-12-22 11:31:00

@MIC yes aside from having the same version in master and slave mysql also supports when master is of older version than in slave but not the other way around

Related Questions

Sponsored Content

1 Answered Questions

error connecting to master '[email protected]:3306' mac-ubuntu

2 Answered Questions

[SOLVED] MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db

  • 2014-10-04 07:28:07
  • Joe Hopfgartner
  • 1154 View
  • 2 Score
  • 2 Answer
  • Tags:   mysql replication

5 Answered Questions

[SOLVED] Broken mysql replication on slave server (error 1236)

  • 2013-07-12 04:12:18
  • onastvar
  • 40412 View
  • 4 Score
  • 5 Answer
  • Tags:   mysql replication

1 Answered Questions

1 Answered Questions

[SOLVED] Automatic replication on slave node after service restart

1 Answered Questions

[SOLVED] Error 1236 From Master After Restored Replication

1 Answered Questions

[SOLVED] Max allowed packet changed still replication not starting

  • 2013-12-04 17:36:59
  • biz14
  • 1292 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql replication

Sponsored Content