How to address “Error establishing a database connection” in WordPress

“Error establishing a database connection” is one the  most common error for custom WordPress installation. It could be due to one of the following reasons

  1. MySQL username and password is incorrect in wp-config.php file.
  2. MySQL database is down
  3. MySQL database is corrupted ( Don’t get scared by this reason. This is least likely reason)

MySQL username and password is incorrect in wp-config.php file.

Most likely you will get this error during installation of wordpress on your server or local machine if you have not updated database username and password in wp-config.php.

Please check following section in wp-config.php file.

/** The name of the database for WordPress */
define('DB_NAME', 'DATABASENAME');

/** MySQL database username */
define('DB_USER', 'DATABASENAME_USERNAME');

/** MySQL database password */
define('DB_PASSWORD', 'DATABASENAME_PASSWORD');

/** 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', '');

 

To avoid getting this error, please follow WordPress installation guide.

MySQL database is down

There could be multiple reasons for database to be done. Simply restart MySQL database using following command

sudo service mysql start

Most likely this will solve your issue. If it does not solve, it will throw following error.

$sudo service mysql start
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Here is the output of both the commands

$sudo systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: inactive (dead) (Result: exit-code) since Sun 2018-04-29 02:10:42 UTC; 53s ago
Process: 10667 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)
Process: 10626 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 3812 (code=killed, signal=KILL)

Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: Failed to start MySQL Community Server.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Unit entered failed state.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: Stopped MySQL Community Server.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: mysql.service: Start request repeated too quickly.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: Failed to start MySQL Community Server
$ sudo journalctl -xe
-- Unit mysql.service has begun starting up.
Apr 29 02:10:41 ubuntu-512mb-server audit[10666]: AVC apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=10666 comm="apparmor_parser"
Apr 29 02:10:41 ubuntu-512mb-server kernel: audit: type=1400 audit(1524967841.661:66): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=10666 comm="apparmor_parser"
Apr 29 02:10:41 ubuntu-512mb-server mysqld[10667]: Initialization of mysqld failed: 0
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Control process exited, code=exited status=1
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Unit entered failed state.
Apr 29 02:10:41 ubuntu-512mb-server systemd[1]: mysql.service: Failed with result 'exit-code'.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Apr 29 02:10:42 ubuntu-512mb-server systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has finished shutting down.

This tells me this is not norma mysql shutdown and I need to check further logs. Use following command after trying MySQL restart.

$sudo tail -30 /var/log/mysql/error.log
2018-04-29T02:15:31.641869Z 0 [ERROR] Failed to initialize builtin plugins.
2018-04-29T02:15:31.641872Z 0 [ERROR] Aborting

2018-04-29T02:15:31.643813Z 0 [Note] Binlog end
2018-04-29T02:15:31.643871Z 0 [Note] Shutting down plugin 'MyISAM'
2018-04-29T02:15:31.644102Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2018-04-29T02:15:32.135955Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-29T02:15:32.138090Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.21) starting as process 10937 ...
2018-04-29T02:15:32.141779Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-04-29T02:15:32.141810Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-04-29T02:15:32.141818Z 0 [Note] InnoDB: Uses event mutexes
2018-04-29T02:15:32.141825Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-04-29T02:15:32.141831Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-04-29T02:15:32.141837Z 0 [Note] InnoDB: Using Linux native AIO
2018-04-29T02:15:32.142157Z 0 [Note] InnoDB: Number of pools: 1
2018-04-29T02:15:32.142291Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-04-29T02:15:32.144099Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-04-29T02:15:32.144141Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2018-04-29T02:15:32.144149Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-04-29T02:15:32.144154Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-04-29T02:15:32.144161Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-04-29T02:15:32.144165Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-04-29T02:15:32.144169Z 0 [ERROR] Failed to initialize builtin plugins.
2018-04-29T02:15:32.144172Z 0 [ERROR] Aborting

2018-04-29T02:15:32.146255Z 0 [Note] Binlog end
2018-04-29T02:15:32.146304Z 0 [Note] Shutting down plugin 'MyISAM'
2018-04-29T02:15:32.146569Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Please note the error statements highlighted in bold.

Root cause.

This error is coming because my system is falling short of RAM memory. Normally when you use server with 512 MB RAM, this kind of issues occur.

Solution

  1. Increase the RAM of the server by upgrading your server capacity
  2. Add swapfile. I solved above problem by adding swapfile. Please refer to this excellent guide to add swap file.

 

MySQL database is corrupted

If your MySQL installation is corrupted, I hope you have some database backup available with you. There are options to recover data. If you have backup data available, best option is to reinstall MySQL. Please follow how to install MySQL on Ubuntu guide.

How to install wordpress on Ubuntu 16.04

Here is quick reference that I created for myselft for creating local wordpress blog installation. Hope it helps you as well.

  • Installation

Make sure you have following packages installed on your local machine

  1. MySQL (Please see post How to install MySQL on Ubuntu)
  2. NGINX (Please see post How to install nginx on Ubuntu)

Besides these, install following packages

sudo apt update
sudo apt install php php-curl php-gd php-mbstring php-xml php-xmlrpc php-soap php-intl php-zip
sudo apt install php-mysql
sudo apt install php-fpm
  • Configure MySQL

Create user for wordpress

$mysql -u root -p
mysql> CREATE DATABASE localwordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> GRANT ALL ON localwordpress.* TO 'localwordpresssuser'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> exit;
  • Download  WordPress.

curl -O https://wordpress.org/latest.tar.gz
tar xzvf latest.tar.gz
cp wordpress/wp-config-sample.php wordpress/wp-config.php
mkdir wordpress/wp-content/upgrade
sudo cp -a wordpress  /var/www/wordpress1

After moving WordPress folder to required path, you need to change few folder permissions.

sudo chmod g+w /var/www/wordpress1/wp-content
sudo chmod -R g+w /var/www/wordpress1/wp-content/themes
sudo chmod -R g+w /var/www/wordpress1/wp-content/plugins
sudo chown -R www-data:www-data /var/www/wordpress1
  • Configure WordPress

Open wp-config.php file and make following changes

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

/** MySQL database username */
define('DB_USER', 'localwordpresssuser');

