Re: Automate Install/Configuration of MySQL on Linux
On Tue, Jul 27, 2010 at 1:56 PM, Todd E Thomas todd_...@ssiresults.com wrote: I'm looking for automation direction... I've found many packages that sit on top of MySQL. For the purposes of consistency I'd like to automate these installs. I've been able to automate the install and configuration of everything except the mysql part. I'm using CentOS 5.5. Installing/verifying is no big deal. It's the MySQL configuration that's holding me up. Basically I've created an expect script. It works 99% but it's a PITA to finish. Here's what I'd like to accomplish: *Set the default admin password # mysqladmin -u root password 'root-password' *login to mysql mysql mysql -u root -p *Drop the anonymous accounts mysql DELETE FROM mysql.user WHERE user = ''; *Sync all of the root passwords mysql UPDATE mysql.user SET Password = PASSWORD('root-password') WHERE User = 'root'; *Remove the test database: mysql drop database test; In another script I would like to create databases for specific packages. EG: Concrete5, for example needs: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY 'admin-password'; If there is a better way to do this than using expect I would greatly appreciate any pointers in the right direction. Bash is comfortable for me and perl is within reach. I'm not much versed in anything else right now. If you are serious about spending time and doing automation well then Puppet or cfengine would be the way to go. As for the basic tasks that you describe, have you considered modifying the rpm/deb/whatever to distribute a data dir with whatever you want? In addition, do you really need expect? Could you get the same effect with good use of the sleep command inside bash? -- Rob Wultsch wult...@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
might need some help recovering tables from trashed DB
i asked about this once upon a time, and might need a little more help here. a friend's mysql hosting provider lost an entire DB, but has managed to recover and hand over the ibdata1 file (or at least some portion of it). when my friend popped into mysql, what he's seeing is that some of the tables appear to be back, but others generate a does not exist diagnostic. by way of trying to help last time, i literally copied the underlying mysql files onto my linux system, then fired up mysql to see what magically appeared, but that was before i even had the ibdata1 file so i wasn't surprised to get very little in the way of recovered data. now, though, with this ibdata1 file, i can try that again -- fire up a new linux box, and manually install the files under /var/lib/mysql. i'm guessing i'll see much of what he's seeing. i just want to verify that, if that's all i have access to and some of the tables still appear to be missing, there's not much i can do. or is there? rday -- Robert P. J. Day Waterloo, Ontario, CANADA Top-notch, inexpensive online Linux/OSS/kernel courses http://crashcourse.ca Twitter: http://twitter.com/rpjday LinkedIn: http://ca.linkedin.com/in/rpjday -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: idle query
I installed iostat and used it. It showed that my MySQL data is striped over four devices. During my idle query each of those four devices has about 25% utilization, which is consistent with the hypothesis that this I/O is the bottleneck. It looks like case closed. I am looking into better serverdisk and rewriting my query along the lines you suggested. Thanks! Mike Spreitzer SMTP: mspre...@us.ibm.com, Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr
concatenate sql query with group by and having
mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A I start mysql with the above command. Then I want to select the rows from the result of the following query, provided that for any rows that have the same symbol, chrom and strand should be the same (basically, discard the rows that have the same symbols but different chrom and strand). Could anybody show me how to do it? select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1; I think that something like SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods)); works for sqlite3 (in terms of syntax). But the following do not work for mysql. Is this a difference between mysql and sqlite3? (I'm always confused by the difference between different variants of SQL) select * from (select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1); -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org