MySQL 8.0 : Use Clone Feature2024/09/03

	Use Clone Feature to take MySQL backup.
[1]	Clone feature is not enabled by default, so enable it.

root@dlp:~ #

vi /usr/local/etc/mysql/my.cnf

# add into [mysqld] section

[mysqld]
.....
.....

plugin-load=mysql_clone.so

# line 13 : if cloning from other hosts, change bind address

bind-address            = 0.0.0.0

root@dlp:~ #

service mysql-server restart

# show plugins

root@dlp:~ #

mysql -e "select plugin_name, plugin_status, plugin_type from information_schema.plugins where plugin_name = 'clone';"

Enter password:
+-------------+---------------+-------------+
| plugin_name | plugin_status | plugin_type |
+-------------+---------------+-------------+
| clone       | ACTIVE        | CLONE       |
+-------------+---------------+-------------+

[2]	Get Clone in a directory on localhost.

# create a directory for clone data

root@dlp:~ #

mkdir /home/mysql_backup

root@dlp:~ #

chown mysql:mysql /home/mysql_backup

# run getting clone
# if specified directory already exists on the filesystem, it will be error,
# so specify new directory for [directory = ***] section

root@dlp:~ #

mysql -e "clone local data directory = '/home/mysql_backup/backup01/';"

root@dlp:~ #

ls -l /home/mysql_backup/backup01

total 3469
drwxr-x---  2 mysql mysql         6 Sep  3 12:58 #clone
drwxr-x---  2 mysql mysql         3 Sep  3 12:58 #innodb_redo
-rw-r-----  1 mysql mysql      5815 Sep  3 12:58 ib_buffer_pool
-rw-r-----  1 mysql mysql 134217728 Sep  3 12:58 ibdata1
drwxr-x---  2 mysql mysql         2 Sep  3 12:58 mysql
-rw-r-----  1 mysql mysql  27262976 Sep  3 12:58 mysql.ibd
drwxr-x---  2 mysql mysql         3 Sep  3 12:58 sys
drwxr-x---  2 mysql mysql         3 Sep  3 12:58 test_database
-rw-r-----  1 mysql mysql  16777216 Sep  3 12:58 undo_001
-rw-r-----  1 mysql mysql  16777216 Sep  3 12:58 undo_002

[3]	To use [CLONE INSTANCE FROM], it's possible to get clone from a remote host directly. On Official documents, clone source is called [Donor] and clone target is called [Recipient].

# on Donor Host, create a user and add [BACKUP_ADMIN] privilege for clone

root@dlp:~ #

mysql


root@localhost [(none)]> create user 'clone_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.09 sec)

root@localhost [(none)]> grant BACKUP_ADMIN on *.* to 'clone_user'@'%';
Query OK, 0 rows affected (0.10 sec)

# on Recipient Host, create a user and add [CLONE_ADMIN] privilege for clone

root@node01:~ #

mysql


root@localhost [(none)]> create user 'clone_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.11 sec)

root@localhost [(none)]> grant CLONE_ADMIN on *.* to 'clone_user'@'%';
Query OK, 0 rows affected (0.04 sec)


# on Recipient Host, run Clone job
# before clone, set [set global clone_valid_donor_list = (Donor IP address:port)]
# clone data is copied under [/var/lib/mysql]
root@localhost [(none)]> set global clone_valid_donor_list = '10.0.0.30:3306';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> clone instance from clone_user@10.0.0.30:3306 identified by 'password';
Query OK, 0 rows affected (0.36 sec)

# confirm clone status
# OK if [STATE] is [Completed]
root@localhost [(none)]> select STATE from performance_schema.clone_status;
+-----------+
| STATE     |
+-----------+
| Completed |
+-----------+
1 row in set (0.00 sec)


# if specify cloned target directory, add [data directory = '***']
# but then it needs to set suitable permission to the target directory like [2], [3]
root@localhost [(none)]> clone instance from clone_user@10.0.0.30:3306 identified by 'password' data directory = '/home/mysql_backup/backup01/';

Matched Content