MySQL

MySQL communicates through either local unix sockets or over TCP/IP port 3306 (default). Database names, tables, field names, and passwords are case sensitive. SQL Commands are not case sensitive.

The main configuration file is /etc/my.cnf. Usually doesn't need tweaking, except when using the InnoDB storage engine, or if you have high performance requirements.

The main command line utilities are mysql, mysqldump, and mysqladmin. Many people like the phpMyAdmin package to manage MySQL through a web browser.

MySQL is not part of the default install in OS X.

Server Administration

---

Show all running MySQL processes
mysqladmin --user=root --password=xxx processlist

Show detailed status report
mysqladmin --user=root --password=xxx extended-status

Reload grant tables (after making security table changes)
mysqladmin --user=root --password=xxx reload

Show running configuration settings
mysqladmin --user=root --password=xxx variables

Kill a slow or locked process
First, get the process id using processlist, then
mysqladmin --user=root --password=xxx kill id

Reset the value of an autoincrement field in a table

1. mysql --user=root database
2. alter table tablename autoincrement=100;

Note: the above resets the autoincrement field to 100. Use caution!

Alter a table so it can grow beyond 4 GB in size

Even if your OS supports file sizes greater than 4 GB, and even though MySQL supports tables larger than 4 GB, it will still give you a "table full" error message if you try to insert records into a table that has reached 4 GB unless you tell it to allow tables to grow larger. You can set a global option in my.cnf or you can tell MySQL on a table by table basis. This command will allow a table to grow to roughly 300 GB:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=300;


Security

---

Change/set the root password

1. mysql --user=root mysql (initially no password)
2. update user set Password=password('new_password') where user='root';
3. flush privileges;

Create a user with remote update authority

1. mysql --user=root --password=xxx mysql
2. insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv) values ('%', 'remote', password('xxx'), 'Y', 'Y', 'Y', 'Y');
3. flush privileges;

Create a user with access to just the db1 database

1. mysql --user=root --password=xxx mysql
2. insert into user (Host, User, Password)
values ('localhost', 'foo', password('xxx'));

3. insert into db (Host, Db, User, Select_priv, Insert_priv,
Update_priv, Delete_priv) values ('localhost', 'db1', 'foo',
'Y', 'Y', 'Y', 'Y');

4. flush privileges;

Backup and Restore

---

Dump all databases (schema and data)
mysqldump --user=root --password=xxx --all-databases > databases.sql

Dump a single database (schema and data)
mysqldump --user=root --password=xxx --databases db1 > db1.sql

Dump a single database (schema only)
mysqldump --all --no-data --user=root --password=xxx --databases db1 > db1.sql

Restore a database from a dump file
mysql --user=root --password=xxx <>

Load data from a text file

---

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege on the server host. You can also load datafiles by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE command to the server.

The defaults cause LOAD DATA INFILE to act as follows when reading input:

• Look for line boundaries at newlines.
• Do not skip over any line prefix.
• Break lines into fields at tabs.
• Do not expect fields to be enclosed within any quoting characters.
• Interpret occurrences of tab, newline, or .. preceded by .. as literal characters that are part of field values.

Example using the defaults (tab delimited):
LOAD DATA INFILE "data.txt" INTO TABLE table;

Example using an unquoted CSV file:
LOAD DATA INFILE "data.txt" INTO TABLE table FIELDS TERMINATED BY ',';

Tuning

---

Log slow queries

MySQL can log queries that take a long time to complete by adding this option to /etc/my.cnf in the [mysqld] section:
log-slow-queries=/var/log/mysqld.slowquery.log

The file should be created first with an owner of mysql:mysql. By default, a slow query is one that takes more than 10 seconds.