yandex / mysync Goto Github PK

MySync is mysql high-availability and cluster configuration tool.

License: Other

Go 63.54% Makefile 0.62% Gherkin 29.42% Dockerfile 0.55% Shell 3.42% Clojure 2.46%

mysync's Introduction

MySync

MySync is mysql high-availability and cluster configuration tool. MySync is designed to switch master in homogeneous MySQL cluster in manual and automatic mode without data loss.

Project status

MySync is production-ready, it is being used in large production setups. We appreciate any kind of feedback and contribution to the project.

Architecture

Limitations and requirements

MySQL 5.7+ with GTID's enabled
MySQL cluster is homogeneous - all nodes have exactly the same data
ZooKeeper database to keep state and locks

MySQL configuration

# required
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_format = ROW

# required, managed by mysync
read_only = ON
super_read_only = ON
offline_mode = ON

# recommended
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

MySync configuration example

log: /var/log/mysync/mysync.log
loglevel: Debug

lockfile: /var/run/mysync/mysync.lock
emergefile: /var/run/mysync/mysync.emerge
resetupfile: /var/run/mysync/mysync.resetup

resetup_crashed_hosts: False
db_timeout: 2s
db_lost_check_timeout: 5s
tick_interval: 5s
healthcheck_interval: 5s
info_file_handler_interval: 5s
dcs_wait_timeout: 30s
critical_disk_usage: 95.00
not_critical_disk_usage: 94.76
disable_semi_sync_replication_on_maintenance: true
keep_super_writable_on_critical_disk_usage: true
db_set_ro_timeout: 30s
db_set_ro_force_timeout: 60s
priority_choice_max_lag: 60s
offline_mode_enable_interval: 900s
offline_mode_enable_lag: 86400s
offline_mode_disable_lag: 300s
disable_set_readonly_on_lost: False
exclude_users:
    - 'repl'
    - 'admin'
    - 'monitor'
    - 'event_scheduler'

semi_sync: true
rpl_semi_sync_master_wait_for_slave_count: 1
semi_sync_enable_lag: 33554432

failover: true
failover_cooldown: 3600s
failover_delay: 60s
inactivation_delay: 120s

zookeeper:
  session_timeout: 10s
  namespace: /mysql/cluster_id_1
  hosts:
    - zk01.db.company.net:2181
    - zk02.db.company.net:2181
    - zk03.db.company.net:2181

mysql:
  user: admin
  password: **********
  ssl_ca: /etc/mysql/ssl/allCAs.pem
  replication_connect_retry: 10
  replication_retry_count: 0
  replication_heartbeat_period: 2
  replication_port: 3306
  replication_user: repl
  replication_password: ********
  replication_ssl_ca: /etc/mysql/ssl/allCAs.pem
  external_replication_ssl_ca: /etc/mysql/ssl/external_CA.pem
  port: 3306

stream_from_reasonable_lag: 5m
stream_from_max_catchup_wait: 5m

replication_repair_aggressive_mode: False
replication_repair_cooldown: 60s
replication_repair_max_attempts: 3

Usage

mysync hosts add fqdn1.db.company.net
mysync hosts add fqdn2.db.company.net
mysync hosts add fqdn3.db.company.net

mysync info -s
mysync switch --to fqdn2
mysync switch --from fqdn2
mysync maint on
mysync maint off

mysync's People

Contributors

Stargazers

Watchers

Forkers

teem0n itzero-ru bularond moridin26 moonug showsmall mialinx deweb-services yunnnnn1 einkrebs michaelcoburn noname0443 verybigsad fizic

mysync's Issues

information_schema.PROCESSLIST is deprecated

From MySQL 8.2 changelog:
https://dev.mysql.com/doc/relnotes/mysql/8.2/en/news-8-2-0.html

INFORMATION_SCHEMA.PROCESSLIST is deprecated and subject to removal in a future MySQL release. As such, the implementation of SHOW PROCESSLIST which uses that table is also deprecated.