/** MySQL database password */
define('DB_PASSWORD', 'password_here');

/** MySQL hostname */
define('DB_HOST', 'password');

/** 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', '');

Visit following link for generate AUTH KEY and other remaining fields

https://api.wordpress.org/secret-key/1.1/salt/

simply copy paste values displayed on above link  into config.php file at appropriate location. Here is the sample. (Please don’t copy paste below values into your installation)

define('AUTH_KEY',         'Johd+fp5c.esU?J26hZb8^6Gi GAL+^Abs-{k4%g0G4IEGVKlZ`|MEk4B;W++%s*');
define('SECURE_AUTH_KEY',  '_J}pBRjcek6f+wj*BNF}lPu-xNX$^.+`nIx|*kg-YjY+v)%Qi<J`pI?|zE/BIa<U');
define('LOGGED_IN_KEY',    '2.T%wJpUwsSeZQV?K,R)7$u }yl]rfLt|.gg,uI.SP&U>u_7q+*uH2+Gy4}-AmYC');
define('NONCE_KEY',        '~^T R=oy/Ej`>q8&FP7rPM1vZ%;}*@oz7^b~~>>Clw{.LGYdbDjV$-t<U5/(&;M7');
define('AUTH_SALT',        'Nn?`[;?9=niA=Jun:ikXi(BR%%Y7MYVtMd+,a&_ZKQei3S ;Z8XteX{=f8~~=D~p');
define('SECURE_AUTH_SALT', 'WCRf7Un/.BX9z~_4dt}!-k$<Y02mm=fKEvHo;5{]!s9=w/x/@9-,Q?ib-jMM#47/');
define('LOGGED_IN_SALT',   '$~+K5U>rd3_B#+X*^G2hlBTnD:)W](rer%VMS #G8jJ^f(5Gr@.aF:6`hg~:OkS(');
define('NONCE_SALT',       'xdtH,{ir(-I5|/NIHaD^eFu.pKCIC-5!Gn`YBDq#?bRfhI5,-c,;?^^<6V%P04iD');

Also add FS_METHOD  after define ('WP_DEBUG', false); It should look as below

define('WP_DEBUG', false);
define('FS_METHOD', 'direct');
  • Troubleshooting

I got error “Authentication is needed to run ‘/bin/cp’ as the super user” while saving wp-config.php file.

Its not a big deal. Simply go to terminal and run following commands

sudo chmod 777 projectname
sudo chown $USER -R projectname/
  • Configure nginx

  • You need to configure nginx so that typed url will direct to correct installation directory. Following is the sample code. You can copy this as is but make sure you change the server_name parameter to your domain name.Create following file in folder /etc/nginx/sites-available/. You name is as you with but for easy reference, add name of domain in the file name.
    
    server {
            listen 80 default_server;
            listen [::]:80 default_server;
    
            # SSL configuration
            #
            # listen 443 ssl default_server;
            # listen [::]:443 ssl default_server;
            #
            # Note: You should disable gzip for SSL traffic.
            # See: https://bugs.debian.org/773332
            #
            # Read up on ssl_ciphers to ensure a secure configuration.
            # See: https://bugs.debian.org/765782
            #
            # Self signed certs generated by the ssl-cert package
            # Don't use them in a production server!
            #
            # include snippets/snakeoil.conf;
    
            root /var/www/fintrekking;
    
            # Add index.php to the list if you are using PHP
            #index index.html index.htm index.nginx-debian.html;
            index index.php
    
            server_name _;
    
            location / {
                    # First attempt to serve request as file, then
                    # as directory, then fall back to displaying a 404.
                    try_files $uri $uri/ =404;
            }
    
            # pass PHP scripts to FastCGI server
            #
            location ~ \.php$ {
                    include snippets/fastcgi-php.conf;
    
                    # With php-fpm (or other unix sockets):
                    fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
            #       # With php-cgi (or other tcp sockets):
            #       fastcgi_pass 127.0.0.1:9000;
            }
            location /wp-admin/ {
                    index index.php;
                    try_files $uri $uri/ /index.php$args;
            }
    
            # deny access to .htaccess files, if Apache's document root
            # concurs with nginx's one
            #
            #location ~ /\.ht {
            #       deny all;
            #}
    }
    

    copy this file to sites-enabled folder using following command and restart nginx

    $sudo ln -s /etc/nginx/sites-available/wordpress1.com /etc/nginx/sites-enabled/
    $sudo nginx -t
    $sudo systemctl restart nginx

    Considering we need to access local installation on custom url, that url need to point to localhost. This needs to be changed in host file.

    sudo nano /etc/hosts
    127.0.1.1 localwordpress1.com
  • Access your installation # localwordpress1.com
install wordpress ubuntu 16.04
install wordpress ubuntu 16.04

Hope this is helpful to you !

MySQL Quick Reference

Here is the list of commands for MySQL that will be evry handy while using MySQL. Over the period of time, you will remember these commands but to get started, you can use this as a cheat sheet for MySQL commands.

Connecting to MySQL

  • Check status of MySQL
$ systemctl status mysql
  • Start and Stop MySQL Server using
$ sudo systemctl start mysql
$ sudo systemctl stop mysql
  • Start MySQL without admin privileges
sudo mysqld_safe --skip-grant-tables --skip-networking &  
  • After installation defact userid and password can be viewed by using following command
sudo cat /etc/mysql/debian.cnf

[client]
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock
  • Login to MySQL Database from command line. Once authentication is completed successfully, mysql> prompt will appear.
$mysql -u USERNAME -p PASSWORD

OR

$ mysql --host=localhost --user=USERNAME --password PASSWORD

If you want to connect to any remote MySQL server, replace ‘localhost’ with remote db address name and add port parameter.

$ mysql --host=remote.example.com --port=13306 --user=USERNAME --password PASSWORD

Commands to be used in mysql> prompt:

Use following command to get list of databases

mysql> show databases;

Use following command to create database

mysql> create database <newDatabaseName>;

Create new user

mysql>CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

grant access to newly created user

mysql> grant create, delete, insert, select, update, alter,references ON <databaseName>.* TO 'usernmae'@'localhost';

Use following command to delete or drop  database

mysql> drop database DatabaseName;

Use following command to get list of users

mysql> SELECT User FROM mysql.user;

 

To check the access level of the user

mysql>SHOW GRANTS FOR 'root'@'localhost';

To use specific database name use following command

mysql>use DBNAME

To export database.

Please note that password is not put after “-p”, there will be prompt for password where you need to provide the password.

$mysqldump -u [username] -p [databaseName] > [databaseName].sql

To import database

$mysql -u [username] -p newdatabase < [databaseName].sql

 

How to migrate Ghost blog from SQLite3 To MySQL

Migrating Ghost blog from SQLite3 to MySQL is fairly simple process. You need to export your posts, configure MySQL for Ghost and lastly, you need to import previously exported posts and you are done. Please follow below mentioned steps.

Step#1: Exporting the Ghost Blog posts.
Login to ghost blog as admin using following link
http://yourblog.com/ghost/ OR http://yourblog.com/admin

Go to labs option and clock on export.

It will create a json file. Keep this file handy as we will import same file after we have configured MySQL.

Step#2: Install MySQL
Please ensure MySQL is installed on your machine. You can follow how to install MySQL on ubuntu.

Step#3: create user for ghost blog
Login to MySQL as root using following command. It will ask for root password.

$ mysql -u root -p

Create database as well as user by using following commands

mysql> create database ghost;  
mysql> CREATE USER 'ghost'@'localhost' IDENTIFIED BY 'password';  

Now add grant user to database

mysql> grant create, delete, insert, select, update, alter,references ON ghost.* TO 'ghost'@'localhost';  

and now flush the newly added access.

mysql> flush privileges;  

Step#4: edit config file to use MySQL
Configure to use production database as mentioned in this post.
Your final config file snippet is as below:

        database: {
            client: 'mysql',
            connection: {
                host: 'localhost',
                user: 'username',
                password: 'password',
                database: 'ghost',
                charset: 'utf8'
            },
            debug: true
        },

Step#4: Now run your blog in production mode as mentioned in post using ghost in production and development mode.

$ npm start --production

Step#5 Importing previously exported blog dump.
Again Login to ghost blog as admin using following link
http://yourblog.com/ghost/ OR http://yourblog.com/admin

Go to labs option and clock on import and use the previously exported file.

This worked well for me, please let me know if you run into any issues…

Recommendations.
Having seen that, I would recommend using SQLite3 as a database someone has said, don’t fix it until its broken and considering SQLite can handle 100k hits per days (which cover most of the websites) there is no need to create extra work for yourself by moving database to MySQL. However you must note that, GhostPro uses MySQL database and ghost officially has dropped support for PostgreSQL.

Please refer to SQLite documentation for further clarification.

Configure Ghost Blog using MySQL

By default Ghost uses sqlite3 database. If you want to use MySQL, this can be done by changing configuration config.js. Please follow changes suggested in below post to use MySQL in Ghost blog.

Ghost blog configuration is as below:

        database: {
            client: 'sqlite3',
            connection: {
                filename: path.join(__dirname, '/content/data/ghost.db')
            },
            debug: false
        },

This needs to be changed to following

        database: {
            client: 'mysql',
            connection: {
                host: 'localhost',
                user: 'username',
                password: 'password',
                database: 'ghost',
                charset: 'utf8'
            },
            debug: true
        },

You can make this change in production section of configuration and keep development section as is. You need to ensure that you start Ghost server in production by following command

npm start --production  

Using MySQL workbench

MySQL Workbench provides DBAs and developers an integrated tools environment for:

  • Database Design & Modeling
  • SQL Development
  • Database Administration
  • Database Migration
  • Installation:

Download installation package as per our OS from following link
https://dev.mysql.com/downloads/workbench/

and follow normal installation process.

Alternative way.
If you are getting any error while install by first method, you can use following commands to get it installed

sudo apt-get install mysql-workbench  

Once installation is done, you will find MySQL Workbench in your list of applications.

When you open MySQL workbench, following screen will appear.

After you connect to local server, you will see following window.

Install MySQL server ubuntu

MySQL is an open-source relational database management system (RDBMS). I had always preferred PostgreSQL but there are some reasons you can not avoid MySQL (more on this later). Here are the following steps I followed to get started with MySQL on ubuntu.

Step#1 Update Ubuntu

Before installation of any new package, it is good practice to update the OS.

$ sudo apt-get update
[sudo] password for conquistadorjd:
Hit:1 http://dl.google.com/linux/chrome/deb stable InRelease
Hit:2 http://in.archive.ubuntu.com/ubuntu focal InRelease
Hit:3 http://packages.microsoft.com/repos/code stable InRelease
Get:4 http://in.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Get:5 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Get:6 http://in.archive.ubuntu.com/ubuntu focal-backports InRelease [101 kB]
Get:7 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [990 kB]
Get:8 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [274 kB]
Get:9 http://in.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [323 kB]
Get:10 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [24.5 kB]
Get:11 http://in.archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 DEP-11 Metadata [2,468 B]
Get:12 http://in.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [1,768 B]
Get:13 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 InRelease [4,217 B]
Get:14 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [58.2 kB]
Get:15 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 DEP-11 Metadata [2,464 B]
Fetched 2,009 kB in 3s (575 kB/s)
Reading package lists... Done

Step#2 Install MySQL server

Run the following command to configure some of the important options and also to set

$ sudo apt-get install mysql-server  
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
  libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-
..
..
..
emitting double-array: 100% |###########################################| 
reading /usr/share/mecab/dic/ipadic/matrix.def ... 1316x1316
emitting matrix      : 100% |###########################################| 

done!
update-alternatives: using /var/lib/mecab/dic/ipadic-utf8 to provide /var/lib/mecab/dic/debian (mecab-dictionary) in auto mode
Setting up mysql-server-8.0 (8.0.25-0ubuntu0.20.04.1) ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
mysqld will log errors to /var/log/mysql/error.log
mysqld is running as pid 11200
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.
Setting up mysql-server (8.0.25-0ubuntu0.20.04.1) ...
Processing triggers for systemd (245.4-4ubuntu3.6) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.2) ...

Once installation is done, you can check the if MySQL process has started using following command

systemctl status mysql  
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2021-05-30 10:46:34 IST; 13s ago
   Main PID: 11439 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 18908)
     Memory: 334.3M
     CGroup: /system.slice/mysql.service
             └─11439 /usr/sbin/mysqld

May 30 10:46:34 nova systemd[1]: Starting MySQL Community Server...
May 30 10:46:34 nova systemd[1]: Started MySQL Community Server.

You can check the version of MySQL using following command

$ mysql -V
mysql  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Step#3 Secure MySQL installation

Once installation is completed, you need to set configuration parameters using below command. This will also set up your password policy and root password. Please use this with sudo

$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Please set the password for root here.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL 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? (Press y|Y for Yes, any other key for No) : n

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

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

Even though we have setup root password in above step, we will not be able to login using root password.

ubuntu@ip-172-31-35-254:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I don’t know the exact reason, but possibility due to type of access or security reasons.

mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+

Step#4 User

After installation default userid and password can be viewed by using following command

$sudo cat /etc/mysql/debian.cnf

[client]
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock

Now Login using above userid and password and run following command to start using root user.

ubuntu@ip-172-31-35-254:~$ mysql --host=localhost --user=debian-sys-maint --password=XXX
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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.

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

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword@41';
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye

Now MySQL server installation and root access, you have full authority to create new users or databases as per your requirement.

Hope this is helpful. If you need any clarification, feel free you let me know. Thanks !