Ubuntu 20.04
Sponsored Link

MySQL 8.0 : Use Clone Feature2020/09/02

 
Use Clone Feature to take MySQL backup which has been implemented on MySQL 8.0.17.
[1] Clone feature is not enabled by default, so enable it.
# MySQL version

root@dlp:~#
mysqld --version

/usr/sbin/mysqld Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))
root@dlp:~#
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# add into [mysqld] section

[mysqld]
.....
.....
plugin-load=mysql_clone.so
root@dlp:~#
systemctl restart mysql
# 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. /home/mysql_backup

# writing to target directory for cloning is denied by AppArmor, so allow it before running

root@dlp:~#
vi /etc/apparmor.d/usr.sbin.mysqld
# add target directory to allow mysqld can write

/usr/sbin/mysqld {
  .....
  .....
  /home/mysql_backup/ r,
  /home/mysql_backup/** rwk,
}

root@dlp:~#
systemctl restart apparmor
# 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:~#
ll /home/mysql_backup/backup01

total 155672
drwxr-x--- 5 mysql mysql     4096 Sep  1 19:20  ./
drwxrwxrwx 3 mysql mysql     4096 Sep  1 19:20  ../
drwxr-x--- 2 mysql mysql     4096 Sep  1 19:20 '#clone'/
-rw-r----- 1 mysql mysql     3319 Sep  1 19:20  ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep  1 19:20  ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep  1 19:20  ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep  1 19:20  ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Sep  1 19:20  mysql/
-rw-r----- 1 mysql mysql 25165824 Sep  1 19:20  mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Sep  1 19:20  sys/
-rw-r----- 1 mysql mysql 10485760 Sep  1 19:20  undo_001
-rw-r----- 1 mysql mysql 10485760 Sep  1 19:20  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


mysql> create user 'clone_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.09 sec)

mysql> 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


mysql> create user 'clone_user'@'%' identified by 'password';
Query OK, 0 rows affected (0.11 sec)

mysql> 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]
mysql> set global clone_valid_donor_list = '10.0.0.30:3306';
Query OK, 0 rows affected (0.00 sec)

mysql> clone instance from clone_user@10.0.0.30:3306 identified by 'password';
Query OK, 0 rows affected (3.00 sec)

# confirm clone status
# OK if [STATE] is [Completed]
mysql> 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]
mysql> clone instance from clone_user@10.0.0.30:3306 identified by 'password' data directory = '/home/mysql_backup/backup01/';
Matched Content