How to import/export large database on Wamp for Windows 10
PHPMyAdmin can be a useful tool when it comes to managing your database. But unfortunately it cannot handle large databases. So you need to revert back to the good old command line to import/export your database and here's how on Wamp for Windows 10.
I use MariaDB on Wamp. If you use MySQL, it's no different.
To import a database:
First open up phpmyadmin and create a new blank database. We need this new database to import your sql file (which contains the database) into it.
To create a new database in phpmyadmin, login (if you have a password) and on the left sidebar with the tree view, click on new. Under the title 'Create database' there is a field, type in a name for your new database. In the dropdown list field next to it, select: utf8_general_ci (or utf8mb4_general_ci). Click on 'create' button to create your new database.
For MariaDB user - Left-click on mampmanager icon -> MariaDB -> MariaDB console
For MySQL user - Left-click on mampmanager icon -> MySQL -> MySQL console
This opens up the console. You will be the root user. The console will prompt you to enter a password. If you did not set a password, you can just leave it blank and press enter key. Otherwise, enter your root password to continue.
After the password prompt, you will be logged into your database console presented with this screen.
Type:
use my_new_database
replace my_new_database with your own name of your newly created database and hit enter key. It should print out a message 'Database changed' on the console.
Next, type:
source full_path_to_your_db/my_new_database.sql
Replace full_path_to_your_db with your own path. for example if your sql database file is in the root of your C: drive, type C:/my_new_database.sql
Note: use forward slash in your path (ie, / )
You can now go check your phpmyadmin to see if the database has been imported.
To export a database:
We can use the mysqldump utility to export any database.
Open up your command prompt (in taskbar search box, type: cmd) and type:
C:\wamp64\bin\mariadb\mariadb10.3.23\bin\mysqldump -uroot -p [database-you-want-to-dump] > [path-to-place-data-dump]\[db_name].sql
If you are using MySQL, then type:
C:\wamp64\bin\mysql\mysql8.0.21\bin\mysqldump -uroot -p [database-you-want-to-dump] > [path-to-place-data-dump]\[db_name].sql
Make sure the database name (such as mariadb10.3.23 or mysql8.0.21) matches your own version. You can check this by navigating to your c:\wamp64\bin\ directory. Another thing, if you did not set a root password for your database server, you can omit the option -p
- Replace [database-you-want-to-dump] with the database name you want to export.
- Replace [path-to-place-data-dump] with the path you want to dump your database sql file to.
- Replace [db_name] with a DB name you want for your sql file (such as backup.sql).
To make things easier, create a directory called backup in C:\wamp64\ This is where we can export any database sql file to.
Note: use backslash in your path (ie, \ ).
An example of what the whole line should look like:
C:\wamp64\bin\mariadb\mariadb10.3.23\bin\mysqldump -uroot -p db-name-to-export > c:\wamp64\backup\backup-v1-date.sql