马哥教育N63013第十二周作业

第十二周作业：
1、主从复制及主主复制的实现

###主从复制###

#主节点配置
1.安装mysql
[root@centos8-hkping ~]#yum -y install mysql-server
2.立即并且开机启动mysqld
[root@centos8-hkping ~]#systemctl enable --now mysqld
[root@centos8-hkping ~]#mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
3.检查开启二进制日志
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
4.设置全局唯一的id号并设置二进制日志单独目录
[root@centos8-hkping ~]#cat /etc/my.cnf
[mysqld]
server_id=150
log-bin=/data/mysql/logbin/mysql-bin
[root@centos8-hkping ~]#mkdir -p /data/mysql/logbin
[root@centos8-hkping ~]#chown -R mysql.mysql /data/mysql/
[root@centos8-hkping ~]#systemctl restart mysqld
[root@centos8-hkping ~]#ll /data/mysql/logbin/
total 8
-rw-r----- 1 mysql mysql 156 Mar 15 20:29 mysql-bin.000001
-rw-r----- 1 mysql mysql  36 Mar 15 20:29 mysql-bin.index
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         150 |
+-------------+
1 row in set (0.00 sec)
5.查看从二进制日志的文件和位置开始进行复制
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       156 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)
6.创建有复制权限的用户账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
#从节点配置
1.修改mysql配置文件
[root@centos8-hkping ~]#cat /etc/my.cnf
!includedir /etc/my.cnf.d
[mysqld]
server_id=155
read-only
2.使用有复制权限的用户账号连接至主服务器，并启动复制线程
[root@centos8-hkping ~]#systemctl restart mysqld
mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.150',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=156;
mysql> start slave;
mysql> show slave status\G;

###主从复制(现实环境已有数据库版)###

#主节点配置
1.安装mysql
[root@centos8-hkping ~]#yum -y install mysql-server
2.立即并且开机启动mysqld
[root@centos8-hkping ~]#systemctl enable --now mysqld
[root@centos8-hkping ~]#mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
3.检查开启二进制日志
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
4.设置全局唯一的id号并设置二进制日志单独目录
[root@centos8-hkping ~]#cat /etc/my.cnf
[mysqld]
server_id=150
log-bin=/data/mysql/logbin/mysql-bin
[root@centos8-hkping ~]#mkdir -p /data/mysql/logbin
[root@centos8-hkping ~]#chown -R mysql.mysql /data/mysql/
[root@centos8-hkping ~]#systemctl restart mysqld
[root@centos8-hkping ~]#mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
#备份主节点所有数据库
[root@centos8-hkping ~]#mysqldump -A -F --master-data=1 --single-transaction > /data/all.sql
5.创建有复制权限的用户账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
6.复制数据库备份到从节点
[root@centos8-hkping ~]#scp /data/all.sql 10.0.0.155:/data
#从节点配置
1.修改mysql配置文件
[root@centos8-hkping ~]#cat /etc/my.cnf
!includedir /etc/my.cnf.d
[mysqld]
server_id=155
read-only
2.使用有复制权限的用户账号连接至主服务器，并启动复制线程
[root@centos8-hkping ~]#systemctl restart mysqld
[root@centos8-hkping ~]#vim /data/all.sql
CHANGE MASTER TO
  MASTER_HOST='10.0.0.150',
  MASTER_USER='repluser',
  MASTER_PASSSWORD='123456',
  MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156;
mysql> set sql_log_bin=0;
mysql> source /data/all.sql
mysql> start slave;
mysql> show slave status\G;

###主主复制###

