Install MySQL server + PHP + phpMyAdmin

Go to End

8 May 2019: References to PHP5 deleted. Secure installation added. Root user authentication added. automysqlbackup added.

25 Dec 2014: First created.

Note

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 restart mysql.service
$ sudo systemctl status mysql.service

Config MySQL-server

(section added, 8 May 2019)

Execute:

$ sudo mysql_secure_installation

When answering the secure installation questions:

  • set password strength
  • remove anonymous users
  • allow or disallow root login remotely
  • remove test database
  • reload privilege table now
  • set root password

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:

$ 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;

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 get 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

 

End of Page