XAMPP MySQL deactivated error

Installing XAMPP is a quick way to setup an Apache Server with PHP and a MySQL database.

But sometimes there's some kind of configuration error or bug that doesn't allow to start the MySQL service. Checking the status with the XAMPP control panel will display a message of "MySQL database DEACTIVATED".

How to fix the XAMPP MySQL deactivated bug

While I'm not sure about the causes of this error, there is a quick fix to activate MySQL in XAMPP following 2 simple steps.

  • First, try not to configure your MySQL database as a service. This is asked during the XAMPP installation. So don't select the checkbox of installing MySQL databases as a system service.
  • Second, create a basic configuration file for your new MySQL installation (you can do this automatically using Win MySQL admin).

Yes, there's an automated way to generate a basic config file for MySQL using tools installed by default in XAMPP. Just go to [XAMPP installation folder] / mysql / bin and run winmysqladmin.exe. Type a new username and password, and let the MySQL admin program generate the default mysql initialization file.

And that's it! A MySQL disabled as a Windows service, and a default configuration file with MySQL username and password defined should change your XAMPP SQL status from "MySQL database deactivated" to MySQL database ACTIVATED.

How to check that MySQL status is active

You can check the status of the MySQL databases opening the XAMPP control panel: MySQL should have a green running tag next to it.

Notice that I wouldn't keep using the Win MySQL Admin once you turned your MySQL service active: it's an old, discontinued application that seems to throw random Windows errors. But don't worry: just close the Win MySQL Admin window and keep using the built-in phpMyAdmin from the XAMPP default installation.

You can open the phpMyAdmin menu to check the active status of the MySQL databases from the XAMPP Apache main menu: start the Apache server, open the main server page using your web browser (just type "localhost" by default in your web browser address bar) and click the phpMyAdmin link to check that your MySQL databases are enabled.

I hope this helped you to activate your MySQL databases in XAMPP! Now, have a happy coding and local server testing!

11 comments:

Anonymous said...

hi i am having same problem... my SQL database is deactivated i followed the procedure u said but still its giving the same thing help needed plz....

ESV said...

If you have already created a basic configuration file, then there isn't much I could suggest you to try, as this solves most kind of Xampp MySQL conflicts.

Try to check the following:
- The MySQL port is not in use.
- There are neither antivirus applications nor firewalls blocking the MySQL port.
- Try reseting the root user using the "resetroot.bat" inside the mysql folder of xampp.
- Make sure XAMPP is installed into the default folder (C:\xampp) as well as the MySQL databases (C:\xampp\mysql). On the contrary, there could be some file path issues.
- If everything else failed, make sure there aren't older MySQL installations on your PC. They could be creating a conflict with the newer MySQL install. If you are not sure about this, try uninstalling the MySQL databases, using a registry cleaner software, and then installing XAMPP again.

I hope this helped to solve the problem. If you finally found out the solution, please tell us about it!

Unknown said...

Hello,

I could not find the file winmysqladmin.exe but rather have mysqladmin.exe in my mysql/bin folder. They are not the same files are they? I just ran it but nothing significant happend. Thanks.

ESV said...

True, the last versions of xampp don't bundle a "winmysqladmin.exe" file, but a "mysqladmin.exe" instead.

I thought they would do pretty much the same, but according to your feedback, the new one does not seem to work.

So, in that same folder (... xampp\mysql\bin\), the first step to check is whether do you have a my.ini file or not. If you do, check the variables and paths inside. If you don't, well, you'll be needing a new one, definitely.

Bharat Prasad Satyal said...

I have the same problem here but I could not even run mysql after doing all these things. I have unstalled it multiple times even deleted all the folders on C for mysql. Now in service i can see the name but could not delete values from there and could not solve after installing xampp again and again. Please help.

Unknown said...
This comment has been removed by the author.
Unknown said...

I've tried the procedure but it doesn't work. Is it possible that XAMPP not compatible with Windows 7?

ESV said...

It is possible to install XAMPP on Windows 7. However, you need to have administrator permissions, you need to make sure all necessary ports are free (including port 80 - try the port check feature on XAMPP to verify this point) and you should be installing it on the root path (C:\xampp) since installing it for example on the Program Files (x86) folder would give you some path mapping problems.

As far as I know, there's no 64-bit version of XAMPP, but I've heard that it is still possible to run it on Windows 7 with a default path installation.

cinnamon_g said...
This comment has been removed by the author.
cinnamon_g said...

Hi,

I've just spent a day trying to sort out this exact same problem after installing XAMPP. I also got the same error installing via AMPPS from the softaculous.com websit.

The GOOD news is that I've managed to FIX THE PROBLEM, the following way:

Edit the MYSQL configuration file C:/xampp/mysql/bin/my.ini to include the two lines:


bind-address="127.0.0.1"
enable-named-pipe


in the [mysqld] section.

e.g.


# The MySQL server
[mysqld]
port= 3306
socket= "C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql"
tmpdir="C:/xampp/tmp"
datadir="C:/xampp/mysql/data"
pid_file="mysql.pid"
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error="mysql_error.log"

###############################
#THE NEXT TWO LINES ARE NEEDED#
###############################
bind-address="127.0.0.1"
enable-named-pipe


Also, edit the file:

C:/xampp/myPhpAdmin/config.inc.php

to contain the two lines:


$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['socket'] = 'C:/xampp/tmp/mysql.sock';


After doing these the XAMPP "status" for MYSQL changes to "ACTIVATED" and the phpMyAdmin utility works perfectly.


Of course using the NAMED-PIPE method only works if the CLIENT and SERVER run on the same machine.


Regards
Neil

Nuno Nickson said...

Thanks tutorial really helped me, this way I do not have to reinstall my PC, once again thanks GBU: D