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/';"
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/'; |
Sponsored Link |
|