How to import and export a large database using SSH

How to import and export a large database using SSH
by Janeth Kent Date: 25-06-2019 database import export SSH SQL server

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, our suggestion is that you find a reliable, high-performing, secure hosting company that can help you.

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 -pP@55w0rd -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 -pP@55w0rd -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:

by Janeth Kent Date: 25-06-2019 database import export SSH SQL server hits : 15467  
Janeth Kent

Janeth Kent

Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.


Related Posts

How to generate an SSH key and add it to GitHub

In this short tutorial we are going to see how you can generate a new SSH key and add it to GitHub, so you can access your private repositories and…

How to fix excessive MySQL CPU usage

What happens if we realise that a series of databases that we thought were optimised through the use of indexes, have begun to consume CPU usage time of a server…

How to connect to MySQL with Node.js

Let's see how you can connect to a MySQL database using Node.js, the popular JavaScript runtime environment. Before we start, it is important to note that you must have Node.js installed…

htaccess Rules to Help Protect from SQL Injections and XSS

This list of rules by no means is a sure bet to secure your web services, but it will help in preventing script-kiddings from doing some basic browsing around. MySQL injection…

MySQL 8.0 is now fully supported in PHP 7.4

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules…

import one JS file into another in the plain JS

Until some years ago, it was not possible to import one js file or block of code from one file into another but since 2015, ES6 has introduced useful ES6…

A roadmap to becoming a web developer in 2019

There are plenty of tutorials online, which won't cost you a cent. If you are sufficiently self-driven and interested, you have no difficulty training yourself. The point to learn coding…

Setting Up SFTP on Ubuntu 16.04

I recently had a request to setup SFTP for a customer so they could manage a set of files in their environment through an FTP GUI. Being an avid user…

MYSQL: How to create a new user and give it full access only to 1 database

To create a new user in MySQL and give it full access only to 1 database, say dbTest, these are the MySQL commands to do that To create the user: CREATE USER…

ArangoDB, install and configure the popular Database in ubuntu 16.04

Introduction to ArangoDb, open source, NoSQL, multi-model database BigData seems to be getting stronger every day and more and more NoSQL databases are coming out to the market, all trying to position…

How JSON data types work in mysql

First introduced in MySQL 5.7.8, the JSON data type is a specialized binary column type, similar to a blob , but with added machinery to optimize the storage and retrieval…

How to Connect to a Remote Server via SSH from a Linux Shell

Introduction Secure Shell (SSH) is a UNIX-based command interface and protocol for securely getting access to a remote computer. SSH is actually a suite of three utilities - slogin, ssh, and…