UW Technology provides support for MySQL "through a limited set of documentation for advanced-level, do-it-yourself use, as well as a set of binaries on the Web development systems that will be updated periodically."


To see what settings a running mysqld is using, connect and run SHOW VARIABLES; and for a short explanation use /usr/libexec/mysqld --verbose --help or the documentation.

There are also some tips on our PostgreSql page.

Adding a user

Log in as the root user with mysql mysql:

GRANT ALL PRIVILEGES ON mydb.* to foo@'localhost' identified by 'bar';
GRANT ALL PRIVILEGES ON mydb.* to foo@'%' identified by 'bar';

Basic dump and restore

mysqldump $MYDB > mydb.dump
# destroy the database contents without dropping it to retain permissions
mysql $MYDB -BNe "show tables" | awk '{print "set foreign_key_checks=0; DROP TABLE `" $1 "`;"}' | mysql $MYDB
mysql $MYDB < mydb.dump

Editing a dump file

Sometimes it is handy to edit a database by editing the text dump file. The skip-extended-insert dump format is easier to work with because it puts one row on each line. However, note that it is also much slower to load than the default dump format. Here is one way to do edit a dump:

mysqldump --skip-extended-insert $MYDB > $MYDB.dump
cp $MYDB.dump $MYDB-orig.dump
mv $MYDB.dump new.dump
# make your edits
nano new.dump
# destroy the database contents
mysql $MYDB -BNe "show tables" | awk '{print "set foreign_key_checks=0; DROP TABLE `" $1 "`;"}' | mysql $MYDB
mysql $MYDB < new.dump 


Set up server as described in Secure PHP Connections to MySQL with SSL.

Connecting to MySQL via an Encrypted Connection using SSL and JDBC:

String url = "jdbc:mysql://"+

Reformatting from SELECT statements

For example, to get an output "543-7315" from a text field of "5437315":

SELECT concat(substr(phonenum,1,3), '-', substring(phonenum,4,4)) from mytable;

Database backups from logrotate

This options have worked pretty well:

/usr/local/data/data13/Protege-backups/fma_p2.dump {
        rotate 14
	  # doesn't automatically read $HOME/.my.cnf!!!
	  mysqldump --defaults-extra-file=/root/.my.cnf --add-drop-table --add-locks --all --extended-insert --quick --lock-tables fma_p2 > d

Converting dump files to postgres

There are a lot of scripts online that take a stab at this difficult problem. A student edited mysql2pgsql to convert the bmap WIRM repo on sphenoid. It works reasonably well when used like this:

mysqldump --defaults-extra-file=/var/lib/pgsql/.my.cnf --skip-opt --create-options bmap_repo > bmap_repo.dump
./mysql2pgsql.perl.JDF bmap_repo.dump pg_bmap_dump.sql

Adding a query log

Add this to /etc/my.cnf:


Create a place for the query log:

mkdir /var/log/mysql/
touch /var/log/mysql/query.log
chmod 600 /var/log/mysql/query.log
chown mysql.mysql /var/log/mysql/query.log
/etc/init.d/mysqld restart

To rotate the logs, create this /etc/logrotate.d/mysql-query file: You need a /root/.my.cnf configuration file with a [mysqladmin] entry for the logrotate to work

/var/log/mysql/query.log {
        create 600 mysql mysql
        rotate 40
        # just if mysqld is really running
        if test -n "`ps acx|grep mysqld`"; then
                /usr/bin/mysqladmin --defaults-extra-file=/root/.my.cnf flush-logs

You may also be interested in the MySql Query Cache.

Separate InnoDB tables

The MyISAM engine keeps data in separate files for each database table, while by default the InnoDB engine uses a single ibdata1 file. You can get separate files with InnoDB like this:

Last modified 8 years ago Last modified on 10/13/11 16:50:21