Data export and data import using Mysql dump in Windows

When operating Mysql or MariaDB, data may be backed up or exported. There are also times when data is transferred. The dump function is used at times like this.

In this post, we will learn about data export using mysqldump and data import using dump result files.

Data export dump

A minimum mysql workbench is required for use in Windows. Please refer to the link below for installation instructions.

how to install mysql workbench-download

When the installation is complete, you must run it using mysqldump.exe.

Run command prompts in Windows as administrator. The following commands are sequentially entered at the executed command prompt.

Move mysqldump.exe Installed Directory

cd "C:\Program Files\MySQL\MySQL Workbench 8.0 CE"

Execute dump command

# mysqldump.exe -h [mysql-server-address] -u [user-id] -p --skip-column-statistics --default-character-set=utf8 --single-transaction [database-name] [table] [--where="id>=10 and id<=20"] > [result_dump_file_path]
# example - some data)
mysqldump.exe -h 192.168.0.15 -u test -p --skip-column-statistics --default-character-set=utf8 --single-transaction mydata test_table --where="id>=10 and id<=20" > C:\temp\dump_001.sql
# example - all data)
mysqldump.exe -h 192.168.0.15 -u test -p --skip-column-statistics --default-character-set=utf8 --single-transaction mydata test_table > C:\temp\dump_001.sql

—where=“id>=10 and id<=20” This option is used only when you want to extract data for a certain period of time in the table, and does not need to be used when you dump the entire data.

Given the -p option, I am asked for the password after execution. At this time, it is executed by entering the password.

Additional Options

During dump, the create database syntax, create table syntax, and drop table syntax are stored by default. To exclude this, you need to add the option below.

—no-create-db : CREATE DATABASE without syntax
—no-create-info : CREATE TABLE without synta
—skip-add-drop-table : drop table without syntax

Dump data import

Now let’s learn how to import dump files.

# mysql -h [mysql-server-address] -u [user-id] -p [database-name] < [export_dump_file_path]
# example)
mysql -h 192.168.0.15 -u test -p mydata < C:\temp\dump_001.sql

Given the -p option, I am asked for the password after execution. At this time, it is executed by entering the password.

precautions

When dump-data-export, the single-transaction option must be given to prevent table lock.

When importing dump data, the table is locked, so work should be carried out considering this.