Re: Locked myself out
On Mon, Mar 29, 2004 at 08:44:28PM -0800, Gene H. Dreher wrote: I seem to have locked myself out of my database. RH8/4.0.18-standard ... the key user does not have file/reload/etc access. and no access at all to database mysql.. Is there some way to break this down as system root or is the only solution to delete the whole thing and re-install it? stop the mysql process, then restart it with the --skip-grant-tables option, reset the passwords, and then restart it as normal. -- Jim Richardson http://www.eskimo.com/~warlock Have you ever noticed that at trade shows Microsoft is always the one giving away stress balls? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speeding up MySQL server
On Thu, Mar 25, 2004 at 07:21:04PM -, Matt Chatterley wrote: Hmm. Taking a stab in the dark here, but.. If it's purely a 'hit counter' type affair, and you're updating a single row, perhaps performance could be gained by instead inserting into a table (presumably this can be done with single-row level locking - at least in InnoDB?), and running a regular job that aggregates the contents of this table into the counter row? Data won't be up-to-date instantly, but it might reduce contention. Do you experience contention when the counter is being read from? If so, this could possibly be reduced using a 'dirty read' (no locking on select), so that although slightly outdated information may be read, no updates would be delayed? Depends on what you're doing, and what you're trying to achieve, really! If I understand you correctly, you are suggesting to create a temp table, and update that all the time, then add that sum to the perm table every once in a while? In this case, the counter is only checked (for stats purposes) once an hour, so updating it at 30min increments would be fine (at our hit rate, that's about 180,000 hits) which would save a fair amount of contention time on the main table (If I understand it correctly). I'll have to look into this. -- Jim Richardson http://www.eskimo.com/~warlock Madness takes its toll. Please have exact change ready. signature.asc Description: Digital signature
Speeding up MySQL server
I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. -- Jim Richardson http://www.eskimo.com/~warlock All true wisdom is found on T-shirts. signature.asc Description: Digital signature
Re: Speeding up MySQL server
On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. signature.asc Description: Digital signature
Max Open Tables
I am trying to push the performance of a Mysql database a little more, it's pretty busy, but I hope to squeeze a tad more out of it. I am a newbie/pretty clueless wrt MySQL in general, so ... I run the status command, and see the following. mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Connection id: 27840031 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.13-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 4 days 14 hours 26 min 56 sec Threads: 161 Questions: 128065100 Slow queries: 0 Opens: 657 Flush tables: 1 Open tables: 512 Queries per second avg: 322.082 The value for Open Tables is 512, which looks suspiciously to me like it's hit a limit. Is there a way to increase that limit? Or am I misunderstanding what status is telling me? -- Jim Richardson http://www.eskimo.com/~warlock Ahhh... I see the fuck-up fairy has visited us again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max Open Tables
On Tue, Mar 23, 2004 at 11:34:13AM -0800, Jim Richardson wrote: I am trying to push the performance of a Mysql database a little more, it's pretty busy, but I hope to squeeze a tad more out of it. I am a newbie/pretty clueless wrt MySQL in general, so ... I run the status command, and see the following. mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Connection id: 27840031 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.13-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 4 days 14 hours 26 min 56 sec Threads: 161 Questions: 128065100 Slow queries: 0 Opens: 657 Flush tables: 1 Open tables: 512 Queries per second avg: 322.082 The value for Open Tables is 512, which looks suspiciously to me like it's hit a limit. Is there a way to increase that limit? Or am I misunderstanding what status is telling me? I have partially answered my own question, the Open Tables number looks like a limit, but I am not sure I am hitting it. Some digging around has shown that one table of the database in question on this machine, is getting beaucoup writes, about 10 mil/day. It is a MyISAM table, and locking for the writes may be the bottleneck. So my question is, how can I improve this? any suggestions of things to try, or paths to take? -- Jim Richardson http://www.eskimo.com/~warlock Nothing says loser like nymshifter. chrisv in C.O.L.A signature.asc Description: Digital signature
Re: Preventing Duplicate Entries
On Sun, Mar 21, 2004 at 07:24:48PM -0800, Axel IS Main wrote: I have a php app that updates an ever growing table with new information on a regular basis. Very often the information is duplicated. I'm currently handling this by checking the table for duplicate values every time I go to add new data. As you can imagine, as the table grows it takes longer and longer for this to happen, and the process gets slower and slower. In order to speed things up I'm wondering of it might not be a good idea to not allow duplication in a given field. The question is, if there is a duplicate, how will MySQL react? And what's the best way to manage that reaction? Also, will this actually be faster than doing it the way I'm doing it now? Perhaps you could hash all the field values into a single 32bit value, then check for that value in the hash field. You might get a false positive, but they will be few and far between. -- Jim Richardson http://www.eskimo.com/~warlock The race isn't always to the swift, nor the battle to the strong, But it's the safest way to bet. signature.asc Description: Digital signature
Moving server
I am beginning the process of moving an exising MySQL/Apache server, (or rather, several of the sites there) to a new machine, to spread the load, and allow us to upgrade the servers bit by bit. Versions of MySQL are both 3.23.58 Apache and PHP, etc, is set up and humming along nicely, doing mostly nothing yet on the new server, but doing it very well. My plan is as follows. Set up the old server (which I will call Alpha) to binlog it's transactions, and act as a master. Set up the new server (now called Beta, so original) to replicate from Alpha. Dump the DB from Alpha, insert it into Beta, (with Alpha set to not process transactions for this time) then restart Beta, to start replicating. Restart Alpha to run normally. Verify that replication, is occuring, and that all transactions are working well. (is there an easy way to check consistancy between master and slave? RTFM is fine, if you can please point me to the FM in question :) Then after I am sure that all is well, I will play jiggery pokery games with BIND and hopefully, all will be well, after a few hours for the changes to replicate. Is there something on the MySQL end I am missing? forgetting? not taking into account? The firewall rules will block all connections to 3306 that don't come from either Alpha or Beta, the data isn't sensitive in any way, so I am not worried about sniffing, and I will shut the replication connection down after all is working on Beta. Thanks all. -- Jim Richardson http://www.eskimo.com/~warlock All life is a conjugation of the verb to eat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
privileges question
Installing a new RHEL box, with 3.23.58. (it's what RH provides, and I don't feel like using stuff from out of the RH tree.) I am a little confused about the privileges. I have done a GRANT ALL on *.* to root IDENTIFIED BY 'password'; which is great, but if I run mysql so mysql -u [EMAIL PROTECTED] I get in, no password. How can I tell mysql to refuse anything that isn't from localhost, and to require a password for root no matter what? Thanks. New to MySQL and databases in general, trying to make sure I don't screw up too badly. -- Jim Richardson http://www.eskimo.com/~warlock It says he made us all to be just like him. So if we're dumb, then god is dumb, and maybe even a little ugly on the side. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: privileges question
On Mon, Feb 23, 2004 at 05:30:38PM -0600, Paul DuBois wrote: At 15:18 -0800 2/23/04, Jim Richardson wrote: Installing a new RHEL box, with 3.23.58. (it's what RH provides, and I don't feel like using stuff from out of the RH tree.) I am a little confused about the privileges. I have done a GRANT ALL on *.* to root IDENTIFIED BY 'password'; which is great, but if I run mysql so mysql -u [EMAIL PROTECTED] I get in, no password. How can I tell mysql to refuse anything that isn't from localhost, and to require a password for root no matter what? Thanks. New to MySQL and databases in general, trying to make sure I don't screw up too badly. When you specify an account name for the GRANT statement, if you specify only the username part, the hostname part defaults to '%'. So your GRANT statement is actually equivalent to GRANT ... TO 'root'@'%'. Clobber that account and specify GRANT ... TO 'root'@'localhost' instead. Then root will be able to connect only from the localhost. Thanks for your help. I realize now I was misunderstanding something, that anyone can log into mysql from localhost, but they can't *do* much of anything except with test_ databases. That was part of my problem, the rest was answered with your post. Thanks. Now back to reading the MySQL book, the author's name seems familiar... :) -- Jim Richardson http://www.eskimo.com/~warlock $HOME is where your dotfiles are - Gym Quirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advise on High Availability configuration
On Mon, Feb 02, 2004 at 09:17:08AM +0100, A.J.Millan wrote: No, when we implemented high-availability MySQL servers we used MySQL's inbuilt replication - this has been running here for years now and we have had constant DB availability during that time, even though individual machines have failed now and again. We're using 2 masters 4 slaves with the logic for sql reads going to the slaves and sql writes going to the master handled in the application layer. We also have automatic master failover (although I believe MySQL plan to build this into their product at some point in the near future). At the moment we are studying the implementation of a MySQL-Apache high High Availability system. Would you be so kind to explain a bit more detailedly the soft/hard aspects of yours application?. I believe that at the moment this is a hot question. Probably there are many more people interested in yours experience. Greetings. A.J.Millan ZATOR Systems. Very much so! -- Jim Richardson http://www.eskimo.com/~warlock If space is warped, time is all that's weft. signature.asc Description: Digital signature
Re: Read Slaves, and load balancing between them...
On Thu, Jan 29, 2004 at 03:40:17PM -, Andrew Braithwaite wrote: Hi, I employ a simple method, I have a 'status' table on the master and have a cron job that updates this table with the current time (now()) every minute. I test all the slaves each minute and if the time in the status table gets too far behind the actual time then it flags a warning to me. Cheers, A That's a great idea, thanks! On a related note, I have two servers, one slave, one master, and I want to secure the datapath between them. What's the best way ? (on Linux, if that matters) I am considering stunnel, or ssh tunnel, or is there something in MySQL I can use to connect securely? -- Jim Richardson http://www.eskimo.com/~warlock We have to go forth and crush every world view that doesn't believe in tolerance and free speech, - David Brin signature.asc Description: Digital signature
Re: Read Slaves, and load balancing between them...
On Thu, Jan 29, 2004 at 11:47:52PM -, Andrew Braithwaite wrote: I believe MySQL 4.1 has support for ssl replication but it's still alpha at the moment. If you are referring to connecting to a MySQL server from an application to query it then the following applies: ---from the manual--- When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication. - http://www.mysql.com/doc/en/Security.html Thanks for the link. I am mostly concerned with data security. I'll probably wind up using ssh or stunnel. -- Jim Richardson http://www.eskimo.com/~warlock I'll get a life when it is proven and substantiated to be better than what I am currently experiencing. signature.asc Description: Digital signature
Re: BUG IN MYSQL
On Sat, Jan 03, 2004 at 04:05:09PM -0700, Richard S. Huntrods wrote: I've submitted this problem three times now, and been ignored all three times. I guess bugs are simply not popular. Since the problem described below is 100% repeatable, I will now escallate it from problem with delete/insert to BUG IN MYSQL. Cheers... I've distilled the problem I'm having with DELETE/INSERT to an even simpler test case. Here's the SQL - it can be used with any database, not just the special expdb I created. I run this script followed by running mysqlcheck expdb. # USE expdb; DROP TABLE IF EXISTS appraised; CREATE TABLE appraised ( idNumber int(11) NOT NULL default '0', indexNo int(11) NOT NULL default '0', experience int(11) default NULL, lastused int(11) default NULL, competence int(11) default NULL, status int(11) default NULL, appraiser int(11) default NULL, comments text, PRIMARY KEY (idNumber,indexNo) ) TYPE=MyISAM; PRINT; DELETE FROM appraised; INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1'); SELECT * FROM appraised; DELETE FROM appraised; # If you run this with the INSERT commented out, mysqlcheck reports OK. If you don't comment out the INSERT, mysqlcheck reports: C:\mysql-4.0.15\binmysqlcheck expdb expdb.appraised warning : Table is marked as crashed warning : Size of indexfile is: 2048 Should be: 1024 warning : Size of datafile is: 160 Should be: 0 error: Record-count is not ok; is 4 Should be: 0 warning : Found 4 partsShould be: 0 parts error: Corrupt This happens every single time. You cannot use the -autorepair function, as it reports appraised.MYD cannot be opened. What is going on? Ran your exact command sequence here, on MySQL 4.0.16, running on Linux, and it worked just fine. No need to comment out the insert. -- Jim Richardson http://www.eskimo.com/~warlock Man's way to God is with beer in hand. --Koffyar Tribal Wisdom, Nigeria signature.asc Description: Digital signature
Re: Quering user privileges
On Sat, Dec 27, 2003 at 05:40:46PM +0100, Plinio Conti wrote: Yes, a cron job will make the solution more robust. I'm new to *classic* client-server DB apps and I'm still amazed for a so standard issue I have to find tricks. Particularly I wonder at this: standard SQL commands exist to assign (GRANT) and remove (REVOKE) privileges, but there is not a SQL command to query current privileges. At least on MySQL 4.0.16, SHOW GRANTS FOR [EMAIL PROTECTED]; shows me the relevent info. I don't know if that's MySQL specific, or not present in 3.x or something. -- Jim Richardson http://www.eskimo.com/~warlock A conclusion is simply the place where someone got tired of thinking. signature.asc Description: Digital signature
Hot standby database question
I would like to set up two systems, a primary, and a backup, in physically seperate locations. I want the backup to be synced with the primary, and if the primary goes down, dns will resolve to the backup, so it needs to be able to start processing transactions immediately. I am new to MySQL, and to SQL in general, I *think* I can do the first part (stay in sync with the primary) by making the backup a slave, and replicating the DB on a constant basis. But will the slave DB happily start accepting transactions when stuff starts coming in from the webserver? or do I have to do something to allow that in MySQL? As an aside, a recommendation on good beginner and intermediate level MySQL books would be appreciated. I want to RTFM, but I want to make sure it *is* the Fine manual :) Oh, and seasons greetings to all, yes I'm working on Christmas, it's a startup, what can I say... -- Jim Richardson http://www.eskimo.com/~warlock Quantum mechanics: The dreams stuff are made of. signature.asc Description: Digital signature
