-
简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
· DDL: Data Defination Language数据定义语言
CREATE,DROP,ALTER
· DML: Data Manipulation Language数据操纵语言
INSERT,DELETE, UPDATE软件开发：CRUD
· DQL:Data Query Language数据查询语言
SELECT
· DCL: Data Control Language数据控制语言
GRANT,REVOKE
-
自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
-
xtrabackup备份和还原数据库练习
-
实验准备
准备两台主机：10.0.0.5,10.0.0.6；其中10.0.0.5作为原主机，10.0.0.6作为目标主机
实验如下：
1.在两台主机安装percona-xtrabackup-80-8.0.32-26.1.el8.x86_64.rpm yum -y install percona-xtrabackup-80-8.0.32-26.1.el8.x86_64.rpm 2.在原主机创建/backup目录做完全备份 mkdir /backup/ xtrabackup -uroot --backup --no-server-version-check --target-dir=/backup/base 3.将/backup/目录下内容scp到目标主机 scp -r /backup/ 10.0.0.6:/ 4.在目标主机上还原 1)预准备：确保数据一致 xtrabackup --prepare --target-dir=/backup/base 2)复制数据库目录 xtrabackup --copy-back --target-dir=/backup/base 3)还原目录属性 chown -R mysql:mysql /var/lib/mysql 4)启动服务 systemctl restart mysqld.service 注意：我当前的mysql版本为：Server version: 8.0.32，使用percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm 不支持；--no-server-version-check 这个选项表示不检测服务版本。-
验证
-
-
-
实现mysql主从复制，主主复制和半同步复制
#主节点： 1.安装mysql-server yum -y install mysql-server 2.修改配置文件 vim /etc/my.cnf [mysqld] server-id=5 log-bin=/data/logbin/mysql-bin 3.创建日志存放路径 mkdir -p /data/logbin/ 4.修改目录属性 chown mysql.mysql /data/logbin 5.启动mysql服务 systemctl start mysqld.service 6.进入数据库创建并授权用户 create user repluser@'10.0.0.%' identified by '123456'; grant replication slave on *.* to repluser@'10.0.0.%'; 7.完全备份 mysqldump -A -F --single-transaction --master-data > /bacup/full-`date +%F`.sql 7.复制完全数据到从节点 scp -r /backup/full-2023-08-14.sql 10.0.0.6:/backup #从节点： 1.安装mysql-server yum -y install mysql-server 2.修改配置文件 vim /etc/my.cnf [mysqld] server-id=6 read-only 3.创建备份目录 mkdir /backup 4.修改主节点备份数据文件 vim /backup/full-2023-08-14.sql CHANGE MASTER TO MASTER_HOST = '10.0.0.5', MASTER_USER = 'repluser', MASTER_PASSWORD = '123456', MASTER_PORT = 3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157; 5.开启从节点 start slave； 6.查看从节点状态:(Slave_IO_Running: Yes Slave_SQL_Running: Yes) show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 10.0.0.5 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 157 Relay_Log_File: hchjq-relay-bin.000003 Relay_Log_Pos: 373 Relay_Master_Log_File: mysql-bin.000004 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: 157 Relay_Log_Space: 752 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: 5 Master_UUID: ded75f06-3a83-11ee-bb55-000c293c0970 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.00 sec) 7.验证： 主节点创建hellodb数据库 create database hellodb; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) 从节点查看： mysql> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
#在第一个master节点上实现 [root@master1 ~]# vim /etc/my.cnf [mysqld] server-id=5 log-bin auto_increment_offset=1 auto_increment_increment=2 [root@master1 ~]# systemctl restart mysqld.service [root@master1 ~]# mysql mysql> show master logs; +--------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +--------------------+-----------+-----------+ | master1-bin.000001 | 157 | No | +--------------------+-----------+-----------+ 2 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.%'; #在第二个master节点上实现 [root@master2 ~]# vim /etc/my.cnf [mysqld] server-id=6 log-bin auto_increment_offset=2 auto_increment_increment=2 [root@master2 ~]# systemctl restart mysqld.service [root@master2 ~]# mysql mysql> CHANGE MASTER TO -> MASTER_HOST = '10.0.0.5', -> MASTER_USER = 'repluser', -> MASTER_PASSWORD = '123456', -> MASTER_PORT = 3306, -> MASTER_LOG_FILE='master1-bin.000001', -> MASTER_LOG_POS=157; mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show master logs; +--------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +--------------------+-----------+-----------+ | master2-bin.000001 | 157 | No | +--------------------+-----------+-----------+ #在第一个master节点上实现 [root@master1 ~]# mysql mysql> CHANGE MASTER TO -> MASTER_HOST = '10.0.0.6', -> MASTER_USER = 'repluser', -> MASTER_PASSWORD = '123456', -> MASTER_PORT = 3306, -> MASTER_LOG_FILE='master2-bin.000001', -> MASTER_LOG_POS=157; mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) #验证： 1.在master1创建数据库建立表以及插入数据，在master2查看数据 mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> use db1 mysql> create table t1(id int auto_increment primary key,name varchar(20))); Query OK, 0 rows affected (0.01 sec) mysql> insert t1 (name) values('user1'); Query OK, 1 row affected (0.05 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | user1 | +----+-------+ 1 row in set (0.00 sec) 2.在master2创建数据库建立表以及插入数据，在master1查看数据 mysql> create database db2; Query OK, 1 row affected (0.00 sec) mysql> use db2; Database changed mysql> create table t2(id int auto_increment primary key,nname varchar(20)); Query OK, 0 rows affected (0.00 sec) mysql> insert t2 (name) values('user2'); Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | db2 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use db2; 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> select *from t2; +----+-------+ | id | name | +----+-------+ | 2 | user2 | +----+-------+ 1 row in set (0.00 sec) #综上所述：主主同步验证成功。
#主节点： 1.安装mysql-server yum -y install mysql-server 2.修改配置文件 vim /etc/my.cnf [mysqld] server-id=3 rpl_semi_sync_master_enabled=on rpl_semi_sync_master_timeout=3000 3. 安装插件（先启动MySQL安装插件后修改配置文件） install plugin rpl_semi_sync_master soname 'semisync_master.so'; 4.启动mysqld systemctl restart mysqld 5.创建有复制权限的用户账号 create user repluser@'192.168.60.%' identified by '123456'; grant replication slave on *.* to repluser@'192.168.60.%'; 6.备份传输数据到从节点 mysqldump -A --single-transaction --master-data > /data/all.sql scp data/all.sql 192.168.60.4:/data/all.sql scp data/all.sql 192.168.60.5:/data/all.sql 7.在mysql数据库中创建表并插入数据； mysql> create table teachers (id int(10), name varchar(20), age int(10)); mysql> insert teachers (id,name,age)values(1,a,30);#从节点slave1 1.安装mysql-server yum -y install mysql-server 2.修改配置文件 vim /etc/my.cnf [mysqld] server-id=3 rpl_semi_sync_master_enabled=on 3. 安装插件（先启动MySQL安装插件后修改配置文件） install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; 4.启动mysqld systemctl restart mysqld 5.修改 /data/all.sql 文件 CHANGE MASTER TO MASTER_HOST='192.168.60.3', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=690; 6.重启mysqld systemctl restart mysqld 7.查看mysql链接状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.60.3 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 1218 Relay_Log_File: rokey8-relay-bin.000002 Relay_Log_Pos: 851 Relay_Master_Log_File: binlog.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: 1218 Relay_Log_Space: 1062 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: 3 Master_UUID: c5c6f3e1-7137-11ee-bbce-000c29f1387a 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.00 sec) ERROR: No query specified 8.查看数据同步 mysql> use mysql Database changed mysql> select * from teachers -> ; +------+------+------+ | id | name | age | +------+------+------+ | 1 | a | 30 | +------+------+------+ 1 row in set (0.00 sec) #SLAVE2 同上 -
用mycat实现mysql的读写分离
前提：master节点和slave节点做了主从复制 mycat节点： 1.安装 mysql-server和Java [root@mycat ~]# yum -y install mysql-server [root@mycat ~]# yum -y install Java 2.上传并解压Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz至/apps [root@mycat ~]# tar -xf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /apps/ 3.修改环境变量，启动mycat [root@mycat ~]# vim /etc/profile.d/mycat.sh PATH=/apps/mycat/bin:$PATH 4.修改启动端口号： [root@mycat ~]# vim /apps/mycat/conf/server.xml <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务 日志--> <property name="serverPort">3306</property> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> 5.修改配置文件，读写分离 [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="false" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost1" database="db4" /> <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="192.168.60.3:3306" user="admin" password="123456"> <readHost host="host2" url="192.168.60.4:3306" user="admin" password="123456"/> </writeHost> </dataHost> </mycat:schema> 6.启动mycat [root@mycat ~]# mycat restart #在后端服务器创建用户并对mycat授权 1.grant all on *.* to admin@'192.168.60.%'; #在client节点连接mycat并测试 [root@client ~]# mysql -uroot -p123456 -h 192.168.60.6 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_db4 | +---------------+ | students | +---------------+ 1 row in set (0.00 sec) #在主从节点上启用通用日志，查看读写分离 [root@master ~]# vim /etc/my.cnf [mysqld] server-id=3 gtid_mode=ON enforce_gtid_consistency general_log=on [root@slave1 ~]# vim /etc/my.cnf [mysqld] server-id=4 gtid_mode=ON enforce_gtid_consistency general_log=on #停止从节点 [root@slave1 ~]# systemctl stop mysqld.service #在client节点登录mycat [root@client ~]# mysql -uroot -p123456 -h 192.168.60.6 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ 1 row in set (0.01 sec) #停止主节点，mycat不会自动调度写请求至从节点； mysql> insert students values(2,'ls',10); ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect...注意：修改/apps/mycat/conf/schema.xml报错:Caused by: io.mycat.config.util.ConfigException: schema TESTDB didn‘t config tables,so you 原因：原来的配置文件内容为： <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" > 改的时候变成了： <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"> 导致没法找到dataNode. 最后将dataNode="dn1"加上就可以了。 -
实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。
#1、配置环境 [14:37:17 root@openvpn-server ~]#ip a 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 inet 10.0.0.3/24 brd 10.0.0.255 scope global noprefixroute eth0 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 inet 192.168.10.1/24 brd 192.168.10.255 scope global noprefixroute eth1 [14:38:00 root@web1 ~]#ip a 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 inet 192.168.10.100/24 brd 192.168.10.255 scope global noprefixroute eth0 [14:38:00 root@wbe2 ~]#ip a 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000 inet 192.168.10.200/24 brd 192.168.10.255 scope global noprefixroute eth0 #2、安装openvpn和证书工具 [root@openvpn-server ~]#yum -y install openvpn easy-rsa #3、生成相关的配置文件 ##openvpn配置文件 [root@openvpn-server ~]#cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn/ ##证书的相关文件 [root@openvpn-server ~]#cp -r /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-server ##颁发证书相关变量的配置文件 [root@openvpn-server ~]#cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/easy-rsa-server/3/vars ##增加CA和openvpn服务器证书的有效期 [root@openvpn-server ~]#vim /etc/openvpn/easy-rsa-server/3/vars set_var EASYRSA_CA_EXPIRE 36500 set_var EASYRSA_CERT_EXPIRE 3650 [root@openvpn-server ~]#tree /etc/openvpn/ /etc/openvpn/ ├── client ├── easy-rsa-server │ ├── 3 -> 3.0.8 │ ├── 3.0 -> 3.0.8 │ └── 3.0.8 │ ├── easyrsa │ ├── openssl-easyrsa.cnf │ ├── vars │ └── x509-types │ ├── ca │ ├── client │ ├── code-signing │ ├── COMMON │ ├── email │ ├── kdc │ ├── server │ └── serverClient ├── server └── server.conf 7 directories, 12 files #4、初始化PKI和CA颁发机构环境 ##查看easyrsa脚本帮助用法 [root@openvpn-server ~]#cd /etc/openvpn/easy-rsa-server/3/ [root@openvpn-server 3]#file ./easyrsa ./easyrsa: POSIX shell script, ASCII text executable [root@openvpn-server 3]#./easyrsa ##初始化PKI生成PKI相关目录文件 [root@openvpn-server 3]#./easyrsa init-pki [root@openvpn-server 3]#tree . ├── easyrsa ├── openssl-easyrsa.cnf ├── pki │ ├── openssl-easyrsa.cnf │ ├── private │ ├── reqs │ └── safessl-easyrsa.cnf ├── vars └── x509-types ├── ca ├── client ├── code-signing ├── COMMON ├── email ├── kdc ├── server └── serverClient 4 directories, 13 files ##创建CA机构环境 [root@openvpn-server 3]#./easyrsa build-ca nopass Your new CA certificate file for publishing is at: /etc/openvpn/easy-rsa-server/3/pki/ca.crt #生成自签名的证书文件 [root@openvpn-server 3]#tree pki pki ├── ca.crt #生成的自签名的证书文件 ├── certs_by_serial ├── index.txt ├── index.txt.attr ├── issued ├── openssl-easyrsa.cnf ├── private │ └── ca.key #生成的私钥文件 ├── renewed │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── reqs ├── revoked │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── safessl-easyrsa.cnf └── serial 12 directories, 7 files #5、创建服务器端证书申请 ##创建服务器证书申请文件，其中server是文件前缀 [root@openvpn-server 3]#./easyrsa gen-req server nopass Keypair and certificate request completed. Your files are: req: /etc/openvpn/easy-rsa-server/3/pki/reqs/server.req #生成请求文件 key: /etc/openvpn/easy-rsa-server/3/pki/private/server.key #生成私钥文件 [root@openvpn-server 3]#tree pki pki ├── ca.crt ├── certs_by_serial ├── index.txt ├── index.txt.attr ├── issued ├── openssl-easyrsa.cnf ├── private │ ├── ca.key │ └── server.key ├── renewed │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── reqs │ └── server.req ├── revoked │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── safessl-easyrsa.cnf └── serial 12 directories, 9 files #6、颁发服务器端证书 ##第一个server表示证书的类型，第二个server表示请求文件名的前缀 [root@openvpn-server 3]#./easyrsa sign server server Request subject, to be signed as a server certificate for 3650 days: #vars文件指定的有效期 subject= commonName = server Certificate created at: /etc/openvpn/easy-rsa-server/3/pki/issued/server.crt #生成服务器证书文件 [root@openvpn-server 3]#tree pki pki ├── ca.crt ├── certs_by_serial │ └── 41ED3602BB7FDF3215AA321299C1C2BF.pem #生成的服务器证书文件 ├── index.txt ├── index.txt.attr ├── index.txt.attr.old ├── index.txt.old ├── issued │ └── server.crt #生成的服务器证书文件 ├── openssl-easyrsa.cnf ├── private │ ├── ca.key │ └── server.key ├── renewed │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── reqs │ └── server.req ├── revoked │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── safessl-easyrsa.cnf ├── serial └── serial.old 12 directories, 14 files #7、创建Diffie-Hellman密钥 [root@openvpn-server 3]#./easyrsa gen-dh Note: using Easy-RSA configuration from: /etc/openvpn/easy-rsa-server/3.0.8/vars Using SSL: openssl OpenSSL 1.1.1k FIPS 25 Mar 2021 Generating DH parameters, 2048 bit long safe prime, generator 2 This is going to take a long time ...................................++*++*++*++* DH parameters of size 2048 created at /etc/openvpn/easy-rsa-server/3/pki/dh.pem #生成密钥的位置 #8、准备客户端证书环境 ##上面服务器端证书配置完成，下面是客户端证书 ##复制内容，从头再来 [root@openvpn-server 3]#cp -a /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-client [root@openvpn-server 3]#cd /etc/openvpn/easy-rsa-client/3/ [root@openvpn-server 3]#tree . ├── easyrsa ├── openssl-easyrsa.cnf ├── vars └── x509-types ├── ca ├── client ├── code-signing ├── COMMON ├── email ├── kdc ├── server └── serverClient 1 directory, 11 files ##生成证书所需要的目录文件 [root@openvpn-server 3]#./easyrsa init-pki Your newly created PKI dir is: /etc/openvpn/easy-rsa-client/3/pki [root@openvpn-server 3]#tree . ├── easyrsa ├── openssl-easyrsa.cnf ├── pki │ ├── openssl-easyrsa.cnf │ ├── private │ ├── reqs │ └── safessl-easyrsa.cnf ├── vars └── x509-types ├── ca ├── client ├── code-signing ├── COMMON ├── email ├── kdc ├── server └── serverClient 4 directories, 13 files ##生成客户端用户的证书申请 [root@openvpn-server 3]#./easyrsa gen-req mazhuobo nopass Keypair and certificate request completed. Your files are: req: /etc/openvpn/easy-rsa-client/3/pki/reqs/mazhuobo.req #证书申请文件 key: /etc/openvpn/easy-rsa-client/3/pki/private/mazhuobo.key #私钥文件 [root@openvpn-server 3]#tree . ├── easyrsa ├── openssl-easyrsa.cnf ├── pki │ ├── openssl-easyrsa.cnf │ ├── private │ │ └── mazhuobo.key #私钥文件 │ ├── reqs │ │ └── mazhuobo.req #证书申请文件 │ └── safessl-easyrsa.cnf ├── vars └── x509-types ├── ca ├── client ├── code-signing ├── COMMON ├── email ├── kdc ├── server └── serverClient 4 directories, 15 files ##颁发客户端证书 [root@openvpn-server 3]#cd /etc/openvpn/easy-rsa-server/3 ##将客户端证书请求文件复制到CA的工作目录下 [root@openvpn-server 3]#./easyrsa import-req /etc/openvpn/easy-rsa-client/3/pki/reqs/mazhuobo.req mazhuobo Note: using Easy-RSA configuration from: /etc/openvpn/easy-rsa-server/3.0.8/vars Using SSL: openssl OpenSSL 1.1.1k FIPS 25 Mar 2021 The request has been successfully imported with a short name of: mazhuobo You may now use this name to perform signing operations on this request. [root@openvpn-server 3]#tree pki pki ├── ca.crt ├── certs_by_serial │ └── 41ED3602BB7FDF3215AA321299C1C2BF.pem ├── dh.pem ├── index.txt ├── index.txt.attr ├── index.txt.attr.old ├── index.txt.old ├── issued │ └── server.crt ├── openssl-easyrsa.cnf ├── private │ ├── ca.key │ └── server.key ├── renewed │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── reqs │ ├── mazhuobo.req │ └── server.req ├── revoked │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── safessl-easyrsa.cnf ├── serial └── serial.old 12 directories, 16 files ##修改给客户端颁发的证书有效期 [root@openvpn-server 3]#vim vars set_var EASYRSA_CERT_EXPIRE 90 ##颁发客户端证书 [root@openvpn-server 3]#./easyrsa sign client mazhuobo Request subject, to be signed as a client certificate for 90 days: #证书有效期 subject= commonName = mazhuobo Certificate created at: /etc/openvpn/easy-rsa-server/3/pki/issued/mazhuobo.crt #证书文件 [root@openvpn-server 3]#tree pki pki ├── ca.crt ├── certs_by_serial │ ├── 1DE23BEC76F9E00DAE268260585D0B89.pem │ └── 41ED3602BB7FDF3215AA321299C1C2BF.pem ├── dh.pem ├── index.txt ├── index.txt.attr ├── index.txt.attr.old ├── index.txt.old ├── issued │ ├── mazhuobo.crt │ └── server.crt ├── openssl-easyrsa.cnf ├── private │ ├── ca.key │ └── server.key ├── renewed │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── reqs │ ├── mazhuobo.req │ └── server.req ├── revoked │ ├── certs_by_serial │ ├── private_by_serial │ └── reqs_by_serial ├── safessl-easyrsa.cnf ├── serial └── serial.old 12 directories, 18 files #9、将CA和服务器证书相关文件复制到服务器相应目录 [root@openvpn-server 3]#mkdir /etc/openvpn/certs [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/ca.crt /etc/openvpn/certs/ [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/issued/server.crt /etc/openvpn/certs/ [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/private/server.key /etc/openvpn/certs/ [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/dh.pem /etc/openvpn/certs/ #10、将客户端证书相关文件复制到服务器相应目录 ##且看仔细是那个目录下的那个文件 [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-client/3.0.8/pki/private/mazhuobo.key /etc/openvpn/client/mazhuobo/ [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3.0.8/pki/issued/mazhuobo.crt /etc/openvpn/client/mazhuobo/ [root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3.0.8/pki/ca.crt /etc/openvpn/client/mazhuobo/ [root@openvpn-server 3]#ll /etc/openvpn/client/mazhuobo/ total 16 -rw------- 1 root root 1204 Aug 20 16:08 ca.crt -rw------- 1 root root 4494 Aug 20 16:08 mazhuobo.crt -rw------- 1 root root 1708 Aug 20 16:04 mazhuobo.key #11、准备OpenVPN服务器配置文件 [root@openvpn-server ~]#vim /etc/openvpn/server.conf [root@openvpn-server ~]#grep '^[a-Z].*' /etc/openvpn/server.conf port 1194 proto tcp dev tun ca /etc/openvpn/certs/ca.crt cert /etc/openvpn/certs/server.crt key /etc/openvpn/certs/server.key # This file should be kept secret dh /etc/openvpn/certs/dh.pem server 10.8.0.0 255.255.255.0 push "route 192.168.10.0 255.255.255.0" 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 ##准备日志相关目录 [root@openvpn-server ~]#getent passwd openvpn openvpn:x:994:991:OpenVPN:/etc/openvpn:/sbin/nologin [root@openvpn-server ~]#mkdir /var/log/openvpn [root@openvpn-server ~]#chown openvpn.openvpn /var/log/openvpn [root@openvpn-server ~]#ll -d /var/log/openvpn drwxr-xr-x 2 openvpn openvpn 6 Aug 20 19:22 /var/log/openvpn #12、启动OpenVPN服务 ##centos8上缺少文件从centos7上拷贝 [19:27:55 root@centos7 ~]#scp /lib/systemd/system/openvpn@.service 10.0.0.28:/lib/systemd/system/ [root@openvpn-server ~]#systemctl daemon-reload [root@openvpn-server ~]#systemctl enable --now openvpn@server #13、生成客户端用户的配置文件 [root@openvpn-server ~]#grep '^[[:alpha:]].*' /usr/share/doc/openvpn/sample/sample-config-files/client.conf > /etc/openvpn/client/mazhuobo/client.ovpn [root@openvpn-server ~]#vim /etc/openvpn/client/mazhuobo/client.ovpn [root@openvpn-server ~]#cat !* cat /etc/openvpn/client/mazhuobo/client.ovpn client dev tun proto udp remote my-server-1 1194 resolv-retry infinite nobind #persist-key #persist-tun ca ca.crt cert client.crt key client.key remote-cert-tls server #tls-auth ta.key 1 cipher AES-256-CBC verb 3 #此值不能随便指定，否则无法通信 compress lz4-v2 #此项在OpenVPN2.4.X版本使用，需要和服务器端保持一致，如不指定，默认使用comp-lz压缩 -
mysql如何实现崩溃后恢复？
首先是使用MySQL官方提供的备份功能来恢复MySQL数据库崩溃后的数据。可以利用MySQL的备份和恢复功能，通过设置定期备份，让MySQL自动在一定时间范围内备份数据，以方便及时恢复数据，以下是实现此功能的代码样例： mysqldump –opt dbname | gzip > backfile.gz 其次，使用binlog来恢复MySQL数据库崩溃后的数据。MySQL 的binlog可以记录每次对数据库进行更改的详细信息，因此可以通过追踪binlog日志中的更改来恢复数据。只需要识别被更改的行，根据binlog日志中的详细信息恢复数据即可。 同时，还可以通过第三方的数据库恢复工具来恢复MySQL数据库崩溃后的数据。现在市面上有大量的第三方数据库恢复工具，它们均可快速准确地恢复MySQL数据库崩溃后的数据，但注意不能滥用，因为这些数据库恢复工具可能会替代原来系统的备份和恢复策略，从而导致恢复出错等问题。 最后一种方法是MySQL的InnoDB附加命令。这个命令是高级恢复方式，能够精确地恢复特定的表或表记录，但是它的实施步骤繁琐，管理员需要对MySQL的文件系统和表结构有所了解，才能够全面正确地恢复数据。 -
myisam和innodb各自在什么场景使用
一、MyIsam
特性
建表会生成frm MYD（特有） MYI（特有）三个文件
并发性及锁级别
表级锁并发性差
表损坏修复：不支持事务
可以使用check table tabklename和repair table tablename 来进行myisam表的检查和修复
还有mysql的工具myisamchk –help 来进行修复
MyISAM 支持数据压缩 myisampack *.MYI
对于已经压缩的表只能进行读操作
使用场景：
1.非事务型应用
2.只读类应用
3.空间类应用
二、innodb(mysql5.5及以后版本默认存储引擎):
show variables like ‘innodb_file_per_table’;查看表空间类型
show variables like ‘innodb_log_buffer_size’; 查看redo 缓冲区大小
show variables like ‘innodb_log_file_in_group’; 查看log file数量
事务型存储引擎，支持ACID
使用表空间进行数据存储
使用 show variables like ‘innodb_file_per_table’;查看表空间类型
on：独立表空间：tablename.ibd
off：系统表空间：ibdataX
修改这个参数使用set global innodb_file_per_table=on(off);来完成
两者之间的比较：
1.系统表空间无法简单的收缩文件大小，会造成较大的空间浪费（即删除无效数据之后不会改变文件的大小）
独立表空间可以通过optimize table命令收缩系统文件，这种方法不会影响数据表的正常使用
2.系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据（频繁写入的表）
建议：对innodb使用独立表空间
表转移的步骤
把原来存在与系统表空间中的表转移到独立表空间中的方法
步骤：
1.使用mysqldump导出所有数据库表数据
2.停止MySQL服务，修改参数，并删除Innodb相关文件
3.重启MySQL服务，重建Innodb系统表空间
4.重新导入数据
Innodb特性
Innodb是一种事务型存储引擎
完全支持事务的ACID特性
即原子性、一致性、隔离性、持久性
使用Redo Log和Undo Log来完成一致性
show variables like ‘innodb_log_buffer_size’; 查看redo 缓冲区大小
show variables like ‘innodb_log_file_in_group’; 查看log file数量
innodb支持行级锁
行级锁可以最大程度的支持并发
行级锁是由存储引擎层实现的
什么是锁
锁对主要作用是管理共享资源的并发访问
用于实现事务的隔离性
锁的类型
共享锁（读锁）
独占锁（写锁）
MySQL锁的粒度
表级锁（开销小，并发性低），通常在服务器层实现
行级锁（开销大，并发性高），只会在存储引擎层面进行实现
阻塞和死锁
innodb状态检查
show engine innodb status
适用场景：
mysql5.7之后已经支持全文索引以及空间函数
适用于大多数OLTP应用（On-Line Transaction Processing联机事务处理过程(OLTP)也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。）
MySQL 常见的两种存储引擎：MyISAM与InnoDB的理解
关于二者的对比与总结:
count运算上的区别：因为MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACIDcompliant))型表。
是否支持外键： MyISAM不支持，而InnoDB支持。
MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下，经常选择MyISAM作为主库的存储引擎。 一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是最好的选择。
