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.

Import Database from File

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

Optional Flags:

• -h <value> - Allows specification of host, such as for connecting to a remote server.
• -P <value> - Allows specification of port, such as for non-standard installations that don't use the default port 3306.