HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   mysql replication issues (http://www.howtoforge.com/forums/showthread.php?t=5857)

Jamesk5 28th July 2006 11:32

mysql replication issues
 
I've got replication working on two machines, both running Debian (etch) and the latest version of mySQL-server-5.0, but I've got an issue with the slave.
If the machine goes down, the slave 'feature' of mysql doesn't seem to automatically turn its self on - not sure why. I've followed the mySQL docs on replication.
Also, when I run the 'start slave' command on the slave, it doesn't go and automatically carry out any changes that have been done on the master since the slave went down.

Any suggestions will be appreciated,
James

falko 29th July 2006 12:23

Did you take a look at this tutorial? http://www.howtoforge.com/mysql_database_replication

Jamesk5 29th July 2006 17:45

Yes I did, but in your tutorial you only cover how to replicate a single database, where as I need the whole MySQL DB replicated for data redundancy and a hotswap incase my main machine goes down.
Your tutorial barely differs from the docs on the MySQL site - which is why I posted the question.
Thanks for the how-to anyway, but I've basically used parts of your tute and the rest of the stuff from the mysql docs.

falko 30th July 2006 16:42

Quote:

Originally Posted by Jamesk5
where as I need the whole MySQL DB replicated for data redundancy and a hotswap incase my main machine goes down.

Basically you must list all databases in my.cnf on the master and slave.

Master:

Code:

binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3
binlog-do-db=db4
binlog-do-db=db5
binlog-do-db=db6
binlog-do-db=db7
...

Slave:

Code:

replicate-do-db=db1
replicate-do-db=db2
replicate-do-db=db3
replicate-do-db=db4
replicate-do-db=db5
replicate-do-db=db6
replicate-do-db=db7
...


Jamesk5 2nd August 2006 02:49

ahhh ok, so every time I add a DB to the master I need to add it to the list in both my.cnf files. It just seems a bit silly too me - considering that it works to some degree without all the lines that you mentioned above.

falko 3rd August 2006 14:56

Yes, maybe a little bit silly, but it's working. :)

Jamesk5 22nd August 2006 16:15

Thanks Falko, I'll give it a try. It really does seem silly to have to add each new DB I create - but if it works I can't really complain!!

I've got automated sql dumps being forwarded out to a secondary machine anyway, so perhaps that would be better than worrying about replication issues.
From what I can see, replication is a great way of having 'real time' backups, but mysqldump seems to be more reliable as a way of backing up all the data in a DB without having to worry about specifying tables etc.

lifeisboost 22nd September 2006 05:20

sorry to bring an old thread back BUT im trying to replicate 7 DB's i tried entering each DB into my.cnf but when i go to show master it shows all the dbs being logged into one bin log is this correct?

+------------------+----------+-------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| mysql-bin.000002 | 1125758 | web1_db1,web1_db2 | |
+------------------+----------+-------------------+------------------+

should there not be a binlog for EACH db? or does each position make up for it being different db's? also i see everytime you restart mysql it creates another log file. if i tell the slave to watch mysql-bin.000002 and the master gets rebooted it will rotate to log mysql-bin.000003, would i then have to set everything back up on the slave?

falko 23rd September 2006 14:40

Quote:

Originally Posted by lifeisboost
when i go to show master it shows all the dbs being logged into one bin log is this correct?

Yes, that's correct.

Quote:

Originally Posted by lifeisboost
if i tell the slave to watch mysql-bin.000002 and the master gets rebooted it will rotate to log mysql-bin.000003, would i then have to set everything back up on the slave?

No, all changes should be replicated to the slave.


All times are GMT +2. The time now is 17:49.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.