Mailinglist Archive: opensuse (1786 mails)

["David C. Rankin" <drankinatty@xxxxxxxxxxxxxxxxxx>]

Guys,

  Recently, managing the family's video clips, I began using BASH with mysql 
much more than I ever had in the past. (primarily because it was a LOT quicker 
than trying to use some database editing program) It is an amazingly simple and 
powerful interface that (1) gives you access to all of your data contained in 
databases from a scripting standpoint, but also (2) shows you just how much you 
can do with data stored in database format as opposed to flat files.

  The key to using mysql from the CLI are the -N -B and -e options.

18:18 nirvana:/home/samba/parents> mysql --help | grep -- "-N\|-B\|-e"
  -B, --batch         Don't use history file. Disable interactive behavior.
  -e, --execute=name  Execute command and quit. (Disables --force and history
                      Will automatically be re-executed when reconnecting.
  -N, --skip-column-names

  Practically speaking '-B' says don't print the ASCII table art; '-N' says 
don't return the column headings and '-e' says execute the stuff that follows 
(i.e.):

mysql -NBe "select fname,begin,dld2dvd,dvd,dld_date from mmedia.dvcapt \
where (begin <= '1999-07-24 16:18:33' && dld2dvd = '0')"

  Filling arrays with mysql information is really handy. Say I needed to do 
something with each filename in the database, I could:

declare -a fnarray
fnarray=( $(mysql -NBe "select fname from mmedia.dvcapt") )

  Managing the data in your mysql tables is also easy from BASH until you have 
your final tools developed. For example, in my case, I had roughly 1200 video 
clips totaling roughly 800G in size that I needed to insure I had backup copies 
of. So I spread the files between 3-4 boxes with an extra 250G of space on each. 
After finishing creating the backup copies, I needed to update the database with 
the location of the backup files so I could keep track of them and rsync them 
back if needed.

  So on each remote host, I simply created a directory list of the files with 
'ls -1 > list.txt' and then updated the database with the file locations by doing:

for i in $(<list.txt); do mysql -e "update mmedia.dvcapt set \
bu_loc='dcrgx2.3111skyline.com:/home/dv/sony' where fname='$i'"; done

  Beats the heck of copy and past of the location in some database editor, etc..

  This all become seamless if you setup a user that can access the needed 
tables and then create a ~/.my.cnf file that provides mysql access without being 
prompted for a password. An example ~/.my.cnf file would be:

[mysqladmin]
password = thepass
user = theuser

[mysql]
password = thepass
user = theuser

[mysqldump]
password = thepass
user = theuser

[mysqlcheck]
password = thepass
user = theuser

  The running as theuser (you) all you need to at the command prompt is type 
'mysql' and you are done. No -u -p options needed. The use of BASH and mysql is 
only limited by your imagination. It is truly a two-way street. In addition to 
making use of the information contained in the database, you can just as easily 
manage the database itself -- all from the CLI or in your favorite script.

  Give it a go. Your tips are welcome as well. Thanks.

--
David C. Rankin, J.D.,P.E.
--
To unsubscribe, e-mail: opensuse+unsubscribe@xxxxxxxxxxxx
To contact the owner, e-mail: opensuse+owner@xxxxxxxxxxxx