LoginSignup
2
0

More than 3 years have passed since last update.

@huato

MySQL 8.0.17のClone Pluginを試す

Last updated at Posted at 2019-07-23

はじめに

huatoです。
MySQL 8.0.17からの新機能、Clone Pluginについてdocker環境上で動作を確認しました。
docker,docker-compose環境は表題と直接関係がないため、ページ最後尾に記載しています

環境

Ubuntu 18.04.2 LTS
CPU:4core
RAM:16GB
Disk:100GB
MySQL 8.0.17

server1:192.168.2.1 donar
server2:192.168.2.2 recipient

参考URL

https://mysqlserverteam.com/the-mysql-8-0-17-maintenance-release-is-generally-available/
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-installation.html
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html

Clone Plugin設定について

LOCAL,REMOTEそれぞれの設定について記載します。

まず、my.cnfにpluginをloadする設定を追記、反映します。
今回は環境起動時のmy.cnfに追記しています。

node1.cnf,node2.cnf

plugin-load-add=mysql_clone.so

mysql -uroot -p

show plugins:
| clone                           | ACTIVE   | CLONE              | mysql_clone.so | GPL     |

LOCALへの複製

リファレンスマニュアルのページ記載内容のコピペではエラー

mysql> GRANT BACKUP_ADMIN ON . TO 'clone_user'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
最近のバージョンではUserを先に作成してから権限付与ですね

【donar】

mkdir backup
mysql -uroot -p 
SET SQL_LOG_BIN=0;
CREATE USER 'clone_user'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'localhost';

複製先のディレクトリを作成して
複製を実行したところ、エラーが出力されました

CLONE LOCAL DATA DIRECTORY = '/backup/';
ERROR 1007 (HY000): Can't create database '/backup/'; database exists

説明を読むと

先にディレクトリが存在すると動作しない
mysqlユーザで複製先ディレクトリへの書き込み権限も必要
datadir以外に配置したテーブルスペースなどは複製対象外

やりなおしです

mkdir db
chown -R mysql:mysql db
mysql -uroot -p
CLONE LOCAL DATA DIRECTORY = '/db/backup/';

複製が取得できていることを確認できました

root@0c7cfb683d99:/# ls -la /db/backup/
total 162844
drwxr-x--- 2 mysql mysql     4096 Jul 23 16:21 #clone
drwxr-x--- 5 mysql mysql     4096 Jul 23 16:21 .
drwxr-xr-x 3 mysql mysql     4096 Jul 23 16:21 ..
-rw-r----- 1 mysql mysql     5404 Jul 23 16:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 Jul 23 16:21 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 23 16:21 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 23 16:21 ibdata1
drwxr-x--- 2 mysql mysql     4096 Jul 23 16:21 mysql
-rw-r----- 1 mysql mysql 29360128 Jul 23 16:21 mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Jul 23 16:21 sys
-rw-r----- 1 mysql mysql 13631488 Jul 23 16:21 undo_001
-rw-r----- 1 mysql mysql 10485760 Jul 23 16:21 undo_002

REMOTEへの複製

動作には要件をいくつか満たす必要があります

