Just like we said in the previous post: How to Backup MySQL Database, mysqldump exports a set of executable SQL statements that can recreate the whole database if necessary. We can save the outputs as a SQL file which is a plain text file for any future restorations. Now here comes the question: How can we execute SQL file in the command line?
Execute SQL File
There're two ways to execute SQL file, one is within mysql command line, the other is under mysql prompt.
Within mysql Command
In this way, we don't have to enter interactive mode of mysql utility, just execute SQL file in one command line. Where a less than operator < is used to read the file and redirect to mysql command.
[root@test ~]# mysql -u root -p'12345678' -D example_db < example_db_data_routines.sql
mysql: [Warning] Using a password on the command line interface can be insecure.Under mysql Prompt
Mysql prompt is an interactive mode. We use it when we need to execute multiple SQL files sequentially. Please use keyword source to execute the file.
[root@test ~]# mysql -u root -p'12345678' -D example_db
...
mysql> source /path/to/files/example_db_data_routines.sqlPlease note the following two things. First, both absolute path and barely file name are acceptable in the above command. Second, a lengthy execution result may scroll infinitely on the console when the SQL file is very big. Well, it's the drawback.
Execute Compressed File
We need to unzip the file before we pipe the content to the database.
[root@test ~]# gunzip < example_db_data_routines.sql.gz | mysql -u root -p'12345678' -D example_db
mysql: [Warning] Using a password on the command line interface can be insecure.In the above, we unzip the file and pipe the output stream to mysql utility to import. The rest is the same as "Execute SQL file".