How to Backup and Restore MySQL Database’s Using MySQL Workbench

MySQL Workbench is a graphical design tool for MySQL databases. It is used for database server administration, database design, creation, security management and server as well as database maintenance.

Install MySQL Workbench on Ubuntu:

sk@sk:~$ sudo apt-get install mysql-workbench

Add Database Server connection

Now open MySQL Workbench from Menu or Dash. The main page of MySQL Workbench will look as shown below.

MySQL Workbench_009

Click on ‘New Server Instance’. Enter remote hostname or IP Address.

Create New Server Instance Profile_010

Now enter the Connection Name to easily identify your database, Select TCP/IP from connection method, hostname and port number and finally enter the database name in the default schema column. Click Next.

Create New Server Instance Profile_002

Enter the database user password. In my case its centos.

Create New Server Instance Profile_003

Click Next.

Create New Server Instance Profile_004

Select Do not use Remote Management and Click Next.

Create New Server Instance Profile_005

Enter Server Instance Name to easily identify your database being backup. Click Finish.

Create New Server Instance Profile_006

Now the remote database connection has been made successfully.

MySQL Workbench_007

Backup Database

Click on Manage Import / Export. It will ask your database user password. Enter the password and click OK.

MySQL Workbench_009

Navigate to Data Export in the Left pane. Select the database to be exported. In my case, i select testdb. Select the tables and define the location where you want to save the database backup. Then click Start Export.

MySQL Workbench_010

It will ask the database user password. Enter the password and click OK.

Export_011

Now the database backup has been created.

MySQL Workbench_012

Import Database

Click on Manage Import / Export in the MySQL workbench Main window. Enter the database user password. Navigate to Data Import/Restore under DATA EXPORT /RESTORE section.

MySQL Workbench_013

Browse to the folder where you have stored the backup file.

Open Directory..._014

Enter the database user password and click Start Import.

MySQL Workbench_016

It will ask the database user password again. Enter the password and you’re done. It will take a while depending upon your database size.

MySQL Workbench_017

You have successfully restored your database backup.