Re: Master Master Replication ... do a fail over and a week agos data is revealed.
2010/10/18 Brent Clark brentgclarkl...@gmail.com: Hiya I run MySQL Master - Master Replication. Ive had an interesting situation whereby I failed over using heartbeat but whats is interesting is that via the application (vbulletin), I see that the forums was showing that a weeks ago data. Why using M-M replication? The book of High performance Mysql says it is not a reliable mechanism. Regards. -- 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 Master Replication ... do a fail over and a week agos data is revealed.
On Tue, Oct 19, 2010 at 9:48 AM, short cutter shortcut...@126.com wrote: 2010/10/18 Brent Clark brentgclarkl...@gmail.com: Hiya I run MySQL Master - Master Replication. Ive had an interesting situation whereby I failed over using heartbeat but whats is interesting is that via the application (vbulletin), I see that the forums was showing that a weeks ago data. Why using M-M replication? The book of High performance Mysql says it is not a reliable mechanism. There's various reasons why - almost all my setups also use it. It's not a reliable mechanism for dual-active setups, but as a hot standby there's nothing wrong with it whatsoever. Read the book again :-) I don't have a straight explanation about why the secondary master offers data from a week ago, though. If replication is running, maybe there's something going on with the binlogging on the primary ? Check the primary's master status and the secondary's slave status; check what's in the primary's binlogs and in the secondary's relay logs; if need be check the traffic that goes over the replication interface. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Failover on master/slave replication
Good day all I am hoping someone has had some experience in this to assist me. I have been going through lots of forums and documentation and can find a lot of information on how to setup a master/slave replication on MySQL. The question I have however is how do you fail over to the slave server in the event that the master server is unavailable and then how to revert back to the master server once the server is available again. This is one thing I can't seem to get much info on unless I am looking in the wrong places. I would really appreciate help on this one. Regards Machiel
Re: Failover on master/slave replication
Quoting Machiel Richards machi...@rdc.co.za: The question I have however is how do you fail over to the slave server in the event that the master server is unavailable and then how to revert back to the master server once the server is available again. Hi, to fail over to the slave, you dont need to do anything (except be sure that replication is off). That is, tell your apps etc to use the slave IP/hostname, change the slave server IP to that of the master or update DNS so that all connections are made to the slave instead of the master. To reinstate the master after a failover, you must copy your live database (now on slave) to the master and reinitiate replication pretty much as you did when replication was initially setup. Copying the live DB can be achieved via restoring a backup (from the live slave) or you could look at Maakit which can analyse table differences and copy only changed data (useful for very large DBs, Ive not personally used it tho). Thats about it I think, unless anyone wants to correct me on that... thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Failover on master/slave replication
That's pretty much it, indeed. You need to make absolutely sure that no more connections can be made to the old, broken master, though - even if you have to physically pull the network or power cable. Failover services refer to this as STONITH: Shoot The Other Node In The Head. Don't think but it went down anyway, that's why I failed over - sometimes the service unexpectedly manages to recover itself after you've switched; and any data that gets inserted into the wrong system is as good as lost, unless you want to go through a manual reconciliation before reinitialising the replication. On Tue, Oct 19, 2010 at 11:55 AM, a.sm...@ukgrid.net wrote: Quoting Machiel Richards machi...@rdc.co.za: The question I have however is how do you fail over to the slave server in the event that the master server is unavailable and then how to revert back to the master server once the server is available again. Hi, to fail over to the slave, you dont need to do anything (except be sure that replication is off). That is, tell your apps etc to use the slave IP/hostname, change the slave server IP to that of the master or update DNS so that all connections are made to the slave instead of the master. To reinstate the master after a failover, you must copy your live database (now on slave) to the master and reinitiate replication pretty much as you did when replication was initially setup. Copying the live DB can be achieved via restoring a backup (from the live slave) or you could look at Maakit which can analyse table differences and copy only changed data (useful for very large DBs, Ive not personally used it tho). Thats about it I think, unless anyone wants to correct me on that... thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Master Master Replication ... do a fail over and a week agos data is revealed.
Johan, You state that master - master is not reliable in dual active environments. I am in the process of setting up just such an environment (moderate active on the primary server, lighter activity on the other server.) Do you know where I can get some information on the risks? Thanks, Carl - Original Message - From: Johan De Meersman vegiv...@tuxera.be To: short cutter shortcut...@126.com Cc: Brent Clark brentgclarkl...@gmail.com; mysql@lists.mysql.com Sent: Tuesday, October 19, 2010 5:10 AM Subject: Re: Master Master Replication ... do a fail over and a week agos data is revealed. On Tue, Oct 19, 2010 at 9:48 AM, short cutter shortcut...@126.com wrote: 2010/10/18 Brent Clark brentgclarkl...@gmail.com: Hiya I run MySQL Master - Master Replication. Ive had an interesting situation whereby I failed over using heartbeat but whats is interesting is that via the application (vbulletin), I see that the forums was showing that a weeks ago data. Why using M-M replication? The book of High performance Mysql says it is not a reliable mechanism. There's various reasons why - almost all my setups also use it. It's not a reliable mechanism for dual-active setups, but as a hot standby there's nothing wrong with it whatsoever. Read the book again :-) I don't have a straight explanation about why the secondary master offers data from a week ago, though. If replication is running, maybe there's something going on with the binlogging on the primary ? Check the primary's master status and the secondary's slave status; check what's in the primary's binlogs and in the secondary's relay logs; if need be check the traffic that goes over the replication interface. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Failover on master/slave replication
You may also want to take a look at MySQL MMM which makes use of Active/passive masters to makes MySQL failover very simple. On 19 October 2010 11:45, Johan De Meersman vegiv...@tuxera.be wrote: That's pretty much it, indeed. You need to make absolutely sure that no more connections can be made to the old, broken master, though - even if you have to physically pull the network or power cable. Failover services refer to this as STONITH: Shoot The Other Node In The Head. Don't think but it went down anyway, that's why I failed over - sometimes the service unexpectedly manages to recover itself after you've switched; and any data that gets inserted into the wrong system is as good as lost, unless you want to go through a manual reconciliation before reinitialising the replication. On Tue, Oct 19, 2010 at 11:55 AM, a.sm...@ukgrid.net wrote: Quoting Machiel Richards machi...@rdc.co.za: The question I have however is how do you fail over to the slave server in the event that the master server is unavailable and then how to revert back to the master server once the server is available again. Hi, to fail over to the slave, you dont need to do anything (except be sure that replication is off). That is, tell your apps etc to use the slave IP/hostname, change the slave server IP to that of the master or update DNS so that all connections are made to the slave instead of the master. To reinstate the master after a failover, you must copy your live database (now on slave) to the master and reinitiate replication pretty much as you did when replication was initially setup. Copying the live DB can be achieved via restoring a backup (from the live slave) or you could look at Maakit which can analyse table differences and copy only changed data (useful for very large DBs, Ive not personally used it tho). Thats about it I think, unless anyone wants to correct me on that... thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Master Master Replication ... do a fail over and a week agos data is revealed.
On Tue, Oct 19, 2010 at 1:03 PM, Carl c...@etrak-plus.com wrote: Johan, You state that master - master is not reliable in dual active environments. I am in the process of setting up just such an environment (moderate active on the primary server, lighter activity on the other server.) Do you know where I can get some information on the risks? On the MySQL site, I guess :-) Your major issue is likely to be the lack of two-phase commits (which has been added in the most recent release, iirc): even if you properly interleave your autoincrements, the lack of a true global lock means you still risk conflicting updates. I can't really tell you a single spot where I got my knowledge on the subject - I'm a long-time and multi-platform DBA, so it's a combination of stuff I read and an understanding of how things work - or don't. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: How to Install mysql from source
Hi, There is a detailed readme (text file) within the file you have downloaded. It will gives you the complete picture. Krishna cgi.com On Mon, Oct 18, 2010 at 9:24 PM, ml ml mliebher...@googlemail.com wrote: Hello List, i am trying to install mysql on debian lenny from source. Here is what i did (yes, i did read the INSTALL-SOURCE): ./configure --prefix=/usr/local/mysql/ make -j 4 make install cp support-files/my-medium.cnf /etc/my.cnf cd /usr/local/mysql chown -R mysql . chgrp -R mysql . bin/mysql_install_db --user=mysql FATAL ERROR: Could not find mysqld The following directories were searched: /usr/libexec /usr/sbin /usr/bin If you compiled from source, you need to run 'make install' to copy the software into the correct location ready for operation. If you are using a binary release, you must either be at the top level of the extracted archive, or pass the --basedir option pointing to that location. == Okay, this my make some sense. Whats the _correct_ way to solve this? Which mysql_install_db options do i need here? bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ Installing MySQL system tables... 101018 11:50:13 [ERROR] /usr/local/mysql//libexec/mysqld: unknown option '--skip-bdb' 101018 11:50:13 [ERROR] Aborting 101018 11:50:13 [Note] /usr/local/mysql//libexec/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. == Why does it also log to /var/lib/mysql ? find /var/lib/mysql/ /var/lib/mysql/ /var/lib/mysql/mysql /var/lib/mysql/test /var/lib/mysql/mysql-bin.index Cheers, Mario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
If within If ?
Hi, I've the following query : SELECT IFNULL(SUM(gate_receipts),0) AS gate_receipts, competitions.caption FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' However, I want to check the value of competitions.competition_type and if it is 'cup' I want to divide the value of gate_receipts by 2. How is the best way to achieve this ? Regards Neil
RE: Failover on master/slave replication
The short answer is this: You need special software outside of MySQL to do this. This software has to - Keep a heartbeat going between the two servers. Whether or not you do this by checking only MySQL, or the machines themselves, depends upon what other applications might be running (web server, etc.) - In the event of a failure, it should cause a shutdown of the primary server MACHINE. - The secondary MACHINE should adopt the IP address of the failed primary. Switching the machines back to the original configuration is done (or at least initiated) by hand, because you need to identify and fix whatever the problem was. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Tuesday, October 19, 2010 5:15 AM To: mysql mailing list Subject: Failover on master/slave replication Good day all I am hoping someone has had some experience in this to assist me. I have been going through lots of forums and documentation and can find a lot of information on how to setup a master/slave replication on MySQL. The question I have however is how do you fail over to the slave server in the event that the master server is unavailable and then how to revert back to the master server once the server is available again. This is one thing I can't seem to get much info on unless I am looking in the wrong places. I would really appreciate help on this one. Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Purposely Corrupting a table
Ok, been googling all morning, and keep getting the same post (on multiple different sites). Is there a way, where i corrupt a table purposely? I've tried playing with the .MYD file, and yeah, it marks it deleted under the check routine, but the table is still readable/writable, just doesn't have any info when selecting it... is there another way to corrupt the table, where you can't even select from it, or the responce back from a select is an error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Purposely Corrupting a table
Hi, Keep key_buffer_size very low and insert lots of data into table (myisam). After some time the index file will crash. Krishna CGI.COM On Tue, Oct 19, 2010 at 7:53 PM, Steve Staples sstap...@mnsi.net wrote: Ok, been googling all morning, and keep getting the same post (on multiple different sites). Is there a way, where i corrupt a table purposely? I've tried playing with the .MYD file, and yeah, it marks it deleted under the check routine, but the table is still readable/writable, just doesn't have any info when selecting it... is there another way to corrupt the table, where you can't even select from it, or the responce back from a select is an error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Fwd: If within If ?
Hi, I think I've managed to do it : SELECT IF(competitions.competition_type = 'cup', IFNULL(ROUND(SUM(gate_receipts)/2),0), IFNULL(SUM(gate_receipts),0))AS gate_receipts FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' Cheers Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 19, 2010 at 2:01 PM Subject: If within If ? To: [MySQL] mysql@lists.mysql.com Hi, I've the following query : SELECT IFNULL(SUM(gate_receipts),0) AS gate_receipts, competitions.caption FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' However, I want to check the value of competitions.competition_type and if it is 'cup' I want to divide the value of gate_receipts by 2. How is the best way to achieve this ? Regards Neil
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Of course you know you did not delete r...@localhost, just root @ '%'
which generally should not matter to mysqldump.
What I suspect is the issue here is that the database you are trying
to dump contains procedures/methods that were defined by a user while
logged in as root@'%'. If you recreate those procs as your database
user, this should correct.
- michael dykman
On Tue, Oct 19, 2010 at 8:40 AM, Krishna Chandra Prajapati
prajapat...@gmail.com wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
uninstall/reinstall
I had an old version of mySQL on my computer but never used it. I thought I knew the root password but it wouldn't let me in. I went ahead and uninstalled it thinking I would get a newer version anyway. I used the Windows interface to uninstall the previous version. It appeared to be gone but when I installed the new version, it asked me what the previous root password was and then give new one. I left previous blank and gave it the new password. Now it has been sitting at the screen in the configuration wizard for Apply security settings for about 20 minutes. How should I recover from this? Barracuda 400 vers 3.5.12 Checked - Virus Free
Re: Purposely Corrupting a table
It's easy to corrupt the MYISAM index (MYI) file... I do something like this in linux -- assuming your table is not tiny, and mysql isn't running or you have a lock on the table: dd if=table.MYI of=table2.MYI bs=2048 count=100 then copy table2.MYI over table.MYI and then flush tables and then unlock. Your table will be unreadable until you rebuild the index with REPAIR TABLE or myisamchk. The MYD file will remain intact. If your MYI file is smaller than 200k, then just reduce the count=#. -Hank On Tue, Oct 19, 2010 at 7:53 PM, Steve Staples sstap...@mnsi.net wrote: Ok, been googling all morning, and keep getting the same post (on multiple different sites). Is there a way, where i corrupt a table purposely? I've tried playing with the .MYD file, and yeah, it marks it deleted under the check routine, but the table is still readable/writable, just doesn't have any info when selecting it... is there another way to corrupt the table, where you can't even select from it, or the responce back from a select is an error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: uninstall/reinstall
Tammie, You might just try resetting the root password http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-windows. Hope this helps, Tyler On 10/19/10 12:38 PM, Montgomery, Tammie wrote: I had an old version of mySQL on my computer but never used it. I thought I knew the root password but it wouldn't let me in. I went ahead and uninstalled it thinking I would get a newer version anyway. I used the Windows interface to uninstall the previous version. It appeared to be gone but when I installed the new version, it asked me what the previous root password was and then give new one. I left previous blank and gave it the new password. Now it has been sitting at the screen in the configuration wizard for Apply security settings for about 20 minutes. How should I recover from this? Barracuda 400 vers 3.5.12 Checked - Virus Free -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re:How to install multiple instances in windows
Hi all, How to install multiple instances in windows??? Thank you.
Re: Purposely Corrupting a table
I'll tell a much easier way to corrupt table. Open the data or index file, remove some text data in the file and save. It will show it a corrupt. ( Only for test setups ). On Tue, Oct 19, 2010 at 10:11 PM, Hank hes...@gmail.com wrote: It's easy to corrupt the MYISAM index (MYI) file... I do something like this in linux -- assuming your table is not tiny, and mysql isn't running or you have a lock on the table: dd if=table.MYI of=table2.MYI bs=2048 count=100 then copy table2.MYI over table.MYI and then flush tables and then unlock. Your table will be unreadable until you rebuild the index with REPAIR TABLE or myisamchk. The MYD file will remain intact. If your MYI file is smaller than 200k, then just reduce the count=#. -Hank On Tue, Oct 19, 2010 at 7:53 PM, Steve Staples sstap...@mnsi.net wrote: Ok, been googling all morning, and keep getting the same post (on multiple different sites). Is there a way, where i corrupt a table purposely? I've tried playing with the .MYD file, and yeah, it marks it deleted under the check routine, but the table is still readable/writable, just doesn't have any info when selecting it... is there another way to corrupt the table, where you can't even select from it, or the responce back from a select is an error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: How to install multiple instances in windows
by running it on different ports. On Tue, Oct 19, 2010 at 10:42 PM, kranthi kranthikiran@gmail.comwrote: Hi all, How to install multiple instances in windows??? Thank you. -- Thanks Suresh Kuna MySQL DBA
mysql_tzinfo_to_sql tz_file rebuilds tables for each tzdata!
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html#c11545 Note that if you are trying to keep your own database of cities and their timezones, you can NOT use the mysql.time_zone_name.Time_zone_id as your FK because the key will change the next time you get a new tzdata update (like from an apt-get update) and re-run mysql_tzinfo_to_sql tz_file. You'll have to store the Name column instead otherwise you loose data integrity. That script wipes the tables and re-creates them! rather than doing an UPDATE to existing records as you would reasonably expect. *sigh* -- 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 Master Replication ... do a fail over and a week agos data is revealed.
To Clarify this a bit: You can only reliably do writes to one server. Your reads can go to both servers with no problems. For reads that need to read data that was just written though, you need to read from the 'active' master, as you can not rely on replication to be instantaneous. On Tue, Oct 19, 2010 at 20:07, Johan De Meersman vegiv...@tuxera.be wrote: On Tue, Oct 19, 2010 at 1:03 PM, Carl c...@etrak-plus.com wrote: Johan, You state that master - master is not reliable in dual active environments. I am in the process of setting up just such an environment (moderate active on the primary server, lighter activity on the other server.) Do you know where I can get some information on the risks? On the MySQL site, I guess :-) Your major issue is likely to be the lack of two-phase commits (which has been added in the most recent release, iirc): even if you properly interleave your autoincrements, the lack of a true global lock means you still risk conflicting updates. I can't really tell you a single spot where I got my knowledge on the subject - I'm a long-time and multi-platform DBA, so it's a combination of stuff I read and an understanding of how things work - or don't. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Walter Heck Founder @ OlinData (http://olindata.com) Co-founder @ Tribily (http://tribily.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: Failover on master/slave replication
On Tue, Oct 19, 2010 at 19:06, John Daisley daisleyj...@googlemail.com wrote: You may also want to take a look at MySQL MMM which makes use of Active/passive masters to makes MySQL failover very simple. +1 We could not do much of our daily work without MMM. It makes the whole HA/Failover thing a breeze. We have it in use at many of our customers and wouldhave a much harder time managing them (and failures) without it. Don't try to do failovers by hand, use MMM instead. Cheers, -- Walter Heck Engineer @ Open Query (http://openquery.com) Exceptional services for MariaDB and MySQL at a fixed budget -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hellpo Krishna,
On 10/19/2010 8:40 AM, Krishna Chandra Prajapati wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Not true. The utility mysqldump is just a client like any other program
and can authenticate with the MySQL instance (the database daemon) as
any valid user.
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhantanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
To fix this, you need to reset the DEFINER for a TRIGGER defined within
the database so that it is defined as a valid user account.
http://dev.mysql.com/doc/refman/5.1/en/triggers.html
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
In order to issue the LOCK TABLES command, a user needs certain privileges:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Securing MySQL is fairly easy. Check out this guide in the manual for
details:
http://dev.mysql.com/doc/refman/5.1/en/security.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Yes, r...@localhost entry is still present in user table. Only root@'%' is deleted. So it's not obvious to fail. Hi yu.zou, The r...@localhost entry already had all privileges, except this entry had empty password column. u...@localhost entry before GRANT ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | root| | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y | Y | Y | Y | Y| || | | 0 | 0 | 0 | 0 | However, I still gave the following cmd. mysql GRANT select, lock tables ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password'; mysql flush privileges; u...@localhost entry after GRANT == ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost
