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 :-(