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