> For the complete documentation index, see [llms.txt](https://refdocs.buymeasoda.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://refdocs.buymeasoda.com/general/mysql.md).

# MySQL

Reference for working with [MySQL](http://www.mysql.com/).

For database, table and column names that are reserved words or irregular names, surround the names with backticks `` ` ``. For string values, wrap the values with single `'` or double `"` quotes.

If you are operating on a local MySQL database, `<host>` in the examples below will be `localhost`.

## Install MySQL Server

Linux

```
sudo apt-get install mysql-server
```

macOS

```
brew install mysql
```

Secure MySQL installation

```
mysql_secure_installation
```

macOS post install instructions via homebrew

```
unset TMPDIR
mysql_install_db
mysql.server start
mysql_secure_installation
```

## MySQL Information

Show installed MySQL version

```
mysql -V
```

## Control MySQL Server

Linux

```
service mysql start | stop | restart | status
```

macOS

```
mysql.server start | stop | restart | status
mysql.server reload | force-reload
```

Run MySQL as a background service (Homebrew / macOS)

```
brew services start mysql@<version>
```

macOS homebrew executable location

```
/usr/local/Cellar/mysql/<version>/support-files
```

## Log In/Out

Log into MySQL as `root`

```
mysql -u root -p
```

Log into MySQL as `<user>`

```
mysql -u <user> -p
```

Exit MySQL

```
mysql> exit
```

## Database Files

Linux

```
/var/lib/mysql
```

macOS

```
/usr/local/var/mysql
```

Report total size of MySQL databases

```
sudo du -skh <mysql-directory>
```

## Manage Users

Create a user

```
CREATE USER '<user>'@'<host>' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;
```

Delete a user

```
DROP USER '<user>'@'<host>';
```

Change a user's password (as root)

```
SET PASSWORD FOR '<user>'@'<host>' = PASSWORD('<password>');
```

Show list of users

```
SELECT User FROM mysql.user;
```

## Manage Privileges

Assign database privileges

Privileges:

* SELECT - Read only
* INSERT - Insert rows/data
* UPDATE - Change inserted rows/data
* DELETE - Delete drop rows of data
* CREATE - Create new tables
* ALTER - Change table/column names
* DROP - Drop columns/tables

Grant all privileges

```
GRANT ALL PRIVILEGES ON <database>.* TO '<user>'@'<host>';
FLUSH PRIVILEGES;
```

Grant general privileges

```
GRANT SELECT, INSERT, UPDATE, DELETE ON <database>.* TO '<user>'@'<host>';
FLUSH PRIVILEGES;
```

Grant table privileges

```
GRANT CREATE, DROP, ALTER ON <database>.* TO '<user>'@'<host>';
```

Revoke all privileges

```
REVOKE ALL ON <database>.* FROM '<user>'@'<host>';
```

Revoke specific privileges

```
REVOKE DELETE, INSERT ON <database>.* FROM '<user>'@'<host>';
```

Show privileges

```
SHOW GRANTS FOR '<user>'@'<host>';
```

## Navigate Databases and Tables

Show list of available databases

```
SHOW DATABASES;
```

Select database to use for subsequent SQL commands

```
USE <database>;
```

Show tables for selected database

```
SHOW TABLES;
```

Show the column names and column types for a table

```
DESCRIBE <table>;
```

Show all rows that exist in a column

```
SELECT * FROM <column>;
```

## Create and Delete databases

Create a new database

```
CREATE DATABASE <database>;
```

Delete an existing database

```
DROP DATABASE <database>;
```

## Create, Delete and Alter Tables

Create a table and columns

```
CREATE TABLE <table> (<column> <type> <settings>);
```

Create a table and columns (example)

```
CREATE TABLE <table> (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, <column> VARCHAR(45));
```

Rename table

```
RENAME TABLE <table> to <new table>;
```

Delete table

```
DROP TABLE <table>;
```

Add table column

```
ALTER TABLE <table> ADD <column> <type> <settings>;
```

Add table column in location (example)

```
ALTER TABLE <table> ADD <new column> VARCHAR(45) NULL AFTER `<column>`;
```

Insert record

```
INSERT INTO <table> (<column>, <other column>) VALUES ('<value>', '<other value>');
```

## Select, Update and Delete Rows

Select records

```
SELECT * FROM <table>;
```

Update record

```
UPDATE <table> SET <column> = '<value>';
```

Update record with condition

```
UPDATE <table> SET <column> = '<value>' WHERE <table>.<other column> = <condition>;
```

Delete all rows

```
DELETE FROM <table>;
```

Delete specific record

```
DELETE FROM <table> WHERE <table>.<column> = '<value>';
```

Delete all non-matching records

```
DELETE FROM <table> WHERE <table>.<column> != '<value>';
```

## Using Wildcards

Wildcards via `%` can be used for grants on database names (literal underscores must be escaped `\_`)

Grant select for databases that match `foo_*` (eg. `foo_bar`, `foo_qux`)

```
GRANT SELECT ON `foo\_%`.* TO '<user>'@'<host>';
```

Revoke all for `foo_*`

```
REVOKE ALL ON `foo\_%`.* TO '<user>'@'<host>';
```

Note: Grants and revokes made in this manner must match the same wildcard value (eg. `foo\_%`)

## MySQL Database Import / Export

Export database

```
mysqldump -u root -p <database> > <file.sql>
```

Export database (and include `CREATE DATABASE` and `USE` commands)

```
mysqldump -u root -p --databases <database> > <file.sql>
```

Export multiple databases

```
mysqldump -u root -p --databases <database1> <database2> > <file.sql>
```

Export all databases

```
mysqldump -u root -p --all-databases > <file.sql>
```

Import database

```
mysqladmin -u root -p create <database>
mysql -u root -p <database> < <file.sql>
```

Executing .sql files

```
mysql> source <file.sql>
```

Alternative way to execute .sql files

```
mysql> \. <file.sql>
```

Output MySQL settings

```
mysql> \s
```

## Rename Database

```
mysqldump -u root -p -v <database> > <file.sql>
mysqladmin -u root -p create <new database>
mysql -u root -p <new database> < <file.sql>
```

## Check Database Configuration

Show current character set configuration

```
SHOW VARIABLES LIKE 'char%';
```

Show database variables

```
mysqladmin -u <user> -p variables
```