MySQL notes

Create a user

This creates a new root level user named USERNAME with no password.

 $ mysql -u root -e "GRANT ALL ON *.* to USERNAME@localhost;"
 $ mysql -u root -e "GRANT ALL ON *.* to USERNAME@'%';"

This creates a new user with a password.

 $ mysql -u root -e "GRANT ALL ON *.* to USERNAME@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;"
 $ mysql -u root -e "GRANT ALL ON *.* to USERNAME@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;"

Error mixing old MySQL client with newer MySQL server

If you are using an old MySQL client to connect to a later version of MySQL you may get an error:

 Client does not support authentication protocol requested by server; consider upgrading MySQL client

This happens when using a MySQL client prior to version 4.1 with a server later than version 4.1. The best thing to do is to upgrade the client. If that cannot be done then set the password on the server to use the old password hash:

 SET PASSWORD FOR 'USERNAME'@'some_host' = OLD_PASSWORD('some_pass');

lost root password

Stop mysql server.

 /etc/init.d/mysql stop

Edit /etc/my.cnf and add this line:

 skip-grant-tables

Start mysql server:

 /etc/init.d/mysql start

Now you can connect without a password. Grant commands won't work. You can reset the password by directly updating the User table:

 mysql> use mysql
 mysql> update mysql.user set Password=password('newpassword') where User='root';

Remove skip-grant-tables from /etc/my.cnf.

Restart mysql server:

 /etc/init.d/mysql restart

select into file

I always forget this:

SELECT * FROM foo INTO OUTFILE '/tmp/foo.sql';

select into CSV file

SELECT * FROM foo INTO OUTFILE '/tmp/foo.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

dump shared memory files

It is pretty easy to read directly from MySQL memmapped files. This can be handy when you need to hack a search through data files rather than doing a proper query. Sometimes it's faster to dump data this way.

#!/usr/bin/env python

import mmap
import os, sys, string

def hex_filter (b, offset, block_size = 8):
    """Any unprintable characters are converted to line feeds, but
    only one line feed is printed per cluster of unprintable characters.
    """
    unprintable_flag = True # used to track unprintable chunk state.
    for i in xrange (offset, offset+block_size):
        #if b[i] in string.printable:
        if ord(b[i])>=ord(' ') and ord(b[i])<=ord('~'):
            sys.stdout.write("%s"%b[i])
            unprintable_flag = False
        else:
            if not unprintable_flag:
                sys.stdout.write("\n")
            unprintable_flag = True

def main ():
    filename="/var/lib/mysql/var/test/rmscrub.MYI"

    file = open(filename, "rb")
    size = os.path.getsize(filename)
    data = mmap.mmap(file.fileno(), size, access=mmap.ACCESS_READ)

    print "Note that 'data' is a memmapped file and 'size' is taken from the memmapped file size,"
    print "so len(data) and size should be the same:"
    print "len(data):", len(data)
    print "size:     ", size

    block_size = 2048
    size = 1000000
    for i in xrange(0,size, block_size):
        hex_filter(data,i, block_size)

main()