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

  Copy

    mysqldump -u root -p [database_name] > [database_name].sql
  

  2. Rename the database while exporting

  Copy

    mysqldump -u root -p [database_name] > [new_database_name].sql 
  

  3. Export to a new directory

  Copy

    mysqldump -u root -p [database_name] > path/[database_name]
  

  4. To dump a database named database_name with its stored procedures and events

  Copy

    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

  Copy

    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

  Copy

    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)

  Copy

    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

  Copy

    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)

  Copy

    sudo service mysql restart
  

  You might also need to edit mysql configuration files to make it work

  Copy

    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

  Copy

    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';