N63050 第十二周运维作业 ansible常用模块介绍未完成
2024-09-08 17:09:24
就业和全程班本周学习内容：
二十三、Mysql数据库四
1、MySQL主从复制实现和故障排错(50分钟)
2、MySQL级联复制和主主复制架构实现(51分钟)
3、MySQL的半同步复制和复制过滤器(48分钟)
4、MySQL主从复制问题和MySQL分库分表原理(49分钟)
5、MyCAT实现MySQL读写分离(61分钟)
6、MHA实现MySQL的高可用性(97分钟)
二十四、ANSIBLE运维自动化
1、实现PXC集群和压力测试(65分钟)
2、自动化运维及运维环境介绍(48分钟)
3、ansible的架构和相关文件(45分钟)
4、ansible基本工具使用(55分钟)
5、ansible常用模块(50分钟)
6、ansible常用模块和YAML语言(52分钟)
7、playbook实现常用组件和基本格式(52分钟)
第十二周作业：
1、主从复制及主主复制的实现
1.1 主从复制
主服务器环境搭建
[root@centos8 ~]#yum -y install mysql-server
[root@centos8 ~]#systemctl enable --now mysqld
#二进制开启
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
#修改配置文件
[root@centos8 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=200 #添加内容
log-bin=/data/mysql/logbin/mysql-bin #添加内容
#创建二进制目录并授权
[root@centos8 ~]#mkdir -p /data/mysql/logbin
[root@centos8 ~]#chown -R mysql.mysql /data/mysql/
[root@centos8 ~]#ll /data/mysql/ -d
drwxr-xr-x 3 mysql mysql 20 Aug 24 06:15 /data/mysql/
[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]#ll /data/mysql/logbin
total 8
-rw-r----- 1 mysql mysql 156 Aug 24 06:17 mysql-bin.000001
-rw-r----- 1 mysql mysql 36 Aug 24 06:17 mysql-bin.index
[root@centos8 ~]#ll /data/mysql/logbin/
total 8
-rw-r----- 1 mysql mysql 156 Aug 24 06:17 mysql-bin.000001
-rw-r----- 1 mysql mysql 36 Aug 24 06:17 mysql-bin.index
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.00 sec)
查看二进制节点，需记录，等会在从服务器里填写节点号
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 156 | No |
+------------------+-----------+-----------+
1 row in set (0.00 sec)
#创建复制专有账号，并授权
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
从服务器配置
[root@centos8 ~]#yum -y install mysql-server
[root@centos8 ~]#systemctl enable --now mysqld
#修改配置文件
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server_id=28
read-only
在mysql里找出配置范例，复制出来修改配置
mysql> help change master to
#找到范例相关行
CHANGE MASTER TO
MASTER_HOST='source2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='source2-bin.001',
MASTER_LOG_POS=4,
#更改如下
CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=156；
#在mysql里执行一下
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.200',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
#查看从服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: centos8-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No #进程没有开启
Slave_SQL_Running: No # 进程灭有开启
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 156
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL #与主服务器延迟时间
开启从服务器进程
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+---
| Id | User | Host | db | Command | Time | State | In
+----+-----------------+-----------+------+---------+------+------------------------+---
| 5 | event_scheduler | localhost | NULL | Daemon | 2914 | Waiting on empty queue | NU
| 9 | root | localhost | NULL | Query | 0 | init | sh
+----+-----------------+-----------+------+---------+------+------------------------+---
2 rows in set (0.00 sec)
#开启从服务器进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------
| Id | User | Host | db | Command | Time | State
+----+-----------------+-----------------+------+---------+------+----------------------
| 5 | event_scheduler | localhost | NULL | Daemon | 2937 | Waiting on empty queu
| 9 | root | localhost | NULL | Query | 0 | init
| 10 | system user | connecting host | NULL | Connect | 13 | Waiting for source to
| 11 | system user | | NULL | Query | 1824 | Replica has read all
+----+-----------------+-----------------+------+---------+------+----------------------
4 rows in set (0.00 sec)
主服务器
[mysql> show processlist;
+----+-----------------+----------------+------+-------------+------+-------------------
| Id | User | Host | db | Command | Time | State
+----+-----------------+----------------+------+-------------+------+-------------------
| 5 | event_scheduler | localhost | NULL | Daemon | 2230 | Waiting on empty q
| 8 | root | localhost | NULL | Query | 0 | init
| 9 | repluser | 10.0.0.8:60480 | NULL | Binlog Dump | 77 | Source has sent al
+----+-----------------+----------------+------+-------------+------+-------------------
3 rows in set (0.00 sec)
[root@centos8 ~]#ss -nt
State Recv-Q Send-Q Local
ESTAB 0 36 10.
ESTAB 0 ]()
1.2主主复制
主节点配置
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id=8
log-bin=/data/mysql/mysql-bin
auto_increment_offset=1
auto_increment_increment=2
[root@centos8 ~]#mkdir /data/mysql -p ; chown -R mysql.mysql /data/mysql/
[root@centos8 ~]#systemctl restart mysqld
#查看二进制位置
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
CHANGE MASTER TO
MASTER_HOST='10.0.0.88',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
#再创建登陆的复制账号，注意：一定先查看位置后建立账号，因为节点位置在前，从节点才能把之后创建的账号拷过去，注意顺序
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
从节点配置
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id=18
log-bin=/data/mysql/mysql-bin
auto_increment_offset=2
auto_increment_increment=2
[root@centos8 ~]#mkdir /data/mysql -p ; chown -R mysql.mysql /data/mysql/
[root@centos8 ~]#systemctl restart mysqld
#进入Mysql把主节点的位置参数执行一下
CHANGE MASTER TO
MASTER_HOST='10.0.0.88',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
#两个线程yes表示开启
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes
#Seconds_Behind_Master: 0
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 705 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
再返回主节点，把从节点的位置参数执行一下
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 705 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=705;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
#两个线程yes表示成功
模拟创建数据冲突
mysql> create database da1;
Query OK, 1 row affected (0.01 sec)
mysql> use da1
Database changed
mysql> create table t1 (id int); #此步骤需要XSHELL多窗口执行同意命令
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
#提示1050出现错误
Replicate_Wild_Ignore_Table:
Last_Errno: 1050
Last_Error: Error 'Table 't2' already exists' on query. Default database: 'da1'. Query: 'create table t2 (id int)'
Skip_Counter: 0
Exec_Master_Log_Pos: 1098
Relay_Log_Space: 727
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 't2' already exists' on query. Default database: 'da1'. Query: 'create table t2 (id int)'
#在etc/my.cnf中增加跳过命令
[mysqld]
server-id=8
log-bin=/data/mysql/mysql-bin
auto_increment_offset=1
auto_increment_increment=2
slave_skip_errors=1050 #添加此行，也可以是ALL
#两台机子都要添加，然后重启mysqld，从服务器如果show slave status 看到还是有错误提示，可以等60s或者stop slave 再start slave，冲突平息
2、xtrabackup实现全量+增量+binlog恢复库
#利用xtrabackup8.0 完全，增量备份及还原MySQL8.0,然后二进制还原数据库最新状态
1 备份过程
1）完全备份：
[root@centos8 ~]# xtrabackup -uroot --backup --target-dir=/backup/base
2）第一次修改数据
mysql> insert teacher values(null,'wang',18,'M');
mysql> insert teachers values(null,'mage',18,'M');
3）第一次增量备份
[root@centos8 ~]# xtrabackup -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4）第二次修改数据
mysql> insert teachers values(null,'zhang',19,'M');
mysql> insert teachers values(null,'jie',20,'M');
5）第二次增量
[root@centos8 ~]# xtrabackup -uroot --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#停止并清除数据
[root@centos28 ~]# systemctl stop mysqld
[root@centos28 ~]# rm -rf /var/lib/mysql/*
[root@centos28 ~]# rm -rf /backup/*
6）[root@centos8 ~]# scp -r /backup/* 10.0.0.28:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}
2还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@centos28 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos28 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份
[root@centos28 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
--incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos28 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-
dir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos28 ~]#xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：
[root@centos28 ~]#chown -R mysql:mysql /var/lib/mysql
6）启动服务：
[root@centos28 ~]#systemctl start mysqld
#二进制还原
[root@centos8 ~]# mysqlbinlog /data/mysql/logbin/mysql-bin.000010 > /opt/binlog.sql
[root@centos8 ~]# scp /opt/binlog.sql 10.0.0.28:/root
[root@centos28 ~]# mysql hellodb < binlog.sql
3、MyCAT实现MySQL读写分离
mycat-server 10.0.0.200 #内存建议2G以上
mysql-master 10.0.0.8 MySQL 8.0
mysql-slave 10.0.0.18 MySQL 8.0
1 搭建主从复制数据库
[root@master ~]#yum -y install mysql-server
[root@slave ~]#yum -y install mysql-server
#主节点
[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=8
log-bin=/data/mysql/logbin/mysql-bin
[root@master ~]# mkdir -pv /data/mysql/logbin
[root@master ~]# chown -R mysql.mysql /data/mysql/logbin
[root@master ~]# systemctl restart mysqld
#查看二进制文件和位置
mysql> show master logs;
#如果是MySQL 8.0 需要分成下面两步实现
mysql> create user 'repluser'@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%';
#从节点
[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
read-only
mysql> change master to master_host='10.0.0.8',master_user='repluser',master_password='123456',master_port=3306,master_log_file='centos8-bin.000001',master_log_pos=4,master_connect_retry=681;
mysql> start slave;
mysql> show slave status\G
#进行测试
[root@master ~]# mysql < hellodb_innodb.sql
2在MySQL代理服务器10.0.0.8安装mycat并启动
[root@centos8 ~]#yum -y install java
[root@centos8 ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps
[root@centos8 ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos8 ~]#. /etc/profile.d/mycat.sh
[root@centos8 ~]#mycat start
Starting Mycat-server...
[root@centos8 ~]#tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/09/16 10:56:56 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/09/16 10:56:56 | Launching a JVM...
INFO | jvm 1 | 2022/09/16 10:56:57 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/09/16 10:56:57 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/09/16 10:56:57 |
INFO | jvm 1 | 2022/09/16 10:56:58 | MyCAT Server startup successfully. see logs in logs/mycat.log
3在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@centos8 ~]#vim /apps/mycat/conf/server.xml
#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="frontWriteQueueSize">4096</property> <propertyname="processors">32</property>
.....
<user name="root"> #连接Mycat的用户名
<property name="password">magedu</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应
</user>
</mycat:server>
服务器端千万不要安装mysql，不然改成一样的端口号3306会冲突，导致mycat启动不了
这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都
有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。
4修改schema.xml实现读写分离策略
[root@centos8 ~]#vim /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.18:3306" user="root"
password="123456">
<readHost host="host2" url="10.0.0.28:3306" user="root"
password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
[root@centos8 ~]#mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
[root@centos8 ~]#tail /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2022/09/16 17:10:11 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@centos8 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 128 *:3306 *:*
5在后端主服务器创建用户并对mycat授权
#master节点
mysql> create user root@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to root@'10.0.0.%';
6主从打开通用日志，在客户端连接并测试读写分离
#主服务器和从服务器都要分别打开通用日志才能观察读写分离
mysql> show variables like 'general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/slave.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
[root@master ~]#tail -f /var/lib/mysql/master.log
#在客户端mycat上查看并增改数据，观察是否读写分离
MySQL [TESTDB]> use TESTDB;
Database changed
MySQL [TESTDB]> select * from teachers;
MySQL [TESTDB]> update teachers set age=@@server_id where tid=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
7从服务器如果挂了，主服务器自动提升兼备读写功能
4、ansible常用模块介绍
最新文章
- iOS开发 Xcode8中遇到的问题及改动
- 7.11 数据注解特性--InverseProperty
- easyUI-增删改查
- iOS开发～CocoaPods使用详细说明【转】
- 转--->svn的使用
- homework-04 抓瞎
- http://blog.csdn.net/itplus/article/details/10088625
- [Rosa]Android ListView 适配器原理及优化（转）
- [转载]网络编辑必知常识:什么是PV、UV和PR值 zz
- 在WPF中将某张表中的数据显示到datagrid
- Toad for Oracle的安装
- JAVA学习总结-面向对象
- 史上最全python面试题详解（一）（附带详细答案（持续更新））
- gdb 调试入门，大牛写的高质量指南
- docker容器备份、恢复和迁移volume方案
- 网络之XML解析－原生
- python基础知识梳理----5dict 字典的应用
- 【Win32】对指定进程进行禁音
- XML CDATA是什么？
- Redis介绍与安装