[root@hkping ~]#hostnamectl set-hostname master1
[root@hkping ~]#hostnamectl set-hostname master2
[root@master1 ~]#yum install -y mysql-server
[root@master2 ~]#yum install -y mysql-server
[root@master1 ~]#cat /etc/my.cnf
[mysqld]
server-id=150
log-bin=/data/mysql/mysql-bin
[root@master2 ~]#cat /etc/my.cnf
[mysqld]
server-id=155
log-bin=/data/mysql/mysql-bin
[root@master1 ~]#mkdir /data/myssql -p ; chown -R mysql.mysql /data/mysql/
[root@master2 ~]#mkdir /data/myssql -p ; chown -R mysql.mysql /data/mysql/
[root@master1 ~]#systemctl enable --now mysqld
[root@master2 ~]#systemctl enable --now mysqld
[root@master1 ~]#mysql
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       179 | No        |
| mysql-bin.000002 |       179 | No        |
| mysql-bin.000003 |       156 | No        |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
[root@master2 ~]#mysql
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)
#master2
mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.150',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=156;
mysql> start slave;
mysql> show slave status\G
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       179 | No        |
| mysql-bin.000002 |       695 | No        |
+------------------+-----------+-----------+
#master1
CHANGE MASTER TO
MASTER_HOST='10.0.0.155',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=695;
mysql> start slave;
mysql> show slave status\G

　　2、xtrabackup实现全量+增量+binlog恢复库

#1.安装xtrabackup包 
[root@centos8-hkping /]#ll percona-xtrabackup-80-8.0.23-
[root@centos8-hkping /]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm 
#2.在原主机备份到/backup 
[root@centos8-hkping /]#mkdir /backup 
[root@centos8-hkping /]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base 
#增量备份 
[root@centos8-hkping /]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
 #复制备份目录到目标主机 
[root@centos8-hkping /]#scp -r /backup/* 10.0.0.155:/ 
3.在目标主机上还原 #预准备完成备份，此选项--apply-log-only阻止回滚未完成事务 [root@centos8-hkping /]xtrabackup --apply-log-only --target-dir=/backup/base 
#合并第一次增量备份到完全备份 
[root@centos8-hkping /]xtrabackup --apply-log-only --target-dir=/backup/base--incremental-dir=/backup/inc1 
4.复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动 
[root@centos8-hkping /]xtrabackup --copy-back --target-dir=/backup/base [root@centos8-hkping /]chown -R mysql:mysql /var/lib/mysql 
[root@centos8-hkping /]systemctl start mysqld

3、MyCAT实现MySQL读写分离

#所有主机的系统环境：
[root@centos8-hkping ~]#cat /etc/centos-release
CentOS Linux release 8.5.2111
#服务器共三台
mycat-server 10.0.0.8 #内存建议2G以上
mysql-master 10.0.0.18 MySQL 8.0 或者Mariadb 10.3.17
mysql-slave  10.0.0.28 MySQL 8.0 或者Mariadb 10.3.17
1.安装主从数据库
[root@centos8-hkping ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@centos8-hkping ~]#hostnamectl set-hostname master
[root@centos8-hkping ~]#hostnamectl set-hostname slave
[root@centos8-hkping ~]#hostnamectl set-hostname mycat
[root@centos8-master ~]#yum install -y mysql-server
[root@centos8-slave ~]#yum install -y mysql-server
2.配置主数据库
[root@master ~]#cat /etc/my.cnf
[mysqld]
server-id=155
[root@master ~]#systemctl restart mysqld
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
3.配置从数据库
[root@slave ~]#cat /etc/my.cnf
[mysqld]
server-id=160
[root@slave ~]#systemctl start mysqld
[root@slave ~]#mysql
CHANGE MASTER TO
MASTER_HOST='10.0.0.155',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=156;
mysql> start slave;
mysql> show slave status\G
4.测试主从复制数据
[root@master ~]#ll hellodb_innodb.sql 
[root@master ~]#mysql < hellodb_innodb.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5.安装mycat服务器
[root@mycat ~]#yum -y install java
[root@mycat ~]#mkdir /apps
[root@mycat ~]#ll Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz 
-rw-r--r-- 1 root root 26030477 Mar  3  2021 Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]#. /etc/profile.d/mycat.sh
[root@mycat ~]#mycat start
Starting Mycat-server...
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log 
......
INFO   | jvm 1    | 2022/03/16 16:55:25 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat ~]#ss -ntl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process                 
LISTEN   0        128                    *:8066                 *:* 
6.配置mycat服务器配置文件server.xml
[root@mycat ~]#vim /apps/mycat/conf/server.xml
            <property name="serverPort">3306</property> #修改成3306
            <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> <property name="processors">32</property>
            <property name="password">magedu</property> #修改密码