It is recommended to use the Performance Schema implementation of SHOW PROCESSLIST instead. As such, performance_schema_show_processlist is also deprecated and subject to removal in a future MySQL release. (WL #15915)

Deleted node is presented in resetup_status

I cteated a MySQL cluster with 1 Master (stand-mysql-1) and 2 Riplicas (stand-mysql-2, stand-mysql-3).
And I used MySync to automate cluster configuration.

I've tried to delete 1 Replica Host by
mysync host remove stand-mysql-2
After that I've destroyed stand-mysql-2 at all (it was vm).

As result I have such output for my cluster configuration:

root@stand-mysql-1:~# mysync info
active_nodes:
- stand-mysql-1
- stand-mysql-3
cascade_nodes: null
ha_nodes:
  stand-mysql-1:
    priority: 0
  stand-mysql-3:
    priority: 0
health:
  stand-mysql-1:
    check_at: "2023-11-09T08:28:55.529207845Z"
    check_by: stand-mysql-1
    daemon_state:
      crash_recovery: false
      recovery_time: "0001-01-01T00:00:01Z"
      start_time: "2023-11-09T08:02:13Z"
    disk_state:
      Total: 1.03944192e+10
      Used: 2.177769472e+09
    error: ""
    is_cascade: false
    is_file_system_readonly: false
    is_master: true
    is_offline: false
    is_readonly: false
    is_super_readonly: false
    master_state:
      executed_gtid_set: 29f6babb-7ed6-11ee-8f7e-fa163e84917e:1-17
    ping_dubious: false
    ping_ok: true
    semi_sync_state:
      master_enabled: true
      slave_enabled: false
      wait_slave_count: 1
    slave_state: null
  stand-mysql-3:
    check_at: "2023-11-09T08:28:54.558386224Z"
    check_by: stand-mysql-3
    daemon_state:
      crash_recovery: false
      recovery_time: "0001-01-01T00:00:01Z"
      start_time: "2023-11-09T08:15:57Z"
    disk_state:
      Total: 1.03944192e+10
      Used: 2.173104128e+09
    error: ""
    is_cascade: false
    is_file_system_readonly: false
    is_master: false
    is_offline: false
    is_readonly: true
    is_super_readonly: true
    master_state: null
    ping_dubious: false
    ping_ok: true
    semi_sync_state:
      master_enabled: false
      slave_enabled: true
      wait_slave_count: 1
    slave_state:
      executed_gtid_set: 29f6babb-7ed6-11ee-8f7e-fa163e84917e:1-17
      last_io_errno: 0
      last_sql_errno: 0
      master_host: stand-mysql-1
      master_log_file: mysql-bin-log.000005
      master_log_pos: 809
      replication_lag: 0
      replication_state: running
      retrieved_gtid_get: ""
last_shutdown_node_time: "2023-11-09T08:02:25.571185739Z"
low_space: false
manager:
  hostname: stand-mysql-1
  pid: 7026
master: stand-mysql-1
resetup_status:
  stand-mysql-1:
    Status: false
    UpdateTime: "2023-11-09T08:28:55.53501369Z"
  stand-mysql-2:
    Status: false
    UpdateTime: "2023-11-09T08:27:30.668844835Z"
  stand-mysql-3:
    Status: false
    UpdateTime: "2023-11-09T08:28:54.564428936Z"
root@stand-mysql-1:~#

As you see, I have "stand-mysql-2" in "resetup_status" section.
Of cource, there is the same situation on the zookeeper cluster:

[zk: localhost:2181(CONNECTED) 0] ls /mysql/cluster_id_1109/resetup_status
[stand-mysql-1, stand-mysql-2, stand-mysql-3]
[zk: localhost:2181(CONNECTED) 2] get /mysql/cluster_id_1109/resetup_status/stand-mysql-2
{"UpdateTime":"2023-11-09T08:27:30.668844835Z","Status":false}

I can't understand is it an error? Are there some reasons to have the record for the deleted host in resetup_status?

Is it possible to monitor multiple mysql clusters?

I may need to monitor multiple mysql clusters

MySync ignores short server's name in mysync hosts add command

Short description:
When you put short server name in mysync hosts add command, MySync ignores it, takes fqfn from the remote host and puts fqnd in zookeeper.
If you deploy your MySQL cluster via short server's name, MySync will fail with error

2023-12-05T15:02:56+03:00 INFO: active: []
2023-12-05T15:02:56+03:00 INFO: master: server1
2023-12-05T15:02:56+03:00 INFO: cs: map[server1:<ping=ok repl=master sync=--- ro=true offline=true lag=0.00 du=??? cr=??? gtid=184a9613-9366-11ee-b999-005056b39ebb:1-14> server2:<ping=ok repl=error sync=--- ro=true offline=true lag=NaN du=??? cr=??? gtid=184a9613-9366-11ee-b999-005056b39ebb:1-14>]
2023-12-05T15:02:56+03:00 INFO: dcs cs: map[server1:<ping=ERR repl=??? sync=??? ro=false offline=false lag=0.00 du=??? cr=??? gtid=???> server2:<ping=ERR repl=??? sync=??? ro=false offline=false lag=0.00 du=??? cr=??? gtid=???>]
2023-12-05T15:02:56+03:00 ERROR: MASTER FAILURE

Full description:

Setup two servers Debian12:

server1.org.com
server2.org.com

Every server has the same fqdn in /etc/hostname:

server1.org.com
server2.org.com

Install MySQL and setup replication with server's short name: server1, server2
Install MySync and run commands:

hosts add server1
hosts add server2

Run zkCli.sh and run command:

[zk: localhost:2181(CONNECTED) 2] ls /mysql/cluster_id_2fb4f574-2ce8-59fb-a8c7-0dfbe20793e3/health
[server1.org.com, server2.org.com]

You can see that MySync created records in zookeeper with fqdn: server1.org.com, server2.org.com
Not with short names: server1, server2, server3
And this is why MySyncs fails with error:

2023-12-05T15:02:56+03:00 INFO: active: []
2023-12-05T15:02:56+03:00 INFO: master: server1
2023-12-05T15:02:56+03:00 INFO: cs: map[server1:<ping=ok repl=master sync=--- ro=true offline=true lag=0.00 du=??? cr=??? gtid=184a9613-9366-11ee-b999-005056b39ebb:1-14> server2:<ping=ok repl=error sync=--- ro=true offline=true lag=NaN du=??? cr=??? gtid=184a9613-9366-11ee-b999-005056b39ebb:1-14>]
2023-12-05T15:02:56+03:00 INFO: dcs cs: map[server1:<ping=ERR repl=??? sync=??? ro=false offline=false lag=0.00 du=??? cr=??? gtid=???> server2:<ping=ERR repl=??? sync=??? ro=false offline=false lag=0.00 du=??? cr=??? gtid=???>]
2023-12-05T15:02:56+03:00 ERROR: MASTER FAILURE

MySync always takes server name from /etc/hostname of remote server, not from hosts add command
Is this behavior by design or bug?