Cara menggunakan mysql workbench export database

Untuk Workbench 6.0

Buka meja kerja MySql. Untuk mengambil cadangan basis data, Anda perlu membuat New Server Instance (Jika tidak tersedia) di dalam Server Administration.

Langkah-langkah untuk Membuat New Server Instance:

  1. Pilih New Server Instance pilihan di dalam Server Administrator.
  2. Berikan detail koneksi.

Setelah membuat instance server baru, itu akan tersedia dalam daftar Server Administration. Klik dua kali pada instance Server yang telah Anda buat OR Klik pada opsi Manage Import/Export dan Pilih Server Instance.

Sekarang, Dari opsi DATA EXPORT/RESTORE pilih DATA EXPORT, Pilih Skema dan Objek Skema untuk cadangan.

Anda dapat mengambil file cadangan dengan cara yang berbeda seperti yang diberikan di bawah ini-

T.1) File cadangan (.sql) berisi pernyataan Buat Tabel dan Masukkan ke dalam Pernyataan Tabel

ANS:

  1. Pilih Mulai Opsi Ekspor

T.2) File cadangan (.sql) hanya berisi Buat Pernyataan Tabel, bukan Masukkan ke dalam pernyataan Tabel untuk semua tabel

ANS:

  1. Pilih opsi Skip Table Data(no-data)

  2. Pilih Mulai Opsi Ekspor

T.3) File cadangan (.sql) hanya berisi Sisipkan ke dalam Pernyataan Tabel, bukan Buat pernyataan Tabel untuk semua tabel

ANS:

  1. Pilih Tab Opsi Tingkat Lanjut, Di Dalam Tables Panel- pilih opsi no-create info-Do not write CREATE TABLE statement that re-create each dumped table.
  2. Pilih Mulai Opsi Ekspor

Untuk Workbench 6.3

  1. Klik pada tab Manajemen di sisi kiri di Panel Navigator
  2. Klik pada Opsi Ekspor Data
  3. Pilih Skema
  4. Pilih Tabel
  5. Pilih opsi yang diperlukan dari dropdown di bawah daftar tabel sesuai kebutuhan Anda
  6. Pilih Sertakan kotak centang Buat skema
  7. Klik pada opsi Advance
  8. Pilih kotak centang masukkan Lengkap di Sisipan Panel
  9. Mulai Ekspor
    Cara menggunakan mysql workbench export database

Back to: MySQL Tutorials for Beginners and Professionals

Table of Contents

  • How do I export a database script in MySQL Workbench?
  • How do I get SQL script from MySQL Workbench?
  • How do I create a script in MySQL?
  • How do you create a table script in MySQL Workbench?

In this article, I am going to discuss how to Export and Import MySQL Database using MySQL Workbench. Please read our previous article, where we discussed how to Insert, Update and Delete Data Rows using MySQL Workbench.

Export MySQL database using MySQL Workbench:

In order to export a database using MySQL Workbench, we should have a database on our MySQL Server. So, let us first create a Database called School and tables Students and StudentAddress with some data. Please use the below SQL Script to create the database, table, and data.

CREATE DATABASE School;
USE School;

CREATE TABLE `School`.`Students`(
  StudentId INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Class VARCHAR(50),
  Age INT
);

INSERT INTO `school`.`students` (StudentId, FirstName, LastName, Class, Age) 
VALUES (1, 'Anurag', 'Mohanty', 'First', 18);
INSERT INTO `school`.`students` (StudentId, FirstName, LastName, Class, Age) 
VALUES (2, 'Priyanka', 'Dewangan', 'Second', 17);
INSERT INTO `school`.`students` (StudentId, FirstName, LastName, Class, Age) 
VALUES (3, 'Hina', 'Sharma', 'First', 19);
INSERT INTO `school`.`students` (StudentId, FirstName, LastName, Class, Age) 
VALUES (4, 'Sambit', 'Monanty', 'Second', 17);

CREATE TABLE `school`.`studentaddress` (
  AddressId INT PRIMARY KEY,
  StudentId INT NOT NULL,
  City VARCHAR(200),
  State VARCHAR(200)
);

INSERT INTO `school`.`studentaddress` (AddressId, StudentId, City, State) 
VALUES (1, 1, 'BBSR', 'Odisha');
INSERT INTO `school`.`studentaddress` (AddressId, StudentId, City, State)
VALUES (2, 2, 'Mumbai', 'Maharashtra');
INSERT INTO `school`.`studentaddress` (AddressId, StudentId, City, State) 
VALUES (3, 3, 'BBSR', 'Odisha');
INSERT INTO `school`.`studentaddress` (AddressId, StudentId, City, State)
VALUES (4, 4, 'Mumbai', 'Maharashtra');

