Using CLONE INSTANCE with DATA DIRECTORY on Replicas When Disk Space Is Limited

This article discusses using the DATA DIRECTORY option with the CLONE INSTANCE command when you do not have enough disk space to store two data sets simultaneously.

In a previous blog post titled "The MySQL Clone Plugin Is Not Your Backup," the author explained that the DATA DIRECTORY option helps avoid re‑initialising a replica from scratch after a failed clone operation.

The MySQL Clone plugin simplifies the configuration of a new replica but does not simplify server recovery after a failure unless you reinstall the MySQL instance.

When cloning a replica that already contains a large data set, you may lack space for both the source data and the replica data. By deciding to clone from another server, you accept the loss of current data, and the only requirement for the DATA DIRECTORY option is to preserve clone‑related privileges and settings if a failure occurs.

1 Start From Scratch

Stop the current server, delete the data directory, re‑initialise it, connect, and set the clone‑related privileges and options. This gives you a new instance with a small data directory, allowing the use of DATA DIRECTORY without exceeding available disk space.

2 Preserve Existing MySQL Schema

If you do not want to reinstall the instance, you can remove user data while keeping the schema.

List all non‑system databases with queries.

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('mysql','sys','information_schema','performance_schema');

Drop them one by one using a stored procedure.

CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE dbname VARCHAR(64);
  DECLARE c1 CURSOR FOR SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE SCHEMA_NAME NOT IN ('mysql','sys','information_schema','performance_schema','test');
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c1;
  drop_loop: LOOP
    FETCH c1 INTO dbname;
    IF done THEN
      LEAVE drop_loop;
    END IF;
    SET @temp = CONCAT('DROP DATABASE ', dbname);
    PREPARE stmt FROM @temp;
    EXECUTE stmt;
  END LOOP;
  CLOSE c1;
END

If you store InnoDB data in a shared tablespace ( innodb_file_per_table=0 ), the ibdata file will not shrink, and you cannot free disk space this way.

3 Clone Instance After Manual Cleanup

After manually deleting data to free space, run the CLONE INSTANCE command with the DATA DIRECTORY option:

CLONE INSTANCE FROM 'clone_user'@'source_host':3306 \
IDENTIFIED BY 'password' \
DATA DIRECTORY = '/path/to/custom_dir';

If the clone succeeds, stop the MySQL instance, replace the data directory contents with those from the clone directory, and start the server again.

If the clone fails, delete the cloned data, fix the error, and retry.

4 Conclusion

Clone operations may fail and force you to re‑initialise the MySQL instance on the replica. To avoid this, use the DATA DIRECTORY option. When disk space is insufficient to hold two copies of the data, clean up existing data before cloning.

References

[1] "The MySQL Clone Plugin Is Not Your Backup" – https://www.percona.com/blog/the-mysql-clone-plugin-is-not-your-backup/

Original article: https://www.percona.com/blog/using-clone-instance-with-data-directory-on-replicas-with-data/

Recommended Reading

Industry Observation | New DBA Challenges in 2023?

Technical Translation | Understanding the New MySQL Version Model

Technical Translation | Differences in Auto‑Increment Counters Between MySQL 8 and 5.7

Technical Translation | MySQL 8.1.0 Introduces InnoDB Cluster Read‑Only Replica

Technical Translation | History and Differences of MySQL and MariaDB Version Management

Technical Translation | XtarBackup 8.0.33‑28 Prepare Speed Boosted 20×!

About SQLE

SQLE is a comprehensive SQL quality management platform covering SQL review and management from development to production, supporting major open‑source, commercial, and domestic databases, providing automation for development and operations, improving release efficiency and data quality.

SQLE Access

🔗 Github: https://github.com/actiontech/sqle

📚 Documentation: https://actiontech.github.io/sqle-docs/

💻 Official site: https://opensource.actionsky.com/sqle/

👥 WeChat group: add admin WeChat ActionOpenSource