Set up MySQL

How to set up MySQL on a local system.

1. Install MySQL (or MariaDB):

First you need to install MySQL; on some distributions, the package mysql actually installs MariaDB, which is a community-developed fork of MySQL. It was originally supposed to be a drop-in replacement; this is no longer the main goal, but most commands should work just the same.

# OpenSUSE
sudo zypper install mysql

2. Start MySQL

Now, start the server. If the server isn’t active, you can’t connect to it, i.e. you can’t use MySQL.

systemctl start mysql

# Or, if you installed MariaDB:
systemctl start mariadb

3. Basic security settings

To choose some basic settings on the first install, run

sudo mysql_secure_installation

This will run a script; you just need to follow the steps.

Instead, if you only want to set a password for the root user, run

mysqladmin -u root -h <hostname> password '<new-password>'

4. Log in as root

Logging in as root is necessary to create other users; to do that, run

sudo mysql -u root

Using sudo may or may not be necessary depending on your settings.

5. Create another user

To create another user, run the following while logged in as root:

create user '<username>'@'<host>localhost';

# Example:
create user 'web'@'localhost';

6. Set a password for the new user

To set a password for the user you just created, run the following while still logged in as root:

alter user '<username>'@'<host>' identified by '<your-password>';

# Example:
alter user 'web'@'localhost0 identified by 'CatPineapple420';

7. Test the user

To test the user you just created, first log out the root account:

exit

Then, try logging in with the new user:

mysql -u '<username>' -p 

This will prompt you for the password you set previously.