Skip to content

How to set up mysql

Most of our servers and projects use the mysql server. Each server should be set up in the same way, regardless of operating system. In particular, the mysql root user should authenticate without a password. Instead, it should use a UNIX socket to ensure that it is the root user in the OS that is trying to start a mysql client as the root mysql user. That way, you should start mysql client like this:

mysql -u root

and not use the -p switch.

Installing mysql server

Install mysql server with apt:

sudo apt-get update
sudo apt-get install mysql-server

and ensure that the server starts on boot:

sudo systemctl enable mysql

Starting and stopping the server

You can start (and stop) the server using service:

sudo service start mysql
sudo service stop mysql

Authenticating the root user

Open the file /etc/mysql/mysql.conf.d/mysqld.cnf as root, and check that you have a line like this:

socket          = /var/run/mysqld/mysqld.sock

Next, check whether the the auth_socket plugin is already used for root:

$ sudo mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User, Host, plugin FROM mysql.user;
| User             | Host      | plugin                |
| root             | localhost | auth_socket           |
5 rows in set (0.01 sec)


If auth_socket isn't already set, update the table row now:

mysql> UPDATE user SET plugin='auth_socket' WHERE User='root';

Then restart the server:

sudo service mysql restart

Further reading