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:
[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