Copy Data Directly To A MySQL Instance With MySQL Shell
In previous posts, I talked about how we can use MySQL Shell to dump and load data using a multithreaded process and also how to export table data in different formats that can then be imported to a new MySQL instance. This post will discuss how we can copy data directly to another MySQL instance without performing separate dump and load operations.
The Setup
Before starting this demo, I created three new sandbox instances (on ports 4444, 4445, and 4446) using the process outlined in this post. After each sandbox instance was created, I connected to each of them and ran the following SQL command:
If we do not set local_infile
to ON
, we cannot move our data.
Copying Table Data
If we only need to copy a few tables, we would use the util.copyTables()
method. This method takes four arguments.
- The name of the schema from which tables will be copied.
- A list of tables from the schema we wish to copy.
- The connection information for the new MySQL instance.
- An options JSON object.
The fourth argument is optional, and we won’t discuss any options in this post. For more information about the available options, head on over to the documentation.
For this example, I am using the following command:
This command specifies that we are copying the ipsum
table in the mysql_shorts
schema to a new instance running on port 4444 on my local machine. If we wanted to copy more than one table, we would add more table names to the array, which is the second argument.
When I run this command, I see the following output in the console:
After the table is copied, we can verify the new table exists on the new instance. Using MySQl Shell, I connect to the new instance and then run the SQL command:
The result of this query is:
This output looks good as the mysql_shorts
schema now exists in the new instance. To check if the table was copied, we run the following command:
The results of this query are:
We can see that the table ipsum
now exists in the new instance.
Copying Schemas
If we want to copy one or more schemas, we use the util.copySchemas()
method. This method accepts three arguments.
- A list of schemas that we wish to copy.
- The connection information for the new MySQL instance.
- An options JSON object.
The options argument is optional as with util.copyTables()
.
To copy the entire mysql_shorts
schema, we would use the command:
Note that this is a different instance using port 4445 instead of port 4444. If we wanted to copy multiple schemas, we would add items to the array in the first argument.
The console output from running this command looks like this:
We can check this by connecting to this new instance of MySQL and running the query:
The output from this query resembles the following:
We see that the mysql_shorts
schema was created. Now let’s see what tables exist in the new schema by running the query:
The results of this query are:
We can see that all the tables in the mysql_shorts
schema were copied over to the new MySQL instance.
Copying A Complete Instance
We use the util.copyInstance()
method to copy an entire MySQL instance to a new one. This method accepts two arguments.
- The connection information for the new MySQL instance.
- An options JSON object.
The options
argument is optional, as with the other examples above.
When we copy an entire instance, all the schemas are copied except the system schemas, such as information_schema
, mysql
, performance_schema
, and sys
.
To copy my entire local instance to the third sandbox instance, I would use the following command:
The console output from this command resembles:
When the instance copying is complete, we can verify the schemas were copied by connecting to the new instance and running the query:
The results of this query are:
As we can see, all the non-system schemas were copied to the new instance.
Wrap Up
MySQL Shell offers various ways to copy or move data from one instance to another. Some of these require two steps - one that dumps or exports the data and one that loads or imports the data. Using the copy
methods in the util
object, we can copy data from one MySQL instance to another in a single command/step. To get more information about the commands we discussed, head on over to the documentation.
Image by manuelwagner0 from Pixabay
Related Entries
- Running External Scripts in MySQL Shell
- Executing System Commands in MySQL Shell
- Getting Help in MySQL Shell
- Sandboxes in MySQL Shell
- Server Upgrade Checks with MySQL Shell
- MySQL Connection Status with MySQL Shell
- Managing MySQL Shell Configuration Options
- Advanced Data Dump with MySQL Shell
- Advanced Data Load with MySQL Shell
- MySQL Shell CLI Integration
- MySQL Shell Table Export Utility
- Create an InnoDB Replica Set With MySQL Shell
- Using MySQL Shell to Get MySQL Diagnostic Information
- Customizing The MySQL Shell Prompt
- Extending MySQL Shell with Startup Scripts
- Extending MySQL Shell with Plugins