mysql8.0 xenon实现高可用
基础架构: vip是 172.16.230.50
| 主机名 | IP地址 |
| mysql1 | 172.16.230.51 |
| mysql2 | 172.16.230.52 |
| mysql3 | 172.16.230.53 |
1. mysql 基础环境
1.1 mysql8.0 GTID 复制结构搭建
mysql1主配置文件
[root@mysql1 mysql_3306]# cat /etc/my.cnf [client] port = 3306 #tee = "/data/mysql/mysql_3306/logs/mysql_output.log" socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock [mysql] prompt="\u@\h \R:\m:\s [\d]> " no-auto-rehash [mysqld] user = mysql port = 3306 admin_address = 127.0.0.1 basedir = /usr/local/mysql datadir = /data/mysql/mysql_3306/data socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock pid-file = mysql_3306.pid character-set-server = utf8mb4 skip_name_resolve = 1#忽略大小写, 初始化时也必须加添加 lower-case-table-names=1 #replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table=test.% #replicate-wild-ignore-table=information_schema.% # Two-Master configure #server-1 #auto-increment-offset = 1 #auto-increment-increment = 2 #server-2 #auto-increment-offset = 2 #auto-increment-increment = 2 # semi sync replication settings # #plugin_dir = /usr/local/mysql/lib/mysql/plugin #plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径 plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径 #rpl_semi_sync_master_enabled=1 #rpl_semi_sync_slave_enabled=1 #rpl_semi_sync_master_timeout=2147483648 slave_parallel_workers = 8 slave_parallel_type = LOGICAL_CLOCK binlog_transaction_dependency_tracking=writeset binlog_transaction_dependency_history_size=25000 transaction_write_set_extraction=XXHASH64 slave_preserve_commit_order = 1 open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_timestamps = SYSTEM slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log log-error = /data/mysql/mysql_3306/logs/error.log long_query_time = 0.1 log_queries_not_using_indexes =1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G # binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates #slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image=FULL binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 8M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 #transaction_isolation = REPEATABLE-READ transaction_isolation = READ-COMMITTED #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2048M innodb_buffer_pool_instances = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0 # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0 server-id = 1 innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0 #performance_schema performance_schema = 1 performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' #innodb monitor innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash" [mysqldump] quick max_allowed_packet = 64M [mysqld_safe] #malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19 open-files-limit=65535
mysql2 从配置文件
[root@mysql2 mysql_3306]# cat /etc/my.cnf [client] port = 3306 #tee = "/data/mysql/mysql_3306/logs/mysql_output.log" socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock [mysql] prompt="\u@\h \R:\m:\s [\d]> " no-auto-rehash [mysqld] user = mysql port = 3306 admin_address = 127.0.0.1 basedir = /usr/local/mysql datadir = /data/mysql/mysql_3306/data socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock pid-file = mysql_3306.pid character-set-server = utf8mb4 skip_name_resolve = 1#忽略大小写, 初始化时也必须加添加 lower-case-table-names=1 #replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table=test.% #replicate-wild-ignore-table=information_schema.% # Two-Master configure #server-1 #auto-increment-offset = 1 #auto-increment-increment = 2 #server-2 #auto-increment-offset = 2 #auto-increment-increment = 2 # semi sync replication settings # #plugin_dir = /usr/local/mysql/lib/mysql/plugin #plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径 plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径 slave_parallel_workers = 8 slave_parallel_type = LOGICAL_CLOCK binlog_transaction_dependency_tracking=writeset binlog_transaction_dependency_history_size=25000 transaction_write_set_extraction=XXHASH64 slave_preserve_commit_order = 1 binlog_transaction_dependency_tracking=writeset binlog_transaction_dependency_history_size=25000 transaction_write_set_extraction=XXHASH64 open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_timestamps = SYSTEM slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log log-error = /data/mysql/mysql_3306/logs/error.log long_query_time = 0.1 log_queries_not_using_indexes =1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 server-id = 2 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G # binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates #slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image=FULL binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 8M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 #transaction_isolation = REPEATABLE-READ transaction_isolation = READ-COMMITTED #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2048M innodb_buffer_pool_instances = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog slave_preserve_commit_order=1 # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0 # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0 innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0 #performance_schema performance_schema = 1 performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' #innodb monitor innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash" [mysqldump] quick max_allowed_packet = 64M [mysqld_safe] #malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19 open-files-limit=65535
mysql3 从配置文件
[root@mysql3 mysql_3306]# cat /etc/my.cnf [client] port = 3306 #tee = "/data/mysql/mysql_3306/logs/mysql_output.log" socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock [mysql] prompt="\u@\h \R:\m:\s [\d]> " no-auto-rehash [mysqld] user = mysql port = 3306 admin_address = 127.0.0.1 basedir = /usr/local/mysql datadir = /data/mysql/mysql_3306/data socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock pid-file = mysql_3306.pid character-set-server = utf8mb4 skip_name_resolve = 1#忽略大小写, 初始化时也必须加添加 lower-case-table-names=1 #replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table=test.% #replicate-wild-ignore-table=information_schema.% # Two-Master configure #server-1 #auto-increment-offset = 1 #auto-increment-increment = 2 #server-2 #auto-increment-offset = 2 #auto-increment-increment = 2 # semi sync replication settings # #plugin_dir = /usr/local/mysql/lib/mysql/plugin #plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径 plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径 slave_parallel_workers = 8 slave_parallel_type = LOGICAL_CLOCK binlog_transaction_dependency_tracking=writeset binlog_transaction_dependency_history_size=25000 transaction_write_set_extraction=XXHASH64 slave_preserve_commit_order open_files_limit = 65535 back_log = 1024 max_connections = 1024 max_connect_errors = 1000000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1536 interactive_timeout = 600 wait_timeout = 600 tmp_table_size = 32M max_heap_table_size = 32M slow_query_log = 1 log_timestamps = SYSTEM slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log log-error = /data/mysql/mysql_3306/logs/error.log long_query_time = 0.1 log_queries_not_using_indexes =1 log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 server-id = 3 log-bin = /data/mysql/mysql_3306/logs/mysql-bin sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G # binlog_expire_logs_seconds=2592000 master_info_repository = TABLE relay_log_info_repository = TABLE gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates #slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_format = row binlog_row_image=FULL binlog_checksum = 1 relay_log_recovery = 1 relay-log-purge = 1 key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 8M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 slave_preserve_commit_order=1 #transaction_isolation = REPEATABLE-READ transaction_isolation = READ-COMMITTED #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2048M innodb_buffer_pool_instances = 4 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 3 innodb_max_undo_log_size = 4G innodb_undo_directory = /data/mysql/mysql_3306/undolog # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G innodb_stats_on_metadata = 0 # some var for MySQL 8 log_error_verbosity = 3 innodb_print_ddl_logs = 1 binlog_expire_logs_seconds = 2592000 #innodb_dedicated_server = 0 innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0 #performance_schema performance_schema = 1 performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' #innodb monitor innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash" [mysqldump] quick max_allowed_packet = 64M [mysqld_safe] #malloc-lib=/usr/local/mysql/lib/jmalloc.so nice=-19 open-files-limit=65535
配置主从:
配置主从可以使用 xtrbackup 或者 mysql clone plugin 插件进行复制同步
利用clone plugin 方式搭建从库
主库实例
INSTALL PLUGIN clone SONAME 'mysql_clone.so'; create user 'fengjian'@'%' identified with mysql_native_password by '123456'; grant backup_admin on *.* to 'fengjian'@'%' ;
从库实例
INSTALL PLUGIN clone SONAME 'mysql_clone.so'; set global clone_valid_donor_list='172.16.230.51:3306'; create user 'fengjian'@'%' identified with mysql_native_password by '123456'; grant clone_admin on *.* to 'fengjian'@'%' ; set global log_error_verbosity=3; clone instance from 'fengjian'@172.16.230.51:3306 identified by '123456';
mysql clone plugin 安装失败的参数有:
sql_require_primary_key = on read_only=1 explicit_defaults_for_timestamp=off 这几个参数会导致clone 安装失败.
从库执行change master to 语句,配置异步复制
root@localhost 10:34: [(none)]> change master to master_host='172.16.230.51', master_user='fengjian', MASTER_PASSWORD='123456', MASTER_PORT=3306, GET_MASTER_PUBLIC_KEY=1, MASTER_SSL=1, MASTER_AUTO_POSITION=1;
root@localhost 10:34: [(none)]> start slave;
root@localhost 10:34: [(none)]> show slave status\G;
1.2 加载 mysql8.0 半同步插件
主 从库操作:
set global super_read_only = 0; set global read_only = 0; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_timeout=2147483648;
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_slave_enabled=1;
从库操作:
从库查看半同步是否状态:
root@localhost 16:19: [mysql]> show global status like '%semi%';
root@localhost 16:19: [mysql]> show global variables like 'rpl%';
# 状态为ON,表示启动
Rpl_semi_sync_slave_status | ON
如果为Rpl_semi_sync_slave_status 为OFF状态, 需要重启io_thread
stop slave io_thread;
start slave io_thread;
主库查看半同步状态
root@localhost 16:19: [mysql]> show global status like '%semi%';
Rpl_semi_sync_master_status | ON
1.3 修改mysql 账号, 有原来的/sbin/nolgoin 变更成 /bin/bash, 修改mysql 用户名密码
[root@mysql1 ~]# chsh mysql Changing shell for mysql. New shell [/sbin/nologin]: /bin/bash Shell changed.
[root@mysql1 ~]# passwd mysql Changing password for user mysql. New password: BAD PASSWORD: The password is shorter than 9 characters Retype new password: passwd: all authentication tokens updated successfully.
1.4 mysql账号之间ssh信任
ssh-keygen -t rsa ssh-copy-id mysql@172.16.230.51 ssh-copy-id mysql@172.16.230.52 ssh-copy-id mysql@172.16.230.53 scp -r .ssh 172.16.230.52:~/ scp -r .ssh 172.16.230.53:~/
1.5 添加mysql账号的sudo权限 （所有机器操作）
[root@mysql1]# visudo
mysql ALL=(ALL) NOPASSWD: /usr/sbin/ip
1.6 安装 xtrabackup
yum localinstall /data/tools/percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm
1.7 修改3台主机host
[root@mysql1 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 172.16.230.51 mysql1 172.16.230.52 mysql2 172.16.230.53 mysql3
2.1 下载xenon
https://github.com/radondb/xenon/blob/master/docs/how_to_build_and_run_xenon.md
[root@mysql1 tools]# git clone https://github.com/radondb/xenon.git [root@mysql1 tools]# cd xenon/ [root@mysql1 xenon]# make build [root@mysql1 xenon]# ls bin/ xenon xenoncli
安装xenon
[root@mysql1 ~]# mkdir /data/xenon/ [root@mysql1 ~]# cd /data/xenon/ [root@mysql1 xenon]# cp /data/tools/xenon/bin ./ -ar [root@mysql1 xenon]# mkdir /etc/xenon/ [root@mysql1 xenon]# cp /data/tools/xenon/conf/xenon-simple.conf.json /etc/xenon/xenon.json [root@mysql1 xenon]# echo "/etc/xenon/xenon.json" > /data/xenon/bin/config.path [root@mysql1 xenon]# chown -R mysql:mysql /data/xenon/ /etc/xenon/
2.2 配置 xenon 配置文件
mysql1 xenon 配置文件
[root@mysql1 xenon]# cat /etc/xenon/xenon.json { "server": { "endpoint":"172.16.230.51:8801" }, "raft": { "meta-datadir":"/data/xenon/raft.meta", "heartbeat-timeout":1000, "election-timeout":3000, "leader-start-command":"sudo /sbin/ip a a 172.16.230.50/24 dev ens32 && arping -c3 -A 172.16.230.50 -I ens32", "leader-stop-command":"sudo /sbin/ip a d 172.16.230.50/24 dev ens32" }, "mysql": { "admin":"root", "passwd":"123456", "host":"127.0.0.1", "port":3306, "basedir":"/usr/local/mysql", "defaults-file":"/etc/my.cnf", "ping-timeout":1000, "master-sysvars":"super_read_only=0;read_only=0;sync_binlog=default;innodb_flush_log_at_trx_commit=default", "slave-sysvars": "super_read_only=1;read_only=1;sync_binlog=1000;innodb_flush_log_at_trx_commit=2" }, "replication": { "user":"fengjian", "passwd":"123456" }, "backup": { "ssh-host":"172.16.230.51", "ssh-user":"mysql", "ssh-passwd":"mysql", "ssh-port":22, "backupdir":"/data/mysql/mysql_3306/data", "xtrabackup-bindir":"/usr/bin", "backup-iops-limits":100000, "backup-use-memory": "2GB", "backup-parallel": 2 }, "rpc": { "request-timeout":500 }, "log": { "level":"INFO" } }
拷贝 xenon 到其他机器
[root@mysql1 xenon]# scp -r /etc/xenon 172.16.230.52:/etc/ [root@mysql1 xenon]# scp -r /etc/xenon 172.16.230.53:/etc/ [root@mysql1 xenon]# scp -r /data/xenon 172.16.230.53:/data/ [root@mysql1 xenon]# scp -r /data/xenon 172.16.230.52:/data/ 授权 [root@mysql2 xenon]# chown -R mysql:mysql /data/xenon/ /etc/xenon/ [root@mysql3 xenon]# chown -R mysql:mysql /data/xenon/ /etc/xenon/
mysql2 配置文件
[root@mysql2 ~]# cat /etc/xenon/xenon.json { "server": { "endpoint":"172.16.230.52:8801" }, "raft": { "meta-datadir":"/data/xenon/raft.meta", "heartbeat-timeout":1000, "election-timeout":3000, "leader-start-command":"sudo /sbin/ip a a 172.16.230.50/24 dev ens32 && arping -c3 -A 172.16.230.50 -I ens32", "leader-stop-command":"sudo /sbin/ip a d 172.16.230.50/24 dev ens32" }, "mysql": { "admin":"root", "passwd":"123456", "host":"127.0.0.1", "port":3306, "basedir":"/usr/local/mysql", "defaults-file":"/etc/my.cnf", "ping-timeout":1000, "master-sysvars":"super_read_only=0;read_only=0;sync_binlog=default;innodb_flush_log_at_trx_commit=default", "slave-sysvars": "super_read_only=1;read_only=1;sync_binlog=1000;innodb_flush_log_at_trx_commit=2" }, "replication": { "user":"fengjian", "passwd":"123456" }, "backup": { "ssh-host":"172.16.230.52", "ssh-user":"mysql", "ssh-passwd":"mysql", "ssh-port":22, "backupdir":"/data/mysql/mysql_3306/data", "xtrabackup-bindir":"/usr/bin", "backup-iops-limits":100000, "backup-use-memory": "2GB", "backup-parallel": 2 }, "rpc": { "request-timeout":500 }, "log": { "level":"INFO" } }
mysql3 配置文件
[root@mysql3 ~]# cat /etc/xenon/xenon.json { "server": { "endpoint":"172.16.230.53:8801" }, "raft": { "meta-datadir":"/data/xenon/raft.meta", "heartbeat-timeout":1000, "election-timeout":3000, "leader-start-command":"sudo /sbin/ip a a 172.16.230.50/24 dev ens32 && arping -c3 -A 172.16.230.50 -I ens32", "leader-stop-command":"sudo /sbin/ip a d 172.16.230.50/24 dev ens32" }, "mysql": { "admin":"root", "passwd":"123456", "host":"127.0.0.1", "port":3306, "basedir":"/usr/local/mysql", "defaults-file":"/etc/my.cnf", "ping-timeout":1000, "master-sysvars":"super_read_only=0;read_only=0;sync_binlog=default;innodb_flush_log_at_trx_commit=default", "slave-sysvars": "super_read_only=1;read_only=1;sync_binlog=1000;innodb_flush_log_at_trx_commit=2" }, "replication": { "user":"fengjian", "passwd":"123456" }, "backup": { "ssh-host":"172.16.230.53", "ssh-user":"mysql", "ssh-passwd":"mysql", "ssh-port":22, "backupdir":"/data/mysql/mysql_3306/data", "xtrabackup-bindir":"/usr/bin", "backup-iops-limits":100000, "backup-use-memory": "2GB", "backup-parallel": 2 }, "rpc": { "request-timeout":500 }, "log": { "level":"INFO" } }
配置文件对比:
endpoint ssh-host 两个配置修改成本机IP地址
2.3 mysql1 启动 xenon
使用 mysql账号启动xenon [root@mysql1 xenon]# su - mysql [mysql@mysql1 xenon]# nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
2.4 mysql1使用xenoncli 命令添加集群
-bash-4.2$ /data/xenon/bin/xenoncli cluster add 172.16.230.51:8801,172.16.230.52:8801,172.16.230.53:8801 2021/02/14 18:17:34.687301 [WARNING] cluster.prepare.to.add.nodes[172.16.230.51:8801,172.16.230.52:8801,172.16.230.53:8801].to.leader[] 2021/02/14 18:17:34.687515 [WARNING] cluster.canot.found.leader.forward.to[172.16.230.51:8801] 2021/02/14 18:17:34.690754 [WARNING] cluster.add.nodes.to.leader[].done
2.5 查看集群
-bash-4.2$ /data/xenon/bin/xenoncli cluster status +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | ID | Raft | Mysqld | Monitor | Backup | Mysql | IO/SQL_RUNNING | MyLeader | +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.51:8801 | [ViewID:0 EpochID:2]@FOLLOWER | RUNNING | ON | state:[NONE] | [ALIVE] [READONLY] | [true/true] | | | | | | | LastError: | | | | +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.52:8801 | UNKNOW | UNKNOW | UNKNOW | UNKNOW | UNKNOW | UNKNOW | UNKNOW | +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.53:8801 | UNKNOW | UNKNOW | UNKNOW | UNKNOW | UNKNOW | UNKNOW | UNKNOW | +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+
2.6 启动mysql2 xenon
使用 mysql账号启动xenon
[root@mysql2 xenon]# su - mysql
[mysql@mysql2 xenon]# nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
2.7 查看mysql2 xenon cluster状态
默认只能看到自己, 其他需要再加入到集群
-bash-4.2$ /data/xenon/bin/xenoncli cluster status +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | ID | Raft | Mysqld | Monitor | Backup | Mysql | IO/SQL_RUNNING | MyLeader | +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.52:8801 | [ViewID:0 EpochID:0]@FOLLOWER | RUNNING | ON | state:[NONE] | [ALIVE] [READONLY] | [true/true] | | | | | | | LastError: | | | | +--------------------+-------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+
2.8 mysql2 使用 xenoncli命令加入到集群中
-bash-4.2$ /data/xenon/bin/xenoncli cluster add 172.16.230.51:8801,172.16.230.52:8801,172.16.230.53:8801 2021/02/14 18:23:09.233397 [WARNING] cluster.prepare.to.add.nodes[172.16.230.51:8801,172.16.230.52:8801,172.16.230.53:8801].to.leader[] 2021/02/14 18:23:09.233578 [WARNING] cluster.canot.found.leader.forward.to[172.16.230.52:8801] 2021/02/14 18:23:09.236497 [WARNING] cluster.add.nodes.to.leader[].done -bash-4.2$ /data/xenon/bin/xenoncli cluster status +--------------------+---------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | ID | Raft | Mysqld | Monitor | Backup | Mysql | IO/SQL_RUNNING | MyLeader | +--------------------+---------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.52:8801 | [ViewID:50 EpochID:2]@FOLLOWER | RUNNING | ON | state:[NONE] | [ALIVE] [READONLY] | [false/true] | | | | | | | LastError: | | | | +--------------------+---------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.51:8801 | [ViewID:50 EpochID:2]@CANDIDATE | RUNNING | ON | state:[NONE] | [ALIVE] [READONLY] | [true/true] | | | | | | | LastError: | | | | +--------------------+---------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ | 172.16.230.53:8803 | [ViewID:54 EpochID:4]@FOLLOWER | RUNNING| ON | state:[NONE] LastError: | [ALIVE] [READONLY] | [false/true] | |
+--------------------+---------------------------------+---------+---------+--------------------------+--------------------+----------------+----------+ (3 rows)
2.9 mysql3 启动 xenon
使用 mysql账号启动xenon [root@mysql3 xenon]# su - mysql [mysql@mysql3 xenon]# nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
3.0 mysql3 使用 xenoncli命令加入到集群中
-bash-4.2$ /data/xenon/bin/xenoncli cluster add 172.16.230.51:8801,172.16.230.52:8801,172.16.230.53:8801
2021/02/14 18:23:09.233397 [WARNING] cluster.prepare.to.add.nodes[172.16.230.51:8801,172.16.230.52:8801,172.16.230.53:8803].to.leader[]
2021/02/14 18:23:09.233578 [WARNING] cluster.canot.found.leader.forward.to[172.16.230.52:8801]
2021/02/14 18:23:09.236497 [WARNING] cluster.add.nodes.to.leader[].done
-bash-4.2$ /data/xenon/bin/xenoncli cluster status
3.1 查看 172.16.230.50 VIP地址
由于在myleader 是 172.16.230.51, 所以查看vip
3.2 切换测试
关闭172.16.230.51 mysql
-bash-4.2$ mysqladmin -u root -p shutdown
由于xenon 进程存在, 会把mysqld_safe 进程拉起来
等候几秒后, leader还是在172.16.230.51
关闭172.16.230.51 xenon mysql 进程
参考
https://blog.csdn.net/assasin0308/article/details/106930135
