Programming/MySQL/Setup

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

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

Optionally install programming dependencies (required for things like Django project serving) via:

sudo apt install libmysqlclient-dev

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

ToDo: Document how to set default character set during setup.

General User Management

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>';

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.

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.

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:

• ALL PRIVILEGES - Gives full permission access to the specified database. If no database is selected, then gives user all possible MySQL permissions.
• GRANT OPTION - Gives permission to grant or remove other's privileges.
• CREATE - Gives permission to create new tables or databases.
• DROP - Gives permission to delete tables or databases.
• SELECT - Gives permission to pull data from tables or databases.
• INSERT - Gives permission to insert rows into tables.
• UPDATE - Gives permission to update table rows.
• DELETE - Gives permission too delete rows from tables.

Show User Permissions

SHOW GRANTS FOR '<username>'@'localhost';

Add User Permissions

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

Remove User Permissions

REVOKE <permission> ON <database_name>.<table_name> FROM '<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;