内容纲要
1、简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
DDL: 数据库定义语言。---create/alter/drop
DML：数据库操作语言。---insert/delete/update
DCL：数据库控制语言。---grant/revoke
DQL：数据库查询语言。---select
mysql书写语句时的的顺序：
select
from
where
group by
having
order by
limit
#顺序不对会报错，比如分组和排序次序：order by 必须在group by 和having之后
执行顺序：
from
where
group by
having
select
order by
2、自行设计sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个
1、select classid,count(*)数量,gender from students group by classid,gender; #多个字段分组统计信息
2、select count(*)数量,gender from students group by gender with rollup; #分组后聚合函数统计再汇总信息
3、select tid,count(*) from teachers group by tid having tid is not null order by tid; #having对聚合函数分组的数据进行筛选
4、select tid,count(*) from teachers group by tid having tid is not null order by tid limit; #对筛选数据只显示前三行
5、select name,age,gender from students order by age asc limit 4; #在学生表中查找姓名、年龄、性别，只显示前四行以年龄降序排列3、xtrabackup备份和还原数据库练习
xtrabackup全量备份：
1、官网下载xtrabackup工具：地址：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
2、上传到master中，安装：yum -y install percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm #这样会将依赖包一起安装上。
3、开始备份数据库，这里因为不是yum安装的数据库所有备份时需要指定socket地址
xtrabackup -uroot -p123456 -S/data/mysql/mysql.socket --backup --target-dir=/dbackup #备份命令
4、复制备份的文件到slave中；
scp -r /backup/ 10.0.0.138:/ #连同文件夹一起复制，
注意：这里已经提前在slave上安装好了数据库，安装好了 xtrabackup，数据库是关闭状态，且/data/mysql/文件夹是空的。
5、提交完成的事务，回滚未完成的事务。
xtrabackup --prepare --target-dir=/backup/
6、复制到数据库目录
xtrabackup --copy-back --tar-dir=/backup/
7、还原属性
chown -R mysql:mysql /data/mysql
8、启动服务
service mysqld start
备份和还原完成。
如果要检测数据是否一致，可提前在备份前创建一条数据，还原后查看。
xtrabackup增量备份：
1、第一次修改数据：insert teachers(name,age,gender)values('b',45,'F');
第一次增量备份：xtrabackup -uroot -p123456 -S/data/mysql/mysql.sock --backup --target-dir=/data/inc1 --incremental-basedir=/backup
执行结果最后两行：xtrabackup: Transaction log of lsn (18541469) to (18541479) was copied.
220928 10:42:16 completed OK!
2、第二次修改数据：insert teachers(name,age,gender)values('c',88,'F');
第二次增量备份：xtrabackup -uroot -p123456 -S/data/mysql/mysql.sock --backup --target-dir=/data/inc2 --incremental-basedir=/data/inc1
执行结果最后两行：xtrabackup: Transaction log of lsn (18543389) to (18543409) was copied.
220928 10:46:43 completed OK!
3、第三次修改数据：insert teachers(name,age,gender)values('d',99,'M');
第三次增量备份：xtrabackup -uroot -p123456 -S/data/mysql/mysql.sock --backup --target-dir=/data/inc3 --incremental-basedir=/data/inc2
执行结果最后两行：xtrabackup: Transaction log of lsn (18543845) to (18543855) was copied.
220928 10:51:19 completed OK!
4、全量备份已经在上一个步骤中实现，这里直接使用全量备份的数据。
1）scp -r /data/inc*/ 10.0.0.138:/data #复制增量备份数据
2）清空mysql目录，关闭服务。
3）xtrabackup --prepare --apply-log-only --target-dir=/backup #app-log-only阻止回滚未完成的事务
4）xtrabackup --prepare --apply-log-only --target-dir=/backup --incremental-dir=/data/inc1 #合并第一次增量备份到全量备份,这里注意指定还原第一次增量备份的选项和增量备份选项不一致，不然会导致合并失败，但是不报错。--incremental-dir
5）xtrabackup --prepare --apply-log-only --target-dir=/backup --incremental-dir=/data/inc2 #合并第二次增量备份到全量备份
6）xtrabackup --prepare --target-dir=/backup --incremental-dir=/data/inc3 #合并第三次增量备份到全量备份，最后一次增量备份还原不需要加apply-log-only。
7）xtrabackup --copy-back --target-dir=/backup #复制到数据库目录，数据库目录必须为空，服务不能启动
copy -r /backup/* /data/mysql #两条命令都可实现复制数据，只是上一条命令看着更专业。
8）chown -R mysql:mysql /data/mysql #还原属性
9）service mysqld start #启动服务4、实现mysql主从复制，主主复制和半同步复制
一、mysql主从复制：
环境：两台rocky8.5，主节点：10.0.0.136，从节点：10.0.0.138
安装mysql使用的脚本，在网站另一篇文章mysql脚本安装内。
安装完mysql8.0后发现无法进入数据库，检查后发现libtinfo.so.5在mysql的依赖库中显示为not found
解决办法：
ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
再次使用mysql -uroot -p 后进入数据库。
重点：
mysql主从复制尽量是版本一致，如果不一致：那从节点版本必须必主节点高。不然无法实现主从复制。
1）主从复制主节点需要做四步：设置server ID；启用二进制日志；创建复制账号并授权；备份数据库；
2）从节点需要配置：安装数据库;配置my.cnf文件，设置slave id，设置read-only=yes；修改主节点备份文件中的主节点服务器信息；
临时关闭二进制日志，因为还原时会产生二进制日志，但是这些二进制日志没用,set sql_log_bin=off; 还原数据库；开启二进制日志,set sql_log_bin=on;
执行change master to 指向master ip 同步用户密码、端口、二进制日志名称及日志开始位置数字
查看slave状态；开启主从复制；start slave；查看主节点状态show processlist;##验证sql线程和IO线程
##########从节点重启或者关机再开机，会自动同步主数据库数据
10.0.0.136（主节点）：
1）配置my.cnf
[mysqld]
port=3306
server-id=136 #全局唯一
log-bin=/data/mysql/mysql-bin #启用二进制日志
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
2）查看二进制文件和位置开始复制
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 815 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3)创建和授权拥有复制权限的账号（从节点也需要做这步且过程一致）,mysql8.0要分成两步
create user repluser@'10.0.0.%' identified by '密码';
grant replication slave on *.* to repluser@'10.0.0.%';
flush privileges;
10.0.0.138（从节点）：
1）配置my.cnf
[mysqld]
server-id=138
log-bin=/data/mysql/bin-log
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
read-only=yes
[client]
socket=/data/mysql/mysql.sock
2)创建和授权拥有复制权限的账号，和主节点步骤一致。这步是为了防止后期主节点挂了从节点可提升为主节点而设置的。
3）这里因为是干净的数据库，就不关闭binlog日志了，如果是运行了一段时间的日志，那就要先关闭binlog日志，不然开启复制后会产生大量无用的二进制日志
直接在数据库里执行：
CHANGE MASTER TO
MASTER_HOST='10.0.0.136', #主节点地址
MASTER_USER='repluser', #主节点复制账号
MASTER_PASSWORD='密码', #密码
MASTER_PORT=3306, #端口
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=815；#这里的名称和815是根据上面主节点使用show master status;查看的。
4）启动并查看状态： start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.136
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 815
Relay_Log_File: rocky-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 815
Relay_Log_Space: 533
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 136
Master_UUID: d8d3bb3e-3b13-11ed-916b-000c29766fc2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
二、mysql主主复制：
环境和上面主从复制一样，恢复到刚装完数据库的快照即可。
主主复制需要注意的点：
两个主机的server_id要唯一
都启用binary log和relay log
创建拥有复制权限的账号
定义自动增长的id字段的数值范围各为奇偶
彼此互相设置为主节点，并启动复制线程
1）第一个节点：10.0.0.136
vim /etc/my.cnf
[mysqld]
port=3306
server-id=136
log-bin=/data/mysql/mysql-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
auto_increment_offset=1 #开始增长点
auto_increment_increment=2 #增长幅度
[client]
socket=/data/mysql/mysql.sock
重启mysql：service mysqld restart
查看bin_log文件和数字，用于在第二个主节点上使用
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 203 | No |
| mysql-bin.000002 | 815 | No |
| mysql-bin.000003 | 179 | No |
| mysql-bin.000004 | 156 | No |
+------------------+-----------+-----------+
4 rows in set (0.01 sec)
第二个节点：
vim /etc/my.cnf
[mysqld]
server-id=138
log-bin=/data/mysql/bin-log
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
#read-only=yes
auto_increment_offset=2 #开始增长点
auto_increment_increment=2 #增长幅度
[client]
socket=/data/mysql/mysql.sock
重启mysql服务：service mysqld restart
查看bin_log文件和位置数字：用于第一个主节点上使用
mysql> show master logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000001 | 179 | No |
| bin-log.000002 | 852 | No |
| bin-log.000003 | 156 | No |
| bin-log.000004 | 179 | No |
| bin-log.000005 | 179 | No |
| bin-log.000006 | 671 | No |
| bin-log.000007 | 156 | No |
+----------------+-----------+-----------+
7 rows in set (0.00 sec)
到这里，需要的数据都拿到了。可以开始配置了。
10.0.0.136：
添加master节点：
CHANGE MASTER TO
MASTER_HOST='10.0.0.138',
MASTER_USER='repluser',
MASTER_PASSWORD='密码',
MASTER_PORT=3306,
MASTER_LOG_FILE='bin-log.000007', MASTER_LOG_POS=156；
start slave;
10.0.0.138:
添加master节点：
CHANGE MASTER TO
MASTER_HOST='10.0.0.136',
MASTER_USER='repluser',
MASTER_PASSWORD='密码',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=156；
start slave;
配置完成。查看复制状态命令都一致：show slave status;
主要查看两个IO和SQL是否为yes和复制的延迟时间
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
三、半同步复制：
环境：上面实验的主从环境，做好主从后再设置半同步复制。master：10.0.0.136，slave：10.0.0.138
master上配置：
在/etc/my.cnf中添加两行
rpl_semi_sync_master_enabled=ON #添加此行,需要先安装semisync_master.so插件,再重启
rpl_semi_sync_master_timeout=3000 #设置3S内无法同步,也将返回成功信息给客户端
登录数据库：
install plugin rpm_semi_sync_master soname 'semisync_master.so';#安装插件
set global rpm_semi_sync_master_enabled=ON; #临时修改变量
set global rpm_semi_sync_master_timeout=3000; #临时设置超时时间
master配置结果如下：
show global variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
slave配置如下：注意：如果实现了主从复制，先关闭主从，配置完半同步复制再开启
在/etc/my.cnf中添加：
rpl_semi_sync_slave_enabled=ON; #修改完后需重启数据库生效，不重启就临时设置数据库变量
进入数据库：
mysql -uroot -p
stop slave; #关闭主从复制
install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装同步插件
set global rpl_semo_sync_slave_enabled=ON; #临时设置同步变量为开启
show global variables like '%semi%'; #查看设置后的值
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
start slave; #开启主从复制
show slave status\G; #查看同步状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.136
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: rocky-relay-bin.000006
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 748
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 136
Master_UUID: d8d3bb3e-3b13-11ed-916b-000c29766fc2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
配置已经完成，可在master上查看是否有客户端通过同步插件连接到master
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
从结果可看到插件状态为ON，有一个客户端已经连接。
测试：
master上创建了两个数据库：
mysql> create database db1;
Query OK, 1 row affected (0.38 sec)
mysql> create database db2;
Query OK, 1 row affected (0.13 sec)
slave上查看结果：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.10 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
半同步复制配置成功。5、用mycat实现mysql的读写分离
mycat读写分离：
环境：mycat： 10.0.0.133
master：10.0.0.136
slave： 10.0.0.138
上面半同步复制环境。关闭防火墙、关闭selinux、时间同步
mycat：安装mycat，将下载好的mycat上传至服务器。链接：链接：https://pan.baidu.com/s/10MEmKSm1mzWmkKs-p3BoLw 提取码：tu3y
mkdir /apps #创建文件夹
mv Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz /apps #移动上传的文件
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz #解压
echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh #配置环境变量
source /etc/profile.d/mycat.sh #生效
vim /apps/mycat/conf/server.xml
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
将这两行添加到 <property name="handleDistributedTransactions">0</property> 这一行上即可，其他不动。
vim /apps/mycat/conf/schema.xml #这里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" /> #数据库名称为hellodb
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" #balance为负载均衡模式，0不开启，1为将请求分配到writeHost对应的readHost和standby的writeHost上，一般使用1
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.136:3306" user="admin" #写入数据库使用的IP、端口、用户和密码
password="123456">
<readHost host="host2" url="10.0.0.138:3306" user="admin" #读取数据库使用的IP、端口、用户和密码
password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
master：主从复制已经实现，这里直接在主库上创建账号和授权即可,主库执行的操作会自动同步到从库。
create user admin@'10.0.0.%' identified by '123456';
grant all on *.* to admin@'10.0.0.%';
环境配置完毕。
开启mycat：
mycat start #启动mycat
[09:40:43 root@rocky ~]#tail -f /apps/mycat/logs/wrapper.log #查看启动日志，这里可以看到最下面一行mycat已经成功启动。
INFO | jvm 1 | 2022/09/28 09:20:34 |
INFO | jvm 1 | 2022/09/28 09:20:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2022/09/28 09:20:54 | TERM trapped. Shutting down.
STATUS | wrapper | 2022/09/28 09:20:55 | <-- Wrapper Stopped
STATUS | wrapper | 2022/09/28 09:38:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/09/28 09:38:56 | Launching a JVM...
INFO | jvm 1 | 2022/09/28 09:38:56 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/09/28 09:38:56 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/09/28 09:38:56 |
INFO | jvm 1 | 2022/09/28 09:39:00 | MyCAT Server startup successfully. see logs in logs/mycat.log
注意：没有配好mycat的情况下不要启动，不然连接错误连接过多会导致连接不上数据库，可以在master上flush hosts;重置连接，也可以设置错误连接次数为1000或者10000改大点参数。默认是100。如果检查了几遍一直没问题可以在mycat上测试连接主库和从库；安装一个mysql-client测试是否能正常连接master和slave。
mysql> show global variables like '%max_connect_error%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
测试读写分离：需要现在master和slave中开启通用日志，set global general_log=on;
连接mycat服务器的数据库：mysql -uroot -p123456 -h10.0.0.133 #账号密码在server.xml文件中，端口在server中已经修改为3306，默认是8066。
在mycat上执行命令：
select @@server_id; #这是一条查询命令，查到的ID为从库的server_id
+-------------+
| @@server_id |
+-------------+
| 138 |
+-------------+
update teachers set age=@@server_id where tid=5; #这里修改tid为5的age修改成@@server_id，根据上面的结果应该是138，实际上并不是。
结果：
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | a | 136 | M |
+-----+---------------+-----+--------+
5 rows in set (0.14 sec)
从这里已经看到读写分离了，读的时候读取的是从库的server_id=138，但是写的时候发现写入数据库的server_id变成了主库的136。
读写分离完成！！！6、实现openvpn部署，并且测试通过。
环境：
openvpn-server：eth0:10.0.0.133 eth1:192.168.234.130
lan:192.168.234.132 GW:192.168.234.130
client:10.0.0.1
安装：
openvpn-server:
yum -y install openvpn easy-rsa #安装openvpn和easy-rsa
rpm -ql openvpn #查看文件
rpm -ql easy-rsa #查看文件
mkdir -p /apps/openvpn/easy-rsa-{server,client} #创建serverheclient的生成文件目录
cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /apps/openvpn 复制模板配置文件到/apps/openvpn下
cp -r /usr/share/easy-rsa/3 /apps/openvpn/easy-rsa-server/ #复制证书生成文件到指定目录
cp /usr/share/doc/easy-rsa/vars.example /apps/openvpn/easy-rsa-server/vars #复制颁发证书的相关配置文件
vim /apps/openvpn/easy-rsa-server/vars #修改ca和openvpn的证书有效期
set_var EASYRSA_CA_EXPIRE 36500 #CA默认是3650天,10年，这里改到了100年
set_var EASYRSA_CERT_EXPIRE 3650 #服务器证书默认是825天,这里改到了10年
cd /apps/openvpn/easy-rsa-server/ #进入目录
./easyrsa init-pki #初始化数据，将会在目录下生成pki文件夹及相关文件
./easyrsa build-ca nopass #生成自签名的ca证书，回车接受默认名称即可
ll pki/ca.crt pki/private/ca.key #生成一个ca.crt证书文件和ca.key私钥文件
-rw-------. 1 openvpn openvpn 1204 Oct 7 16:33 pki/ca.crt
-rw-------. 1 openvpn openvpn 1679 Oct 7 16:32 pki/private/ca.key
./easyrsa gen-req server nopass #创建服务器证书文件申请，server是文件前缀，也是接受默认回车即可
ll pki/reqs/server.req pki/private/server.key #生成一个server.req的请求文件和一个server.key的私钥文件
-rw-------. 1 openvpn openvpn 1704 Oct 7 16:37 pki/private/server.key
-rw-------. 1 openvpn openvpn 887 Oct 7 16:37 pki/reqs/server.req
./easyrsa sign server server #生成服务器证书
Request subject, to be signed as a server certificate for 3650 days: #这里就是设置的服务器证书有效期了10年
Type the word 'yes' to continue, or any other input to abort.
Confirm request details: #输入yes回车即可
ll pki/issued/server.crt #生成的服务器证书文件
-rw-------. 1 openvpn openvpn 4608 Oct 7 16:42 pki/issued/server.crt
ll pki/certs_by_serial/99C506B4A64B32CFF1C11FC0FAC72B1A.pem #生成的服务器证书文件
-rw-------. 1 openvpn openvpn 4608 Oct 7 16:42 pki/certs_by_serial/99C506B4A64B32CFF1C11FC0FAC72B1A.pem
diff pki/certs_by_serial/99C506B4A64B32CFF1C11FC0FAC72B1A.pem pki/issued/server.crt #查看文件内容是否一
./easy-rsa gen-dh #创建diffie-hellman文件
ll pki/dh.pem 生成的文件
-rw-------. 1 openvpn openvpn 424 Oct 7 16:48 pki/dh.pem
到这里服务器的证书已经配置完成。
配置client证书，这个证书和相关配置文件就是给用户连接openvpn-server使用的。
cp -r /usr/share/easy-rsa/3 /apps/openvpn/easy-rsa-client/ #复制证书生成文件到指定目录
cp /usr/share/doc/easy-rsa/vars.example /apps/openvpn/easy-rsa-client/vars #复制颁发证书的相关配置文件
./easyrsa init-pki #和openvpn-server一样初始化生成pki文件
./easyrsa gen-req zhang nopass #生成用户zhang的证书申请文件，不使用密码，接收默认值，回车即可
ll pki/reqs/zhang.req pki/private/zhang.key #生成的密钥文件和证书申请文件
-rw-------. 1 openvpn openvpn 1708 Oct 7 19:20 pki/private/zhang.key
-rw-------. 1 openvpn openvpn 887 Oct 7 19:21 pki/reqs/zhang.req
cd /apps/openvpn/easy-rsa-server
./easyrsa import-req /apps/openvpn/easy-rsa-client/pki/reqs/zhang.req zhang #复制客户端的证书申请文件到server的ca目录下
ll pki/reqs/
total 8
-rw-------. 1 openvpn openvpn 887 Oct 7 16:37 server.req
-rw-------. 1 openvpn openvpn 887 Oct 7 19:24 zhang.req
vim vars
set_var EASYRSA_CERT_EXPIRE 180 #修改客户端证书有效期，根据具体情况这里设置的是180天
./easyrsa sign client zhang #默认，输入yes回车即可
ll pki/issued/zhang.crt #生成的zhang.crt文件
-rw-------. 1 openvpn openvpn 4491 Oct 7 19:27 pki/issued/zhang.crt
mkdir -p /etc/openvpn/{certs,client/zhang} #创建存放服务器证书和客户端证书的文件夹
复制服务器端文件到指定目录
cp /apps/openvpn/easy-rsa-server/pki/ca.crt /etc/openvpn/certs
cp /apps/openvpn/easy-rsa-server/pki/issued/server.crt /etc/openvpn/certs
cp /apps/openvpn/easy-rsa-server/pki/private/server.key /etc/openvpn/certs
cp /apps/openvpn/easy-rsa-server/pki/dh.pem /etc/openvpn/certs
复制客户端证书文件到指定目录
find /apps/openvpn/ -name \("zhang.key" -o -name "zhang.crt" -o -name ca.crt\) -exec cp {} /etc/openvpn/client/zhang \; #多复制一个ca.crt删除即可
准备openvpn配置文件：
vim /etc/openvpn/server.conf
port 1194 #端口
proto tcp #协议
dev tun #IP路由隧道
ca /etc/openvpn/certs/ca.crt #证书路径
cert /etc/openvpn/certs/server.crt
key /etc/openvpn/certs/server.key
dh /etc/openvpn/certs/dh.pem
server 10.8.0.0 255.255.255.0 #分配给客户端的ip地址
push "route 192.168.234.0 255.255.255.0" #内网ip段
keepalive 10 120 #活动检测和超时时间
cipher AES-256-CBC
compress lz4-v2
push "compress lz4-v2"
max-clients 2048 #客户端连接数
user openvpn #用户
group openvpn #组
status /var/log/openvpn/openvpn-status.log #日志路径
log-append /var/log/openvpn/openvpn.log #日志路径
verb 3 #日志级别
mute 20
duplicate-cn
创建日志路径并授权：
mkdir -p /var/log/openvpn/
chown -R openvpn. /var/log/openvpn
准备openvpn.service文件，centos8默认没有，从centos7上拷贝即可
vim /lib/systemc/system/openvpn@.service
[Unit]
Description=OpenVPN Robust And Highly Flexible Tunneling Application On %I
After=network.target
[Service]
Type=notify
PrivateTmp=true
ExecStart=/usr/sbin/openvpn --cd /etc/openvpn/ --config %i.conf
[Install]
WantedBy=multi-user.target
重新载入systemctl并启动
systemctl daemon-reload
systemctl enable --now openvpn@server
systemctl status openvpn@server.service #查看状态，已经启动
● openvpn@server.service - OpenVPN Robust And Highly Flexible Tunneling Application On server
Loaded: loaded (/usr/lib/systemd/system/openvpn@.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2022-10-08 09:37:40 CST; 1h 46min ago
Main PID: 1030 (openvpn)
Status: "Initialization Sequence Completed"
Tasks: 1 (limit: 23470)
Memory: 2.5M
CGroup: /system.slice/system-openvpn.slice/openvpn@server.service
└─1030 /usr/sbin/openvpn --cd /etc/openvpn/ --config server.conf
ss -ntl #查看端口，是否有1194
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 32 0.0.0.0:1194 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
准备客户端配置文件：
vim /etc/openvpn/client/zhang/client.ovpn
client
dev tun
proto tcp
remote 10.0.0.133 1194 #服务器地址和端口，生产中建议写域名，便于维护
resolv-retry infinite
nobind
#persist-key
#persist-tun
ca ca.crt
cert zhang.crt
key zhang.key
remote-cert-tls server
#tls-auth ta.key 1
cipher AES-256-CBC
verb 3 #日志级别，和server端一致
compress lz4-v2
进入到client目录下，cd /etc/openvpn/client/zhang
tar cf zhang.tar * #打包文件传到windows上。
lan主机：提前安装好http服务并启动,然后再修改网卡为仅主机模式
yum -y install httpd
systemctl enable --now httpd
echo "this is test-20221001" > /var/www/html/index.html #设置页面
route add -net 10.8.0.0/24 gw 192.168.234.130 #修改lan网关为openvpn-server，网段为10.8.0.0，因为server.conf中分配给客户端的网段为10.8.0.0，访问的时候使用的是openvpn的192.168.234.130的地址，但返回客户端就要使用10.8.0.0了。
windows机器：下载openvpn安装包，在官网上有
安装Open-V-P-N-2.5.5-I602-amd64.msi，装完后会提示没有配置文件。
把刚才从openvpn-server打包下载的zhang.tar解压放到windows下的config中，注意：直接放到config中，不用再创建文件夹
然后开始启动openvpn，连接成功右下角会变成绿色。并且会给windows分配一个10.8.0.6的一个ip地址。
到这里openvpn已经配置完成。
测试访问lan：192.168.234.132 #提前在windows中禁用仅主机的192.168.234的网卡。不然会不通过vpn直接访问到。
这里发现无法访问网页。
因为openvpn-server是一台服务器，不是路由器，访问192.168.234.132时，openvpn-server发现不是访问它的就直接丢弃了。需要执行开启转发功能：
openvpn-server：
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf #开启转发功能
sysctl -p #马上生效
再次访问：192.168.234.132，访问成功。
openvpn已经搭建完成，但是没有密码是不安全的，而且手工分配证书很浪费时间，也没有讲吊销证书。下次写好脚本一键颁发证书再补充。
1