$Id: mysql.txt 1896 2008-09-24 23:35:41Z mjs $

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

Find out what privileges they've got:

mysql> show grants for 'mjs'@'localhost';
Revoke the user's privileges:

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

Drop the user either with

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

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

$ 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

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

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".
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.)