Prerequisites

• A server with a Linux-based distribution (we used Ubuntu 22.04)
• User privileges: root or non-root user with sudo privileges

MySQL Can’t connect to local server

The error message “can’t connect to local MySQL server through socket” can appear due to various reasons. One possibility is that the server cannot establish a connection to the MySQL server using its default socket due to an incorrect path to the socket file. Other reasons include incorrect permissions, misconfiguration in the MySQL setup, or the MySQL service is crashing unexpectedly due to a broken database, not enough RAM on the server, etc.

The socket is an endpoint where two processes communicate with each other. When we say the MySQL socket, we are talking about the mysqld.sock file located at /var/run/mysqld/mysqld.sock. Let’s go over the possible solutions for this connecting issue with the local MySQL server.

Let’s go over each possibility – we’re confident your MySQL server will be accessible locally once you try all of these solutions.

Check Your MySQL Service

Sometimes the MySQL service can stop unexpectedly due to various reasons. It may be due to insufficient disk space, not enough RAM, or the server has been rebooted and the MySQL service was not enabled to start on boot previously.

So, when we try to log in to the MySQL console with the following command:

mysql -u root -p

The connection cannot be established, and we will receive the following output:

root@host:~# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

First, we need to check if the MySQL service is up and running with the following command:

systemctl status mysql

If the service is not running, you should receive the following output:

root@host:~# systemctl status mysql
○ mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead)  since Mon 2024-01-08 18:19:37 CST; 13min ago
    Process: 30718 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 30749 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 30749 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"
        CPU: 1.981s

We just need to start and enable the service with the command below:

sudo systemctl start mysql && sudo systemctl enable mysql

After checking the status again, you should receive the following output:

root@host:~# systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2024-01-08 18:32:21 CST; 8min ago
   Main PID: 30950 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4558)
     Memory: 365.3M
        CPU: 5.241s
     CGroup: /system.slice/mysql.service
             └─30950 /usr/sbin/mysqld

Try to log into your server now – it should be working again. If you are still seeing the same error, move onto the next potential solution.

MySQL Socket Permissions

Let’s say that we started and enabled the MySQL service, but we are still experiencing the error message:

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

Now we need to check if the socket file exists and is in the right directory with the correct permissions. Execute the command below:

ls -al /var/run/mysqld/

If you get this output:

root@host:~# ls -al /var/run/mysqld/
drwxr-xr-x  2 root root 120 Jan 8 19:12 .
drwxr-xr-x 32 root root 960 Jan 8 19:12 ..
srwxrwxrwx  1 root root   0 Jan 8 19:12 mysqld.sock
-rw-------  1 root root   6 Jan 8 19:12 mysqld.sock.lock

This output shows us that the permissions are incorrect – the root user owns the files. In this case, we need to set the right permissions:

chown -R mysql:mysql /var/run/mysqld/
chmod 755 /var/run/mysqld/

Once the permissions are set, restart the MySQL service and try to connect again to the MySQL console.

MySQL Configuration File

If you are still not able to connect to the MySQL console, or in other words your MySQL client can’t connect to a local server, then you should check the MySQL configuration file usually located at /etc/mysql/mysql.conf.d/mysqld.cnf – other locations for this include /etc/mysql/my.cnf, or even /etc/my.cnf. Find where your configuration file is and open it using your preferred text editor.

Look for the socket variable and make sure to put the full path of the socket file. If there is no socket variable, add it to your file like so:

socket = /var/run/mysqld/mysqld.sock

Make sure the permissions for that folder are set correctly as well. Save the file, close it and restart the MySQL service. Your MySQL client should be able to connect to your local server.

If none of this is solving the issue and if you have a managed Linux support plan with us, you can ask our support team to help you with your MySQL service. We are available 24/7 and are experts in system configuration, including with MySQL. We will be able to help you with the configuration of MySQL as well as any other issues you’re having with your server.

The post How to Fix MySQL “Can’t connect to local server” appeared first on LinuxHostSupport.

MySQL is an open-source SQL database management system distributed and supported by Oracle Corporation. It is a relational database management system that provides fast, reliable, scalable, and easy usage. MySQL works in client/server or embedded systems.

MariaDB is also a popular open-source relational database management system made by the original MySQL developers. In this tutorial, we are going to install the MariaDB database system and will explain how to reset the root password. Since it is made by MySQL developers, the same commands are applied for both systems.

Installing MariaDB or MySQL and resetting the root password is a straightforward process that may take up to 15 minutes. Let’s get started!

Prerequisites

• A server with Ubuntu 22.04 as OS
• User privileges: root or non-root user with sudo privileges

Step 1. Update the System

Every fresh installation of Ubuntu 22.04 needs to be updated. That’s why we need to update the package to the latest versions available.

