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 [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

 
by Janeth Kent Date: 25-06-2019 database import export SSH SQL server hits : 10512  
 
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 END DEVELOPMENT.

 
 
 

Related Posts

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…

How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04

If your app was written for older versions of MySQL and is not compatible with strict SQL mode in MySQL 5.7, you can disable strict SQL mode. For example, apps…

How to send an e-mail in 1984 - Vintage overdose

How to send an e mail 1980's style. Electronic message writing down the phone line. First shown on Thames TV's computer programme 'Database' in 1984 07/06/1984 Database is an old British…

MySql: How to build a performant search engine

In content-heavy websites, it becomes increasingly important to provide capable search possibilities to help your users find exactly what they’re looking for. The most obvious solution is searching your MySQL…

SQLMAP installation and usage in Ubuntu and Kali linux

Let's talk about the penetration testing using one of the KALI linux tool called SQLMAP. What is SQLMAP? sqlmap is an open source penetration testing tool that automates the process of detecting…

We use our own and third-party cookies to improve our services, compile statistical information and analyze your browsing habits. This allows us to personalize the content we offer and to show you advertisements related to your preferences. By clicking "Accept all" you agree to the storage of cookies on your device to improve website navigation, analyse traffic and assist our marketing activities. You can also select "System Cookies Only" to accept only the cookies required for the website to function, or you can select the cookies you wish to activate by clicking on "settings".

Accept All Only sistem cookies Configuration