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