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=gcdmg-mysql-2@m.gmane.org

RE: Variable Expansion Using MySQL Client and .sql Files

You could try it inside a "here" document:

mysql < GRANT SELECT=2C INSERT=2C UPDATE=2C DELETE=2C CREATE=2C DROP=2C ALTER ON co=
ncrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'=3B
EOF

----------------------------------------
> Date: Thu=2C 29 Jul 2010 14:29:55 -0500
> From: todd_dsm@ssiresults.com
> To: mysql@lists.mysql.com
> Subject: Variable Expansion Using MySQL Client and .sql Files
>
> Hey all=2C
>
> 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=2C for example needs:
> GRANT SELECT=2C INSERT=2C UPDATE=2C DELETE=2C CREATE=2C DROP=2C ALTER ON
> concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'=3B
> ---
>
> The secure_mysqld.sql script is fairly simple as well:
>
> # Display the current user:
> select user()=3B
>
> # Display all default accounts:
> SELECT User=2CHost=2Cpassword FROM mysql.user=3B
>
> # Remove anonymous accounts:
> DELETE FROM mysql.user WHERE user =3D ''=3B
>
> # Display all remaining accounts:
> SELECT User=2CHost=2Cpassword FROM mysql.user=3B
>
> # Sync root passowrds:
> UPDATE mysql.user SET Password =3D PASSWORD('$PASSWD_PRIV_ROOT') WHERE
> User =3D 'root'=3B
>
> 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=2C
>
> 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=3Dtravis_ard@hotmail.c=
om
>
=

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Variable Expansion Using MySQL Client and .sql Files

Simple - brilliant. I've modified to cat a temporary script...

1) cat the file as suggested:
cat > ~/payload/scripts/create_mysqldb.sql << EOF
###---
### Display the current user
###---
create database puppet;


###---
### Display all default accounts
###---
USE puppet;


###---
### Remove anonymous accounts
###---
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON puppet.* TO
'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
(variables are expanded)

###---
### Verify new admin account was added
###---
SELECT
user,Host,Select_priv,Insert_priv,Update_priv,Delete_priv,Cr eate_priv,Drop_priv,Alter_priv
from mysql.db;
EOF

2) Execute the script

3) Remove the script
rm -f ~/payload/scripts/create_mysqldb.sql

It is often the simplest things that are most beautiful. Thanks for
pulling my head out :D


TT







On 07/29/2010 03:19 PM, Travis Ard wrote:
> mysql< > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
> EOF
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org