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 A Database
The following example will show how you how to export your database into a single SQL file. It is important to backup your database regularly or when you are making changes to the server such as releasing new code. 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.
mysqldump -pP@55w0rd -u username -h hostname database_name > /var/www/vhosts/website/backup/dbname.sql
Compress The Outputted File
When the file is created on your server it could be a huge file and take up a lot of space on the server, as you may not be using this file directly it is good practice to compress the file to save storage. You can do this using gzip like so
gzip -v backup-file.sql
Or you can do this on export of the database.
mysqldump < mysqldump options> | gzip > backup-file.sql.gz
To restore the file you simply need to use the following script.
gunzip -v backup-file.sql.gz
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 php zcat /var/www/vhosts/website/backup/dbname.sql.gz | mysql -u root -p your_database