Problems Starting Mariadb/Mysql on Ubuntu 20.10 After Installing

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

 

● 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 12:43:45 GMT; 5min ago
Docs: man:mysqld(8)

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/