Programming/MySQL/Databases

Note: Unless otherwise specified, all commands from this section assumes you're in the MySQL shell.

MySQL splits things up into individual "databases". Generally speaking, each database is a cohesive, separate entity.

General Commands

Show Databases

SHOW DATABASES;

Create a New Database

CREATE DATABASE <database_name>;

Specifying Character Set

Note: The following command should only be used if the database has a different character set from the server's default set. To set the server's character set, see MySQL Setup.

To both create and specify the database character set:

CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

Note that utf8mb4 is the preferred character set for most situations. See this link for details.

Load a Database

USE <database_name>;

Note that only one database can be selected/loaded at a time.

Delete a Database

Warn: Note that this action cannot be undone.

DROP DATABASE <database_name>;

Set Database Privileges

See MySQL User Permissions.

Database Backups

Export Database to File

mysqldump -u <username> -p <database_name> > <file_location>

Optional Flags:

• -h <host> - Allows specification of host, such as for connecting to a remote server.
• -P <port> - Allows specification of port, such as for non-standard installations that don't use the default port 3306.
• --password=<value> - Replaces the above -p flag. Allows specification of the user password in the command itself. Note that this means typing the password in plaintext, and thus is not recommended except for instances such as using the command within a script.

Import Database from File

mysql -u <username> -p <database_name> < <file_location>

Optional Flags:

• -h <host> - Allows specification of host, such as for connecting to a remote server.
• -P <port> - Allows specification of port, such as for non-standard installations that don't use the default port 3306.
• --password=<value> - Replaces the above -p flag. Allows specification of the user password in the command itself. Note that this means typing the password in plaintext, and thus is not recommended except for instances such as using the command within a script.
• --column-statistics=0 - Required if mysqldump gives an error about "column statistics". May or may not show up, depending on database setup.