Programming/MySQL/Databases - Revision history

Brodriguez: Add column-statistics note

2022-10-07T02:23:55Z

Add column-statistics note

← Older revision		Revision as of 02:23, 7 October 2022
Line 46:		Line 46:
	* {{ ic \|-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|3306}}.		* {{ 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.		* {{ 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.

Brodriguez: Correct commands

2020-11-20T07:11:22Z

Correct commands

← Older revision		Revision as of 07:11, 20 November 2020
Line 43:		Line 43:

	Optional Flags:		Optional Flags:
	* {{ ic \|-h <~~value~~>}} - Allows specification of host, such as for connecting to a remote server.		* {{ ic \|-h <host>}} - Allows specification of host, such as for connecting to a remote server.
	* {{ ic \|-P <~~value~~>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|3306}}.		* {{ 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.		* {{ 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.

Brodriguez: Add another flag

2020-11-20T07:10:34Z

Add another flag

← Older revision		Revision as of 07:10, 20 November 2020
Line 37:		Line 37:
	* {{ ic \|-h <host>}} - Allows specification of host, such as for connecting to a remote server.		* {{ 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 \|-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 ===		=== Import Database from File ===
Line 45:		Line 45:
	* {{ ic \|-h <value>}} - Allows specification of host, such as for connecting to a remote server.		* {{ ic \|-h <value>}} - Allows specification of host, such as for connecting to a remote server.
	* {{ ic \|-P <value>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|3306}}.		* {{ ic \|-P <value>}} - 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.

Brodriguez: Add command flags

2020-11-20T07:05:51Z

Add command flags

← Older revision		Revision as of 07:05, 20 November 2020
Line 33:		Line 33:
	=== Export Database to File ===		=== Export Database to File ===
	mysqldump -u <username> -p <database_name> > <file_location>		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}}.


	=== Import Database from File ===		=== Import Database from File ===
	mysql -u <username> -p <database_name> < <file_location>		mysql -u <username> -p <database_name> < <file_location>

			Optional Flags:
			* {{ ic \|-h <value>}} - Allows specification of host, such as for connecting to a remote server.
			* {{ ic \|-P <value>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|3306}}.

Brodriguez: Add commands

2020-11-20T06:43:15Z

Add commands

← Older revision		Revision as of 06:43, 20 November 2020
Line 4:		Line 4:

	== General Commands ==		== General Commands ==
			=== Show Databases ===
			SHOW DATABASES;

	=== Create a New Database ===		=== Create a New Database ===
	CREATE DATABASE <database_name>;		CREATE DATABASE <database_name>;
Line 23:		Line 26:
	{{ warn \| Note that this action cannot be undone. }}		{{ warn \| Note that this action cannot be undone. }}
	DROP DATABASE <database_name>;		DROP DATABASE <database_name>;

			=== Set Database Privileges ===
			See [[Programming/MySQL/Setup#User Permissions \| MySQL User Permissions]].

	== Database Backups ==		== Database Backups ==

Brodriguez: Add commands and clean up page

2020-11-20T06:40:43Z

Add commands and clean up page

← Older revision		Revision as of 06:40, 20 November 2020
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~~.		=== 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>;

			== Database Backups ==
			=== Export Database to File ===
			mysqldump -u <username> -p <database_name> > <file_location>

	=== ~~Deleting a~~ Database ===		=== Import Database from File ===
	~~From the MySQL Shell:~~		mysql -u <username> -p <database_name> < <file_location>
	~~{{ bc \| DROP DATABASE~~ <database_name>~~; }}~~
	* Where {{ ic \|<~~database_name~~>~~}} is replaced by the name of the database to delete. Note that this action cannot be undone.~~

Brodriguez: Brodriguez moved page MySQL/Databases to Programming/MySQL/Databases

2020-11-03T00:30:17Z

Brodriguez moved page MySQL/Databases to Programming/MySQL/Databases

← Older revision

Revision as of 00:30, 3 November 2020

Brodriguez: Import from original mysql page

2020-11-02T20:36:08Z

Import from original mysql page

New page

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

=== Creating a New Database ===
From the MySQL Shell:
{{ bc | CREATE DATABASE <database_name>; }}
* 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.

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.

=== Loading a Database ===
From the MySQL Shell:
{{ 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.

=== Deleting a Database ===
From the MySQL Shell:
{{ bc | DROP DATABASE <database_name>; }}
* Where {{ ic |<database_name>}} is replaced by the name of the database to delete. Note that this action cannot be undone.

← Older revision		Revision as of 02:23, 7 October 2022
Line 46:		Line 46:
	* {{ ic \|-P <port>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|3306}}.		* {{ 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.		* {{ 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.

← Older revision		Revision as of 07:10, 20 November 2020
Line 37:		Line 37:
	* {{ ic \|-h <host>}} - Allows specification of host, such as for connecting to a remote server.		* {{ 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 \|-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 ===		=== Import Database from File ===
Line 45:		Line 45:
	* {{ ic \|-h <value>}} - Allows specification of host, such as for connecting to a remote server.		* {{ ic \|-h <value>}} - Allows specification of host, such as for connecting to a remote server.
	* {{ ic \|-P <value>}} - Allows specification of port, such as for non-standard installations that don't use the default port {{ ic \|3306}}.		* {{ ic \|-P <value>}} - 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.