Programming/MySQL/Databases: Difference between revisions

From Dev Wiki
Jump to navigation Jump to search
(Import from original mysql page)
 
(Add column-statistics note)
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Databases ==
{{ note | Unless otherwise specified, all commands from this section assumes you're in the [[MySQL#Basics | MySQL shell]]. }}
MySQL splits things up into individual "databases". Generally speaking, each database is a cohesive, separate project.


=== Creating a New Database ===
MySQL splits things up into individual "databases". Generally speaking, each database is a cohesive, separate entity.
From the MySQL Shell:
 
{{ bc | CREATE DATABASE <database_name>; }}
== General Commands ==
* Where {{ ic |<database_name>}} is replaced by the desired name for the database. Must be unique from all other existing databases in the MySQL server.
=== 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 [[Programming/MySQL/Setup | MySQL Setup]]. }}
 
To both create and specify the database character set:
CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;


To specify the database character set:
{{ bc | CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; }}
Note that <code>utf8mb4</code> is the preferred character set for most situations. See [https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 this link] for details.
Note that <code>utf8mb4</code> is the preferred character set for most situations. See [https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 this link] for details.


=== Loading a Database ===
=== Load a Database ===
From the MySQL Shell:
USE <database_name>;
{{ bc | USE <database_name>; }}
 
* Where {{ ic |<database_name>}} is replaced by the name of database you wish to select/load.
Note that only one database can be selected/loaded at a time.
* 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 [[Programming/MySQL/Setup#User Permissions | MySQL User Permissions]].
 
== Database Backups ==
=== Export Database to File ===
mysqldump -u <username> -p <database_name> > <file_location>
 
Optional Flags:
* {{ ic |-h <host>}} - Allows specification of host, such as for connecting to a remote server.
* {{ ic |-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic |3306}}.
* {{ ic |<nowiki>--password=<value></nowiki>}} - Replaces the above {{ ic |-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>


=== Deleting a Database ===
Optional Flags:
From the MySQL Shell:
* {{ ic |-h <host>}} - Allows specification of host, such as for connecting to a remote server.
{{ bc | DROP DATABASE <database_name>; }}
* {{ ic |-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic |3306}}.
* Where {{ ic |<database_name>}} is replaced by the name of the database to delete. Note that this action cannot be undone.
* {{ ic |<nowiki>--password=<value></nowiki>}} - Replaces the above {{ ic |-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.
* {{ ic |<nowiki>--column-statistics=0</nowiki>}} - Required if mysqldump gives an error about "column statistics". May or may not show up, depending on database setup.

Latest revision as of 02:23, 7 October 2022

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.
Retrieved from "https://wiki.brandon-rodriguez.com/index.php?title=Programming/MySQL/Databases&oldid=652"