Programming/MySQL/Setup - Revision history

Brodriguez: Add command

2020-11-20T08:24:46Z

Add command

← Older revision		Revision as of 08:24, 20 November 2020
Line 13:		Line 13:
	Basic installation can be performed with:		Basic installation can be performed with:
	sudo apt install mysql-server		sudo apt install mysql-server

			Optionally install programming dependencies (required for things like Django project serving) via:
			sudo apt install libmysqlclient-dev

	From here, you can access MySQL via:		From here, you can access MySQL via:

Brodriguez: Add todo

2020-11-20T06:56:51Z

Add todo

← Older revision		Revision as of 06:56, 20 November 2020
Line 23:		Line 23:
	sudo mysql_secure_installation		sudo mysql_secure_installation

			{{ todo \| Document how to set default character set during setup. }}

	== General User Management ==		== General User Management ==

Brodriguez: Add list of available permissions

2020-11-20T06:53:18Z

Add list of available permissions

← Older revision		Revision as of 06:53, 20 November 2020
Line 58:		Line 58:

	Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.		Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.

			=== Permission Types ===
			You can see the full, official documentation of current permissions at https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html.

			However, that page is kind of dense, so here's a condensed list of the main privileges you'll likely need at any given time:
			* {{ ic \|ALL PRIVILEGES}} - Gives full permission access to the specified database. If no database is selected, then gives user all possible MySQL permissions.
			* {{ ic \|GRANT OPTION}} - Gives permission to grant or remove other's privileges.
			* {{ ic \|CREATE}} - Gives permission to create new tables or databases.
			* {{ ic \|DROP}} - Gives permission to delete tables or databases.
			* {{ ic \|SELECT}} - Gives permission to pull data from tables or databases.
			* {{ ic \|INSERT}} - Gives permission to insert rows into tables.
			* {{ ic \|UPDATE}} - Gives permission to update table rows.
			* {{ ic \|DELETE}} - Gives permission too delete rows from tables.

	=== Show User Permissions ===		=== Show User Permissions ===

Brodriguez: Correct commands and organize page further

2020-11-20T06:30:42Z

Correct commands and organize page further

