{question}
How to use the mysqldump to repartition a database?
{question}
{answer}
Increasing the database number of partition counts requires dropping and recreation of the database. There are two options available to perform this. The first requires the use of BACKUP DATABASE WITH SPLIT PARTITIONS
to double the partition count, which is available from SingleStore 7.1 version.
The second option is using mysqldump, which is applicable with any version of SingleStore. mysqldump can be used to restore the database with desired partition count.
Suppose you have a database customer
and you want to increase the number of partitions. The current partition count is 4 based on default_partitions_per_leaf
Follow the below steps:
1. Stop the workload on the database before taking the backup using mysqldump. You should pause the write workload to avoid losing writes that are not included in the backup. Note: If you want to add leaves, you should add them before stopping the workload on the database. This ensures that restore can run on the final and larger number of leaves, and the restored database can start with balanced partitions. Click here to learn about adding leaves.
2. Perform a backup using mysqldump with the below command,
mysqldump -h <SingleStore MA> -u <User> -P <PORT> -p<Password> --lock-tables=false --default-auth=mysql_native_password DatabaseName > test.sql
Example:
mysqldump -h 192.167.10.1 -u root -P 3306 -pP@ssword --lock-tables=false --default-auth=mysql_native_password customer > customer.sql
3. After the backup has been completed successfully, drop the database. Note: If you have sufficient disk space, then you may optionally restore the backup to a different database first, for example, to verify that the backup can be restored. You can then drop the original database customer
, restore it with its original name from the new backup and then drop testdb
.
mysql> DROP DATABASE customer;
4. Now, let's create a database with the same name and desired number of partitions as below. For Example: Creating a database with 10 partitions.
mysql> CREATE DATABASE IF NOT EXISTS customer PARTITIONS 10;
5. Restore the database using the SQL file created with 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
Example:
$ mysql -h 192.167.10.1 -u root -P 3306 -pP@ssword --default-auth=mysql_native_password customer < /home/admin/customer.sql
6. Use SHOW DATABASE STATUS; command to validate the partition count,
mysql> use customer;
Database changed
mysql> SHOW DATABASE STATUS;
+--------------------------+-----------+
| Key | Value |
+--------------------------+-----------+
| database | customer |
| role | master |
| state | online |
| commit_count | 0 |
| provision_file | 0 |
| oldest_living_snapshot | 0 |
| newest_snapshot | 0 |
| log_type | paged |
| commit_lsn | 0:2 |
| hardened_lsn | 0:2 |
| replay_lsn | 0:0 |
| tail_lsn | 0:2 |
| replicated_committed_lsn | 0:1 |
| term | 100000017 |
| workload_throttled | no |
| partitions | 10 |
+--------------------------+-----------+
16 rows in set (0.00 sec)
Click here to learn more about cluster expansion.
Note: mysqldump is a 3rd Party Utility. SingleStore doesn't manage this tool. To learn more about mysqldump, Click here.
{answer}