Mariadb not working just after install on Ubuntu 16.04

Question

Hi I have installed MariaDB server and mariaDB client, php7.0-mysql on my fresh Ubuntu 16.04 VPS, but after installation I cant login using PhpMyAdmin, when I am trying to login using PHPMyAdmin its always give Access denied for user 'root'@'localhost' error.

here is some info. If try to get status

root@ubuntu-2gb-ams2-01:/var/lib# service mariadb status
● mariadb.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)

When I tried to start get this

# service mariadb start
Failed to start mariadb.service: Unit mariadb.service not found.

I can login and create new database with SSH

root@ubuntu-2gb-ams2-01:/var/lib# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 61
Server version: 10.0.27-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create database protest;
Query OK, 1 row affected (0.00 sec)

If I want to see database then I can see all database

root@ubuntu-2gb-ams2-01:/var/lib# mysql -u root -p -e 'show databases'
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| protest            |
+--------------------+

So its look like Mariadb is installed but cant understand why status showing dead, start/restart not working, front end login not working (using phpmyadmin, can anyone give a clue?

UPDATE: I just created another DB and user, I can login that user and access db using phpmyadmin.

Solution 1

If you check mysql --version it should say something like:

mysql  Ver 15.1 Distrib 10.1.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Then you know you're indeed running mariaDB then you can simply

sudo service mysql restart

and it should be all good!

Worth to lookup the file in /lib/systemd/system/mariadb.service to see that it uses mysqld.

Solution 2

MariaDB is running, it's just that it has disguised itself as MySQL (sneaky). MariaDB is a drop-in replacement for MySQL, so you start the MariaDB monitor using the CLI $ mysql command, etc. All interactions where you specify MySQL directly in some way will actually affect MariaDB. (I don't know what happens if you are also running MySQL!) So to run MariaDB, the command is

$ service mysql start

Again, try this

$ service mysql status

to generate a whole lot of info, including something like this:

...
16 08:19:55 <YR-CMPTR> mysqld[1769]: Version: '10.0.28-MariaDB-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306
...

All apps that normally use MySQL won't even notice that they are interacting with MariaDB.

Solution 3

I believe the status thing is still using MySQL's name for the MariaDB package. Try

service mysql status

This should give you an "active (running)" status.

After migrating from MySQL to MariaDB, I had issues logging in as root from my (development) Chamilo portal (an e-learning platform) in PHP (so connecting through TCP/IP). I could, however, perfectly connect from the command line.

Checking mysql.user, I found that the "plugin" column said "unix_socket". This is all due to the new management of authentication through plugins since MySQL 5.5 and MariaDB 5.2.

I fixed it by re-granting all privileges to root without any plugin mention:

grant all privileges on *.* to root@'127.0.0.1' identified by 'mypass';
flush privileges;

This simply emptied the "plugin" column and I could connect through TCP/IP again.