Re: master-slave replication sync problems.
On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine nor...@khine.net wrote: hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: As one other stated, it should not lose position when you stop and start the master or slave in a controlled manner. And also stated was that the way you were doing it was causing a loss of data, and you'll need to use maatkit to fix it, or if you can afford some downtime, stop mysql on both and copy the whole database(s) from master to slave (and reconfigure replication of course). On the slave, I'd be curious what the result was when you did only a STOP SLAVE; and then START SLAVE; because in my estimation, chances are it would have picked right up where it was supposed to. Your issue is likely that your slave has a large slave_net_timeout value (default is 3600 seconds IIRC). That means your slave will take 1 hour to finally give up on the replication connection (that the master closed on it), close the connection, and then reopen it. Lower that setting to something smaller, I've gone as low as 30 seconds, but I would only do that on a reliable network. Try lowering it to 300 (5 minutes) on your slave. When you restart the master, the slave should timeout after only 5 minutes and then resume replication. -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
since u r starting slave by postion beyond master, and if some of the
changes are already present on slave, it would error out with duplicate.
You need
show slave status\G;
slave stop;
show slave status\G;..wait for few min..than again
show slave status\G;just to make sure...slave is in complete sync with
master...
Here u will see the master log file and position.
U need to use this, next time u start ur slave to sync with master.
regards
anandkl
On Thu, Aug 26, 2010 at 6:34 PM, Norman Khine nor...@khine.net wrote:
hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.
what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:
[MASTER]
mysql show master status;
+--+--+--+--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+--+
1 row in set (0.00 sec)
[SLAVE]
mysql stop slave;
mysql change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql start slave;
is this correct or is there a better way to do this?
thanks
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
True, But some times, this does not work and u need to know the master bin-log file and position to start, so that there is no loss of data. regards anandkl On Thu, Aug 26, 2010 at 6:39 PM, a.sm...@ukgrid.net wrote: That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
i see, so the best is to just stop slave and then check the master
status, and when the master status syncs then i start the slave?
On Thu, Aug 26, 2010 at 3:09 PM, a.sm...@ukgrid.net wrote:
That is really bad, you will loose changes.
You shouldnt have to do anything when rebooting either the master or slave.
If the master is down, then the slave recieves no updates. If the slave is
down, when it comes back up it checks the master log pos and plays thro any
changes that are necessary to bring it up to date...
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Yes,
You need to note down the master bin-log file name and position on the
slave, this is a must.
regards
anandkl
On Thu, Aug 26, 2010 at 7:34 PM, Norman Khine nor...@khine.net wrote:
i see, so the best is to just stop slave and then check the master
status, and when the master status syncs then i start the slave?
On Thu, Aug 26, 2010 at 3:09 PM, a.sm...@ukgrid.net wrote:
That is really bad, you will loose changes.
You shouldnt have to do anything when rebooting either the master or
slave.
If the master is down, then the slave recieves no updates. If the slave
is
down, when it comes back up it checks the master log pos and plays thro
any
changes that are necessary to bring it up to date...
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
Quoting Norman Khine nor...@khine.net: i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? Well Im willing to hear from others experiences, but if you really shouldnt have to do anything. If you want you can issue a stop slave before the reboot and a start slave after, but it shouldnt make any difference. And Im not sure what you refer to when you talk about the master status syncing. The master will continue to increment the binlog when changes are made regardless of whether the slave is up. If the slave has been rebooted and is behind the master binlog pos it should automatically sync those changes when started... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Smith, I never said, this wont work.Some times, there are chances of lossing data. regards anandkl On Thu, Aug 26, 2010 at 8:48 PM, a.sm...@ukgrid.net wrote: Quoting Norman Khine nor...@khine.net: i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? Well Im willing to hear from others experiences, but if you really shouldnt have to do anything. If you want you can issue a stop slave before the reboot and a start slave after, but it shouldnt make any difference. And Im not sure what you refer to when you talk about the master status syncing. The master will continue to increment the binlog when changes are made regardless of whether the slave is up. If the slave has been rebooted and is behind the master binlog pos it should automatically sync those changes when started... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
Hi,
The best way to use sync the master and slave is using mk-checksum tools. just
google it and use the the tools for online sync of master and slave.
Jeetendra Ranjan
MySQL DBA
--- On Thu, 26/8/10, Norman Khine nor...@khine.net wrote:
From: Norman Khine nor...@khine.net
Subject: master-slave replication sync problems.
To: mysql@lists.mysql.com
Date: Thursday, 26 August, 2010, 6:34 PM
hello,
i have a working master-slave replication, the problem i find is that
if i restart the MASTER there is a difference in the MASTER_LOG_FILE
and MASTER_LOG_POS on the SLAVE.
what is the correct way to keep the two slaves in sync even after i
restart the server. the way i do it now is to:
[MASTER]
mysql show master status;
+--+--+--+--+
| mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
mysql,information_schema |
+--+--+--+--+
1 row in set (0.00 sec)
[SLAVE]
mysql stop slave;
mysql change master to MASTER_HOST='master.domain.com',
MASTER_USER='replicator', MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
mysql start slave;
is this correct or is there a better way to do this?
thanks
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=jitendra_ran...@yahoo.com
Re: master-slave replication sync problems.
Quoting Ananda Kumar anan...@gmail.com: Smith, I never said, this wont work.Some times, there are chances of lossing data. regards anandkl If you have experience of this fair enough. Theres no reason it should make any difference, as everything is based upon the binlog file and position... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Quoting jitendra ranjan jitendra_ran...@yahoo.com: Hi, The best way to use sync the master and slave is using mk-checksum tools. just google it and use the the tools for online sync of master and slave. Jeetendra Ranjan MySQL DBA Yes, data integrity isnt guaranteed with MySQL replication. So if you want to be sure good idea to use the maartkit tools... Tho this isnt necessarily answering the original question, which is to do with the master and slave not showing the same binlog file and log position. If replication is working at all, these will be in sync (assuming the changes on the master arent happening faster than replication can keep up). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: master-slave replication sync problems.
ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Hello List, On 8/26/2010 3:00 PM, Daevid Vincent wrote: ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks To me, it appears that many of you are not fully versed in the theory of operations for how MySQL replication actually functions. Granted, there are two formats for replication (ROW and STATEMENT) but the general process remains the same. Here is a nutshell summary of the process. ** on the master ** m1) The MySQL master is instructed to change some data. m2) The data is changed and the results are committed to disk. If you rollback the changes before you commit them, then there is nothing to replicate. Only the InnoDB engine supports this type of rollback. m3) The change committed in step 2 is written to the binary log (repeat from step m1 until the Master is shutdown) ** on the slave - the IO thread ** (assuming that the slave is already configured with compatible data, a starting position, and the proper credentials to act as a slave) o1) The SLAVE IO thread requests information from the master's binary logs. This information is identified by a file name and a byte offset from the start of that file o2) The SLAVE IO thread copies all available information from the master's binary logs into a local copy of those logs known as the relay logs. (repeat from o1 until the SLAVE IO thread is stopped(by error or by command) or the slave is shutdown) ** on the slave - the SQL thread ** s1) Once an unapplied change has been completely buffered into the relay logs, the SLAVE SQL thread attempts to apply the change to the slave's data. s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the correct format) into the slave's binary log. (repeat from s1 until the SLAVE SQL thread is stopped (by error or by command) or the slave is shutdown) ** As you can tell by this very simplified process description, there is no attempt to rectify one dataset to the other. Replication operates under the principle that if you perform identical changes to identical sets of data, you will end up with identical end results. Various replication filters can omit certain changes
Re: Master/Slave Replication Question
from what i've read and seen geographical load balancer works as: multiple DNS A records routes to multiple Apache Servers(mod_php tucks in as a module under Apache) each web servers would connect to MySQL on their own localhost 1.2.3.4-WebServer would communicate directly to 1.2.3.4-MySQL (localhost) master-slave configurations imply A1-MySql master replicate to A2-MySql slave how should this new replication be handled? You are correct about the DNS load balancing and connecting to localhost. Here is the actual set-up I'm working with: We have three LAMP servers: www-01, www-02 and www-03. Currently, mySQL on www-01 replicates to www-02, which replicates to www-03, which replicates back to www-01. What I'd like to do is have www-01 be the authoritative server, and have www-02 and www-03 both be replication slaves to www-01. When a user connect to www-01 and makes an update, nothing special - the update gets recorded, written to the binary log and replicated to www-02 and www-03. When a users attempts to update/insert/delete a record on www-02, I want www-02 to: 1. open a connection to www-01 (or have one connection that's already open for this purpose). if the connection to www-01 fails because of a TCP problem, www-02 should return an error along the lines of replication master is unavailable; query not executed to the client 2. authenticate against www-01 as the user the is currently authenticated on www-02. again, if this fails, return an error along the lines of could not authenticate on replication master; query not executed 3. pass the exact same query that www-02 received upstream to www-01 4. wait for www-01 to respond ok or query error 5. pass the response from www-01 back to the client connected to www-02 Is that clear? Normal replication would then pass the update from www-01 back to www-02 and www-03 so that all three servers are in sync. Since the update happened on www-01, auto_increments would not need to use the auto_increment_increment and auto_increment_offset, stored functions and stored procedures would not have to worry about their respective replication problems (certain types of functions/procedures aren't safe to use with normal mySQL replication), and locking would work (because lock statements would also be passed up the chain to the master server). Yes, this means that updates wouldn't be as fast as multi-master replication, but I think dealing with almost all the other problems with replication would become much more manageable. This set-up makes the most sense in read-heavy environments. As I mentioned before, my main goal here is geographic redundancy, load balancing and the ability to use applications that aren't really designed to be used with master/slave replication (Drupal, WordPress, etc) without having to modify those applications. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Master/Slave Replication Question
Another thought would be at the application layer, sending all the updates (insert,delete, update, etc) to server A, and the selects to the local slave servers. This has been suggested before, and I'm totally against it. Applications like PHPBB, Drupal, WordPress, etc can't be easily configured to do this, and I'd really like to use those applications in a more robust and redundant environment. Pushing the work of this sort of master/slave relationship to the application level means that most applications will not support it. Replication is a database server problem, not an application problem. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Master/Slave Replication Question
You'd be surprised how many places uses this as an actual solution. All arguments aside about what level of the architecture should be doing what, it simply works well. Moreover, it works today as opposed to waiting until the end of time for the database developers to add features like that (which mysql cluster is already a distributed database, and the devs have said they're not interested in trying to turn the regular mysql into a distributed product, instead they want to focus on what it does best) I would love to see a drop-in solution that requires no application changes, and doesn't introduce any additional complications such as adding excessive overhead, or have yet another single point of failure, but none (or at least no good ones) exist. This is because optimization is a very specific process. Applications like PHPBB, Drupal and WordPress should have their database access encapsulated well enough that making the changes to split reads and writes is trivial. If not, then it's bad design on their part. Scalability is very much a part of application design as well, and just because lots of people use those apps, doesn't mean they were designed to scale well. Regards, Gavin Towey -Original Message- From: Tim Gustafson [mailto:t...@soe.ucsc.edu] Sent: Friday, September 25, 2009 2:44 PM To: mysql@lists.mysql.com Subject: Re: Master/Slave Replication Question Another thought would be at the application layer, sending all the updates (insert,delete, update, etc) to server A, and the selects to the local slave servers. This has been suggested before, and I'm totally against it. Applications like PHPBB, Drupal, WordPress, etc can't be easily configured to do this, and I'd really like to use those applications in a more robust and redundant environment. Pushing the work of this sort of master/slave relationship to the application level means that most applications will not support it. Replication is a database server problem, not an application problem. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Master/Slave Replication Question
Moreover, it works today as opposed to waiting until the end of time for the database developers to add features like that (which mysql cluster is already a distributed database, and the devs have said they're not interested in trying to turn the regular mysql into a distributed product, instead they want to focus on what it does best) With all due respect to the mySQL cluster people, setting up a mySQL cluster just isn't in the cards for lots of organizations. It's just too much. There's a huge implementation gap between a single mySQL server and a mySQL Cluster. I've also heard from people who have tried to implement mySQL clustering that wide-area cluster replication is hard or impossible (I can't remember which), so the ability to provide geographic redundancy (one of my requirements here) isn't workable. I think saying that I'd have to wait until the end of time is a bit harsh. Sure, it's not going to happen tomorrow, but I wasn't expecting that anyhow. I'm not sure if you've looked at the database integration for things like Drupal, but there will probably never be a way for Drupal to use an updates go to this server, reads go to this server configuration, as there are thousands of Drupal modules and almost all of them use the database directly, and each would have to be re-coded to work with the read/write split configuration. And anyhow, I think that suggestion is missing the point: If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that writes go here, reads go there when they write their own PHP code. And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant. So yeah, maybe lots of custom-written software handles the read/write split configuration well, but there's lots more that doesn't. I don't know of a single open source application that does. So again, I go back to my original statement: replication is a database server problem, not an application problem. :) Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Master/Slave Replication Question
Cluster is not supposed to be a universal solution, for a reason. Universal solutions tend not to be very performant. If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that writes go here, reads go there when they write their own PHP code. Do you want geographic redundancy or do you want to scale reads? In this case you're talking about scaling reads for a bunch of apps all running together. If you want performance in that case, then first you'd want to isolate the apps from each other. And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant. Geographical redundancy is different: a dns record with a zero ttl, with a master-slave replication setup. Point the record a the master and if it fails, change the dns entry to point to the slave. Your applications never need to know about replication. That’s even if you don't want to go with the more complex Linux HA or hardware based ip takeover solutions. There are many ways you could add redundancy without modifying the apps. That's the great thing about open source software and techniques. They're like building blocks, and you can put them together however you want. I find this much more preferable to the all-in-one black-box solution. Regards, Gavin Towey -Original Message- From: Tim Gustafson [mailto:t...@soe.ucsc.edu] Sent: Friday, September 25, 2009 4:18 PM To: Gavin Towey Cc: mysql@lists.mysql.com Subject: Re: Master/Slave Replication Question Moreover, it works today as opposed to waiting until the end of time for the database developers to add features like that (which mysql cluster is already a distributed database, and the devs have said they're not interested in trying to turn the regular mysql into a distributed product, instead they want to focus on what it does best) With all due respect to the mySQL cluster people, setting up a mySQL cluster just isn't in the cards for lots of organizations. It's just too much. There's a huge implementation gap between a single mySQL server and a mySQL Cluster. I've also heard from people who have tried to implement mySQL clustering that wide-area cluster replication is hard or impossible (I can't remember which), so the ability to provide geographic redundancy (one of my requirements here) isn't workable. I think saying that I'd have to wait until the end of time is a bit harsh. Sure, it's not going to happen tomorrow, but I wasn't expecting that anyhow. I'm not sure if you've looked at the database integration for things like Drupal, but there will probably never be a way for Drupal to use an updates go to this server, reads go to this server configuration, as there are thousands of Drupal modules and almost all of them use the database directly, and each would have to be re-coded to work with the read/write split configuration. And anyhow, I think that suggestion is missing the point: If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that writes go here, reads go there when they write their own PHP code. And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant. So yeah, maybe lots of custom-written software handles the read/write split configuration well, but there's lots more that doesn't. I don't know of a single open source application that does. So again, I go back to my original statement: replication is a database server problem, not an application problem. :) Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Master/Slave Replication Question
Do you want geographic redundancy or do you want to scale reads? In this case you're talking about scaling reads for a bunch of apps all running together. If you want performance in that case, then first you'd want to isolate the apps from each other. Geographic redundancy is my primary goal. Geographic redundancy also by its very nature helps boost performance of reads because you have more than one server from which to read. Each web site hosted on this group of servers has an A record that points to all three servers - so, foo.com has A records for 1.2.3.4, 2.3.4.5 and 3.4.5.6. Using round-robin DNS, the connections for clients are distributed across all three geographically redundant servers using poor-man's load balancing. Geographical redundancy is different: a dns record with a zero ttl, with a master-slave replication setup. Point the record a the master and if it fails, change the dns entry to point to the slave. Your applications never need to know about replication. Except I'm not just making my database geographically redundant, but my web server and applications as well. Each server runs mySQL, Apache and PHP. PHP connects to localhost on each server. Using your method (which by the way requires human intervention to activate), only the mySQL would be redundant. And your solution does not balance reads across all mySQL servers. That's the great thing about open source software and techniques. They're like building blocks, and you can put them together however you want. I find this much more preferable to the all-in-one black-box solution. You find it preferable to handle your database redundancy with something other than your database server? Any mySQL is black-box? I was pretty sure mySQL was open source. Anyhow, you have actually proven my point: mySQL has already provided the building blocks of master/slave replication. Now I'd like to see an addition to that protocol (upstream updates) and bam, we've got a whole new replication methodology that fills the needs of a set of people whose needs were not met by multi-master replication or mySQL cluster. At any rate, this part of the discussion is getting very off-topic. My original suggestion pertained to a new way of doing replication that I'd like to see in mySQL. If you don't like my idea, don't use the feature if and when it ever comes into being. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Master-Slave replication error: Last_Errno: 1146
On Thu, May 22, 2008 at 5:36 PM, Salah Nait-Mouloud [EMAIL PROTECTED] wrote: Hi all. I have 2 MySQL servers. One master and one slave. In order to add new slave server, and because i can't stop the master one, i have tried this: http://forums.mysql.com/read.php?26,99846,102058 *You don't have to modify the other slaves configuration. What you need to do is obtain a snapshot of the data on those slaves with their current position relative to the master. The easiest way to do this is to stop mysql on one of the slaves and copy it's data directory (except for the bin logs) and my.cnf to the new slave. Then start the old slave so it doesn't get too far behind in replication. On the new slave change the server-id in my.cnf so it's unique from the other servers. Add skip-slave to my.cnf. Start mysql, login and run show slave status\G Then run the following command substituting the $values from the show slave status change master to master_log_file='$Relay_master_log_file', master_log_pos=$Exec_master_log_pos; Then start the new slave; Once replication catches up it's good to go :)* The issue is when i do START SLAVE; i obtain this error: Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1146 Last_Error: Error 'Table 'XX.y' doesn't exist' on query. Default database: ''. Query: 'INSERT INTO y ' MySQL Server version: version 5.0.32 . Any idea ? It looks like you started at a position that after an INSERT. So your position is wrong. Many Thanks. -- Salah NAIT-MOULOUD Echovox - www.echovox.com m-Boost - www.m-boost.com
Re: master - slave replication - slave not updating
The error 1062 you could skipped in the slave modifying your my.cnf ( slave side) using : slave-skip-errors=1062 This error on duplicated records, normally is problem in binarylogs transfer data. But, anyway, please confer that the data exist in the slave. Yes thanks - i have done that and restarted the slave and _some_ tables now appear to update but others dont eg master: mysql select count(*) from tbl_checkcommand; +--+ | count(*) | +--+ | 30 | +--+ 1 row in set (0.00 sec) slave: mysql select count(*) from tbl_checkcommand; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) all seems ok in the log 080516 12:21:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.03' at position 2941, relay log './devsw19nagios02-relay-bin.01' position: 2909 /usr/libexec/mysqld: ready for connections. Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 080516 12:21:55 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.03' at position 4608829 any ideas as to why they are not all updating ? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: master - slave replication - slave not updating
Hi Tom, The error 1062 you could skipped in the slave modifying your my.cnf ( slave side) using : slave-skip-errors=1062 This error on duplicated records, normally is problem in binarylogs transfer data. But, anyway, please confer that the data exist in the slave. Regards, Juan On Fri, May 16, 2008 at 8:40 AM, Tom Brown [EMAIL PROTECTED] wrote: Hi I am running 4.1.20 as this is the stock version in RHEL4 u5 and i have setup a master/slave combination. Updates at first appeared to work as creating and inserting into the master created the db's on the slave etc. I have noticed however that data in the master is not making its way to the slave. I am now seeing issues like this mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.12.225 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.03 Read_Master_Log_Pos: 2008085 Relay_Log_File: devsw19nagios02-relay-bin.01 Relay_Log_Pos: 2909 Relay_Master_Log_File: mysql-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '754' for key 1' on query. Default database: 'nagiosql'. Query: 'INSERT INTO tbl_logbook SET user='Admin',time=NOW(), entry='Passwort successfully modified'' Skip_Counter: 0 Exec_Master_Log_Pos: 2941 Relay_Log_Space: 2008053 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) and in the mysql log 080516 9:23:01 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.03' at position 79, relay log './devpctnagios02-relay-bin.01' position: 4 080516 9:23:01 [Note] Slave I/O thread: connected to master ' [EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.03' at position 79 080516 10:06:58 [ERROR] Slave: Error 'Duplicate entry '754' for key 1' on query. Default database: 'nagiosql'. Query: 'INSERT INTO tbl_logbook SET user='Admin',time=NOW(), entry='Passwort successfully modified'', Error_code: 1062 080516 10:06:58 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'mysql-bin.03' position 2941 Any clues as to how i can fix this? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: master - slave replication - slave not updating
Yes thanks - i have done that and restarted the slave and _some_ tables now appear to update but others dont eg master: mysql select count(*) from tbl_checkcommand; +--+ | count(*) | +--+ | 30 | +--+ 1 row in set (0.00 sec) slave: mysql select count(*) from tbl_checkcommand; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) all seems ok in the log 080516 12:21:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.03' at position 2941, relay log './devsw19nagios02-relay-bin.01' position: 2909 /usr/libexec/mysqld: ready for connections. Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 080516 12:21:55 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.03' at position 4608829 any ideas as to why they are not all updating ? i should probabbly also mention that the slave appears to think its happy, i believe, mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.12.225 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.03 Read_Master_Log_Pos: 6412734 Relay_Log_File: devsw19nagios02-relay-bin.02 Relay_Log_Pos: 1803952 Relay_Master_Log_File: mysql-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 6412734 Relay_Log_Space: 1803952 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: master - slave replication - slave not updating
Hi Tom, Your master binary log start with mysql-bin.03, then, your insert ( or update) in the master could be in mysql-bin.02 or 1. Please, reload your binary logs again from the beginning ( put mysql-bin.02 or 1, ). Your insert or update could be in one of this files. Regards, Juan On Fri, May 16, 2008 at 10:09 AM, Tom Brown [EMAIL PROTECTED] wrote: Yes thanks - i have done that and restarted the slave and _some_ tables now appear to update but others dont eg master: mysql select count(*) from tbl_checkcommand; +--+ | count(*) | +--+ | 30 | +--+ 1 row in set (0.00 sec) slave: mysql select count(*) from tbl_checkcommand; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) all seems ok in the log 080516 12:21:55 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.03' at position 2941, relay log './devsw19nagios02-relay-bin.01' position: 2909 /usr/libexec/mysqld: ready for connections. Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 080516 12:21:55 [Note] Slave I/O thread: connected to master ' [EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.03' at position 4608829 any ideas as to why they are not all updating ? i should probabbly also mention that the slave appears to think its happy, i believe, mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.12.225 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.03 Read_Master_Log_Pos: 6412734 Relay_Log_File: devsw19nagios02-relay-bin.02 Relay_Log_Pos: 1803952 Relay_Master_Log_File: mysql-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 6412734 Relay_Log_Space: 1803952 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec)
Re: master/slave replication - errors!!
bruce wrote: hi.. doing a simple test of master/slave replication, using mysql. i have two test systems: master - foo (192.168.10.13) slave - cat (192.168.20.20) on both machines, i created a testmasterdb. on the master, i populated the tbl within the db with some test data. there are no tbls in the slave, only the create database... for the master, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-bin=/var/log/mysql/mysql-bin.log binlog-do-db=testmasterdb server-id=11 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid for the slave, the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #replication - slave server server_id=2 master_host=mfgtest3.stratalight.com master_user=slave master_password=slave master_connect_retry=60 replicate-do-db=testmasterdb Use CHANGE MASTER TO instead. It avoids a lot of potential problems. Settings in the my.cnf is deprecated. [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid on the master, (logged in as root) i granted replication privileges to my test user (slave/slave). on the slave mysql, i then tried to do a load data from master Don't use LOAD DATA FROM MASTER either. It only ever worked for certain cases and is also deprecated. You should do a dump-and-load or file copy to initialize the slave. and got the following error: Error running query on master: Access denied; you need the RELOAD privilege for this operation do i need to run the load data cmd from the slave, when i'm logged in as user slave, i would think that being root would allow me to issue the cmd? do i have to have the physical ipaddress or the master in the my.cnf file? (i would think i could have the fqdn, given that ips change -dhcp) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Slave Replication
At 14:11 -0400 10/18/03, DePhillips, Michael P wrote: Hi List I'm having trouble start a slave. All seems to be configured well as per show slave status, I issue a mysql slave start; Query OK, 0 rows affected (0.00 sec) and nothing happens, show slave status sill says slave is not running. Any insght?? Look in the slave's error log. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Master-Slave Replication
Got it to work after the restart. Now works fine. SO now back to the Master-Master replication Dathan Vance Pattishall [EMAIL PROTECTED] wrote: Type show slave status to figure out what the problem is on the slave. Type show full processlist on the master to see if the slave is connected waiting for binlog updates. Make sure the master is replicating.. Etc. ---Original Message- --From: Sanya Shaik [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 21, 2003 12:51 PM --To: [EMAIL PROTECTED] --Subject: Master-Slave Replication -- --Hi all, -- -- Thanks for the answers for Master-Master replication. Right now i want --to try the Master-Slave replication first and then do a circular --replication. -- --Unfortunately, I am facing problems with updating slave automatically. -- --I started the slave and loaded the data from the master, later any --changes made to master are not reflected on slave. -- --Please HELP me in this regard. -- -- --- --Do you Yahoo!? --The New Yahoo! Search - Faster. Easier. Bingo. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
Re: Master-Slave Replication
Here is a good URL, maybe it can help you to deploy your Master-Slave solution. URL: http://mysql.us.themoes.org/doc/en/Replication_HOWTO.html Greetings Mikel From: Sanya Shaik [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Master-Slave Replication Date: Thu, 21 Aug 2003 12:50:31 -0700 (PDT) Hi all, Thanks for the answers for Master-Master replication. Right now i want to try the Master-Slave replication first and then do a circular replication. Unfortunately, I am facing problems with updating slave automatically. I started the slave and loaded the data from the master, later any changes made to master are not reflected on slave. Please HELP me in this regard. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. _ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Master-Slave Replication
Type show slave status to figure out what the problem is on the slave. Type show full processlist on the master to see if the slave is connected waiting for binlog updates. Make sure the master is replicating.. Etc. ---Original Message- --From: Sanya Shaik [mailto:[EMAIL PROTECTED] --Sent: Thursday, August 21, 2003 12:51 PM --To: [EMAIL PROTECTED] --Subject: Master-Slave Replication -- --Hi all, -- -- Thanks for the answers for Master-Master replication. Right now i want --to try the Master-Slave replication first and then do a circular --replication. -- --Unfortunately, I am facing problems with updating slave automatically. -- --I started the slave and loaded the data from the master, later any --changes made to master are not reflected on slave. -- --Please HELP me in this regard. -- -- --- --Do you Yahoo!? --The New Yahoo! Search - Faster. Easier. Bingo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
