Tags Archives: mysql

How To Install MariaDB on Ubuntu 20.04 LTS

By Admin on April 22, 2022 in

Notes for installing MariaDB on Ubuntu 20.04.

root@len:/var/www# apt update && sudo apt upgrade

root@len:/var/www# apt -y install software-properties-common
Reading package lists… Done
Building dependency tree
Reading state information… Done
software-properties-common is already the newest version (0.99.9.8).
0 to upgrade, 0 to newly install, 0 to remove and 0 not to upgrade.
root@len:/var/www#

apt-key adv –fetch-keys ‘https://mariadb.org/mariadb_release_signing_key.asc’

root@len:/var/www# apt-key adv –fetch-keys ‘https://mariadb.org/mariadb_release_signing_key.asc’
Executing: /tmp/apt-key-gpghome.J3PN2sGZod/gpg.1.sh –fetch-keys https://mariadb.org/mariadb_release_signing_key.asc
gpg: requesting key from ‘https://mariadb.org/mariadb_release_signing_key.asc’

gpg: key F1656F24C74CD1D8: public key “MariaDB Signing Key <signing-key@mariadb.org>” imported
gpg: Total number processed: 1
gpg: imported: 1
root@len:/var/www#
root@len:/var/www#

add-apt-repository ‘deb [arch=amd64] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main’

sudo apt update
sudo apt install mariadb-server mariadb-client

1 package can be upgraded. Run ‘apt list –upgradable’ to see it.
root@len:/var/www# apt list –upgradabale
E: Command line option –upgradabale is not understood in combination with the other options
root@len:/var/www# apt list –upgradabale
E: Command line option –upgradabale is not understood in combination with the other options
root@len:/var/www# apt list –upgradable
Listing… Done
mysql-common/unknown 1:10.5.15+maria~focal all [upgradable from: 5.8+1.0.5ubuntu2]
N: There are 3 additional versions. Please use the ‘-a’ switch to see them.
root@len:/var/www#

root@len:/var/www# apt list –upgradable -a
Listing… Done
mysql-common/unknown 1:10.5.15+maria~focal all [upgradable from: 5.8+1.0.5ubuntu2]
mysql-common/unknown 1:10.5.13+maria~focal all
mysql-common/unknown 1:10.5.12+maria~focal all
mysql-common/focal,focal,now 5.8+1.0.5ubuntu2 all [installed,upgradable to: 1:10.5.15+maria~focal]

root@len:/var/www#

apt install mariadb-server mariadb-client

root@len:/var/www# apt install mariadb-server mariadb-client
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following additional packages will be installed:
galera-4 gawk libaio1 libcgi-fast-perl libcgi-pm-perl libdbd-mariadb-perl libdbi-perl libfcgi-perl libhtml-template-perl libmariadb3
libreadline5 libsigsegv2 libterm-readkey-perl mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server-10.5
mariadb-server-core-10.5 socat
Suggested packages:
gawk-doc libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl libipc-sharedcache-perl mailx mariadb-test
The following NEW packages will be installed
galera-4 gawk libaio1 libcgi-fast-perl libcgi-pm-perl libdbd-mariadb-perl libdbi-perl libfcgi-perl libhtml-template-perl libmariadb3
libreadline5 libsigsegv2 libterm-readkey-perl mariadb-client mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server
mariadb-server-10.5 mariadb-server-core-10.5 socat
0 to upgrade, 21 to newly install, 0 to remove and 1 not to upgrade.
Need to get 28.8 MB of archives.
After this operation, 224 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

… … ..

Setting up libreadline5:amd64 (5.2+dfsg-3build3) …
Setting up gawk (1:5.0.1+dfsg-1) …
Setting up libcgi-pm-perl (4.46-1) …
Setting up libhtml-template-perl (2.97-1) …
Setting up socat (1.7.3.3-2) …
Setting up libmariadb3:amd64 (1:10.5.15+maria~focal) …
Setting up libfcgi-perl (0.79-1) …
Setting up libterm-readkey-perl (2.38-1build1) …
Setting up libaio1:amd64 (0.3.112-5) …
Setting up libdbi-perl:amd64 (1.643-1ubuntu0.1) …
Setting up mariadb-server-core-10.5 (1:10.5.15+maria~focal) …
Setting up libcgi-fast-perl (1:2.15-1) …
Setting up libdbd-mariadb-perl (1.11-3ubuntu2) …
Setting up mariadb-client-core-10.5 (1:10.5.15+maria~focal) …
Setting up mariadb-client-10.5 (1:10.5.15+maria~focal) …
Setting up mariadb-client (1:10.5.15+maria~focal) …
Setting up mariadb-server-10.5 (1:10.5.15+maria~focal) …
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /lib/systemd/system/mariadb.service.
Setting up mariadb-server (1:10.5.15+maria~focal) …
Processing triggers for libc-bin (2.31-0ubuntu9.7) …
Processing triggers for systemd (245.4-4ubuntu3.16) …
Processing triggers for man-db (2.9.1-1) …
Processing triggers for doc-base (0.10.9) …
Processing 1 added doc-base file…
root@len:/var/www#

next secure MariaDB Server by manually running the MySQL hardening script:

mysql_secure_installation

then check the mariadb database service is started automatically after installation:

root@len:~# systemctl status mysql

you can also use the command:

root@len:~# systemctl status mariadb

for same output:

● mariadb.service – MariaDB 10.5.15 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Fri 2022-04-22 14:36:13 BST; 4min 16s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 51723 (mariadbd)
Status: “Taking your SQL requests now…”
Tasks: 9 (limit: 9308)
Memory: 61.0M
CGroup: /system.slice/mariadb.service
└─51723 /usr/sbin/mariadbd

Apr 22 14:36:13 len mariadbd[51723]: 2022-04-22 14:36:13 0 [Note] /usr/sbin/mariadbd: ready for connections.
Apr 22 14:36:13 len mariadbd[51723]: Version: ‘10.5.15-MariaDB-1:10.5.15+maria~focal’ socket: ‘/run/mysqld/mysqld.sock’ port: 3306 mariadb.org>
Apr 22 14:36:13 len systemd[1]: Started MariaDB 10.5.15 database server.
Apr 22 14:36:13 len /etc/mysql/debian-start[51746]: Upgrading MySQL tables if necessary.
Apr 22 14:36:13 len /etc/mysql/debian-start[51749]: Looking for ‘mysql’ as: /usr/bin/mysql
Apr 22 14:36:13 len /etc/mysql/debian-start[51749]: Looking for ‘mysqlcheck’ as: /usr/bin/mysqlcheck
Apr 22 14:36:13 len /etc/mysql/debian-start[51749]: This installation of MariaDB is already upgraded to 10.5.15-MariaDB.
Apr 22 14:36:13 len /etc/mysql/debian-start[51749]: There is no need to run mysql_upgrade again for 10.5.15-MariaDB.
Apr 22 14:36:13 len /etc/mysql/debian-start[51749]: You can use –force if you still want to run mysql_upgrade
Apr 22 14:36:13 len /etc/mysql/debian-start[51757]: Checking for insecure root accounts.
~
~

Test your root login to MariaDB shell using the mysql client command:

mysql -u root -p

root@len:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.5.15-MariaDB-1:10.5.15+maria~focal mariadb.org binary distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

you can check the version at any time by entering

MariaDB [(none)]> SELECT VERSION();
+—————————————+
| VERSION() |
+—————————————+
| 10.5.15-MariaDB-1:10.5.15+maria~focal |
+—————————————+
1 row in set (0.001 sec)

MariaDB [(none)]>

CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE USER ‘wordpressuser’@localhost IDENTIFIED BY ‘*********’;

MariaDB [(none)]> CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.001 sec)

NOTE! you have to use ” quote marks!!! for MariaDB – different quote marks to mysql!

MariaDB [(none)]> CREATE USER ‘wordpressuser’@localhost IDENTIFIED BY ‘****’;
Query OK, 0 rows affected (0.022 sec)

MariaDB [(none)]> GRANT ALL ON wordpress.* TO ‘wordpressuser’@’localhost’;
Query OK, 0 rows affected (0.021 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> CREATE DATABASE kevwells DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> GRANT ALL ON kevwells.* TO ‘wordpressuser’@’localhost’;
Query OK, 0 rows affected (0.021 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

How To Automate the Replication of a WordPress System from an Online Server to an Offline Localhost Domain

By Admin on April 19, 2022 in

This article documents the procedure involved in creating a local working offline replica of an online WordPress website and the automation of this process by means of a shell script.

This exercise involves a Ubuntu 20 server and laptop, both running Apache2 and MySQL/Mariadb 10.

First I installed apache2, mariadb, php, and phpmyadmin on the laptop.

Copy the WordPress Directory

I then copied the current /var/www/wordpress directory to /home/kevin/DATA on the server.

This is then accessible via the NFS share DATA on the laptop.

Then on the laptop I copied this to /var/www/wordpress, having, first of all, renamed the existing wordpress directory instance.

then on laptop:

Create the Website MySQL Database and User

root@asus:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 90
Server version: 10.3.29-MariaDB-0ubuntu0.20.10.1 Ubuntu 20.10

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

Our website WordPress database on the server is called kevwells, so we need to have a database with the same name present on our MariaDB on the laptop:

So create a database as follows on laptop with these parameters:

MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘kevwells’ );

/** MySQL database username */
define( ‘DB_USER’, ‘wordpressuser’ );

/** MySQL database password */
define( ‘DB_PASSWORD’, ‘**COMMENTED OUT***’ );

/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’ );

MariaDB [(none)]> CREATE DATABASE kevwells DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]>

MariaDB [(none)]>

Next, create a user for the database:

MariaDB [(none)]> CREATE USER wordpressuser@localhost;
Query OK, 0 rows affected (0,001 sec)

MariaDB [(none)]>

NOTE no quote marks

to set the password:

ALTER USER ‘wordpressuser’@’localhost’ IDENTIFIED BY ‘passwordcommentedout’;

pay attention to the inverted commas, these have to be the correct type else it wont work with mariadb!

To list all existing users in your database server, you need to query the user table in your mysql database.

SELECT the user and host column from the table as follows:

SELECT user, host FROM mysql.user;

Along the way, I noticed a wrongly entered username above for phpmyadmin, ie ‘phpmyadmin’ , so I deleted this:

MariaDB [(none)]> drop user ‘‘phpmyadmin’’@localhost ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

we got rid of the ‘phpmyadmin’ as this was an error with the apostrophes as part of the user name.

We already have the wordpressuser existing.

So all is well. we just need to make sure the password for our user is the correct one.

Just to be certain, I also then went into http://localhost/phpmyadmin and changed the password for user wordpressuser to **COMMENTED OUT** as above.

to set the password:

ALTER USER ‘wordpressuser’@’localhost’ IDENTIFIED BY ‘passwordcommentedout’;

pay attention to the inverted commas, these have to be the correct type else it wont work with mariadb!

It returned the message:

The password for ‘wordpressuser’@’localhost’ was changed successfully.

So on our laptop have created a kevwells database (as yet empty) and a user called wordpressuser as on the online server.

