daixijun.mysql
mysql
This is for quickly deploying a MySQL cluster.
It supports the following cluster modes:
- GTID-based master-slave replication
- Semi-Sync for enhanced semi-synchronization (enabled by default)
- Master-slave replication based on MHA architecture
- MGR single-master mode (default)
- MGR multi-master mode
- ReplicaSet (replication set)
Supported MySQL versions:
- Oracle official MySQL Community Server
- GreatSQL from Wanli Open Source GreatSQL
- Percona Percona Server for MySQL
Environment Requirements
- CentOS 7+
- Ansible 2.9+
- MySQL 8.0+
Variables
| Variable Name | Type | Default Value | Description |
|---|---|---|---|
| mysql_version | string | 8.0.23 | MySQL version |
| mysql_upgrade | bool | false | Whether to perform a version upgrade |
| mysql_download_url | string | Download URL for binary distribution | |
| mysql_inventory_group | string | mysql | Name of the group in Ansible inventory; default is mysql |
| mysql_basedir | string | /usr/local/mysql | Installation directory |
| mysql_datadir | string | /data/mysql | Directory for storing data files |
| mysql_logdir | string | /var/log/mysqld | Directory for storing logs |
| mysql_pidfile | string | /var/run/mysqld/mysqld.pid | Location of the PID file |
| mysql_socket | string | /var/run/mysqld/mysqld.sock | Location of the socket file |
| mysql_port | int | 3306 | Listening port |
| mysql_bind_address | string | 0.0.0.0 | Listening address |
| mysql_interface | string | "" | Specify network interface, defaults to the first interface except lo |
| mysql_default_time_zone | string | +8:00 | Time zone specification |
| mysql_character_set_server | string | utf8mb4 | Default character set |
| mysql_collation_server | string | utf8mb4_general_ci | Default string collation |
| mysql_max_connections | int | 1005 | Maximum number of connections |
| mysql_max_user_connections | int | 1000 | Maximum user connections, must be less than mysql_max_connections for admin use |
| mysql_interactive_timeout | int | 28800 | Seconds to wait for activity before closing interactive connections |
| mysql_wait_timeout | int | 28800 | Seconds to wait for activity before closing non-interactive connections |
| mysql_max_connect_errors | int | 200 | Maximum number of connection errors |
| mysql_root_password | string | "" | Password for the root account |
| mysql_cluster_type | string | mgr | Cluster type (default is mgr); options: mgr (Mysql Group Replication) / ms (Master-Slave) |
| mysql_cluster_name | string | default | Name of the cluster, applicable for InnodbCluster/ReplicaSet |
| mysql_mha_enabled | bool | false | Enable MHA-based master-slave high availability |
| mysql_mha_config_dir | string | /etc/mha | Directory for MHA configuration files |
| mysql_mha_manager_workdir | string | /var/log/mha | MHA working directory for logs and status information |
| mysql_mha_user | string | mha | User for managing MySQL, must have ALL permissions |
| mysql_mha_password | string | "" | MySQL management password |
| mysql_mha_repl_user | string | repl | Account for MySQL master-slave replication |
| mysql_mha_repl_password | string | "" | Password for MySQL master-slave replication account |
| mysql_mha_ssh_user | string | root | SSH account for communication between nodes |
| mysql_mha_ssh_port | int | 22 | SSH port for communication between nodes |
| mysql_mha_ping_interval | int | 2 | Heartbeat interval for master node status |
| mysql_mha_vip | string | "" | VIP for binding to the master node |
| mysql_mha_wechat_token | string | "" | Key for corporate WeChat robot for sending alerts |
| mysql_mha_proxy | string | "" | Proxy for sending alerts (used in send_report script) |
| mysql_repl_user | string | repl | Account for master-slave/group replication |
| mysql_repl_password | string | Password for master-slave/group replication account | |
| mysql_group_replication_name | uuid | "" | Group replication cluster name, generated using SELECT UUID() or uuidgen |
| mysql_group_replication_single_primary_mode | bool | true | Whether the MGR cluster is single-primary |
| mysql_innodb_cluster_enable | bool | true | Enable InnoDB Cluster |
| mysql_innodb_cluster_username | string | ic | Account for creating and managing InnoDB Cluster, must have ALL WITH GRANT OPTION |
| mysql_innodb_cluster_password | string | "" | Management password |
| mysql_proxysql_config | bool | false | Whether to use ProxySQL as a proxy layer |
| mysql_proxysql_monitor_username | string | monitor | User for monitoring MGR status in ProxySQL |
| mysql_proxysql_monitor_password | string | "" | Password for monitoring MGR status in ProxySQL |
| mysql_databases | array | [] | Databases to create |
| mysql_users | array | [] | Users to create |
Dependencies
Collections:
Example
Installation
ansible-galaxy collection install -r requirements.yml
Usage
- hosts: servers
roles:
- { role: mysql, mysql_version: 8.0.20 }
Known Issues
- The
mysql_usermodule can have idempotent issues grantingALLpermissions for versions above MySQL 8.0 Idempotence all grant
To-Do
- Support semi-synchronous replication in master-slave mode
- Improve side-effect testing under MHA
- Support version updates
- Support node rebuilding
- After a complete failure of all nodes in an MGR cluster, find the instance with the largest GTID to reboot the cluster
Recovering from Cluster Abnormalities
Recovering from MGR Cluster Asynchronous Failures
Take the intersection, select RECEIVED_TRANSACTION_SET from performance_schema.replication_connection_status + show variables like 'gtid_executed', then select the maximum as the primary.