How to Fix MySQL or MariaDB Startup Failure on Ubuntu 20.04

Learn how to troubleshoot and resolve the common issue of MySQL or MariaDB failing to start on Ubuntu 20.04.

MySQL and MariaDB are popular open-source relational database management systems that are widely used for web development, data analysis, and other applications. However, sometimes they may fail to start on Ubuntu 20.04 due to various reasons, such as configuration errors, permission issues, corrupted files, or insufficient disk space.

In this article, we will show you how to diagnose and fix the problem of MySQL or MariaDB failing to start on Ubuntu 20.04, using some simple commands and steps.

Check the Status of MySQL/MariaDB Service

The first step to troubleshoot the issue is to check the status of the MySQL or MariaDB service using the systemctl command. This will show you if the service is running, stopped, or failed, and also provide some information about the error code and message. For example, you can run the following command to check the status of MySQL:

sudo systemctl status mysql

Or, if you are using MariaDB, you can run:

sudo systemctl status mariadb

The output will look something like this:

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2021-03-02 11:32:34 IST; 58s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3383 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)
  Main PID: 695 (code=exited, status=1/FAILURE)
    Status: "MariaDB server is down"

Mar 02 11:32:34 avivilloz systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Mar 02 11:32:34 avivilloz systemd[1]: Stopped MySQL Community Server.
Mar 02 11:32:34 avivilloz systemd[1]: mysql.service: Start request repeated too quickly.
Mar 02 11:32:34 avivilloz systemd[1]: mysql.service: Failed with result 'exit-code'.
Mar 02 11:32:34 avivilloz systemd[1]: Failed to start MySQL Community Server.

From the output, you can see that the MySQL service has failed to start and the error message says “Can’t create test file /mnt/Data/mysql/Ubuntu.lower-test”. This indicates that there is a problem with the data directory of MySQL, which we will discuss in the next section.

Solution: Change the Data Directory of MySQL/MariaDB

One of the common reasons for MySQL or MariaDB failing to start on Ubuntu 20.04 is that the data directory of the database is not set correctly or does not have the proper permissions. The data directory is where the database files are stored, and by default, it is located at /var/lib/mysql for both MySQL and MariaDB. However, sometimes you may want to change the data directory to a different location, such as a separate partition or drive, for better performance, security, or backup purposes.

If you have changed the data directory of MySQL or MariaDB, you need to make sure that you have followed the correct steps to do so, otherwise the database service may not be able to access the files and fail to start. The steps to change the data directory of MySQL or MariaDB on Ubuntu 20.04 are as follows:

1. Stop the MySQL or MariaDB service using the systemctl command. For example:

   sudo systemctl stop mysql

   Or, for MariaDB:

   sudo systemctl stop mariadb

2. Copy the contents of the original data directory to the new location using the rsync command. For example, if you want to move the data directory to /mnt/Data/mysql, you can run:

   sudo rsync -av /var/lib/mysql /mnt/Data

3. Change the ownership and permissions of the new data directory to mysql:mysql using the chown and chmod commands. For example:

   sudo chown -R mysql:mysql /mnt/Data/mysql
   sudo chmod -R 750 /mnt/Data/mysql

4. Edit the configuration file of MySQL or MariaDB to specify the new data directory. The configuration file is located at /etc/mysql/mariadb.conf.d/50-server.cnf for both MySQL and MariaDB. You need to find the line that says datadir and change it to the new location. For example:

   datadir=/mnt/Data/mysql

5. Edit the AppArmor configuration file to allow the database service to access the new data directory. AppArmor is a security module that restricts the access of programs to certain files and directories. The AppArmor configuration file for MySQL or MariaDB is located at /etc/apparmor.d/usr.sbin.mysqld. You need to add two lines to the file, one for the new data directory and one for the parent directory, with the r and w permissions. For example:

   /mnt/Data/mysql/ r,
   /mnt/Data/mysql/** rwk,

6. Reload the AppArmor configuration using the systemctl command. For example:

   sudo systemctl reload apparmor

7. Start the MySQL or MariaDB service using the systemctl command. For example:

   sudo systemctl start mysql

   Or, for MariaDB:

   sudo systemctl start mariadb

8. Check the status of the MySQL or MariaDB service using the systemctl command to verify that it is running. For example:

   sudo systemctl status mysql

   Or, for MariaDB:

   sudo systemctl status mariadb

The output should show that the service is active and running, and the error message should be gone.

Other Possible Solutions

If changing the data directory of MySQL or MariaDB does not fix the problem, there are some other possible solutions that you can try, depending on the error message and the situation. Here are some of them:

If the error message says “Another process with pid [number] is using unix socket file”, it means that there is another instance of MySQL or MariaDB running on the same port. You need to kill the process using the kill command and then restart the service. For example:

sudo kill [number]
sudo systemctl restart mysql

Or, for MariaDB:

sudo kill [number]
sudo systemctl restart mariadb

If the error message says “InnoDB: Error: log file ./ib_logfile0 is of different size”, it means that the size of the InnoDB log file has changed and does not match the configuration. You need to delete the log file and then restart the service. For example:

sudo rm /var/lib/mysql/ib_logfile*
sudo systemctl restart mysql

Or, for MariaDB:

sudo rm /var/lib/mysql/ib_logfile*
sudo systemctl restart mariadb

If the error message says “InnoDB: Database was not shut down normally!”, it means that the database was not closed properly and may have some corrupted data. You need to use the innodb_force_recovery option to recover the data and then restart the service. For example:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following line under the [mysqld] section:

innodb_force_recovery = 1

Save and exit the file, and then run:

sudo systemctl restart mysql

Or, for MariaDB:

sudo systemctl restart mariadb

The innodb_force_recovery option can have a value from 1 to 6, depending on the level of recovery. You can try different values until the service starts successfully. However, be careful with this option, as it may cause some data loss or corruption. You should always backup your data before using this option.

If the error message says “No space left on device”, it means that the disk where the data directory is located is full and does not have enough space for the database files. You need to free up some space by deleting some unnecessary files or moving them to another disk. You can use the df command to check the disk usage and the du command to check the file size. For example:

df -h
du -sh /var/lib/mysql

Frequently Asked Questions (FAQs)

Question: How do I check the logs of MySQL or MariaDB on Ubuntu 20.04?

Answer: You can check the logs of MySQL or MariaDB on Ubuntu 20.04 by using the journalctl command with the -u option and the name of the service. For example:

sudo journalctl -u mysql

Or, for MariaDB:

sudo journalctl -u mariadb

This will show you the logs of the service, which may contain some useful information about the error and the cause.

Question: How do I backup and restore MySQL or MariaDB on Ubuntu 20.04?

Answer: You can backup and restore MySQL or MariaDB on Ubuntu 20.04 by using the mysqldump and mysql commands. For example, to backup all the databases on the server, you can run:

sudo mysqldump --all-databases > backup.sql

sudo mysql < backup.sql

sudo apt update
sudo apt upgrade mysql-server

sudo apt update
sudo apt upgrade mariadb-server