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
}
