Skip to main content

MySQL

Resources​

Tuning​

Misc​

Naming convention​

  • Use lower case names for databases and tables
  • Use _ to separate words in database and table names

To log into MySQL​

mysql -h host -u username -p

(You can leave -h host out if the MySQL server is on the local host)

Show global variables​

(Shows important info about the mysql server)

mysql> show variables;

Show active connections​

Logged in as root (or a user with process privileges):

mysql> show processlist;

To filter out inactive connections:

mysql> \P grep -v Sleep
mysql> show processlist;

Or (from the command line):

$ mysqladmin status -u username -p

Users and privileges​

Show users​

mysql> select user,host from mysql.user [where user...];

Show privileges for a user​

show grants for username@'192.168.0.%';

Create a user with full privileges on all databases​

mysql> grant all on *.* to username@'192.168.0.%' identified by 'password';

Or:

mysql> grant all on *.* to username@'localhost' identified by 'password';

Create a user with privileges to create other users​

Append with grant option; to any user creation statement.

Ex:

mysql> grant all on *.* to username@'192.168.0.%' identified by 'password' with grant option;

Create a user with full privileges on a particular database​

mysql> grant all on database.* to username@'192.168.0.%' identified by 'password';

Change a user's password​

mysql> update mysql.user set password=password('password') where user='username' [and host='192.168.0.%'];
mysql> flush privileges;

Delete a user​

mysql> drop user username@'192.168.0.%';
mysql> flush privileges;

Databases​

Use a database​

mysql> use database_name;

Show information about a database​

mysql> use database_name;
mysql> status;

Export a database​

mysqldump [-h host] -u username -p [options] database_name [tables]> database_name-`date +%Y%m%d`.sql

Common options:

  • --databases
    • Treats arguments following the database name as databases instead of tables, dumping the entire database (all tables)
  • --opt
    • Adds a bunch of default options that make the dump easier to import
  • --quote-names
    • Put database, table, and column names in single quotes
  • --routines
    • Include stored routines (functions and procedures) in the database dump
  • --single-transaction
    • Dumps the database at a consistent state if it's using a transactional engine (like InnoDB)

Import a database​

mysql [-h host] -u username -p [options] database_name < database_export.sql

Database storage engines​

InnoDB or MyISAM

Character set and collation​

  • In general, use utf8 character set and utf8_general_ci collation (the default) for best performance
  • If you need sorting to be extremely accurate for non-English characters, use utf8_unicode_ci collation
  • Source: http://stackoverflow.com/q/766809/399105

Set database character set at creation time​

create database test character set utf8;

Set database character set and collation at creation time​

create database test character set utf8 collate utf8_bin;

Overriding default DB character set when creating a table​

create table ... default charset=utf8;

Configuration​

Option files​

  • /etc/my.cnf
    • This is the global option file and applies system-wide
  • ~/.my.cnf
    • This is the user option file and applies only to the currently logged-in user
    • Useful information for this file would be the mysql username and password to be used