MySQL

Note: Examples shown here were tested on a range of MySQL versions from v5.1 to v5.5 and MariaDB v10.0 with no regard to compatibility across versions.

TODO

Consider breaking content into subpages? Perhaps a "Create" page for databases, tables, users? A "Drop" page, etc?

Character length restrictions

• 64 characters for database name

• username
  • 16 characters <= 5.6
  • 32 characters >= 5.7

• 32 characters for password length
  • Specific to MySQL replication slave account
  • Resolved in MySQL 5.7 (Bug #11752299, Bug #43439)

• Field comments
  • 255 before before MySQL 5.5.3
  • 1024 after MySQL 5.5.3

• Table comments
  • 60 characters before MySQL 5.5.3
  • 2048 characters after MySQL 5.5.3

Security tips

• Rename root account
• Run mysql_secure_installation after a fresh installation

SELECT statement syntax

A quick reminder of the proper keyword order

SELECT          FIELD(S)
FROM            TABLES
JOIN            OTHER TABLES
WHERE           CRITERIA
GROUP BY        ANY GROUPS
HAVING          FILTERS FOR GROUPED VALUES
ORDER BY        SORT ORDER

Using comments in SQL files

These are all valid comment styles.

# Comment 1
-- Comment 2
/*
 
    Comment 3

*/

Create a database

CREATE DATABASE my_test_db CHARACTER SET utf8;

Create a table

Simple example

CREATE TABLE pet (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);

More complex example showing table and field comments

-- Holds per-user custom expiration settings for various IMAP mailboxes

CREATE TABLE `mailserver`.`mailbox_custom_expiration_settings` 
(
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL
    COMMENT "The virtual user id",
  `imap_mailbox_name` text NOT NULL 
    COMMENT "Examples: Newsletters, Trash, Spam, Facebook, Cuteoverload",
  `imap_search_key` varchar(20) NOT NULL DEFAULT 'SAVEDBEFORE'
    COMMENT "BEFORE or SAVEDBEFORE are supported choices",
  `date_specification_interval` VARCHAR(20) NOT NULL
    COMMENT "Should be set to a period shorter than the default to be effective",
  `last_modified` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `comments` text,
  PRIMARY KEY (`id`),
  -- Nuke the matching row from this table that corresponds to the virtual user id
  FOREIGN KEY (`user_id`) REFERENCES virtual_users(`id`) ON DELETE CASCADE 
) 
    ENGINE=InnoDB 
    DEFAULT CHARSET=utf8
    COMMENT="Controls (optional) per-user expiration times for mail content"
;

Display Tables or Views

• As of MySQL 5.0.1 the SHOW TABLES statement also displays view names.
• As of MySQL 5.0.2 the (optional) FULL keyword may be given to display for each table whether the name refers to a base table or a view.

SHOW FULL TABLES FROM my_test_db;

or

USE my_test_db;
SHOW FULL TABLES;

How to determine the size of MySQL databases and tables

Credit: These recipes are from a a2hosting.com KB article

List size of databases

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

List size of tables within a specific database

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

Create a View

Syntax:

CREATE [OR REPLACE]
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
[DEFINER = definer_name]
[SQL SECURITY = {DEFINER | INVOKER}]
VIEW view_name [(col_list)] AS select_stmt
[WITH [CASCADED | LOCAL] CHECK OPTION]

• When the view is invoked, the DEFINER and SQL SECURITY clauses determine the security context (the account to use for access checking). The default is to use the account for the user who executes the CREATE VIEW statement.

• The view definition is "frozen" at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view.

CREATE VIEW `mailserver`.`user_access` AS
  SELECT
    virtual_users.email,
    virtual_users.enabled,
    service_access.smtp_receive_active,
    service_access.smtp_send_active,
    service_access.pop3_active,
    service_access.chat_active,
    service_access.webmail_active,
    service_access.comments
  FROM
      service_access,
      virtual_users
  WHERE
      virtual_users.id = service_access.user_id
;

This view allows for querying the table like so to get all service access permissions for a user:

SELECT * from user_access;

Note: Actual queries from MySQL-enabled applications would select only particular fields that were necessary to process access checks.

Create a user for that database with full privileges to it

Depending on your needs, you'll setup the user in one of several ways.

Database server is on the same box as the application

Connecting via a UNIX socket

CREATE USER 'my_test_db_usr'@'localhost' IDENTIFIED BY 'INITIAL_PASSWORD';
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'localhost';

Connecting via a TCP socket

Useful if the application is running inside of a chroot jail for instance

CREATE USER 'my_test_db_usr'@'127.0.0.1' IDENTIFIED BY 'INITIAL_PASSWORD';
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'127.0.0.1';

Application is on a remote server

Remote host is specified via IP Address

CREATE USER 'my_test_db_usr'@'192.168.1.105' IDENTIFIED BY 'INITIAL_PASSWORD';
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'192.168.1.105';

Remote host is specified by hostname

Valid DNS A record or /etc/hosts entry

CREATE USER 'my_test_db_usr'@'webserver1.example.org' IDENTIFIED BY 'INITIAL_PASSWORD';
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'webserver1.example.org';

Connecting from a different host

Reset Hosts Value

If you created your MySQL user account and limited it to connections from a specific IP Address, at some point you'll need to create another account or update the host it is allowed to be used from (my choice) if the remote IP changes. One example would be moving the application server from one VLAN to another. In our example 192.168.2.25 is the new IP and the old IP was 192.168.1.105.

UPDATE mysql.user set Host = '192.168.2.25' WHERE User = 'my_test_db_usr' AND Host = '192.168.1.105';
UPDATE mysql.db set Host = '192.168.2.25' WHERE User = 'my_test_db_usr' AND Host = '192.168.1.105';
FLUSH PRIVILEGES;

Create replacement account

Another approach which phpMyAdmin uses is to just create a new account to replace the old. To do that, we'll need to replicate the settings of the existing account, only specifying a new host in place of the old. Once we're done, we'll delete the old account.

Get existing encrypted/hashed password

SELECT Host, User, Password from mysql.user WHERE User LIKE '%USER_ACCOUNT%' AND Host = 'OLD_HOST';

example results here

Create new account using old password

Here we'll use the literal encrypted/hashed password value taken directly from the mysql.user table to create the new account:

# Create the replacement user with appropriate IP
CREATE USER 'USER_ACCOUNT'@'NEW_HOST' IDENTIFIED BY PASSWORD '*NOTREALPASSWORD';

Get privileges for old account

We get the privileges for the old account so we can replicate them on the new account:

SHOW GRANTS FOR 'USER_ACCOUNT'@'OLD_HOST'

example results here

Give the new account the same privileges as the old

Grant those privileges to the new account:

GRANT USAGE ON *.* TO 'USER_ACCOUNT'@'NEW_HOST' IDENTIFIED BY PASSWORD '*NOTREALPASSWORD';
GRANT SELECT, LOCK TABLES ON `DATABASE_NAME`.* TO 'USER_ACCOUNT'@'NEW_HOST';

example results here

Retire old account

Finally, once we confirm that we no longer need access from the old host, we delete the old account:

DROP USER 'USER_ACCOUNT'@'OLD_HOST';

Reset password for user account

SET PASSWORD for 'my_test_db_usr'@'localhost' = PASSWORD('REAL_PASSWORD_HERE');

Viewing the account you're logged in as (equivalent of UNIX whoami)

SELECT USER(),CURRENT_USER();

• USER() reports how you attempted to authenticate in MySQL
• CURRENT_USER() reports how you were allowed to authenticate in MySQL

RolandoMySQLDBA notes: Sometimes, they are different

Import or run SQL statements from file

source my-sql-file.sql;

Rename table

RENAME TABLE `my_test_db`.`table_name` TO `my_test_db`.`my_new_table_name`;

Alter table

Insert new field

ALTER TABLE contacts ADD email VARCHAR(60);

Insert new field at specific location

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

Remove Unique Key/Index

ALTER TABLE `service_access` DROP INDEX `user_id_2`;

Inserting new rows

Note: For fields that have default values, you can skip including those field names and values in the INSERT INTO and VALUES statements if you wish to use the defaults.

A single row

INSERT INTO table_name (
  `field_name1`, 
  `field_name2`, 
  `field_name3`
)
VALUES (
  'Value1', 
  'Value2', 
  'Value3'
);

Multiple rows

INSERT INTO table_name (
  `field_name1`, 
  `field_name2`, 
  `field_name3`
)
VALUES 
  ('Value1', 'Value2', 'Value3'),
  ('Value1', 'Value2', 'Value3'),
  ('Value1', 'Value2', 'Value3'),
  ('Value1', 'Value2', 'Value3'),
  ('Value1', 'Value2', 'Value3'),
  ('Value1', 'Value2', 'Value3')
;

Pay careful attention to the lack of a trailing comma for the last row we are inserting. I often forget and include it and have to remove it after MySQL complains.

Update field

UPDATE table_name SET table_field = 'Value' WHERE id = '1';

Delete row

DELETE FROM virtual_domains WHERE id = '1' OR id = '7';

Delete user account

DROP USER 'my_test_db_usr'@'localhost';

Delete database

DROP DATABASE 'my_test_db';

Resetting the root user account

More Secure

primarily because the command history is not saved

1 nano /home/me/reset_root_password.sql
2 # type the following:
3 # UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
4 # Save & quit nano
5 sudo /etc/init.d/mysql stop
6 mysqld_safe --init-file=/home/me/FILE &
7 rm /home/me/FILE
8 sudo /etc/init.d/mysql restart

Less secure

command history IS saved

1 sudo /etc/init.d/mysql stop
2 sudo mysqld --skip-grant-tables --skip-networking &
3 mysql -u root mysql
4 UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;

Viewing privileges granted to a user

• select * from mysql.user;
  

  • Parse results by eye (not pretty)

• SHOW GRANTS FOR 'username'@'ipaddress';
  

• SHOW GRANTS;
  

  • For current user

Show the character set for a database

SHOW CREATE DATABASE database_name;

Show the database engine type

USE DATABASE database_name;
SHOW CREATE TABLE table_name;

Safely/gracefully restart MySQL/MariaDB

This section is nearly verbatim from Michael - sqlbot from stackexchange.com

Note: if you are shutting down the server for an upgrade, then don't use this process; instead, follow the process detailed in What should be done before upgrading MySQL?. Otherwise, if you're just restarting an otherwise-healthy server so that you can change a read-only global variable or something similar, here is a graceful path:

Enable innodb_fast_shutdown

Enable innodb_fast_shutdown if it isn't already. This isn't directly related to the gracefulness of the shutdown, but it should bring your server back faster.

SHOW VARIABLES LIKE 'innodb_fast_shutdown';

If it's set to 0 as shown here:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

then run this query to enable it:

mysql> SET GLOBAL innodb_fast_shutdown = 1;

Query OK, 0 rows affected (0.01 sec)

Close all open tables

Next, instruct the server to close all open tables as soon as no currently-running queries are referencing them. This step also has nothing to do with the graceful shutdown, but it will make the subsequent step go faster:

mysql> FLUSH LOCAL TABLES;

Query OK, 0 rows affected (41.12 sec)

The FLUSH TABLES statement (with the optional LOCAL keyword, which avoids an unnecessary but otherwise harmless flush of any slaves) will block and your prompt won't return until all of the tables can be closed. Once each table has been "flushed" (closed), if a query subsequently references the table, it will be automatically reopened, but that's okay. What we're accomplishing with this step is making less work for the final step.

Lock tables read-only

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (13.74 sec)

mysql>

This statement flushes all tables (hence the advantage of getting some of that out of the way less disruptively with the prior step) and acquires a global (server-wide) read-only lock on them.

You can't have a global read lock until every currently running "write" query (i.e., pretty much everything but SELECT) is done. Issuing the lock request will allow existing queries to finish but won't allow new ones to start.

Your prompt doesn't return until you hold this global lock, so every query that is in progress when you request the lock is able to finish, and you know they're finished, because you get the prompt back. Any subsequent queries that try to write anything to any table will just stall, changing no data, waiting indefinitely for the lock, until...

• you change your mind about the restart and release the lock manually (UNLOCK TABLES;)
• you restart the server, or
• you accidentally or intentionally disconnect the command line client from this thread (so don't do that). Keep this window connected and sitting at the mysql prompt:

Resist the temptation to close the mysql> prompt

This idle console prompt is what's holding the global lock for you. Lose this, lose the lock.

From another console window, restart MySQL the way you normally would, either with initscripts or with mysqladmin shutdown followed by a manual restart.

Restoring databases

Restoring views

When creating a view the security model is one of Definer or Invoker. The problem is that this some_user@localhost will always be hard-coded to the user account that was used to create the view in the original DB and NOT the user that you've used to export or import the database. And later, during the import, this user will be used to re-create the view.

So you can export/import as root, but if the original DB is running under another user and it has no CREATE VIEW rights in the new database, the import will fail. In short, you will need to search/replace within the SQL file you're importing or you will need to create the applicable user account prior to the import attempt.

MySQL config/option file search order

The MySQL configuration file (referred to as an option file) can be located in multiple places, but the order of precedence determines which configuration file will be used when you do not explicitly specify a file.

Unix, Linux and Mac OS X MySQL option file search order

File Name	Purpose
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options
defaults-extra-file	The file specified with --defaults-extra-file=path, if any
~/.my.cnf	User-specific options

As mentioned on the Using Option Files reference page running mysql with the --help option will show you the preconfigured locations that MySQL will search for the options file.

Example:

$ mysql --help --verbose  | grep -A 1 'Default options'

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Important

• On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.
• MySQL looks for option files in the order listed in the table reads any that exist. If multiple instances of a given option are found, the last instance takes precedence.
  • Exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.

Path variables

• ~ represents the current user's home directory (the value of $HOME).
• SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.
• MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:
  • Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively.
  • If there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR.
  • Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

In MySQL 5.5, use of DATADIR as the location for my.cnf is deprecated.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.