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';

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. Here is one way to do it:

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
mysqldump --add-drop-table --no-data mydb |grep DROP | mysql mydb
mysql mydb < new.dump 

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

touch /var/log/mysql/query.log
chmod 600 /var/log/mysql/query.log
chown mysql.mysql /var/log/mysql/query.log
/etc/init.d/mysql 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
}