Programming/MySQL: Difference between revisions

From Dev Wiki
Jump to navigation Jump to search
(Add user permission stuff)
(Correct link)
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
[https://www.mysql.com/ MySQL] is an open source database management system.


== Installation ==
It is one of the most commonly used database systems, designed to work with the SQL standard.


=== Ubuntu ===
Alternatives are [[Programming/MariaDB | MariaDB]] and [[Programming/PostgreSQL | PostgreSQL]].
Basic installation can be performed with:
{{ bc | sudo apt install mysql-server }}


From here, you can access MySQL via:
== Setup ==
{{ bc | sudo mysql }}
* [[Programming/MySQL/Setup | Setup]]


==== Securing the Installation ====
== Syntax ==
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.
* [[Programming/MySQL/Databases | Databases]]
* [[MySQL/Queries | Queries]]


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


 
== Basics ==
== The MySQL Shell ==
=== Accessing the MySQL Shell ===
You can access the MySQL server from a terminal via 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 a fresh install with no setup yet, then this is likely done via:
 
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 ===
If you have done some setup, such as setting a password for the root account or creating a user to login with, then use:
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.
mysql -u <username> -p


==== Full Permissions ====
=== Exiting the MySQL 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. }}
exit


To give a user full permissions (like the root account), launch the MySQL Shell, then run:
or
{{ bc | GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION; }}
\q
* 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.

Latest revision as of 06:58, 20 November 2020

MySQL is an open source database management system.

It is one of the most commonly used database systems, designed to work with the SQL standard.

Alternatives are MariaDB and PostgreSQL.

Setup

Syntax


Basics

Accessing 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: 

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: 

mysql -u <username> -p

Exiting the MySQL Shell

exit

or 

\q
Retrieved from "https://wiki.brandon-rodriguez.com/index.php?title=Programming/MySQL&oldid=528"