Note
- 8 Jan 2020: Configuring MySQL-server updated. Removing completely MySQL added.
- 6 Jan 2020: Updated.
- 8 May 2019: References to PHP5 deleted. Secure installation added. Root user authentication added. automysqlbackup added.
- 25 Dec 2014: First created.
Intro
MySQL is a database server.
PHP (PHP5 before Ubuntu 16.04) is a web page programming language.
phpMyAdmin is a web interface to administer the MySQL server.
Install MySQL-server
Execute:
$ sudo apt update $ sudo apt install mysql-server $ sudo systemctl start mysql.service $ sudo systemctl status mysql.service
Config MySQL-server
(section added, 8 May 2019)
Execute:
$ sudo mysql_secure_installation
Enter new root password for the first time or the existing root password.
When answering the secure installation questions:
- validate password component - yes
- set password validation policy - 2 for MEDIUM
- set password strength - high
- change the password for root - yes or no as appropriate
- set new password - as appropriate
- re-enter new password -
- continue with the password - yes or no as appropriate
- remove anonymous users - yes
- disallow root login remotely - no
- remove test database - yes
- reload privilege table now - yes
(steps updated, 8 Jan 2022)
(suggested answers added, 6 Jan 2020)
Adjust authentication for use with phpMyAdmin
(section added, 8 May 2019)
If not done, the phpMyAdmin menu would not provide a choice to add users.
Execute:
$ sudo mysql -u root -p
enter the root user's password when prompted.
Execute:
mysql> SELECT user,plugin FROM mysql.user;
It will show that the root user's authentication plugin is "auth_socket".
Execute:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<root user password>'; mysql> FLUSH PRIVILEGES; mysql> SELECT user,plugin FROM mysql.user;
(semi colon added after "PRIVILEGES", 6 Jan 2020)
It should show that the root user's authentication plugin has been changed to "mysql_nature_password".
Install PHP and related apache2 module
Execute:
$ sudo apt install php libapache2-mod-php
Install phpMyAdmin
Execute:
$ sudo apt install phpmyadmin
Set its own password.
Choose whether to keep the existing database whenever phpmyadmin is removed and re-installed.
Enable Apache2 config:
$ sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf $ sudo a2enconf phpmyadmin.conf
Whenever Apache2 has been purged and re-installed, this enabling must be done again.
Restart Apache2 service:
Execute:
$ sudo systemctl restart apache2.service
Create a database user and a database
Execute:
http://localhost/phpmyadmin or http://www.kctang.com.hk/phpmyadmin
Enter login name and password.
Create a user called <name, e.g. Drupal8> together with a database also called <name>:
- click "Users" at the top menu bar
- click "Add user" at the page middle
- enter <name> at the User name entry
- select "Local" at the Host entry
- enter and re-type the password
- click "Create database with same name and grant all privileges"
- click "Go" at the bottom
Install automysqlbackup
(section added, 8 May 2019)
The software will backup mySQL databases daily, weekly and monthly.
Execute to install:
$ sudo apt install automysqlbackup
Edit the configuration file:
$ sudo gedit /etc/default/automysqlbackup
Specify:
BACKUPDIR="/var/lib/automysqlbackup" as the backup directory
MAILCONTENT="log" to send log email
MAILADDR="root" to send the email to root user, which has been set under Postfix Aliases to re-direct to the appropriate user.
Execute to run for the first time:
$ sudo automysqlbackup
Inspect daily backups:
$ sudo ls /var/lib/automysqlbackup/daily
Remove mySQL completely
(section added, 8 Jan 2022)
sudo systemctl stop mysql.service sudo apt purge mysql* sudo apt autoremove (optional) sudo apt autoclean (optional) sudo apt remove dbconfig-mysql sudo rm -r /var/lib/mysql sudo rm -r /log/mysql sudo rm -r /etc/mysql sudo deluser mysql