Difference between revisions of "MySQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
m (Added reference for removing UNIQUE constraint)
m (Added security tips references)
Line 21: Line 21:
** 2048 characters after MySQL 5.5.3  
** 2048 characters after MySQL 5.5.3  
== Security tips ==
<ref name="mysql-security-tips-1" /><ref name="mysql-security-tips-2" />
* Rename root account
* Run <code>mysql_secure_installation</code> after a fresh installation
== Using comments in SQL files ==
== Using comments in SQL files ==
Line 524: Line 530:
<ref name="mysql-unique-constraint">[http://www.w3schools.com/sql/sql_unique.asp SQL UNIQUE Constraint]</ref>
<ref name="mysql-unique-constraint">[http://www.w3schools.com/sql/sql_unique.asp SQL UNIQUE Constraint]</ref>
<ref name="mysql-security-tips-1">[https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps How To Secure MySQL and MariaDB Databases in a Linux VPS]</ref>
<ref name="mysql-security-tips-2">[https://mariadb.com/kb/en/mariadb/mysql_secure_installation/ MariaDB Knowledge Base > mysql_secure_installation]</ref>

Revision as of 23:25, 4 February 2015

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

Character length restrictions

[1] [2]

  • 64 characters for database name
  • 16 characters for user name
  •  ? characters for password length
    • seems to be dependent on the version
  • 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

Using comments in SQL files


These are all valid comment styles.

# Comment 1
-- Comment 2
    Comment 3


Create a database



Create a table


Simple example

    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",
  `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 
    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.



USE my_test_db;

Create a View

[8] Syntax:

[DEFINER = definer_name]
VIEW view_name [(col_list)] AS select_stmt
  • [9] 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
      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

GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'';

Application is on a remote server

Remote host is specified via IP Address

GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'';

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';

Update user account to allow connecting from a different host


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 is the new IP and the old IP was

UPDATE user set Host = '' WHERE User = 'my_test_db_usr' AND Host = '';

It's important to note however that you'll need to reapply database permissions for the affected user account however. What we've done so far has only adjusted the login or USAGE permission.

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)


  • 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

[12] [13]

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;

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 (

Multiple rows

INSERT INTO table_name (
  ('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

[16] [17]

More Secure

1 nano /home/me/FILE
2 # Add SQL statement from section below
3 # Save & quit nano
4 sudo /etc/init.d/mysql stop
5 mysqld_safe --init-file=/home/me/FILE &
6 rm /home/me/FILE
7 sudo /etc/init.d/mysql restart

Less secure

1 sudo /etc/init.d/mysql stop
2 sudo mysqld --skip-grant-tables &
3 mysql -u root mysql

Viewing privileges granted to a user

[18] [19]

  • select * from mysql.user;
    • Parse results by eye (not pretty)
  • SHOW GRANTS FOR username@ipaddress;
    • For current user

Show the character set for a database



Show the database engine type


USE DATABASE database_name;

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 [23] reference page running mysql with the --help option will show you the preconfigured locations that MySQL will search for the options file.


$ 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


  • 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.


  1. 6.3.1 User Names and Passwords
  2. 9.2 Schema Object Names
  3. How To Secure MySQL and MariaDB Databases in a Linux VPS
  4. MariaDB Knowledge Base > mysql_secure_installation
  5. Are there multiline comment delimiters in SQL that are vendor agnostic?
  6. MySQL 5.5 Reference Manual :: 3 Tutorial :: 3.3 Creating and Using a Database :: 3.3.1 Creating and Selecting a Database
  7. [MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax]]
  8. MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.20 CREATE VIEW Syntax
  9. [www.informit.com/title/9780132800754 MySQL, 4th Edition by Paul Dubois]
  10. MySql: Give Root User Logon Permission From Any Host
  11. how do I see which user I am logged in as in MySQL?
  12. MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.7 ALTER TABLE Syntax
  13. Tech-Recipes - Add a column to an existing MySQL table
  14. SQL UNIQUE Constraint
  15. 15.0 15.1 WikiBooks - MySQL CheatSheet
  16. Set / Change / Reset the MySQL root password on Ubuntu Linux
  17. MySQL :: MySQL 5.0 Reference Manual :: C.5.4.1 How to Reset the Root Password
  18. How can I show user's privileges in MySQL?
  19. How to show user privileges on mysql
  20. How do I see what character set a database / table / column is in MySQL?
  21. How can I check MySQL engine type for a specific table?
  22. Can MySQL reliably restore backups that contain views or not?
  23. MySQL 5.5 Reference Manual :: 4 MySQL Programs :: 4.2 Using MySQL Programs :: 4.2.6 Using Option Files