营销微网站建设公司,wordpress用HTML,设计平台官网,wordpress rest登录文章目录 Ansible 自动化运维工具部署主从数据库读写分离一、主从复制和读写分离介绍二、准备工作#xff08;1#xff09;节点规划#xff08;2#xff09;修改主机名#xff08;3#xff09;免密#xff08;4#xff09;配置IP映射#xff08;5#xff09;安装ansi… 文章目录 Ansible 自动化运维工具部署主从数据库读写分离一、主从复制和读写分离介绍二、准备工作1节点规划2修改主机名3免密4配置IP映射5安装ansible6配置主机清单文件 三、目录结构1创建项目目录2创建角色目录3创建变量目录4init角色5编写剧本入口文件 四、编写角色任务文件1init初始化任务文件2mysql-master任务文件3mysql-slave任务文件4mysql-create-testdb任务文件5mycat任务文件 五、执行playbook六、验证主从复制读写分离1登录mycat管理窗口2读写分离验证 Ansible 自动化运维工具部署主从数据库读写分离
一、主从复制和读写分离介绍
数据库主从复制是一种常见的数据库架构用于提高数据库的可用性、可扩展性和性能。它通过将写操作主节点复制到一个或多个从节点来实现数据的同步。
主从复制的工作原理如下
首先将一个数据库节点指定为主节点所有的写操作都在主节点上执行。主节点将写操作记录到二进制日志binary log中并将这些日志发送到从节点。从节点接收到二进制日志后将其应用到自己的数据库上实现数据的同步。
读写分离是基于主从复制的核心思想是将读操作分发到多个从节点上而将写操作集中在主节点上。Mycat是一个开源的数据库中间件支持读写分离将读操作和写操作分别分发到不同的数据库节点上
二、准备工作
1节点规划
准备三台服务器centos7.9.2009
IP节点192.168.100.10ansible192.168.100.20master192.168.100.30slave192.168.100.40mycat
2修改主机名
1. 修改主机名
# ansible节点
[rootlocalhost ~]# hostnamectl set-hostname ansible
[rootlocalhost ~]# bash
[rootansible ~]#
# master节点
[rootlocalhost ~]# hostnamectl set-hostname master
[rootlocalhost ~]# bash
[rootmaster ~]#
# slave节点
[rootlocalhost ~]# hostnamectl set-hostname slave
[rootlocalhost ~]# bash
[rootslave ~]#
# mycat节点
[rootlocalhost ~]# hostnamectl set-hostname mycat
[rootlocalhost ~]# bash
[rootmycat ~]# 3免密
[rootansible ~]# ssh-keygen
[rootmaster ~]# ssh-keygen
[rootslave ~]# ssh-keygen
[rootmycat ~]# ssh-keygen[rootansible ~]# ssh-copy-id 192.168.100.20
[rootansible ~]# ssh-copy-id 192.168.100.30
[rootansible ~]# ssh-copy-id 192.168.100.404配置IP映射
[rootansible ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.10 ansible
192.168.100.20 master
192.168.100.30 slave
192.168.100.40 mycat5安装ansible
[rootansible ~]# yum install -y epel-release vim tree
[rootansible ~]# yum install -y ansible
[rootansible ~]# ansible --version
ansible 2.9.27config file /etc/ansible/ansible.cfgconfigured module search path [u/root/.ansible/plugins/modules, u/usr/share/ansible/plugins/modules]ansible python module location /usr/lib/python2.7/site-packages/ansibleexecutable location /usr/bin/ansiblepython version 2.7.5 (default, Oct 14 2020, 14:45:30) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]6配置主机清单文件
[rootansible ~]# vim /etc/ansible/hosts
[master]
192.168.100.20
[slave]
192.168.100.30
[mycat]
192.168.100.40[rootansible ~]# ansible all -m ping
192.168.100.30 | SUCCESS {ansible_facts: {discovered_interpreter_python: /usr/bin/python}, changed: false, ping: pong
}
192.168.100.20 | SUCCESS {ansible_facts: {discovered_interpreter_python: /usr/bin/python}, changed: false, ping: pong三、目录结构
1创建项目目录
[rootansible ~]# mkdir mycat_mariadb
[rootansible ~]# cd mycat_mariadb/2创建角色目录
在创建roles之前我们将数据库读写分离部署的步骤分为 3个role执行这样更加易懂
init初始化
mysql主从复制
mycat读写分离
[rootansible mycat_mariadb]# mkdir -p roles/{mysql-master,mysql-slave,init,mycat,mysql-create-testdb}/{templates,tasks,files} 这次项目中会用到template、tasks、files目录可以选择性创建自己用到哪个创建哪个
3创建变量目录
创建该目录用来存放变量
[rootansible mycat_mariadb]# mkdir group_vars
[rootansible mycat_mariadb]# vim group_vars/all
PASSWD: 000000
master_host: 192.168.100.20
slave_host: 192.168.100.30
mycat_host: 192.168.100.40
shujuku: testdb4init角色
这一步操作是用来创建数据库主从复制以及读写分离的配置文件 master-my.cnf.j2文件内容 [rootansible mycat_mariadb]# vim roles/mysql-master/files/master-my.cnf
[mysqld]
datadir/var/lib/mysql
socket/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id20
log_bin mysql-bin
binlog-ignore-dbmysql
binlog-do-dbtestdb
binlog_formatSTATEMENT
[mysqld_safe]
log-error/var/log/mariadb/mariadb.log
pid-file/var/run/mariadb/mariadb.pid#
# include all files from the config directory
#
!includedir /etc/my.cnf.dslave-my.cnf.j2 文件内容 [rootansible mycat_mariadb]# vim roles/mysql-slave/files/slave-my.cnf[mysqld]
datadir/var/lib/mysql
socket/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemdserver_id30
relay-logmysql-relay
[mysqld_safe]
log-error/var/log/mariadb/mariadb.log
pid-file/var/run/mariadb/mariadb.pid#
# include all files from the config directory
#
!includedir /etc/my.cnf.dserver.xml.j2 文件内容 [rootansible mycat_mariadb]# vim roles/mycat/templates/server.xml.j2
?xml version1.0 encodingUTF-8?
!-- - - Licensed under the Apache License, Version 2.0 (the License); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an AS IS BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --
!DOCTYPE mycat:server SYSTEM server.dtd
mycat:server xmlns:mycathttp://io.mycat/systemproperty nameuseSqlStat0/property !-- 1为开启实时统计、0为关闭 --property nameuseGlobleTableCheck0/property !-- 1为开启全加班一致性检测、0为关闭 --property namesequnceHandlerType2/property!-- property nameuseCompression1/property-- !--1为开启mysql压缩协议--!-- property namefakeMySQLVersion5.6.20/property-- !--设置模拟的MySQL版本号--!-- property nameprocessorBufferChunk40960/property --!-- property nameprocessors1/property property nameprocessorExecutor32/property --!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena--property nameprocessorBufferPoolType0/property!--默认是65535 64K 用于sql解析时最大文本长度 --!--property namemaxStringLiteralLength65535/property--!--property namesequnceHandlerType0/property--!--property namebackSocketNoDelay1/property--!--property namefrontSocketNoDelay1/property--!--property nameprocessorExecutor16/property--!--property nameserverPort8066/property property namemanagerPort9066/property property nameidleTimeout300000/property property namebindIp0.0.0.0/property property namefrontWriteQueueSize4096/property property nameprocessors32/property --!--分布式事务开关0为不过滤分布式事务1为过滤分布式事务如果分布式事务内只涉及全局表则不过滤2为不过滤分布式事务,但是记录分布式事务日志--property namehandleDistributedTransactions0/property!--off heap for merge/order/group/limit 1开启 0关闭--property nameuseOffHeapForMerge1/property!--单位为m--property namememoryPageSize1m/property!--单位为k--property namespillsFileBufferSize1k/propertyproperty nameuseStreamOutput0/property!--单位为m--property namesystemReserveMemorySize384m/property!--是否采用zookeeper协调切换 --property nameuseZKSwitchtrue/property/system!-- 全局SQL防火墙设置 --!-- firewall whitehosthost host127.0.0.1 usermycat/host host127.0.0.2 usermycat//whitehostblacklist checkfalse/blacklist/firewall--user namerootproperty namepassword{{PASSWD}}/propertyproperty nameschemasTESTDB/property!-- 表级 DML 权限设置 --!-- privileges checkfalseschema nameTESTDB dml0110 table nametb01 dml0000/tabletable nametb02 dml1111/table/schema/privileges--/useruser nameuserproperty namepassword{{PASSWD}}/propertyproperty nameschemasTESTDB/propertyproperty namereadOnlytrue/property/user/mycat:serverschema.xml.j2 文件内容 [rootansible mycat_mariadb]# vim roles/mycat/templates/schema.xml.j2 ?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/schema nameTESTDB checkSQLschemafalse sqlMaxLimit100 dataNodedn1/schemadataNode namedn1 dataHosthost1 database{{shujuku}} /dataHost namehost1 maxCon1000 minCon10 balance3writeType0 dbTypemysql dbDrivernative switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hosthostM1 url{{master_host}}:3306 userrootpassword{{PASSWD}}readHost hosthostS1 url{{slave_host}}:3306 userroot password{{PASSWD}} //writeHost/dataHost
/mycat:schema5编写剧本入口文件
将调用roles的顺序及哪些主机调用哪些roles在这个文件夹中体现出来
[rootansible mycat_mariadb]# vim mycat_mariadb.yaml
- hosts: allremote_user: rootroles:- init- hosts: masterremote_user: rootroles:- mysql-master- hosts: slaveremote_user: rootroles:- mysql-slave- hosts: mycatremote_user: rootroles:- mycat四、编写角色任务文件
1init初始化任务文件
[rootansible tasks]# vim /root/mycat_mariadb/roles/init/tasks/main.yaml - name: 配置所有主机的host映射copy: src/etc/hosts dest/etc/hosts- name: 关闭防火墙shell: systemctl stop firewalld systemctl disable firewalld setenforce 0- name: 安装mariadb expectshell: yum install -y mariadb-server expect- name: 设置开机自启并开启mariadbshell: systemctl enable mariadb --now- name: 设置密码shell: mysqladmin -uroot password {{PASSWD}}- name: 安装MySQL-pythonshell: yum install -y MySQL-python2mysql-master任务文件
[rootansible tasks]# vim /root/mycat_mariadb/roles/mysql-master/tasks/main.yaml - name: 移动文件copy: srcmaster-my.cnf dest/etc/my.cnf- name: 重新启动mariadbshell: systemctl restart mariadb- name: 设置root用户访问权限shell: mysql -uroot -p{{PASSWD}} -e GRANT ALL PRIVILEGES ON *.* TO root% IDENTIFIED BY {{PASSWD}};flush privileges;- name: 创建数据库用户用于复制shell: mysql -uroot -p{{PASSWD}} -e grant replication slave on *.* to csq192.168.100.% identified by {{PASSWD}};- name: 存放变量Log_nameshell: mysql -uroot -p{{PASSWD}} -e show master status;|awk NR2{print $1}register: file
- name: 存放变量File_sizeshell: mysql -uroot -p{{PASSWD}} -e show master status;|awk NR2{print $2}register: pot- name: 设置全局变量set_fact: masterbin{{ file.stdout_lines[0] }}
- name: 设置全局变量set_fact: position{{ pot.stdout_lines[0] }} 3mysql-slave任务文件
[rootansible mycat_mariadb]# vim roles/mysql-slave/tasks/main.yaml- name: 移动文件copy: srcslave-my.cnf dest/etc/my.cnf
- name: 重启mariadbshell: systemctl restart mariadb- name: 设置root用户访问权限shell: mysql -uroot -p{{PASSWD}} -e GRANT ALL PRIVILEGES ON *.* TO root% IDENTIFIED BY {{PASSWD}};flush privileges;- name: 设置主数据信息mysql_replication:login_user: rootlogin_password: 000000mode: changemastermaster_user: csqmaster_password: 000000master_host: 192.168.100.20master_log_file: {{ hostvars[192.168.100.20][masterbin] }}master_log_pos: {{ hostvars[192.168.100.20][position] }}- name: 开启slaveshell: mysql -uroot -p{{PASSWD}} -e start slave; - name: 定义输出的信息shell: mysql -uroot -p{{PASSWD}} -e show slave status\G; | grep -E Slave_IO_Running|Slave_SQL_Runningregister: slave_status_output
- name: 打印输出的信息debug:var: slave_status_output.stdout4mysql-create-testdb任务文件
[rootansible mycat_mariadb]# vim roles/mysql-create-testdb/tasks/main.yaml
- name: 创建用于复制的testdb库shell: mysql -uroot -p000000 -e create database testdb;
- name: 创建表插入数据shell: mysql -uroot -p000000 -e use testdb; create table mytbl(id int,name varchar(20));insert into mytbl values(1,csq);5mycat任务文件
[rootansible mycat_mariadb]# vim roles/mycat/tasks/main.yaml- name: 设置root用户访问权限shell: mysql -uroot -p{{PASSWD}} -e GRANT ALL PRIVILEGES ON *.* TO root% IDENTIFIED BY {{PASSWD}};flush privileges;- name: 安装openjdkshell: yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel wget net-tools- name: 下载Mycat软件包shell: wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz- name: 解压Mycat软件包到/usr/localshell: tar -zxvf /root/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/- name: 复制模板文件template: srcschema.xml.j2 dest/usr/local/mycat/conf/schema.xml- name: 复制模板文件template: srcserver.xml.j2 dest/usr/local/mycat/conf/server.xml- name: 开启mycatshell: /bin/bash /usr/local/mycat/bin/mycat start
五、执行playbook
[rootansible mycat_mariadb]# ansible-playbook mycat_mariadb.yaml PLAY [all] ************************************************************************************************************************************************************TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.40]
ok: [192.168.100.30]
ok: [192.168.100.20]TASK [init : 配置所有主机的host映射] *******************************************************************************************************************************************
changed: [192.168.100.40]
changed: [192.168.100.20]
changed: [192.168.100.30]TASK [init : 关闭防火墙] ***************************************************************************************************************************************************
changed: [192.168.100.40]
changed: [192.168.100.30]
changed: [192.168.100.20]TASK [init : 安装mariadb expect] ****************************************************************************************************************************************
[WARNING]: Consider using the yum module rather than running yum. If you need to use command because yum is insufficient you can add warn: false to this command
task or set command_warningsFalse in ansible.cfg to get rid of this message.
changed: [192.168.100.40]
changed: [192.168.100.30]
changed: [192.168.100.20]TASK [init : 设置开机自启并开启mariadb] ****************************************************************************************************************************************
changed: [192.168.100.30]
changed: [192.168.100.40]
changed: [192.168.100.20]TASK [init : 设置密码] ****************************************************************************************************************************************************
changed: [192.168.100.30]
changed: [192.168.100.20]
changed: [192.168.100.40]TASK [init : 安装MySQL-python] ******************************************************************************************************************************************
changed: [192.168.100.40]
changed: [192.168.100.30]
changed: [192.168.100.20]PLAY [master] *********************************************************************************************************************************************************TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.20]TASK [mysql-master : 移动文件] ********************************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-master : 重新启动mariadb] *************************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-master : 设置root用户访问权限] ************************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-master : 创建数据库用户用于复制] *************************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-master : 存放变量Log_name] ************************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-master : 存放变量File_size] ***********************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-master : 设置全局变量] ******************************************************************************************************************************************
ok: [192.168.100.20]TASK [mysql-master : 设置全局变量] ******************************************************************************************************************************************
ok: [192.168.100.20]PLAY [slave] **********************************************************************************************************************************************************TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.30]TASK [mysql-slave : 移动文件] *********************************************************************************************************************************************
changed: [192.168.100.30]TASK [mysql-slave : 重启mariadb] ****************************************************************************************************************************************
changed: [192.168.100.30]TASK [mysql-slave : 设置root用户访问权限] *************************************************************************************************************************************
changed: [192.168.100.30]TASK [mysql-slave : 设置主数据信息] ******************************************************************************************************************************************
changed: [192.168.100.30]TASK [mysql-slave : 开启slave] ******************************************************************************************************************************************
changed: [192.168.100.30]TASK [mysql-slave : 定义输出的信息] ******************************************************************************************************************************************
changed: [192.168.100.30]TASK [mysql-slave : 打印输出的信息] ******************************************************************************************************************************************
ok: [192.168.100.30] {slave_status_output.stdout: Slave_IO_Running: Yes\n Slave_SQL_Running: Yes
}PLAY [master] *********************************************************************************************************************************************************TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.20]TASK [mysql-create-testdb : 创建用于复制的testdb库] ***************************************************************************************************************************
changed: [192.168.100.20]TASK [mysql-create-testdb : 创建表插入数据] **********************************************************************************************************************************
changed: [192.168.100.20]PLAY [mycat] **********************************************************************************************************************************************************TASK [Gathering Facts] ************************************************************************************************************************************************
ok: [192.168.100.40]TASK [mycat : 设置root用户访问权限] *******************************************************************************************************************************************
changed: [192.168.100.40]TASK [mycat : 安装openjdk] **********************************************************************************************************************************************
changed: [192.168.100.40]TASK [mycat : 下载Mycat软件包] *********************************************************************************************************************************************
[WARNING]: Consider using the get_url or uri module rather than running wget. If you need to use command because get_url or uri is insufficient you can add warn:
false to this command task or set command_warningsFalse in ansible.cfg to get rid of this message.
changed: [192.168.100.40]TASK [mycat : 解压Mycat软件包到/usr/local] **********************************************************************************************************************************
[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_warningsFalse in ansible.cfg to get rid of this message.
changed: [192.168.100.40]TASK [mycat : 复制模板文件] *************************************************************************************************************************************************
changed: [192.168.100.40]TASK [mycat : 复制模板文件] *************************************************************************************************************************************************
changed: [192.168.100.40]TASK [开启mycat] ********************************************************************************************************************************************************
changed: [192.168.100.40]PLAY RECAP ************************************************************************************************************************************************************
192.168.100.20 : ok19 changed14 unreachable0 failed0 skipped0 rescued0 ignored0
192.168.100.30 : ok15 changed12 unreachable0 failed0 skipped0 rescued0 ignored0
192.168.100.40 : ok15 changed13 unreachable0 failed0 skipped0 rescued0 ignored0 在执行过程中会打印出这两条信息如果都是YES 说明你主从配置成功了 六、验证主从复制读写分离
1登录mycat管理窗口
[rootmycat ~]# mysql -uroot -p000000 -h127.0.0.1 -P 9066查看读写配置情况
MySQL [(none)] show datasource;
---------------------------------------------------------------------------------------------------------
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
---------------------------------------------------------------------------------------------------------
| dn1 | hostM1 | mysql | 192.168.100.20 | 3306 | W | 0 | 10 | 1000 | 57 | 0 | 1 |
| dn1 | hostS1 | mysql | 192.168.100.30 | 3306 | R | 0 | 5 | 1000 | 53 | 2 | 0 |
---------------------------------------------------------------------------------------------------------
参数详解
DATANODE数据节点的名称
NAME数据节点的标识名称
TYPE数据节点的类型这里是mysql
HOST数据节点的主机地址
PORT数据节点的端口号
W/R数据节点的读写类型W表示写入R表示读取
ACTIVE当前活跃连接数
IDLE当前空闲连接数
ZE连接池大小即连接池中的最大连接数
EXECUTE执行次数表示该数据节点的执行操作次数
READ_LOAD读取负载表示该数据节点的读取负载情况查看心跳信息
MySQL [(none)] show heartbeat;
----------------------------------------------------------------------------------------------------------------
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
----------------------------------------------------------------------------------------------------------------
| hostM1 | mysql | 192.168.100.20 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2023-09-13 15:27:30 | false |
| hostS1 | mysql | 192.168.100.30 | 3306 | 1 | 0 | idle | 0 | 1,0,1 | 2023-09-13 15:27:30 | false |
----------------------------------------------------------------------------------------------------------------
参数详解
NAME心跳节点的名称
TYPE心跳节点的类型这里是mysql
HOST心跳节点的主机地址
PORT心跳节点的端口号
RS_CODE复制状态码用于判断复制是否正常进行 (1表示主从复制正常进行)
RETRY重试次数表示心跳节点尝试重连的次数
STATUS心跳节点的状态包括idle空闲和active活跃
TIMEOUT超时时间表示心跳节点的超时时间
EXECUTE_TIME执行时间表示心跳节点的执行时间
LAST_ACTIVE_TIME最后活跃时间表示心跳节点最后一次发送心跳的时间2读写分离验证
[rootmycat ~]# mysql -uroot -p000000 -h127.0.0.1 -P 8066
MySQL [(none)] use TESTDB;
MySQL [TESTDB] insert into mytbl values(1,hostname); # 插入数据
MySQL [TESTDB] select * from mytbl; # 查询数据自动跳转从主机进行查询
-------------
| id | name |
-------------
| 1 | csq |
| 1 | slave |
-------------
# hostname是MySQL系统变量表示当前数据库服务器的主机名。
# 由此可以推断select * from mytbl;查询的是从库。使用master节点访问一下
[rootmaster ~]# mysql -uroot -p000000 -e use testdb;select * from mytbl;
--------------
| id | name |
--------------
| 1 | csq |
| 1 | master |使用slave节点访问一下
[rootslave ~]# mysql -uroot -p000000 -e use testdb;select * from mytbl;
-------------
| id | name |
-------------
| 1 | csq |
| 1 | slave |
-------------至此 Ansible部署主从数据库读写分离结束
