建设工程东莞网站建设技术支持,简单大气好记的公司名称,官方网站建设制作平台,专业网站制作咨询部署目标#xff1a; 1、快速部署一套一主两从的mysql集群 2、部署过程中支持交互式定义安装目录及监听端口号 部署清单目录结构#xff1a;
rootmaster:/opt/mysql# tree .
.
├── group_vars
│ └── all.yml
├── hosts
├── mysql.yml
└── roles└── mys…部署目标 1、快速部署一套一主两从的mysql集群 2、部署过程中支持交互式定义安装目录及监听端口号 部署清单目录结构
rootmaster:/opt/mysql# tree .
.
├── group_vars
│ └── all.yml
├── hosts
├── mysql.yml
└── roles└── mysql├── files│ └── mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz├── tasks│ └── main.yml└── templates├── my.cnf.j2└── mysqld.service.j26 directories, 7 files主机清单
定义了需要部署mysql的主机组、IP及设定mysql主机角色
# 主机清单
rootmaster:/opt/mysql# cat hosts
[mysql]
192.168.16.140 slavetrue
192.168.16.150 slavetrue
192.168.16.129 mastertrue声明变量
声明变量的好处在于用户可以按需改这一个文件而不需要挨个儿修改部署清单同时可以在交互式中进行个性化安装设置。
# 声明变量
rootmaster:/opt/mysql# cat group_vars/all.yml
mysql_pkg: mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
mysql_version: mysql-5.7.33-linux-glibc2.12-x86_64
mysql_install_path: /opt/soft
mysql_link: mysql
mysql_sock: /tmp/mysql.sock
mysql_port: 3306
mysql_root_passwd: Root_123^
repl_user: repl
repl_passwd: Repl_123^
user: mysql
group: mysql
master_ip: 192.168.16.129 交互式配置文件
rootmaster:/opt/mysql# cat mysql.yml
---
- hosts: mysqlgather_facts: falsevars_prompt:- name: mysql_install_pathprompt: 请输入mysql安装目录default: /opt/softprivate: no- name: mysql_portprompt: 请输入mysql服务的端口default: 3306private: no- name: mysql_sockprompt: 请输入mysql服务的socket文件default: /tmp/mysql.sockprivate: no- name: mysql_root_passwdprompt: 请输入mysql的root密码default: Root_123^ private: yes- name: repl_userprompt: 请输入复制用户名default: replprivate: no- name: repl_passwdprompt: 请输入复制用户密码default: Repl_123^private: yes- name: userprompt: 请输入mysql服务的启动用户default: mysqlprivate: noroles:- mysql部署清单
其实就是把手动部署MySQL集群的步骤通过Ansible的相关模块、playbook语法及条件判断进行组合继而实现自动化部署的过程。原理很简单但是其中涉及的关于操作MySQL的模块需要着重研究还有关于template模板的用法也非常重要在通过Ansible playbook部署一些更复杂的系统时经常会用到使用template模板语法渲染不同的配置实现更为复杂系统的部署。比如通过Ansible playbook离线部署Kubernetes集群。
---
- name: 1、创建{{ user }}用户user:name: {{ user }}shell: /bin/bash- name: 2、创建安装目录file:path: {{ mysql_install_path }}state: directoryowner: {{ user }}group: {{ group }}recurse: yes- name: 3、解压mysql二进制包unarchive:src: {{ mysql_pkg }}dest: {{ mysql_install_path }}owner: {{ user }}group: {{ group }}- name: 4、创建数据目录file:path: {{ item }}state: directoryowner: {{ user }}group: {{ group }}recurse: yeswith_items:- {{ mysql_install_path }}/{{ mysql_version }}/data- {{ mysql_install_path }}/{{ mysql_version }}/undolog- name: 5、修改权限command: chown -R {{ user }}:{{ group }} {{ mysql_install_path }}- name: 6、创建链接文件file:src: {{ mysql_install_path }}/{{ mysql_version }}dest: {{ mysql_install_path }}/{{ mysql_link }}owner: {{ user }}group: {{ group }}state: link- name: 7、生成配置文件template:src: my.cnf.j2dest: /etc/my.cnf- name: 8、数据库初始化shell: ./mysqld --initialize --user{{ user }} --basedir{{ mysql_install_path }}/{{ mysql_link }} --datadir{{ mysql_install_path }}/{{ mysql_link }}/dataargs:chdir: {{ mysql_install_path }}/{{ mysql_link }}/bin- name: 9、注册初始密码shell: cat error.log |grep localhost|grep temporary password|awk {print $NF}register: mysql_init_passwdargs: chdir: {{ mysql_install_path }}/{{ mysql_link }}/data- name: 10、打印初始密码debug: msg: {{ mysql_init_passwd.stdout }}- name: 11、配置systemd守护进程template:src: mysqld.service.j2dest: /usr/lib/systemd/system/mysqld.service- name: 12、启动mysqld服务systemd:name: mysqldstate: starteddaemon_reload: yesenabled: yes- name: 13、修改初始密码shell: ./mysqladmin -u root -p{{ mysql_init_passwd.stdout }} password {{ mysql_root_passwd }}args:chdir: {{ mysql_install_path }}/{{ mysql_link }}/bin- name: 14、创建{{ repl_user }}同步用户mysql_user: login_host: localhostlogin_port: {{ mysql_port }}login_user: rootlogin_unix_socket: {{ mysql_sock }} login_password: {{ mysql_root_passwd }}name: {{ repl_user }}password: {{ repl_passwd }}priv: *.*:ALLstate: present host: %when: master is defined- name: 15、从库配置从主库同步mysql_replication:login_unix_socket: {{ mysql_sock }}login_host: localhostlogin_port: {{ mysql_port }}login_user: root login_password: {{ mysql_root_passwd }}master_host: {{ master_ip }} master_user: {{ repl_user }} master_password: {{ repl_passwd }}master_port: {{ mysql_port }}master_auto_position: 1mode: changemasterwhen: slave is defined- name: 16、Start Slavemysql_replication: login_unix_socket: {{ mysql_sock }}login_user: root login_host: localhostlogin_port: {{ mysql_port }}login_password: {{ mysql_root_passwd }}mode: startslavewhen: slave is defined- name: 17、注册复制状态mysql_replication:login_host: localhostlogin_user: rootlogin_port: {{ mysql_port }}login_password: {{ mysql_root_passwd }}login_unix_socket: {{ mysql_sock }}mode: getslavewhen: slave is definedregister: info- name: 18、打印复制状态信息debug:msg: Slave_IO_Running{{ info.Slave_IO_Running }} Slave_SQL_Running{{ info.Slave_SQL_Running }}when: slave is definedMySQL配置文件
MySQL配置文件中的系统参数可以根据实际按需修改以下配置只供参考着重看一下文件中有标注的地方。
rootmaster:/opt/mysql# cat roles/mysql/templates/my.cnf.j2
[client]
port {{ mysql_port }}
socket {{ mysql_sock }}
default-character-setutf8mb4[mysqldump]
single-transaction[mysqld]
port {{ mysql_port }}
socket {{ mysql_sock }}
character-set-serverutf8mb4
sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators1
innodb_flush_log_at_trx_commit1
sync_binlog1gtid-mode on
enforce_gtid_consistency
log-bin on
log-slave-updates on#rpl_semi_sync_master_enabled1
#rpl_semi_sync_master_timeout1000
#rpl_semi_sync_slave_enabled1master_info_repository TABLE
relay_log_info_repository TABLEreplicate-ignore-tablemysql.failover_consoledatadir{{ mysql_install_path }}/{{ mysql_link }}/data# 设置主节点server-id1模式为读写从节点server-id2,模式为只读
{% if master is defined %}
server-id1
#read-only0
{% else %}
server-id2
#read-only1
{% endif %}#relay_log_purge0
log_timestampsSYSTEM
lower_case_table_names1
log_slave_updatesonskip-name-resolve
#skip-networking
back_log 600slave_parallel_workers 16
slave-parallel-type LOGICAL_CLOCK
master_info_repository TABLE
relay_log_info_repository TABLE
relay_log_recovery ON
slave_preserve_commit_order 1innodb_undo_directory{{ mysql_install_path }}/{{ mysql_link }}/undolog
innodb_undo_tablespaces4
innodb_undo_logs128
innodb_max_undo_log_size512M
innodb_purge_rseg_truncate_frequency
innodb_undo_log_truncate1max_connections 4000
max_connect_errors 6000
open_files_limit 1024
table_open_cache 4096
table_open_cache_instances 64
max_allowed_packet 128M
binlog_cache_size 32M
max_heap_table_size 128M
tmp_table_size 32M
read_buffer_size 8M
read_rnd_buffer_size 8M
sort_buffer_size 8M
join_buffer_size 8M
key_buffer_size 8M
thread_cache_size 64
query_cache_type 0
query_cache_size 0
#query_cache_size 16M
#query_cache_limit 8M
ft_min_word_len 4
log_bin mysql-bin
binlog_format row
expire_logs_days 15
log_error {{ mysql_install_path }}/{{ mysql_link }}/data/error.log
slow_query_log 1
long_query_time 3
performance_schema 0
explicit_defaults_for_timestamp
#lower_case_table_names 1
skip-external-locking
default_storage_engine InnoDB
innodb_flush_method O_DIRECT
innodb_file_per_table 1
innodb_stats_persistent_sample_pages 64
innodb_open_files 10000
innodb_buffer_pool_size 2G
innodb_write_io_threads 24
innodb_read_io_threads 24
innodb_thread_concurrency 0
innodb_purge_threads 1
innodb_log_buffer_size 64M
innodb_sort_buffer_size 64M
innodb_log_file_size 512M
innodb_log_files_in_group 3
innodb_max_dirty_pages_pct 75
innodb_lock_wait_timeout 120
#log_warnings1
log_error_verbosity1
#local-infile0
#connection-control-failed-connections-threshold10
#connection-control-min-connection-delay10800
wait_timeout 3600
interactive_timeout 3600
innodb_temp_data_file_path ibtmp1:200M:autoextend:max:5G部署
# 部署命令rootmaster:/opt/mysql# ansible-playbook -i hosts mysql.yml交互式配置不输入即保持默认配置如默认端口为3306我自定义为33306部署路径我自定义为/opt/software其他的保持默认直接按回车 获取到的初始密码 部署完成后的截图 可以看到一主两从已成功部署完成获取到的复制状态信息正常。
**验证
**
登录节点查看状态
# 主节点
rootubuntu:/opt/software# mysql -u root -pRoot_123^ -e show master status;
mysql: [Warning] Using a password on the command line interface can be insecure.
------------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
------------------------------------------------------------------------------------------------------
| mysql-bin.000002 | 852 | | | 07c31ccf-208c-11ee-abc5-000c29e5c1ce:1-3 |
------------------------------------------------------------------------------------------------------# 从节点1
rootwork-01:/opt/software# mysql -u root -pRoot_123^ -e show slave status\G;
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.16.129Master_User: replMaster_Port: 33306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 852Relay_Log_File: work-01-relay-bin.000002Relay_Log_Pos: 1065Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes# 从节点2
rootwork-02:/opt/software# mysql -u root -pRoot_123^ -e show slave status\G;
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.16.129Master_User: replMaster_Port: 33306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 852Relay_Log_File: work-02-relay-bin.000002Relay_Log_Pos: 1065Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes主节点建库从节点查看同步正常
# 主节点建库
rootubuntu:/opt/software# mysql -u root -pRoot_123^ -e create database db_demo;
mysql: [Warning] Using a password on the command line interface can be insecure.
# 从节点1查看
rootwork-01:/opt/software# mysql -u root -pRoot_123^ -e show databases;
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------------
| Database |
--------------------
| information_schema |
| db_demo |
| mysql |
| performance_schema |
| sys |
--------------------
# 从节点2查看
rootwork-02:/opt/software# mysql -u root -pRoot_123^ -e show databases;
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------------
| Database |
--------------------
| information_schema |
| db_demo |
| mysql |
| performance_schema |
| sys |
--------------------以上便是这篇文章的全部内容。文章内容仅供参考如有纰漏欢迎大家留言指正。
- 文章转载自： http://www.morning.snxbf.cn.gov.cn.snxbf.cn http://www.morning.yongkangyiyuan-pfk.com.gov.cn.yongkangyiyuan-pfk.com http://www.morning.mjtft.cn.gov.cn.mjtft.cn http://www.morning.hympq.cn.gov.cn.hympq.cn http://www.morning.mdlqf.cn.gov.cn.mdlqf.cn http://www.morning.kyhnl.cn.gov.cn.kyhnl.cn http://www.morning.rdkt.cn.gov.cn.rdkt.cn http://www.morning.ptxwg.cn.gov.cn.ptxwg.cn http://www.morning.mttqp.cn.gov.cn.mttqp.cn http://www.morning.fwnqq.cn.gov.cn.fwnqq.cn http://www.morning.brnwc.cn.gov.cn.brnwc.cn http://www.morning.hdrrk.cn.gov.cn.hdrrk.cn http://www.morning.rykgh.cn.gov.cn.rykgh.cn http://www.morning.ynrzf.cn.gov.cn.ynrzf.cn http://www.morning.gcfg.cn.gov.cn.gcfg.cn http://www.morning.xqltq.cn.gov.cn.xqltq.cn http://www.morning.rqpgk.cn.gov.cn.rqpgk.cn http://www.morning.nkwgy.cn.gov.cn.nkwgy.cn http://www.morning.pmdzd.cn.gov.cn.pmdzd.cn http://www.morning.lyhrg.cn.gov.cn.lyhrg.cn http://www.morning.txfxy.cn.gov.cn.txfxy.cn http://www.morning.qpmmg.cn.gov.cn.qpmmg.cn http://www.morning.rshkh.cn.gov.cn.rshkh.cn http://www.morning.rjynd.cn.gov.cn.rjynd.cn http://www.morning.qrzqd.cn.gov.cn.qrzqd.cn http://www.morning.gpcy.cn.gov.cn.gpcy.cn http://www.morning.fhghy.cn.gov.cn.fhghy.cn http://www.morning.cljpz.cn.gov.cn.cljpz.cn http://www.morning.wnjbn.cn.gov.cn.wnjbn.cn http://www.morning.xsgxp.cn.gov.cn.xsgxp.cn http://www.morning.jhyfb.cn.gov.cn.jhyfb.cn http://www.morning.wqngt.cn.gov.cn.wqngt.cn http://www.morning.mbaiwan.com.gov.cn.mbaiwan.com http://www.morning.xjqrn.cn.gov.cn.xjqrn.cn http://www.morning.jgcxh.cn.gov.cn.jgcxh.cn http://www.morning.gsqw.cn.gov.cn.gsqw.cn http://www.morning.mprtj.cn.gov.cn.mprtj.cn http://www.morning.mjgxl.cn.gov.cn.mjgxl.cn http://www.morning.xnqjs.cn.gov.cn.xnqjs.cn http://www.morning.jkzq.cn.gov.cn.jkzq.cn http://www.morning.grjh.cn.gov.cn.grjh.cn http://www.morning.rzmlc.cn.gov.cn.rzmlc.cn http://www.morning.ylpl.cn.gov.cn.ylpl.cn http://www.morning.mjctt.cn.gov.cn.mjctt.cn http://www.morning.gwqcr.cn.gov.cn.gwqcr.cn http://www.morning.dmtld.cn.gov.cn.dmtld.cn http://www.morning.gltmz.cn.gov.cn.gltmz.cn http://www.morning.mntxalcb.com.gov.cn.mntxalcb.com http://www.morning.zztmk.cn.gov.cn.zztmk.cn http://www.morning.psyrz.cn.gov.cn.psyrz.cn http://www.morning.kryr.cn.gov.cn.kryr.cn http://www.morning.xmtzk.cn.gov.cn.xmtzk.cn http://www.morning.nlkhr.cn.gov.cn.nlkhr.cn http://www.morning.ypdhl.cn.gov.cn.ypdhl.cn http://www.morning.pprxs.cn.gov.cn.pprxs.cn http://www.morning.qsy39.cn.gov.cn.qsy39.cn http://www.morning.xsfny.cn.gov.cn.xsfny.cn http://www.morning.xqbbc.cn.gov.cn.xqbbc.cn http://www.morning.rhsg.cn.gov.cn.rhsg.cn http://www.morning.hsgxj.cn.gov.cn.hsgxj.cn http://www.morning.dqcpm.cn.gov.cn.dqcpm.cn http://www.morning.kjyfq.cn.gov.cn.kjyfq.cn http://www.morning.zrlms.cn.gov.cn.zrlms.cn http://www.morning.zhffz.cn.gov.cn.zhffz.cn http://www.morning.jrlgz.cn.gov.cn.jrlgz.cn http://www.morning.rpjr.cn.gov.cn.rpjr.cn http://www.morning.grjh.cn.gov.cn.grjh.cn http://www.morning.dmxzd.cn.gov.cn.dmxzd.cn http://www.morning.fqcdh.cn.gov.cn.fqcdh.cn http://www.morning.qjtbt.cn.gov.cn.qjtbt.cn http://www.morning.ysckr.cn.gov.cn.ysckr.cn http://www.morning.tdmgs.cn.gov.cn.tdmgs.cn http://www.morning.jbkcs.cn.gov.cn.jbkcs.cn http://www.morning.trqzk.cn.gov.cn.trqzk.cn http://www.morning.mmclj.cn.gov.cn.mmclj.cn http://www.morning.wkwds.cn.gov.cn.wkwds.cn http://www.morning.ztjhz.cn.gov.cn.ztjhz.cn http://www.morning.wdrxh.cn.gov.cn.wdrxh.cn http://www.morning.qnhpq.cn.gov.cn.qnhpq.cn http://www.morning.kxwsn.cn.gov.cn.kxwsn.cn
