MySQL - Exporting and Importing Data

There are times when we may need to have a live environment updated with data from a testing environment. Now we could manually write our select and insert statements, but this becomes increasingly tedious when working with large data sets. It is also very error prone. Luckily, this is a problem solved by exporting and importing the data. The export command is one line of code and the import command is also one line of code.
In MySQL this is accomplished with the mysqldump and mysqlimport commands.

Exporting Data - mysqldump client utility

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

You can pass some optional arguments to the command to specify the user, password and the host. Let's look at the first syntax:

mysqldump --no-tablespaces --column-statistics=0 -u<user> -p<password> -h<host> <db_name> <table_name> > <file_name>.sql

As of MySQL 8.0.21 mysqldump requires the PROCESS privilege. The cleardb user I was given did not have this privilege, so the --no-tablespaces flag became necessary. Additionally, the mysqldump command also queries the column_statistics table from the information_schema. In order to prevent this we needed to pass 0 to the --column-statistics option.

This would create a series of sql statements and store them in an sql file of your choice. The statements found in the file would be able to create an exact copy of the data currently stored in your particular table. The table name is optional and omitting it would simply dump the data from all the tables in the database.

Importing Data

mysqlimport

mysqlimport [options] db_name textfile1 [textfile2 ...]

Once more you can pass optional arguments to the mysqlimport command.

mysqlimport -u<user> -p<password> -h<host> <db_name> <file_name>.sql

The command will strip the extensions from the name of the sql files and attempt to execute the statements contained in the file in reference to the name of the file without the extension. So if the file name is file1.sql the table that would be affected is file1. You can also import more than one files at a time. These files being generated from the mysqldump command.

Error - Access Denied

The import command failed for me because I did not have the rights to the table in the live environment. I was using a shared instance so I don't have a lot of priveleges.

mysql

Get-Content backup.sql | mysql -h <host> -u <user> -p<password> <database>

This alternative first loads the contents of the backup file - in this case it was the result of a database dump. It then passes the data over to the following command through the pipe operator. This in turn executes the contents of the file and reproduces the tables making up the database.

It is worth noting that all these commands were executed via powershell on windows.