7.配置mycat服务器配置文件schema.xml
[root@mycat ~]#cp /apps/mycat/conf/schema.xml /apps/mycat/conf/schema.xml.bak2
[root@mycat ~]#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="true" 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.155:3306" user="root" password="123456">
                    <readHost host="host1" url="10.0.0.160:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>
8.重启mycat
[root@mycat ~]#mycat restart
[root@mycat ~]#ss -ntl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process                
LISTEN   0        128                    *:3306                 *:* 
9.验证主从节点读写分离
#主节点
mysql> create user root@'10.0.0.%' identified by '123456';
mysql> grant all on *.* to root@'10.0.0.%';
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| repluser         | 10.0.0.%  |
| root             | 10.0.0.%  |
#客户端测试
[root@centos7 ~]# mysql -uroot -pmagedu -h 10.0.0.150
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
MySQL [(none)]> use TESTDB
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         160 |
+-------------+
#从节点开启通用日志，测试读操作
mysql> show variables like 'general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF                      |
| general_log_file | /var/lib/mysql/slave.log |
+------------------+--------------------------+
mysql> set global general_log=1;
[root@slave ~]#tail -f /var/lib/mysql/slave.log
2022-03-16T09:37:30.778464Z       31 Query    select * from teachers
#主节点开启通用日志，测试写操作
mysql> set global general_log=1;
[root@master ~]#tail -f /var/lib/mysql/master.log
2022-03-16T09:39:43.037074Z       47 Query    update teachers set age=40 where tid=4
#测试从节点挂了
[root@slave ~]#systemctl stop mysqld
#主节点自动顶替从节点读功能
MySQL [TESTDB]> select * from teachers;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: TESTDB

+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  40 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.06 sec)
#恢复从节点后，查询重新在从节点进行
[root@slave ~]#systemctl start mysqld
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         160 |
+-------------+
#主节点挂了，任何操作都会失败
[root@master ~]#systemctl stop mysqld
MySQL [TESTDB]> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused

4、ansible常用模块介绍

1.Command模块
在远程主机执行命令，此为默认模块，可忽略-m选项
2.Shell模块
和command相似，用shell执行命令，支持各种符合，比如:*,$,>
3.Script模块
在远程主机上运行ansible服务器上的脚步（无需执行权限）
4.Copy模块
从ansible服务器主控端复制文件到远程主机
5.Get_url模块
用于将文件从http、https或ftp下载到被管理机节点上
6.Fetch模块
从远程主机提取文件至ansible的主控端，copy相反，目前不支持目录
7.File模块
设置文件属性，创建软链接等
8.stat模块
检查文件或文件系统的状态
9.unarchive模块
解包解压缩
10.Archive模块
打包压缩保存在被管理节点
11.Hostname模块
管理 主机名
12.Cron模块
计划任务
13.Yum和Apt模块
yum管理软件包，只支持RHEL、Centos、fdora，不支持ubuntu其它版本
14.yum_repository模块
帮助我们管理远程主机上的yum仓库
15.Service模块
管理服务
16.User模块
管理用户
17.Group模块
管理组
18.Lineinfile模块
修改某个文件的单行进行替换的时候使用
19.Replace模块
基于正则进行匹配和替换
20.SELinux模块
管理SELinx策略
21.reboot模块
重启计算机模块
22.mount模块
挂载和卸载文件系统
23.Setup模块
setup模块来收集主机的系统信息
24.debug模块
用于输出信息，并且通过msg定制输出的信息内容