In my case, It seems like I wasnt really able to kill the mysql process, when I run
sudo service mysql stop
ps -ef | grep mysql
The mysql process was always there, it looks like it was blocking the socket file and new mysql process wasnt able to create it itself.
so this helped
cd /var/run
sudo cp mysqld/ mysqld.bc -rf
sudo chown mysql:mysql mysqld.bc/
sudo service mysql stop
sudo cp mysqld.bc/ mysqld -rf
sudo chown mysql:mysql mysqld -R
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
Now Im able to log in database using
mysql -u root
Then to update root password:
UPDATE user SET authentication_string=password('YOURPASSWORDHERE') WHERE user='root';
FLUSH PRIVILEGES;
PS: I had trouble updating root passwod, seems like problem with «auth_socket» plugin, so I had to create new user with full privileges
insert into user set `Host` = "localhost", `User` = "super", `plugin` = "mysql_native_password", `authentication_string` = NULL, `password_expired` = "N", `password_lifetime` = NULL, `account_locked` = "N", `Select_priv` = "Y",
`Insert_priv` = "Y", `Update_priv` = "Y", `Delete_priv` = "Y", `Create_priv` = "Y", `Drop_priv` = "Y", `Reload_priv` = "Y", `Shutdown_priv` = "Y", `Process_priv` = "Y", `File_priv` = "Y",
`Grant_priv` = "Y", `References_priv` = "Y", `Index_priv` = "Y", `Alter_priv` = "Y", `Show_db_priv` = "Y", `Super_priv` = "Y", `Create_tmp_table_priv` = "Y", `Lock_tables_priv` = "Y",
`Execute_priv` = "Y", `Repl_slave_priv` = "Y", `Repl_client_priv` = "Y", `Create_view_priv` = "Y", `Show_view_priv` = "Y", `Create_routine_priv` = "Y", `Alter_routine_priv` = "Y",
`Create_user_priv` = "Y", `Event_priv` = "Y", `Trigger_priv` = "Y", `Create_tablespace_priv` = "Y";
This creates user «super» with no password and then you can connect with mysql -u super
I am using PHP 5.5 and MAMP (downloaded from here):
I have a basic script like this:
<?php
$servername = "127.0.0.1";
$username = "root";
$password = "root";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
and when I run this script I get this error:
PHP Warning: mysqli_connect(): (HY000/2002): Connection refused in /Applications/MAMP/htdocs/test/test.php on line 7
Is there some configuration issue that I need to set up within MAMP or PHP?
Script47
14.3k4 gold badges46 silver badges66 bronze badges
asked Jan 12, 2015 at 15:09
johncorserjohncorser
9,27217 gold badges57 silver badges102 bronze badges
8
In case anyone else comes by this issue, the default port on MAMP for mysql is 8889, but the port that php expects to use for mysql is 3306. So you need to open MAMP, go to preferences, and change the MAMP mysql port to 3306, then restart the mysql server. Now the connection should be successful with host=localhost, user=root, pass=root.
answered Jan 12, 2015 at 15:22
johncorserjohncorser
9,27217 gold badges57 silver badges102 bronze badges
2
Sometimes you need to include mysql db port id in the server like so.
$serverName = "127.0.0.1:3307";
answered Jul 22, 2017 at 5:23
0
For me to make it work again I just deleted the files
ib_logfile0
and
ib_logfile1
.
from :
/Applications/MAMP/db/mysql56/ib_logfile0
Mac 10.13.3
MAMP:Version 4.3 (853)
answered Feb 6, 2018 at 9:49
3
You have to change the mamp Mysql Database port into 8889.
answered Aug 19, 2017 at 11:57
rashedcsrashedcs
3,5962 gold badges40 silver badges41 bronze badges
In my case I was using XAMPP, and there was a log that told me the error. To find it, go to the XAMPP control panel, and click «Configure» for MySQL, then click on «Open Log.»
The most current data of the log is at the bottom, and the log is organized by date, time, some number, and text in brackets that may say «Note» or «Error.» One that says «Error» is likely causing the issue.
For me, my error was a tablespace that was causing an issue, so I deleted the database files at the given location.
Note: The tablespace files for your installation of XAMPP may be at a different location, but they were in /opt/lampp/var/mysql for me. I think that’s typical of XAMPP on Debian-based distributions. Also, my instructions on what to click in the control panel to see the log may be a bit different for you because I’m running XAMPP on an Ubuntu-based distribution of Linux (Feren OS).
answered Jul 27, 2019 at 13:08
StealthDroidStealthDroid
3651 gold badge4 silver badges14 bronze badges
In WAMP, right click on WAMP tray icon then change the port from 3308 to 3306 like this:
answered Feb 14, 2020 at 20:54
gadolfgadolf
1,03511 silver badges19 bronze badges
MySQL worked perfectly until I updated to macOS Big Sur. After I updated to the latest version macOS, MySQL was not working as expected.
I added a port to the MySQL config file(I’m using AMPPS, but does not matter, find your MySQL config file), and it started to work.
/Applications/Ampps/phpMyAdmin/config.inc.php
from
$cfg['Servers'][$i]['port'] = '';
to
$cfg['Servers'][$i]['port'] = '3306';
answered Apr 12, 2021 at 21:10
kbhattakbhatta
4573 silver badges12 bronze badges
1
If you use docker, you may find it useful
I connected mysql container two separate network:
-
in nodejs app network work fine
-
but in phpproject network I had this error
so I got inspect from phpproject network by this command in PowherShell:
docker network inspect phpproject
and use IPv4Address from mysql container instead of localhost or 127.0.0.1 address like this:
$servername = "p:172.22.0.3:3306";
$username = "root";
$password = "secret";
$conn = mysqli_connect($servername, $username, $password,'database_name');
and solved problem.
I hope help for you.
answered Jun 30, 2022 at 11:33
asgharasghar
4375 silver badges5 bronze badges
The error message «SQLSTATE[HY000] [2002] Connection refused» typically indicates that the client was unable to establish a connection to the database server. This can be due to several reasons, such as:
- The database server is not running.
- The database server is not running on the correct host or port.
- There is a firewall blocking access to the database server.
- The credentials (username and password) used to connect to the database are incorrect.
To troubleshoot the problem, you should check the following:
- Verify that the database server is running.
- Check that the database server is running on the correct host and port. You can do this by checking the configuration of your database client (e.g., in a configuration file or your code).
- Check for any firewall rules that may be blocking access to the database.
- Verify that the credentials used to connect to the database are correct. Ensure the username and password are correct, and the user has the necessary permissions to access the database.
If you still have problems after checking these items, there may be a problem with the database server itself or the network connection between the server and the client. In this case, you may need to contact the system administrator or the database administrator for further assistance.
Mon 14th December 2020 By David T. Sadler.
One of the first things you do when creating a new Larvel application is run php artisan migrate to create the application database tables. However you may come across the below message.
$ php artisan migrate
Illuminate\Database\QueryException
SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Temporary failure in name resolution (SQL: select * from information_schema.tables where table_schema = testsite and table_name = migrations and table_type = 'BASE TABLE')
at vendor/laravel/framework/src/Illuminate/Database/Connection.php:678
674▕ // If an exception occurs when attempting to run a query, we'll format the error
675▕ // message to include the bindings with SQL, which will make this exception a
676▕ // lot more helpful to the developer instead of just the database's errors.
677▕ catch (Exception $e) {
678▕ throw new QueryException(
679▕ $query, $this->prepareBindings($bindings), $e
680▕ );
681▕ }
682▕
+33 vendor frames
34 artisan:37
Illuminate\Foundation\Console\Kernel::handle()The cause of this issue is due to a change introduced to the .env.example file. This changed the environment variable DB_HOST from 127.0.0.1 to mysql. The reason for this change is to support [Laravel Sail](https://laravel.com/docs/8.x/sail) which is a Docker development environment for Laravel.
The change means your Laravel application will try and connect to a database server with the hostname of mysql. Unless this exists then the application can’t connect.
To resolve the issue just change the value back to 127.0.0.1
DB_HOST=127.0.0.1Links
Commit that changed .env.example.Laravel SailLaravel — Read More Posts.
I don’t have comments as I don’t want to manage them. You can however contact me at the below address if you want to.
Email david@davidtsadler.com
License
The contents of this site is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
Copyright © 2021 David T. Sadler.
Return to Homepage.
The mysqli_real_connect(): (HY000/2002) Connection Refused error is a common issue that developers face while working with MySQL and PHP. This error occurs when the PHP script is unable to establish a connection with the MySQL server. In this guide, we will walk you through various steps to troubleshoot and resolve this error.
Table of Contents
- Check MySQL Server Status
- Verify MySQL Server Configuration
- Check PHP Configuration
- Inspect Firewall Settings
- Allow Remote Connections
- FAQs
Check MySQL Server Status
The first step in resolving the error is to ensure that the MySQL server is running. You can check the status of the MySQL server using the following commands:
For Linux
sudo systemctl status mysql
Or
sudo service mysql status
For Windows
Open the «Services» application and look for «MySQL» in the list of services. Check if the service is running.
If the MySQL server is not running, start the server using the following commands:
For Linux
sudo systemctl start mysql
Or
sudo service mysql start
For Windows
Right-click on the «MySQL» service in the «Services» application and select «Start».
Source: MySQL Official Documentation
Verify MySQL Server Configuration
If the MySQL server is running, the next step is to verify the server configuration. The configuration file (my.cnf or my.ini) contains settings that the server uses when it starts. The default location of the configuration file varies depending on the operating system.
- Linux:
/etc/mysql/my.cnf - Windows:
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Open the configuration file and ensure that the following settings are correctly configured:
bind-address: This should be set to «127.0.0.1» for local connections or the IP address of the server for remote connections.
bind-address = 127.0.0.1
port: This should be set to the port number on which the MySQL server is listening for connections.
port = 3306
Source: MySQL Official Documentation
Check PHP Configuration
Ensure that your PHP configuration is set up correctly to connect to the MySQL server. Open your PHP script and check the following settings:
Hostname: Make sure the hostname is set to «localhost» for local connections or the IP address of the server for remote connections.
Port: Ensure that the port number matches the one configured in the MySQL server configuration.
Username and Password: Verify that the username and password are correct and have the necessary privileges to connect to the MySQL server.
Source: PHP Official Documentation
Inspect Firewall Settings
Firewalls can block incoming connections to the MySQL server. Check your firewall settings to ensure that the MySQL port is not being blocked.
For Linux
Use the ufw command to check the firewall settings:
sudo ufw status
If the MySQL port is not allowed, add a rule to allow the port:
sudo ufw allow 3306
For Windows
Check the «Windows Defender Firewall» settings and ensure that the MySQL port is allowed.
Source: Ubuntu Official Documentation
Allow Remote Connections
If you are trying to connect to the MySQL server from a remote location, ensure that the server is configured to allow remote connections. Follow these steps to allow remote connections:
Open the MySQL server configuration file (my.cnf or my.ini) and set the «bind-address» setting to the IP address of the server or «0.0.0.0» to allow connections from any IP address.
bind-address = 0.0.0.0
Restart the MySQL server to apply the changes.
Grant the necessary privileges to the user for remote connections:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Source: MySQL Official Documentation
FAQs
Why am I getting the mysqli_real_connect(): (HY000/2002) Connection Refused error?
This error occurs when the PHP script is unable to establish a connection with the MySQL server. The possible reasons behind this error include:
- The MySQL server is not running.
- Incorrect MySQL server configuration.
- Incorrect PHP configuration.
- Firewall blocking the MySQL port.
- Remote connections not allowed on the MySQL server.
How do I check if the MySQL server is running?
Use the following commands to check the status of the MySQL server:
- For Linux:
sudo systemctl status mysqlorsudo service mysql status - For Windows: Open the «Services» application and look for «MySQL» in the list of services.
How do I restart the MySQL server?
Use the following commands to restart the MySQL server:
- For Linux:
sudo systemctl restart mysqlorsudo service mysql restart - For Windows: Right-click on the «MySQL» service in the «Services» application and select «Restart».
How do I check if the MySQL port is being blocked by a firewall?
- For Linux: Use the
sudo ufw statuscommand to check the firewall settings. - For Windows: Check the «Windows Defender Firewall» settings.
How do I enable remote connections on the MySQL server?
Open the MySQL server configuration file (my.cnf or my.ini) and set the «bind-address» setting to the IP address of the server or «0.0.0.0» to allow connections from any IP address.
Restart the MySQL server to apply the changes.
Grant the necessary privileges to the user for remote connections using the following SQL commands:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;