memory_limit<\/code>, post_max_size<\/code>, and max_execution_time<\/code> may prevent large SQL files from being uploaded or processed successfully.<\/p>\n\n\n\nA 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.<\/p>\n\n\n\n![\"\"](\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2016\/09\/image.png\")
<\/a><\/figure>\n\n\n\nPrerequisites<\/h2>\n\n\n\n
Before starting, make sure you have:<\/p>\n\n\n\n
\n- XAMPP installed on a Windows machine.<\/li>\n\n\n\n
- MySQL service running through XAMPP.<\/li>\n\n\n\n
- A MySQL database backup in
.sql<\/code> format.<\/li>\n\n\n\n- MySQL username and password, if authentication is configured.<\/li>\n\n\n\n
- Access to the Windows Command Prompt.<\/li>\n<\/ul>\n\n\n\n
Implementation<\/h2>\n\n\n\nStep 1: Find the MySQL Installation Directory<\/h3>\n\n\n\n
Open the XAMPP Control Panel<\/strong>.<\/p>\n\n\n\nUnder the MySQL<\/strong> section, select:<\/p>\n\n\n\nConfig \u2192 my.ini<\/strong><\/p>\n\n\n\nNote down the MySQL installation directory.<\/p>\n\n\n\n
Step 2: Open the Windows Command Prompt<\/h3>\n\n\n\n
Open the Windows Run dialog:<\/p>\n\n\n\n
Press Ctrl + R<\/code><\/strong><\/p>\n\n\n\nEnter:<\/p>\n\n\n\n
cmd.exe<\/pre>\n\n\n\nPress Enter<\/strong> to open the Command Prompt.<\/p>\n\n\n\nStep 3: Move to the MySQL Binary Directory<\/h3>\n\n\n\n
Navigate to the MySQL execution directory:<\/p>\n\n\n\n
cd <mysql directory>\/mysql\/bin<\/pre>\n\n\n\nFor example, depending on your XAMPP installation, the directory may look similar to:<\/p>\n\n\n\n
cd C:\\xampp\\mysql\\bin<\/pre>\n\n\n\nStep 4: Copy the SQL Backup<\/h3>\n\n\n\n
Copy the SQL database backup file into the MySQL bin<\/code> directory.<\/p>\n\n\n\nThis is optional but makes the restore command easier to execute because you do not need to specify the complete path to the SQL file.<\/p>\n\n\n\n
Step 5: Restore the Database<\/h3>\n\n\n\n
Run the following command:<\/p>\n\n\n\n
mysql.exe -u <username> < database.sql<\/pre>\n\n\n\nReplace <username><\/code> with the MySQL username and database.sql<\/code> with the name of your SQL backup file.<\/p>\n\n\n\nFor example:<\/p>\n\n\n\n
mysql.exe -u root < database.sql<\/pre>\n\n\n\nIf the MySQL user requires a password, you can use:<\/p>\n\n\n\n
mysql.exe -u root -p < database.sql<\/pre>\n\n\n\nYou will then be prompted to enter the MySQL password.<\/p>\n\n\n\n
Step 6: Wait for the Restore to Complete<\/h3>\n\n\n\n
The SQL file will be processed directly by MySQL through the command line.<\/p>\n\n\n\n
For a large database, the restore may take some time. Wait until the command completes before closing the Command Prompt.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Restoring a MySQL database through the command line is a practical alternative to phpMyAdmin, especially when working with large SQL backup files.<\/p>\n\n\n\n
By accessing the XAMPP MySQL binary directory and using the mysql.exe<\/code> command, you can restore a database without depending on PHP upload and execution limits such as memory_limit<\/code>, post_max_size<\/code>, and max_execution_time<\/code>.<\/p>\n\n\n\nFAQs<\/h2>\n\n\n\n1. Why use the command line instead of phpMyAdmin?<\/h3>\n\n\n\n
The command line avoids several PHP-related upload and execution limitations that can affect the restoration of large database backups through phpMyAdmin.<\/p>\n\n\n\n
2. Where is MySQL installed in XAMPP?<\/h3>\n\n\n\n
The MySQL installation is commonly located under the XAMPP installation directory, such as:<\/p>\n\n\n\n
C:\\xampp\\mysql<\/pre>\n\n\n\nYou can verify the actual installation directory from the XAMPP Control Panel.<\/p>\n\n\n\n
3. Can I restore a large SQL file using this method?<\/h3>\n\n\n\n
Yes. Restoring through mysql.exe<\/code> is particularly useful for large SQL backup files because it does not rely on phpMyAdmin’s web upload mechanism.<\/p>\n\n\n\n4. What does the <<\/code> symbol do in the restore command?<\/h3>\n\n\n\nThe <<\/code> operator redirects the contents of the SQL backup file into the MySQL client, allowing MySQL to execute the SQL statements contained in the file.<\/p>\n\n\n\n5. Can I restore the database with a password?<\/h3>\n\n\n\n
Yes. Use the -p<\/code> option:<\/p>\n\n\n\nmysql.exe -u <username> -p < database.sql<\/pre>\n\n\n\nRelated Articles<\/h2>\n\n\n\n\n- MySQL FIND_IN_SET with Multiple Search Strings<\/strong> \u2013 Learn how to use MySQL’s
FIND_IN_SET()<\/code> function when working with multiple search values.
Read the article<\/a><\/li>\n\n\n\n- Change WordPress Site URL from Backend Using MySQL\/MariaDB<\/strong> \u2013 Learn how to update WordPress site URL values directly from the MySQL\/MariaDB database.
Read the article<\/a><\/li>\n<\/ul>\n\n\n\nTalk to our experts<\/h2>\n\n\n\n