MySQL shutdown unexpectedly on XAMPP - Server socket created on IP: '::'

Question

I'm still fairly new to XAMPP and it has been running great until I started work this morning. Here is the error log file information. When I try to start MySQL it turns on and then stops. I'd prefer to not reinstall if needed but can if that's what it takes.

InnoDB: using atomic writes.
2020-06-15  6:53:59 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-06-15  6:53:59 0 [Note] InnoDB: Uses event mutexes
2020-06-15  6:53:59 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-06-15  6:53:59 0 [Note] InnoDB: Number of pools: 1
2020-06-15  6:53:59 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-06-15  6:53:59 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2020-06-15  6:53:59 0 [Note] InnoDB: Completed initialization of buffer pool
2020-06-15  6:54:00 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-06-15  6:54:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-15  6:54:00 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-15  6:54:00 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2020-06-15  6:54:00 0 [Note] InnoDB: 10.4.11 started; log sequence number 8795036; transaction id 3060
2020-06-15  6:54:00 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2020-06-15  6:54:00 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-06-15  6:54:00 0 [Note] Server socket created on IP: '::'.

And here is the my.ini file.

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# You can copy this file to
# C:/xampp/mysql/bin/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:/xampp/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
# password       = your_password 
port=3307
socket="C:/xampp/mysql/mysql.sock"


# Here follows entries for some specific programs 

# The MySQL server
default-character-set=utf8mb4
[mysqld]
port=3307
socket="C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql"
tmpdir="C:/xampp/tmp"
datadir="C:/xampp/mysql/data"
pid_file="mysql.pid"
# enable-named-pipe
key_buffer=16M

Answer 1

Priyesh's answer guided me to the solution (Thank you @Priyesh!! +1). It was a little unclear, though, and I did take a few additional steps to get it working. In the end, the only data I lost was all my DB users.

Specifically, I'm using XAMPP/MariaDB in my case. This may work in other cases too, but I cannot confirm:

---

1. In the /mysql/ folder within XAMPP, rename the /data/ folder to something else (e.g., /old-data/) since it contains all the database data and table descriptions you don't want to lose.

2. Then copy the /backup/ folder that also appears under /mysql/ and paste it again under /mysql/, and rename it to /data/. This /backup/ folder appears to be a completely blank but working database setup.

At this point, mysql should start up again when running it from the XAMPP control panel. If you try to access it with phpmyadmin or an application, note that the username is 'root', and the password is blank (''). In any case, leave it off for now.

3. Next, go into the /old-data/ folder and copy the ibdata1 file and paste it over into the new /data/ folder, replacing the one that's there.

4. Also within the /old-data/ folder is a number of subfolders, each with the name of one of the databases. Since the core databases are already working correctly having been copied from /backup/, copy all but those four database folders over to the new /data/ folder. Specifically, skip the database folders /mysql/, /performance_schema/, /phpmyadmin/, and /test/.

At this point, the data should be restored - everything, except at this point the mysql users likely will be corrupt (I'm using HeidiSQL, and it gives me an error when trying to visit the User Manager).

5. To fix this, go to phpmyadmin, login - again, username: 'root' / password: '' - and click the mysql database. Then, select all tables, and pick "Repair table" from the bulk action menu to repair all the tables. If you're not using XAMPP, you should be able to accomplish this with the SQL REPAIR TABLE mysql.table_name; for each mysql database table.

After this finishes, the users will be reset, but everything should work again. The only data you are likely to lose is your users.

---

I do understand every environment is different, and, depending on the environment, certain things may work while others don't. Thus, if it matters, my environment is,

• XAMPP for Windows 7.4.27
• MariaDB 10.4.22
• Windows 10 (21H2 19044.2006)

(p.s. In my case, I believe mysql became corrupt due to a Windows update)

Answer 2

Caution! Please do NOT delete ibdata1 file! First take backup MySQL of folder 'mysql/data'

1. Take backup all files+folder from folder mysql/data to mysql/data_old_backup

2. Sometime XAMPP MySQL service stopped working, in my case reason in XAMPP control panel: "Error: MySQL shutdown unexpectedly" with the same error log

Solution:

1. I have fixed the same issue, copy (+overwrite) all files+folder from xampp/mysql/backup/ into xampp/mysql/data/
2. You may lost your Mysql root password after step-1, your mysql password is null now.
3. Now, Copy (+overwrite) file ibdata1 from backup folder from "mysql/data_old_backup/ibdata1" to xampp/mysql/data/
   (because you want to restore all your data)
4. Start MySQL from XAMPP control panel.

It should work