MySQL

HOWTO: Compile

OS X (client only):

$ CPPFLAGS="-I/Ports/include" LDFLAGS="-L/Ports/lib" \
  ./configure --prefix=$LOCAL --without-server

(For PHP at least, can't use separate --exec-prefix and --prefix because the PHP script only accepts a single location for the MySQL libraries.)

NOTE: You may need to copy include/mysqld_error.h into the include/mysql directory manually; the client-only build doesn't install it, but various other programs seem to require it, such as PDO MySQL. (Not just a PHP thing; there are reports of other programs requiring it as well.)

TIP: About usernames and passwords

In MySQL, permissions are granted on the basis of a username/hostname pair--root@localhost is treated completely different to root@192.168.1.213.

HOWTO: Set/reset root's password

$ mysqladmin -uroot password <password>

HOWTO: Add a user

mysql> grant select, insert on `test\_cms`.* to 'mjs'@'localhost';
mysql> grant all privileges on `test\_cms`.* to 'mjs'; -- all hosts

NOTE: the quotes around the database name are backticks, not apostrophes!

HOWTO: Set a user's password

mysql> set password for 'mjs'@'localhost' = password('foo');

HOWTO: Check what privileges a user has

mysql> show grants for 'mjs'@'localhost'; 

HOWTO: Remove a user

1. Find out what privileges they've got:

   mysql> show grants for 'mjs'@'localhost';

2. Revoke the user's privileges:

   mysql> revoke select, insert, update, delete on test_cms.* from 'mjs'@'localhost';

3. Drop the user either with

   mysql> drop user 'mjs'@'localhost'; -- MySQL 4.1.1+
   

   or

   mysql> delete from mysql.user where User='mjs' and Host='localhost';
   mysql> flush privileges;
   

FAQ: What privileges are available?

See "GRANT and REVOKE Syntax".

HOWTO: Start the server on Windows

$ mysqld --console

See Starting the Server for the First Time.

HOWTO: Shutdown the server

$ mysqladmin shutdown

HOWTO: Remove MySQL Service (Windows)

$ mysqld --remove

HOWTO: Install MySQL Service (Windows)

$ mysqld --install

or

$ mysqld --install-manual # manual start

See Starting MySQL as a Windows Service.

TIP: Useful command-line options

-B/--batch batch output (no box-drawing characters)
-t/--table with box-drawing characters

-N         no column names
-e sql     execute sql command

See also The MySQL Command-Line Tool.

HOWTO: Dump a database/table schema

$ mysqldump -h $HOST -u $USERNAME -p$PASSWORD -d $DATABASE [$TABLE]

-d

prevents the data being dumped (it doesn't specify the database)

--add-drop-table

adds SQL drop statements

e.g.

$ mysqldump -h parmesan -u dowcarter -pfrug4l -d carlinglive user_details

HOWTO: Convert to/from Unix time

mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300

HOWTO: Output CSV

Doesn't seem to be a good way to do this. The MySQL manual says you can do SELECT ... INTO OUTFILE but this writes to a file on the server.

The other way is to provide mysql with the --batch and --raw command-line options, which writes a line for each row, with fields separated by tabs. (So, if you pipe to tr "\t" "," you get comma separated lines.)

HOWTO: Convert to/from and IP addresses (dotted-quad)

Use INET_ATON()/INET_NTOA().

HOWTO: Number the rows in a result set

SELECT *, @num := @num + 1 n FROM tbl a, (SELECT @num := 0) dummy;

HOWTO: Search and replace

mysql> update wp_posts set post_content = replace(post_content, 'http://webwatch.testdc.com/', '/')

HELP: My mysqldump/import is all screwed up! (Funny characters are appearing.)

This is probably an encoding problem. If you're using mysqldump to generate the SQL, and mysql to read it back in again, you need to ensure that both the writing bit and the reading bit are using the same character set. (They may be different if different versions of MySQL are used, but they seem to differ gratuitously even so.)

The best way to ensure that the same encoding is being used in both places is to specify --default-character-set=utf8 in both the export and the import. (Note: this seems to be somewhat unrelated to the character set of the database and tables themselves.)

HOWTO: Import CSV/Excel data

An example:

LOAD DATA LOCAL INFILE "C:\\balvenie.csv"
INTO TABLE balvenie
CHARACTER SET latin1
FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(EventName,FirstName,LastName,Address,@dummy,City,State,ZipCode,PhoneNo,Email)
SET Source = 'CSV', name_fr = name_en;

Note that if there's a column in the table that doesn't exist in the CSV (e.g. an "id" primary key) you must explicitly list all columns in the CSV. (Without the LOCAL, the server looks for the file on the server itself. Use LOCAL if possible, even if the server is the local machine--it gives you better error messages.)

The @dummy ignores a column in the input.

The SET Source ... can be used to assign a default value, or even evaluate expressions.

HOWTO: Produce a MySQL-formatted date string with PHP

date("Y-m-d H:i:s")

HOWTO: Get ssh tunnelling working

1. Check that you really can connect to the MySQL server from the machine you're going to ssh into via TCP/IP with something like

   $ mysql -h 127.0.0.1
   

   DON'T do mysql -h localhost--MySQL will try to use the faster socket connections if you try to connect to a hostname of localhost. It's a good idea to never use localhost as a hostname when setting up tunnelling--even non MySQL-branded products seem to employ this "optimisation".

2. Set up the tunnel with something like:

   $ ssh imsadmin@example.com -L 3307:17.72.133.31:3306
   

   This creates a connection from port 3307 on your local machine to port 3306 on 17.72.133.31--the local machine to example.com is via ssh connection, and example.com to 17.72.133.31 is unsecured. (You can't do tunnelling without setting up a secure shell to example.com.)

HOWTO: Backup/Restore a database with mysqldump

# backup
$ mysqldump -uusername -ppassword --default-character-set=utf8 example_database > example_database.sql

# restore
$ mysql -umjs -pheffalump --default-character-set=utf8 example_database < example_database.sql

The dumped SQL file does not contain the database name (when using mysqldump), so you can restore to a different database if necessary.

The MySQL Administrator GUI can also be used to backup and restore. (The Windows version has more features; the OS X can only restore to the same database.)

FAQ: How do I insert a NULL value in MySQL Query Browser?

In edit mode, use the right-click context menu, and select "Clear Field".

FAQ: Where can I download the the source for the MySQL client only?

This doesn't seem to be possible, though the configure script that comes with the source does have a --without-server option. (Also, note that some Unix package system have client-only packages available, so if you're lucky you may be able to use that.)

FAQ: How do I turn on the query log?

Either:

1. Add a log argument to the [mysqld] section of /etc/my.cnf:

   [mysqld] log = /tmp/mysql.log

2. Start MySQL with the -l=XXXX command-line argument.

HOWTO: Sequel Pro: connect to a database over a ssh tunnel

If this isn't working correctly, you probably need to enter the same host in both the "Host" field and the "SSH Host" field. (Configure via preferences.) That is, if you're setting up the tunnel with something like:

ssh root@example.com -L 3306:localhost:3306

Then this translates into a "Host" and "SSH Host" of example.com. Enter 3306 as the "Local Port". I don't know where the equivalent of localhost should go--perhaps there's no way to tunnel to one host, and then connect to a database running on another host.

HOWTO: Show table create SQL

mysql> SHOW CREATE TABLE foo;

(You can also use mysqldump --no-data.)

HOWTO: Enable remote access to MySQL

This is useful if, for example, you have MySQL running of a virtual machine, and you want to connect to it using a GUI tool running on the host.

You need to make two changes:

1. In /etc/my.cnf, ensure that the line bind-address = 127.0.0.1 is commented out, or absent.
2. Ensure the appropriate user (root?) has remote access to the database: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

If you still have problems, check that MySQL is accessible on port 3306 from both the VM itself, and the host.

(Note that it's also possible to use an ssh tunnel--see above.)