Introduction
Restoring a large MySQL database through phpMyAdmin on XAMPP can be challenging because PHP configuration limits, such as memory_limit, post_max_size, and max_execution_time may prevent large SQL files from being uploaded or processed successfully.
A simpler approach is to restore the database directly using the MySQL command line. This guide explains how to restore a MySQL database on a Windows machine running XAMPP.
Prerequisites
Before starting, make sure you have:
- XAMPP installed on a Windows machine.
- MySQL service running through XAMPP.
- A MySQL database backup in
.sqlformat. - MySQL username and password, if authentication is configured.
- Access to the Windows Command Prompt.
Implementation
Step 1: Find the MySQL Installation Directory
Open the XAMPP Control Panel.
Under the MySQL section, select:
Config → my.ini
Note down the MySQL installation directory.
Step 2: Open the Windows Command Prompt
Open the Windows Run dialog:
Press Ctrl + R
Enter:
cmd.exe
Press Enter to open the Command Prompt.
Step 3: Move to the MySQL Binary Directory
Navigate to the MySQL execution directory:
cd <mysql directory>/mysql/bin
For example, depending on your XAMPP installation, the directory may look similar to:
cd C:\xampp\mysql\bin
Step 4: Copy the SQL Backup
Copy the SQL database backup file into the MySQL bin directory.
This is optional but makes the restore command easier to execute because you do not need to specify the complete path to the SQL file.
Step 5: Restore the Database
Run the following command:
mysql.exe -u <username> < database.sql
Replace <username> with the MySQL username and database.sql with the name of your SQL backup file.
For example:
mysql.exe -u root < database.sql
If the MySQL user requires a password, you can use:
mysql.exe -u root -p < database.sql
You will then be prompted to enter the MySQL password.
Step 6: Wait for the Restore to Complete
The SQL file will be processed directly by MySQL through the command line.
For a large database, the restore may take some time. Wait until the command completes before closing the Command Prompt.
Conclusion
Restoring a MySQL database through the command line is a practical alternative to phpMyAdmin, especially when working with large SQL backup files.
By accessing the XAMPP MySQL binary directory and using the mysql.exe command, you can restore a database without depending on PHP upload and execution limits such as memory_limit, post_max_size, and max_execution_time.
FAQs
1. Why use the command line instead of phpMyAdmin?
The command line avoids several PHP-related upload and execution limitations that can affect the restoration of large database backups through phpMyAdmin.
2. Where is MySQL installed in XAMPP?
The MySQL installation is commonly located under the XAMPP installation directory, such as:
C:\xampp\mysql
You can verify the actual installation directory from the XAMPP Control Panel.
3. Can I restore a large SQL file using this method?
Yes. Restoring through mysql.exe is particularly useful for large SQL backup files because it does not rely on phpMyAdmin’s web upload mechanism.
4. What does the < symbol do in the restore command?
The < operator redirects the contents of the SQL backup file into the MySQL client, allowing MySQL to execute the SQL statements contained in the file.
5. Can I restore the database with a password?
Yes. Use the -p option:
mysql.exe -u <username> -p < database.sql
Related Articles
- MySQL FIND_IN_SET with Multiple Search Strings – Learn how to use MySQL’s
FIND_IN_SET()function when working with multiple search values.
Read the article - Change WordPress Site URL from Backend Using MySQL/MariaDB – Learn how to update WordPress site URL values directly from the MySQL/MariaDB database.
Read the article
Talk to our experts
Looking for the right technology solution for your business? Our team of experts can help you with development, cloud, DevOps, design, and a wide range of other technology needs. Get in touch with our team here.