MySQL notes 2

17 Nov 2005

MySQL notes 2

remote servers

  • comment out this line in /etc/mysql/my.cnf:
bind-address = 127.0.0.1
 /etc/init.d/mysql restart
  • connect to remove server
$mysql -h foo.bar.com -u root -p

useful options

  • q quit
  • G display in vertical format (when queries wrap on screen). Think G instead of g (go):
select * from userG;
  • s status command – which database am I using, etc
  • c cancel a partially entered query. May not work if prompt indicates waiting for a trailing ‘ or “ (’> or “>), in which case enter trailing character then c
  • # rehash table names for command line completion (if turned off in .my.cnf)
  • . source a file of sql commands
  • P turn on pager
  • n turn off pager
  • T turn on log (tee) file
  • t turn off log (tee) file)

option files

  • use option files (/etc/mysql/my.cnf or ~/.my.cnf) to save having to type in passwords, etc:
# options for all clients
[client]
host=localhost
user=root
password=secret

# program specific options
[mysql]
pager=/usr/bin/less
no-auto-rehash

[mysqladmin]

[mysqlshow]
  • set permissions:
$ chmod 600 .my.cnf

file usage

  • read queries from a file
$ mysql dbname < file.sql
  • use a here document in scripts
msyql dbname <<FIN
 SELECT * FROM tbl;
 FIN
  • read in a file of commands whilst in mysql
mysql> source file.sql, or
 mysql> . file.sql
  • pipe the results of a command into mysql
    • copy a database between machines:
msyql dump dbname | mysql -h foo.bar.com dbname
: * fill a db with test data:
generate-test-data | mysql dbname
  • specify an SQL query at the command line: -e
    • -B or –batch produces tab delimited output (default when non-interactive)
    • -t or –table produces tabular output (default when interactive)
    • -H or –html produces html output
    • -X or –xml produces xml output
    • -N or –skip-column-names: don’t produce column headers
mysql -e "SHOW TABLES;DESCRIBE TABLES" dbname

produce csv output

$ mysql db < in.sql | csv.pl > out.csv

Where csv.pl is:

#!/usr/bin/perl
while (<>) {
  s/"/""/g;
  s/t/","/g;
  s/^/"/g;
  s/$/"/g;
  print;
}
exit(0);

using a pager

$ mysql --pager=/usr/bin/less
 $ mysql --pager (use $PAGER)
  • also pager=/usr/bin/less in .my.cnf
  • also P (turn on) and n turn off

misc

  • keep a log of commands typed in
$ mysql --tee=out.txt dbname
 mysql> T out.txt
  • also, see ~/.mysql_history – which will contain passwords assigned to users :-(
comments powered by Disqus

  « Previous: Next: »