sudo apt-get update -y && sudo apt-get upgrade -y

Step 2. Install MariaDB database service.

To install the MariaDB database server, execute the command below.

sudo apt install mariadb-server -y

Start and enable the mariadb.service with the following commands:

sudo systemctl start mariadb && sudo systemctl enable mariadb

Check the status of the mariadb.service

sudo systemctl status mariadb

You should receive the following output:

root@host:~# sudo systemctl status mariadb 
● mariadb.service - MariaDB 10.6.12 database server 
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) 
Active: active (running) since Thu 2023-09-07 09:28:14 CDT; 17s ago 
Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ 
Main PID: 2722 (mariadbd) 
Status: "Taking your SQL requests now..." 
Tasks: 16 (limit: 4558) 
Memory: 61.2M 
CPU: 528ms 
CGroup: /system.slice/mariadb.service └─2722 /usr/sbin/mariadbd

Step 3. Secure MariaDB database service

Next we will secure the MariaDB database service and will set a root password. To do that execute the following command:

mysql_secure_installation

You need to pass these steps with the following options:

Switch to unix_socket authentication [Y/n] Y 
Change the root password? [Y/n] Y 
New password: 
Re-enter new password: 
Password updated successfully! 
Reloading privilege tables.. 
... Success! 
Remove anonymous users? [Y/n] Y 
Disallow root login remotely? [Y/n] Y 
Remove test database and access to it? [Y/n] Y 
Reload privilege tables now? [Y/n] Y 
All done! If you've completed all of the above steps, your MariaDB installation should now be secure. 
Thanks for using MariaDB

After installation login to the MySQL console will not ask for a password and will let you without any password, even though we set it in the previous step.

To set MySQL to ask you for a password execute the following command:

ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('YourStrongPasswordHere');

Now, if you try to log in only with this command: mysql you will get this message:

root@host:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

You need to use the following command and enter your password:

root@host:~# mysql -u root -p 
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 64 Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04 

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)]>

Step 4. Reset MariaDB Root password

In the previous step we set the root password for the MariaDB root user to be YourStrongPasswordHere. We know our root password but in some cases, the admins or developers manage to lose it and need to log in to the MySQL server.

So, to set a new root password we must follow the next steps:

First stop the MariaDB service with the following command:

systemctl stop mariadb

After stopping the service check the status:

systemctl status mariadb

You should get the following output:

root@host:~# systemctl status mariadb 
○ mariadb.service - MariaDB 10.6.12 database server 
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) 
Active: inactive (dead) since Thu 2023-09-07 10:13:33 CDT; 36s ago 
Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ 
Process: 2722 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS) 
Main PID: 2722 (code=exited, status=0/SUCCESS) 
Status: "MariaDB server is down" 
CPU: 1.760s

Now, when the service is stopped we need to start it without permission checking and without networking to prevent other users from connecting in the meantime:

sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand will allow us to use the terminal. Now, execute the following command:

mysql -u root

This will connect us without a password. Flush the privileges first and then change the password with the commands below:

FLUSH PRIVILEGES; 
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStrongPasswordHere'); EXIT;

Now, the password is changed from YourStrongPasswordHere TO NewStrongPasswordHere.

Next we need to kill the process id manually for the mysql service. To find it execute the following command:

ps aux | grep mysqld_safe

You will get output similar to this: root@host:~#

ps aux | grep mysqld_safe 
root 3292 0.0 0.1 14100 5596 pts/0 S 05:18 0:00 sudo mysqld_safe --skip-grant-tables --skip-networking 
root 3293 0.0 0.0 14100 880 pts/1 Ss+ 05:18 0:00 sudo mysqld_safe --skip-grant-tables --skip-networking 
root 3294 0.0 0.0 2888 1732 pts/1 S 05:18 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --skip-networking 
root 3427 0.0 0.0 9208 2288 pts/0 S+ 05:28 0:00 grep mysqld_saf

In our case, the process id is 3292. To kill this process execute the following command:

kill -9 3292

Now, we can start the MariaDB service normally:

sudo systemctl start mariadb

Now, you can use the NewStrongPasswordHere.

Congratulations! You just learned how to reset MySQL or MariaDB Root password on Ubuntu 22.04. Of course, you do not have to do this if you find any difficulties. You can contact our technical support by submitting a support ticket or live chat. We are available 24/7

If you liked this post on how to reset your MySQL or MariaDB root password on Ubuntu 22.04, please share it with your friends on the social networks or simply leave a reply below. Thanks.

The post How to Reset Your MySQL or MariaDB Root Password on Ubuntu 22.04 appeared first on LinuxHostSupport.

The upgrade process should take about 10 minutes if you follow the easy steps described below.

