Mr. Editor-in-chief Mr. Editor-in-chief • June 5, 2020 • Updated April 24, 2026

MySQL Import & Export

Essential MySQL Import & Export Commands

Export sql (executed in CMD)

  • export a database

    1. Export to the current directory with the same database name
    mysqldump -u root -p [database_name] > [database_name].sql
    2. Rename the database while exporting
    mysqldump -u root -p [database_name] > [new_database_name].sql
    3. Export to a new directory
    mysqldump -u root -p [database_name] > path/[database_name]
    4. To dump a database named database_name with its stored procedures and events
    mysqldump -u root -p --routines --events --databases [database_name] > [database_name].sql

    • export a particular table

    1. Export to the current directory with the same table name
    mysqldump -u root -p [database_name] [table_name] > [table_name].sql
    2. You can rename it, export to a different directory or do both as shown in export a database above

Import sql (executed in MySQL monitor [mysql -u root -p])

  • import a database

    1. If you don't want to import a database overiding the existing database; Or you can skip line one
    mysql> Create database [database_name] (charset=utfmb4);
mysql> use [database_name];
mysql> source [database_name].sql;
    2. The imported database_name can be different from the database_name created or existed (as long as their schemas are the same)

  • import a database table

    1. The imported table_name can be different from the table_name in the current database (as long as their schemas are the same. I will prefer using the same name to avoid the confusions)
    mysql> use [database_name];
mysql> source [table_name].sql;

Access MySQL monitor remotely

  • On to-be-connected computer

    1. Find the IP address
    2. Make sure port (MySQL default is 3306) is open
    3. Create a new user and grant all priveledges to him
    mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'pass1';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
    4. Restart mysql server (Windows: Start => Services => mysql80)
    sudo service mysql restart
    You might also need to edit mysql configuration files to make it work
    sudo nano /etc/mysql/my.cnf 
# Or
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Comment out the below line
# bind-address        = 127.0.0.1

  • On to-connect computer

    1. Connect to a remote MySQL server
    mysql -u user1 -p -h [IP address]
# "h" stands for host (the IP address or hostname of the remote MySQL server)
# We can add an optional `-P <port>` to specify the MySQL port. If the server is using the default port 3306, you can omit this option.

    Additional commands

    mysql> \! clear # Clear the MySQL command line in Windows, `\! cls` or `system cls` will also do the trick.
mysql> select user, host from mysql.user;
mysql> show global variables like 'port'; # This and the next variable name has to be exact, not wild card guesses
mysql> show global variables like 'wait_timeout';
← Back