How to import and export a large database using SSH
The following code snippets will allow you to import and export a database Command Line.
To get SSH access to your hosts server you will need to contact your web hosting company.
In case 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 must have MySQL installed on your server.
How to Export the Database
The following example will show you how to export a database into a single SQL file.
IMPORTANT: backup your database regularly!
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 [email protected] -u username -h hostname database_name > dbname.sql
The other parameter is -u which means username, this will be the username you use to access your database. The 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 [email protected] -u username -h hostname database_name > /var/www/vhost
How to Import the 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 of all 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.
original source: paulund.co.uk
- A roadmap to becoming a web developer in 2019
- Setting Up SFTP on Ubuntu 16.04
- MYSQL: How to create a new user and give it full access only to 1 database
- ArangoDB, install and configure the popular Database in ubuntu 16.04
- How JSON data types work in mysql
- How to Connect to a Remote Server via SSH from a Linux Shell
- How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04
- How to send an e-mail in 1984 - Vintage overdose
- MySql: How to build a performant search engine
- SQLMAP installation and usage in Ubuntu and Kali linux
- Install HHVM + Nginx on Ubuntu 14.04
- Optimize MySql On Low Memory Servers