Programming/MySQL/Setup

Installation

Windows

Download and install from https://dev.mysql.com/downloads/installer/

Arch Linux

Arch Linux recommends installing MariaDB instead.
For installation details, see MariaDB setup.

Ubuntu

Basic installation can be performed with:

 sudo apt install mysql-server 

From here, you can access MySQL via:

 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:

 sudo mysql_secure_installation 

User Management

Note: Unless otherwise specified, all commands from this section assume you're in the PostgreSQL shell.

List All Users

CREATE USER user FROM mysql.user;

Create User

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

Remove User

DROP USER '<username>'@'localhost';

Change User Password

ALTER USER '<username>'@'localhost' IDENTIFIED BY '<password>';

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.

MySQL generally assumes permissions will be granted per database. And for security, it even allows restricting access per table.

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 * character instead of the database or table name.

Add User Permissions

GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

Remove User Permissions

GRANT <permission> ON <database_name>.<table_name> TO '<username>'@'localhost';

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), run:

GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The 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 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 FLUSH PRIVILEGES line ensures that changes will propagate through the server without a reboot.

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