This blog post discusses using the CLONE INSTANCE command with the safety option DATA DIRECTORY when you do not have enough disk space to store two datasets.
In my previous blog post on the CLONE INSTANCE command, The MySQL Clone Plugin Is Not Your Backup, I mentioned that using the option DATA DIRECTORY helps to avoid situations where you need to re-initialize replica and clone-related settings from scratch in case of the clone operation failure.
MySQL Clone plugin simplifies new replica provisioning but does not simplify server restore after failure unless you are ready to re-install your MySQL instance from scratch.
However, when you clone a replica that already has a huge dataset, you may not have enough space for two datasets: one from the source server and data present on the replica.
Since you decided to clone a replica from another server, you agreed to lose your current data. The only need for the DATA DIRECTORY option is to keep clone-related privileges and settings untouched in case of failure. You may use one of the following strategies to perform the clone operation safely.
Start from scratch
To do this, stop your current server, remove the data directory, initialize it again, connect, and set up clone-related privileges and options. This way, you will have a new instance with a small data directory, so you can use option DATA DIRECTORY without fear of exceeding available disk space.
Keep your existing MySQL schema
If you do not want to re-install your instance, you can remove user data from it instead.
- List all non-system databases with query12SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATAWHERE SCHEMA_NAME NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');
- Remove them one by one. You can use the following stored procedure to do it:123456789101112131415161718192021CREATE PROCEDURE p1()BEGINDECLARE 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: LOOPFETCH c1 INTO dbname;IF done THENLEAVE drop_loop;END IF;SET @temp = CONCAT('DROP DATABASE ', dbname);PREPARE stmt FROM @temp;EXECUTE stmt;END LOOP;CLOSE c1;END
Note! If you store your InnoDB data in the shared tablespace ( innodb_file_per_table=0 ), file ibdatawould not be shrunk, and you won’t be able to free disk space this way.
Cloning the instance
Once you have freed disk space by removing data manually, you can use the CLONE INSTANCE command with the option DATA DIRECTORY.
1 | CLONE INSTANCE FROM ‘clone_user'@'source_host':3306 IDENTIFIED BY 'password' DATA DIRECTORY = '/path/to/custom_dir'; |
In case of a successful clone, you need to finalize it with one extra step: stop your MySQL instance and replace the content of the data directory with the content of the directory you used for the clone operation. After that, start the server.
In case of a clone operation failure, remove cloned data, fix errors, and try again.
Conclusion
Clone operation may fail and force you to perform extra steps by re-initializing the MySQL instance on the replica. To avoid it, use the option DATA DIRECTORY. Clean your existing data before cloning if you do not have enough disk space to store two copies of data.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
