Re: slow query on replication master and slave
On Wed, Apr 28, 2010 at 12:17 AM, Kandy Wong kan...@triumf.ca wrote: Is it true that the performance of running a query on a live replication master and slave has to be much slower than running a query on a static server? I've tried to run the following query on a replication master and it takes 1 min 13.76 sec to finish. SELECT *, ABS(timeA-1266143632) as distance FROM tableA WHERE timeA - 1266143632 = 0 ORDER BY distance LIMIT 1; And if I run it on the replication slave, it takes 24.15 sec. The slave most likely didn't have the table data in memory, so had to do a disk read. Also, if you've performed that query before on your master, it may still have been in the query cache. Add the SQL_NO_CACHE keyword right after the word select to disable the query cache when checking query performance. But if I dump the whole database to another machine as static, it only takes 3.70 sec or even less to finish. When you load just that table, the data you just inserted will still have been in memory and/or in the OS cache, avoiding a disk read. Is there a way to improve the query or any other factors that would affect the performance? Rewrite your where clause to where timeA = 1266143632 - that will allow you to put an index on timeA and benefiting from it. Also, ordering by timeA should give the same ordering as by distance, and may also use the index to sort instead of a filesort. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
slow query on replication master and slave
Hi, Is it true that the performance of running a query on a live replication master and slave has to be much slower than running a query on a static server? I've tried to run the following query on a replication master and it takes 1 min 13.76 sec to finish. SELECT *, ABS(timeA-1266143632) as distance FROM tableA WHERE timeA - 1266143632 = 0 ORDER BY distance LIMIT 1; And if I run it on the replication slave, it takes 24.15 sec. But if I dump the whole database to another machine as static, it only takes 3.70 sec or even less to finish. The table has 386 columns and timeA is an index. Is there a way to improve the query or any other factors that would affect the performance? Thanks. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Hi Eric, At least with Maatkit, you get transparency. We make a concerted effort to update the RISKS section of each tool with each release, so there is full disclosure. Fair enough, but I still found the warnings a little too scary. A more complete explanation of the exact nature of the bugs and the exact circumstances under which I should be concerned about triggering them would have increased my comfort level. I've made a note to review these, because the ones I checked have kind of drifted from their original purity. I updated the RISKS section for mk-table-sync the other day. I checked it and agreed with you -- it didn't distinguish between cases where there is actually a risk, or cases where the tool would just refuse to work (which isn't a risk IMO). And it sounded ambiguously scary in a don't-blame-us, we're-avoiding-your-eyes kind of way because of passive voice. You can see my changes here: http://code.google.com/p/maatkit/source/detail?r=5269 I think that's a pretty realistic balanced statement of risk: you are playing with a powerful tool, so learn how to use it first. Thanks for the feedback! BTW, there's also a Maatkit mailing list that I watch closely: http://groups.google.com/group/maatkit-discuss - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Hi Baron, I'm the primary author of Maatkit. Awkward... :-) What can I say -- you could go buy a commercial off-the-shelf tool and believe the song and dance they feed you about the tool being perfect. There's not a single commercial software solution in our toolbox. We're big fans of CentOS, LVS, heartbeat, ldirectord, tomcat, MySQL, Xen, pureFTP, and more. We've been happy with the performance and reliability of all of our FOSS tools. I'm definitely not a Kool-aid drinker when it comes to commercial product marketing. At least with Maatkit, you get transparency. We make a concerted effort to update the RISKS section of each tool with each release, so there is full disclosure. Fair enough, but I still found the warnings a little too scary. A more complete explanation of the exact nature of the bugs and the exact circumstances under which I should be concerned about triggering them would have increased my comfort level. I think Maatkit is by far the best solution for live master-slave sync in most real-world situations. We'll give it another look. -- Eric Robinson Disclaimer - December 9, 2009 This email and any files transmitted with it are confidential and intended solely for Baron Schwartz,Gavin Towey,Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Eric, There are ways to resync data that don't involve all this as well: Maatkit has some tools I've looked with great interest at Maatkit, but their tools are replete with warnings about dangers, bugs, and crashes. They certainly do not inspire confidence. I'm the primary author of Maatkit. What can I say -- you could go buy a commercial off-the-shelf tool and believe the song and dance they feed you about the tool being perfect. At least with Maatkit, you get transparency. We make a concerted effort to update the RISKS section of each tool with each release, so there is full disclosure. I think Maatkit is by far the best solution for live master-slave sync in most real-world situations. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Let's face it, sometimes the master and slave get out of sync, even when 'show slave status' and 'show master status' indicate that all is well. And sometimes it is not feasible to wait until after production hours to resync them. We've been working on a method to do an emergency hot-resync during production hours with little or no user downtime. What do you guys think of this approach? It's only for Linux, though... 1. Shut down the slave and remove its replication logs (master.info and *relay* files). 2. Do an initial rsync of the master to the slave. Using rsync's bit-differential algorithm, this quickly copies most of the changed data and can be safely be done against a live database. This initial rsync is done before the next step to minimize the time during which the tables will be read-locked. 3. Do a 'flush tables with read lock;reset master' on the master server. At this point, user apps may freeze briefly during inserts or updates. 4. Do a second rsync, which goes very fast because very little data has changed between steps 2 and 3. 5. Unlock the master tables. 6. Restart the slave. When you're done, you have a 100% binary duplicate of the master database on the slave, with no worries that some queries got missed somewhere. The master was never stopped and users were not severely impacted. (Mileage may vary, of course.) We've tried this a few times and it has seemed to work well in most cases. We had once case where the slave SQL thread did not want to restart afterwards and we had to do the whole thing again, only we stopped the master the second time. Not yet sure what that was all about, but I think it may have been a race issue of some kind. We're still exploring it. Anyway, comments would be appreciated. -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
i have two questions. (1) innodb? (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? tom On 12/4/09 6:34 AM, Robinson, Eric eric.robin...@psmnv.com wrote: Let's face it, sometimes the master and slave get out of sync, even when 'show slave status' and 'show master status' indicate that all is well. And sometimes it is not feasible to wait until after production hours to resync them. We've been working on a method to do an emergency hot-resync during production hours with little or no user downtime. What do you guys think of this approach? It's only for Linux, though... 1. Shut down the slave and remove its replication logs (master.info and *relay* files). 2. Do an initial rsync of the master to the slave. Using rsync's bit-differential algorithm, this quickly copies most of the changed data and can be safely be done against a live database. This initial rsync is done before the next step to minimize the time during which the tables will be read-locked. 3. Do a 'flush tables with read lock;reset master' on the master server. At this point, user apps may freeze briefly during inserts or updates. 4. Do a second rsync, which goes very fast because very little data has changed between steps 2 and 3. 5. Unlock the master tables. 6. Restart the slave. When you're done, you have a 100% binary duplicate of the master database on the slave, with no worries that some queries got missed somewhere. The master was never stopped and users were not severely impacted. (Mileage may vary, of course.) We've tried this a few times and it has seemed to work well in most cases. We had once case where the slave SQL thread did not want to restart afterwards and we had to do the whole thing again, only we stopped the master the second time. Not yet sure what that was all about, but I think it may have been a race issue of some kind. We're still exploring it. Anyway, comments would be appreciated. -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
(1) innodb? It's an off-the-shelf application that uses MyISAM tables. It is possible to convert to innodb, but I have not been sold on innodb in terms of its performance characteristics for this particular application. Maybe I've been reading the wrong stuff. Do you have general thoughts on the differences with respect to performance? (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? Well... I guess mainly because I didn't know about that option! I thought I needed to fake out mysql on this, but it sounds like I can just do 'flush tables with read lock;reset master;' on the master and 'change master to...;' on the slave. So cool. Thanks for the input! -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
On 12/4/09 11:59 AM, Robinson, Eric eric.robin...@psmnv.com wrote: (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? Well... I guess mainly because I didn't know about that option! I thought I needed to fake out mysql on this, but it sounds like I can just do 'flush tables with read lock;reset master;' on the master and 'change master to...;' on the slave. So cool. Thanks for the input! 16.1.1 is probably my favorite chapter of the manual. 16.1.1.8 is particularly worth a read. http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
I think he's trying to say that this method wouldn't work for innodb, unless you copied files from an LVM snapshot, or something similar. I would say that it's very important to know why data is getting out of sync between your master and slave. Fixing those root causes would eliminate the need for this. There are cases where non-deterministic queries will produce different results, but that's what row based replication is supposed to solve =) There are ways to resync data that don't involve all this as well: Maatkit has some tools that compare data between servers, and can fix them with queries. No stopping the slave or locking the master necessary. I've used them in production with good results. Regards, Gavin Towey -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Friday, December 04, 2009 9:00 AM To: Tom Worster; mysql@lists.mysql.com Subject: RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much) (1) innodb? It's an off-the-shelf application that uses MyISAM tables. It is possible to convert to innodb, but I have not been sold on innodb in terms of its performance characteristics for this particular application. Maybe I've been reading the wrong stuff. Do you have general thoughts on the differences with respect to performance? (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? Well... I guess mainly because I didn't know about that option! I thought I needed to fake out mysql on this, but it sounds like I can just do 'flush tables with read lock;reset master;' on the master and 'change master to...;' on the slave. So cool. Thanks for the input! -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
On 12/4/09 3:14 PM, Gavin Towey gto...@ffn.com wrote: I would say that it's very important to know why data is getting out of sync between your master and slave. Fixing those root causes would eliminate the need for this. i very much agree. the only instances of slaves getting out of whack that i've experienced was when i screwed something up administratively. There are cases where non-deterministic queries will produce different results, but that's what row based replication is supposed to solve =) 16.3.1 lists some interesting cases to consider: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html There are ways to resync data that don't involve all this as well: Maatkit has some tools that compare data between servers, and can fix them with queries. No stopping the slave or locking the master necessary. I've used them in production with good results. thanks for the pointer. looks handy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
I would say that it's very important to know why data is getting out of sync between your master and slave. Ultimately, I agree. But since it's a canned application, getting to that point might be hard, and once it is resolved, new issues might arise. I would never have any confidence that the replication is solid enough to use the slave server for backup purposes. (Which, by the way, is the real reason I'm doing this. In the middle of the night, when there are few users on the system, I want to backup the slave, but first I want to make sure I have a 100% reliable copy of the data.) There are ways to resync data that don't involve all this as well: Maatkit has some tools I've looked with great interest at Maatkit, but their tools are replete with warnings about dangers, bugs, and crashes. They certainly do not inspire confidence. -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Gavin Towey,Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
I would never have any confidence that the replication is solid enough to use the slave server for backup purposes. I agree completely there. That's the other reason I like filesystem snapshots is that it allows you to take a backup from the master relatively painlessly. -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Friday, December 04, 2009 1:24 PM To: Gavin Towey; Tom Worster; mysql@lists.mysql.com Subject: RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much) I would say that it's very important to know why data is getting out of sync between your master and slave. Ultimately, I agree. But since it's a canned application, getting to that point might be hard, and once it is resolved, new issues might arise. I would never have any confidence that the replication is solid enough to use the slave server for backup purposes. (Which, by the way, is the real reason I'm doing this. In the middle of the night, when there are few users on the system, I want to backup the slave, but first I want to make sure I have a 100% reliable copy of the data.) There are ways to resync data that don't involve all this as well: Maatkit has some tools I've looked with great interest at Maatkit, but their tools are replete with warnings about dangers, bugs, and crashes. They certainly do not inspire confidence. -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Gavin Towey,Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
I would never have any confidence that the replication is solid enough to use the slave server for backup purposes. I agree completely there. That's the other reason I like filesystem snapshots is that it allows you to take a backup from the master relatively painlessly. I've thought of using snapshots. Offhand, can't remember the reason that I decided they would not work for us. It'll come to me... -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Gavin Towey,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication switch Master to slave and back
I have successfully set up a replication master/slave scenario with my MySQL 5.0.51a Now in the event of a fail over to the slave (an identical appliance), I want the old master to become the slave for an eventual switch back the other way. Would it really take the same process to keep the old master up to date with the new one? Or is there a way to bring it up to date with the new machine without a mysqldump or copying data files? I have binary logging running on both machines in hopes that I could just tell the new slave how to catch up with the new master... Any assistance here? 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: Replication switch Master to slave and back
The simplest method may be to set them up in a multi-master configuration, similar to what's documented here: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html This way you won't have to worry about switching between two masters in a failover scenario since they're both writable. -Aaron On Wed, Jul 8, 2009 at 1:38 PM, Cantwell, Bryan bcantw...@firescope.comwrote: I have successfully set up a replication master/slave scenario with my MySQL 5.0.51a Now in the event of a fail over to the slave (an identical appliance), I want the old master to become the slave for an eventual switch back the other way. Would it really take the same process to keep the old master up to date with the new one? Or is there a way to bring it up to date with the new machine without a mysqldump or copying data files? I have binary logging running on both machines in hopes that I could just tell the new slave how to catch up with the new master... Any assistance here? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=aaronb...@gmail.com
Re: Replication switch Master to slave and back
On 8 Jul 2009, at 22:26, Aaron Blew wrote: The simplest method may be to set them up in a multi-master configuration, similar to what's documented here: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html This way you won't have to worry about switching between two masters in a failover scenario since they're both writable. That setup works very well for me, but I also use google's mmm on top of it so that the switch is transparent to client apps. Works beautifully. https://launchpad.net/mysql-mmm Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ smime.p7s Description: S/MIME cryptographic signature
MySQL Master and Slave Database Setup
Hi, I need to run a longer running insert and test it in the Master Slave Replication Database Server. And interrupt the Insert query on the Master and check on slave to ensure that the data is in sync. How can i do it, are there benchmark tools to do that. Thanks and Regards Kaushal
Re: MySQL Master and Slave Database Setup
Use the below link to know how to setup replication. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html regards anandkl On 4/15/08, Kaushal Shriyan [EMAIL PROTECTED] wrote: Hi, I need to run a longer running insert and test it in the Master Slave Replication Database Server. And interrupt the Insert query on the Master and check on slave to ensure that the data is in sync. How can i do it, are there benchmark tools to do that. Thanks and Regards Kaushal
MySQL Master and Slave Database Server
Hi, what are the different test cases to test MySQL Master and Slave Replication Database Server. Thanks and Regards Kaushal
Re: master and slave in same machine
In news:[EMAIL PROTECTED], Rilawich Ango [EMAIL PROTECTED] wrote: I found the the position in show master status also keep as 98 but I am sure the replication from S2 to S1 works. It seems that S2 master hasn't started yet. If by that you mean that you want the changes made on S1 to be replicated to S2, and also through S2 to S3, then you should run MySQL on S2 with log-slave-updates option. Otherwise it will not write into its own binary log statements received from S1 via replication. Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master and slave in same machine
Hi, I tried to set a replication as below. S1(master) --- S2 (slave), (master) -- S3 (slave) I have set S1 and S2 (slave) without problem. However, there is a problem in S2 as a master. I found the the position in show master status also keep as 98 but I am sure the replication from S2 to S1 works. It seems that S2 master hasn't started yet. I have restarted the mysql and found no error in the db log. Anyone can help? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
Kishore Jalleda schrieb: Hi you may be having issues with the byte order on the opetron's and the P4's , this was asked earlier in the list, and here's what Jimmy from Mysql had to say Kishore, Thanks for the suggestion, but all x86 have the same byte order... and as I wrote its not a cluster problem but a replication problem :( btw: I just started the mysql-tests and it hangs, too: db5:/usr/local/mysql/mysql-test# ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-da ta --skip-innodb --skip-ndbcluster --skip-bdb Installing Master Databases 1 running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-da ta1 --skip-innodb --skip-ndbcluster --skip-bdb Installing Slave Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/slave-dat a --skip-innodb --skip-ndbcluster --skip-bdb Manager disabled, skipping manager start. Starting ndbcluster Starting ndbd Starting ndbd Waiting for started... NDBT_ProgramExit: 0 - OK Connected to Management Server at: localhost:9350 Cluster Configuration - [ndbd(NDB)] 2 node(s) id=1@127.0.0.1 (Version: 5.0.18, Nodegroup: 0, Master) id=2@127.0.0.1 (Version: 5.0.18, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=3@127.0.0.1 (Version: 5.0.18) [mysqld(API)] 4 node(s) id=4 (not connected, accepting connect from any host) id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host) id=7 (not connected, accepting connect from any host) Loading Standard Test Databases Starting Tests TESTRESULT --- alias [ pass ] alter_table[ pass ] analyse[ pass ] analyze[ pass ] ansi [ pass ] archive[ pass ] archive_gis[ pass ] now nothing happens, cpuload is at 0 - any ideas? Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
Could you post the error log entries from the slave and the binlog where the slave hangs , also just to make sure d2 and d3 replicate well without any problems from d1 right ? also as per your message d4 and d5 would work well if no replication is enabled at all so essentially its only the replication that is causing the hang right?( could you clarify this for us ) , finally if thats the case and you feel that you have no error on your side ( assuming you have exhausted all the possibilities trying to isloate the problem) but still the slave hangs, then you might want to open up a bug report http://dev.mysql.com/doc/refman/5.0/en/replication-bugs.html hope this helps . Kishore Jalleda On 2/8/06, Jan Kirchhoff [EMAIL PROTECTED] wrote: Kishore Jalleda schrieb: Hi you may be having issues with the byte order on the opetron's and the P4's , this was asked earlier in the list, and here's what Jimmy from Mysql had to say Kishore, Thanks for the suggestion, but all x86 have the same byte order... and as I wrote its not a cluster problem but a replication problem :( btw: I just started the mysql-tests and it hangs, too: db5:/usr/local/mysql/mysql-test# ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-da ta --skip-innodb --skip-ndbcluster --skip-bdb Installing Master Databases 1 running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-da ta1 --skip-innodb --skip-ndbcluster --skip-bdb Installing Slave Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/slave-dat a --skip-innodb --skip-ndbcluster --skip-bdb Manager disabled, skipping manager start. Starting ndbcluster Starting ndbd Starting ndbd Waiting for started... NDBT_ProgramExit: 0 - OK Connected to Management Server at: localhost:9350 Cluster Configuration - [ndbd(NDB)] 2 node(s) id=1@127.0.0.1 (Version: 5.0.18, Nodegroup: 0, Master) id=2@127.0.0.1 (Version: 5.0.18, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=3@127.0.0.1 (Version: 5.0.18) [mysqld(API)] 4 node(s) id=4 (not connected, accepting connect from any host) id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host) id=7 (not connected, accepting connect from any host) Loading Standard Test Databases Starting Tests TESTRESULT --- alias [ pass ] alter_table[ pass ] analyse[ pass ] analyze[ pass ] ansi [ pass ] archive[ pass ] archive_gis[ pass ] now nothing happens, cpuload is at 0 - any ideas? Jan
Re: 5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
A neverending story. I thought it worked (without having an idea what has been the problem), but it broke down again after a few hours. My current set up is: -A p4 production server (Server1) running debian linux, 2.4 kernel, mysql 4.1.13-standard-log. This server is replicating to several other production-servers. -Two new Dual-Opteron Servers (Server2+Server3) with 6GB RAM each, 3ware SATA-RAID, custom kernel 2.6.15.1 SMP, mysql 5.0.18-max-log. Server2 is replicating from Server1 with a few Replicate_Ignore_DB/Replicate_Wild_Ignore_Table rules. I have had problems getting this server running at first since it always hung with replicated queries (different ones) and the only thing helped was to kill -9 the mysqld. At some point it suddenly worked and is running for almost a week now - having replicated at least 20-30GB so far. Server 3 was supposed to become a slave of the first one, but it shows the same problems I had with Server2 at first: it starts to replicate and some query hangs after a few minutes. These are no complicated mass-inserts (those 1-5MB mass-inserts work without trouble), but simple queries like insert into table (a,b,c) values (1,2,3) or update table set a=1 where b=2. I tried kernel 2.6.8, 2.6.15, SMP and non-SMP (debian-kernels and self-compiled), the official mysql-max and mysql-standard-binaries and a self-compiled mysql 5.0.18. I disabled Innodb and Cluster, I put all variables back to the standard values and played around with lots of settings. lspci and the output of /proc/cpuinfo are the same on both servers. I have exactly the same BIOS-settings on both servers (I was going nuts comparing these bios-screens with a KVM in a loud server-room). Both servers have exactly the same debian-packages installed. lsmod shows the same on both systems. I have had trouble with mysql-replication in 3.2x and 4.x in the last years, but I always got everything working and it was was working good without bigger trouble once it was up and running. But this time I have no clue what else to try. I currently have no other server that is powerful enough to handle all the updates being replicated in order to test a 5.0.18 on some other CPU. I'll probably try to get my workstation (p4 3ghz, 1GB RAM) running as a slave hoping the IDE-disk is fast enough, but no matter if that works or not - I don't know what to change/try on my new servers?!? any ideas anybody? thanks Jan Jan Kirchhoff schrieb: I thought I found the reason for my problems with the change in join-behaviour in mysql 5, but Iwas wrong :( there is more trouble :( my replications hangs with simple queries like insert into table (a,b,c) values (1,2,3) on a myisam-table. It just hangs forever with no cpu-load on the slave. I have to kill and restart mysql with the following commands: killall -9 mysqld;sleep 2;mysqladmin shutdown;sleep 5;/etc/init.d/mysql start;sleep 2;mysql -e 'slave start' I can find the changed row in the table, so the query was processed correctly. Then it runs again for some time and hangs again with some other simple insert. I disabled innodb, cluster, took out all my variables out of my.cnf except max_allowed_packet = 16M which I need for the replication to work and I have no clue what the reason for my problem is. what else could I try? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
Hi you may be having issues with the byte order on the opetron's and the P4's , this was asked earlier in the list, and here's what Jimmy from Mysql had to say All machines used in the cluster must have the same architecture; that is, all machines hosting nodes must be either big-endian or little-endian, and you cannot use a mixture of both. For example, you cannot have a management node running on a PPC which directs a data node that is running on an x86 machine. This restriction does not apply to machines simply running mysql or other clients that may be accessing the cluster's SQL nodes. http://mysql.osuosl.org/doc/refman/5.0/en/mysql-cluster-limitations.html So make sure both the opetron's and P4's are running with the same byte order Kishore Jalleda On 2/7/06, Jan Kirchhoff [EMAIL PROTECTED] wrote: A neverending story. I thought it worked (without having an idea what has been the problem), but it broke down again after a few hours. My current set up is: -A p4 production server (Server1) running debian linux, 2.4 kernel, mysql 4.1.13-standard-log. This server is replicating to several other production-servers. -Two new Dual-Opteron Servers (Server2+Server3) with 6GB RAM each, 3ware SATA-RAID, custom kernel 2.6.15.1 SMP, mysql 5.0.18-max-log. Server2 is replicating from Server1 with a few Replicate_Ignore_DB/Replicate_Wild_Ignore_Table rules. I have had problems getting this server running at first since it always hung with replicated queries (different ones) and the only thing helped was to kill -9 the mysqld. At some point it suddenly worked and is running for almost a week now - having replicated at least 20-30GB so far. Server 3 was supposed to become a slave of the first one, but it shows the same problems I had with Server2 at first: it starts to replicate and some query hangs after a few minutes. These are no complicated mass-inserts (those 1-5MB mass-inserts work without trouble), but simple queries like insert into table (a,b,c) values (1,2,3) or update table set a=1 where b=2. I tried kernel 2.6.8, 2.6.15, SMP and non-SMP (debian-kernels and self-compiled), the official mysql-max and mysql-standard-binaries and a self-compiled mysql 5.0.18. I disabled Innodb and Cluster, I put all variables back to the standard values and played around with lots of settings. lspci and the output of /proc/cpuinfo are the same on both servers. I have exactly the same BIOS-settings on both servers (I was going nuts comparing these bios-screens with a KVM in a loud server-room). Both servers have exactly the same debian-packages installed. lsmod shows the same on both systems. I have had trouble with mysql-replication in 3.2x and 4.x in the last years, but I always got everything working and it was was working good without bigger trouble once it was up and running. But this time I have no clue what else to try. I currently have no other server that is powerful enough to handle all the updates being replicated in order to test a 5.0.18 on some other CPU. I'll probably try to get my workstation (p4 3ghz, 1GB RAM) running as a slave hoping the IDE-disk is fast enough, but no matter if that works or not - I don't know what to change/try on my new servers?!? any ideas anybody? thanks Jan Jan Kirchhoff schrieb: I thought I found the reason for my problems with the change in join-behaviour in mysql 5, but Iwas wrong :( there is more trouble :( my replications hangs with simple queries like insert into table (a,b,c) values (1,2,3) on a myisam-table. It just hangs forever with no cpu-load on the slave. I have to kill and restart mysql with the following commands: killall -9 mysqld;sleep 2;mysqladmin shutdown;sleep 5;/etc/init.d/mysql start;sleep 2;mysql -e 'slave start' I can find the changed row in the table, so the query was processed correctly. Then it runs again for some time and hangs again with some other simple insert. I disabled innodb, cluster, took out all my variables out of my.cnf except max_allowed_packet = 16M which I need for the replication to work and I have no clue what the reason for my problem is. what else could I try? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
I thought I found the reason for my problems with the change in join-behaviour in mysql 5, but Iwas wrong :( there is more trouble :( my replications hangs with simple queries like insert into table (a,b,c) values (1,2,3) on a myisam-table. It just hangs forever with no cpu-load on the slave. I have to kill and restart mysql with the following commands: killall -9 mysqld;sleep 2;mysqladmin shutdown;sleep 5;/etc/init.d/mysql start;sleep 2;mysql -e 'slave start' I can find the changed row in the table, so the query was processed correctly. Then it runs again for some time and hangs again with some other simple insert. I disabled innodb, cluster, took out all my variables out of my.cnf except max_allowed_packet = 16M which I need for the replication to work and I have no clue what the reason for my problem is. what else could I try? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs
I've been trying to get my new mysql-5.0.18-servers running as slaves of our
production systems to check if all our applications work fine with mysql 5 and
to do some tests and tuning on the new servers.
The old servers are all P4s, 3GB RAM running debian-linux, 2.4-kernel and
official mysql 4.1.13-standard-log binaries:
d1 is the master, d2 and d3 are slaves.
my new servers are dual-opterons, 6 GB RAM, running debian-linux with a
2.6.15-SMP-kernel, official mysql 5.0.18-max-log-binary. their names are d4 and
d5. I am currently trying to get d4 running as a slave of d1. d5 should later
become a slave of d4.
The old servers only have myisam and memory-tables, innodb is disabled. The new
ones had innodb and mysql-cluster enabled (datanodes running on the same
servers, management-node running on d3) since I wanted to do some testing with
the different engines, but I disabled both temporarily without any change in
this weird problem:
No matter if I do a copy of the /var/lib/mysql of d1 (and dump the contents of the memory-tables) while a flush tables with read lock is active and copy that to d4 (and doing a change master to... on d4 afterwards) or if I do a mysqldump --master-data=1:
The replication runs for maybe a minute or two and then hangs. show slave status says everything is OK but a
replicated replace hangs in the processlist and nothing happens. CPU-load goes down to zero. Even after 2 hours
nothing changed, a slave stop hangs, too, when I kill the replicated replace-process nothing happens and
I can't stop the mysql server and have to kill it with killall -9 mysqld in the shell :(
At first I thought this was a problem with a temporary table, but after having
reloaded a new dump a few times I had the same problem with really simple
inserts/updates like:
A new dump, everything works for a few minutes, then this query hangs:
| 4 | system user | | nachrichten | Connect | 11164 | update |replace into nachrichten.x_symbole (symbol,syscode,nachrichten_id) values('KUN','de','99949')
(taken directly from show processlist)
Info about the simple table:
CREATE TABLE `x_symbole` (
`symbol` char(20) NOT NULL default '',
`syscode` char(6) NOT NULL default '',
`nachrichten_id` int(11) NOT NULL default '0',
PRIMARY KEY (`symbol`,`syscode`,`nachrichten_id`),
KEY `nachrichten_id` (`nachrichten_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I have to kill the mysqld with killall -9 mysqld, do a mysqladmin shutdown
again and then restart mysql and issue the query in the mysql-shell: it works!
Then I issue a start slave, everything works again for a minute or two and
hangs with some different query.
I go nuts with this! I spent so much time with this problem and did not get any further and I have absolutely no idea what the problem is. nothing in the error log.
Can anybody suggest something that might help? I have no idea whats wrong!
regards
Jan
d4:
mysql show variables;
+-++
| Variable_name | Value
|
+-++
| auto_increment_increment| 1
|
| auto_increment_offset | 1
|
| automatic_sp_privileges | ON
|
| back_log| 50
|
| basedir |
/usr/local/mysql-max-5.0.18-linux-x86_64-glibc23/ |
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 15728640
|
| character_set_client| latin1
|
| character_set_connection| latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server| latin1
|
| character_set_system| utf8
|
| character_sets_dir |
/usr/local/mysql-max-5.0.18-linux-x86_64-glibc23/share/mysql/charsets/ |
| collation_connection| latin1_swedish_ci
Query caused different errors on master and slave.
Anyone know what to do when SHOW SLAVE STATUS says: Query caused different errors on master and slave. Master: MySQL 4.1.9-Debian_2-log Slave: MySQL 4.1.5-gamma-log Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master and slave with different versions
Hello, Right now we are running a mysql-max db with InnoDb for transaction tracking. This is a older version of mysql running mysql-3.23 build 50. We are planning on upgrading to mysql 4.0 latest in a couple of weeks (hopefully). Anyways in order to do proper backups with InnoDB I am going to use the following method: 1. Make current mysql server master 2. Setup secondary mysql server as a slave so nightly I can bring down the slave to do binary backups. The slave server will be on a backup box and is only for backup purposes. It will not be uses by any production system. The question for the list is ... can I use the latest version of mysql on the slave server so when we upgrade the master in a few weeks / month the master and slave will be at the same version ? Or does the slave have to be version 3.23 build 50 ? Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master and slave with different versions
Michael Gale wrote: Hello, Right now we are running a mysql-max db with InnoDb for transaction tracking. This is a older version of mysql running mysql-3.23 build 50. We are planning on upgrading to mysql 4.0 latest in a couple of weeks (hopefully). Anyways in order to do proper backups with InnoDB I am going to use the following method: 1. Make current mysql server master 2. Setup secondary mysql server as a slave so nightly I can bring down the slave to do binary backups. The slave server will be on a backup box and is only for backup purposes. It will not be uses by any production system. The question for the list is ... can I use the latest version of mysql on the slave server so when we upgrade the master in a few weeks / month the master and slave will be at the same version ? Or does the slave have to be version 3.23 build 50 ? 4.0 slave can replicate 3.23 master, but you might run in a couple of glitches. If you are using LOAD LOCAL DATA INFILE, for example, there would be a problem. But the same problem would exist even if you used 3.23 to 3.23. You may also hit a couple of bugs, as this kind of replication has not been as well field-tested as 3.23 to 3.23 or 4.0 to 4.0. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's the best way to align master and slave before starting replication?
Hello colleagues, I want to set up a replication schema on MySQL (ISAM tables). I then have to align the databases before starting the replication. I would like to NOT stop the database to do the alignment because the application needs a lot of time to startup (1 hour) and I may have to start and stop the replication several times during the application life (don't care about binary logs, they are always enabled!) My database is composed by several pieces ... What is the best way to perform the alignment including the costs Vs. benefits? * tar cvf of the datafiles? Is it absolutely needed to shutdown the database? * mysqlhotcopy? can I keep ON the application? * mysqldump? How can I to lock all the database structures? what's about the application transactions? are they frozen on the COMMIT until the tables are unlocked? Thanks Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
clarification needed for a one master, one slave replication scenario
Hello, I have basic replication working between a master and slave. I'm attempting to clarify what happens during a master failure, master reintegration scenario using heartbeat- and mysql-3.23.52-27. Clients connect to a movable IP managed by heartbeat. M=master, S=slave # my.cnf settings M - log-bin, server_id=1 S - log-bin, server_id=2 and various replication settings (master-host=M, etc.) 1. M fails Heartbeat moves the client IP to S S writes to its binary log but still considers M the master -- Are any commands required here to prevent S from replicating from M when M returns? slave stop? 2. M reintegrates as slave Client IP stays on S M: change master to master_host=S... etc. M replicates from S 3. Slave fails Heartbeat moves the Client IP to M M writes to its binary log slave stop? reset master? 4. Slave reintegrates as slave based on my.cnf settings Have I missed anything? Do I have all my data? If anyone can assist with this methodology, I'd be thankful. - Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master and slave
I am using 1 master (4.0.13 named PISSARRO ) and 3 slaves (4.0.15); of course the my.ini are different in the master and slaves. Today for an error (I thinked to be on my slave) I executed in sequence these commands: change master to master_host='pissarro',master_user='root',master_password='',master_log_file ='pissarro-bin.313',MASTER_LOG_POS=4 SLAVE START and then thread slave start as you can see below ! but this command were on my MASTER ! 031013 22:33:15 InnoDB: Started MySql: ready for connections. Version: '4.0.13-max-nt-log' socket: '' port: 3306 031014 11:17:32 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'pissarro-bin.352' at position 4 031014 11:17:45 Slave I/O thread exiting, read up to log 'pissarro-bin.352', position 98405708 031014 11:17:45 Slave SQL thread exiting, replication stopped in log 'pissarro-bin.352' at position 3 Immediately I see the error and execute a slave stop But it seem is possible to start a slave command on a master to update itself ! There is way to avoid this error ? tks in advance - Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication - master and slave on the same computer
Hi list, I have a question about replication in MySQL. I have mysql 4.0.12 (master and slave) on windows 2000. Can I make master and slave on the same computer? If I have the master on disk c, and the slave on disk d: 1) When do I put the my.ini file of the slave? 2) How can I stop and start the slave server? If I run the command NET STOP Mysql it stop the server on disk c.. Thanks very much Inbal --8 I think it should be possible. Trying the same thing here under linux with a config like this: [mysqld_multi] mysqld = /usr/bin/safe_mysqld mysqladmin = /usr/bin/mysqladmin user = user password = pass [mysqld1] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port= 3306 pid-file= /var/lib/mysql/hostname.pid skip-locking skip-innodb set-variable= max_connections=300 set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=384 set-variable= sort_buffer=128M set-variable= join_buffer_size=64M set-variable= record_buffer=512M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=256M log-bin server-id = 1 [mysqld2] datadir = /var/lib/mysqlrep socket = /var/lib/mysqlrep/mysql.sock port= 3307 pid-file= /var/lib/mysqlrep/hostname.pid skip-locking skip-innodb set-variable= max_connections=300 set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=384 set-variable= sort_buffer=128M set-variable= join_buffer_size=64M set-variable= record_buffer=512M set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=256M master-host = localhost master-user = repl master-password = password master-port = 3306 server-id = 2 replicate-do-db = nuria replicate-ignore-db = mysql [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid HOWEVER, although it seems to try and replicate there must still be something with this config, because i can run both daemons but not at the same time. E.g. mysqld_multi start 1 works fine but when I start the second the errorlog shows me this: 030527 17:26:14 mysqld started /usr/libexec/mysqld: ready for connections 030527 17:26:14 Slave thread: error connecting to master: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=536866816 record_buffer=536866816 sort_buffer=134217720 max_used_connections=0 max_connections=300 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4193097 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x4001f40c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80e345c 0x4007d618 0x 0x80e3a5b 0x400782b6 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Number of processes running now: 0 Still looking into it.. Let me know when you find out anything.
RE: replication - master and slave on the same computer
You need to start 2 instances of mysqld both on different ports and different datadirs. I recommend reading Paul Dubois MySQL second Edition- The definitive guide to using, programming, and administering mysql 4 databases. He has a section on replication + running multiple instances of mysqld. ---Original Message- --From: Vico Timmermans [mailto:[EMAIL PROTECTED] --Sent: Tuesday, May 27, 2003 8:51 AM --To: [EMAIL PROTECTED] --Subject: replication - master and slave on the same computer -- -- --Hi list, --I have a question about replication in MySQL. --I have mysql 4.0.12 (master and slave) on windows 2000. -- --Can I make master and slave on the same computer? -- --If I have the master on disk c, and the slave on disk d: --1) When do I put the my.ini file of the slave? --2) How can I stop and start the slave server? If I run the command NET --STOP --Mysql it stop the server on disk c.. -- --Thanks very much --Inbal -- 8 -- --I think it should be possible. Trying the same thing here --under linux with a config like this: -- --[mysqld_multi] --mysqld = /usr/bin/safe_mysqld --mysqladmin = /usr/bin/mysqladmin --user = user --password = pass -- --[mysqld1] --datadir = /var/lib/mysql --socket = /var/lib/mysql/mysql.sock --port= 3306 --pid-file= /var/lib/mysql/hostname.pid --skip-locking --skip-innodb --set-variable= max_connections=300 --set-variable= key_buffer=512M --set-variable= max_allowed_packet=1M --set-variable= table_cache=384 --set-variable= sort_buffer=128M --set-variable= join_buffer_size=64M --set-variable= record_buffer=512M --set-variable= net_buffer_length=8K --set-variable= myisam_sort_buffer_size=256M --log-bin --server-id = 1 -- --[mysqld2] --datadir = /var/lib/mysqlrep --socket = /var/lib/mysqlrep/mysql.sock --port= 3307 --pid-file= /var/lib/mysqlrep/hostname.pid --skip-locking --skip-innodb --set-variable= max_connections=300 --set-variable= key_buffer=512M --set-variable= max_allowed_packet=1M --set-variable= table_cache=384 --set-variable= sort_buffer=128M --set-variable= join_buffer_size=64M --set-variable= record_buffer=512M --set-variable= net_buffer_length=8K --set-variable= myisam_sort_buffer_size=256M --master-host = localhost --master-user = repl --master-password = password --master-port = 3306 --server-id = 2 --replicate-do-db = nuria --replicate-ignore-db = mysql -- --[mysql.server] --user=mysql --basedir=/var/lib -- --[safe_mysqld] --err-log=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid -- -- -- -- --HOWEVER, although it seems to try and replicate there must still --be something with this config, because i can run both daemons but --not at the same time. E.g. mysqld_multi start 1 works fine but --when I start the second the errorlog shows me this: -- -- --030527 17:26:14 mysqld started --/usr/libexec/mysqld: ready for connections --030527 17:26:14 Slave thread: error connecting to master: Access denied --for user: '[EMAIL PROTECTED]' (Using password: YES) (0), retry in 60 sec --mysqld got signal 11; --This could be because you hit a bug. It is also possible that this binary --or one of the libraries it was linked against is corrupt, improperly --built, --or misconfigured. This error can also be caused by malfunctioning --hardware. --We will try our best to scrape up some info that will hopefully help --diagnose --the problem, but since we have already crashed, something is definitely --wrong --and this may fail -- --key_buffer_size=536866816 --record_buffer=536866816 --sort_buffer=134217720 --max_used_connections=0 --max_connections=300 --threads_connected=0 --It is possible that mysqld could use up to --key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4193097 --K --bytes of memory --Hope that's ok, if not, decrease some variables in the equation -- --Attempting backtrace. You can use the following information to find out --where mysqld died. If you see no messages after this, something went --terribly wrong... --Cannot determine thread, fp=0x4001f40c, backtrace may not be correct. --Stack range sanity check OK, backtrace follows: --0x80e345c --0x4007d618 --0x --0x80e3a5b --0x400782b6 --0x420de407 --New value of fp=(nil) failed sanity check, terminating stack trace! --Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and --follow --instructions on how to resolve the stack trace. Resolved --stack trace is much more helpful in diagnosing the problem, so please do --resolve it --The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains --information that should help you find out what is causing the crash -- --Number of processes running now: 0 -- -- -- --Still looking into it.. Let me know when you find out anything. -- MySQL General Mailing List For list
Re: replication - master and slave on the same computer
At 17:51 27/5/2003 +0200, Vico Timmermans wrote: Hi, Hi list, I have a question about replication in MySQL. I have mysql 4.0.12 (master and slave) on windows 2000. Can I make master and slave on the same computer? Yes. Assuming that you want the master on drive C and the slave on drive D, the lines begin # are my comments: # set the service for master: mysqld-max-nt --install or mysqld-max-nt --install-manual # the commands above installs a service called MySQL # set the service for slave mysqld-nt --install myslave or mysqld-nt --install-manual myslave # the commands above installs a service called myslave now using the same \winnt\my.ini file [mysqld] basedir=c:/mysql datadir=c:/mysql/data port=3306 [myslave] #notice that the server group is the same of the service name basedir=c:/mysql datadir=d:/mysql/data port=3307 #follow the complementary instructions how to set a master and a #slave locating the set keys for master under [mysqld] and the #set keys for the slave under [myslave]. #Run and stop the services using the name mysql for master and #myslave for the slave. If I have the master on disk c, and the slave on disk d: 1) When do I put the my.ini file of the slave? 2) How can I stop and start the slave server? If I run the command NET STOP Mysql it stop the server on disk c.. -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ São Paulo - Brazil /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master (pc) Slave (laptop) Replication
I have set up replication and have it working properly for the most part. If I update the master the slave follows suit. But my slave is a laptop, that I use for testing but would like that data to stay up to date withthe master. Overnight if 'live changes take place on the Master, while the slave is not connected, How to I sync up the slave ? (without dumping the db or creating a snapshot) mysql - master = 3.23.33 mysql - slave = 3.23.52 Thank you, --- -Daniel Negron // \\* / _ __ ___ \Lotus Notes Admin / Developer | | |/ /| _ ) | KB Electronic, Inc. | | ' | _ \ | 12095 NW 39th Street | |_|\ \|___/ | Coral Springs, FL. 33065 \ \_\ /954.346.4900 x 122 \\ // email: [EMAIL PROTECTED] - http://www.kbelectronics.com --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Master (pc) Slave (laptop) Replication
At 11:48 -0500 3/11/03, Daniel Negron/KBE wrote: I have set up replication and have it working properly for the most part. If I update the master the slave follows suit. But my slave is a laptop, that I use for testing but would like that data to stay up to date withthe master. Overnight if 'live changes take place on the Master, while the slave is not connected, How to I sync up the slave ? (without dumping the db or creating a snapshot) mysql - master = 3.23.33 mysql - slave = 3.23.52 It shouldn't matter. Slave servers retry occasionally when the connection breaks. There are startup options you can use to set the number of retries and the interval between them. Thank you, --- -Daniel Negron // \\* / _ __ ___ \Lotus Notes Admin / Developer | | |/ /| _ ) | KB Electronic, Inc. | | ' | _ \ | 12095 NW 39th Street | |_|\ \|___/ | Coral Springs, FL. 33065 \ \_\ /954.346.4900 x 122 \\ // email: [EMAIL PROTECTED] - http://www.kbelectronics.com --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Master (pc) Slave (laptop) Replication
It is automatic. Next time you connect your laptop to the network, and start mysqld on your laptop, it will catch up to the master provided the bin logs are still on the master. Daniel Negron/KBE wrote: I have set up replication and have it working properly for the most part. If I update the master the slave follows suit. But my slave is a laptop, that I use for testing but would like that data to stay up to date withthe master. Overnight if 'live changes take place on the Master, while the slave is not connected, How to I sync up the slave ? (without dumping the db or creating a snapshot) mysql - master = 3.23.33 mysql - slave = 3.23.52 Thank you, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Master (pc) Slave (laptop) Replication
It is automatic. Next time you connect your laptop to the network, and start mysqld on your laptop, it will catch up to the master provided the bin logs are still on the master. The reason i am asking is because it is not updating a couple of records. But if I create a new db with some contents in it, it replicates with no problem. is there anyway to force a sync ? Thank you, --- -Daniel Negron // \\* / _ __ ___ \Lotus Notes Admin / Developer | | |/ /| _ ) | KB Electronic, Inc. | | ' | _ \ | 12095 NW 39th Street | |_|\ \|___/ | Coral Springs, FL. 33065 \ \_\ /954.346.4900 x 122 \\ // email: [EMAIL PROTECTED] - http://www.kbelectronics.com --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Different Table type between MASTER and SLAVE
Hello. I'm build a system aka of Shopping mall.(I think most of queies will be SELECT) For performance, MySQL will run on replication. I need TRANSACTIONs to manages orders and payment. So, Master's Table type is InnoDB. But I think that InnoDB is slower than MyISAM. (InnoDB's row level locking is very good. but, My system will query SELECT most cases) So, I decide that Master's table uses InnoDB Slave's table uses MyISAM. This has any problem? Any sugesstion will please me. Thank you for advanced answer! ## Heo, Jungsu Mr. SimpleX Internet. http://www.simplexi.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Different Table type between MASTER and SLAVE
Yes, this shouldn't be a problem. Master on InnoDB Slaves on MyIsam. Only after a commit on the master does it get replicated to the slave. Regards P.Murali Placed At : Heo, Jungsu [EMAIL PROTECTED] on 01/03/2003 01:37:50 PM To: [EMAIL PROTECTED] cc:(bcc: Murali P/Satyam) Subject: Different Table type between MASTER and SLAVE Hello. I'm build a system aka of Shopping mall.(I think most of queies will be SELECT) For performance, MySQL will run on replication. I need TRANSACTIONs to manages orders and payment. So, Master's Table type is InnoDB. But I think that InnoDB is slower than MyISAM. (InnoDB's row level locking is very good. but, My system will query SELECT most cases) So, I decide that Master's table uses InnoDB Slave's table uses MyISAM. This has any problem? Any sugesstion will please me. Thank you for advanced answer! ## Heo, Jungsu Mr. SimpleX Internet. http://www.simplexi.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Different Table type between MASTER and SLAVE
Yes, this shouldn't be a problem. Master on InnoDB Slaves on MyIsam. Only after a commit on the master does it get replicated to the slave. Regards P.Murali Placed At : Heo, Jungsu [EMAIL PROTECTED] on 01/03/2003 01:37:50 PM To: [EMAIL PROTECTED] cc:(bcc: Murali P/Satyam) Subject: Different Table type between MASTER and SLAVE Hello. I'm build a system aka of Shopping mall.(I think most of queies will be SELECT) For performance, MySQL will run on replication. I need TRANSACTIONs to manages orders and payment. So, Master's Table type is InnoDB. But I think that InnoDB is slower than MyISAM. (InnoDB's row level locking is very good. but, My system will query SELECT most cases) So, I decide that Master's table uses InnoDB Slave's table uses MyISAM. This has any problem? Any sugesstion will please me. Thank you for advanced answer! ## Heo, Jungsu Mr. SimpleX Internet. http://www.simplexi.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Bothway Replication - Fail Back - Hold Master while Slave is Synchronizing.
MySQL Bothway Replication - Fail Back - Hold Master while Slave is Synchronizing. I have the following question: Is it possible to have a slave on machine M1 synchronizing with a master on another machine M2 while the master on the first machine M1 does not accept requests yet (i.e. it is not available to a client yet)? Background info: Why do I want this? Well, I have a configuration where two machines run MySQL. They are configured to replicate each others updates. Although two masters are effectively running, only one is used at a time. This is taken care of by the client. When the first machine fails, the client detects this and goes to the other machine. So for so good. When machine M1 comes on-line again then our client immediately starts to use it (fail-back) (it has - let's say - a preferred machine and I can't disable this). The database, however, is not correct yet. The slave needs to synchronize with M2 still. So we read old data. This situation could be avoided if we can let the master on M1 wait until the salve on M1 has completed its synchronization, and the databases are in sync again. Thanks, Johan. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
master and slave source code
Hi there, I was looking for the master and client source code but I was unable to find, I found the rpm but not source in tar format. I ma tiring to configure 2 linux server one master and one slave, can you tell me what's the best way to accomplish this and what settings I have to change. any help will be really appreciated. Thanks Ali, Mohammed Systems Administrator iWon.com One Bridge Street, Suite #42 Irvington, NY 10533 (914) 591-2000 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mutual Replication Fails Occasionally Under Heavy Stress with Master-Slave/Slave-Master Configuration
Description:
While setting up a mutual master-slave/slave-master MySQL application for
High-Availability, redundancy, and load-balancing, the autoincrement functionality of
disparate servers seem to be detached from replication functionality. It's likely the
replication code can be improved to consider multiple master-slave arrangements
trivially.
How-To-Repeat:
Get yourself a 64-bit DEC Alpha Linux server (Not Necessary, but, Fun):
Linux tempe.dedserius.com 2.4.2 #3 Sun Feb 25 16:07:31 MST 2001 alpha unknown
root@tempe:~# cat /proc/cpuinfo
cpu : Alpha
cpu model : EV5
cpu variation : 0
cpu revision: 0
cpu serial number : Linux_is_Great!
system type : Alcor
system variation: 0
system revision : 0
system serial number: MILO-
cycle frequency [Hz]: 300014100 est.
timer frequency [Hz]: 1024.00
page size [bytes] : 8192
phys. address bits : 40
max. addr. space # : 127
BogoMIPS: 593.48
kernel unaligned acc: 106 (pc=fc4f26e0,va=fc000417a836)
user unaligned acc : 1 (pc=1200cda00,va=2f8a912)
platform string : N/A
cpus detected : 0
root@tempe:~# free
total used free sharedbuffers cached
Mem:189440 184224 5216 0 42696 17808
-/+ buffers/cache: 123720 65720
Swap: 257008 3104 253904
In separate terminals the following was run from Tempe on a 100/Mbps HUB-based network:
Tempe passes data at 8MBytes/Sec over his NIC. Connectivity is not likely to be a
factor.
/usr/local/apache/bin/ab -n 100 -c 20 http://scottsdale.ds.lan/index.php3
/usr/local/apache/bin/ab -n 100 -c 20 http://pima.ds.lan/index.php3
The index.php3 script does many things, but, included is an insert to a db with 2
tables:
Newstats with the following structure:
CREATE TABLE newstats (
nid int(10) NOT NULL auto_increment,
Referer varchar(150) NOT NULL,
UserAgent varchar(75) NOT NULL,
ScriptFilename varchar(150) NOT NULL,
RemoteHost varchar(50) NOT NULL,
RemoteAddr varchar(15) NOT NULL,
HitTime datetime DEFAULT '-00-00 00:00:00' NOT NULL,
Host varchar(25) NOT NULL,
AcceptLanguage varchar(15) NOT NULL,
QryString varchar(150) NOT NULL,
PRIMARY KEY (nid),
KEY HitTime (HitTime),
KEY RemoteHost (RemoteHost),
KEY RemoteAddr (RemoteAddr),
KEY ScriptFilename (ScriptFilename)
);
CREATE TABLE pagestats (
uri varchar(96) NOT NULL,
hits bigint(21) NOT NULL,
since datetime,
PRIMARY KEY (uri)
);
CREATE TABLE excludes (
ip varchar(25)
);
The query selects from excludes and if the host isn't in there does an insert into
both of the first tables.
After the query on excludes, it does a select from both tables. So for each hit,
there's a select on excludes and an insert if not found, then a select on the tables.
Queries:
SELECT * FROM excludes WHERE ip = '192.168.1.2'
UPDATE pagestats SET hits=hits+1 WHERE uri = '/www/www.dedserius.com/index.php3'
INSERT INTO newstats VALUES ('', '', 'ApacheBench/1.3c',
'/www/www.dedserius.com/index.php3', 'tempe.ds.lan', '192.168.1.2', '20010320
00:34:47', 'scottsdale.ds.lan', '', '/index.php3')
Scottsdale is a Dual 400 MHz Celeron with 128MBytes RAM;
Pima is a Single PII 200 MHz with 64MBytes RAM;
Both machines run Slackware Linux 7.2; patches for GLIBC 2.2.
scottsdale:/mysql# gcc -v
Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
scottsdale:/mysql# uname -a
Linux scottsdale 2.3.99-pre9 #2 SMP Fri Feb 23 16:14:56 MST 2001 i686 unknown
root@pima:/mysql# gcc -v
Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
root@pima:/mysql# uname -a
Linux pima 2.4.2 #2 Wed Mar 14 01:56:45 MST 2001 i586 unknown
MySQL-3.23.33 built from source.
./configure --with-mysqld-user=mysql --localstatedir=/mysql
I have public and private name servers. Public servers end with dedserius.com.
Private with ds.lan and reverse nslookup on privates function. DBase host
authentication is based on DNS not Host lookups. This is for future implementation of
round-robbin DNS decisions.
The replication succeeded when hitting Scottsdale, first.
Failed when hitting Pima first.
Failure was due to autoincrement field containing duplicate value during the benchmark.
my.cnf:
Scottsdale:
# The MySQL server
[mysqld]
port= 3306
log-bin = scottsdale-bin.log
server-id
