Unable to access mysql

Support Software & Applications
1

I had deleted all databases from mysql. Some problem have arisen. I can’t access mysql properly. I had created a mysql database manually.

    ~  mysql -u root                                             ✔  9s  
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.6.3-MariaDB Arch Linux

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> use databases;
ERROR 1049 (42000): Unknown database 'databases'
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| general_log     |
+-----------------+
1 row in set (0.000 sec)

I am using --skip-grant-tables. I had deleted MariaDB multiple times. I had deleted from Add/Remove Software also. But, whenever I install again I just get what I had earlier. Everything isn’t removing properly. Or, I can’t reinstall everything properly. I wanted to hard remove MariaDB. But, I can’t find any other way to remove. I usually use pacman -R.

2 
sudo pacman -Rn mariadb

rm -rf /var/lib/mysql

And reinstall it.

3

I have double checked. Not working (I meant nothing is changing. Everything is happening like earlier)… :frowning:

4

The commands you posted are all working. What is your real problem?

Keep in mind you can only use databases if they exist. And as show databases list, there is no database called databases.

5

The mysql database was created on the installation of Mariadb and if you delete /var/lib/mysql, Mariadb needs to recreated the content. Delete the folder too.

6

I was using --skip-grant-tables that’s why it was working.

7

I had deleted that too…

8

Hi @Istiakshovon,

These two databases are created by MySQL (MariaDB in this case) system upon i8nstallation for it to function correctly, and can’t be removed.

9

What’s the content of the folder after the reinstall (and the content of mysql database) ?

10

Sorry! I had removed mysql not only mysql I had removed information_schema either. When I had reinstalled again and again and again… Then, I got information_schema but, not mysql. I have created that database manually by following a SO answer.

11

AH, OK. Couldn’t remember not seeing it specifically, it looked system-created. So I assumed it was.

But my point still stands for the information_schema database.

12

I tested:

sudo pacman -Rn mariadb && sudo rm -rf /var/lib/mysql

then

sudo pacman -S mariadb
mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
systemctl start mariadb
mysql_secure_installation

That works.

13

ERROR 2002 (HY000): Can’t connect to local server through socket ‘/run/mysqld/mysqld.sock’ (111)

I got above error. Enter current password for root (enter for none): I know that I have installed it now so, there’s no password So, I clicked enter. Then, I got error. I again tried using my root user password. Although, I got same error. Then, I tried root as password.

14

Mariadb is started ?

15

Yes! It is running. I have copy-and-pasted your command. Even, I had run again. Although, same error.

16

I ran exactly theses commands and that worked (don’t forget sudo, I forgot them in some commands in the post). Try again and on mysql_secure_installation, you need sudo potentially.

17

I have run sudo mysql_secure_installation either. But, getting same error

I have run something now…

    ~  mysqld --initialize                                                                                                                  1 ✘ 
2021-08-10  0:01:53 0 [Note] mysqld (mysqld 10.6.3-MariaDB) starting as process 13198 ...
2021-08-10  0:01:53 0 [Warning] Can't create test file /var/lib/mysql/istiak-4291zfv.lower-test
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13 "Permission denied")
2021-08-10  0:01:53 0 [ERROR] Aborting

I think the above error stack-trace may helpful to find problem. I was looking at it (SO answer)

    ~  mysqladmin variables                                                                                                                   ✔ 
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local server through socket '/run/mysqld/mysqld.sock' (111)'
Check that mariadbd is running and that the socket: '/run/mysqld/mysqld.sock' exists!
18

@Slowsaz I have edited my last reply. Pls, take a look at that

19

I remember this error. /var/lib/mysql must be owned by mysql

sudo chown -R mysql:mysql /var/lib/mysql
20

I am still getting the error.

ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (111)

I have started MariaDB either. When I tried mysqld (sudo msyqld) with sudo I got following error.

2021-08-11 16:17:30 0 [Note] mysqld (mysqld 10.6.3-MariaDB) starting as process 3609 ...
mysqld: Please consult the Knowledge Base to find out how to run mysqld as root!
2021-08-11 16:17:30 0 [ERROR] Aborting