Ansible Playbook快速部署一主多从MySQL集群
1. 部署目标
1、快速部署一套一主两从的mysql集群
2、部署过程中支持交互式定义安装目录及监听端口号
2. 部署清单目录结构
1root@master:/opt/mysql# tree .
2.
3├── group_vars
4│ └── all.yml
5├── hosts
6├── mysql.yml
7└── roles
8 └── mysql
9 ├── files
10 │ └── mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
11 ├── tasks
12 │ └── main.yml
13 └── templates
14 ├── my.cnf.j2
15 └── mysqld.service.j2
16
176 directories, 7 files
3. 主机清单
定义了需要部署mysql的主机组、IP及设定mysql主机角色
1# 主机清单
2root@master:/opt/mysql# cat hosts
3[mysql]
4192.168.16.140 slave=true
5192.168.16.150 slave=true
6192.168.16.129 master=true
4. 声明变量
声明变量的好处在于用户可以按需改这一个文件，而不需要挨个儿修改部署清单，同时可以在交互式中进行个性化安装设置。
1# 声明变量
2root@master:/opt/mysql# cat group_vars/all.yml
3mysql_pkg: mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
4mysql_version: mysql-5.7.33-linux-glibc2.12-x86_64
5mysql_install_path: /opt/soft
6mysql_link: mysql
7mysql_sock: /tmp/mysql.sock
8mysql_port: 3306
9mysql_root_passwd: Root_123^
10repl_user: repl
11repl_passwd: Repl_123^
12user: mysql
13group: mysql
14master_ip: 192.168.16.129
5. 交互式配置文件
1root@master:/opt/mysql# cat mysql.yml
2---
3- hosts: mysql
4 gather_facts: false
5 vars_prompt:
6 - name: mysql_install_path
7 prompt: 请输入mysql安装目录
8 default: "/opt/soft"
9 private: no
10 - name: mysql_port
11 prompt: 请输入mysql服务的端口
12 default: 3306
13 private: no
14 - name: mysql_sock
15 prompt: 请输入mysql服务的socket文件
16 default: "/tmp/mysql.sock"
17 private: no
18 - name: mysql_root_passwd
19 prompt: 请输入mysql的root密码
20 default: Root_123^
21 private: yes
22 - name: repl_user
23 prompt: 请输入复制用户名
24 default: repl
25 private: no
26 - name: repl_passwd
27 prompt: 请输入复制用户密码
28 default: Repl_123^
29 private: yes
30 - name: user
31 prompt: 请输入mysql服务的启动用户
32 default: mysql
33 private: no
34 roles:
35 - mysql
6. 部署清单
其实就是把手动部署MySQL集群的步骤通过Ansible的相关模块、playbook语法及条件判断进行组合，继而实现自动化部署的过程。原理很简单，但是其中涉及的关于操作MySQL的模块需要着重研究，还有关于template模板的用法也非常重要，在通过Ansible playbook部署一些更复杂的系统时，经常会用到使用template模板语法渲染不同的配置，实现更为复杂系统的部署。比如通过Ansible playbook离线部署Kubernetes集群。
1root@master:/opt/mysql# cat roles/mysql/tasks/main.yml
2---
3- name: "1、创建{{ user }}用户"
4 user:
5 name: "{{ user }}"
6 shell: /bin/bash
7
8- name: "2、创建安装目录"
9 file:
10 path: "{{ mysql_install_path }}"
11 state: directory
12 owner: "{{ user }}"
13 group: "{{ group }}"
14 recurse: yes
15
16- name: "3、解压mysql二进制包"
17 unarchive:
18 src: "{{ mysql_pkg }}"
19 dest: "{{ mysql_install_path }}"
20 owner: "{{ user }}"
21 group: "{{ group }}"
22
23- name: "4、创建数据目录"
24 file:
25 path: "{{ item }}"
26 state: directory
27 owner: "{{ user }}"
28 group: "{{ group }}"
29 recurse: yes
30 with_items:
31 - "{{ mysql_install_path }}/{{ mysql_version }}/data"
32 - "{{ mysql_install_path }}/{{ mysql_version }}/undolog"
33
34- name: "5、修改权限"
35 command: chown -R "{{ user }}:{{ group }}" "{{ mysql_install_path }}"
36
37- name: "6、创建链接文件"
38 file:
39 src: "{{ mysql_install_path }}/{{ mysql_version }}"
40 dest: "{{ mysql_install_path }}/{{ mysql_link }}"
41 owner: "{{ user }}"
42 group: "{{ group }}"
43 state: link
44
45- name: "7、生成配置文件"
46 template:
47 src: my.cnf.j2
48 dest: /etc/my.cnf
49
50- name: "8、数据库初始化"
51 shell: ./mysqld --initialize --user={{ user }} --basedir={{ mysql_install_path }}/{{ mysql_link }} --datadir={{ mysql_install_path }}/{{ mysql_link }}/data
52 args:
53 chdir: "{{ mysql_install_path }}/{{ mysql_link }}/bin"
54
55- name: "9、注册初始密码"
56 shell: cat error.log |grep localhost|grep "temporary password"|awk '{print $NF}'
57 register: mysql_init_passwd
58 args:
59 chdir: "{{ mysql_install_path }}/{{ mysql_link }}/data"
60
61- name: "10、打印初始密码"
62 debug:
63 msg: "{{ mysql_init_passwd.stdout }}"
64
65- name: "11、配置systemd守护进程"
66 template:
67 src: mysqld.service.j2
68 dest: /usr/lib/systemd/system/mysqld.service
69
70- name: "12、启动mysqld服务"
71 systemd:
72 name: mysqld
73 state: started
74 daemon_reload: yes
75 enabled: yes
76
77- name: "13、修改初始密码"
78 shell: ./mysqladmin -u root -p"{{ mysql_init_passwd.stdout }}" password "{{ mysql_root_passwd }}"
79 args:
80 chdir: "{{ mysql_install_path }}/{{ mysql_link }}/bin"
81
82- name: "14、创建{{ repl_user }}同步用户"
83 mysql_user:
84 login_host: localhost
85 login_port: "{{ mysql_port }}"
86 login_user: root
87 login_unix_socket: "{{ mysql_sock }}"
88 login_password: "{{ mysql_root_passwd }}"
89 name: "{{ repl_user }}"
90 password: "{{ repl_passwd }}"
91 priv: "*.*:ALL"
92 state: present
93 host: "%"
94 when: master is defined
95
96- name: "15、从库配置从主库同步"
97 mysql_replication:
98 login_unix_socket: "{{ mysql_sock }}"
99 login_host: localhost
100 login_port: "{{ mysql_port }}"
101 login_user: root
102 login_password: "{{ mysql_root_passwd }}"
103 master_host: "{{ master_ip }}"
104 master_user: "{{ repl_user }}"
105 master_password: "{{ repl_passwd }}"
106 master_port: "{{ mysql_port }}"
107 master_auto_position: 1
108 mode: changemaster
109 when: slave is defined
110
111- name: "16、Start Slave"
112 mysql_replication:
113 login_unix_socket: "{{ mysql_sock }}"
114 login_user: root
115 login_host: localhost
116 login_port: "{{ mysql_port }}"
117 login_password: "{{ mysql_root_passwd }}"
118 mode: startslave
119 when: slave is defined
120
121- name: "17、注册复制状态"
122 mysql_replication:
123 login_host: localhost
124 login_user: root
125 login_port: "{{ mysql_port }}"
126 login_password: "{{ mysql_root_passwd }}"
127 login_unix_socket: "{{ mysql_sock }}"
128 mode: getslave
129 when: slave is defined
130 register: info
131
132- name: "18、打印复制状态信息"
133 debug:
134 msg: "Slave_IO_Running={{ info.Slave_IO_Running }} Slave_SQL_Running={{ info.Slave_SQL_Running }}"
135 when: slave is defined
7. MySQL配置文件
MySQL配置文件中的系统参数可以根据实际按需修改，以下配置只供参考，着重看一下文件中有标注的地方。
1root@master:/opt/mysql# cat roles/mysql/templates/my.cnf.j2
2[client]
3port = {{ mysql_port }}
4socket = {{ mysql_sock }}
5default-character-set=utf8mb4
6
7[mysqldump]
8single-transaction
9
10[mysqld]
11port = {{ mysql_port }}
12socket = {{ mysql_sock }}
13character-set-server=utf8mb4
14sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
15log_bin_trust_function_creators=1
16innodb_flush_log_at_trx_commit=1
17sync_binlog=1
18
19gtid-mode = on
20enforce_gtid_consistency
21log-bin = on
22log-slave-updates = on
23
24#rpl_semi_sync_master_enabled=1
25#rpl_semi_sync_master_timeout=1000
26#rpl_semi_sync_slave_enabled=1
27
28
29master_info_repository = TABLE
30relay_log_info_repository = TABLE
31
32
33replicate-ignore-table=mysql.failover_console
34
35datadir={{ mysql_install_path }}/{{ mysql_link }}/data
36
37# 设置主节点server-id=1，模式为读写；从节点server-id=2,模式为只读
38{% if master is defined %}
39server-id=1
40#read-only=0
41{% else %}
42server-id=2
43#read-only=1
44{% endif %}
45
46
47#relay_log_purge=0
48log_timestamps=SYSTEM
49lower_case_table_names=1
50log_slave_updates=on
51
52skip-name-resolve
53#skip-networking
54back_log = 600
55
56slave_parallel_workers = 16
57slave-parallel-type = LOGICAL_CLOCK
58master_info_repository = TABLE
59relay_log_info_repository = TABLE
60relay_log_recovery = ON
61slave_preserve_commit_order = 1
62
63innodb_undo_directory={{ mysql_install_path }}/{{ mysql_link }}/undolog
64innodb_undo_tablespaces=4
65innodb_undo_logs=128
66innodb_max_undo_log_size=512M
67innodb_purge_rseg_truncate_frequency
68innodb_undo_log_truncate=1
69
70max_connections = 4000
71max_connect_errors = 6000
72open_files_limit = 1024
73table_open_cache = 4096
74table_open_cache_instances = 64
75max_allowed_packet = 128M
76binlog_cache_size = 32M
77max_heap_table_size = 128M
78tmp_table_size = 32M
79read_buffer_size = 8M
80read_rnd_buffer_size = 8M
81sort_buffer_size = 8M
82join_buffer_size = 8M
83key_buffer_size = 8M
84thread_cache_size = 64
85query_cache_type = 0
86query_cache_size = 0
87#query_cache_size = 16M
88#query_cache_limit = 8M
89ft_min_word_len = 4
90log_bin = mysql-bin
91binlog_format = row
92expire_logs_days = 15
93log_error ={{ mysql_install_path }}/{{ mysql_link }}/data/error.log
94slow_query_log = 1
95long_query_time = 3
96performance_schema = 0
97explicit_defaults_for_timestamp
98#lower_case_table_names = 1
99skip-external-locking
100default_storage_engine = InnoDB
101innodb_flush_method = O_DIRECT
102innodb_file_per_table = 1
103innodb_stats_persistent_sample_pages = 64
104innodb_open_files = 10000
105innodb_buffer_pool_size = 2G
106innodb_write_io_threads = 24
107innodb_read_io_threads = 24
108innodb_thread_concurrency = 0
109innodb_purge_threads = 1
110innodb_log_buffer_size = 64M
111innodb_sort_buffer_size = 64M
112innodb_log_file_size = 512M
113innodb_log_files_in_group = 3
114innodb_max_dirty_pages_pct = 75
115innodb_lock_wait_timeout = 120
116#log_warnings=1
117log_error_verbosity=1
118#local-infile=0
119#connection-control-failed-connections-threshold=10
120#connection-control-min-connection-delay=10800
121wait_timeout = 3600
122interactive_timeout = 3600
123innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:5G
8. 部署
1# 部署命令
2root@master:/opt/mysql# ansible-playbook -i hosts mysql.yml
交互式配置，不输入即保持默认配置，如默认端口为3306，我自定义为33306，部署路径我自定义为/opt/software，其他的保持默认直接按回车
1root@master:/opt/mysql# ansible-playbook -i hosts mysql.yml
2请输入mysql安装目录 [/opt/soft]: /opt/software
3请输入mysql服务的端口 [3306]: 33306
4请输入mysql服务的socket文件 [/tmp/mysql.sock]:
5请输入mysql的root密码 [Root 123~]:
6请输入复制用户名[repl]:
7请输入复制用广密码 [Rep] 123~]:
8请输入mysql服务的启动用户 [mysql]:
获取到的初始密码
1TASK [mysql :9、注册初始密码] ****************************
2changed: [192,168,16,129]
3changed: [192.168.16.150]
4changed: [192.168.16.140]
5
6TASK [mysql : 10、打印初始密码] ****************************
7ok: [192.168.16.140] => {
8 "msg":"EZfCR--5B2ui"
9}
10ok: [192.168.16.150] => {
11 "msg":"9frskdQpr3#n"
12}
13ok: [192.168.16.129] => {
14 "msg":"Vih.A%,px7Sp"
15}
部署完成后的截图：
1TASK [mysgl : 16、Start Slave] ************************************************
2skipping: [192.168.16.129]
3changed: [192.168,16,150]
4changed: [192.168.16.140]
5
6TASK [mysql : 17、注册复制状态] ************************************************
7skipping: [192.168.16.129]
8ok: [192.168.16.150]
9ok: [192.168.16.140]
10
11TASK [mysql :18、打印复制状态信息] ************************************************
12ok: [192.168.16.140] => {
13 "msg" : "Slave_IO_Running=Yes Slave_SQL_Running=Yes"
14}
15ok: [192.168.16.150] => {
16 "msg" : "Slave_IO_Running=Yes Slave_SQL_Running=Yes"
17}
18Skipping : [192.168.16.129]
19
20PLAY RECAP ************************************************
21192.168.16.129 : ok=14 changed=12 unreachable=0 failed=0 rescued=0 ignored=0
22192.168.16.140 : ok=14 changed=12 unreachable=0 failed=0 rescued=0 ignored=0
23192.168.16.150 : ok=14 changed=12 unreachable=0 failed=0 rescued=0 ignored=0
可以看到，一主两从已成功部署完成，获取到的复制状态信息正常。
9. 验证
9.1. 登录节点查看状态
1# 主节点
2root@ubuntu:/opt/software# mysql -u root -pRoot_123^ -e "show master status;"
3mysql: [Warning] Using a password on the command line interface can be insecure.
4+------------------+----------+--------------+------------------+------------------------------------------+
5| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
6+------------------+----------+--------------+------------------+------------------------------------------+
7| mysql-bin.000002 | 852 | | | 07c31ccf-208c-11ee-abc5-000c29e5c1ce:1-3 |
8+------------------+----------+--------------+------------------+------------------------------------------+
9
10# 从节点1
11root@work-01:/opt/software# mysql -u root -pRoot_123^ -e "show slave status\G;"
12mysql: [Warning] Using a password on the command line interface can be insecure.
13*************************** 1. row ***************************
14 Slave_IO_State: Waiting for master to send event
15 Master_Host: 192.168.16.129
16 Master_User: repl
17 Master_Port: 33306
18 Connect_Retry: 60
19 Master_Log_File: mysql-bin.000002
20 Read_Master_Log_Pos: 852
21 Relay_Log_File: work-01-relay-bin.000002
22 Relay_Log_Pos: 1065
23 Relay_Master_Log_File: mysql-bin.000002
24 Slave_IO_Running: Yes
25 Slave_SQL_Running: Yes
26
27# 从节点2
28root@work-02:/opt/software# mysql -u root -pRoot_123^ -e "show slave status\G;"
29mysql: [Warning] Using a password on the command line interface can be insecure.
30*************************** 1. row ***************************
31 Slave_IO_State: Waiting for master to send event
32 Master_Host: 192.168.16.129
33 Master_User: repl
34 Master_Port: 33306
35 Connect_Retry: 60
36 Master_Log_File: mysql-bin.000002
37 Read_Master_Log_Pos: 852
38 Relay_Log_File: work-02-relay-bin.000002
39 Relay_Log_Pos: 1065
40 Relay_Master_Log_File: mysql-bin.000002
41 Slave_IO_Running: Yes
42 Slave_SQL_Running: Yes
1root@ubuntu: /opt/software# mysql -u root -pRoot_123^ -e "show master status;"
2+------------------+----------+--------------+------------------+------------------------------------------+
3| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4+------------------+----------+--------------+------------------+------------------------------------------+
5| mysql-bin.000002 | 852 | | | 07c31ccf-208c-11ee-abc5-000c29e5c1ce:1-3 |
6+------------------+----------+--------------+------------------+------------------------------------------+
9.2. 主节点建库，从节点查看同步正常
1
2# 主节点建库
3root@ubuntu:/opt/software# mysql -u root -pRoot_123^ -e "create database db_demo;"
4mysql: [Warning] Using a password on the command line interface can be insecure.
5# 从节点1查看
6root@work-01:/opt/software# mysql -u root -pRoot_123^ -e "show databases;"
7mysql: [Warning] Using a password on the command line interface can be insecure.
8+--------------------+
9| Database |
10+--------------------+
11| information_schema |
12| db_demo |
13| mysql |
14| performance_schema |
15| sys |
16+--------------------+
17# 从节点2查看
18root@work-02:/opt/software# mysql -u root -pRoot_123^ -e "show databases;"
19mysql: [Warning] Using a password on the command line interface can be insecure.
20+--------------------+
21| Database |
22+--------------------+
23| information_schema |
24| db_demo |
25| mysql |
26| performance_schema |
27| sys |
28+--------------------+