MySQL Quick Reference

This page is where I am going to be compiling all of the MySQL related material that I find myself looking up time and time again. So here it is all in one spot.

From myself (or other misc. sources)

If you want to change your password on borg, you can despite what the help desk manual says - simply log into mysql and type:

set password = password("yournewpassword");

You can spare yourself from always typing in your password when logging into mysql by creating a .my.cnf file in your home directory. In it put:

[client]
password=yourpassword
user=yourusername
database=yourdatebasename

Make sure that you keep this file chmod 600. Also potentially useful for people who do larger queries: put the line pager=less in that file as well and when you run a query it is as though the result set was piped into less (you can use more if you are that type of person). Very nice. :)

From the MySQL Documentation

DATE_FORMAT(date,format)

Formats the date value according to the format string. The following specifiers may be used in the format string:

Specifier	Description
%M	Month name (January..December)
%W	Weekday name (Sunday..Saturday)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.)
%Y	Year, numeric, 4 digits
%y	Year, numeric, 2 digits
%X	Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with ‘%V’
%x	Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with ‘%v’
%a	Abbreviated weekday name (Sun..Sat)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%m	Month, numeric (00..12)
%c	Month, numeric (0..12)
%b	Abbreviated month name (Jan..Dec)
%j	Day of year (001..366)
%H	Hour (00..23)
%k	Hour (0..23)
%h	Hour (01..12)
%I	Hour (01..12)
%l	Hour (1..12)
%i	Minutes, numeric (00..59)
%r	Time, 12-hour (hh:mm:ss [AP]M)
%T	Time, 24-hour (hh:mm:ss)
%S	Seconds (00..59)
%s	Seconds (00..59)
%p	AM or PM
%w	Day of the week (0=Sunday..6=Saturday)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week. Used with ‘%X’
%v	Week (01..53), where Monday is the first day of the week. Used with ‘%x’
%%	A literal %.

All other characters are just copied to the result without interpretation

Restoring MySQL root password

Well crap… you lost your password. And your MySQL root password no less. That happens when you don’t use it very often or you have some weird web hosting tool that installed mysql for you and didn’t bother to mention the root password (but it did set one thank <?=$deity?>). So this is how to make it all better. This of course assumes some familiarity with Unix (and that you are dealing with a unix system) and that you have root access to the system:

1. Kill the mysqld process with a nice kill <mysql pid> (you can lookup the mysqld pid using ps).
2. Restart mysqld with the --skip-grant-tables option.
3. Log into mysql as so: mysql -u root.
4. Type: flush privileges;
5. Type: grant all on *.* to root@localhost identified by '_your_new_pass_';.
6. Exit mysql and restart the server in the way that you would normally (or reboot).