Now let us see how to export the School database. To do so, from the top menu select ‘Server’ and ‘Data Export’ Option as shown in the below image.

It will open the data export settings and options window as shown below. The left section on the window displays all the existing databases on our MySQL database server. When we select a database by clicking on it, it will display all the respected tables under the selected database. We can select one or more database check-boxes to include the database in the Export file. We can also select one or more tables from the right section of this window.

So, let’s select our school database which we created earlier. And in the right-side pane, select the students and studentaddress table as shown in the below image.

In the drop-down setting, we can select ‘Dump Structure Only’, ‘Dump Data Only’ or ‘Dump Structure and Data’ option. The ‘Dump Structure Only’ option will save only the table structure. That is database columns and data types defined by us. While ‘Dump Data Only’ option will save only the inserted rows in the tables. The Dump Structure and Data option will save both data and structure. Let’s choose the ‘Dump Structure and Data’ option to save both table structure and data rows in it as shown in the below image.

Under the Export Options section, you can change the default export path of your choice and I am going with the default one. Also, there are two radio buttons. By selecting the first option that is ‘Export to Dump Project Folder’, MySQL Workbench will save all the tables as separate SQL files under one folder which is useful when you will be importing or restoring the export file one by one table. The second option Export to Self-contained File will store all the databases and tables in a single SQL file. This is a good option when you will be importing all the databases, tables, and data rows by using a single SQL file. We will export the database using both the method, to understand the difference. Let’s export the database using the first option ‘Export to Dump Project Folder’ as shown in the below image.

Finally, click on the start export button to start export functionality as shown in the below image.

Once you click on the start export button it will display the progress bar and log as shown in the below image.

Now, if you go to the location where it saved the file, then you will see multiple SQL files which are nothing but individual tables as shown in the below image.

Now again go to the Server => Data Export window and this time select Export to self-contained file option and click on the Start Export button as shown in the below image.

Once the Export did successfully, go to the folder location where this file saved and you will see under the dumps folder, we have a single dump file that includes all the databases and tables, and data rows as shown in the below image.

Import Database using MySQL Workbench

Before Importing the database, let us first delete the tables which are already there in the school database by executing the below SQL statement.

DROP TABLE school.studentaddress;
DROP TABLE school.students;

Now let’s learn how to import the database and tables using the MySQL workbench. Go to the top ‘Server’ menu and select the ‘Data Import’ option as shown in the below image.

It will open the ‘Data import’ window which is exactly opposite to the data export window as shown in the below image. Here you can either Import from the dump project folder or import by using a single self-contained file.

Here, I am Selecting Import from Dump project folder radio button option, and then I will select the dump project folder location where we saved the file while exporting as shown in the below image.

Once you select the Import from Dump project folder, then click the ‘Load Folder Content’ button to display all the available databases in the ‘project folder’ as shown in the below image.

Next, select the school database from the left pane and select the tables that you want to import from the right-hand side, select the Dump Structure and Data option from the drop-down list, and then click on the Select Tables and finally click on the Start Import button as shown in the below image.

Once you click on the Start Import button, it will import the data and if the import completed successfully, then you will get the below window.

Now again under the schemas option, go to the school database, and under the tables tab clicks ‘refresh’ and you can see students and studentaddress tables appear again as shown in the below image.

In this way, we can export and import a single database table or multiple tables using MySQL Workbench ‘Data Export’ and ‘Data Import’ options.

In the next article, I am going to discuss Data Types in MySQL with Examples. Here, in this article, I try to explain how to Export and Import MySQL Database using MySQL Workbench and I hope you enjoy this Export and Import MySQL Database using MySQL Workbench article.

How do I export a database script in MySQL Workbench?

Select File > Export > Forward Engineer SQL CREATE Script... Enter a location to save the file (optional) and set options to include on the script (such as DROP statements etc), then click Continue.

How do I get SQL script from MySQL Workbench?

To run SQL script in MySQL, use the MySQL workbench. First, you need to open MySQL workbench. Now, File -> Open SQL Script to open the SQL script. Note − Press OK button twice to connect with MySQL.

How do I create a script in MySQL?

Open the MySQL Workbench as an administrator (Right-click, Run as Admin). Click on File>Create Schema to create the database schema. Enter a name for the schema and click Apply. In the Apply SQL Script to Database window, click Apply to run the SQL command that creates the schema.

How do you create a table script in MySQL Workbench?

Start MySQL Workbench. ... .

Click the + button on the right side of the Physical Schemas toolbar to add a new schema. ... .

Double-click Add Table in the Physical Schemas section..

This automatically loads the table editor with the default table name table1 . ... .

Next, add columns to your table..