Here’s a guide with Windows commands to perform the MySQL database recovery for XAMPP

If you’re getting an error as below due to crash, or sudden power off, follow the steps to recover the database easily.

12:12:12 PM [mysql] Attempting to start MySQL app…
12:12:12 PM [mysql] Status change detected: running
12:12:13 PM [mysql] Status change detected: stopped
12:12:13 PM [mysql] Error: MySQL shutdown unexpectedly.
12:12:13 PM [mysql] This may be due to a blocked port, missing dependencies,
12:12:13 PM [mysql] improper privileges, a crash, or a shutdown by another method
12:12:13 PM [mysql] Press the Logs button to view error logs and check
12:12:13 PM [mysql] the Windows Event Viewer for more clues
12:12:13 PM [mysql] If you need more help, copy and post this
12:12:13 PM [mysql] entire log window on the forums

1. Rename the “mysql/data” folder to “mysql/data_old”:

• Open a Command Prompt window (press the Windows key, type “cmd”, and press Enter).
• Navigate to the XAMPP installation directory using the cd command. For example, if XAMPP is installed in “C:\xampp”, type: cd C:\xampp
• Rename the folder using the ren command: ren mysql/data mysql/data_old

2. Create a new “mysql/data” folder from the “mysql/backup” folder:

• Use the xcopy command to copy the contents of the “backup” folder to a new “data” folder: xcopy mysql/backup mysql/data /s /e

3. Copy specific database folders from “mysql/data_old” to “mysql/data”:

• Use the xcopy command again, but exclude the specified folders: xcopy mysql/data_old mysql/data /s /e /exclude:mysql /exclude:performance_schema /exclude:phpmyadmin

4. Copy the “ibdata1” file from “mysql/data_old” to “mysql/data”:

• Use the copy command: copy mysql/data_old/ibdata1 mysql/data

5. Start MySQL from the XAMPP control panel:

• Open the XAMPP control panel.
• Click the “Start” button next to “MySQL”.
• If you need to recreate users, which you “may” need to, follow the following steps for creating or restoring a user to have access to the database.

Important notes:

• Replace “C:\xampp” with the actual path to your XAMPP installation if it’s different.
• If you encounter any errors or have further questions, refer to the XAMPP documentation or seek assistance from online forums or communities.
• It’s always recommended to create a backup of your MySQL data before making any significant changes.

Creating a User and Granting Privileges in MySQL with Windows Commands

Remember:

• Replace {username} with your desired username.
• Replace {password} with a strong password.
• Replace {database_name} with the specific database name.

1. Open a Command Prompt window:

• Press the Windows key, type “cmd”, and press Enter.

2. Connect to MySQL using the mysql client:

• Type the following command and press Enter:

mysql -u root -p

• Enter your MySQL root password when prompted.

3. Create the user:

• Type the following command and replace the placeholders as needed:

CREATE USER '{username}'@'localhost' IDENTIFIED BY '{password}';

4. Grant privileges to the user:

• There are two options for granting privileges:

a) Grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON {database_name}.* TO '{username}'@'localhost';

b) Grant specific privileges on a specific database:

GRANT SELECT, INSERT, UPDATE, DELETE ON {database_name}.* TO '{username}'@'localhost';

5. Flush privileges to make changes effective:

• Type the following command and press Enter:

FLUSH PRIVILEGES;

6. (Optional) Test the user connection:

• Type the following command and replace the placeholders as needed:

mysql -u {username} -p{password} {database_name}

• Enter your new user’s password when prompted. If the connection is successful, you will be logged in to the specified database.

Additional notes:

• You can further refine the privileges granted to the user by specifying table-level or column-level permissions.
• Consider using a separate user with limited privileges for each application/user accessing the database for increased security.
• Always practice caution when manipulating user privileges and backup your data regularly.