I then made following changes to the /var/www/wordpress/wp-config.php:

/* define(‘FORCE_SSL_ADMIN’, true);
*/

/*
define( ‘WP_HOME’, ‘https://kevwells.com’ );
define( ‘WP_SITEURL’, ‘https://kevwells.com’ ); */

define(‘WP_HOME’,’http://localhost’);
define(‘WP_SITEURL’,’http://localhost’);

Had to give wordpressuser privilege to database kevwells because we are using the database name kevwells and not wordpress for our website wordpress database!

MariaDB [(none)]> GRANT ALL ON kevwells.* TO wordpressuser@localhost;
Query OK, 0 rows affected (0,001 sec)

MariaDB [(none)]>

MariaDB [(none)]> flush privileges ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]>

Note that the reply of mariadb says

Query OK, 0 rows affected (0.000 sec)

but it has processed and implemented the change when you do flush privileges.

user wordpressuser then has all privileges granted to kevwells database.

Launch the WordPress Duplicator Plugin

Next, start the Duplicator WordPress Plugin procedure on the server to prepare the copy of the site for download from the server to the laptop.

You need to download both the zip file and the installer.php for duplicator – you carry out both these actions from within the duplicator dashboard in the plugin on WordPress on the server.

Then, on localhost ie laptop, we activate the duplicator installer.php in the WordPress folder on laptop.

This installs the instance onto WordPress and apache on localhost.

Finally, then do the following:

Admin Login Login to the WordPress Admin to finalize this install.

Auto delete installer files after login (recommended)

IMPORTANT FINAL STEPS: Login into the WordPress Admin to remove all installation files and finalize the install process.

This install is NOT complete until all installer files have been completely removed. Leaving any of the installer files on this server can lead to security issues.

Review Install Report
The install report is designed to give you a synopsis of the possible errors and warnings that may exist after the installation is completed.

Test Site
After the install is complete run through your entire site and test all pages and posts.

Final Security Cleanup
When completed with the installation please delete all installation files. Leaving these files on your server can be a security risk! You can remove all these files by logging into your WordPress admin and following the remove notification links or by deleting these file manually. Be sure these files/directories are removed. Optionally it is also recommended to remove the archive.zip/daf file.

dup-installer
installer.php
installer-backup.php
dup-installer-bootlog__[HASH].txt
archive.zip/daf

I moved all the above out of /var/www/wordpress to a directory called /var/www/wordpress_duplicator_myfiles/

NOTE: wordpress admin dashboard username, password, authcode etc is the same on http://localhost/wp-admin as on http://3.222.27.169/wp-admin

Assemble the MySQL Command To Modify the Website URL References

To execute statements from the command line without an interactive prompt, use the -e option:

mysql mydb -e ‘select * from foo’

We need the following command set:

UPDATE wp_options SET option_value = replace(option_value, ‘http://3.222.27.169’, ‘http://localhost’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
UPDATE wp_posts SET guid = replace(guid, ‘http://3.222.27.169′,’http://localhost’);
UPDATE wp_posts SET post_content = replace(post_content, ‘http://3.222.27.169’, ‘http://localhost’);
UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://3.222.27.169′,’http://localhost’);
FLUSH PRIVILEGES;

The mysql client utility can take a password on the command line with either the -p or –password= options.

If you use -p, there must not be any blank space after the option letter.

BUT: this is insecure because it means a user could view the password – either directly via /proc/$pid/cmdline or via the ps command.

The safest way to do this would be to create a new config file and pass it to mysql using either the –defaults-file= option.

to do this, create a file ~/.my.cnf, make it only accessible by yourself, permission 600.

[client]
user=myuser
password=mypassword

Then you don’t need type password any more. Make sure that you secure this file.

nano /root/.my.cnf

[client]
user=root
password=<yourmysqlrootpassword>

chmod 640 /root/.my.cnf
chown root.root /root/.my.cnf

-rw-r—– 1 root root 38 Apr 19 18:44 .my.cnf

Then run:

mysql –defaults-file=

mysql –defaults-file=/root/.my.cnf -e “show databases ;”

It works. So, now let’s try the full command:

/usr/bin/mysql –defaults-file=/root/.my.cnf -e “USE kevwells ; UPDATE wp_options SET option_value = replace(option_value, ‘https://kevwells.com’, ‘http://localhost’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’; UPDATE wp_posts SET guid = replace(guid, ‘https://kevwells.com’,’http://localhost’); UPDATE wp_posts SET post_content = replace(post_content, ‘https://kevwells.com’, ‘http://localhost’); UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://kevwells.com’,’http://localhost’); FLUSH PRIVILEGES;”

Build a Shell Script to Automate the Replication Process

Next we want to put this and other commands in an executable script under /usr/local/bin as the second part of our backup script routine which downloads the databases from kevwells.com to localhost.

Easiest way to do the downloading would be via the /home/kevin/ NFS Share, this also provides a regular database backup on the share.

Then call up the script using crontab.

Finally, make sure you have the two definitions to the wp-config.php and also the FORCE_SSL_ADMIN directive (we disable this as we are not using SSL on the laptop localhost apache2).

define(‘FORCE_SSL_ADMIN’, false);

define( ‘WP_HOME’, ‘http://localhost’ );
define( ‘WP_SITEURL’, ‘http://localhost’ );

We need a script to download the MySQL WordPress database from the online server at kevwells.com to the laptop, and then to make the necessary URL adjustments.

Do not simply copy database files from one machine to the other. This can lead to database inconsistencies. In any case with a dynamic online database with real-time changes this would be a recipe for disaster.

The correct way to export and import a mysql database between two machines is to use mysqldump and mysqlimport.

On an online machine with continual write actions, you would also need to pause the database while performing the export-import action. However as this is simply a database for a WordPress website with few write actions taking place, we can ignore this and go straight to the export routine.

To do this, we use mysqldump.

Use mysqldump –help to see what options are available.

The basic structure of our command will be, on the server:

mysqldump –quick db_name | gzip > db_name.gz

and to restore, ie import on the other machine:

mysqladmin create db_name

gunzip < db_name.gz | mysql db_name

using the mysql cli client the command would be (but we will use mysqladmin in our case)

root@asus:/usr/local/bin# mysql -p -uwordpressuser kevwells < /home/kevin/DATA/KEVWELLS.COM/kevwells.sql
Enter password:
root@asus:/usr/local/bin#

on the server we use:

mysqldump –quick db_name | gzip > db_name.gz
mysqldump –defaults-file=/root/.my.cnf –quick kevwells | gzip > /home/kevin/DATA/KEVWELLS.COM/kevwells.sql.gz

So,

root@gemini:~#
root@gemini:~#
root@gemini:~# mysqldump –defaults-file=/root/.my.cnf –quick kevwells | gzip > /home/kevin/DATA/KEVWELLS.COM/kevwells.sql.gz
root@gemini:~# mysqldump –defaults-file=/root/.my.cnf –quick mysql | gzip > /home/kevin/DATA/KEVWELLS.COM/mysql.sql.gz
root@gemini:~#

then on laptop:

Transfer both .sql files with database contents to the target machine and run these commands there:

mysqladmin create db_name

gunzip < db_name.gz | mysql db_name

You need to delete the old database .sql files first, else the mysqlimport command will fail:

root@asus:~# rm /home/kevin/DATA/KEVWELLS.COM/kevwells.sql
root@asus:~# rm /home/kevin/DATA/KEVWELLS.COM/mysql.sql
root@asus:~#

then perform the imports:

root@asus:~# mysqlimport kevwells | gunzip /home/kevin/DATA/KEVWELLS.COM/kevwells.sql.gz
root@asus:~#

root@asus:~# mysqlimport mysql | gunzip /home/kevin/DATA/KEVWELLS.COM/mysql.sql.gz
root@asus:~#

After you import the mysql database onto the laptop, execute mysqladmin flush-privileges so the laptop mysql server reloads the grant table info:

mysqladmin flush-privileges

root@asus:~# mysqladmin flush-privileges
root@asus:~#

Some other commands:

root@asus:/home/kevin/DATA/KEVWELLS.COM# mysqladmin status
Uptime: 32382 Threads: 7 Questions: 7651 Slow queries: 0 Opens: 155 Flush tables: 1 Open tables: 148 Queries per second avg: 0.236
root@asus:/home/kevin/DATA/KEVWELLS.COM#

We can implement the automation in various ways.

One way would be to have two scripts, one on server which executes first via crontab, and the other on the laptop which executes later via crontab.

The server script would perform the mysqldump, the laptop script would do the mysqlimport.

Another way would be remote ssh – but we would have to give root ssh key permission on the server from the laptop for this. Then we would do everything from the one script on the laptop.

We will use this method to keep things simpler and means there is just one script that controls the entire process.

Set up the SSH for Remote Login without Password Prompt

First, copy the ssh key for root to geminivpn

use geminivpn and not gemini as wifi routers do not always permit outgoing ssh connections!

on gemini:

In the /etc/ssh/sshd_config you must have the directive:

PermitRootLogin yes

then restart sshd:

systemctl restart sshd

Next, assemble the script on the laptop:

Check Mysql Server Command Functionality

First let’s check the functionality on the server:

root@gemini:/etc/ssh# mysqladmin status
Uptime: 1458129 Threads: 7 Questions: 4569013 Slow queries: 0 Opens: 358 Flush tables: 1 Open tables: 337 Queries per second avg: 3.133
root@gemini:/etc/ssh#

and let’s see if we have our ssh command set correctly and if it works for mysqladmin commands:

root@asus:/home/kevin# ssh root@geminivpn “mysqladmin status”
Uptime: 1458134 Threads: 7 Questions: 4569014 Slow queries: 0 Opens: 358 Flush tables: 1 Open tables: 337 Queries per second avg: 3.133
root@asus:/home/kevin#

everything good.

Check the Functions.php:

there was also a problem with /var/www/wordpress# nano wp-content/themes/canvas/functions.php

This contained:

update_option( ‘siteurl’, ‘http://3.222.27.169’ );
update_option( ‘home’, ‘http://3.222.27.169’ );

These directives were changing the site name in the WordPress database back to kevwells.com from localhost.

So we have to make sure this is modified when we copied the WordPress files across. This is not dependent on the MySQL databases far as I can gather at this stage, it is solely in the functions.php of the canvas theme used for the site.

I have now commented this out on both kevwells.com and localhost.

Assemble the Script

So, next step is to assemble our script on the laptop:

root@asus:/usr/local/bin# cat kevwells.com_db_replication.sh

#!/bin/bash
# Script: /usr/local/bin/kevwells.com_db_replication.sh
# Date Created: 19.4.2022
# Last Modified 25.02.2024
# Author: Kevin Wells
# Purpose: export wordpress database for kevwells.com website from NFS share on kevinvm1vpn to laptop
# and modify database domain url from http://kevwells.com to http://localhost
# How called: via crontab. Can also be called manually
# Requires: mysql/mariadb, mysqladmin, mysql client, ssh, ssh keys installed on server for passwordless remote command execution
# /root/.my.cnf containing mysql password for root, gzip/gunzip on both machines
# Location: asus laptop /usr/local/bin

set -x

SERVER=”kevinvm1vpn”
WEBSITESOURCE=”/home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM”
LOCALSOURCE=”/media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM”

## not needed, doing it on the server locally:
## scp -r root@$SERVER:/var/www/wordpress/* root@$SERVER:/home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM/

# first, make sure the wordpress cache is flushed to disk:
ssh root@$SERVER “wp cache flush –path=/var/www/wordpress –allow-root “

# first delete the old .sql database file from last time:
##dont need to do this remote, is a problem with the rm command in any case, but delete it locally
##ssh root@$SERVER runuser -l kevin -c ‘rm -f $WEBSITESOURCE/all_databases.sql’
# delete locally:
rm -f $LOCALSOURCE/all_databases.sql

# create the database export from server kevwells.com:

#ssh -i /root/KevinVM1.pem ubuntu@kevinvm1vpn “mysqldump –all-databases > /home/kevin/DATAVOLUME/KEVWELLS.COM/all_databases.sql”

ssh root@$SERVER “mysqldump –all-databases > $WEBSITESOURCE/all_databases.sql”

# next import the databases into the laptop mysql:
# no username or password needed as this is defined in the /root/.my.cnf file:

mysql < $LOCALSOURCE/all_databases.sql

# should not be necessary, but to be on safe side:
chown -R mysql.mysql /var/lib/mysql/kevwells

/usr/bin/mysql -e “USE kevwells ; UPDATE wp_options SET option_value = replace(option_value, ‘http://kevwells.com’, ‘http://localhost’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’; UPDATE wp_posts SET guid = replace(guid, ‘https://kevwells.com’,’http://localhost’); UPDATE wp_posts SET post_content = replace(post_content, ‘https://kevwells.com’, ‘http://localhost’); UPDATE wp_postmeta SET meta_value = replace(meta_value,’https://kevwells.com’,’http://localhost’); FLUSH PRIVILEGES;”

/usr/local/bin/wp cache flush –path=/var/www/wordpress –allow-root

# then execute mysqladmin flush-privileges so the laptop mysql server reloads the grant table info:

mysqladmin flush-privileges

# finally, we can reset the permalinks to plain, we do this using the wordpress wp-cli tool:

#wp option get permalink_structure –path=/var/www/wordpress –allow-root
#this should return the following output – or similar tag category:
#/%postname%/

#wp option update permalink_structure ” ” –path=/var/www/wordpress –allow-root

/usr/bin/php -f /usr/local/bin/wp option update permalink_structure ‘ ‘ –path=/var/www/wordpress –allow-root

#this should return the following output:
#Success: Updated ‘permalink_structure’ option.

# wp option get permalink_structure –path=/var/www/wordpress –allow-root
#this should return the following output: (ie empty line)

# END OF SCRIPT

Testing the script with bash option set -x:

root@asus:/home/kevin/shellscripts# ./kevwells.com_db_replication.sh
+ SERVER=kevinvm1vpn
+ WEBSITESOURCE=/home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM
+ LOCALSOURCE=/media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM
+ ssh root@kevinvm1vpn ‘wp cache flush –path=/var/www/wordpress –allow-root ‘
PHP Warning: Undefined array key “HTTP_HOST” in /var/www/wordpress/wp-content/plugins/force-https-littlebizzy.disabled/core/redirect.php on line 91
Warning: Some code is trying to do a URL redirect. Backtrace:
#0 /var/www/wordpress/wp-includes/class-wp-hook.php(312): WP_CLI\Utils\wp_redirect_handler(‘…’)
#1 /var/www/wordpress/wp-includes/plugin.php(205): WP_Hook->apply_filters(‘…’, Array)
#2 /var/www/wordpress/wp-includes/pluggable.php(1396): apply_filters(‘…’, ‘…’, 301)
#3 /var/www/wordpress/wp-content/plugins/force-https-littlebizzy.disabled/core/redirect.php(91): wp_redirect(‘…’, 301)
#4 /var/www/wordpress/wp-content/plugins/force-https-littlebizzy.disabled/core/redirect.php(68): FHTTPS_Core_Redirect->redirect()
#5 /var/www/wordpress/wp-includes/class-wp-hook.php(310): FHTTPS_Core_Redirect->start(”)
#6 /var/www/wordpress/wp-includes/class-wp-hook.php(334): WP_Hook->apply_filters(NULL, Array)
#7 /var/www/wordpress/wp-includes/plugin.php(517): WP_Hook->do_action(Array)
#8 /var/www/wordpress/wp-settings.php(495): do_action(‘…’)
#9 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(1317): require(‘…’)
#10 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(1235): WP_CLI\Runner->load_wordpress()
#11 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/WP_CLI/Bootstrap/LaunchRunner.php(28): WP_CLI\Runner->start()
#12 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/bootstrap.php(78): WP_CLI\Bootstrap\LaunchRunner->process(Object(WP_CLI\Bootstrap\BootstrapState))
#13 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/wp-cli.php(27): WP_CLI\bootstrap()
#14 phar:///usr/local/bin/wp/php/boot-phar.php(11): include(‘…’)
#15 /usr/local/bin/wp(4): include(‘…’)
+ rm -f /media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM/all_databases.sql
+ ssh root@kevinvm1vpn ‘mysqldump –all-databases > /home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM/all_databases.sql’
+ mysql
./kevwells.com_db_replication.sh: line 50: /media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM/all_databases.sql: No such file or directory
+ chown -R mysql.mysql /var/lib/mysql/kevwells
+ /usr/bin/mysql -e ‘USE kevwells ; UPDATE wp_options SET option_value = replace(option_value, ‘\”http://kevwells.com’\”, ‘\”http://localhost’\”) WHERE option_name = ‘\”home’\” OR option_name = ‘\”siteurl’\”; UPDATE wp_posts SET guid = replace(guid, ‘\”https://kevwells.com’\”,’\”http://localhost’\”); UPDATE wp_posts SET post_content = replace(post_content, ‘\”https://kevwells.com’\”, ‘\”http://localhost’\”); UPDATE wp_postmeta SET meta_value = replace(meta_value,’\”https://kevwells.com’\”,’\”http://localhost’\”); FLUSH PRIVILEGES;’
+ /usr/local/bin/wp cache flush –path=/var/www/wordpress –allow-root
Success: The cache was flushed.
+ mysqladmin flush-privileges
+ /usr/bin/php -f /usr/local/bin/wp option update permalink_structure ‘ ‘ –path=/var/www/wordpress –allow-root
Success: Value passed for ‘permalink_structure’ option is unchanged.
root@asus:/home/kevin/shellscripts#

root@asus:/usr/local/bin#

Make sure the script is executable!

root@asus:/usr/local/bin# chmod 750 kevwells.com_db_replication.sh
root@asus:/usr/local/bin# ls -l kevwells.com_db_replication.sh
-rwxr-x— 1 root root 2007 Apr 20 10:57 kevwells.com_db_replication.sh
root@asus:/usr/local/bin#

And finally, schedule the script to run using crontab on the laptop:

9 2 * * * /usr/local/bin/kevwells.com_db_replication.sh

Additional Notes on the Replication Process

Some WordPress plugins can interfere with the replication and correct rendition of the website from https://kevwells.com to http://localhost.

In particular, check that:

Really Simple SSL is “deactivated, but still using https” (select this option in the plugin settings in the WP Dashboard).

Also I have disabled Permalinks Moved Permanently plugin.

Provided these plugins are disabled and the replication script runs correctly with the MySQL DB Update URL commands executed to change siteurl links from https://kevwells.com to http://localhost then the replication should be error-free and there is no need for the permalinks to be saved in the WP Dashboard as is otherwise usually the case with WordPress site transfers and domain name changes.

Problems Starting Mariadb/Mysql on Ubuntu 20.10 After Installing

By Admin on March 15, 2022 in

After installing Mariadb/Mysql on Linux Ubuntu 20.10 I had the following error when trying to start mariadb server:

root@asus:/home/kevin/Downloads# systemctl status mariadb

https://mariadb.com/kb/en/library/systemd/
Process: 57943 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 57944 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 57946 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_>
Process: 57975 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=7)
Main PID: 57975 (code=exited, status=7)
Status: “MariaDB server is down”

Mär 15 12:43:43 asus systemd[1]: Starting MariaDB 10.3.34 database server…
Mär 15 12:43:43 asus mysqld[57975]: 2022-03-15 12:43:43 0 [Note] /usr/sbin/mysqld (mysqld 10.3.34-MariaDB-0ubuntu0.20.04.1) starting as process 57975 …
Mär 15 12:43:45 asus systemd[1]: mariadb.service: Main process exited, code=exited, status=7/NOTRUNNING
Mär 15 12:43:45 asus systemd[1]: mariadb.service: Failed with result ‘exit-code’.
Mär 15 12:43:45 asus systemd[1]: Failed to start MariaDB 10.3.34 database server.
lines 5-19/19 (END)

This then leads to a ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.s

However, this latter error can have various potential causes, but its occurrance in this particular case is because the mysqld service has been unable to start.

This in turn was due to corruption of the Mysql server system and configuration due to earlier installs and deinstalls. The problems may also have been compounded by the fact that the system on which mysql was being installed was ubuntu version 20.10 and not 20.04 LTS. The latter is recommended, the former (20.10 is not).

The solution was to first do a comprehensive complete deinstall and cleanup of the mysql/mariadb configuration and then to start again from scratch with a fresh installation of mariadb-server.

However, before I resorted to this action as part of my troubleshooting activity, I tried out various other suggested solutions, some of these I have documented below.

Check error logs

Check that the mysql error log has been explicitly written to another location. This is often done by changing the datadir or log_error system variables in an option file.A quick way to get the values of these system variables is to do:

mysqld –help –verbose | grep ‘log-error’ | tail -1
mysqld –help –verbose | grep ‘datadir’ | tail -1

root@asus:/var/lib# mysqld –help –verbose | grep ‘log-error’ | tail -1
2022-03-15 18:53:51 0 [Note] Plugin ‘FEEDBACK’ is disabled.
2022-03-15 18:53:51 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
2022-03-15 18:53:51 0 [ERROR] mysqld: unknown variable ‘mysqlx-bind-address=127.0.0.1’
2022-03-15 18:53:51 0 [ERROR] Aborting

log-error /var/log/mysql/error.log
root@asus:/var/lib# mysqld
2022-03-15 18:54:06 0 [Note] mysqld (mysqld 10.3.34-MariaDB-0ubuntu0.20.04.1) starting as process 76968 …
^C
root@asus:/var/lib#

Check configuration variables

You can check which configuration options MariaDB server will use from its option files by doing:

mysqld –print-defaults
You can also check by executing the following command:

my_print_defaults –mysqld

root@asus:/var/lib# mysqld –print-defaults
mysqld would have been started with the following arguments:
–user=mysql –bind-address=127.0.0.1 –mysqlx-bind-address=127.0.0.1 –key_buffer_size=16M –myisam-recover-options=BACKUP –log_error=/var/log/mysql/error.log –max_binlog_size=100M
root@asus:/var/lib#

root@asus:~# my_print_defaults –mysqld
–user=mysql
–bind-address=127.0.0.1
–mysqlx-bind-address=127.0.0.1
–key_buffer_size=16M
–myisam-recover-options=BACKUP
–log_error=/var/log/mysql/error.log
–max_binlog_size=100M
root@asus:~#

It is possible to see errors similar to the following:

System error 1067 has occurred.
Fatal error: Can’t open privilege tables: Table ‘mysql.host’ doesn’t exist

If errors like this occur, then critical system tables are either missing or are in the wrong location.

The above error can occur after an upgrade if the option files set the basedir or datadir to a non-standard location, but the new server is using the default location.

So make sure that the basedir and datadir variables are correctly set.

root@asus:/var/lib#
root@asus:/var/lib# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code.
See “systemctl status mariadb.service” and “journalctl -xe” for details.

root@asus:/var/lib# systemctl status mariadb

● mariadb.service – MariaDB 10.3.34 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: failed (Result: exit-code) since Tue 2022-03-15 18:50:37 GMT; 4s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 76564 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 76565 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 76567 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -e>
Process: 76596 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=7)
Main PID: 76596 (code=exited, status=7)
Status: “MariaDB server is down”

Mär 15 18:50:35 asus systemd[1]: Starting MariaDB 10.3.34 database server…
Mär 15 18:50:35 asus mysqld[76596]: 2022-03-15 18:50:35 0 [Note] /usr/sbin/mysqld (mysqld 10.3.34-MariaDB-0ubuntu0.20.04.1) starting as process 7>
Mär 15 18:50:37 asus systemd[1]: mariadb.service: Main process exited, code=exited, status=7/NOTRUNNING
Mär 15 18:50:37 asus systemd[1]: mariadb.service: Failed with result ‘exit-code’.
Mär 15 18:50:37 asus systemd[1]: Failed to start MariaDB 10.3.34 database server.
lines 3-19/19

Check the mysql user exists

Does the mysql user exist? yes…

root@asus:~# cat /etc/passwd | grep mysql
mysql:x:133:144:MySQL Server,,,:/nonexistent:/bin/false
root@asus:~#

Check the output of mysql_install_db

The error message “Installation of system tables failed” sent by mysql_install_db is revealing.

Examine the logs in /var/lib/mysql for more information.

See below.

root@asus:~# mysql_install_db
Installing MariaDB/MySQL system tables in ‘/var/lib/mysql’ …

Installation of system tables failed! Examine the logs in
/var/lib/mysql for more information.

The problem could be conflicting information in an external
my.cnf files. You can ignore these by doing:

shell> /usr/bin/mysql_install_db –defaults-file=~/.my.cnf

You can also try to start the mysqld daemon with:

shell> /usr/sbin/mysqld –skip-grant-tables –general-log &

and use the command line tool /usr/bin/mysql

to connect to the mysql database and look at the grant tables:

shell> /usr/bin/mysql -u root mysql
mysql> show tables;

Try ‘mysqld –help’ if you have problems with paths. Using
–general-log gives you a log in /var/lib/mysql that may be helpful.

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
You can find the latest source at https://downloads.mariadb.org and
the maria-discuss email list at https://launchpad.net/~maria-discuss

Please check all of the above before submitting a bug report
at http://mariadb.org/jira

root@asus:~#

I also tried the following, but result was the same:

root@asus:~# mysql_install_db –user=mysql –ldata=/var/lib/mysql/ –basedir=/usr
Installing MariaDB/MySQL system tables in ‘/var/lib/mysql/’ …

Installation of system tables failed! Examine the logs in
/var/lib/mysql/ for more information.

Deinstall Mariadb/Mysql

At this point I decided to do a complete deinstall and clean-up and then a fresh new reinstall of mariadb-server:

apt-get remove –purge mysql*
apt-get remove –purge mariadb*

then do:

apt-get remove –purge mysql*

root@asus:~# dpkg -l | grep mysql
ii libdbd-mysql-perl:amd64 4.050-3 amd64 Perl5 database interface to the MariaDB/MySQL database
ii libmysqlclient21:amd64 8.0.28-0ubuntu0.20.04.3 amd64 MySQL database client library
ii mysql-common 1:10.5.15+maria~focal all MariaDB database common files (e.g. /etc/mysql/my.cnf)
rc mysql-server-8.0 8.0.28-0ubuntu0.20.04.3 amd64 MySQL database server binaries and system database setup
ii php-mysql 2:7.4+76 all MySQL module for PHP [default]
ii php7.4-mysql 7.4.9-1ubuntu1.2 amd64 MySQL module for PHP
root@asus:~#

remove all these packages…

root@asus:~# apt-get remove –purge php-mysql

apt-get remove –purge mariadb*
apt-get remove –purge libdbd-mysql-perl:amd64
apt-get remove –purge mysql*
apt-get remove –purge php-mysql
apt-get remove –purge php7.4-mysql

then do a thorough clean up….

root@asus:~# rm -rf /etc/mysql /var/lib/mysql
root@asus:~# apt-get autoremove
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages will be REMOVED
dbconfig-common dbconfig-no-thanks galera-3 icc-profiles-free libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbi-perl libfcgi-perl
libhtml-template-perl libjs-openlayers libjs-sphinxdoc libjs-underscore libreadline5 libterm-readkey-perl php-bz2 php-google-recaptcha
php-phpmyadmin-motranslator php-phpmyadmin-shapefile php-phpmyadmin-sql-parser php-phpseclib php-psr-cache php-psr-container php-psr-log php-symfony-cache
php-symfony-cache-contracts php-symfony-expression-language php-symfony-service-contracts php-symfony-var-exporter php-tcpdf php-twig php-twig-extensions
php7.4-bz2
0 to upgrade, 0 to newly install, 33 to remove and 0 not to upgrade.
After this operation, 46,9 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database … 253422 files and directories currently installed.)
Removing dbconfig-no-thanks (2.0.14) …
Removing dbconfig-common (2.0.14) …
Removing galera-3 (25.3.30-1) …
Removing php-tcpdf (6.3.5+dfsg1-1) …
Removing icc-profiles-free (2.0.1+dfsg-1) …
Removing libcgi-fast-perl (1:2.15-1) …
Removing libhtml-template-perl (2.97-1) …
Removing libcgi-pm-perl (4.50-1) …
Removing libconfig-inifiles-perl (3.000003-1) …
Removing libdbi-perl:amd64 (1.643-2) …
Removing libfcgi-perl (0.79-1) …
Removing libjs-openlayers (2.13.1+ds2-8) …
Removing libjs-sphinxdoc (3.2.1-1) …
Removing libjs-underscore (1.9.1~dfsg-1ubuntu0.20.10.1) …
Removing libreadline5:amd64 (5.2+dfsg-3build3) …
Removing libterm-readkey-perl (2.38-1build1) …
Removing php-bz2 (2:7.4+76) …
Removing php-google-recaptcha (1.2.4-1) …
Removing php-phpmyadmin-motranslator (5.0.0-2) …
Removing php-phpmyadmin-shapefile (2.1-4) …
Removing php-phpmyadmin-sql-parser (4.6.1-2) …
Removing php-phpseclib (2.0.28-1) …
Removing php-symfony-expression-language (4.4.14+dfsg-1) …
Removing php-symfony-cache (4.4.14+dfsg-1) …
Removing php-symfony-cache-contracts (1.1.9-1) …
Removing php-psr-cache (1.0.1-2) …
Removing php-symfony-service-contracts (1.1.9-1) …
Removing php-psr-container (1.0.0-2) …
Removing php-psr-log (1.1.3-1) …
Removing php-symfony-var-exporter (4.4.14+dfsg-1) …
Removing php-twig-extensions (1.5.4-2) …
Removing php-twig (2.13.0-1) …
Removing php7.4-bz2 (7.4.9-1ubuntu1.2) …
Processing triggers for doc-base (0.10.9) …
Processing 3 removed doc-base files…
Processing triggers for libc-bin (2.32-0ubuntu3) …
Processing triggers for man-db (2.9.3-2) …
Processing triggers for libapache2-mod-php7.4 (7.4.9-1ubuntu1.2) …
root@asus:~# apt-get autoclean
Reading package lists… Done
Building dependency tree
Reading state information… Done
Del mysql-client-core-8.0 8.0.27-0ubuntu0.20.04.1 [4.423 kB]
Del libapache2-mod-php7.4 7.4.3-4ubuntu2.8 [1.364 kB]
Del python-setuptools 44.0.0-2 [330 kB]
Del libapache2-mod-php 2:7.4+75 [2.836 B]
Del php7.4-mysql 7.4.3-4ubuntu2.8 [121 kB]
Del chromium-codecs-ffmpeg-extra 1:85.0.4183.83-0ubuntu0.20.04.2 [2.968 B]
Del mysql-client-8.0 8.0.27-0ubuntu0.20.04.1 [22,0 kB]
Del python-pkg-resources 44.0.0-2 [129 kB]
Del php7.4-odbc 7.4.3-4ubuntu2.8 [30,6 kB]
Del dbconfig-no-thanks 2.0.13 [1.396 B]
root@asus:~#

Reinstall mariadb-server

Then we can start from scratch and reinstall mariadb-server:

root@asus:~# apt install mariadb-server
Reading package lists… Done
Building dependency tree
Reading state information… Done

The following additional packages will be installed:
galera-3 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl libfcgi-perl libhtml-template-perl libmysqlclient21
libreadline5 libterm-readkey-perl mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 mysql-common

Suggested packages:
libmldbm-perl libnet-daemon-perl libsql-statement-perl libipc-sharedcache-perl mailx mariadb-test tinyca
The following NEW packages will be installed
galera-3 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl libfcgi-perl libhtml-template-perl libmysqlclient21
libreadline5 libterm-readkey-perl mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server mariadb-server-10.3 mariadb-server-core-10.3
mysql-common
0 to upgrade, 18 to newly install, 0 to remove and 0 not to upgrade.
Need to get 20,4 MB of archives.
After this operation, 170 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

then do…

root@asus:~# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we’ll need the current
password for the root user. If you’ve just installed MariaDB, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer ‘n’.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
… Success!

Normally, root should only be allowed to connect from ‘localhost’. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
… Success!

By default, MariaDB comes with a database named ‘test’ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
… Success!

Cleaning up…

All done! If you’ve completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
root@asus:~#

mariadb is now working

root@asus:~# systemctl status mariadb
● mariadb.service – MariaDB 10.3.29 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Tue 2022-03-15 19:14:01 GMT; 1min 59s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 84344 (mysqld)
Status: “Taking your SQL requests now…”
Tasks: 31 (limit: 21459)
Memory: 70.2M
CGroup: /system.slice/mariadb.service
└─84344 /usr/sbin/mysqld

Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: information_schema
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: mysql
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: performance_schema
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: Phase 6/7: Checking and upgrading tables
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: Processing databases
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: information_schema
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: performance_schema
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: Phase 7/7: Running ‘FLUSH PRIVILEGES’
Mär 15 19:14:02 asus /etc/mysql/debian-start[84384]: OK
Mär 15 19:14:02 asus debian-start[84453]: WARNING: tempfile is deprecated; consider using mktemp instead.
root@asus:~#

Further Troubleshooting Advice at mariadb.com

See the official MariaDB Troubleshooting page at https://mariadb.com/kb/en/what-to-do-if-mariadb-doesnt-start/

mysql_install_db page at https://mariadb.com/kb/en/mysql_install_db/

LAMP Server Migration

By Admin on March 9, 2022 in

The following notes document the migration of a virtual Linux machine running LAMP – Linux Ubuntu, Apache webserver, MySql/MariaDB & PHP, from an existing machine to a new machine and the procedural steps involved.

Both old and new machines also run ssh, nfs-server shares, sslh port multiplexer, nextcloud, checkmk, wordpress, phpmyadmin, together with Linux shell script crontab-run backup routines to backup data to an external cloud provider.

The scripts and crontab entries had to be migrated, installed and reconfigured on the new server.

This is an overview of the steps involved. For more detailed discussion of the specific actions, for example covering mysql, apache, sslh multiplexer, SSL certificates, see the appropriate relevant subject sections of this IT Knowledge Base.

Initial Preparation

First task is to create the new virtual server with Linux Ubuntu OS. This has a new localhost name and new unique publicly accessible IP address.

After the migration, the old original server machine is then switched off and deleted from the virtual server provider environment. The new machine name is changed to the old machine name, taking the place of the old machine online.

After installing the basic operating system Linux Ubuntu version 20.04 LTS, we migrate existing /usr/local/bin scripts from the old to the new machine, together with any binaries and other files contained in this directory.

Later we will configure crontab on the new machine for backups and mysql nextcloud nightly database scan and updates for newly added data files (and deleted/modified data files).

We give the new machine the temporary localhost name of “gemininew”.

When the migration is complete we will change the name to “gemini”, taking the place of the old machine.

The /etc/hosts file entries on the server and on all connecting hosts will then need to be modified so that the entry for gemini points to the NEW IP address and no longer to the old one.

In the meantime, for ease of logging in, we have made an entry on the old gemini server /etc/hosts and on our connecting hosts as follows:

gemininew <the new IP address for gemini>

while keeping the old gemini IP entry intact for now. Later on towards the end of the migration this old entry will be deleted from the file.

Modify DNS Record

We need to modify the DNS record for the domain kevwells.com for the new IP address of the new machine to replace the old one.

This is done on the DNS server of our virtual server provider.

Install Apache

Install apache2 on the new machine and define the virtual hosts that are required.

Next, for apache

To allow .htaccess files, we need to set the AllowOverride directive within a Directory block pointing to our document root. Add the following block of text inside the VirtualHost block in your configuration file, making sure to use the correct web root directory.

In my case it is:

root@gemininew:/etc/apache2/sites-available# nano 000-default.conf

add this under

<Directory /var/www/wordpress/>
AllowOverride All
</Directory>

By default the use of .htaccess files is disabled. WordPress and many WordPress plugins use these files extensively for in-directory tweaks to the web server’s behavior.

Next, we can enable mod_rewrite so that we can utilize the WordPress permalink feature:

a2enmod rewrite

root@gemininew:/etc/apache2/sites-available# a2enmod rewrite
Enabling module rewrite.
To activate the new configuration, you need to run:
systemctl restart apache2
root@gemininew:/etc/apache2/sites-available# systemctl restart apache2
root@gemininew:/etc/apache2/sites-available#

This allows you to have more human-readable permalinks to your posts, like the following two examples:

http://example.com/2012/post-name/
http://example.com/2012/12/30/post-name

The a2enmod command calls a script that enables the specified module within the Apache configuration.

next, check to make sure we haven’t made any syntax errors by running the following test.

apache2ctl configtest

root@gemininew:/etc/apache2/sites-available# apache2ctl configtest
AH00558: apache2: Could not reliably determine the server’s fully qualified domain name, using 127.0.1.1. Set the ‘ServerName’ directive globally to suppress this message
Syntax OK
root@gemininew:/etc/apache2/sites-available#

Ensure apache is running with

a2ensite <sites-enabled_configfile>

systemctl enable apache2
systemctl start apache2

Install Mysql/MariaDB

apt install mariadb-server

During the installation you’ll be requested to set a mysql server admin user password.

If the secure installation utility does not run automatically during the installation process, then you can run it explicitly:

mysql_secure_installation utility

Next install the php-mysql module:

root@gemininew:~# apt-get install php-mysql
Reading package lists… Done
Building dependency tree
Reading state information… Done
php-mysql is already the newest version (2:7.4+75).

Enable the mysql service and start it:

root@gemini:/var/lib/mysql# systemctl start mysql
root@gemini:/var/lib/mysql# systemctl status mysql
● mysql.service – LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql; generated)
Active: active (running) since Thu 2022-03-03 13:24:50 UTC; 4s ago
Docs: man:systemd-sysv-generator(8)
Process: 4328 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
Tasks: 33 (limit: 2274)
Memory: 64.0M
CGroup: /system.slice/mysql.service
├─4367 /bin/sh /usr/bin/mysqld_safe
├─4483 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib/x86_64-linux-gnu/mariadb19/plugin –user=mysql –skip-log-error –pid-file=/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock
└─4484 logger -t mysqld -p daemon error

Mar 03 13:24:50 gemini /etc/mysql/debian-start[4538]: Upgrading MySQL tables if necessary.
Mar 03 13:24:50 gemini mysqld[4484]: 2022-03-03 13:24:50 10 [Warning] Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
Mar 03 13:24:50 gemini /etc/mysql/debian-start[4541]: Looking for ‘mysql’ as: /usr/bin/mysql
Mar 03 13:24:50 gemini /etc/mysql/debian-start[4541]: Reading datadir from the MariaDB server failed. Got the following error when executing the ‘mysql’ command line client
Mar 03 13:24:50 gemini /etc/mysql/debian-start[4541]: ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
Mar 03 13:24:50 gemini /etc/mysql/debian-start[4541]: FATAL ERROR: Upgrade failed
Mar 03 13:24:50 gemini /etc/mysql/debian-start[4546]: Checking for insecure root accounts.
Mar 03 13:24:50 gemini mysqld[4484]: 2022-03-03 13:24:50 11 [Warning] Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
Mar 03 13:24:50 gemini mysql[4549]: ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
Mar 03 13:24:51 gemini mysqld[4484]: 2022-03-03 13:24:51 12 [Warning] Access denied for user ‘wordpressuser’@’localhost’ (using password: YES)
lines 3-22/22 (END)

and we now have the socket for mysqld:

root@gemini:/run/mysqld# ls -lias
total 4
586 0 drwxr-xr-x 2 mysql root 80 Mar 3 13:24 .
2 0 drwxr-xr-x 29 root root 880 Mar 3 13:20 ..
678 4 -rw-rw—- 1 mysql mysql 5 Mar 3 13:24 mysqld.pid
677 0 srwxrwxrwx 1 mysql mysql 0 Mar 3 13:24 mysqld.sock
root@gemini:/run/mysqld#

Test the server:

root@gemininew:~#
root@gemininew:~#
root@gemininew:~#
root@gemininew:~# mysqladmin -p -u root version
Enter password:
mysqladmin Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 8.0.28-0ubuntu0.20.04.3
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 6 min 48 sec

Threads: 2 Questions: 14 Slow queries: 0 Opens: 130 Flush tables: 3 Open tables: 49 Queries per second avg: 0.034
root@gemininew:~#

Run the mysql secure installation utility

/usr/bin/mysql_secure_installation

This will also set the root password for mysql

You have to set the root password here:

root@gemini:/run/mysqld# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

Enter current password for root (enter for none): ***NOTE**** if you try to enter the password for root here it gives an error at this stage:!!! see below
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
Enter current password for root (enter for none): ****PRESS ENTER***- you set the root password further below!***
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
… Success!

Remove anonymous users? [Y/n]
… Success!

Normally, root should only be allowed to connect from ‘localhost’. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
… Success!

By default, MariaDB comes with a database named ‘test’ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
… Success!

Cleaning up…

All done! If you’ve completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
root@gemini:/run/mysqld#

root@gemini:~# mysqldump -u root -p –all-databases > allgeminidatabases-backup.sql
Enter password:
root@gemini:~#

to restore

mysql -u root -p < allgeminidatabases-backup.sql

root@gemininew:~# mysql -u root -p < allgeminidatabases-backup.sql
Enter password:

Set up a Mysql Database for WordPress

Create an exclusive database for WordPress to control. This can have any name, but we will use the standard name wordpress for this example.

Enter the mysql admin client, using:

root@gemininew:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 319
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

then do the following:

MariaDB [(none)]> CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use wordpress;
Database changed
MariaDB [wordpress]>

MariaDB [(none)]> CREATE USER ‘wordpressuser’@localhost IDENTIFIED BY ‘*********’;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT ALL ON wordpress.* TO ‘wordpressuser’@’localhost’;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

you can now exit mariadb:

MariaDB [(none)]> exit
Bye
root@gemininew:/#

Install WordPress

next, download and install wordpress:

you can do a wget from https://wordpress.org/latest.tar.gz

and then unpack at /var/www/wordpress

you can then create a /var/www/html -> /var/www/wordpress link

and set permissions as follows:

chown -R www-data:www-data /var/www/wordpress

Next we’ll run two find commands to set the correct permissions on the WordPress directories and files:

root@gemininew:~# find /var/www/wordpress/ -type d -exec chmod 750 {} \;
root@gemininew:~# find /var/www/wordpress/ -type f -exec chmod 640 {} \;
root@gemininew:~#

Next, modify some of the database connection settings, adjusting database name, the database user, and the associated password you configured within MySQL.

Also set the method WordPress should use to write to the filesystem, set this filesystem method to “direct”.

If we dont do this it could mean WordPress prompting for FTP credentials for some actions:

/var/www/wordpress/wp-config.php

. . .

// ** MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘wordpress’ );

/** MySQL database username */
define( ‘DB_USER’, ‘wordpressuser’ );

/** MySQL database password */
define( ‘DB_PASSWORD’, ‘***password commented out***’ );

/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’ );

/** Database Charset to use in creating database tables. */
define( ‘DB_CHARSET’, ‘utf8’ );

/** The Database Collate type. Don’t change this if in doubt. */
define( ‘DB_COLLATE’, ” );

. . .

define(‘FS_METHOD’, ‘direct’);

NOTE: we substitute the above with our own password as created for the wordpressuser in mariadb! (not shown here for security reasons)

next, in web-browser, open the http://servername/wp-admin link

and the wordpress admin dashboard initial setup page should appear.

Firewalling for apache

The following ports need to be opened, port 443 and 80.

root@gemini:/etc/apache2# ufw status
Status: active

To Action From
— —— —-
22 ALLOW Anywhere
80 ALLOW Anywhere
443 ALLOW Anywhere
3306 ALLOW Anywhere
9993 ALLOW Anywhere
2049 ALLOW Anywhere
22 (v6) ALLOW Anywhere (v6)
80 (v6) ALLOW Anywhere (v6)
443 (v6) ALLOW Anywhere (v6)
3306 (v6) ALLOW Anywhere (v6)
9993 (v6) ALLOW Anywhere (v6)
2049 (v6) ALLOW Anywhere (v6)

root@gemini:/etc/apache2#

DO NOT open port 444!

NOTE that port 444 is NOT opened on the firewall – this is used internally by apache ie behind the firewall, between apache and sslh. It should not be accessible from outside.

MySql/MariaDB has to be installed and up and running.

Check it is enabled and running using

systemctl enable mysql
systemctl start mysql
systemctl status mysql

During installation, all being well, mysql/mariadb will start automatically, unless the port used by mysql is already in use.

Install SSL Certificates

SSL/TLS https certificates supplied by Lets Encrypt

The apache webserver needs to be running on port 80 to carry out registration to obtain the certificates for each virtual host.

Certificates are required for all virtual websites operating on the IP with https:

kevwells.com, nextcloud.kevwells.com

Use the certbot command-line utility to request the SSL certificates for the websites. You need to run certbot in turn for each virtual host website you are operating on https.

Make sure /etc/apache2/ports.conf is set to listen on port 80 and 443 initially for ssl.

After obtaining the certificates, and since we are using the sslh multiplexer, you need to change ports.conf all references to Listen 443 port to Listen 444 as this is the port used for sslh multiplexer.

The sslh multiplexer separates out incoming traffic on 443 to 444 for Apache and 22 for ssh.

Install wordpress under /var/www/wordpress

then create symbolic links as follows:

/var/www/html -> /var/www/kevwells.com

/var/www/kevwells.com -> /var/www/wordpress

Configure the wp-config.php file:

root@gemini:/var/www/wordpress#
root@gemini:/var/www/wordpress# cat wp-config.php
<?php
//Begin Really Simple SSL session cookie settings
@ini_set(‘session.cookie_httponly’, true);
@ini_set(‘session.cookie_secure’, true);
@ini_set(‘session.use_only_cookies’, true);
//END Really Simple SSL

/**
* The base configuration for WordPress
*
* The wp-config.php creation script uses this file during the installation.
* You don’t have to use the web site, you can copy this file to “wp-config.php”
* and fill in the values.
*
* This file contains the following configurations:
*
* * MySQL settings
* * Secret keys
* * Database table prefix
* * ABSPATH
*
* @link https://wordpress.org/support/article/editing-wp-config-php/
*
* @package WordPress
*/

!!!!! DB_NAME and other variables have been omitted here for security reasons!!!!

// ** MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘***’ );

/** MySQL database username */
define( ‘DB_USER’, ‘***’ );

/** MySQL database password */
define( ‘DB_PASSWORD’, ‘***’ );

/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’ );

/** Database charset to use in creating database tables. */
define( ‘DB_CHARSET’, ‘utf8mb4’ );

/** The database collate type. Don’t change this if in doubt. */
define( ‘DB_COLLATE’, ” );

/**#@+
* Authentication unique keys and salts.
*
* Change these to different unique phrases! You can generate these using
* the {@link https://api.wordpress.org/secret-key/1.1/salt/ WordPress.org secret-key service}.
*
* You can change these at any point in time to invalidate all existing cookies.
* This will force all users to have to log in again.
*
* @since 2.6.0
*/
define( ‘AUTH_KEY’, ‘;
…
…
…

!!!!!! NOT DISPLAYED HERE FOR SECURITY REASONS!!!!!!!!!!!!!!
…
…
…

/**
* WordPress database table prefix.
*
* You can have multiple installations in one database if you give each
* a unique prefix. Only numbers, letters, and underscores please!
*/
$table_prefix = ‘wp_’;

/**
* For developers: WordPress debugging mode.
*
* Change this to true to enable the display of notices during development.
* It is strongly recommended that plugin and theme developers use WP_DEBUG
* in their development environments.
*
* For information on other constants that can be used for debugging,
* visit the documentation.
*
* @link https://wordpress.org/support/article/debugging-in-wordpress/
*/
define( ‘WP_DEBUG’, false );

/* define( ‘WP_DEBUG’, false ); */

/* Add any custom values between this line and the “stop editing” line. */

/* That’s all, stop editing! Happy publishing. */

/** Absolute path to the WordPress directory. */
if ( ! defined( ‘ABSPATH’ ) ) {
define( ‘ABSPATH’, __DIR__ . ‘/’ );
}

/** Sets up WordPress vars and included files. */
require_once ABSPATH . ‘wp-settings.php’;

/* define(‘WP_ALLOW_REPAIR’, true); */

define(‘FS_METHOD’, ‘direct’);

@ini_set( ‘upload_max_filesize’ , ‘12800M’ );
@ini_set( ‘post_max_size’, ‘12800M’);
@ini_set( ‘memory_limit’, ‘256M’ );
@ini_set( ‘max_execution_time’, ‘300’ );
@ini_set( ‘max_input_time’, ‘300’ );

define(‘FORCE_SSL_ADMIN’, true);

define( ‘WP_HOME’, ‘http://3.222.27.169’ );
define( ‘WP_SITEURL’, ‘http://3.222.27.169’ );

root@gemini:/var/www/wordpress#

Install Nextcloud

How to configure Nextcloud

Nextcloud has to be downloaded and installed under /var/www/nextcloud

enter a database name for nextcloud – usually “nextcloud”

port is localhost:3306

a database username and a password.

this gets saved in the nextcloud config file under /var/www/nextcloud/config/config.php

Create a virtual host for nextcloud in /etc/apache2/sites-available/<apacheconfigfile>.conf

You will also need to define the virtual host ie nextcloud for http port 80 in order to obtain the SSL/TLS certificate from Lets Encrypt.

Once this is obtained and installed, you can then delete this port 80 http virtual host for nextcloud.

Switch WordPress from http to https

Once you have obtained and installed the SSL certificates for the virtual hosts, you can switch wordpress to https.

In practice, the switch from http to https for wordpress was complicated.

There are two aspects to this: apache, and wordpress.

The apache virtual host definitions are relatively straightforward. See the apache section above.

WordPress is a little more involved and complicated.

First, you need to install the wordpress plugin Really Simple SSL (if only it were “really simple” in practice).

It was difficult trying to switch the wordpress site name in the wordpress dashboard from http to https.

Also you need to save permalinks twice, using the Permalinks plugin which must first be installed.

Trying to change the wordpress site definitions in the wordpress dashboard under settings – general, did not work, wordpress kept changing them back.

I also tried changing the site names using the mysql admin client and sql commands, but these too would get changed back!

Finally, I had to add the two definitions to the wp-config.php and also the FORCE_SSL_ADMIN directive:

define(‘FORCE_SSL_ADMIN’, true);

define( ‘WP_HOME’, ‘http://3.222.27.169’ );
define( ‘WP_SITEURL’, ‘http://3.222.27.169’ );

Then after numerous attempts, it finally accepted the change from http to https.

You also have to save the links again in WordPress Permalinks plugin after changing from http to https:

select plain, then back to custom, then plain, then finally set to custom.

The links should then function correctly.

Also in this case, don’t forget we are using sslh multiplexer, so the virtual host definitions for the ports need to be 444 for https and not 443.

Make sure these are set in the sites-enabled config file as well as in the apache ports.conf file! (Lets Encrypt changes then to 443 – you have to change them back manually!).

Then make sure sslh is running correctly, listening on 443, and passing ssh traffic to 22 sshd, and https traffic on to 444 apache.

Firewalling – Portmapper

Make sure port 111 for nfs portmapper is closed.

Portmapper is a service usually used with NFS. When this is not properly firewalled, it can be abused to conduct DDOS attacks.

All portmapper services should therefore be behind a firewall and restricted to IPs that need to contact them.

For Linux machines, add firewall rules to block port 111 on both UDP and TCP as follows:

iptables -I INPUT 1 -m tcp -p tcp –dport 111 -j DROP
iptables -I INPUT 1 -m udp -p udp –dport 111 -j DROP

Alternatively, set using the ufw firewall utility in ubuntu with:

root@gemini:# ufw deny 111
Rule updated
Rule updated (v6)
root@gemini:#
root@gemini:~# ufw status
Status: active

To Action From
— —— —-
22 ALLOW Anywhere
80 ALLOW Anywhere
443 ALLOW Anywhere
3306 ALLOW Anywhere
9993 ALLOW Anywhere
111 DENY Anywhere
2049 ALLOW Anywhere
22 (v6) ALLOW Anywhere (v6)
80 (v6) ALLOW Anywhere (v6)
443 (v6) ALLOW Anywhere (v6)
3306 (v6) ALLOW Anywhere (v6)
9993 (v6) ALLOW Anywhere (v6)
111 (v6) DENY Anywhere (v6)
2049 (v6) ALLOW Anywhere (v6)

root@gemini:~#

Modify hosts files

The hosts file on the new server and all connecting clients must be updated to point the server name to the new IP address.

The new server machine name is changed from gemininew to gemini.

Modify /etc/hosts files to define the new machine IP with the server localhost name ie gemini

Distribute the new /etc/hosts to all connecting clients. This can be done using scp.

Switch off old virtual machine

The final task is to switch off and delete the old virtual machine.

This does not actually have to be switched off in order to delete, but to keep things clean in the event of having to request a special restore from the server provider we will switch off correctly first and then delete.

Check the ip address of the machine to make absolutely sure you are logged in on the OLD machine:

ifconfig

then at the command line, do a:

shutdown -h now

Then switch off the old machine on the virtual server provider account dashboard.

Next you can delete the old virtual server.

Make absolutely sure first that you are clicking on and deleting the correct machine!

Finally, delete any associated snapshot backups associated with the old machine.

With that the server migration is now complete. You’ve earned a good cup of coffee!

MySQL-WordPress Problem: “Error Connecting to Database”

By editor on January 2, 2022 in

One day without warning and for no clear reason, my website suddenly started sending “error connecting to database” messages in web-browser for http://kevwells.com.

No content was displayed. The WordPress WP-Admin could not be accessed either. Big Problem!

Non-Wordpress html pages were displaying and functioning ok, so it was obviously a Mysql-Wordpress communication issue.

Also pure PHP pages also displayed without problem – eg phpmyadmin, nextcloud, checkmk. So PHP was running ok.

Possible Causes of the Problem

Most support suggestions list 3 main possibilities:

Your database login credentials are wrong or have been changed.
Your database server is unresponsive.
Your database has been corrupted.

I checked, checked, and rechecked all this in line with all the suggestions – and found everything to be order. No errors, no mistakes, nothing missing.

Searching online for possible alternative explanations for this problem yielded little of use. Most suggestions simply regurgitated the standard textbook stuff – check login credentials, check database is running, check apache is running. Check the database isn’t corrupted.

I encountered other people who also faced this sudden same problem – so I was not alone with this issue – but they were as stuck as I was.

However, there was a fourth possibility which no-one appeared to have mentioned and did not seem to have occurred to anyone:

WordPress plugins causing the problem.

After extensive investigation I ascertained that this was indeed the cause in this case.

The solution was to move plugins to a new directory: plugins.disabled.

Then move plugins one by one back to the original plugin directory, login to the wp-admin and activate one by one, test the website content functionality and move plugins back one by one, repeating the procedure for each in turn.

Below are the steps I used in analysing this problem:

Check MySQL is running and listening on the correct port

First check the mysqld or mysql service is up and running, and that apache is running:

ps -ef | grep apache

ps -ef | grep mysql

also check they are listening for connections, and listening on the correct ports:

netstat -tulpn

Check version of mysql with:

root@gemini:/var/www/kevwells.com# mysql –version
mysql Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@gemini:/var/www/kevwells.com#

Check your WordPress wp-config.php is correct and does not contain any errors

Next check the wp-config.php definitions for the mysql database. These are essential definitions that must be present – substitute your own definitions for your system and make sure they are correct:

Check your wp-config.php file in your WordPress document root directory.

Your wp-config.php should contain following:

// ** MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘database_name_here’ );
/** MySQL database username */
define( ‘DB_USER’, ‘username_here’ );
/** MySQL database password */
define( ‘DB_PASSWORD’, ‘password_here’ );
/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’ );

How to Debug wp-config.php

The following code, inserted in your wp-config.php file, will log all errors, notices, and warnings to a file called debug.log in the wp-content directory. It will also hide the errors so they do not interrupt page generation.

This code will need be to inserted before the comment /* That’s all, stop editing! Happy blogging. */ in the wp-config.php file.

// Enable WP_DEBUG mode
define( ‘WP_DEBUG’, true );

// Enable Debug logging to the /wp-content/debug.log file
define( ‘WP_DEBUG_LOG’, true );

// Disable display of errors and warnings
define( ‘WP_DEBUG_DISPLAY’, false );
@ini_set( ‘display_errors’, 0 );

// Use dev versions of core JS and CSS files (only needed if you are modifying these core files)
define( ‘SCRIPT_DEBUG’, true );

WP_DEBUG #WP_DEBUG
WP_DEBUG is a PHP constant (a permanent global variable) that can be used to trigger the “debug” mode throughout WordPress. It is assumed to be false by default and is usually set to true in the wp-config.php file on development copies of WordPress.

// This enables debugging.
define( ‘WP_DEBUG’, true );
// This disables debugging.
define( ‘WP_DEBUG’, false );

Obviously you do not want both true and false defined at the same time, so comment out the one you do not currently want to be active.

Note: The true and false values in the example are not surrounded by apostrophes (‘) because they are boolean (true/false) values. If you set constants to ‘false’, they will be interpreted as true because the quotes make it a string rather than a boolean.

It is not recommended to use WP_DEBUG or the other debug tools on live site; they are meant for local testing, debugging, and install stage only.

WP_DEBUG_LOG #WP_DEBUG_LOG

WP_DEBUG_LOG is a companion to WP_DEBUG that causes all errors to also be saved to a debug.log log file This is useful if you want to review all notices later or need to view notices generated off-screen (e.g. during an AJAX request or wp-cron run).

Note that this allows you to write to log file using PHP’s built in error_log() function, which can be useful for instance when debugging Ajax events.

When set to true, the log is saved to debug.log in the content directory (usually wp-content/debug.log) within your site’s filesystem. Alternatively, you can set it to a valid file path to have the file saved elsewhere.

define( ‘WP_DEBUG_LOG’, true );
-or-
define( ‘WP_DEBUG_LOG’, ‘/tmp/wp-errors.log’ );
Note: for WP_DEBUG_LOG to do anything, WP_DEBUG must be enabled (true). Remember you can turn off WP_DEBUG_DISPLAY independently.

Does the problem occur for /wp-admin/ as well?

Does it occur with webpages on the server which are not provided by the wordpress database? eg plain html, php-driven services, and the like, if there are any on the server?

eg checkmk, or nextcloud, or simple html pages.

Try Repairing the WordPress Database

If the error is only on the front end. try to repair the database by adding the following line of code in wp-config.php

define(‘WP_ALLOW_REPAIR’, true);

Then go to ‘http://www.yoursite.com/wp-admin/maint/repair.php’ and click ‘repair and optimize database’ button.

/usr/bin/mysqlcheck –all-databases –auto-repair

check:

mysqlshow -u database_username -p

root@gemini:~#
root@gemini:~# mysqlshow –verbose
+——————–+——–+
| Databases | Tables |
+—

(DATA REMOVED FOR SECURITY REASONS)

| 12 |

+——————–+——–+
9 rows in set.
root@gemini:~#

Check Your index.php is Present and Error-Free

Check the index.php is present and is the correct instance. This is important!

Whenever someone visits a web page on a WordPress site, the server attempts to run index.php or index.html from the defined WordPress document root directory.

Your website document root directory index.php contains the code that generates a WordPress site.

However there is also an index.php in the wp-content directory and in other locations. If you examine the index.php located under wp-content and other directories you will find that it contains no active code. It is simply a placeholder.

Without index.php, anyone could access your site’s /wp-content folder and see all of the media, files, and directories it contains.

The index.php file at this location and others functions like a privacy screen: it blocks visitors from directly accessing your directories.

Alternativey you can use the following directive in your .htaccess file to prevent directory listings even if that index.php file is not present:

Options –Indexes

However, be aware that you do NOT want this file in your main document root folder, else no one can access any website content!

Check Apache Can Connect To MySQL Server

Next, check your web-host can connect to your mysql server.

In PHP you can easily do this using the mysqli_connect() function. All communication between PHP and the MySQL database server takes place through this connection. Here’re the basic syntaxes for connecting to MySQL using MySQLi extensions:

Syntax: MySQLi, Procedural way
$link = mysqli_connect(“hostname”, “username”, “password”, “database”);

Syntax: MySQLi, Object Oriented way
$mysqli = new mysqli(“hostname”, “username”, “password”, “database”);

Create a new file called testconnection.php and paste the following code in it:

<?php
$link = mysqli_connect(‘localhost’, ‘username’, ‘password’);
if (!$link) {
die(‘Could not connect: ‘ . mysqli_error());

}
echo ‘Connected successfully’;
mysqli_close($link);
?>

(substitute localhost, username and password with the correct parameters for your site)

Then I called up the file in the webbrowser:

http://3.222.27.169/testconnection.php

Result, it displayed:

Connected successfully

So that means the connection parameters – dbhost name, username and password are ok.

Check the Error Logs for MySQL and Apache

If the configuration seems correct and the service is running, but your website still doesn’t load as it should, try checking the logs for any hints to as what might be the cause.

Error logs for Apache are located at /var/log/apache/error.log

Error logs for MySQL are located at /var/log/mysql/error.log

Should you not be able to find anything within the most recent logs, check the archived ones as well. To do this, use ‘zgrep’ with otherwise the same command as regular ‘grep’

sudo zgrep -i error /var/log/mysql/error.log.1.gz

Since the database under CentOS is named MariaDB instead of MySQL, the logs are also saved under a different name. You can search the logs with the following command.

sudo grep -i error /var/log/mariadb/mariadb.log

Debian systems also report MySQL events to /var/log/syslog, to filter out everything else, use ‘grep’ with two keywords separated by .* to express ‘and’ like in the command below.

sudo grep -i -E ‘mysql.*error’ /var/log/syslog

If you are having difficulties finding anything helpful, try different keywords such as ‘start’ to see when the service was last restarted, or ‘failed’ to find any less critical problems that might not be reported as errors.

When we try

http://3.222.27.169/wp-admin

we see in /var/log/mysql/error.log:

2021-12-29 19:39:44 264 [Warning] Access denied for user ”@’localhost’ (using password: NO)
2021-12-29 19:40:22 270 [Warning] Access denied for user ”@’localhost’ (using password: NO)

so, it looks like there is some login issue for the mysql database user:

Check the MySQL Database

MariaDB []> SELECT User, Host FROM mysql.user;
+—————+———–+
| User | Host |
+—————+———–+
| * | % |
| * | localhost |
| * | localhost |
| * | localhost |
| * | localhost |
| * | localhost |
+—————+———–+
6 rows in set (0.000 sec)

MariaDB [kevwells]>

NOTE! Usernames have been removed from the above for security reasons.

Use the desc mysql.user; statement to display information about the table’s columns. Once you know the column name, you can run a query against a selected data.

For example, to get a list of all MySQL users accounts including information about the password and whether it is active or expired, you would use the following query:

SELECT User, Host, Password, password_expired FROM mysql.user;

MariaDB []> SELECT User, Host, Password, password_expired FROM mysql.user;
+—————+———–+——————————————-+——————+
| User | Host | Password | password_expired |
+—————+———–+——————————————-+——————+
| * | localhost | * | N |
| * | localhost | * | |
| * | localhost | * | N |
| * | localhost | * | N |
| * | % | * | N |
| * | localhost | * | N |
+—————+———–+——————————————-+——————+
6 rows in set (0.001 sec)

MariaDB []>

#NOTE! Usernames and passwords have been removed from the above for security reasons.

Show Users that Have Access to a Particular Database

The information about the database-level privileges is stored in the mysql.db table.
You can query the table to find out which users have access to a given database and the level of the privileges.

For example, to get a list of all users that have some level access to the database named db_name you would use the following query:

SELECT * FROM mysql.db WHERE Db = ‘db_name’\G;

MariaDB []> SELECT * FROM mysql.db WHERE Db = ‘*****’\G;
*************************** 1. row ***************************
Host: localhost
Db: ****
User: ***
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
Delete_history_priv: Y
1 row in set (0.001 sec)

ERROR: No query specified

MariaDB []>

NOTE! Usernames, dbname and passwords have been removed from the above for security reasons.

To fetch information only about the user accounts that have access to a given database, without displaying the privileges use:

SELECT db, host, user FROM mysql.db WHERE db = ‘db_name’;

MariaDB []> SELECT db, host, user FROM mysql.db WHERE db = ‘***’
-> ;
+———-+———–+—————+
| db | host | user |
+———-+———–+—————+
| * | localhost | * |
+———-+———–+—————+
1 row in set (0.001 sec)

MariaDB []>

NOTE: Sensitive information has been removed from the output for security reasons!

The following query will show you information about all databases and associated users:

SELECT db, host, user FROM mysql.db;

Try restoring database from a backup made using wp-db-backup plugin:

Put the backed-up SQL back into MySQL/MariaDB:
user@linux:~/files/blog> mysql -h mysqlhostserver -u mysqlusername -p databasename < blog.bak.sql

Enter password: (enter your mysql password)
user@linux:~/files/blog>

root@gemini:/var/www/kevwells.com# mysql -h localhost -u *** -p *** < /var/www/kevwells.com/*****.sql

Enter password:
root@gemini:/var/www/kevwells.com#

NOTE! Usernames, dbname and passwords have been removed from the above for security reasons

To delete a user use:

DROP USER ‘exampleuser1’@localhost;

then check the user is no longer present:

MariaDB [(none)]> SELECT User FROM mysql.user;

The syntax for changing a password using the SET PASSWORD statement in MariaDB is:

SET PASSWORD [ FOR user_name ] =
{
PASSWORD(‘plaintext_password1’)
| OLD_PASSWORD(‘plaintext_password2’)
| ‘encrypted_password’
};

For example, if you had an entry with User and Host column values of ‘bob’ and ‘%.loc.gov’, you would write the statement like this:

SET PASSWORD FOR ‘bob’@’%.loc.gov’ = PASSWORD(‘newpass’);

If you want to delete a password for a user, you would do:

SET PASSWORD FOR ‘bob’@localhost = PASSWORD(“”);

I was then able to access phpmyadmin, so one step forward on the way to solving the problem.

In phpmyadmin update the site url if necessary with:

UPDATE wp_options SET option_value=’YOUR_SITE_URL’ WHERE option_name=’siteurl’

UPDATE wp_options SET option_value=’http://3.222.27.169′ WHERE option_name=’siteurl’

Show query box
1 row affected. (Query took 0.0024 seconds.)
UPDATE wp_options SET option_value=’http://3.222.27.169′ WHERE option_name=’siteurl’

1 row affected.. yes, but it also says

apache2, mysql and sshd are all running.

check version of mysql with

root@gemini:/var/www/kevwells.com# mysql –version
mysql Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
root@gemini:/var/www/kevwells.com#

Set the MySQL user password
Type the following commands if you have MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

ALTER USER ‘user-name’@’localhost’ IDENTIFIED BY ‘NEW_USER_PASSWORD’;
FLUSH PRIVILEGES;

You MUST do a FLUSH PRIVILEGES;

MariaDB []> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

that is now working ok!

but the website still can’t connect…

Note the command and its arguments are of the form:

mysql -h locahost -u username -p database

The arguments are

-h followed by the server host name (localhost)
-u followed by the account user name (use your MySQL username)
-p which tells mysql to prompt for a password
database the name of the database (use your database name).
Once mysql is running, you can type SQL statements and see the output in the terminal window.

NOTE -p is NOT PASSWORD!!!

so lets try:

mysql -h locahost -u username -p database

It works, we can connect to the database…

but the website still does not connect!

To reset a user’s MySQL/MariaDB password, log in to the command-line client as root with the command:

mysql -u root -p

Next, reset the user’s password with the command:

update mysql.user set password = MD5(‘(new_password)’) where user = “jdoe”;

Replace new_password with the new password, and jdoe with the username.

Next, show the user’s grants with the command:

show grants for ‘jdoe’@’localhost’;

Replace jdoe with the username. If applicable, change localhost to the host name.

You should get a list of the privileges that user has for the relevant database. It should look something like this:

MariaDB [(none)]> show grants for ‘***’@’localhost’;
+——————————————————————————————————————————-+
| Grants for ***@localhost |
+——————————————————————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO `****`@`localhost` IDENTIFIED BY PASSWORD ‘********** ‘ |
| GRANT ALL PRIVILEGES ON `<database>`.* TO `****`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `<database`.* TO `***`@`localhost` |
+——————————————————————————————————————————-+
3 rows in set (0.000 sec)

MariaDB [(none)]>

NOTE: Sensitive data removed from above for security reasons!

to create a new user:

Log into MYSQL as root

mysql -u root
Grant privileges. For a new user, execute:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@’localhost’;
FLUSH PRIVILEGES;

I created a new php user for admin as the original user could no longer login all of a sudden!

can now login once again to phpmyadmin

Check the MySQL hostname

If the script is hosted on the same server as the MySQL database (e.g. your Linux Cloud Server), the hostname is localhost by default.

To double-check the MySQL hostname, log into the command-line client as root with the command:

mysql -u root -p

Next, show the hostname with the command:

SHOW VARIABLES WHERE Variable_name = ‘hostname’;

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name = ‘hostname’;

MariaDB [(none)]>

On Debian and Ubuntu servers the configuration file for MySQL is usually saved at /etc/mysql/. It’s also possible to have user-specific settings stored at /home/<user>/.my.cnf, which would override the global configurations. Check if any user level overrides have been set.

It is commonly advised to have separate usernames for different web applications, so check at least those relevant to your page loading issues. You can open the global configuration file with first of the following two commands below, and the user-specific with the latter by replacing the <user> with a database username.

sudo nano /etc/mysql/my.cnf

sudo nano /home/<user>/.my.cnf

By scrolling down past [client] and [mysqld_safe] settings you’ll find something like the example here.

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

The lines here to pay close attention to are ‘socket’, ‘datadir’ and ‘bind-address’. The parameters in the example above are in their default values, and in most cases, your configuration would look the same. Make sure the settings point to the correct directories so that MySQL can actually find the required files.

The easiest way to check the ‘datadir’ is to use this command below

sudo ls -l /var/lib/mysql/

The output will list all files in that directory, it should contain at least the following plus any databases you have created.

drwx—— 2 mysql root 4096 Aug 5 12:23 mysql
drwx—— 2 mysql mysql 4096 Aug 5 12:29 performance_schema

If the data directory or socket has been moved and MySQL doesn’t know where they are, fix the configuration file to point to the correct directories. You can search for the folders with the following command.

sudo find / -name performance_schema && sudo find / -name mysql.sock

The third parameter you’ll need to check is the bind-address, this is only really relevant if your database needs to be accessed remotely. In Debian and Ubuntu installations the bind is by default set to the loopback address, which prevents database calls from outside the localhost. CentOS doesn’t have the same parameter unless manually set. For any setup where your web service is on a different server to the database, this bind-address should be set to the server’s own private IP.

Step 2 — Importing a MySQL or MariaDB Database

To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data.

First, log in to MySQL as root or another user with sufficient privileges to create new databases:

mysql -u root -p

This command will bring you into the MySQL shell prompt. Next, create a new database with the following command. In this example, the new database is called new_database:

CREATE DATABASE new_database;

You’ll see this output confirming the database creation.

Output
Query OK, 1 row affected (0.00 sec)

Then exit the MySQL shell by pressing CTRL+D. From the normal command line, you can import the dump file with the following command:

mysql -u username -p new_database < data-dump.sql

username is the username you can log in to the database with
newdatabase is the name of the freshly created database
data-dump.sql is the data dump file to be imported, located in the current directory

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead. To check if the import was successful, log in to the MySQL shell and inspect the data. Selecting the new database with USE new_database and then use SHOW TABLES; or a similar command to look at some of the data.

The Actual Cause of the Problem In This Case – WordPress Plugins

I finally ascertained that the cause of the problem was WordPress plugins. Presumably one or more WordPress plugins have been automatically updated and have suddenly resulted in this bug being released.

The Solution

The solution was to move all plugins to a new directory: plugins.disabled.

Then move each plugin one by one back to the original plugin directory, for each plugin, login to the wp-admin and activate one by one, test the website content functionality.

Continue in this way, moving plugins back one by one, repeating the procedure for each in turn.

Make sure when you test the pages content to call up different pages each time – else you could be receiving a “false positive” from cached website content from caches in the web-browser or internet provider!

In addition, I made sure to disable the automatic WordPress Plugin update option. As so often happens with these kind of “smart functionality” bright ideas for auto-updating dreamt up by programmers, it ends up in practice causing more problems than the one it was originally intended to solve.

So, the website is now connecting to the database successfully and is once again able to display the content without errors. Let’s hope we can keep it that way.

Until the next software update.

How To Install Mysql/Mariadb

By editor on June 30, 2020 in

I refer here to mysql and mariadb interchangeably.

Mariadb is a fork of a later version of mysql and is largely compatible with mysql. To all intents and purposes for this installation and configuration instance they are the same.

apt install mysql-server

during the installation you’ll be requested to set a mysql server admin user password.

If the secure installation utility does not run automatically during the installation process, then you can run it explicitly:

mysql_secure_installation utility

This prompts you to set the mysql root password and some other security options, such as disabling remote access for the root user.

Next, install the mysql php module:

apt-get install php-mysql

then start the server:

systemctl enable mysql
systemctl start mysql

Check that mysql is running with:

root@gemini:/etc/apache2/sites-enabled# systemctl status mysql
● mariadb.service – MariaDB 10.3.22 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sun 2020-06-28 18:19:05 BST; 24h ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 586 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 619 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 631 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR>
Process: 738 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 740 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 694 (mysqld)
Status: “Taking your SQL requests now…”
Tasks: 30 (limit: 2282)
Memory: 382.1M
CGroup: /system.slice/mariadb.service
└─694 /usr/sbin/mysqld

You can also test the system with:

mysqladmin -p -u root version

This will return the mysql or mariadb server version information.

root@gemini:~#mysqladmin -p -u root version
Enter password:

mysqladmin Ver 9.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version 10.3.22-MariaDB-1ubuntu1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 day 15 hours 16 min 7 sec

Threads: 10 Questions: 1333249 Slow queries: 0 Opens: 127 Flush tables: 1 Open tables: 120 Queries per second avg: 9.431
root@gemini:~#

Next, you can create an admin user and an initial database:

If you have problems with the root password, you can reset it using the mysql command line tool:

mysql -u root -p

you can display the users which are defined with:

SELECT User, Host, authentication_string FROM mysql.user;

To change the root password enter:

UPDATE mysql.user SET authentication_string = PASSWORD(‘password’) WHERE User = ‘root’;

and then:

FLUSH PRIVILEGES:

exit

Again in the mysql command line tool, you can create a database with:

CREATE DATABASE demodb;

display all existing databases with:

SHOW DATABASES;

To work with a database:

USE <databasename>;

and delete permanently a database with:

DROP DATABASE <databasename>;

NOTE: you cannot retrieve a database once you have deleted it with DROP!

With that the basic installation of mysql server is complete.

Navigation

Tags Archives: mysql

Copy the WordPress Directory

Create the Website MySQL Database and User

Launch the WordPress Duplicator Plugin

Assemble the MySQL Command To Modify the Website URL References

Build a Shell Script to Automate the Replication Process

Set up the SSH for Remote Login without Password Prompt

Check Mysql Server Command Functionality

Assemble the Script

Additional Notes on the Replication Process

Check error logs

Check configuration variables

Check the mysql user exists

Check the output of mysql_install_db

Deinstall Mariadb/Mysql

Reinstall mariadb-server

mariadb is now working

Further Troubleshooting Advice at mariadb.com

Initial Preparation

Modify DNS Record

Install Apache

Install Mysql/MariaDB

Run the mysql secure installation utility

Set up a Mysql Database for WordPress

Install WordPress

Firewalling for apache

Install SSL Certificates

Install Nextcloud

Switch WordPress from http to https

Firewalling – Portmapper

Modify hosts files

Switch off old virtual machine

Possible Causes of the Problem

Check MySQL is running and listening on the correct port

Check your WordPress wp-config.php is correct and does not contain any errors

How to Debug wp-config.php

Try Repairing the WordPress Database

Check Your index.php is Present and Error-Free

Check Apache Can Connect To MySQL Server

Check the Error Logs for MySQL and Apache

Check the MySQL Database

The Actual Cause of the Problem In This Case – WordPress Plugins

The Solution