​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."

Settings

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 ​MySQL.com 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

MYDB=my_database_name
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:

MYDB=mysql 
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 

JDBC with SSL

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://127.0.0.1:3306/sample"+
             "?verifyServerCertificate=false"+ 
             "&useSSL=true&requireSSL=true"; 

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 {
	ifempty
	daily
        rotate 14
        compress
        postrotate
	  # 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
        endscript
}

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:

[mysqld]
log-long-format
log=/var/log/mysql/query.log

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
        weekly
        rotate 40
        missingok
        compress
    postrotate
        # 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
        fi
    endscript
}

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:

[mysqld]
innodb_file_per_table=1