Yephy Wang

Linux import & export MySQL database commands

1st, export the database


1. Export complete data: table structure + data

Under Linux, you can use the mysqldump command to export the database. The syntax is as follows:

mysqldump -u username -p DatabaseName > DatabaseName.sql

The following command can export the data and table structure of abc database:

# /usr/local/mysql/bin/mysqldump -uroot -p abc > abc.sql

After pressing the Enter key, you will be prompted to enter the password, just enter it.

Notice:The mysqldump command path is based on your MySQL installation path.

2.Export only the table structure

If you only need to export the MySQL table structure, you can use the -d option, the format is as follows:

mysqldump -u username -p -d DatabaseName > DatabaseName.sql

The following command can export the table structure of abc database:

#/usr/local/mysql/bin/mysqldump -uroot -p -d abc > abc.sql

 

 

2nd, import the database


1.Import using mysql command

The format for importing a database using the mysql command is as follows:

mysql -u username -p DatabaseName < DatabaseName.sql

The following example imports the backed up database abc.sql into the database:

# mysql -uroot -p123456 < abc.sql

2.Use the source command to import

Using the source command requires that we first log in to mysql and create an empty database:

mysql> create database abc;      # Create database
mysql> use abc;                  # Use the created database 
mysql> set names utf8;           # Set encoding
mysql> source /home/abc/abc.sql  # Import backup database

Note the path to the sql file you backed up.

 

1 Comment

Leave a Comment