Re: Automate Install/Configuration of MySQL on Linux
To answer both emails...
My first install is Puppet :)
1) I have many clients (schools with Macs/Linux) that could use this
package. This is in the works - good call.
For the sake of DRP (Disaster Recovery Planning) I automate all of my
installs on the 1% off-chance that my backups are partially fouled for
some reason.
2) I used the .sql file that you recommended and wow - it's so much
easier than expect.
Once I had the right key-words (mysql .sql script) Google returned the
right pages:
4.5.1.4. Executing SQL Statements from a Text File
http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html
The problem I have now is variable substitution. But, that's another thread.
Thanks guys,
TT
On 07/27/2010 11:40 PM, Andrés Tello wrote:
just place all your sql sentences in a file, setup the database and
then use:
mysql -uroot -hlocalhost file_with_allsql_you_need.sql
or
cat file_file_with_allsql_you_need.sql | mysql -u root -hlocalhost
and you are done.
On Tue, Jul 27, 2010 at 3:56 PM, Todd E Thomas
todd_...@ssiresults.com mailto: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.
--
Thanks for the assist,
Todd E Thomas
It's a frail music knits the world together.
-Robert Dana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
RE: concatenate sql query with group by and having
With some databases such as MySQL, subqueries have to be explicitly named. For example select * from (select * from (select * from table) sub1) sub2; If not, you will see an error like: ERROR 1248 (42000): Every derived table must have its own alias If I understand your problem correctly, you are looking to limit your result set to only those records that have symbols with a single unique combination of chrom, and strand. If that's correct, something like the query below might work: select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat where geneName in -- returns all geneNames (symbols) with one unique combination of chrom and strand (select geneName from -- returns all unique combinations of symbol, chrom, and strand (select distinct geneName, chrom, strand from refFlat) sub1 group by geneName having count(*) = 1) group by refSeq having count(*) = 1; Date: Wed, 28 Jul 2010 11:10:32 -0500 Subject: concatenate sql query with group by and having From: pengyu...@gmail.com To: mysql@lists.mysql.com 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=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can I have 30GB of Innodb Index on a 16GB 2CPU quad core server?
Hi All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Any suggestions on what I should do? I am thinking of doing one of these: 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. Thanks... Nunzio
Variable Expansion Using MySQL Client and .sql Files
Hey all,
I've found many packages that sit on top of MySQL for various clients.
For the purposes of consistency I'd like to automate these installs.
I've been directed towards using .sql files and they work great.
The trouble I'm having now is that I would like to secure the
installation but variable expansion isn't clicking for me.
My setup is fairly straight-forward:
I have a single installer script that calls all other scripts. This is
how it works:
1) Source in all global environment variables from a working file:
1_GLOBAL_ENV.sh
2) execute script to create mysql db
3) secure mysql
. /root/payload/1_GLOBAL_ENV.sh
...
###---
### Configure MySQL
###---
set -x
mysql -v ${INST_SCRIPTS}/mysqld/secure_mysqld.sql
...
mysql -v ${INST_SCRIPTS}/mysqld/create_db.sql
...
---
The create_db.sql should be similar to this:
Concrete5, for example needs:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON
concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
---
The secure_mysqld.sql script is fairly simple as well:
# Display the current user:
select user();
# Display all default accounts:
SELECT User,Host,password FROM mysql.user;
# Remove anonymous accounts:
DELETE FROM mysql.user WHERE user = '';
# Display all remaining accounts:
SELECT User,Host,password FROM mysql.user;
# Sync root passowrds:
UPDATE mysql.user SET Password = PASSWORD('$PASSWD_PRIV_ROOT') WHERE
User = 'root';
exit
---
It all works pretty well. Variable expansion is the problem. For now all
of my other scripts substitute $PASSWD_PRIV_ROOT (from my
1_GLOBAL_ENV.sh) for the actual password. The *.sql scripts do not.
If anyone can shed some light on this I would appreciate the help.
--
Thanks for the assist,
Todd E Thomas
C: 515.778.6913
It's a frail music knits the world together.
-Robert Dana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CentOS 4.8 no-install of MySQL 5.1.4X???
Hello Gurus, I am trying to see if there is a no install version of MySQL 5.1.4X for Cent OS 4.8? We got a copy for Solaris x86 and it works AWESOME, I cant seem to find one for Cent OS? We wanted to install several flavors and test a 200 MB query script file against it to see how the performance changes between the iterations of the software. Any help / advice is much appreciated. P.S. I know there are RPM's but the last time I tried using RPM's it started to rip other components out of of prod boxes and I was in backup mode restoring from the AM snapshot. Besides RPM's dont like to go up a version then down a version like in a sandbox ;-) Thanks... Nunzio
RE: Variable Expansion Using MySQL Client and .sql Files
You could try it inside a here document:
mysql EOF
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO
'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
EOF
Date: Thu, 29 Jul 2010 14:29:55 -0500
From: todd_...@ssiresults.com
To: mysql@lists.mysql.com
Subject: Variable Expansion Using MySQL Client and .sql Files
Hey all,
I've found many packages that sit on top of MySQL for various clients.
For the purposes of consistency I'd like to automate these installs.
I've been directed towards using .sql files and they work great.
The trouble I'm having now is that I would like to secure the
installation but variable expansion isn't clicking for me.
My setup is fairly straight-forward:
I have a single installer script that calls all other scripts. This is
how it works:
1) Source in all global environment variables from a working file:
1_GLOBAL_ENV.sh
2) execute script to create mysql db
3) secure mysql
. /root/payload/1_GLOBAL_ENV.sh
...
###---
### Configure MySQL
###---
set -x
mysql -v ${INST_SCRIPTS}/mysqld/secure_mysqld.sql
...
mysql -v ${INST_SCRIPTS}/mysqld/create_db.sql
...
---
The create_db.sql should be similar to this:
Concrete5, for example needs:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON
concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
---
The secure_mysqld.sql script is fairly simple as well:
# Display the current user:
select user();
# Display all default accounts:
SELECT User,Host,password FROM mysql.user;
# Remove anonymous accounts:
DELETE FROM mysql.user WHERE user = '';
# Display all remaining accounts:
SELECT User,Host,password FROM mysql.user;
# Sync root passowrds:
UPDATE mysql.user SET Password = PASSWORD('$PASSWD_PRIV_ROOT') WHERE
User = 'root';
exit
---
It all works pretty well. Variable expansion is the problem. For now all
of my other scripts substitute $PASSWD_PRIV_ROOT (from my
1_GLOBAL_ENV.sh) for the actual password. The *.sql scripts do not.
If anyone can shed some light on this I would appreciate the help.
--
Thanks for the assist,
Todd E Thomas
C: 515.778.6913
It's a frail music knits the world together.
-Robert Dana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: CentOS 4.8 no-install of MySQL 5.1.4X???
Yes, I do it all the time. download TAR archive at the bottom of the download list. untar in a custom dir. use dedicated user watch the my.cnf location, its kind of css style, if you have side effects its cause your mysql installation is picking it up also from the wrong location remove /etc/my.cnf, put custom my.cnf into the basedir good luck Claudio You have Intel On 7/29/2010 9:30 PM, Nunzio Daveri wrote: Hello Gurus, I am trying to see if there is a no install version of MySQL 5.1.4X for Cent OS 4.8? We got a copy for Solaris x86 and it works AWESOME, I cant seem to find one for Cent OS? We wanted to install several flavors and test a 200 MB query script file against it to see how the performance changes between the iterations of the software. Any help / advice is much appreciated. P.S. I know there are RPM's but the last time I tried using RPM's it started to rip other components out of of prod boxes and I was in backup mode restoring from the AM snapshot. Besides RPM's dont like to go up a version then down a version like in a sandbox ;-) Thanks... Nunzio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
IS NULL returns Empty set, but I have empty items
Hello,
I don't know what I am missing, but I have this:
mysql SELECT url FROM product WHERE url IS NULL;
Empty set (0.05 sec)
mysql SELECT url FROM product WHERE product_Id = 67;
+--+
| url |
+--+
| |
+--+
1 row in set (0.00 sec)
what goes with this, how can i ensure that this is NULL?
here is the table structure
http://pastie.org/1066140
thanks for any insight.
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: IS NULL returns Empty set, but I have empty items
blank is not null... I'll bet if you did SELECT url FROM product WHERE url = ''; you'll get a row or three. If the field was NULL, your product_id select would look like: +--+ | url | +--+ |NULL | +--+ andy Norman Khine wrote: Hello, I don't know what I am missing, but I have this: mysql SELECT url FROM product WHERE url IS NULL; Empty set (0.05 sec) mysql SELECT url FROM product WHERE product_Id = 67; +--+ | url | +--+ | | +--+ 1 row in set (0.00 sec) what goes with this, how can i ensure that this is NULL? here is the table structure http://pastie.org/1066140 thanks for any insight. -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: IS NULL returns Empty set, but I have empty items
In the last episode (Jul 29), Norman Khine said: I don't know what I am missing, but I have this: mysql SELECT url FROM product WHERE url IS NULL; Empty set (0.05 sec) mysql SELECT url FROM product WHERE product_Id = 67; +--+ | url | +--+ | | +--+ 1 row in set (0.00 sec) what goes with this, how can i ensure that this is NULL? Your url may be the empty string . If it was really NULL, you would see NULL in the resultset. Try SELECT * FROM product WHERE url= -- 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: IS NULL returns Empty set, but I have empty items
thank you, this makes sense.
On Thu, Jul 29, 2010 at 11:29 PM, Dan Nelson dnel...@allantgroup.com wrote:
In the last episode (Jul 29), Norman Khine said:
I don't know what I am missing, but I have this:
mysql SELECT url FROM product WHERE url IS NULL;
Empty set (0.05 sec)
mysql SELECT url FROM product WHERE product_Id = 67;
+--+
| url |
+--+
| |
+--+
1 row in set (0.00 sec)
what goes with this, how can i ensure that this is NULL?
Your url may be the empty string . If it was really NULL, you would see
NULL in the resultset. Try
SELECT * FROM product WHERE url=
--
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=nor...@khine.net
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
