Sql Max Upload Size Increase in Phpmyadmin Server
MAMP is gratis software y'all can use to install localhost environment for testing and developing WordPress sites. In our earlier article, nosotros have explained how to move live WordPress site to your MacBook using MAMP. One of the of import steps in moving the live site is to upload your database into MAMP server. It will work seamlessly when you take smaller database. However, uploading large database of few hundreds MB in size will non work through phpMyAdmin. In this article, let us explain how to upload large MySQL database in MAMP using Last app in macOS.
Uploading Limitations in phpMyAdmin
The database upload through phpMyAdmin has limitation of maximum file size. Launch your MAMP application and click on the "WebStart page" if it is not opened automatically.
The webstart page will testify all the details of your local server installation. Navigate to the PHP section and click on the phpinfo link.
Search for "post_max_size" parameter. As yous can run across in the below example, MAMP ready this parameter by default every bit 8MB.
On the webstart folio click on phpMyAdmin link or go to "localhost/phpmyadmin" URL in the address bar. Here y'all tin can see all the databases used in MAMP server and click on the "Import" button. You will encounter the maximum size of the file to import is viii,192KiB which is zip merely 8MB limit prepare in "post_max_size" parameter.
Annotation that the "upload_max_filesize" parameter is also used to limit the upload file size; phpMyAdmin limit is determined from "post_max_size" parameter as it is an upload through HTML class. MAMP will limit the "upload_max_filesize" size to 32MB but phpMyAdmin will nevertheless use the lower limit of 8MB as defined in "post_max_size".
Increasing Limit Using php.ini File
Increasing the PHP parameters is very piece of cake. Create a plain text file, paste the below values and salvage information technology with the name as "php.ini".
upload_max_filesize = 64M post_max_size = 64M max_execution_time = 3000 memorylimit = 512M
Go to "/Applications/MAMP/bin/php/" folder and open the PHP version you are using in MAMP. You can view the PHP version by going to "MAMP > Preferences…>PHP". If you apply vii.2.7 version then open the folder "php7.2.7" and open "conf" folder.
Upload the "php.ini" file here to increment the values in the phpinfo and phpMyAdmin sections. Merely in our experience, this will not help in uploading larger database files of 100+ MB.
Importing Big MySQL Database File
If your live site database size is 100+ MB, so most probably the file import in phpMyAdmin will simply neglect. Also there will bug when you have security and caching plugins that have tables in the database. These plugins volition not allow uploading the database due to missing dependencies or verifications.
So the uncomplicated way to upload the database in MAMP server is to utilize Final command prompt. Follow the below instructions to upload the bigger database files in MAMP server.
Stride ane – Download SQL File
First stride is to download the database file from your live site. You can ask your hosting company to help you lot or employ cPanel backup options or use phpMyAdmin to export the database into SQL dump. Yous can also use plugins to download MySQL database from your WordPress installation. Remember the dumped database file will be in compressed format like .gzip or .zip. Extract the file and go on it in .sql format.
Stride ii – Open up MAMP
Open MAMP application and ensure that the MySQL server is running. Y'all should see "MySQL Server" and "Apache Server" statuses are showing as green in the app.
Pace 3 – Using MySQL in Terminal
Printing "Control + Spacebar" to launch Spotlight Search on your MacBook. Type "terminal" and open Terminal application. Enter the below command and hit enter.
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot
Y'all will a welcome bulletin to "MySQL Monitor" along with password usage alarm and help. The command prompt will change from your username to "mysql>".
The localhost database uses both username and countersign every bit "root". So the uroot and proot in the Last commands are indicating the database username is root and the password is root. You will come across the alarm message for using the password in control line as insecure.
Step 4 – Handling Databases in Terminal
Before uploading the database, yous need to create the aforementioned database proper name and use it in mysql Terminal prompt. Enter the following command to create a database with the name "test_db".
CREATE DATABASE test_db;
Recollect you should create the aforementioned database name that yous download in step 1 from live site. Otherwise you should change the database. Now enter the beneath command to cheque the new database is created successfully.
testify databases;
This control volition show all the databases of the electric current MySQL Server running with MAMP.
Finally enter the beneath command to use the database for importing.
use test_db;
You lot will encounter a messages every bit "Database inverse".
Footstep 5 – Importing Database
Enter the below control to import the database you take download from live server in pace one.
Set up autocommit=0 ; source /Applications/MAMP/htdocs/test_db.sql ; COMMIT ;
In this example, we employ database name as "test_db.sql" which is saved under "/Applications/MAMP/htdocs/" folder. If y'all don't know the path of the database then just enter the following control in Terminal prompt and press spacebar to have a space.
SET autocommit=0 ; source
Open Finder and locate the database file, just drag and drop no the Terminal to add the location of the file. And so you can enter the remaining command ; COMMIT ;to complete it.
Prepare autocommit=0 ; source SQL_database_file_path/database_name.sql ; COMMIT ;
Finish typing the command and hit enter. Terminal volition start dumping the database and show query running status.
Step six – Check the Database
After the Terminal dumping is finished, open phpMyAdmin and check the database is created successfully. In our case, nosotros have "test_db" successfully uploaded in phpMyadmin volition the tables.
Remember after uploading the database yous demand to change the URLs from live server to localhost as explained in this article. After completing all the migration steps, you tin can cheque the localhost site having the unabridged content from the alive site.
Source: https://www.webnots.com/how-to-import-large-mysql-database-in-mamp-using-terminal/
0 Response to "Sql Max Upload Size Increase in Phpmyadmin Server"
Post a Comment