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