Install MySQL server + PHP + phpMyAdmin

Go to End

Note

  • 28 Apr 2024: Updated. "Manipulate whole database" section added.
  • 7 Nov 2023: Changing transaction_isolation.
  • 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.

Replace "[text]" with "actual text without [ or ] and without spaces" in the following commands.

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 sudo user password, and new mysql root password for the first time or the existing mysql root password.

When answering the secure installation questions:

  • validate password component - yes
  • set password validation policy - 2 for HIGH

(corrected, "2" is for HIGH, 28 April 2023)

  • 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

$ mysql -u root -p

enter the mysql root user's password when prompted.

(revised to remove unnecessary "sudo", 28 Apr 2024)

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 [mysql_root_user_password];
mysql> FLUSH PRIVILEGES;
mysql> SELECT user,plugin FROM mysql.user;
mysql> quit

(semi colon added after "PRIVILEGES", 6 Jan 2020)

It should show that the root user's authentication plugin has been changed to "mysql_native_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:

 https://localhost/phpmyadmin
 (or)
 https://www.kctang.com.hk/phpmyadmin

Enter login name and password.

Create a user called [name, e.g. Drupal] together with a database also called [database_name]:

  • click "Users" at the top menu bar
  • click "Add user" at the page middle
  • enter [database_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

Set transaction_isolation to READ COMMITTED

(section added, 6 Nov 2023)

To suit Drupal 10, change the transaction_isolation from "REPEATABLE READ" to "READ COMMITTED".

Execute:

$ mysql -u root -p               (enter mysql root user's password)

(revised to remove "sudo", 28 Apr 2024)

Execute:

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> SELECT @@GLOBAL.transaction_isolation;
mysql> quit

The first command is to set. The second command is to verify.

Manipulate whole database

(section added, 28 Apr 2024)

Execute:

$ mysql -u root -p               (enter mysql root user's password when prompted)
or
$ mysql -u [database_username] -p[database_user_password]   (no space after "-p")

Show existing databases:

mysql> SHOW DATABASES;

Create database:

mysql> CREATE DATABASE [new_database_name];
(or)
$ mysqladmin -u [database_username] -p[database_user_password] create [new_.database_name]

Delete database:

mysql> DROP DATABASE [database_name];
(or)
$ mysqladmin -u [database_username] -p[database_user_password] drop [database_name]

Export database:

$ mysqldump -u [database_username] -p[database_user_password] -R [database_name] > [filename].sql

No space right after "-p".

"-R" to include stored procedures and functions.

Import exported database:

$ mysql -u [database_username] -p[database_user_password] [empty_database_name] < [filename].sql

Rename database:

  • Export as above.
  • Create a blank empty database of a new name as above
  • Import to the new empty database as above.
  • Check if restored database useable as intended.

Restore backup database:

  • Find a backup .sql or .sql.gz file, copy to the current directory, and, if gz file, unzip to sql file:
$ sudo ls -ls /var/lib/automysqlbackup/daily/
(or)
$ sudo ls -ls /var/lib/automysqlbackup/weekly/
(or)
$ sudo ls -ls /var/lib/automysqlbackup/monthly/
$ sudo cp [path_to_backup_file]/[backup_filename] [temp_filename].sql.gz
$ sudo gunzip [temp_filename].sql.gz
$ sudo chmod 777 [temp_filename].sql
  • Backup the existing database as above.
  • Delete the existing database as above.
  • Create a blank empty database of the same name or a new name as above.
  • Import to the new empty database of the desired name as above.
  • Check if restored database useable as intended.
  • Delete the temporary file if no longer to be used:
$ rm [temp_filename].sql

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

End of Page

Back to top with progress scrollbar