In order to check the current MariaDB version installed on your server, run the following command:

mysql -V
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Upgrade MariaDB from 5.5.x version to 10.2.x version

Before starting with the upgrade procedure, create a backup of your MariaDB databases:

mysqldump -uroot -p --all-databases > /opt/database-dump.sql

Stop the MariaDB service with the following command:

systemctl stop mariadb

Remove MariaDB 5.5.x packages:

yum remove mariadb mariadb-server mariadb-libs

Removing:
 mariadb                                                x86_64                                         1:5.5.56-2.el7                                          @base                                          49 M
 mariadb-libs                                           x86_64                                         1:5.5.56-2.el7                                          @base                                         4.4 M
 mariadb-server                                         x86_64                                         1:5.5.56-2.el7                                          @base                                          58 M
Removing for dependencies:
 net-snmp                                               x86_64                                         1:5.7.2-28.el7                                          @base                                         850 k
 perl-DBD-MySQL                                         x86_64                                         4.023-5.el7                                             @base                                         323 k
 php-mysql                                              x86_64                                         5.4.16-42.el7                                           @base                                         232 k
 postfix                                                x86_64                                         2:2.10.1-6.el7                                          @base                                          12 M

Transaction Summary
===================================================================================================================================================================================================================
Remove  3 Packages (+4 Dependent packages)

Installed size: 125 M
Is this ok [y/N]: y

...

Removed:
  mariadb.x86_64 1:5.5.56-2.el7                                     mariadb-libs.x86_64 1:5.5.56-2.el7                                     mariadb-server.x86_64 1:5.5.56-2.el7

Dependency Removed:
  net-snmp.x86_64 1:5.7.2-28.el7                     perl-DBD-MySQL.x86_64 0:4.023-5.el7                     php-mysql.x86_64 0:5.4.16-42.el7                     postfix.x86_64 2:2.10.1-6.el7

Complete!

Add a new MariaDB repository:

vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Clean the repository cache on your server with:

yum clean all

Install MariaDB 10.2

Install MariaDB 10.2.x version using the following command:

yum install MariaDB-client MariaDB-server

Installing:
 MariaDB-server                                           x86_64                                    10.2.9-1.el7.centos                                           mariadb                                    108 M
Installing for dependencies:
 MariaDB-client                                           x86_64                                    10.2.9-1.el7.centos                                           mariadb                                     48 M
 MariaDB-common                                           x86_64                                    10.2.9-1.el7.centos                                           mariadb                                    155 k
 MariaDB-compat                                           x86_64                                    10.2.9-1.el7.centos                                           mariadb                                    2.8 M
 boost-program-options                                    x86_64                                    1.53.0-27.el7                                                 base                                       156 k
 galera                                                   x86_64                                    25.3.20-1.rhel7.el7.centos                                    mariadb                                    8.0 M

Transaction Summary
===================================================================================================================================================================================================================
Install  1 Package (+5 Dependent packages)

Total download size: 167 M
Installed size: 708 M
Is this ok [y/d/N]: y

...

Installed:
  MariaDB-server.x86_64 0:10.2.9-1.el7.centos

Dependency Installed:
  MariaDB-client.x86_64 0:10.2.9-1.el7.centos         MariaDB-common.x86_64 0:10.2.9-1.el7.centos         MariaDB-compat.x86_64 0:10.2.9-1.el7.centos         boost-program-options.x86_64 0:1.53.0-27.el7
  galera.x86_64 0:25.3.20-1.rhel7.el7.centos

Complete!

Please note, you need to install Postfix and php-mysql packages if you used them prior this upgrade, so run:

yum install postfix php-mysql

Enable MariaDB service to automatically start on server boot:

systemctl enable mariadb

Start the MariaDB service:

systemctl start mariadb

Run the mysql_upgrade command-line command to examine all tables in all MariaDB databases for incompatibilities with the currently installed version of the MariaDB server:

mysql_upgrade

If everything is OK, check the currently installed MariaDB version with:

mysql -V

mysql  Ver 15.1 Distrib 10.2.9-MariaDB, for Linux (x86_64) using readline 5.1

Set the MariaDB root user password and secure MariaDB

There is no MariaDB root user password set by default. Use the following command to set a new MariaDB root user password, remove the anonymous users, secure MariaDB by restricting the remote login for root user and remove the test database.

mysql_secure_installation

That is it. MariaDB has been successfully installed on your server.

If you use one of our Linux Host Support  services, you  can simply ask our expert Linux admins to upgrade MariaDB on your CentOS 7 server for you. They are available 24×7 and will take care of your request immediately.

PS. If you liked this post please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

The post How to Upgrade MariaDB on Centos 7 appeared first on LinuxHostSupport.