MySQL8 clone plugin

  安装mysql8.0.17

 

• 下载

mysql8.0.17 二进制版本

 

解压，修改权限

1 tar xf mysql-8.0.17-linux-glibc2.12-x86_64.tar -c /usr/local/
2 
3 ln -sv mysql-8.0.17-linux-glibc2.12-x86_64 mysql8
4 
5 chown -r mysql:mysql mysql8/*

• 配置文件

[client]
port = 5432
socket = /data/mysql8017/tmp/mysql.sock

[mysql]
no-auto-rehash
default-character-set = utf8mb4
prompt=mysql(\\u[\\d])>

[mysqld]
###############basic setting#################
#skip-grant-tables
sql_mode = 'traditional'
#ngram_token_size = 1

basedir = /usr/local/mysql8
datadir = /data/mysql8017/var
port = 5432
mysqlx_port = 54321
admin_port = 54322
server_id = 158
socket = /data/mysql8017/tmp/mysql.sock
mysqlx_socket = /data/mysql8017/tmp/mysqlx.sock
pid-file = /data/mysql8017/var/mysql.pid
tmpdir = /data/mysql8017/tmp

default_authentication_plugin =mysql_native_password
explicit_defaults_for_timestamp = 1
auto_increment_increment = 1
auto_increment_offset = 1
#lower_case_table_names = 1

secure-file-priv = '/data/mysql8017/tmp'

default-time-zone = system
character-set-server = utf8mb4
collation_server = utf8mb4_general_ci
#default_collation_for_utf8mb4 = utf8mb4_general_ci

interactive_timeout = 1800
wait_timeout = 1800
max_connections = 2000
max_allowed_packet = 64m

#####skip
#skip-name-resolve
#skip-grant-tables
#skip-external-locking
#skip-networking
skip-slave-start

thread_cache_size = 768

####log settings###############

expire_logs_days = 7

log-bin = mysql-bin
log-bin-index = mysql-bin.index
relay-log = relay-log
relay_log_index = relay-log.index

#log-warnings = 1
log_error_verbosity = 3
log-error = /data/mysql8017/log/mysql.err

slow_query_log = 1
long-query-time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_slave_statements = 1
#min_examined_row_limit = 100
slow_query_log_file = /data/mysql8017/log/slow.log

general_log = 0
general_log_file = /data/mysql8017/log/mysql.log
max_binlog_size = 1g
max_relay_log_size = 1g

#####innodb setting###########

default_storage_engine = innodb
innodb_buffer_pool_size = 4g
innodb_data_home_dir = /data/mysql8017/var
innodb_data_file_path = ibdata1:1g:autoextend
innodb_temp_data_file_path = ibtmp1:12m:autoextend:max:10g
innodb_lru_scan_depth = 2000
innodb_file_per_table = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 8
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 32m
innodb_log_file_size = 2g
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /data/mysql8017/var
innodb_flush_neighbors = 1
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_undo_tablespaces = 3
innodb_max_dirty_pages_pct = 75
innodb_flush_method = o_direct
innodb_lock_wait_timeout = 5
innodb_open_files = 65535
innodb_use_native_aio = on
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 3000
log_timestamps = system

###replication settings###########

master_info_repository = table
relay_log_info_repository = table

binlog_format = row
sync_binlog = 0
transaction_isolation = read-committed
log_slave_updates = on
relay_log_recovery = 1

#for gtid
gtid_mode = on
enforce_gtid_consistency = on
binlog_gtid_simple_recovery = 1

#####myisam setting##########
key_buffer_size = 128m
read_buffer_size = 8m
read_rnd_buffer_size = 8m
join_buffer_size = 8m
bulk_insert_buffer_size = 64m
sort_buffer_size = 8m
myisam_sort_buffer_size = 1g
myisam_max_sort_file_size = 10g
myisam_repair_threads = 1
table_open_cache = 4096


[mysqldump]
quick
max_allowed_packet = 64m


[myisamchk]
key_buffer_size = 64m 
sort_buffer_size = 16m
read_buffer_size = 64m
write_buffer_size = 64m

[mysqlhotcopy]
interactive-timeout

 

初始化

cd /usr/local/mysql8

./bin/mysqld --defaults-file=/data/mysql8017/etc/my.cnf --user=mysql --initialize

 

启动

/usr/local/mysql8/bin/mysqld --defaults-file=/data/mysql8017/etc/my.cnf --user=mysql &

 

安装clone plugin

•  命令行加载插件

install plugin clone soname 'mysql_clone.so';

 

• 配置文件启动时加载

[mysqld]
plugin-load-add=mysql_clone.so

 

• 强制启动时，必须加载clone插件

[mysqld]
plugin-load-add=mysql_clone.so
clone=force_plus_permanent

 

• 本地 clone
• clone 需要游clone_admin权限的用户才能clone，本地实验使用root

grant backup_admin on *.* to 'clone_user';

 

• 本地执行clone

clone local data directory ='clone_dir';

clone_dir mysql 有写入权限

 

mysql> clone local data directory ='/data/mysql8/clone_data/mysql8017';
query ok, 0 rows affected (5 min 13.88 sec)

 

• 远程 clone

set global clone_valid_donor_list='192.168.64.154:5432'

clone instance from gao@192.168.64.154:5432 identified by '213456' data directory = '/data/backup/clone/mysql8';

 

•  验证

mkdir -pv /data/mysql8/{etc,var,log,tmp}
mv /data/backup/clone/mysql8/* /data/mysql8/var/

/usr/local/mysql8/bin/mysqld --defaults-file=/data/mysql8/etc/my.cnf --user=mysql &

 

mysql> use performance_schema;
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

show database changed
mysql> show tables like '%clone%';
+----------------------------------------+
| tables_in_performance_schema (%clone%) |
+----------------------------------------+
| clone_progress |
| clone_status |
+----------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from clone_status ;
+------+------+-----------+-------------------------+-------------------------+---------------------+----------------+----------+---------------+-----------------+-----------------+---------------+
| id | pid | state | begin_time | end_time | source | destination | error_no | error_message | binlog_file | binlog_position | gtid_executed |
+------+------+-----------+-------------------------+-------------------------+---------------------+----------------+----------+---------------+-----------------+-----------------+---------------+
| 1 | 0 | completed | 2019-07-30 18:09:20.763 | 2019-07-31 11:29:31.355 | 192.168.64.154:5432 | local instance | 0 | | mybinlog.000007 | 565 | |
+------+------+-----------+-------------------------+-------------------------+---------------------+----------------+----------+---------------+-----------------+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select * from clone_progress ;
+------+-----------+-----------+----------------------------+----------------------------+---------+------------+------------+------------+------------+---------------+
| id | stage | state | begin_time | end_time | threads | estimate | data | network | data_speed | network_speed |
+------+-----------+-----------+----------------------------+----------------------------+---------+------------+------------+------------+------------+---------------+
| 1 | drop data | completed | 2019-07-30 18:09:20.762836 | 2019-07-30 18:09:21.038340 | 1 | 0 | 0 | 0 | 0 | 0 |
| 1 | file copy | completed | 2019-07-30 18:09:21.038469 | 2019-07-30 18:09:37.925766 | 2 | 1133629497 | 1133629497 | 1133697917 | 0 | 0 |
| 1 | page copy | completed | 2019-07-30 18:09:37.925995 | 2019-07-30 18:09:38.228929 | 2 | 0 | 0 | 197 | 0 | 0 |
| 1 | redo copy | completed | 2019-07-30 18:09:38.229253 | 2019-07-30 18:09:38.530556 | 2 | 6144 | 6144 | 6639 | 0 | 0 |
| 1 | file sync | completed | 2019-07-30 18:09:38.530749 | 2019-07-30 18:09:54.844468 | 2 | 0 | 0 | 0 | 0 | 0 |
| 1 | restart | completed | 2019-07-30 18:09:54.844468 | 2019-07-31 11:29:25.573844 | 0 | 0 | 0 | 0 | 0 | 0 |
| 1 | recovery | completed | 2019-07-31 11:29:25.573844 | 2019-07-31 11:29:31.355007 | 0 | 0 | 0 | 0 | 0 | 0 |
+------+-----------+-----------+----------------------------+----------------------------+---------+------------+------------+------------+------------+---------------+
7 rows in set (0.00 sec)

 

tips:

•  配置文件中目录，字符集 和donor实例保持一致
• 用clone数据启动的实例可用donor实例的从库

 reference