第六周
1、简述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
mysql各个关键字查询时候的先后顺序：
from
on/using
join
where
group by
having
select
distinct
union
order by
limit
2、自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
1、查询stuid小于3
mysql> select * from students where Stuid <3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+-------------+-----+--------+---------+-----------+
2、分页查询
mysql> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from students limit 1,3;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
3、分组查询
mysql> select classid, count(*)数量 from students group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------+
8 rows in set (0.00 sec)
4、排序查询
mysql> select * from students where classid is not null order by gender desc,age asc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
5、子查询
mysql> select * from students s where exists(select * from teachers t where s.teacherid=t.tid);
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
6、联合查询
mysql> select tid as id,name,age,gender from teachers union select stuid,name,age,gender from students;
+----+---------------+-----+--------+
| id | name | age | gender |
+----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
+----+---------------+-----+--------+
29 rows in set (0.00 sec)
7、查看学生表students
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
8、查看学生表teachers；
mysql> select * from 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 |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
9、查询课程表的数据classes
mysql> select * from classes;
+---------+----------------+----------+
| ClassID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
+---------+----------------+----------+
10、查询的数据courses
mysql> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
注意：以上查询用的表和表的数据
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
3、xtrabackup备份和还原数据库练习
完成备份与还原：
1、进入官网https://www.percona.com/downloads，下载包percona-xtrabackup-80-8.0.34-29.1.el8.x86_64.rpm
2、安装xtrabackup，
[root@Rocky8 ~]# yum install percona-xtrabackup-80-8.0.34-29.1.el8.x86_64.rpm -y
3、创建备份目录，备份，备份到远程主机
[root@Rocky8 ~]# mkdir /backup
[root@Rocky8 ~]# xtrabackup -uroot --backup --target-dir=/backup/base
[root@Rocky8 ~]# scp -r /backup/ 10.0.0.4:/
4、登录远程主机10.0.0.4，进行安装xtrabackup
[root@Rocky8 ~]# yum install -y percona-xtrabackup-80-8.0.34-29.1.el8.x86_64.rpm
5、确保数据一致，提交完成事务，回滚未完成事务
[root@Rocky8 ~]# xtrabackup --prepare --target-dir=/backup/base/
6、复制到数据库目录，还有属性
[root@Rocky8 ~]# ls /var/lib/mysql
auto.cnf '#ib_16384_0.dblwr' mysql.sock server-cert.pem
binlog.000005 '#ib_16384_1.dblwr' mysql.sock.lock server-key.pem
binlog.000006 ib_buffer_pool mysqlx.sock sys
binlog.index ibdata1 mysqlx.sock.lock undo_001
ca-key.pem ibtmp1 performance_schema undo_002
ca.pem '#innodb_redo' private_key.pem xtrabackup_info
client-cert.pem '#innodb_temp' public_key.pem
client-key.pem mysql Rocky8-relay-bin.000001
hellodb mysql.ibd Rocky8-relay-bin.index
[root@Rocky8 ~]# xtrabackup --copy-back --target-dir=/backup/base/
[root@Rocky8 ~]# chown -R mysql.mysql /var/lib/mysql
7、启动服务，查看状态和数据
[root@Rocky8 ~]# systemctl start mysqld.service
root@Rocky8 ~]# systemctl start mysqld.service
[root@Rocky8 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.32 Source distribution
Copyright (c) 2000, 2023, 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> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
xtrabackup还可以实现增量备份与还原
步骤
第一步：进行完全备份，同上
第二步：进行增量备份
第一次:
[root@Rocky8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
第二次:
[root@Rocky8 ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
以此类推第n次
第三步：到远程主机上还原
完成备份还原--合并第一场增量备份到完全备份--合并第二次增量备份到完全备份--直到最后一次（注意：最后一次不需要加--apply-log-only选项）
第一次：
xtrabackup --prepare --apply-log-only --target-dir=/backup/base/ --incremental-dir=/backup/inc1
第二次：
xtrabackup --prepare --target-dir=/backup/base/ --incremental-dir=/backup/inc2
第四步：还原数据库目录
第五步：启动服务，验证。
4、实现mysql主从复制，主主复制和半同步复制
一、主从复制
1主节点操作：
vi /etc/my.cnf
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=5
log-bin=/data/logbin/mysql-bin
[mysql]
user=root
2、创建账号授权
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
3、备份:
mysqldump -uroot -A -F --single-transaction --source-data > /backup/full-`date +%F`.sql
把备份的包备份到slave节点
4、slave节点操作
编辑/etc/my.cnf文件
[root@Rocky8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=135
read-only
5、进入数据库，关闭二进制
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
还原数据库
mysql> source /backup/full-2023-10-11.sql
6、开启从节点
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 157
Relay_Log_File: Rocky8-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: binlog.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: 157
Relay_Log_Space: 744
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: 11
Master_UUID: 1cc4199c-67e9-11ee-83fd-000c29d83f78
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、查看验证
主插入
mysql> insert teachers(name,age,gender) values('liang',22,'F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from 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 | liang | 22 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
从查看
mysql> select * from 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 | liang | 22 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
二、主主复制
查看主1的二进制日志
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 180 | No |
| binlog.000002 | 726 | No |
| binlog.000003 | 180 | No |
| binlog.000004 | 180 | No |
| binlog.000005 | 1269658 | No |
+---------------+-----------+-----------+
5 rows in set (0.00 sec)
在主1上创建授权账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
在主2上运行
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.14',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=1269658;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
查看主1和主2的状态和数据
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.14
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 1269658
Relay_Log_File: Rocky8-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000005
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: 1269658
Relay_Log_Space: 534
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: 14
Master_UUID: 6bd6b564-67fa-11ee-90ab-000c2927caa5
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)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.4
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 1211204
Relay_Log_File: Rocky8-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.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: 1211204
Relay_Log_Space: 534
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: 1
Master_UUID: 5d161e95-67fa-11ee-93c3-000c29d83f78
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)
三、半同步复制
准备三台机器：10.0.0.4（主） 10.0.0.14/24（备）
主的操作配置
[root@Rocky8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=4
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=3000
创建账号和授权
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (3.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
数据库备份复制给从库
[root@Rocky8 ~]# mysqldump -uroot -A -F --single-transaction --source-data > /backup/full-`date +%F`.sql
[root@Rocky8 ~]# scp /backup/full-2023-10-12.sql 10.0.0.14:/backup
The authenticity of host '10.0.0.14 (10.0.0.14)' can't be established.
ECDSA key fingerprint is SHA256:Sbg16UVjBn6XYNe/crrlTuc24V72p+2sftITd17vbfE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.14' (ECDSA) to the list of known hosts.
root@10.0.0.14's password:
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
full-2023-10-12.sql 100% 1254KB 124.6MB/s 00:00
[root@Rocky8 ~]# scp /backup/full-2023-10-12.sql 10.0.0.24:/backup
The authenticity of host '10.0.0.24 (10.0.0.24)' can't be established.
ECDSA key fingerprint is SHA256:Sbg16UVjBn6XYNe/crrlTuc24V72p+2sftITd17vbfE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.24' (ECDSA) to the list of known hosts.
root@10.0.0.24's password:
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
tput: No value for $TERM and no -T specified
full-2023-10-12.sql 100% 1254KB 100.0MB/s 00:00
重启数据库服务
从设备配置
从设备1：
[root@Rocky8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=14
rpl_semi_sync_slave_enable
从设备2：
[root@Rocky8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
server-id=24
rpl_semi_sync_slave_enabled
安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
编辑主数据库备份文件，增加以下配置
CHANGE MASTER TO
MASTER_HOST='10.0.0.4',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
重启数据库服务
查看实现效果
主设备：
mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| 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 | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| 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 |
+--------------------------------------------+-------+
从设备：
mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
5、用mycat实现mysql的读写分离
此处主从按照上面配置，此处不再展示，主从配置完成后，方能继续以下步骤
使用四台主机10.0.0.135/136配置主从10.0.0.137配置mycat10.0.0.130设置为客户端
下载安装JDK yum -y install java
yum -y install java
[root@Rocky8 mycat]# java -version
openjdk version "1.8.0_382"
OpenJDK Runtime Environment (build 1.8.0_382-b05)
OpenJDK 64-Bit Server VM (build 25.382-b05, mixed mode)
[root@Rocky8 mycat]#
下载解压安装mycat
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@Rocky8 ~]# tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
mycat/bin/mycat
mycat/bin/wrapper-linux-ppc-64
mycat/bin/wrapper-linux-x86-32
mycat/bin/wrapper-linux-x86-64
mycat/lib/annotations-13.0.jar
mycat/lib/asm-4.0.jar
mycat/lib/commons-collections-3.2.1.jar
mycat/lib/commons-lang-2.6.jar
mycat/lib/curator-client-2.11.0.jar
mycat/lib/curator-framework-2.11.0.jar
mycat/lib/curator-recipes-2.11.0.jar
mycat/lib/disruptor-3.3.4.jar
mycat/lib/dom4j-1.6.1.jar
mycat/lib/druid-1.0.26.jar
mycat/lib/ehcache-core-2.6.11.jar
mycat/lib/fastjson-1.2.58.jar
mycat/lib/guava-19.0.jar
mycat/lib/hamcrest-core-1.3.jar
mycat/lib/hamcrest-library-1.3.jar
mycat/lib/jline-0.9.94.jar
mycat/lib/joda-time-2.9.3.jar
mycat/lib/jsr305-2.0.3.jar
mycat/lib/kotlin-stdlib-1.3.50.jar
mycat/lib/kotlin-stdlib-common-1.3.50.jar
mycat/lib/kryo-2.10.jar
mycat/lib/leveldb-0.7.jar
mycat/lib/leveldb-api-0.7.jar
mycat/lib/libwrapper-linux-ppc-64.so
mycat/lib/libwrapper-linux-x86-32.so
mycat/lib/libwrapper-linux-x86-64.so
mycat/lib/log4j-1.2-api-2.5.jar
mycat/lib/log4j-1.2.17.jar
mycat/lib/log4j-api-2.5.jar
mycat/lib/log4j-core-2.5.jar
mycat/lib/log4j-slf4j-impl-2.5.jar
mycat/lib/mapdb-1.0.7.jar
mycat/lib/minlog-1.2.jar
mycat/lib/mongo-java-driver-3.11.0.jar
mycat/lib/Mycat-server-1.6.7.4-release.jar
mycat/lib/mysql-binlog-connector-java-0.16.1.jar
mycat/lib/mysql-connector-java-5.1.35.jar
mycat/lib/netty-3.7.0.Final.jar
mycat/lib/netty-buffer-4.1.9.Final.jar
mycat/lib/netty-common-4.1.9.Final.jar
mycat/lib/objenesis-1.2.jar
mycat/lib/okhttp-4.2.2.jar
mycat/lib/okio-2.2.2.jar
mycat/lib/reflectasm-1.03.jar
mycat/lib/sequoiadb-driver-1.12.jar
mycat/lib/slf4j-api-1.6.1.jar
mycat/lib/univocity-parsers-2.2.1.jar
mycat/lib/velocity-1.7.jar
mycat/lib/wrapper.jar
mycat/lib/zookeeper-3.4.6.jar
mycat/conf/wrapper.conf
mycat/conf/
mycat/conf/zkconf/
mycat/conf/zkdownload/
mycat/conf/auto-sharding-long.txt
mycat/conf/auto-sharding-rang-mod.txt
mycat/conf/autopartition-long.txt
mycat/conf/cacheservice.properties
mycat/conf/dbseq - utf8mb4.sql
mycat/conf/dbseq.sql
mycat/conf/ehcache.xml
mycat/conf/index_to_charset.properties
mycat/conf/migrateTables.properties
mycat/conf/myid.properties
mycat/conf/partition-hash-int.txt
mycat/conf/partition-range-mod.txt
mycat/conf/rule.xml
mycat/conf/schema.xml
mycat/conf/sequence_conf.properties
mycat/conf/sequence_db_conf.properties
mycat/conf/sequence_distributed_conf.properties
mycat/conf/sequence_http_conf.properties
mycat/conf/sequence_time_conf.properties
mycat/conf/server.xml
mycat/conf/sharding-by-enum.txt
mycat/conf/zkconf/auto-sharding-long.txt
mycat/conf/zkconf/auto-sharding-rang-mod.txt
mycat/conf/zkconf/autopartition-long.txt
mycat/conf/zkconf/cacheservice.properties
mycat/conf/zkconf/ehcache.xml
mycat/conf/zkconf/index_to_charset.properties
mycat/conf/zkconf/partition-hash-int.txt
mycat/conf/zkconf/partition-range-mod.txt
mycat/conf/zkconf/rule.xml
mycat/conf/zkconf/schema.xml
mycat/conf/zkconf/sequence_conf.properties
mycat/conf/zkconf/sequence_db_conf.properties
mycat/conf/zkconf/sequence_distributed_conf-mycat_fz_01.properties
mycat/conf/zkconf/sequence_distributed_conf.properties
mycat/conf/zkconf/sequence_time_conf-mycat_fz_01.properties
mycat/conf/zkconf/sequence_time_conf.properties
mycat/conf/zkconf/server-mycat_fz_01.xml
mycat/conf/zkconf/server.xml
mycat/conf/zkconf/sharding-by-enum.txt
mycat/conf/zkdownload/auto-sharding-long.txt
mycat/version.txt
mycat/conf/log4j2.xml
mycat/bin/dataMigrate.sh
mycat/bin/init_zk_data.sh
mycat/bin/rehash.sh
mycat/bin/startup_nowrap.sh
mycat/logs/
mycat/catlet/
启动mycat，查看是否报错
[root@Rocky8 mycat]# mycat start
Starting Mycat-server...
[root@Rocky8 mycat]# ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=945,fd=4))
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=1690,fd=4))
LISTEN 0 50 *:36391 *:* users:(("java",pid=1690,fd=68))
LISTEN 0 100 *:9066 *:* users:(("java",pid=1690,fd=87))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=945,fd=6))
LISTEN 0 50 *:39355 *:* users:(("java",pid=1690,fd=66))
LISTEN 0 50 *:1984 *:* users:(("java",pid=1690,fd=67))
LISTEN 0 100 *:8066 *:* users:(("java",pid=1690,fd=91))
[root@Rocky8 mycat]# tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2023/10/16 14:42:35 | --> Wrapper Started as Daemon
STATUS | wrapper | 2023/10/16 14:42:35 | Launching a JVM...
INFO | jvm 1 | 2023/10/16 14:42:36 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2023/10/16 14:42:36 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2023/10/16 14:42:36 |
INFO | jvm 1 | 2023/10/16 14:42:37 | MyCAT Server startup successfully. see logs in logs/mycat.log
客户端测试链接
[root@Rocky8 ~]# mysql -uroot -p123456 -h 10.0.0.34 -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 2
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2023, 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> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql>
创建账号授权
mysql> create user admin@'10.0.0.%' identified by '654321';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to admin@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
修改schema.xml文件和server.xml文件
客户端上查看（端口号已修改所以不用加），现在能看到数据库表的信息
验证读写分离
6、实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。
未购买阿里云产品，后期会输出到博客
7、mysql如何实现崩溃后恢复？
MySQL数据库在崩溃后可以通过以下几种方式来实现恢复：
启用二进制日志（Binary Log）：MySQL提供了二进制日志功能，它记录了所有数据库操作语句的日志。在崩溃后，可以使用二进制日志进行恢复。需要确保在MySQL配置文件中启用了二进制日志，并定期备份二进制日志文件。
使用事务日志（InnoDB存储引擎）：如果使用的是InnoDB存储引擎，它提供了事务日志（也称为重做日志）功能。事务日志记录了正在进行中的事务操作，包括对表的修改。在崩溃后，MySQL可以使用事务日志进行恢复。
备份与恢复：定期备份MySQL数据库是非常重要的。使用数据库备份工具，如mysqldump命令行工具或第三方xtrabackup备份工具，可以将数据库备份到另一个存储位置。在崩溃后，可以使用备份文件来还原数据库。
使用故障转移与复制功能：MySQL支持主从复制和主主复制功能。通过设置主从复制或主主复制，可以将数据复制到多个服务器上。当主服务器崩溃时，可以使用其中一个从服务器继续提供服务
8、myisam和innodb各自在什么场景使用？"
MyISAM和InnoDB是MySQL数据库中两种常见的存储引擎。
MyISAM适用于以下场景：
1、对于读密集型应用：MyISAM在处理大量的SELECT操作时性能较好，因为它使用了表级锁定而不是行级锁定，这意味着在读取数据时不会出现锁冲突。
2、需要全文搜索功能：MyISAM支持全文索引，使得在文本数据上进行高效的全文搜索成为可能。
3、插入和查询的比例较低：由于MyISAM会在执行写操作时锁定整个表，所以在插入和查询的比例较低的应用中，效果更好。
InnoDB适用于以下场景：
1、对于写密集型应用：InnoDB在处理大量的INSERT和UPDATE操作时性能较好，因为它使用了行级别的锁定，可以避免锁冲突，提高并发性能。
2、需要事务支持：InnoDB是MySQL的默认事务存储引擎，支持ACID（原子性、一致性、隔离性和持久性）事务，可以确保数据的完整性和一致性。
3、强调数据的安全性：InnoDB支持外键约束和崩溃恢复机制，可以保证数据的安全性和稳定性。
浙公网安备 33010602011771号