Re: How to Optimize distinct with index
Please don't change the subject on someone else's thread. Next time, post a new message instead of hitting reply on an unrelated message. In the last episode (Jun 19): Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table however. I have thousands of queries per second. How to optimize it? Because of the distinct clause, mysql has to remember all of the user_id values during the query so it can remove duplicates. You do have an index containing all of your fields, which certainly helps performance, but because the index is sorted with user_id last, mysql can't use that index to perform the distinct operation. Imagine your query returns 1000 rows with 998 unique usernames that happen to have key1 values from 2..999, and one duplicate username that happens to have rows with key1=1 and key1=1000. Because it's using the index to fetch data, rows will be sorted by key1, and the duplicate name will be in the first and last rows. Mysql needs to store the names in a temporary table to be able to remove the duplicates. Now, if user_id were first, mysql could use it directly to remove duplicates (since it would see duplicate names next to each other), but it wouldn't be able to use that index in your where clause.. :( You can't win in this case. Luckily, temporary tables aren't bad as long as they are small and mysql doesn't have to write them to disk. To be sure, run show status like 'created_%' before and after a query and see if the Created_tmp_disk_tables number increases. As long as the temp tables stay in RAM, your query will be efficient. http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Created_tmp_disk_tables Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by user_id; I add index on user_id, but after in, order use temporary table, How to optimize it? Mysql should have been able to use the index here, I think. Please post the output of create table user, a sample query, and its EXPLAIN output. -- Dan Nelson dnel...@allantgroup.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: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
In the last episode (Jun 18), Matt Neimeyer said:
I'm converting a PHP app from using Visual FoxPro as the database backend
to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX
10.4. The end application will be deployed cross platform and to both 4.x
and 5.x MySQL servers.
This query returned 21 records in .27 seconds:
SELECT zip FROM zipcodes WHERE
degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
5
Ouch. You might want to calculate the rectange enclosing your target
distance, add an index on lat (and/or long), and add the rectangle check to
your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
ln2 AND ln2. That way mysql can use the index to pare down most of the rows
without having to call all those trig functions for every zipcode.
This query returned 21442 records in 1.08 seconds:
SELECT custzip FROM customers
This query is still running half an hour later, with a Time of 2167
and a State of Sending Data (according to the mysql process list)
SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM
zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
5)
When I try to EXPLAIN the query it gives me the following...
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using
index
2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where
Neither mysql 4 or 5 are very smart when it comes to subqueries. Unless
your inner query is dead simple, mysql assumes it's a dependent subquery and
runs it once per row in your outer query. You might want to try mysql 6 and
see if it does any better. For example, here are explain plans for mysql 5
and 6 for the following query on the famous Oracle emp sample table:
select ename from emp where mgr in
(select empno from emp where ename in ('scott'));
+---+
| ename |
+---+
| ADAMS |
+---+
1 row in set (0.00 sec)
Mysql 5.1.30:
+++---+-+---+-+-+--+--+-+
| id | select_type| table | type| possible_keys | key |
key_len | ref | rows | Extra |
+++---+-+---+-+-+--+--+-+
| 1 | PRIMARY| emp | ALL | NULL | NULL|
NULL| NULL | 14 | Using where |
| 2 | DEPENDENT SUBQUERY | emp | unique_subquery | PRIMARY,ENAME | PRIMARY |
4 | func |1 | Using where |
+++---+-+---+-+-+--+--+-+
Note that it didn't use an index on the outer query, and had to examine all
14 rows. It even used the wrong index on the inner query :)
Mysql 6.0.11:
++-+---+--+---+---+-++--+---+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+--+---+---+-++--+---+
| 1 | PRIMARY | emp | ref | PRIMARY,ENAME | ENAME | 13 | const
|1 | Using index condition |
| 1 | PRIMARY | emp | ref | MGR | MGR | 5 |
test.emp.EMPNO |2 | |
++-+---+--+---+---+-++--+---+
Note that the queries have flipped and aren't nested anymore (id is 1 on
both queries). The first query uses the ename index and estimates it will
return one row. The second query uses the mgr index based on the empno
value returned by the first query and estimates it will return 2 rows. Much
better :)
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
safe query prevent sites from hijacker
Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's Thanks B.S.Bharanikumar
Re: BULK DATA HANDLING 0.5TB
st...@edberg-online.com schrieb: At 11:10 AM +0530 6/13/09, Krishna Chandra Prajapati wrote: Hi guys, I'm working in a telecom company. I have table called deliverylog in which 30 million records gets inserted per/day. The table has grown to 0.5TB I have to keep 60days record in the table. So, 60days * 30 million = 1800 million records. The query is taking a lot of time to fetch the result. Please sugget me what storage engine must be used and how i can get the things done. Is there any other alternative. Any response is highly appreciated. Thanks, Krishna Can you provide us with more details about the current configuration? Eg, MySQL version, current database engine, and the result of an EXPLAIN on the problematic queries. Just offhand, unless you need transactions/foreign keys/all the other niceties of InnoDB, I would suspect MyISAM would be the fastest engine, but hard to say for sure. There's a lot of room for performance optimization with all of the system variables as well (eg; increasing key buffers if you have adequate RAM). You can eke out more performance by putting indexes and tables on different drives on different channels. Some references: Book: High Performance MySQL, Second Edition http://oreilly.com/catalog/9780596101718/ Useful tips from the authors of the above book: http://www.mysqlperformanceblog.com/ And assuming you are using MySQL 5.0: Optimization Overview http://dev.mysql.com/doc/refman/5.0/en/optimize-overview.html Table OPTIMIZE command http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html Using EXPLAIN http://dev.mysql.com/doc/refman/5.0/en/using-explain.html http://dev.mysql.com/doc/refman/5.0/en/explain.html MySQL system variables http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html steve and take a look at partions (available with =5.1), btw do not forget to force one-file-per-table that make handling a lot more easy. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
SELECT zip FROM zipcodes WHERE
degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515
5
Ouch. You might want to calculate the rectange enclosing your target
distance, add an index on lat (and/or long), and add the rectangle check to
your where clause: WHERE latitude BETWEEN lt1 AND lt2 AND longitude BETWEEN
ln2 AND ln2. That way mysql can use the index to pare down most of the rows
without having to call all those trig functions for every zipcode.
I like this idea the best (it always bothered me running a query that
involved multiple mathmatical functions).
So... Here's the scratch php code I ended up with... Anyone see any
problems with it? The only problem I see is that I think the old code
was more circular this will be a square (within the limits of a
square on a non-spherical earth... etc.. etc..) ... so there will be
more zip codes included in the corners. If there are too many
complaints about that I might look at some sort of overlapping
rectangle scheme instead of a square.
function ChangeInLatitude($Miles) { return rad2deg($Miles/3960); }
function ChangeInLongitude($Lat, $Miles) { return
rad2deg($Miles/3960*cos(deg2rad($Lat))); }
$Miles = 5;
$OriginalLat = 39.0788994;
$OriginalLon = -77.1227036;
$ChangeInLat = ChangeInLatitude($Miles);
$ChangeInLon = ChangeInLongitude($OriginalLat, $Miles);
$MinLat = $OriginalLat-$ChangeInLat;
$MaxLat = $OriginalLat+$ChangeInLat;
$MinLon = $OriginalLon-$ChangeInLon;
$MaxLon = $OriginalLon+$ChangeInLon;
My only other question is... when I explained the new query... On the
dependent subquery it says possible keys are zip, longitude and
latitude but it used zip. It seems like a better index would be
longitude or latitude? On the primary query, even though there is an
index on custzip it doesn't say it's using ANY indexes. I should
probably leave well enough alone... but I'm curious.
Thanks again!
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: safe query prevent sites from hijacker
bharani kumar schrieb: Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's pull the plug for the mains and save energy. there is no silver bullet. take a lecture in security and you will scream who much simple mistakes are made already. security is a habit, a target at best. re, wh -- 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 Optimize distinct with index
Select user_id from user where key1=value and key2=value2 and key3=value2 GROUP BY user_id is faster than Select distinct user_id from user where key1=value and key2=value2 and key3=value2; 2009/6/18 周彦伟 yanwei.z...@opi-corp.com: Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: safe query prevent sites from hijacker
pull the plug for the mains and save energy.. It's still early, but it was only a matter of time before people on this list start typing what I was thinking. But for starter, check out http://shiflett.org/ and read his Essential PHP Security book. On Fri, Jun 19, 2009 at 10:03 AM, walter harmswha...@bfs.de wrote: bharani kumar schrieb: Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's pull the plug for the mains and save energy. there is no silver bullet. take a lecture in security and you will scream who much simple mistakes are made already. security is a habit, a target at best. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: safe query prevent sites from hijacker
Of course I'm assuming you are using PHP. On Fri, Jun 19, 2009 at 10:28 AM, Darryle Steplightdstepli...@gmail.com wrote: pull the plug for the mains and save energy.. It's still early, but it was only a matter of time before people on this list start typing what I was thinking. But for starter, check out http://shiflett.org/ and read his Essential PHP Security book. On Fri, Jun 19, 2009 at 10:03 AM, walter harmswha...@bfs.de wrote: bharani kumar schrieb: Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's pull the plug for the mains and save energy. there is no silver bullet. take a lecture in security and you will scream who much simple mistakes are made already. security is a habit, a target at best. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: safe query prevent sites from hijacker
yes am using PHP , On Fri, Jun 19, 2009 at 7:59 PM, Darryle Steplight dstepli...@gmail.comwrote: Of course I'm assuming you are using PHP. On Fri, Jun 19, 2009 at 10:28 AM, Darryle Steplightdstepli...@gmail.com wrote: pull the plug for the mains and save energy.. It's still early, but it was only a matter of time before people on this list start typing what I was thinking. But for starter, check out http://shiflett.org/ and read his Essential PHP Security book. On Fri, Jun 19, 2009 at 10:03 AM, walter harmswha...@bfs.de wrote: bharani kumar schrieb: Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's pull the plug for the mains and save energy. there is no silver bullet. take a lecture in security and you will scream who much simple mistakes are made already. security is a habit, a target at best. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bharanikumariyer...@gmail.com -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
It sounds like you want to use spatial indexes, but they only became available in v4.1 http://dev.mysql.com/doc/refman/5.0/en/create-index.html http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html You would need to switch your table type from InnoDB to MyISAM, which is fairly easy with ALTER TABLE. But that should allow you to drop all your calculations in the query. You don't have to do any re-architecture to change you subquery to a join: SELECT custzip FROM customers JOIN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) AS zips ON custzip=zip Often times that simple change speeds things up considerably in MySQL. An explain should show it has a DERIVED TABLE if I recall correctly. Brent Baisley On Thu, Jun 18, 2009 at 9:06 PM, Matt Neimeyerm...@neimeyer.org wrote: I'm converting a PHP app from using Visual FoxPro as the database backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX 10.4. The end application will be deployed cross platform and to both 4.x and 5.x MySQL servers. This query returned 21 records in .27 seconds. SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5 This query returned 21442 records in 1.08 seconds. SELECT custzip FROM customers This query is still running half an hour later, with a Time of 2167 and a State of Sending Data (according to the mysql process list) SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) When I try to EXPLAIN the query it gives me the following... id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using index 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where If it matters both tables are INNODB and both customers.custzip and zipcodes.zip are indexed. We used a program called DBConvert from DMSoft to convert the data so it's exactly the same on both the VFP side and the MySQL side. With all that in mind... VFP returns the exact same query in 5-10 seconds and that includes render time in the web browser. By comparison... the query WHERE id IN (SELECT id FROM phone WHERE phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns almost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-architecturing than I want to do... (Unless I'm forced) Especially since more than a few of those solutions suggested precalculating the distance between zipcodes which only works if the distances are known (only allow 10, 50 and 100 mile radi for example) Any ideas? Thanks in advance! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.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: Duplicate key name when importing mysql dump file
Hi Jason, if you run mysql with -f it will ignore any errors and continue importing cat aac.sql | mysql -f -u root AAC Isart On Wed, Jun 17, 2009 at 8:59 PM, Jason Novotny jason.novo...@gmail.comwrote: Hi, I'm trying to import a dumpfile like so: cat aac.sql | mysql -u root AAC It all runs fine until I get something like: ERROR 1061 (42000) at line 5671: Duplicate key name 'FK_mediaZip_to_zipSet' Is there a way I can tell it to ignore or replace the key? Thanks, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com
Re: Duplicate key name when importing mysql dump file
Hi Jason, You may have to run ANALYZE TABLE.. for the particular table for which you are facing the error. So it'll rebuild the indexes. This would be the best one to save your data. We can use the method which Mr.Isart suggested, but it'll ignore the error and also will lead to data loss. Regards, Vinodh.k On Sat, Jun 20, 2009 at 12:19 AM, Isart Montane isart.mont...@gmail.comwrote: Hi Jason, if you run mysql with -f it will ignore any errors and continue importing cat aac.sql | mysql -f -u root AAC Isart On Wed, Jun 17, 2009 at 8:59 PM, Jason Novotny jason.novo...@gmail.com wrote: Hi, I'm trying to import a dumpfile like so: cat aac.sql | mysql -u root AAC It all runs fine until I get something like: ERROR 1061 (42000) at line 5671: Duplicate key name 'FK_mediaZip_to_zipSet' Is there a way I can tell it to ignore or replace the key? Thanks, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
Matt, This query is still running half an hour later, with a Time of 2167 and a State of Sending Data (according to the mysql process list) SELECT custzip FROM customers WHERE custzip IN ( ... For explanation alternatives see The unbearable slowness of IN() at http://localhost/artful/infotree/queries.php. PB - Matt Neimeyer wrote: I'm converting a PHP app from using Visual FoxPro as the database backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX 10.4. The end application will be deployed cross platform and to both 4.x and 5.x MySQL servers. This query returned 21 records in .27 seconds. SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5 This query returned 21442 records in 1.08 seconds. SELECT custzip FROM customers This query is still running half an hour later, with a Time of 2167 and a State of Sending Data (according to the mysql process list) SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) When I try to EXPLAIN the query it gives me the following... id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using index 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where If it matters both tables are INNODB and both customers.custzip and zipcodes.zip are indexed. We used a program called DBConvert from DMSoft to convert the data so it's exactly the same on both the VFP side and the MySQL side. With all that in mind... VFP returns the exact same query in 5-10 seconds and that includes render time in the web browser. By comparison... the query WHERE id IN (SELECT id FROM phone WHERE phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns almost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-architecturing than I want to do... (Unless I'm forced) Especially since more than a few of those solutions suggested precalculating the distance between zipcodes which only works if the distances are known (only allow 10, 50 and 100 mile radi for example) Any ideas? Thanks in advance! Matt No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.364 / Virus Database: 270.12.78/2185 - Release Date: 06/18/09 05:53:00
Master-Master Replication Problem
I have configured 2 MySQL server to do master-master replication. Below is my config: Node A server-id = 1 log_bin = mysql-bin expire_logs_days= 10 max_binlog_size = 100M binlog_do_db= clustertest binlog_do_db= gateway binlog_do_db= reporting binlog_do_db= traffic binlog_ignore_db= mysql binlog_ignore_db= test replicate-same-server-id = 0 log-slave-updates auto-increment-increment = 10 auto-increment-offset= 1 master-host = 10.1.20.103 master-user = replicator master-password = username master-port = 3306 replicate-do-db = clustertest replicate-do-db = gateway replicate-do-db = reporting replicate-do-db = traffic Node B server-id = 2 log_bin = mysql-bin expire_logs_days= 10 max_binlog_size = 100M binlog_do_db= clustertest binlog_do_db= gateway binlog_do_db= reporting binlog_do_db= traffic binlog_ignore_db= mysql binlog_ignore_db= test replicate-same-server-id = 0 log-slave-updates auto-increment-increment = 10 auto-increment-offset= 2 master-host = 10.1.20.102 master-user = replicator master-password = username master-port = 3306 replicate-do-db = clustertest replicate-do-db = gateway replicate-do-db = reporting replicate-do-db = traffic I restart MySQL daemon, and go to mysql shell and execute this on both server: Node A flush tables with read lock; show master status \G Node b stop slave CHANGE MASTER TO MASTER_HOST='10.1.20.102', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.01', MASTER_LOG_POS=98; start slave I went back to Node A and execute unlock tables; Node B flush tables with read lock; show master status \G Node A stop slave CHANGE MASTER TO MASTER_HOST='10.1.20.103', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.01', MASTER_LOG_POS=98; start slave I went back to Node B and execute unlock tables; After that I try to insert new record from Node A and I see Node B is synchronized. But when I try to insert new record from Node B. I see nothing change at Node A. What I missed here? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Setting up host password on a shared server
I have MySQL set up and running, but I am under the impression that I am unable to password protect it properly because I can't protect it as root user. From what I've been reading I should be setting it up as mysqladmin -u root password _thepassword_ But with phpMyAdmin I can still log in as other users. Is there a way around this? I am under the impression that I could not. Much thanks for any help! Michael Katz
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
Peter, On Thu, Jun 18, 2009 at 9:27 PM, Peter Brawleypeter.braw...@earthlink.net wrote: For explanation alternatives see The unbearable slowness of IN() at http://localhost/artful/infotree/queries.php. you prolly meant to not post a url pointing at your local copy of your website. This works better for most of us: http://www.artfulsoftware.com/infotree/queries.php ;) Walter -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
