{question}
How to use mysqldump (--lock-tables=false) to export and import a single table?
{question}
{answer}
This topic describes how to export and import a table from SingleStore Database using mysqldump, which you can use because SingleStore DB supports the same query-level inputs and outputs as MySQL. The mysqldump creates a sql file containing the queries necessary to recreate a database/table.
Find the mysqldump reference page here.
Let's look into how we will take Single Table Backup in SingleStore Database using mysqldump tools.
We need to install mysqlclient to use mysqldump utility,
Red Hat Distribution:
$ sudo yum install mysql
You can download the MySQL client package from here
Debian Distribution:
$ sudo apt-get install mysql-client-<version>
Example: sudo apt-get install mysql-client-5.7
Note: You can also use MariaDB client, which also includes mysqldump utility.
Single Table Export using mysqldump:
In order to use the mysqldump tool, you must have access to a server running the instance of SingleStore. You must also have user credentials with the required privileges for the database which you want to export. Also, you can perform the below operation from a remote server as well. Provided that your access to the remote server is not blocked.
All the below-mentioned commands exports both the data and structure of a table.
$ mysqldump -h <SingleStore MA Host Address> -u <Database User> -P <Port> -p<Password> --lock-tables=false --default-auth=mysql_native_password <Database Name> <Table Name> > /path/<Table Name>.sql
Example:
Here we have a Database: retail_demoand Table: customer, We are going to export the customer table.
$ mysqldump -h 192.152.1.1 -u root -P 3306 -pPass@word --lock-tables=false --default-auth=mysql_native_password retail_demo customer > /home/admin/customer.sql
The above command exports a single table in SQL Format like below,
/home/admin $ ls -lrt
total 25M
-rw-r--r-- 1 admin 25M Apr 6 15:36 customer.sql
Single Table Import using mysqldump:
$ mysql -h <SingleStore MA Host Address> -u <Database Username> -P <Port> -p<Password> --default-auth=mysql_native_password <Destination Database Name> < /<path>/<table name>.sql
Now we are restoring the customer table into the retail_demo Database:
$ mysql -h 192.152.1.1 -u root -P 3306 -pPass@word --default-auth=mysql_native_password retail_demo < /home/admin/customer.sql
We can also restore the same table into a different database as below,
For Example:
Created a new database called retail2_demo:
mysql> use retail2_demo;
Database changed
mysql> show tables;
Empty set (0.00 sec)
Now we are going to restore the exported table from the retail_demo database into the retail2_demodatabase, we need to change the database name to retail2_demo as below,
$ mysql -h 192.152.1.1 -u root -P 3306 -pPass@word --default-auth=mysql_native_password retail2_demo < /home/admin/customer.sql
We can view the new table created under retail2_demo database.
mysql> use retail2_demo;
Database changed
mysql> show tables;
+------------------------+
| Tables_in_retail2_demo |
+------------------------+
| customer |
+------------------------+
1 row in set (0.00 sec)
Multiple Tables Export and Import using mysqldump:
Now we are going to export table: customerand table: part from the Database retail_demo and import it into the database retail2_demo.
If you want to export more than one table, then you must separate the names of the tables with space,
$ mysqldump -h <SingleStore MA Host Address> -u <Database User> -P <Port> -p<Password> --lock-tables=false --default-auth=mysql_native_password <Database Name> <Table Name> <Table Name> > /path/<Table Name>.sql
The following command exports the customer and part table of the retail_demo database.
$ mysqldump -h 192.152.1.1 -u root -P 3306 -pPass@word --lock-tables=false --default-auth=mysql_native_password retail_demo customer part > /home/admin/customer_part.sql
Generated SQL file is as below,
$ ls -lrt
total 51M
-rw-r--r-- 1 admin 51M Apr 6 17:03 customer_part.sql
Now we are going to import the customer and part table into a different database retail2_demo.
mysql> use retail2_demo;
Database changed
mysql> show tables;
Empty set (0.00 sec)
The following command imports the customer and part table into the retail2_demo database,
$ mysql -h 192.152.1.1 -u root -P 3306 -pPass@word --default-auth=mysql_native_password retail2_demo < /home/admin/custome_part.sql
We can view the new table created under retail2_demo the database.
mysql> use retail2_demo;
Database changed
mysql> show tables;
+------------------------+
| Tables_in_retail2_demo |
+------------------------+
| customer |
| part |
+------------------------+
2 rows in set (0.00 sec)
Other Frequently used options with mysqldump:
Exporting all databases:
$ mysqldump -h <SingleStore MA Host Address> -u <Database User> -P <Port> -p<Password> --lock-tables=false --default-auth=mysql_native_password --all-databases > /path/<File Name>.sql
Example:
$ mysqldump -h 192.152.1.1 -u root -P 3306 -pPass@word --lock-tables=false --default-auth=mysql_native_password --all-databases > /home/admin/alldatabase.sql
Exporting Single Database:
$ mysqldump -h <SingleStore MA Host Address> -u <Database User> -P <Port> -p<Password> --lock-tables=false --default-auth=mysql_native_password <Database Name> > /path/<Database Name>.sql
Example:
$ mysqldump -h 192.152.1.1 -u root -P 3306 -pPass@word --lock-tables=false --default-auth=mysql_native_password retail_demo > /home/admin/reatil_demo.sql
Exporting Multiple Database:
$ mysqldump -h <SingleStore MA Host Address> -u <Database User> -P <Port> -p<Password> --lock-tables=false --default-auth=mysql_native_password --databases <Database One> <Database two> > /path/<Database Name>.sql
$ mysqldump -h 192.152.1.1 -u root -P 3306 -pPass@word --lock-tables=false --default-auth=mysql_native_password --databases retail_demo retail2_demo > /home/admin/retail_one_two.sql
Note: mysqldump is a 3rd Party Utility. SingleStore doesn't manage this tool. To learn more about mysqldump, Click here
{answer}