donarもrecipient(転送先）もClone Pluginがactiveであること
donarはBACKUP_ADMIN,recipientはCLONE_ADMINが必要
innodb_page_sizeとinnodb_data_file_pathの値がdonarとrecipientで同じであること

【donar】

CREATE USER 'clone_user'@'192.168.2.2' IDENTIFIED BY 'Donar?1';
GRANT CLONE_ADMIN on *.* to 'clone_user'@'192.168.2.2';

【recipient】

SET SQL_LOG_BIN=0;
CREATE USER 'clone_user'@'192.168.2.2' IDENTIFIED BY 'Recipient!';
GRANT CLONE_ADMIN on *.* to 'clone_user'@'192.168.2.2';
SET GLOBAL clone_valid_donor_list = '192.168.2.1:3306';
CLONE INSTANCE FROM clone_user@192.168.2.1:3306 IDENTIFIED BY 'Donar?1' DATA DIRECTORY = '/db/backup';

こちらも複製の取得ができていました

root@5ea896188eb3:/# ls -la db/backup/
total 162844
drwxr-x--- 2 mysql mysql     4096 Jul 23 17:54 #clone
drwxr-x--- 5 mysql mysql     4096 Jul 23 17:54 .
drwxr-xr-x 3 mysql mysql     4096 Jul 23 17:54 ..
-rw-r----- 1 mysql mysql     5404 Jul 23 17:54 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 Jul 23 17:54 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 23 17:54 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 23 17:54 ibdata1
drwxr-x--- 2 mysql mysql     4096 Jul 23 17:54 mysql
-rw-r----- 1 mysql mysql 29360128 Jul 23 17:54 mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Jul 23 17:54 sys
-rw-r----- 1 mysql mysql 13631488 Jul 23 17:54 undo_001
-rw-r----- 1 mysql mysql 10485760 Jul 23 17:54 undo_002

まとめ

Clone Pluginを使うことで少しの設定変更だけでdonarからrecipientへデータを複製できるので
MySQL8.0での環境複製対応は手間が減りそうですね。

Docker周り

https://docs.docker.com/install/linux/docker-ce/ubuntu/#install-using-the-repository
https://docs.docker.com/compose/install/

今回はUbuntu環境でした

sudo apt-get update
sudo apt-get install apt-transport-https ca-certificates curl gnupg-agent software-properties-common
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install docker-ce docker-ce-cli containerd.io
sudo apt-get install docker-ce=<VERSION_STRING> docker-ce-cli=<VERSION_STRING> containerd.io

sudo curl -L "https://github.com/docker/compose/releases/download/1.24.1/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
docker-compose --version
mkdir work
cd work

cat -<<'__EOF__'> docker-compose.yml
version: '2'
services:
  node1:
     restart: always
     image: mysql:latest
     networks:
          db:
           ipv4_address: 192.168.2.1
     expose:
      - "6606"
     volumes:
       - /data/mysql1:/var/lib/mysql
       - $PWD/node1.cnf:/etc/my.cnf
     privileged: true
     extra_hosts:
       - "node1:192.168.2.1"
       - "node2:192.168.2.2"
       - "node3:192.168.2.3"
     env_file: pass.env

  node2:
     restart: always
     image: mysql:latest
     networks:
          db:
           ipv4_address: 192.168.2.2
     expose:
      - "6606"
     volumes:
       - /data/mysql2:/var/lib/mysql
       - $PWD/node2.cnf:/etc/my.cnf
     privileged: true
     extra_hosts:
       - "node1:192.168.2.1"
       - "node2:192.168.2.2"
       - "node3:192.168.2.3"
     env_file: pass.env

networks:
  db:
   ipam:
    config:
      - subnet: 192.168.0.0/22
__EOF__

cat -<<'__EOF__'> node1.cnf
[mysqld]
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
log-bin
binlog_format=ROW
binlog_error_action=IGNORE_ERROR
binlog_rows_query_log_events
binlog_row_image=minimal
character-set-server=utf8mb4
master_info_repository=TABLE
relay_log_info_repository=TABLE
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=OFF
innodb_strict_mode=0
innodb_checksum_algorithm=innodb
innodb_flush_method=O_DIRECT
log_timestamps=SYSTEM
log_error=/var/lib/mysql/mysql.err
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=off
loose-group_replication_auto_increment_increment=1
loose-group_replication_local_address="192.168.2.1:6606"
loose-group_replication_group_seeds= "192.168.2.1:6606,192.168.2.2:6606,192.168.2.3:6606"
loose-group_replication_ip_whitelist="192.168.2.1,192.168.2.2,192.168.2.3,127.0.0.1"
loose-group_replication_bootstrap_group=off
plugin-load-add=mysql_clone.so
__EOF__

cat -<<'__EOF__'> node2.cnf
[mysqld]
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
log-bin
binlog_format=ROW
binlog_error_action=IGNORE_ERROR
binlog_rows_query_log_events
binlog_row_image=minimal
character-set-server=utf8mb4
master_info_repository=TABLE
relay_log_info_repository=TABLE
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=OFF
innodb_strict_mode=0
innodb_checksum_algorithm=innodb
innodb_flush_method=O_DIRECT
log_timestamps=SYSTEM
log_error=/var/lib/mysql/mysql.err
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=off
loose-group_replication_auto_increment_increment=1
loose-group_replication_local_address="192.168.2.2:6606"
loose-group_replication_group_seeds= "192.168.2.2:6606,192.168.2.2:6606,192.168.2.3:6606"
loose-group_replication_ip_whitelist="192.168.2.2,192.168.2.2,192.168.2.3,127.0.0.1"
loose-group_replication_bootstrap_group=off
plugin-load-add=mysql_clone.so
__EOF__

cat -<<'__EOF__'> pass.env
MYSQL_ROOT_PASSWORD=パスワード
__EOF__

docker-compose up -d
2
0
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0