Increase Time to Upload Large Sql File Phpmyadmin
when nosotros are working with MySQL database it'due south very obvious that you need to import and export the database at several times. when the .sql file that you need to import is of minor size, the process of import and export is very fast. But when the database file (.sql) file that y'all want to import is of large size of 10Mb, 100Mb or even large size than that 800Mb information technology becomes very time taking the process and sometimes fifty-fifty the browser crash.
On localhost, when we use a MySQL database and use XAMPP server, we will be using Phpmyadmin. it provides a very simple interface to interact with the database and perform different operations. we employ phpmyadmin to import or export the database.
on the Server side, when we use cPanel we get the aforementioned Phpmyadmin to interact with the database and import and consign database. By default, the maximum upload size is (Max: two,048KiB).
If the database file (.sql ) is big size you will become mistake :
"the file size exceeded the maximum size permitted by your PHP configuration"
So here are the 2 ways to upload the large SQL files to MySQL database
i. increasing the PHP upload limit in XAMPP / WAMP
the showtime method is to increase the limit of file upload for PHP in XAMPP / WAMP. This method of increasing the PHP file upload limit is skillful for middle size files eg. files of size 10Mb to 100Mb or sometimes even files up to 200MB considering very large files can hang up the browser.
then follow the below steps to increment the php file upload size in XAMPP :
- notice a file named every bit php.ini, it can be found on your estimator c drive: "C:\xampp\php\php.ini " or sometimes here: "C:\xampp\apache\bin\php.ini "
2. hence open the php.ini file and search for "upload_max_filesize". We can CTRL+F to search for the keyword upload_max_filesize, below is the fastened screenshot. Hence we can increase the upload size to whatever we want. Then In our example, I increased information technology to 56MB
upload_max_filesize =56M .
certainly, It is brash not to increase the upload_max_filesize to very high which tin can ultimately make sometimes system/browser to crash.
iii. next search for "post_max_size", make sure to check your up and downwardly radio push button if you practice not find the variable post_max_size.
increase the value more or equal to upload_max_filesize.
In my case, I increased to 128 MB. post_max_size =128M.
5. restart XAMPP Apache and MySQL and reload phpmyadmin
That's all at present you can import the files of the large size upward-to limit set above.
Likewise to increment the php file upload size in WAMP
similarly, follow the above steps and in php.ini file & search for: "memory_limit" and increase it to 1024M.
probably the memory_limit default value is 128M, increase it to as per required past y'all. In my case, I increase information technology to 128MB
memory_limit=1024M
ii. Another Method is to import the very large database SQL files through command prompt (CMD).
certainly, this method is very helpful when you accept a very heavy SQL file to be imported to any database, even if the SQL file is as large as 1GB the import is done more 100 times faster than importing it through phpmyadmin.
Follow the below steps to import very big sql file through cmd :
- first of all, we need to open the command prompt in administrator privilege.
- furthermore, modify your location to "C:\xampp\mysql\bin>mysql". run command: " cd C:\xampp\mysql\bin>mysql "
- after that yous need to run this command: "mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sq50 " where you have to enter your database user_name and database name. In my case my
DB_USER : root && my
DB_NAME: vintagehome && we demand vh_backup.sql file to import with the complete path.
finally merging both 2nd and third point the whole syntax is :
"C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql "
hither is the code That I execute for importing SQL file:
my database name DB_NAME: vintageh_vintagehome
user name DB_USER: root
DB password: root
SQL file proper name: vh_backup
" mysql -u root -p vintageh_vintagehome < D:\downloads\sql\vh_backup.sql "
furthermore, the password of DB user is required. In my instance, the countersign: root.
above all below are the images of the steps:
the 2nd method is the fastest manner to import the long sql files.
I promise yous sympathise the above instance if there is an event comment here and ane of usa will assistance you. thank you for reading.
This blog is developed by Team Skydevelopers
choatebroateretted.blogspot.com
Source: https://www.skydevelopers.net/blog/2-ways-to-import-large-sql-files-to-mysql-database/
0 Response to "Increase Time to Upload Large Sql File Phpmyadmin"
Post a Comment