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.

On CentOS

Installing MariaDB

Install MariaDB using yum:

sudo yum install mariadb-server

and ensure that it starts on boot:

sudo systemctl enable mariadb

Starting and stopping the server

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

sudo service mariadb start
sudo service mariadb stop

Authenticating the root user

Edit the file /etc/my.cnf.d/server.cnf and add these lines:

[mariadb]
plugin_load = auth_socket

Next, alter the mysql database so that the root user uses the unix_socket plugin:

$ sudo mysqld_safe --skip-grant-tables --skip-networking &
$ sudo mysql -u root
[sudo] password for mounts:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...

MariaDB [(none)]> 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
MariaDB [mysql]> UPDATE USER SET plugin='unix_socket' WHERE user='root';
MariaDB [mysql]> FLUSH PRIVILEGES;

Then restart the server:

sudo kill `/var/run/mariadb/mariadb.pid`
sudo systemctl start mariadb

On Ubuntu

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:

[mysqld]
...
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)

mysql>

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

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

Then restart the server:

sudo service mysql restart

Further reading