Cara menggunakan how use mysqldump command

Salah satu komponen penting dalam pembuatan website yaitu database. Database berfungsi untuk menyimpan data yang tersimpan dalam website. Misalnya data user, data transaksi, data penjualan, data artikel yang di posting, data customer dan sebagainya. Ada berbagai jenis database salah satunya MySQL. Dikutip dari DB Engines, MySQL menduduki peringkat kedua setelah Oracle. Ada berbagai cara untuk melakukan export dan import database MySQL yaitu melalui phpMyAdmin dan melalui command line. Silakan baca Cara Import MySQL Melalui phpMyAdmin dan Cara Export MySQL Melalui phpMyAdmin. Nah pada panduan kali ini akan dijelaskan tentang cara export dan import MySQL melalui command line.

Tahap Persiapan

Sebelum mengikuti cara export dan import MySQL, kamu harus tahu beberapa hal berikut ini,
1. Untuk melakukan export dan import MySQL melalui command line, kamu harus bisa mengakses server via SSH. Pastikan kamu tahu cara akses server via SSH melalui Terminal dan PuTTy. Jika belum tahu, silahkan baca Cara Akses SSH. Oiya, untuk melakukan akses SSH, pastikan kamu memiliki minimal Paket Hosting Super.
2. Pastikan kamu telah membuat akun database MySQL melalui cPanel Hosting. Jika belum, silahkan baca Cara Membuat Database MySQL di Hosting
3. Pastikan kamu tahu user dan password MySQL ya.

Cara Export MySQL

Sekarang saatnya melakukan export MySQL yang ada di Hosting melalui command line/ konsol. Silahkan akses server via SSH melalui terminal atau PuTTY. Kamu bisa melakukan export MySQL dengan hasil otput berupa file .sql. Adapun perintahnya yaitu:

mysqldump -u NAMAUSER -p NAMADATABASE > dbexport.sql

Keterangan:
-u : nama username dari database MySQL
-p : menggunakan password
dbexport.sql : file database yang berhasil diexport

Tulis password database lalu tunggu sebentar hingga proses export selesai.
Silahkan ketik ls pada terminal maka file dbexport.sql akan muncul. Nah itulah database MySQL yang telah diexport.

Catatan: dbexport.sql dapat diganti dengan nama database yang akan dieksport

Cara Import MySQL

Nah agar database bisa di olah lagi, maka kamu perlu melakukan import database PostgreSQL. Import database artinya memasukkan database yang telah diexport kedalam database. Adapun cara import MySQL yaitu,

mysql -u NAMAUSER -p NAMADATABASE < dbexport.sql


Tulis password database lalu tunggu sebentar hingga proses import selesai.

Keterangan:
-u : nama username dari database MySQL
-p : menggunakan password
dbexport.sql : file yang berhasil diexport

Cara mudahnya:
Coba perhatikan command > (pada export MySQL) dan command < (pada import MySQL). Command tersebut adalah simbol instruksi Unix.

> adalah STDOUT sedangkan < adalah STDIN.
Jika ingin melakukan export MySQL, silakan tulis > sebelum file export (dbexport.sql)
Jika ingin melakukan import MySQL, silakan tulis < sebelum file export (dbexport.sql)

Kesimpulan

Cara export dan import MySQL di Hosting melalui command line sangat mudah lho. Yang penting kamu harus tahu apa nama database, nama username database dan password database tersebut. Bagi yang terbiasa menggunakan command line, mungkin panduan ini bisa dicoba. Tetapi bagi pemula, Kami sarankan untuk melakukan export dan import MySQL melalui phpMyAdmin saja. Karena lebih mudah dan berbasis GUI. Jadi kamu cukup pilih dan klik tombol yang ada di phpMyAdmin. Jika ada pertanyaan, jangan ragu menghubungi Tim Support DomaiNesia ya!

In this article, I am going to explain different ways to generate the backup in the MySQL database server. As we know, data is a valuable asset to the organization. As database administrators, it is our primary and crucial job to keep the data available and safe. If the system or data center fails, database corruption, and data loss, we must be able to recover it within the defined SLA.

Different database platforms provide various methods to generate the backup and restore the database. Many vendors provide state-of-the-art software and hardware solutions that can help to back up the database, and it can restore the database within the defined RTO and RPO.

Here, we are not going to discuss any third-party vendor’s backup solutions. I am going to explain the native methods that are used to generate the backup of the database. We can generate the backup of the MySQL database using any of the following methods:

  1. Generate the backup using mysqldump utility
  2. Generate Incremental backups using Binary Log
  3. Generate backups using the Replication of Slaves

In this article, I am going to explain how we can use mysqldump to generate the backup of the MySQL database.

Generate backup using mysqldump utility

Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in the XML, delimited text, or CSV format.

This command is easy to use, but the only problem that occurs while restoring the database. As I mentioned, when we generate a backup of the MySQL database, it creates a backup file that contains SQL commands that are necessary to rebuild or restore the database. Now, when we restore the database, the command executes all the SQL Statements to create tables and insert the data. If you have a large database, then the restoration process takes a long time to complete.

Note: By default, mysqldump command does not dump the information_schema database, performance_schema, and MySQL Cluster ndbinfo database.

If you want to include the information_schema tables, you must explicitly specify the name of the database in the mysqldump command, also include the —skip-lock-tables option.

There are lots of options and features that can be used with mysqldump. You can view the complete list of options here. I am going to some of the basic features. Following is the syntax of the mysqldump utility.