← Older revision		Revision as of 06:30, 20 November 2020
Line 1:		Line 1:
			{{ note \| Unless otherwise specified, all commands from this section assumes you're in the [[MySQL#Basics \| MySQL shell]]. }}


	== Installation ==		== Installation ==
	=== Windows ===		=== Windows ===
Line 9:		Line 12:
	=== Ubuntu ===		=== Ubuntu ===
	Basic installation can be performed with:		Basic installation can be performed with:
	~~{{ bc \|~~ sudo apt install mysql-server }}		sudo apt install mysql-server

	From here, you can access MySQL via:		From here, you can access MySQL via:
	~~{{ bc \|~~ sudo mysql }}		sudo mysql

	==== Securing the Installation ====		==== Securing the Installation ====
Line 18:		Line 21:

	To configure MySQL for security also run:		To configure MySQL for security also run:
	~~{{ bc \|~~ sudo mysql_secure_installation }}		sudo mysql_secure_installation


	== User Management ==		== General User Management ==
	~~{{ note \| Unless otherwise specified, all commands from this section assume you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}~~

	=== List All Users ===		=== List All Users ===
	CREATE USER user FROM mysql.user;		CREATE USER user FROM mysql.user;
Line 30:		Line 31:
	CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';		CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

	=== Remove User===		=== Remove User ===
	DROP USER '<username>'@'localhost';		DROP USER '<username>'@'localhost';

Line 36:		Line 37:
	ALTER USER '<username>'@'localhost' IDENTIFIED BY '<password>';		ALTER USER '<username>'@'localhost' IDENTIFIED BY '<password>';

	=== User Permissions ===		=== Connecting as Root ===
			{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

			For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic \|root}}. This is no longer the default, but can easily be set with a few commands:
			ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
			FLUSH PRIVILEGES;

			Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).

			The {{ ic \|FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.


			Once set, the {{ ic \|sudo mysql}} command will likely no longer work. Instead, login with root as {{ ic \|mysql -u root -p}}.


			== User Permissions ==
	Once created, a user account needs to be given permission in order to be able to do anything. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.		Once created, a user account needs to be given permission in order to be able to do anything. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.

Line 43:		Line 59:
	Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.		Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.

	==== Show User Permissions ====		=== Show User Permissions ===
	SHOW GRANTS FOR '<username>'@'localhost';		SHOW GRANTS FOR '<username>'@'localhost';

	==== Add User Permissions ====		=== Add User Permissions ===
	GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';		GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

	==== Remove User Permissions ====		=== Remove User Permissions ===
	~~GRANT~~ <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';		REVOKE <permission> ON <database_name>.<table_name> FROM '<username>'@'localhost';

	==== Full Permissions ====		=== Full Permissions ===
	{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}		{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

	To give a user full permissions (like the root account), run:		To give a user full permissions (like the root account), run:
	GRANT ALL PRIVILEGES ON . TO '<username>'@'localhost' WITH GRANT OPTION;		GRANT ALL PRIVILEGES ON . TO '<username>'@'localhost' WITH GRANT OPTION;
	~~FLUSH PRIVILEGES;~~

	~~The {{ ic \|FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.~~

	~~=== Connecting as Root ===~~
	~~{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}~~

	~~For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic \|root}}. This is no longer the default, but can easily be set with a few commands:~~
	~~ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';~~
	~~FLUSH PRIVILEGES;~~

	~~Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).~~

	~~The {{ ic \|FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.~~


	~~Once set, the {{ ic \|sudo mysql}} command will likely no longer work. Instead, login with root as {{ ic \|mysql -u root -p}}.~~

Brodriguez: Add command

2020-11-20T06:17:46Z

Add command

← Older revision		Revision as of 06:17, 20 November 2020
Line 42:		Line 42:

	Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.		Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.

			==== Show User Permissions ====
			SHOW GRANTS FOR '<username>'@'localhost';

	==== Add User Permissions ====		==== Add User Permissions ====

Brodriguez: Clean up page and add more user commands

2020-11-20T06:15:09Z

Clean up page and add more user commands

← Older revision		Revision as of 06:15, 20 November 2020
Line 21:		Line 21:


	== User ~~Accounts~~ ==		== User Management ==
	=== ~~Connecting as Root~~ ===		{{ note \| Unless otherwise specified, all commands from this section assume you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}
	~~{{ Warn \| Note that this is not recommended for any instances when the database should be secure~~. ~~Use this only for development and testing instances, where security of data does not matter. }}~~
			=== List All Users ===
			CREATE USER user FROM mysql.user;

			=== Create User ===
			CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

	For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic \|root}}. This is no longer the default, but can easily be set with a few commands. First, enter the MySQL shell. Then enter:		=== Remove User===
	~~{{ bc \| ALTER~~ USER '~~root~~'@'localhost' ~~IDENTIFIED WITH mysql_native_password BY 'root'; }}~~		DROP USER '<username>'@'localhost';
	* This will set the root user's password to "root".
	* Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).
	~~{{ bc \| FLUSH PRIVILEGES~~; }}
	* This ensures that changes will propagate through the server without a reboot.

			=== Change User Password ===
			ALTER USER '<username>'@'localhost' IDENTIFIED BY '<password>';

	Once ~~set~~, ~~the {{ ic \|sudo mysql}} command will likely no longer work~~. ~~Instead~~, ~~login with root as {{ ic \| mysql -u root -p}}~~.		=== User Permissions ===
			Once created, a user account needs to be given permission in order to be able to do anything. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.

	~~=== Adding a New User Account ===~~		MySQL generally assumes permissions will be granted per database. And for security, it even allows restricting access per table.
	~~Add a new user account with:~~
	~~{{ bc \| CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>'; }}~~

	~~Where {{ ic \|<username>}}~~ and {{ic \|~~<password>~~}} ~~are replaced by~~ the ~~desired username and password for the user account~~.		Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.

			==== Add User Permissions ====
			GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

	=== User Permissions ===		==== Remove User Permissions ====
	~~Once created, a user account needs to be given~~ permission ~~in order to be useful at all~~. ~~Note that, from a security standpoint, it~~'~~s always best to give the minimum possible permissions necessary for the user to do their job.~~		GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

	==== Full Permissions ====		==== Full Permissions ====
	{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}		{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

	To give a user full permissions (like the root account), ~~launch the MySQL Shell, then~~ run:		To give a user full permissions (like the root account), run:
	~~{{ bc \|~~ GRANT ALL PRIVILEGES ON . TO '<username>'@'localhost' WITH GRANT OPTION; }}		GRANT ALL PRIVILEGES ON . TO '<username>'@'localhost' WITH GRANT OPTION;
	* Where {{ ic \| ~~username~~}} is ~~replaced by~~ the ~~name of~~ the user ~~account to adjust~~.		FLUSH PRIVILEGES;
	{{ bc \| FLUSH PRIVILEGES; }}
	* This ensures that changes will propagate through the server without a reboot.		The {{ ic \|FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.

			=== Connecting as Root ===
			{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

			For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic \|root}}. This is no longer the default, but can easily be set with a few commands:
			ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
			FLUSH PRIVILEGES;

			Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).

			The {{ ic \|FLUSH PRIVILEGES}} line ensures that changes will propagate through the server without a reboot.


			Once set, the {{ ic \|sudo mysql}} command will likely no longer work. Instead, login with root as {{ ic \|mysql -u root -p}}.

Brodriguez: Update installation section

2020-11-20T05:57:53Z

Update installation section

← Older revision		Revision as of 05:57, 20 November 2020
Line 1:		Line 1:
	== Installation ==		== Installation ==
			=== Windows ===
			Download and install from https://dev.mysql.com/downloads/installer/

			=== Arch Linux ===
			Arch Linux recommends installing [[Programming/MariaDB \| MariaDB]] instead.<br>
			For installation details, see [[Programming/MariaDB/Setup \| MariaDB setup]].

	=== Ubuntu ===		=== Ubuntu ===
Line 18:		Line 24:
	=== Connecting as Root ===		=== Connecting as Root ===
	{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}		{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}


	For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic \|root}}. This is no longer the default, but can easily be set with a few commands. First, enter the MySQL shell. Then enter:		For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic \|root}}. This is no longer the default, but can easily be set with a few commands. First, enter the MySQL shell. Then enter:

Brodriguez: Move basic "shell access" to MySQL page

2020-11-20T05:49:47Z

Move basic "shell access" to MySQL page

← Older revision		Revision as of 05:49, 20 November 2020
Line 13:		Line 13:
	To configure MySQL for security also run:		To configure MySQL for security also run:
	{{ bc \| sudo mysql_secure_installation }}		{{ bc \| sudo mysql_secure_installation }}


	~~== The MySQL Shell ==~~
	~~You can access the MySQL server from a terminal via the MySQL shell.~~

	~~If you have a fresh install with no setup yet, then this is likely done via {{ ic \|sudo mysql}}.~~

	~~If you have done some setup, such as setting a password for the root account or creating a user to login with, then use {{ ic \|mysql -u <username> -p}}.~~


	== User Accounts ==		== User Accounts ==

	=== Connecting as Root ===		=== Connecting as Root ===
	{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}		{{ Warn \| Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

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

2020-11-03T00:30:29Z

Brodriguez moved page MySQL/Setup to Programming/MySQL/Setup

← Older revision

Revision as of 00:30, 3 November 2020

Brodriguez: Import from original mysql page

2020-11-02T20:35:45Z

Import from original mysql page

New page

== Installation ==

=== Ubuntu ===
Basic installation can be performed with:
{{ bc | sudo apt install mysql-server }}

From here, you can access MySQL via:
{{ bc | sudo mysql }}

==== Securing the Installation ====
Note that the above command alone is an insecure installation, and is not suitable for things like production, or instances where stored data is not just test data and thus should be stored securely.

To configure MySQL for security also run:
{{ bc | sudo mysql_secure_installation }}

== The MySQL Shell ==
You can access the MySQL server from a terminal via the MySQL shell.

If you have a fresh install with no setup yet, then this is likely done via {{ ic |sudo mysql}}.

If you have done some setup, such as setting a password for the root account or creating a user to login with, then use {{ ic |mysql -u <username> -p}}.

== User Accounts ==

=== Connecting as Root ===
{{ Warn | Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

For previous versions of MySQL, it was standard to default the root user as a login user with a password of {{ ic |root}}. This is no longer the default, but can easily be set with a few commands. First, enter the MySQL shell. Then enter:
{{ bc | ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; }}
* This will set the root user's password to "root".
* Alternatively, alter the password to whatever you desire, if you want to add a tiny bit more security (setting root as a login-able password user is still not very recommended for security reasons).
{{ bc | FLUSH PRIVILEGES; }}
* This ensures that changes will propagate through the server without a reboot.

Once set, the {{ ic |sudo mysql}} command will likely no longer work. Instead, login with root as {{ ic | mysql -u root -p}}.

=== Adding a New User Account ===
Add a new user account with:
{{ bc | CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>'; }}

Where {{ ic |<username>}} and {{ic |<password>}} are replaced by the desired username and password for the user account.

=== User Permissions ===
Once created, a user account needs to be given permission in order to be useful at all. Note that, from a security standpoint, it's always best to give the minimum possible permissions necessary for the user to do their job.

==== Full Permissions ====
{{ Warn | Note that this is not recommended for any instances when the database should be secure. Use this only for development and testing instances, where security of data does not matter. }}

To give a user full permissions (like the root account), launch the MySQL Shell, then run:
{{ bc | GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION; }}
* Where {{ ic | username}} is replaced by the name of the user account to adjust.
{{ bc | FLUSH PRIVILEGES; }}
* This ensures that changes will propagate through the server without a reboot.

← Older revision		Revision as of 06:53, 20 November 2020
Line 58:		Line 58:

	Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.		Thus for security, the general syntax is to specify both database and table to set permissions for. To set the permission for the user on all tables in the database, or to set the permission on all database, use an {{ ic \|*}} character instead of the database or table name.

			=== Permission Types ===
			You can see the full, official documentation of current permissions at https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html.

			However, that page is kind of dense, so here's a condensed list of the main privileges you'll likely need at any given time:
			* {{ ic \|ALL PRIVILEGES}} - Gives full permission access to the specified database. If no database is selected, then gives user all possible MySQL permissions.
			* {{ ic \|GRANT OPTION}} - Gives permission to grant or remove other's privileges.
			* {{ ic \|CREATE}} - Gives permission to create new tables or databases.
			* {{ ic \|DROP}} - Gives permission to delete tables or databases.
			* {{ ic \|SELECT}} - Gives permission to pull data from tables or databases.
			* {{ ic \|INSERT}} - Gives permission to insert rows into tables.
			* {{ ic \|UPDATE}} - Gives permission to update table rows.
			* {{ ic \|DELETE}} - Gives permission too delete rows from tables.

	=== Show User Permissions ===		=== Show User Permissions ===