使用ansible自动化安装MySQL8的mysql-router+mysql-shell+mysql架构InnoDB ReplicaSet
【说明】
当前数据库MySQL Community Server 8.4.0 LTS版本已经发行，使用InnoDB ReplicaSet架构自动化搭建
【自动化安装】
使用ansible安装mysql-router+mysql-shell+mysql
tree mysql8/ mysql8/ ├── mysql_ms.yaml └── roles └── mysql_ms ├── tasks │ └── main.yml ├── templates │ ├── auto_mysqlroute_pass.sh │ ├── my.cnf │ ├── readme.md │ ├── replicaset_addinstance.js │ ├── replicaset_configure.js │ ├── replicaset_create.js │ └── replicaset_sleep.sql └── vars └── main.yml
【剧本说明】
环境变量信息，填写对应的介质及路径 cat main.yml --- mysql_tgz: mysql-8.4.0-linux-glibc2.17-x86_64.tar.xz tgz_extracted: mysql-8.4.0-linux-glibc2.17-x86_64 mysqlsh_tgz: mysql-shell-8.4.0-linux-glibc2.17-x86-64bit.tar.gz shtgz_extracted: mysql-shell-8.4.0-linux-glibc2.17-x86-64bit mysql_router: mysql-router-community-8.4.0-1.el7.x86_64.rpm db_data: /db/mysql monitor_user: monitor download_target: /tmp/deployer
ansible的hosts配置文件，这里将密码汇总是方便hosts文件加密
[mysql_ms] 10.x.x.xx7 ansible_user=root ansible_ssh_pass=xxxxxxxx cluster_role=master server_id=103306 10.x.x.xx8 ansible_user=root ansible_ssh_pass=xxxxxxxx cluster_role=slave server_id=103307 [mysql_ms:vars] mysql_pass='xxxxxxxx' dbadmin_pass='xxxxxxxx' repl_pass='xxxxxxxx' monitor_pass='xxxxxxxx'
主要运行的剧本信息 cat main.yml --- - name: create mysql user and group user: name: mysql shell: /sbin/nologin - name: download installation media copy: src: "{{download_target}}/{{mysql_tgz}}" dest: /tmp/{{mysql_tgz}} - name: decompress mysql package shell: tar -xf /tmp/{{mysql_tgz}} -C /usr/local/ - name: add mysql conf template: src: my.cnf dest: /etc/my.cnf owner: mysql group: mysql - name: link mysql file: src: /usr/local/{{tgz_extracted}} dest: /usr/local/mysql owner: mysql group: mysql state: link - name: create data directory file: path: '{{db_data}}/{{item}}' state: directory owner: mysql group: mysql recurse: yes loop: - mysql - logs - relaylog - binlog - name: init mysql shell: /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql - name: copy mysql server script copy: src: /usr/local/mysql/support-files/mysql.server dest: /etc/init.d/mysql owner: mysql group: mysql mode: "0755" - name: checkconfig mysql shell: chkconfig --add mysql - name: Ensure mysql is enabled systemd: daemon_reload: yes name: mysql enabled: yes - name: Start mysql service: name: mysql state: restarted enabled: yes - name: Pause for 5 seconds pause: seconds: 5 - name: set mysql root password shell: /usr/local/mysql/bin/mysqladmin -uroot password {{mysql_pass}} - name: add profile for mysql lineinfile: dest=/etc/profile line='{{ item.line }}' with_items: - line: " " - line: "### for mysql " - line: "export PATH=/usr/local/mysql/bin:$PATH" - name: download installation mysqlsh media copy: src: "{{download_target}}/{{mysqlsh_tgz}}" dest: /tmp/{{mysqlsh_tgz}} - name: decompress mysqlsh package shell: tar -xf /tmp/{{mysqlsh_tgz}} -C /usr/local/ - name: link mysqlsh file: src: /usr/local/{{shtgz_extracted}} dest: /usr/local/mysqlsh owner: mysql group: mysql state: link - name: add profile for mysqlsh lineinfile: dest=/etc/profile line='{{ item.line }}' with_items: - line: " " - line: "### for mysqlsh " - line: "export PATH=/usr/local/mysqlsh/bin:$PATH" - name: add dbadmin user command: /usr/local/mysql/bin/mysql -uroot -p{{mysql_pass}} -e "create user root@'%' identified by '{{mysql_pass}}';GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION;GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION;GRANT PROXY ON ``@`` TO `root`@`%` WITH GRANT OPTION ;" - name: mysql cluster set_fact: mysql_cluster_servers_group: [] - name: declare host group set_fact: mysql_cluster_servers_group: "{{mysql_cluster_servers_group}} + [ '{{ item }}' ]" with_items: "{{ groups['mysql_ms'] }}" - name: copy db replicatset files template: src: ../templates/{{ item }} dest: /tmp/{{ item }} mode: 0775 with_items: ['replicaset_configure.js','replicaset_sleep.sql','replicaset_create.js','replicaset_addinstance.js','auto_mysqlroute_pass.sh'] - name: replicaset configure shell: /usr/local/mysqlsh/bin/mysqlsh -uroot -p{{mysql_pass}} -S /db/mysql/mysql/mysqld.sock --file=/tmp/replicaset_configure.js when: cluster_role == "master" - name: replicaset sleep shell: /usr/local/mysqlsh/bin/mysqlsh -uroot -p{{mysql_pass}} -S /db/mysql/mysql/mysqld.sock --file=/tmp/replicaset_sleep.sql when: cluster_role == "master" - name: replicaset create shell: /usr/local/mysqlsh/bin/mysqlsh -uroot -p{{mysql_pass}} -S /db/mysql/mysql/mysqld.sock --file=/tmp/replicaset_create.js when: cluster_role == "master" - name: replicaset sleep shell: /usr/local/mysqlsh/bin/mysqlsh -uroot -p{{mysql_pass}} -S /db/mysql/mysql/mysqld.sock --file=/tmp/replicaset_sleep.sql when: cluster_role == "master" - name: replicaset addinstance shell: /usr/local/mysqlsh/bin/mysqlsh -uroot -p{{mysql_pass}} -S /db/mysql/mysql/mysqld.sock --file=/tmp/replicaset_addinstance.js when: cluster_role == "master" - name: download installation mysql router media copy: src: "{{download_target}}/{{mysql_router}}" dest: /tmp/{{mysql_router}} - name: install mysql router rpm on all node yum: name: '/tmp/{{ item }}' state: present with_items: - "{{ mysql_router }}" - name: mysqlrouter add replicaset shell: /usr/bin/expect -f /tmp/auto_mysqlroute_pass.sh - name: ensure mysqlrouter is enabled systemd: daemon_reload: yes name: mysqlrouter enabled: yes - name: start mysqlrouter service: name: mysqlrouter state: restarted enabled: yes
使用mysql shell创建集群的步骤，这里有sleep原因是太快执行会导致初始化元数据还没有加载导致异常报错
这里需要注意如果是低版本的话，configureReplicaSetInstance时候需要添加一个password参数，代表传入root密码，8.4.0版本不需要这个参数
cat replicaset_configure.js
dba.configureReplicaSetInstance('root@{{ mysql_cluster_servers_group[0] }}:3306', {clusterAdmin: "'repl'@'%'", clusterAdminPassword: 'Repl_xxxx'});
cat replicaset_sleep.sql
select sleep(10);
cat replicaset_create.js
rs = dba.createReplicaSet("replicaset");
cat replicaset_addinstance.js
var rs =dba.getReplicaSet();
rs.addInstance('{{ mysql_cluster_servers_group[1] }}:3306',{recoveryMethod: "clone"});
使用mysql router命令添加集群脚本，因为查看到没有参数直接添加root密码，当前使用expect命令方式自动输入密码，防止交互式中断
cat auto_mysqlroute_pass.sh #!/usr/bin/expect -f set timeout 30 spawn mysqlrouter --bootstrap root@{{ mysql_cluster_servers_group[0] }}:3306 --user=mysqlrouter expect "Please enter MySQL password for root:" send {{ mysql_pass }}\n expect off
【脚本运行情况】
ansible-playbook mysql_ms.yaml PLAY [mysql_ms] ****************************************************************************************************************************************************************************************** TASK [mysql_ms : create mysql user and group] ************************************************************************************************************************************************************ ok: [10.x.x.xx7] ok: [10.x.x.xx8] TASK [mysql_ms : download installation media] ************************************************************************************************************************************************************ ok: [10.x.x.xx8] ok: [10.x.x.xx7] TASK [mysql_ms : decompress mysql package] *************************************************************************************************************************************************************** [WARNING]: Consider using the unarchive module rather than running 'tar'. If you need to use command because unarchive is insufficient you can add 'warn: false' to this command task or set 'command_warnings=False' in ansible.cfg to get rid of this message. changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : add mysql conf] ************************************************************************************************************************************************************************* changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : link mysql] ***************************************************************************************************************************************************************************** ok: [10.x.x.xx7] ok: [10.x.x.xx8] TASK [mysql_ms : create data directory] ****************************************************************************************************************************************************************** changed: [10.x.x.xx8] => (item=mysql) changed: [10.x.x.xx8] => (item=logs) changed: [10.x.x.xx8] => (item=relaylog) changed: [10.x.x.xx8] => (item=binlog) changed: [10.x.x.xx7] => (item=mysql) changed: [10.x.x.xx7] => (item=logs) changed: [10.x.x.xx7] => (item=relaylog) changed: [10.x.x.xx7] => (item=binlog) TASK [mysql_ms : init mysql] ***************************************************************************************************************************************************************************** changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : copy mysql server script] *************************************************************************************************************************************************************** changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : checkconfig mysql] ********************************************************************************************************************************************************************** changed: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : Ensure mysql is enabled] *************************************************************************************************************************************************************** ok: [10.x.x.xx8] ok: [10.x.x.xx7] TASK [mysql_ms : Start mysql] **************************************************************************************************************************************************************************** changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : Pause for 5 seconds] ******************************************************************************************************************************************************************** Pausing for 5 seconds (ctrl+C then 'C' = continue early, ctrl+C then 'A' = abort) ok: [10.x.x.xx7] TASK [mysql_ms : set mysql root password] *************************************************************************************************************************************************************** changed: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : add profile for mysql] ****************************************************************************************************************************************************************** ok: [10.x.x.xx8] => (item={u'line': u' '}) ok: [10.x.x.xx8] => (item={u'line': u'### for mysql '}) ok: [10.x.x.xx8] => (item={u'line': u'export PATH=/usr/local/mysql/bin:$PATH'}) ok: [10.x.x.xx7] => (item={u'line': u' '}) ok: [10.x.x.xx7] => (item={u'line': u'### for mysql '}) ok: [10.x.x.xx7] => (item={u'line': u'export PATH=/usr/local/mysql/bin:$PATH'}) TASK [mysql_ms : download installation mysqlsh media] **************************************************************************************************************************************************** ok: [10.x.x.xx7] ok: [10.x.x.xx8] TASK [mysql_ms : decompress mysqlsh package] ************************************************************************************************************************************************************* changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : link mysqlsh] *************************************************************************************************************************************************************************** changed: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : add profile for mysqlsh] **************************************************************************************************************************************************************** ok: [10.x.x.xx8] => (item={u'line': u' '}) ok: [10.x.x.xx7] => (item={u'line': u' '}) ok: [10.x.x.xx8] => (item={u'line': u'### for mysqlsh '}) ok: [10.x.x.xx7] => (item={u'line': u'### for mysqlsh '}) ok: [10.x.x.xx8] => (item={u'line': u'export PATH=/usr/local/mysqlsh/bin:$PATH'}) ok: [10.x.x.xx7] => (item={u'line': u'export PATH=/usr/local/mysqlsh/bin:$PATH'}) TASK [mysql_ms : add dbadmin user] *********************************************************************************************************************************************************************** changed: [10.x.x.xx7] changed: [10.x.x.xx8] TASK [mysql_ms : mysql cluster] ************************************************************************************************************************************************************************** ok: [10.x.x.xx7] ok: [10.x.x.xx8] TASK [mysql_ms : declare host group] ********************************************************************************************************************************************************************* ok: [10.x.x.xx7] => (item=10.x.x.xx7) ok: [10.x.x.xx7] => (item=10.x.x.xx8) ok: [10.x.x.xx8] => (item=10.x.x.xx7) ok: [10.x.x.xx8] => (item=10.x.x.xx8) TASK [mysql_ms : copy db replicatset files] ************************************************************************************************************************************************************** changed: [10.x.x.xx8] => (item=replicaset_configure.js) ok: [10.x.x.xx7] => (item=replicaset_configure.js) changed: [10.x.x.xx8] => (item=replicaset_sleep.sql) ok: [10.x.x.xx7] => (item=replicaset_sleep.sql) changed: [10.x.x.xx8] => (item=replicaset_create.js) ok: [10.x.x.xx7] => (item=replicaset_create.js) ok: [10.x.x.xx7] => (item=replicaset_addinstance.js) ok: [10.x.x.xx7] => (item=auto_mysqlroute_pass.sh) changed: [10.x.x.xx8] => (item=replicaset_addinstance.js) changed: [10.x.x.xx8] => (item=auto_mysqlroute_pass.sh) TASK [mysql_ms : replicaset configure] ******************************************************************************************************************************************************************* skipping: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : replicaset sleep] *********************************************************************************************************************************************************************** skipping: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : replicaset create] ********************************************************************************************************************************************************************** skipping: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : replicaset sleep] *********************************************************************************************************************************************************************** skipping: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : replicaset addinstance] ***************************************************************************************************************************************************************** skipping: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : download installation mysql router media] *********************************************************************************************************************************************** ok: [10.x.x.xx8] ok: [10.x.x.xx7] TASK [mysql_ms : download installation mysql router media] *********************************************************************************************************************************************** ok: [10.x.x.xx8] ok: [10.x.x.xx7] TASK [mysql_ms : install mysql router rpm on all node] ************************************************************************************************************************************************** changed: [10.x.x.xx8] => (item=[u'/tmp/mysql-router-community-8.4.0-1.el7.x86_64.rpm']) changed: [10.x.x.xx7] => (item=[u'/tmp/mysql-router-community-8.4.0-1.el7.x86_64.rpm']) TASK [mysql_ms : mysqlrouter add replicaset] ************************************************************************************************************************************************************* changed: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : ensure mysqlrouter is enabled] ********************************************************************************************************************************************************** changed: [10.x.x.xx8] changed: [10.x.x.xx7] TASK [mysql_ms : start mysqlrouter] ********************************************************************************************************************************************************************** changed: [10.x.x.xx7] changed: [10.x.x.xx8] PLAY RECAP *********************************************************************************************************************************************************************************************** 10.x.x.xx7 : ok=32 changed=20 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 10.x.x.xx8 : ok=26 changed=16 unreachable=0 failed=0 skipped=5 rescued=0 ignored=0
【查看状态】
MySQL localhost JS > var rs=dba.getReplicaSet(); You are connected to a member of replicaset 'replicaset'. MySQL localhost JS > MySQL localhost JS > rs.status() { "replicaSet": { "name": "replicaset", "primary": "xxx01:3306", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "rac01:3306": { "address": "xxx01:3306", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" }, "rac02:3306": { "address": "xxx02:3306", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 6, "receiverStatus": "ON", "receiverThreadState": "Waiting for source to send event", "replicationLag": null, "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3", "replicationSslMode": "REQUIRED" }, "status": "ONLINE" } }, "type": "ASYNC" } } MySQL localhost JS >
【测试同步】
MySQL localhost JS > \sql Switching to SQL mode... Commands end with ; MySQL localhost SQL > create database sbtest; Query OK, 1 row affected (0.0071 sec) MySQL localhost SQL > use sbtest; Default schema set to `sbtest`. Fetching global names, object names from `sbtest` for auto-completion... Press ^C to stop. MySQL localhost sbtest SQL > create table sbtest(id int primary key,v_name varchar(20)); Query OK, 0 rows affected (0.0486 sec) MySQL localhost sbtest SQL > insert into sbtest values(1,'sbtest1'); Query OK, 1 row affected (0.0064 sec) MySQL localhost sbtest SQL > select * from sbtest.sbtest; +----+---------+ | id | v_name | +----+---------+ | 1 | sbtest1 | +----+---------+ 1 row in set (0.0008 sec)
【测试读写分离】
## MySQL Classic protocol - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 - Read/Write Split Connections: localhost:6450 写测试 mysql -uroot -pxxxxxx -hx.x.x.xxx -P6447 -e 'select @@hostname,@@port' 读测试 mysql -uroot -pxxxxxx -hx.x.x.xxx -P6446 -e "START TRANSACTION; SELECT @@hostname,@@port; ROLLBACK;" 自动读写分离测试 mysql -uroot -pxxxxxx -hx.x.x.xxx -P6450 -e 'select @@hostname,@@port' mysql -uroot -pxxxxxx -hx.x.x.xxx -P6450 -e "START TRANSACTION; SELECT @@hostname,@@port; ROLLBACK;"
【高可用切换测试】
MySQL localhost JS > var rs =dba.getReplicaSet(); You are connected to a member of replicaset 'replicaset'. MySQL localhost JS > rs.status() { "replicaSet": { "name": "replicaset", "primary": "xxx01:3306", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "xxx01:3306": { "address": "xxx01:3306", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" }, "xxx02:3306": { "address": "xxx02:3306", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 6, "receiverStatus": "ON", "receiverThreadState": "Waiting for source to send event", "replicationLag": null, "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3", "replicationSslMode": "REQUIRED" }, "status": "ONLINE" } }, "type": "ASYNC" } } MySQL localhost JS > rs.setPrimaryInstance("xxx02:3306"); xxx02:3306 will be promoted to PRIMARY of 'replicaset'. The current PRIMARY is xxx01:3306. * Connecting to replicaset instances ** Connecting to xxx01:3306 ** Connecting to xxx02:3306 ** Connecting to xxx01:3306 ** Connecting to xxx02:3306 * Performing validation checks ** Checking async replication topology... ** Checking transaction state of the instance... * Synchronizing transaction backlog at xxx02:3306 ** Transactions replicated ############################################################ 100% * Updating metadata * Acquiring locks in ReplicaSet instances ** Pre-synchronizing SECONDARIES ** Acquiring global lock at PRIMARY ** Acquiring global lock at SECONDARIES * Updating replication topology ** Changing replication source of xxx01:3306 to xxx02:3306 xxx02:3306 was promoted to PRIMARY. MySQL localhost JS > rs.status() { "replicaSet": { "name": "replicaset", "primary": "xxx02:3306", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "xxx01:3306": { "address": "xxx01:3306", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for source to send event", "replicationLag": null, "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3", "replicationSslMode": "REQUIRED" }, "status": "ONLINE" }, "xxx02:3306": { "address": "xxx02:3306", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" } }, "type": "ASYNC" } } MySQL localhost JS >