1

mysqldump -u [user name] p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

The parameters are as following:

  1. -u [user_name]: It is a username to connect to the MySQL server. To generate the backup using mysqldump, ‘Select‘ to dump the tables, ‘Show View‘ for views, ‘Trigger‘ for the triggers. If you are not using —single-transaction option, then ‘Lock Tables‘ privileges must be granted to the user
  2. -p [password]: The valid password of the MySQL user
  3. [option]: The configuration option to customize the backup
  4. [database name]: Name of the database that you want to take backup
  5. [table name]: This is an optional parameter. If you want to take the backup specific tables, then you can specify the names in the command
  6. “<” OR ”>”: This character indicates whether we are generating the backup of the database or restoring the database. You can use “>” to generate the backup and “<” to restore the backup
  7. [dumpfilename.sql]: Path and name of the backup file. As I mentioned, we can generate the backup in XML, delimited text, or SQL file so we can provide the extension of the file accordingly

Generate the backup of a single database

For example, you want to generate the backup of the single database, run the following command. The command will generate the backup of the “sakila” database with structure and data in the sakila_20200424.sql file.

1

mysqldump -u root -p sakila > C:\MySQLBackup\sakila_20200424.sql

When you run this command, it prompts for the password. Provide the appropriate password. See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Once backup generated successfully, let us open the backup file to view the content of the backup file. Open the backup location and double-click on the “sakila_20200424.sql” file.

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

As you can see in the above image, the backup file contains the various T-SQL statements that can be used to re-create the objects.

Generate the backup of multiple databases or all the databases

For example, you want to generate a backup of more than one database. You must add the —databases option in the mysqldump command. The following command will generate the backup of “sakila” and “employees” database with structure and data.

1

mysqldump -u root -p --databases sakila employees > C:\MySQLBackup\sakila_employees_20200424.sql

See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Similarly, if you want to generate the backup of all the databases, you must use –all-databases option in the mysqldump command. The following command will generate the backup of all databases within MySQL Server.

1

mysqldump -u root -p --all-databases > C:\MySQLBackup\all_databases_20200424.sql

See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Generate the backup of database structure

If you want to generate the backup of the database structure, then you must use the –no-data option in the mysqldump command. The following command generates the backup of the database structure of the sakila database.

1

mysqldump -u root -p --no-data sakila > C:\MySQLBackup\sakila_objects_definition_20200424.sql

See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Generate the backup of a specific table

If you want to generate the backup of a specific table, then you must specify the name of the tables after the name of the database. The following command generates the backup of the actor table of the sakila database.

1

mysqldump -u root -p sakila actor payment > C:\MySQLBackup\actor_payment_table_20200424.sql

If you want to generate the backup of more than one tables, than you must separate the names of the tables with space, the following command generates the backup of actor and payment table of sakila database.

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Generate the backup of database data

If you want to generate the backup of the data without the database structure, then you must use the –no-create-info option in the mysqldump command. The following command generates the backup of data of the sakila database.

1

mysqldump -u root -p sakila --no-create-info > C:\MySQLBackup\sakila_data_only_20200424.sql

See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Let us view the content of the backup file.

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

As you can see in the above screenshot, the backup file contains the various T-SQL statements that can be used to insert data in the tables.

Restore the MySQL Database

Restoring a MySQL database using mysqldump is simple. To restore the database, you must create an empty database. First, let us drop and recreate the sakila database by executing the following command.

1

2

3

4

5

mysql> drop database sakila;

Query OK, 24 rows affected (0.35 sec)

mysql> create database sakila;

Query OK, 1 row affected (0.01 sec)

MySQL>

When you restore the database, instead of using mysqldump, you must use mysql; otherwise, the mysqldump will not generate the schema and the data. Execute the following command to restore the sakila database:

1

mysql -u root -p sakila < C:\MySQLBackup\sakila_20200424.sql

Once command executes successfully, execute the following command to verify that all objects have been created on the sakila database.

mysql> use sakila;
Database changed
mysql> show tables;

See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Restore a specific table in the database

For instance, someone dropped a table from the database. Instead of restoring the entire database, we can restore the dropped table from the available backup. To demonstrate, drop the actor table from the sakila database by executing the following command on the MySQL command-line tool.

1

2

3

mysql> use sakila;

Database changed

mysql> drop table actor;

To restore the actor table, perform the following step by step process.

Step 1 :

Create a dummy database named sakila_dummy and restore the backup of the sakila database on it. Following is the command.

1

2

3

mysql> create database sakila_dummy;

mysql> use sakila_dummy;

mysql> source C:\MySQLBackup\sakila_20200424.sql

Step 2:

Backup the actor table to sakila_dummy_actor_20200424.sql file. Following is the command

1

C:\Users\Nisarg> mysqldump -u root -p sakila_dummy actor > C:\MySQLBackup\sakila_dummy_actor_20200424.sql

Step 3:

Restore the actor table from the “sakila_dummy_actor_20200424.sql” file. Following is the command on the MySQL command-line tool.

1

mysql> source C:\MySQLBackup\sakila_dummy_actor_20200424.sql

Execute the following command to verify the table has been restored successfully.

1

2

3

mysql> use sakila;

Database changed

mysql> show tables;

See the following image:

Cara menggunakan how use mysqldump command
Cara menggunakan how use mysqldump command

Summary

In this article, I have explained how we can use the mysqldump command-line utility to generate the following: