mysql实验&ansible常用模块

1、主从复制及主主复制的实现

主从复制
1.1主节点
```html/xml
#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysqlmysql密码忘记了怎么办
[root@localhost ~]# yum -y install mys星门老鹰ql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enmysql密码忘记了怎么办ablcentos7网络配置e mysqld
#查看mysql初始密码
[root@localhost ~]#mysql grep 'root@localhost:' /var/log/mmysql安装配置教程ysqld.log
2022-02-20T05:33:52.076247Z 1 [Note] A temporary password is generated for root@localhost: R<V:GU:ra3O9
#设置密码
[root@localhost ~]# mysqlalinuxdmin -uroot -p'R<V:GU:ra3O9' password 'Root123!'
#修改配置
[root@localhost ~]# vim /etc/my厦门理工学院.cnf
[mysqld]
server-id=1
log-bin
#创建有复制权限的用户账号
mysql> grant replication slave on . to repluser@'10.0.0.linux是什么操作系统%' identified by 'Root123!';

#查看二进制文件和位置

mysql> show master logs;
+---------喜马拉雅-------------+-----------+
| Log_name |小米路由器登录入口 File_size |
+---linux系统安装-------------------+mysql密码忘记了怎么办-----------西门龙霆+
| lo许慕离calhost-bin.000001 | 154mysql增删改查语句 |
+----------------------+---------mysql数据库命令大全--+
1 row in set (厦门理工学院0.00 sec)

1.2从节点
```html/xml
#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2022-02-20T11:55:37.969845Z 1 [Note] A temporary password is generated for root@localhost: ib=Lw8r9q3tN
#设置密码
[root@localhost ~]# mysqladmin -uroot -p'ib=Lw8r9q3tN' password 'Root123!'
#导入数据库文件
[root@localhost ~]# mysql -hlocalhost -uroot -p'Root123!' < hellodb.sql
#修改配置
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=18
[root@localhost ~]# systemctl restart mysqld
#使用有复制权限的用户账号连接至主服务器,并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.153',MASTER_USER='repluser',MASTER_PASSWORD='Root123!', MASTER_PORT=3306,MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=759;
mysql> start slave;
mysql> show slave status\G

主主复制
1.2第一个master
```html/xml
#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-communicentos查看系统版本ty-release-el7-11.noarch.rpm
#安装mys西门龙霆ql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqldcentos7重置root密码
#查看mysql初始密码
[root@localhost ~]# grep 'root@mysql数据库llinux操作系统基础知识ocalhost:' /var/log/mysqld.log
2022-02-22T06:56:57.727527Z 1 [Note] A temporary password is generated for root@lolinux系统calhost: QmtSPzqXW0m
#修改密码
[root@localhost ~]# mysqladmin -uroot -p'QmtSPzqXW0
m' password'Root123!'

#使用新密码登录
mysql> grant replication slave on . tlinuxo repluser@'10.0.0.%'
identified by 'Root123!';
#导入数据库mysql文件
[root@localhost ~]# mysql -uroot -p'Root123!' < hellodb.sql
#修改配置
[root@localhost ~]# vim /et西门龙霆c/my.cnf
[mysqld]
server-id=8
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度

#重启
[root@localhost ~]# systemctl restart mysqld
#linux查看节点
[root@localhost ~]# mysql -hlocalhost -uroot -p
mysql> show master logs;
+-mysql密码忘记了怎么办---------------------+-----------+
| Log_ncentosame | File_size |
+----------------------+centos7重置root密码------mysql安装配置教程-----+
| localhost-bin.000001 |centos关机命令 456 |
+---mysql安装配置教程-------------------centos查看系统版本+-----------+
1 row in setmysql密码忘记了怎么办 (0.00 sec)
#创建可复制账户
mysql> grant replication slave on . to repluser@'10.0.0.%' identified by 'Root123!';

1.2.1第二个master
```html/xml
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -hlocalhost -uroot -p
#创建可复制账户
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'Root123!';
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.153',
MASTER_USER='repluser',
MASTER_PASSWORD='Root123!',
MASTER_PORT=3306,
MASTER_LOG_FILE='localhost-bin.000001',
MASTER_LOG_POS=456;
#启动节点
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看二进制文件的位置
mysql> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |       456 |
+----------------------+-----------+
1 row in set (0.00 sec)

1.2.linux重启命令2第一个mascentos是什么系统ter
```html/xml
[root@localhost ~]# mysql -hlocalhostlinux命令 -ulinux命令roomysql安装t -p
mysql> CHANGE MASTER TO
CHANGE MASTER TOMASTER_HOST='192.168.2.158',
MASTER_USER='repluser',
MASTER_PASSWORD='Jhd2020@',
br/>MASTER_HOST='192.168.2.158',
MASTER_USER=centos查看系统版本'repluser',
MASTER_PASSWORD='Jhd2020@',
MASTER_PORT=3306,
MASTER_LOG_FILE='localhost-bin.000001',
MASTER_LOG_POS=4centos是什么系统56;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#在第一个master节点操作
mysql&linux命令gt; create database db1;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> use db1
Database changed
mysql> create table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.01 seclinux)

mysql> insert t1 (name) values('usermysql密码忘记了怎么办1');
Query OK, 1 row affected (0.centos7重置root密码00 sec)

mysql> insert t1 (name) values('user2');
Query OK, 1 row affected (0.00 sec)

#在第二个master节点进行查询
mysql> selinuxlect * from t1;centos查看系统版本
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
+----+-------+
1 row in set (0.00 sec)


##### 2、xtrabackup实现全量+增量+binlog恢复库
2.1安装xtrabackup安装mysql
```html/xml
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2022-20-23T06:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#设置密码
[root@localhost ~]# mysqladmin -uroot -p'QmtSPzqXW0*m' password'Root123!'

2喜马拉雅.3 导入测试数据,实现增量备份
```html/xml
#导入数据库
[root@localhost ~]# mysql -uroot -p'Root123!' < hellodb.sql
#创建数据库
[root@localhost ~]# mysql -urooxmlt -p
mysql> create database db1;
Query OK, 1 row afcentos7重置root密码fected (0.00 sec)

mysql>
mysql> use db1
Database changed
mysql> create厦门理工学院 table t1(id int auto_increment primary key,name char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 (name) v喜马拉雅acentos7关闭防火墙lues('user1');
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 (name) values('user2centos查看系统版本');
Query OKlinux系统, 1 row affected (0.00 sec)

1、完全备份

[root@localhost ~]# mkdir /backup/
[root@localhost ~]# xtrabackup -uroot -p'Root123!' --backup --target-dir=/backup/base

2、第一次修改数据系统运维工作内容

mysql> inser小米路由器t t1 (name) values('user3');
Query OK, 1 row affected (0.00 sec)

mysql> insert t1 (name) values('user4');
Query OK, 1 row affected (系统运维工资一般多少0.01 sec)

3、第一次增量备份

[root@localhost ~]# xtrabackup -uroot -p'Root123!' --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

4、第二次修改数据

mysql> insert t1 (name) va星门老鹰lues('user5');
Query OK, 1 row af系统运维工程师fected (0.00 sec)

mysql> insert t1 (name) values('user6');
Query OK, 1 row affectelinux系统安装d (0.00 sec)

5、第二linux删除文件命令次增量备份

[root@localhost ~]# xtrabackup -uroot -p'R喜马拉雅听书免费版官方下载oot123!' --backup --targcentos7网络配置et-dir=/backup/inc2 --incremental-basedilinux操作系统基础知识r=/backup/inc1

6、src传输到目标主机

[root@localhost ~]# scp -r /centos7安装教程backup/* 10.0.0.156:/backup/
#备份过centos程生成三个备份目录
/backup/{base,inc1,inc2}

2还linux是什么操作系统原过程

1)预准备完成备份,此选项--apply-lxmlog-only 阻止回滚未完成的事务
[root@localhost ~]# xtrabackup --prepa小米路由器re --apply-log-only --target-dir=/backup/base
2)合并第1次增量备份到完全备份,
[root@localhost ~]# xtrabackupcentos关机命令 -linux是什么操作系统-prepare --apply-log-only --target-linuxdir=/backup/base --incremental-dir=/backup/inc1
3)合并第2次增量备份到完全备份:最后一次还原不需要加选项--apply-log-only
[root@localhost ~]# xtrabackup --prepare --targcentos7配置ip地址et-dirlinux常用命令=/backup/base --incremental-dir=/backup/inc2
4)复制到数据库目录,注意数据库目录必须为空,MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --targmysql索引et-dir=/backup/星门老鹰base
5)还原属性:
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql
6)启动服务:
[root@localhost ~]# systemctl start mysqld

2.4完全备份
```html/xml
#安装
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2022-20-23T07:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#设置密码
[root@localhost ~]# mysqladmin  -uroot -p'QmtSPzqXW0*m' password'Root123!'
# 在原主机上进行备份到/backup
[root@localhost ~]# xtrabackup -uroot -p'Root123!' --backup --target-dir=/backup/
#复制到目标主机
[root@localhost ~]# scp -r /backup/* 10.0.0.156:/backup/
# 在目标主机上还原
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql
3、MyCAT实现MySQL读写分喜马拉雅

```html/xml
mlinux常用命令ysql-master 10.0.0.152
mysql-slave 10.0.0.153
mycat-server喜马拉雅 10.0.0.156

关闭防火墙、SELinux,并进行时间同步
systemctl stop firewalinux系统安装lld
setenforce 0
nntpdate ntp.aliylinux命令un.com

3.1、创建mysql 主从数据库
```html/xml
#下载mysql5.7
[root@localhost ~]# systemctl stop firewall
[root@localhost ~]# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装mysql rpm源
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
[root@localhost ~]# yum -y install mysql-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
#查看mysql初始密码
[root@localhost ~]# grep 'root@localhost:' /var/log/mysqld.log
2022-20-23T07:56:57.727527Z 1 [Note] A temporary password is generated for root@localhost: QmtSPzqXW0*m
#设置密码
[root@localhost ~]# mysqladmin  -uroot -p'QmtSPzqXW0*m' password'Root123!'

3.2.1、修改master和slave上的西门龙霆配置文linux命令
```html/xml
#master上linux系统的my.cnf
[root@centos7 ~]# vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin

#slamysql数据库ve上的my厦门理工学院.cnf
[root@centos17 ~]# vim /etc/my.cnf
[mysqld]
server-id = 2

3.2.2、master上创建复制用户
```html/xml
[root@centos7 ~]# mysql -uroot -p
mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'Root123!';
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| centos7-bin.000002 |      306 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.152',
MASTER_USER='repluser',
MASTER_PASSWORD='Root123!',
MASTER_LOG_FILE='centos7-bin.000002',
MASTER_LOG_POS=306;

3.2.3、slave上执行
```html/xml
[root@centos17 ~]# mysql -urootmysql面试题 -p
mysql> CHAmysql数据库NGE MASTER TO
MASTER_HOST='10.0.0.152',
MASTER_USER='repluser',
MASmysql创建表TER_PASSWxmlORD='Root12linux系统安装3!',
MASTER_LOG_FILE='centos7-bin.000002',
MAScentos7关闭防火墙TER_LOG_POS=306;

mysql> start slave;
Query OK, 0 rows affec许慕离ted (0.00 sec)
mysql> show slave status\G
1. row
Slave_IO_State:
Maste小米路由器r_Host: 10.0.0.152
Master_User: repluser
Master_Port: 3306
Connect_Relinuxtry: 60
Master_Log_Ficentos关机命令le: cent系统运维工资一般多少os7-linuxbin.000002
Read_Master_Lo系统运维工程师g_Pos: 306
Relay_Log小米路由器登录入口_File: centos17-rcentos7配置ip地址elay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: centomysql面试题s7-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.3、在10.0.0.156的服务器上安装mycat 并启动
```html/xml
[root@centos27 ~]# yum -y install mysql
[root@centos27 ~]# yum -y install java
#确认安装成功
[root@centos27 ~]# java -version
openjdk version "1.8.0_282"
OpenJDK Runtime Environment (build 1.8.0_282-b08)
OpenJDK 64-Bit Server VM (build 25.282-b08, mixed mode)
#下载并安装
[root@centos27 ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@centos27 ~]# mkdir /app
[root@centos27 ~]# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /app/
[root@centos27 ~]# echo 'PATH=/app/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos27 ~]# source /etc/profile.d/mycat.sh 
[root@centos27 ~]# ss -ntl
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN     0      100            127.0.0.1:25                                 *:*                  
LISTEN     0      128                    *:111                                *:*                  
LISTEN     0      128                    *:22                                 *:*                  
LISTEN     0      100                [::1]:25                              [::]:*                  
LISTEN     0      128                 [::]:111                             [::]:*                  
LISTEN     0      128                 [::]:22                              [::]:* 
[root@centos27 ~]# mycat start
Starting Mycat-server...
[root@centos27 ~]# ss -ntlp
State      Recv-Q Send-Q     Local Address:Port                    Peer Address:Port              
LISTEN     0      100            127.0.0.1:25                                 *:*                   users:(("master",pid=2626,fd=13))
LISTEN     0      1              127.0.0.1:32000                              *:*                   users:(("java",pid=2720,fd=4))
LISTEN     0      128                    *:111                                *:*                   users:(("rpcbind",pid=708,fd=4),("systemd",pid=1,fd=54))
LISTEN     0      128                    *:22                                 *:*                   users:(("sshd",pid=1127,fd=3))
LISTEN     0      100                [::1]:25                              [::]:*                   users:(("master",pid=2626,fd=14))
LISTEN     0      50                  [::]:1984                            [::]:*                   users:(("java",pid=2720,fd=65))
LISTEN     0      100                 [::]:8066                            [::]:*                   users:(("java",pid=2720,fd=86))
LISTEN     0      100                 [::]:9066                            [::]:*                   users:(("java",pid=2720,fd=82))
LISTEN     0      50                  [::]:44523                           [::]:*                   users:(("java",pid=2720,fd=66))
LISTEN     0      128                 [::]:111                             [::]:*                   users:(("rpcbind",pid=708,fd=6),("systemd",pid=1,fd=58))
LISTEN     0      50                  [::]:39636                           [::]:*                   users:(("java",pid=2720,fd=64))
LISTEN     0      128                 [::]:22                              [::]:*                   users:(("sshd",pid=1127,fd=4))
#查看日志,确定成功,可能需要等一会儿才能看到成功的提示
[root@centos27 ~]# tail -f /app/mycat/logs/wrapper.log 
STATUS | wrapper  | 2021/04/18 16:38:10 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/04/18 16:38:10 | Launching a JVM...
INFO   | jvm 1    | 2021/04/18 16:38:10 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/04/18 16:38:10 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/04/18 16:38:10 | 
INFO   | jvm 1    | 2021/04/18 16:38:12 | MyCAT Server startup successfully. see logs in logs/mycat.log
#用默认密码123456来连接mycat
[root@centos27 ~]# mysql -uroot -p'123456' -h10.0.0.27 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| address          |
| travelrecord     |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from travelrecord;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0

3.4、在mycat服务器上修centos安装改server.xml文件配置mycat的连接信息
```html/xml
[root@centos27 ~]# vim /app/mycat/conf/sercentos7重置root密码vecentos安装r.xmlinux删除文件命令l
<user name=&quomysql密码忘记了怎么办t;root" defaultAccount="true">
<property name=&mysql索引quot;password">Root123!</property>
<property name="schemas"&linux系统安装gt;TESTDB</property>
<property name="defaultSchema">TESTDB</property>
</user>

3.5、修改schema.xml实现读写分离策略
```html/xml
[root@centos27 ~]# vim /app/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="mycat" />
<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.152:3306" user="root"
password="Root123!">
<readHost host="host2" url="10.0.0.153:3306" user="root"
password="Root123!" />
</writeHost>
</dataHost>
</mycat:schema>
#重启mycat
[root@centos27 ~]# mycat restart

3.6、在后端主服务器创建用户并对mycat授权
```html/xmcentos7网络配置l
[root@centos7 ~]# mysql -uroot -p < mycat.sql
[root@centos7 ~]# mysql -uroot -p
mysql>GRANT ALL ON . TO 'root'@'10.0.0.%' IDENTIFIED BY 'Root123!' WITH GRANT OPTION;
mysql> fmysql创建表lushcentos查看系统版本 privileges;

3.7、在mycat服务器上连接并测试
```html/xml
[root@centos27 ~]# mysql -uroot -p'Root123!' -h127.0.0.1 -P8066 -DTESTDB
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |        #只能看到一个虚拟数据库
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.01 sec)
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.01 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| centos17    |
+------------+
1 row in set (0.01 sec)

3小米路由器登录入口.8、通过通用日志确认实现读写分离
```html/xml
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+-许慕离--------系统运维工程师------+-------+
| general_log | OFFlinux是什么操作系统 |
+---------------+---centos查看系统版本----+
1 row in set (0.00 sec)

mylinuxsql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'gencentos7重置root密码eral_log_file';
+------------------+------------linux----------------+
| Variable_name | Value |
+------------------+----------------------------+
| generalinux是什么操作系统l_log_file | /var/lib/mysql/centos7.log |
+-----------linux命令-------+----------------------------+
1 row in set (0.00 sec)

在主和从服务器分别启用通用日志,查看读写分离
```html/xml
[root@centos7 ~]# vim /etc/my.cnf
[mysqld]
general_log=ON
[root@centos7 ~]# systemctl restart mysqld
[root@centos7 ~]# tail -f /var/lib/mysql/centos7.log 
[root@centos17 ~]# vim /etc/my.cnf
[mysqld]
general_log=ON
[root@centos17 ~]# systemctl restart mysqld
[root@centos17 ~]# tail -f /var/lib/mysql/centos17.log 

3.9、停止从节点,MyCAT自动调度读请求至主节点
```html/linux重启命令xml
#主节点centos7配置ip地址停止前
[root@centos27 ~]# mysql -uroocentos7网络配置t -p'Root123!' -h127.0.0.1 -P8066
mysql> select @@sermysql安装ver_id;
+-------------+
| @@server_id |
+-------------+星门老鹰
| 2 |
+-------------+
1 row in set (0.00 sec)

mysql&glinux必学的60个命令t; select @@hostname;
+------------+
| @@hostname |
+------------+
| centos17 |
+------------+
1 row in se喜马拉雅t (0.00 sec)

#停止从节点后
[root@centos17 ~]# syst小米路由器emctl stop mysqld
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

mysql> selectcentos7安装教程 @@hostname;
+------------+
| @@hoscentos是什么系统tname |
+centos是什么系统------------+
| centos7 |
+------------+
1 row in set (喜马拉雅0.00 sec)


##### 4、ansible常用模块介绍
###### ping 模块
ping模块尝尝用于检测网络是否通畅
```html/xml
ansible -i hosts all -m ping
group 模块

group用户在目标机器上xml创建/删除用户组
```htm星门老鹰l/xml
#创建小米路由器登录入口用户组
ansible -i hosts bigops -m group -a "gid=3000 name=algrolinuxup state=present system=no" -b

#参数解释
gid:指定组gid号,如果不指定,默认在主机上向后延伸gid号
namemysql增删改查语句:指定组名称
state:指定组状系统运维工程师态,值可以为 absent:删除用户组, present:创建用户组,默认为 present
system:是否为系统组,值为 yes 或 no,gid=mysql创建表3000,也就证明不是系统组了,system值默认也为 no

#在目标主机上查看我们创建的组
ansible -i hosts bigops -m shell -a "cat /etc/group | grep algroup" -b
192.168.31.225 | Ccentos查看系统版本HANGED | rc=0 >>
algroup:x:centos7网络配置3000:
linux必学的60个命令
#删除用户组
ansible -i hosts bigops -m grouplinux常用命令 -a "gid=3000 name=algroup stat星门老鹰ecentos7安装教程=absent system=no小米路由器登录入口" -b

ansible -i hosts bigops -m shell -a "cat /etc/group | tail -1" -b #再去查看的时候发现用户组已被删除
192.168喜马拉雅山高度多少米.31.225 | CHANGED | rc=0 >>

###### user 模块
user模块用户在目标主机上创建/删除用户
```html/xml
#创建用户组
ansible -i hosts bigops -m group -a "name=ansible gid=1038 state=present system=no" -b 
​
#创建用户
ansible -i hosts bigops -m user -a "name=ansible password=123456 create_home=yes home=/home/ansible group=ansible groups=root shell=/bin/bash uid=1024 state=present move_home=yes generate_ssh_key=yes" -b
​
#参数解释
name=ansible:指定用户名称
password=123456:指定用户密码
create_home=yes:是否创建家目录
home=/home/ansible:指定家目录位置
group=ansible:指定基本组(此基本组必须存在)
groups=root:指定附加组
shell=/bin/bash:指定用户登录shell环境
uid=1024:指定用户uid
state=present:指定用户状态, present为创建,absent为删除
move_home=yes:假如此用户已经存在,yes为覆盖家目录,no为创建为此用户创建另外一个家目录,两个家目录通过uid区分
generate_ssh_key=yes:创建用户的同时是否为此用户创建ssh密钥文件
​
#查看创建的用户
ansible -i hosts bigops -m shell -a "id ansible"
192.168.31.225 | CHANGED | rc=0 >>
uid=1024(ansible) gid=1038(ansible) groups=1038(ansible),0(root)
​
#删除用户
ansible -i hosts bigops -m user -a "name=ansible state=absent remove=yes" -b
​
#参数解释
state=absent:删除用户
remove=yes:删除此用户关联的家目录
copy 模块

copy模块用户将文件拷贝到目标主机上
```html/xml
#方法一
ansible -i hosts bigops -m copy -a "src=/home/amysql数据库命令大全nsxmlible/hosts dest=/tmp/file_text modlinuxe=777"

#参数解释:
src=指定本地源文件
dest=指定要复制到目标路径凶猛领主,我这里把linux命令原文件hosts修改为了file_text
mode=指定复制后的文件权限为777

#方法二
将/home/凶猛领主ansible/下的所有文件复制到目标主机的/tmp/目录linux系统
ansible -i hosts bilinux操作系统基础知识gops -m copy -a "src=/home/a系统运维工资一般多少nsible/ dest=/tmp/"

#方法三
将/home目录复制到目mysql怎么读标主机的/tmp目录下,注意/home后面没有 "/",如果后面加了 "/&qucentos7重置root密码ot;则把home下厦门理工学院的所有文件复制过去,而不是 home 目录
ansi喜马拉雅听书免费版官方下载ble -i hosts bigops -m copy -a "src=/home dest=/tmp/"

#方法四
使用content来生成我们要复制到目标的文件内容
ansible -i hosts bigops -m copycentos安装 -a "content='abcops opsblogs\nMy abcmysql索引ops\n' dest=/tmp/ops.txt"
查看文件内容
cat ops.txt
abcops opsblogs
My abcops

#方法五
复制到目的地址的文件直接修改权限及属性信息
ansible -i hosts bigops -m copy -a "content='abcops opsblogs\nMy abcops\n' dest=/tmp/ops.txt owner=micvs group=micvs mode=750"
owner=micvs:将ops.txt的属主修改为micvs
gorup=micvs:将ops.txt的属组修改为micvs
mode=750:将ops.txt的权限修喜马拉雅听书免费版官方下载改为750

###### fetch 模块
fetch模块主要作为为将远程主机文件复制到本地
```html/xml
ansible -i hosts bule_cmdb -m fetch -a "src=/root/anaconda-ks.cfg dest=/tmp/" -b
#参数解释
src=/root/anaconda-ks.cfg:为远程主机文件
dest=/tmp/:本地目录
#在本地/tmp目录中是以IP地址区分各个主机的文件
command 模块

commancentos安装d模块主要是可以直接执行命令,command本身执行不使用shell来解析命令,所有输出的命令将作为原始命令输出
```html/xml
#方法一
ansible -i hosts bigops -m command -a "chdir=/usr/local/src pwd"

#参数解释
chdir:切换到指定目录
pwd:为我linux系统安装们常规命令列出当前所在目录

#方法二
[root@Jumpserver ansible]# ansible -i hosts bigops -m command -a &q系统运维工程师uot;echo '123456' | passwd --stdicentos安装n apper" -b
192.16centos查看系统版本8.31.225 | CHANGED | rc=0 &glinux常用命令t;>
123456 | passwd --stdin apper
我们使用echo来把apper用户的密码改为123456,但是c系统运维工作内容ommand模块却把我们的命令给echo输出到了界面,是因为command命令默认不识别shell,所以使用command命令无法完成具备shell类型的命令

###### shell 模块
shell模块比command模块功能强大,能完成command模块完成不了的任务,shell模块能够识别shell类型的命令
```html/xml
#chdir切换到/var/log/nginx目录,然后使用pwd命令显示当前目录
ansible -i hosts bigops -m shell -a "chdir=/var/log/nginx pwd"
#执行大于两个命令时,可使用分号 ; 来进行作为间隔符,直接使用cd命令也可以切换到相应的目录中,无需使用chdir
ansible -i hosts bigops -m shell -a "cd /var/log/nginx;pwd;ls;tail -2 /etc/passwd" -b
#使用shell模块,可直接创建用户,也就是无需使用 user 模块,shell模块可识别 echo 在线修改密码
ansible -i hosts bigops -m shell -a "useradd apper;echo '123456' | passwd --stdin apper" -b
192.168.31.225 | CHANGED | rc=0 >>
Changing password for user apper.
passwd: all authentication tokens updated successfully.
file 模块

