I want to upload MySQL database systm_db.sql which was extracted from localhost and it's of size 72MB.
Now, I want to upload it on remote server by using it's cPanel. Is there any way to modifiy cPanel's configuration like following:
memory_limit =750M
post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 3000
max_input_time = 3000
I successfully implemented & uploaded my database on localhost using above configuration, but I am unable to do like this.
you can zip file .sql before upload to server. we excecute zip file. :D
The following code snippets will allow you to import and export a large database using SSH commands.
To get SSH access to your hosts server you will need to contact your current web hosting company. If you are on a shared hosting package you may not be allowed to have SSH access to your hosts server.
To run both of these commands you will need to have MySQL installed on your server.
Export Database
To export the database you need to run the following command.
mysqldump -p -u username -h hostname database_name > dbname.sql
This runs the mysqldump command with a number of parameters. The first parameter is -p which means password, when you run this command the script will ask you to enter your database password. If you want to do this in one line then you type in the password after the -p without any spaces.
mysqldump -pP@55w0rd -u username -h hostname database_name > dbname.sql
The next parameter is -u which means username, this will be the username you use to access your database. The next parameter is -h for host, you only need to use this if your database is on a different server, if you have your database on the current server then you do not need this parameter.
Next you type in the database name that you want to export, followed by a > for export and then the name and location of the file you are going to export this data into. In this example it just puts the file in dbname.sql which will place the file in the current location you are in, if you want to put these in a certain folder you need to provide the full folder path.
Import A Database
With the SQL file that you have just exported you can now easily import this into any database that you want by using the following code snippet.
First you need to make sure that they file you want to import is of a .sql format, then upload this file to your server so that you have access to it from an SSH command. Next make sure that the database you want to import this file into has been created, now you will able to import the database.
mysql -p -u username -h hostname database_name < /var/www/vhosts/website/backup/dbname.sql
This time you are running the mysql command with the same parameters, -p for password again if you want to put the password in this one command you can do or you will be asked for the password when you run this command. Next parameter is -u for the username of the database, -h for the host if the database is on a different server, next is the name of the database you want to import the SQL file. The next parameter is a < for import and then the location of the file you want to import.