1/7

How to Export/Import a MySQL database via SSH inAWS Lightsail WordPress

rizwanrafiq.com/export-import-a-mysql-database-via-ssh-in-aws-lightsail-wordpress

Suppose you want a database backup of any instances ‘ in AWS Lightsail WordPress ‘

through putty or SSH. For that, first, we need to create an instance. In this article, I am

assuming that you already have a WordPress instance in Lightsail.

1:Download Instance SSH Key

So you need to download the SSH key of this instance. This is an essential step. Follow the

below screenshot to download the SSH key in Lightsail.

2:Install PUTTYgen

Download PUTTYgen through below mentioned link (For SSH), and install this file into

your system.

https://www.puttygen.com/

Now Open PUTTYgen and click on ‘load’ and select the downloaded SSH key file and after

that click on the ‘save private key’ file. Save this file with any name with this (*.ppk)

extension.

2/7

3:Install PUTTY

Download PUTTY through below mentioned link, and after that, install this file into your

system.

https://www.puttygen.com/

Now, Open PUTTY, in ‘Host Name’ input filed you need to write your Lightsail instance’s

Static IP address, now give the name to a session and then save and after that click on

‘Data’ under ‘Connection’ (left bar), write username of your instance in the field ‘Auto-

login username’ (bitnami, ubuntu, etc according to the type of instance ), again go to left

bar and click on ‘SSH'(click on SSH ‘+’ sign) then click on ‘Auth’, now choose Private

Key(*.ppk) file.

OR, If you want to open your AWS Lightsail Instance ‘ PHPMyAdmin ‘ database, and also

you want to check if your Instnace database is available in ‘PHPMyAdmin’ or not, then

follow these steps;

Go to the left bar and click on ‘SSH'(click on SSH ‘+’ sign) then click on ‘Tunnels’, now

add ‘8888’ in the Source post field and add ‘localhost:80’ in the Destination field. Now

click on ‘Session’ on the left bar and now choose your last session (in my case, the last

session was ‘aws session’ ) then click on ‘save’ and now click on ‘open’ then it will show

you a popup and you need to click on “Yes”.

3/7

4: Filezilla or WinSCP

Download Filezilla or WinSCP through this link, and after that, install this file into your

system.

https://filezilla-project.org/download.php

OR

https://winscp.net/eng/download.php

Open Filezilla/WinSCP to connect to your Instance. I’ll choose Filezilla for this work.

Go to the Edit option of Filezilla > open settings > click SFTP > pick the Private key of

your Lightsail Instance (*.ppk).

4/7

5:Filezilla Connection

After that go back to the main page of Filezilla and Write-host (IP/domain), the

username (server name), password (instance password), Port (22), and Quick

connect this.

After that, you will see your Lightsail instance directories are showing. Now, create a new

directory/folder for AWS Lightsail database backup and also copy the entire path with the

new folder.

6:Check Instance Databases through PUTTY

Now back to the PUTTY, write some commands For checking, is this showing all

databases & tables of Lightsail instance,

sudo su

mysql -u root -p -h localhost (root mean USERNAME OF DB, P mean password)

show databases; (show all databases)

use DATABASE NAME; (Backup Database name)

show tables; (show all tables of your backup DB)

exit

7:Command for export Lightsail Instance database

mysqldump -u DATABASE USERNAME -p –h localhost DBNAME –single-

transaction –quick –lock-tables=false > Paste Filezilla path where you save

backup/filename.sql

Note: Must care for the Angle Bracket inside the Command. Don’t change this Angle

Bracket. Otherwise, you can’t take a backup of your AWS Lightsail database tables.

5/7

Commands:

mysqldump -u root -p -h localhost wp_demo –single-transaction –quick –lock-

tables=false > /opt/bitnami/apache2/htdocs/bbbb/backup.sql

enter password

After some time, you will see your database tables will have been created with your given

Filezilla path.

Now, you can delete all tables of your backup database from MySQL and after that, you

can also check your database backup whether it is imported in MySQL or not.

8:Check Database

Again back to the PUTTY, and write below mentioned commands

6/7

sudo su

mysql –u USERNAME –P

Enter Password

show databases;

use DBNAME; (backup database name )

show tables; (now you will see all tables are missing)

exit

9:Command for import Lightsail Instance database

mysql -u USERNAME DB -p –h localhost DBNAME –single-transaction –quick –

lock-tables=false < Address link where you save backup/filename.sql

Note: Must care for the Angle Bracket inside the Command. Don’t change this Angle

Bracket. Otherwise, you can’t import backups of your AWS Lightsail database tables.

Commands:

mysql -u root -p -h localhost wp_demo <

/opt/bitnami/apache2/htdocs/bbbb/backup.sql

Enter password

After some seconds all tables will be successfully imported.

10:Import Database Tables

Now if you want to check if tables have been imported or not, then you can repeat all

previous commands;

sudo su

mysql –u USERNAME –P

Enter Password

show databases;

7/7

use DBNAME; (backup database name)

show tables; (now you will see all tables are again showing)

exit

Related Article: How can we Upload Large Files to AWS S3?