file模块主要为设定文件属性
```html/xml
#方法一
在目标主机/tmp/目录下创建ansible_dmysql安装ir空目录linux必学的60个命令
ansible -i hostcentos查看系统版本s bigops -m file -a "path=/tmp/ansible_dir state=directory"

#方法二
在目标主机centos7关闭防火墙/tmp/ansible_dir/目录下创建ansible_txt空linux是什么操作系统文件
ansimysql安装ble -i hos许慕离ts bigops -m file -a "path=/tmp/ansibl许慕离e_dir/ansible_txt state=file"
但是使用file来创建空文件则报错,我们可以使用ccentos7安装教程opy中的contelinux命令nt来写入一个空文件linux,具体如下
ansible -i hostslinux操作系统基础知识 bigops -m copmysql增删改查语句y -a "mysql创建表;content='' dest=/tmp/anscentos7网络配置ible_dir/ansible_txt"

#方法三
使用file模块来为目标主机创建符号链接
ansible -i hosts bigops -m file -a "src=/tmp/ansible_dir/ansible_txt path=/ansible_txt state=link" -b
源文件为/tmp/ansible_dir/ansiblelinux常用命令_txt,链接至/ansible_txt

###### cron 模块
cron模块主要是为目标主机来创建任务计划
```html/xml
#创建任务计划
ansible -i hosts bigops -m cron -a "minute=01 hour=00 day=* month=* weekday=* job='/sbin/ntpdate ntp2.aliyun.com;hwclock -w' name='Clock synchronization' user=micvs state=present" 
#参数详解:
minute==分
hour==时
day==日
month==月
weekday==周
job=任务
name==任务计划名称
user==指定用户添加crontab
state=值为present,则添加任务计划,值为absent则删除任务计划
#查看定义的任务计划
[micvs@abcops_cmdb01 ~]$ crontab -l
#Ansible: Clock synchronization
01 00 * * * /sbin/ntpdate ntp2.aliyun.com;hwclock -w
#删除任务计划
删除任务计划必须要指定任务计划名称,方可删除,否则删除失败
ansible -i hosts bigops -m cron -a "name='Clock synchronization' state=absent user=micvs"
删除用户为micvs的名称为'Clock synchronization'的任务计划
yum 模块

yum模块主要是在目标主机上进行yum软件安装/卸载等操作
```html/xxmlml
acentos7关闭防火墙nsible -i hosts bigops -m yum -a 'name=htop state=latest disable_gpg_check=yes'centos7 -b
ansible -i hosts bi喜马拉雅山高度多少米gops -m yum -a 'centos7配置ip地址name=httpd smysql索引tate=latest disable_gpg_check=yes' -b

#参数解释
name:安装软件的名称
conf_file:指定远程主机yum源位置
state:安装状态,值如下
latest 如果安装的软件存在则进行更新,如果不存在则安装最新版
pr系统运维工作内容esemysqlnt 如果安装的软件存在则不进行安装,如果不存在则安装
install 正常安装
absent 删除mysql软件包
removed 卸载mysql密码忘记了怎么办软件包
disable_gpg_check:关闭gpg_chlinuxeck
disablerepo:禁centos7关闭防火墙用某个yum源
enablerepo:启用某个yum仓库

###### service 模块
service模块主要用于管理linux主机上的服务
```html/xml
#启动目标主机上的httpd服务,是通过service启动的,在CentOS7上不可用
ansible -i hosts bigops -m yum -a "name=httpd state=startd enable=yes" -b
#参数解释:
name:指定服务名称
state:对当前服务执行启动,停止、重启、重新加载等操作(started,stopped,restarted,reloaded)
enable:是否开机启动 yes|no