MySQL 8.0 : Use Clone Feature2019/11/28 |
|
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@www ~]# /usr/libexec/mysqld --version /usr/libexec/mysqld Ver 8.0.17 for Linux on x86_64 (Source distribution)
[root@www ~]#
vi /etc/my.cnf.d/mysql-server.cnf # add into [mysqld] section [mysqld] ..... .....
plugin-load=mysql_clone.so
[root@www ~]#
systemctl restart mysqld
# show plugins [root@www ~]# mysql -u root -p -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@www ~]# mkdir /home/mysql_backup [root@www ~]# chown 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@www ~]# mysql -u root -p -e "clone local data directory = '/home/mysql_backup/backup01/';"
ll /home/mysql_backup/backup01 total 166916 drwxr-x---. 2 mysql mysql 89 Nov 26 19:55 '#clone' -rw-r-----. 1 mysql mysql 3367 Nov 26 19:55 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Nov 26 19:55 ibdata1 -rw-r-----. 1 mysql mysql 50331648 Nov 26 19:55 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 Nov 26 19:55 ib_logfile1 drwxr-x---. 2 mysql mysql 6 Nov 26 19:55 mysql -rw-r-----. 1 mysql mysql 24117248 Nov 26 19:55 mysql.ibd drwxr-x---. 2 mysql mysql 28 Nov 26 19:55 sys drwxr-x---. 2 mysql mysql 28 Nov 26 19:55 test_database -rw-r-----. 1 mysql mysql 10485760 Nov 26 19:55 undo_001 -rw-r-----. 1 mysql mysql 10485760 Nov 26 19:55 undo_002 |
| [3] | If SELinux is enabled, it needs to change policies for target directory. If you get clone data under [/home] like this example, change like follows. |
|
[root@www ~]#
vi mysqld-clone.te # create new
module mysqld-clone 1.0;
require {
type mysqld_t;
type user_home_dir_t;
class dir { add_name create remove_name write };
class file { create getattr open read unlink write };
}
#============= mysqld_t ==============
allow mysqld_t user_home_dir_t:dir { add_name create remove_name write };
allow mysqld_t user_home_dir_t:file { create getattr open read unlink write };
checkmodule -m -M -o mysqld-clone.mod mysqld-clone.te checkmodule: loading policy configuration from mysqld-clone.te checkmodule: policy configuration loaded checkmodule: writing binary representation (version 19) to mysqld-clone.mod [root@www ~]# semodule_package --outfile mysqld-clone.pp --module mysqld-clone.mod [root@www ~]# semodule -i mysqld-clone.pp |
| [4] | 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@www ~]# mysql -u root -p Enter password: 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 -u root -p Enter password: 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.31:3306'; Query OK, 0 rows affected (0.00 sec) mysql> clone instance from clone_user@10.0.0.31:3306 identified by 'password'; Query OK, 0 rows affected (2.64 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.31:3306 identified by 'password' data directory = '/home/mysql_backup/backup01/'; |
| Sponsored Link |
|
|