数据库的简述
数据库的简述
DDL
创建表
mysql> CREATE TABLE teacher(
-> id int unsigned auto_increment PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> age tinyint UNSIGNED,
-> gender ENUM('M','F') default 'M'
-> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
表查看
查看表
mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| autoinc1 |
| schools |
| student |
| teacher |
| testdate |
+------------------+
5 rows in set (0.00 sec)
查看表创建命令
mysql> SHOW CREATE TABLE teacher\G;
*************************** 1. row ***************************
Table: teacher
Create Table: CREATE TABLE `teacher` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
查看表的结构
mysql> DESC teacher;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
查看表的状态
mysql> SHOW TABLE STATUS LIKE 'teacher'\G;
*************************** 1. row ***************************
Name: teacher
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2023-08-23 11:58:06
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
查看支持engine类型
mysql> show ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
修改和删除表
增加字段
mysql> ALTER TABLE teacher ADD phone varchar(11) AFTER gender;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from teacher;
+----+----------+------+--------+-------+
| id | name | age | gender | phone |
+----+----------+------+--------+-------+
| 1 | xiaoming | 18 | M | NULL |
+----+----------+------+--------+-------+
1 row in set (0.00 sec)
修改字段名称和类型
mysql> ALTER TABLE teacher CHANGE COLUMN phone mobile char(11);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM teacher;
+----+----------+------+--------+--------+
| id | name | age | gender | mobile |
+----+----------+------+--------+--------+
| 1 | xiaoming | 18 | M | NULL |
+----+----------+------+--------+--------+
1 row in set (0.00 sec)
删除字段
mysql> ALTER TABLE teacher DROP mobile;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from teacher;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 18 | M |
+----+----------+------+--------+
1 row in set (0.00 sec)
删除表
mysql> DROP TABLE teacher;
添加和删除主键
创建没主键表
mysql> CREATE TABLE teachers (id int ,name varchar(10),school_id int);
Query OK, 0 rows affected (0.01 sec)
mysql> DESC teachers;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| school_id | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加主键
mysql> ALTER TABLE teachers ADD primary key(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC teachers;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
| school_id | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除主键
mysql> ALTER TABLE teachers DROP primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC teachers;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
| school_id | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加外键
确认要添加的外键的数据类型要一致
mysql> desc student;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table student modify TeacherID int(10) unsigned;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table student add foreign key(TeacherID) references teacher(id);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
| TeacherID | int(10) unsigned | YES | MUL | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
删除外键
mysql> alter table student drop foreign key TeacherID;
DML
INSERT
全值插入
mysql> insert teacher(name,age,gender) values('kk',18,'M');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teacher;
+----+-----------+------+--------+
| id | name | age | gender |
+----+-----------+------+--------+
| 1 | xiaoming | 18 | M |
| 2 | wang | 34 | M |
| 3 | li | 54 | M |
| 4 | xiaozhang | 24 | M |
| 5 | kk | 18 | M |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
部分插入
mysql> insert teacher(name,age) values('tt',18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from teacher;
+----+-----------+------+--------+
| id | name | age | gender |
+----+-----------+------+--------+
| 1 | xiaoming | 18 | M |
| 2 | wang | 34 | M |
| 3 | li | 54 | M |
| 4 | xiaozhang | 24 | M |
| 5 | kk | 18 | M |
| 6 | tt | 18 | M |
+----+-----------+------+--------+
6 rows in set (0.00 sec)
UPDATE
改为值必须要指定条件（where xxx=y），否则会修改整张表的值！
mysql> update teacher set age=20 where id=6;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teacher;
+----+-----------+------+--------+
| id | name | age | gender |
+----+-----------+------+--------+
| 1 | xiaoming | 18 | M |
| 2 | wang | 34 | M |
| 3 | li | 54 | M |
| 4 | xiaozhang | 24 | M |
| 5 | kk | 18 | M |
| 6 | tt | 20 | M |
+----+-----------+------+--------+
6 rows in set (0.00 sec)
DELETE
一定要指定条件，否则会删除表中所有数据
mysql> delete from teacher where id=6;
Query OK, 1 row affected (0.00 sec)
mysql> select * from teacher;
+----+-----------+------+--------+
| id | name | age | gender |
+----+-----------+------+--------+
| 1 | xiaoming | 18 | M |
| 2 | wang | 34 | M |
| 3 | li | 54 | M |
| 4 | xiaozhang | 24 | M |
| 5 | kk | 18 | M |
+----+-----------+------+--------+
5 rows in set (0.00 sec)
DCL
授权
mysql> create user 'li'@'10.0.0.%' identified by 'Mgmau0!';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'li'@'10.0.0.%' identified by 'Mgmau0!?';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'li'@'10.0.0.%';
+---------------------------------------+
| Grants for li@10.0.0.% |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'li'@'10.0.0.%' |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES on *.* TO 'li'@'10.0.0.%' IDENTIFIED BY 'Mgmau0!?';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW GRANTS FOR 'li'@'10.0.0.%';
+------------------------------------------------+
| Grants for li@10.0.0.% |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'li'@'10.0.0.%' |
+------------------------------------------------+
1 row in set (0.00 sec)
取消授权
mysql> REVOKE DELETE ON *.* FROM 'li'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'li'@'10.0.0.%'\G;
*************************** 1. row ***************************
Grants for li@10.0.0.%: GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'li'@'10.0.0.%'
1 row in set (0.00 sec)
ERROR:
No query specified
DQL
单表查询
mysql> select * from mysql.user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *ACBB1481E5A8A1C26FB33BC1527372B2C31D818B
password_expired: N
password_last_changed: 2023-08-21 19:46:59
password_lifetime: NULL
account_locked: N
*************************** 2. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2023-08-21 16:03:20
password_lifetime: NULL
account_locked: Y
..........................................................
..........................................................
..........................................................
选取字段查询
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| li | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
字段别名
mysql> select user 用户,host 主机 from mysql.user;
+---------------+-----------+
| 用户 | 主机 |
+---------------+-----------+
| li | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
聚合函数
count()，sum()，max()，min()，avg()
它不统计null
mysql> select sum(age)/count(*) from students ;
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 27.4000 |
+-------------------+
1 row in set (0.00 sec)
排序
asc升序，desc降序
mysql> select * from students order by age desc limit 5;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
分组统计
mysql> select classid,gender,count(*) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | count(*) |
+---------+--------+----------+
| NULL | M | 2 |
| 1 | F | 2 |
| 1 | M | 2 |
| 2 | M | 3 |
| 3 | F | 3 |
| 3 | M | 1 |
| 4 | M | 4 |
| 5 | M | 1 |
| 6 | F | 3 |
| 6 | M | 1 |
| 7 | F | 2 |
| 7 | M | 1 |
+---------+--------+----------+
12 rows in set (0.00 sec)
多表查询
子查询
在SQL语句再嵌套查询语句。
mysql> select stuid,name,age from students where age > (select avg(age) from students);
+-------+--------------+-----+
| stuid | name | age |
+-------+--------------+-----+
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 6 | Shi Qing | 46 |
| 13 | Tian Boguang | 33 |
| 25 | Sun Dasheng | 100 |
+-------+--------------+-----+
5 rows in set (0.00 sec)
联合查询
Union条件：多表的字段数量相同，字段名和数据类型可以不同。
union all 不去重
mysql> select name,age,gender from teachers union select name,age,gender from students;
+---------------+-----+--------+
| name | age | gender |
+---------------+-----+--------+
| Song Jiang | 45 | M |
| Zhang Sanfeng | 94 | M |
| Miejue Shitai | 77 | F |
| Lin Chaoying | 93 | F |
| Shi Zhongyu | 22 | M |
| Shi Potian | 22 | M |
| Xie Yanke | 53 | M |
| Ding Dian | 32 | M |
| Yu Yutong | 26 | M |
| Shi Qing | 46 | M |
| Xi Ren | 19 | F |
| Lin Daiyu | 17 | F |
| Ren Yingying | 20 | F |
| Yue Lingshan | 19 | F |
| Yuan Chengzhi | 23 | M |
| Wen Qingqing | 19 | F |
| Tian Boguang | 33 | M |
| Lu Wushuang | 17 | F |
| Duan Yu | 19 | M |
| Xu Zhu | 21 | M |
| Lin Chong | 25 | M |
| Hua Rong | 23 | M |
| Xue Baochai | 18 | F |
| Diao Chan | 19 | F |
| Huang Yueying | 22 | F |
| Xiao Qiao | 20 | F |
| Ma Chao | 23 | M |
| Xu Xian | 27 | M |
| Sun Dasheng | 100 | M |
+---------------+-----+--------+
29 rows in set (0.00 sec)
交叉查询
多张表做笛卡尔乘积组合
mysql> select * from teachers cross join students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| 1 | Song Jiang | 45 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Zhang Sanfeng | 94 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Lin Chaoying | 93 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 1 | Song Jiang | 45 | M | 2 | Shi Potian | 22 | M | 1 | 7 |
| 2 | Zhang Sanfeng | 94 | M | 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M | 1 | 7 |
| 4 | Lin Chaoying | 93 | F | 2 | Shi Potian | 22 | M | 1 | 7 |
| 1 | Song Jiang | 45 | M | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 2 | Zhang Sanfeng | 94 | M | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 3 | Miejue Shitai | 77 | F | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Lin Chaoying | 93 | F | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 1 | Song Jiang | 45 | M | 4 | Ding Dian | 32 | M | 4 | 4 |
| 2 | Zhang Sanfeng | 94 | M | 4 | Ding Dian | 32 | M | 4 | 4 |
| 3 | Miejue Shitai | 77 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| 4 | Lin Chaoying | 93 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| 1 | Song Jiang | 45 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 2 | Zhang Sanfeng | 94 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 3 | Miejue Shitai | 77 | F | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Lin Chaoying | 93 | F | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 1 | Song Jiang | 45 | M | 6 | Shi Qing | 46 | M | 5 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Miejue Shitai | 77 | F | 6 | Shi Qing | 46 | M | 5 | NULL |
| 4 | Lin Chaoying | 93 | F | 6 | Shi Qing | 46 | M | 5 | NULL |
| 1 | Song Jiang | 45 | M | 7 | Xi Ren | 19 | F | 3 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 7 | Xi Ren | 19 | F | 3 | NULL |
| 3 | Miejue Shitai | 77 | F | 7 | Xi Ren | 19 | F | 3 | NULL |
| 4 | Lin Chaoying | 93 | F | 7 | Xi Ren | 19 | F | 3 | NULL |
| 1 | Song Jiang | 45 | M | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 3 | Miejue Shitai | 77 | F | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 4 | Lin Chaoying | 93 | F | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 1 | Song Jiang | 45 | M | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 4 | Lin Chaoying | 93 | F | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 1 | Song Jiang | 45 | M | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 3 | Miejue Shitai | 77 | F | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 4 | Lin Chaoying | 93 | F | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 1 | Song Jiang | 45 | M | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 4 | Lin Chaoying | 93 | F | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 1 | Song Jiang | 45 | M | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 3 | Miejue Shitai | 77 | F | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 4 | Lin Chaoying | 93 | F | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 1 | Song Jiang | 45 | M | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 3 | Miejue Shitai | 77 | F | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Lin Chaoying | 93 | F | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 1 | Song Jiang | 45 | M | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 3 | Miejue Shitai | 77 | F | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 4 | Lin Chaoying | 93 | F | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 1 | Song Jiang | 45 | M | 15 | Duan Yu | 19 | M | 4 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 15 | Duan Yu | 19 | M | 4 | NULL |
| 3 | Miejue Shitai | 77 | F | 15 | Duan Yu | 19 | M | 4 | NULL |
| 4 | Lin Chaoying | 93 | F | 15 | Duan Yu | 19 | M | 4 | NULL |
| 1 | Song Jiang | 45 | M | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 3 | Miejue Shitai | 77 | F | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 4 | Lin Chaoying | 93 | F | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Song Jiang | 45 | M | 17 | Lin Chong | 25 | M | 4 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 17 | Lin Chong | 25 | M | 4 | NULL |
| 3 | Miejue Shitai | 77 | F | 17 | Lin Chong | 25 | M | 4 | NULL |
| 4 | Lin Chaoying | 93 | F | 17 | Lin Chong | 25 | M | 4 | NULL |
| 1 | Song Jiang | 45 | M | 18 | Hua Rong | 23 | M | 7 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 18 | Hua Rong | 23 | M | 7 | NULL |
| 3 | Miejue Shitai | 77 | F | 18 | Hua Rong | 23 | M | 7 | NULL |
| 4 | Lin Chaoying | 93 | F | 18 | Hua Rong | 23 | M | 7 | NULL |
| 1 | Song Jiang | 45 | M | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 4 | Lin Chaoying | 93 | F | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 1 | Song Jiang | 45 | M | 20 | Diao Chan | 19 | F | 7 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 20 | Diao Chan | 19 | F | 7 | NULL |
| 3 | Miejue Shitai | 77 | F | 20 | Diao Chan | 19 | F | 7 | NULL |
| 4 | Lin Chaoying | 93 | F | 20 | Diao Chan | 19 | F | 7 | NULL |
| 1 | Song Jiang | 45 | M | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 4 | Lin Chaoying | 93 | F | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 1 | Song Jiang | 45 | M | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 3 | Miejue Shitai | 77 | F | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 4 | Lin Chaoying | 93 | F | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 1 | Song Jiang | 45 | M | 23 | Ma Chao | 23 | M | 4 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 23 | Ma Chao | 23 | M | 4 | NULL |
| 3 | Miejue Shitai | 77 | F | 23 | Ma Chao | 23 | M | 4 | NULL |
| 4 | Lin Chaoying | 93 | F | 23 | Ma Chao | 23 | M | 4 | NULL |
| 1 | Song Jiang | 45 | M | 24 | Xu Xian | 27 | M | NULL | NULL |
| 2 | Zhang Sanfeng | 94 | M | 24 | Xu Xian | 27 | M | NULL | NULL |
| 3 | Miejue Shitai | 77 | F | 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Lin Chaoying | 93 | F | 24 | Xu Xian | 27 | M | NULL | NULL |
| 1 | Song Jiang | 45 | M | 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 2 | Zhang Sanfeng | 94 | M | 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Miejue Shitai | 77 | F | 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 4 | Lin Chaoying | 93 | F | 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
100 rows in set (0.00 sec)
内连接
inner join取多个表的交集
mysql> select * from students inner join teachers on students.stuid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 94 | M |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
外连接
左连接：以左表为根据条件查询右表的数据，如果右表对应的数据不存在使用NULL填充。
mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+-----+-----------+------+---------------+------+
| stuid | name | age | teacherid | tid | name | age |
+-------+---------------+-----+-----------+------+---------------+------+
| 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 93 |
| 2 | Shi Potian | 22 | 7 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | 16 | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL | NULL |
+-------+---------------+-----+-----------+------+---------------+------+
25 rows in set (0.00 sec)
右连接：以右表为根据条件查询左表的数据，如果左表对应的数据不存在使用NULL填充。
mysql> select * from students s right outer join teachers t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
完全外连接
MySQL不支持full outer join
用其他方法
mysql> select * from students left join teachers on students.teacherid=teachers.tid union select * from students right join teachers on students.teacherid=teachers.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.00 sec)
自连接
表自身连自身
mysql> select a.name,b.name from students as a inner join students as b on a.stuid=b.classid;
+-------------+---------------+
| name | name |
+-------------+---------------+
| Shi Potian | Shi Zhongyu |
| Shi Zhongyu | Shi Potian |
| Shi Potian | Xie Yanke |
| Ding Dian | Ding Dian |
| Xie Yanke | Yu Yutong |
| Yu Yutong | Shi Qing |
| Xie Yanke | Xi Ren |
| Xi Ren | Lin Daiyu |
| Shi Qing | Ren Yingying |
| Xie Yanke | Yue Lingshan |
| Shi Qing | Yuan Chengzhi |
| Shi Zhongyu | Wen Qingqing |
| Shi Potian | Tian Boguang |
| Xie Yanke | Lu Wushuang |
| Ding Dian | Duan Yu |
| Shi Zhongyu | Xu Zhu |
| Ding Dian | Lin Chong |
| Xi Ren | Hua Rong |
| Shi Qing | Xue Baochai |
| Xi Ren | Diao Chan |
| Shi Qing | Huang Yueying |
| Shi Zhongyu | Xiao Qiao |
| Ding Dian | Ma Chao |
+-------------+---------------+
23 rows in set (0.00 sec)
MySQL各个关键字查询的先后顺序
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause --> SELECT --> ORDER BY --> LIMIT
SQL查询案列
在students表中查询年龄大于25岁，且为男性同学的名字和年龄，从大到小排序。
mysql> select name,age,gender from students where age > 25 and gender='M' order by age desc;
+--------------+-----+--------+
| name | age | gender |
+--------------+-----+--------+
| Sun Dasheng | 100 | M |
| Xie Yanke | 53 | M |
| Shi Qing | 46 | M |
| Tian Boguang | 33 | M |
| Ding Dian | 32 | M |
| Xu Xian | 27 | M |
| Yu Yutong | 26 | M |
+--------------+-----+--------+
7 rows in set (0.00 sec)
以classid为分组依据，显示每组的平均年龄
mysql> select classid,avg(age) as 平均年龄 from students group by classid ;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| NULL | 63.5000 |
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+--------------+
8 rows in set (0.00 sec)
以classid为分组依据，显示平均年龄>30的分组及平均年龄
mysql> select classid,avg(age) as 平均年龄 from students group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+--------------+
3 rows in set (0.00 sec)
显示以L开头的同学的信息
mysql> select * from students order by name asc limit 5,3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
显示teacherid的值为非空，按照年龄从大到小取前面3个
mysql> select * from students where teacherid is not null order by age desc limit 3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
按年龄排序后，显示年龄最大的前10位同学的信息
mysql> select * from students order by age desc limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
以classid分组，显示每班的同学的人数
mysql> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
8 rows in set (0.00 sec)
以gender分组，显示其年龄之和
mysql> select gender, sum(age) from students group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| F | 190 |
| M | 495 |
+--------+----------+
2 rows in set (0.00 sec)
以classid分组，显示平均年龄大于25的班级
mysql> select classid,avg(age) from students group by classid having avg(age) > 25;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
3 rows in set (0.00 sec)
以Gender分组，显示各组中年龄大于25的学员的年龄之和
mysql> select gender,sum(age) from students where age > 25 group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| M | 317 |
+--------+----------+
1 row in set (0.00 sec)
Xtrabackup
下载网址（🪜）：https://www.percona.com/downloads
或者配置epel源
Xtrabackup是唯一开源的能对innode和xtradb数据库进行热备的工具。
完全备份
数据库采用Mysql 8.0，两台主机数据库采用相同版本
安装Xtrabackup
[root@localhost ~]# ls
anaconda-ks.cfg Documents Music Pictures Templates Videos
Desktop Downloads initial-setup-ks.cfg percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm Public
[root@localhost ~]# yum install percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm
创建备份用的账号
mysql> create user li identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'li'@'%' ;
Query OK, 0 rows affected (0.00 sec)
首先要创建/backup目录，然后做完全备份。
mkdir /backup
[root@localhost backup]# xtrabackup -uli -p123456 --backup --target-dir=/backup/base
备份到目标主机
[root@localhost backup]# scp -r /backup/ 10.0.0.8:/backup
开始还原
[root@rocky mysql]# yum install percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm
[root@rocky mysql]# systemctl stop mysqld
xtrabackup --prepare --target-dir=/back/base
数据库的目录必须清空，一般目录在/var/lib/mysql
[root@rocky mysql]# rm -rf /var/lib/mysql/*
[root@rocky mysql]# xtrabackup --copy-back --target-dir=/backup/base
[root@rocky mysql]# chown -R mysql. /var/lib/mysql
[root@rocky mysql]# systemctl start mysqld
增量备份
在完全备份的基础上
[root@localhost /]# mkdir /backup
[root@localhost /]# xtrabackup -uli -p123456 --backup --target-dir=/backup/base
增量备份
第一次修改数据
[root@localhost /]# xtrabackup -uli -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
第二次修改数据
[root@localhost /]# xtrabackup -uli -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
远程拷贝
[root@localhost /]# scp -r /backup/* 10.0.0.8:/backup/
远程主机准备操作
[root@rocky mysql]# systemctl stop mysqld
[root@rocky mysql]# rm -rf /var/lib/mysql/*
开始还原
[root@rocky mysql]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
[root@rocky mysql]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
#最后一次增量去掉 --apply-log-only
[root@rocky mysql]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
[root@rocky mysql]# xtrabackup --copy-back --target-dir=/backup/base
[root@rocky mysql]# chown -R mysql. /var/lib/mysql
[root@rocky mysql]# systemctl start mysqld
MySQL主从复制
版本采用8.0
创建从节点复制账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.04 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
主节点开启二进制日志
[root@localhost mysql]# 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]
log_bin
[root@localhost mysql]# systemctl restart mysqld
mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 11293 | No |
| binlog.000002 | 157 | No |
| binlog.000003 | 508 | No |
| binlog.000004 | 507 | No |
| binlog.000005 | 705 | No |
| binlog.000006 | 157 | No |
+---------------+-----------+-----------+
6 rows in set (0.00 sec)
从节点配置
[root@rocky mysql]# 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]
log_bin
server_id=8
read-only=ON //只读
[root@rocky mysql]# systemctl restart mysqld
登录mysql
[root@rocky mysql]# mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.4',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=705,
-> MASTER_CONNECT_RETRY=5;
Query OK, 0 rows affected, 10 warnings (0.02 sec)
启动复制线程
mysql> start slave;
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: 5
Master_Log_File: binlog.000006
Read_Master_Log_Pos: 460
Relay_Log_File: rocky-relay-bin.000003
Relay_Log_Pos: 670
Relay_Master_Log_File: binlog.000006
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: 460
Relay_Log_Space: 1046
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: 4
Master_UUID: dac7dc24-4282-11ee-909d-000c29e5e294
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)
ERROR:
No query specified
完成。
MySQL主主复制
开始点：auto_increment_offset
增幅点：auto_increment_increment
第一个主节点（10.0.0.4）配置
[root@localhost mysql]# 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
log_bin
auto_increment_offset=1
auto_increment_increment=2
[root@localhost mysql]# systemctl restart mysqld
登录mysql
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 11293 | No |
| binlog.000002 | 157 | No |
| binlog.000003 | 508 | No |
| binlog.000004 | 507 | No |
| binlog.000005 | 705 | No |
| binlog.000006 | 483 | No |
| binlog.000007 | 180 | No |
| binlog.000008 | 157 | No |
+---------------+-----------+-----------+
8 rows in set (0.00 sec)
第二个主节点配置
[root@rocky mysql]# 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=8
log_bin
auto_increment_offset=2
auto_increment_increment=2
[root@rocky mysql]# systemctl restart mysqld
登录mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.4',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=483,
-> MASTER_CONNECT_RETRY=5;
Query OK, 0 rows affected, 10 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 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: 5
Master_Log_File: binlog.000008
Read_Master_Log_Pos: 157
Relay_Log_File: rocky-relay-bin.000004
Relay_Log_Pos: 367
Relay_Master_Log_File: binlog.000008
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: 787
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: 4
Master_UUID: dac7dc24-4282-11ee-909d-000c29e5e294
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
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000005 | 157 | No |
| binlog.000006 | 180 | No |
| binlog.000007 | 485 | No |
| binlog.000008 | 157 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
第一个主节点
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000008', MASTER_LOG_POS=692,
-> MASTER_CONNECT_RETRY=5;
Query OK, 0 rows affected, 10 warnings (0.02 sec)
###下面开始验证
mysql> create database db1;
第二个主节点
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
第一个主节点
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
MySQL半同步复制
主节点
登录mysql，先安装主节点插件
[root@localhost ~]# rpm -ql mysql-server | grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_replica.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/semisync_source.so
[root@localhost ~]# mysql
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.02 sec)
配置文件
[root@localhost ~]# 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
log_bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
auto_increment_offset=1
auto_increment_increment=2
[root@localhost ~]# systemctl restart mysqld
登录mysql
mysql> 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 |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
从节点（两台）
安装从节点的插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)
配置文件
[root@rocky mysql]# 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=8
log_bin
rpl_semi_sync_slave_enabled=ON
auto_increment_offset=2
auto_increment_increment=2
[root@rocky mysql]# systemctl restart mysqld
登录mysql
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
注意：要在实现主从复制的基础下，执行操作才能看到效果
mysql> stop slave ;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
主节点
mysql> show global 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 | 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 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
用mycat实现mysql的读写分离
准备3台服务器，1台客户端
主节点配置
[root@localhost ~]# yum install mysql-server -y
[root@localhost ~]# 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
log_bin
general_log=ON //开启通用日志
max_binlog_size=1073741824
[root@localhost ~]# systemctl start mysqld
#创建复制账号
[root@localhost ~]# mysql
mysql> create user 'repluser'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
mysql> show master logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| localhost-bin.000001 | 157 | No |
+----------------------+-----------+-----------+
1 row in set (0.00 sec)
mysql> create user admin@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> grant all on *.* to 'admin'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
从节点配置
[root@rocky ~]# yum install mysql-server -y
[root@rocky ~]# 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=8
log_bin
general_log=ON
[root@rocky ~]# systemctl start mysqld
[root@rocky ~]# mysql
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.4',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=157,
-> MASTER_CONNECT_RETRY=5;
Query OK, 0 rows affected, 10 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 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: 5
Master_Log_File: localhost-bin.000001
Read_Master_Log_Pos: 157
Relay_Log_File: rocky-relay-bin.000002
Relay_Log_Pos: 330
Relay_Master_Log_File: localhost-bin.000001
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: 540
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: 4
Master_UUID: dac7dc24-4282-11ee-909d-000c29e5e294
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
mysql> create user admin@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> grant all on *.* to 'admin'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mycat服务器配置：此主机不需要开启mysqld服务
mycat依赖于java环境，需要下载java
[root@mycat ~]# yum install java -y
[root@mycat ~]# mkdir /apps
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[root@mycat mycat]# echo 'PATH=/apps/mycat/bin:$PATH' >/etc/profile.d/mycat.sh
[root@mycat mycat]# source /etc/profile.d/mycat.sh
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
mycat配置文件： server.xml
[root@mycat mycat]# cat /apps/mycat/conf/server.xml
....................................................
<user name="root" defaultAccount="true"> //连接mycat的用户名和密码，自行指定
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
....................................................
mycat配置文件 ：schema.xml
balance为1，表示读写分离；主库为10.0.0.4，从库为10.0.0.8；
[root@mycat mycat]# cat /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" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.4:3306" user="admin"
password="123456">
<readHost host="host2" url="10.0.0.8:3306" user="admin" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
启动mycat
//内存不宜太小，否则会启动失败。
[root@mycat profile.d]# mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@mycat profile.d]# echo $PATH
/apps/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mycat profile.d]# ss -ntl
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 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 128 *:3306 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@mycat profile.d]# mycat start
Starting Mycat-server...
[root@mycat conf]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 50 *:1984 *:*
LISTEN 0 100 *:8066 *:*
LISTEN 0 50 *:35427 *:*
LISTEN 0 50 *:40451 *:*
LISTEN 0 100 *:9066 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
客户端
[root@clinet ~]# mysql -uroot -p123456 -h 10.0.0.10 -P8066 //注意区分大小写P，p
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, 2022, 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.01 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_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 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 | Zhang ming | 30 | M |
| 6 | xiaozhang | 88 | F |
| 7 | TheShy | 34 | M |
+-----+---------------+-----+--------+
7 rows in set (0.02 sec)
mysql> insert teachers(name,age,gender) values('ggbond',35,'M');
Query OK, 1 row affected (0.01 sec)
主节点
[root@localhost ~]# tail -f /var/lib/mysql/localhost.log
2023-08-26T09:34:28.975795Z 25 Connect admin@10.0.0.10 on hellodb using TCP/IP
2023-08-26T09:34:38.974240Z 19 Query select user()
2023-08-26T09:34:48.971548Z 21 Query select user()
2023-08-26T09:34:58.973204Z 22 Query select user()
2023-08-26T09:35:08.972551Z 20 Query select user()
2023-08-26T09:35:18.974259Z 23 Query select user()
2023-08-26T09:35:28.972708Z 24 Query select user()
2023-08-26T09:35:38.974414Z 25 Query select user()
2023-08-26T09:35:48.972221Z 19 Query select user()
2023-08-26T09:35:58.974765Z 21 Query select user()
2023-08-26T09:36:08.971984Z 22 Query select user()
2023-08-26T09:36:18.973836Z 20 Query select user()
2023-08-26T09:36:23.419561Z 23 Query insert teachers(name,age,gender) values('ggbond',35,'M')
2023-08-26T09:36:28.971774Z 24 Query select user()
2023-08-26T09:36:38.973593Z 25 Query select user()
从节点
[root@rocky ~]# tail -f /var/lib/mysql/rocky.log
2023-08-26T09:34:28.984224Z 14 Query select user()
2023-08-26T09:34:28.986489Z 25 Connect admin@10.0.0.10 on hellodb using TCP/IP
2023-08-26T09:34:38.985104Z 16 Query select user()
2023-08-26T09:34:48.982314Z 21 Query select user()
2023-08-26T09:34:58.983940Z 20 Query select user()
2023-08-26T09:35:08.983315Z 24 Query select user()
2023-08-26T09:35:18.985214Z 22 Query select user()
2023-08-26T09:35:28.983473Z 17 Query select user()
2023-08-26T09:35:38.985146Z 18 Query select user()
2023-08-26T09:35:48.982909Z 14 Query select user()
2023-08-26T09:35:58.985654Z 25 Query select user()
2023-08-26T09:36:08.982637Z 16 Query select user()
2023-08-26T09:36:18.984603Z 21 Query select user()
2023-08-26T09:36:23.440009Z 8 Query BEGIN
2023-08-26T09:36:23.440341Z 8 Query COMMIT /* implicit, from Xid_log_event */
2023-08-26T09:36:28.982574Z 20 Query select user()
2023-08-26T09:36:38.984337Z 24 Query select user()
2023-08-26T09:36:48.982634Z 22 Query select user()
2023-08-26T09:36:58.983202Z 17 Query select user()
Open VPN部署
准备四台主机
- openvpn server：eth0（NAT）：192.168.3.140 eth1（仅主机）：10.0.0.4
- web1：eth0（仅主机）：10.0.0.6
- web2：eth0（仅主机）：10.0.0.8
- 客户端：window11
openvpn server
安装软件
[root@localhost /]# yum install openvpn -y
[root@localhost /]# yum install easy-rsa -y
服务端证书生成
[root@localhost /]# cp -r /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn/
[root@localhost /]# cp -r /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-server
[root@localhost /]# cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/easy-rsa-server/3/vars
[root@localhost /]# grep '^s.*' /etc/openvpn/easy-rsa-server/3/vars
set_var EASYRSA_CA_EXPIRE 36500 //ca证书的有效期
set_var EASYRSA_CERT_EXPIRE 90 //服务器证书
初始化PKI和CA颁发机构环境
[root@localhost 3]# cd /etc/openvpn/easy-rsa-server/3
[root@localhost 3]# ./easyrsa init-pki
[root@localhost 3]# ./easyrsa build-ca nopass
[root@localhost 3]# ./easyrsa gen-req server nopass //创建服务端证书申请
[root@localhost 3]# ./easyrsa sign server server //颁发服务端证书
[root@localhost 3]# ./easyrsa gen-dh //创建Diffie-Hellman密钥
客户端证书生成
[root@localhost 3]# cp -a /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-client
[root@localhost 3]# cd /etc/openvpn//easy-rsa-client/3/
[root@localhost 3]# ./easyrsa init-pki
[root@localhost 3]# ./easyrsa gen-req ggbond nopass
[root@localhost 3]# cd /etc/openvpn/easy-rsa-server/3
[root@localhost 3]# ./easyrsa import-req /etc/openvpn/easy-rsa-client/3/pki/reqs/ggbond.req ggbond //将客户端请求文件复制到CA工作目录
[root@localhost 3]# grep '^s.*' vars
set_var EASYRSA_CA_EXPIRE 36500
set_var EASYRSA_CERT_EXPIRE 90 //客户端颁发证书的有效期为90天
[root@localhost 3]# ./easyrsa sign client ggbond
将CA和服务器证书相关文件复制到服务器对应目录
[root@localhost 3]# mkdir /etc/openvpn/certs
[root@localhost 3]# cp /etc/openvpn/easy-rsa-server/3/pki/ca.crt /etc/openvpn/certs/
[root@localhost 3]# cp /etc/openvpn/easy-rsa-server/3/pki/issued/server.crt /etc/openvpn/certs/
[root@localhost 3]# cp /etc/openvpn/easy-rsa-server/3/pki/private/server.key /etc/openvpn/certs/
[root@localhost 3]# cp /etc/openvpn/easy-rsa-server/3/pki/dh.pem /etc/openvpn/certs/
客户端私钥与证书相关文件复制到服务器对应目录
[root@localhost 3]# mkdir /etc/openvpn/client/ggbond/
[root@localhost 3]# cp /etc/openvpn/easy-rsa-client/3.0.8/pki/private/ggbond.key /etc/openvpn/client/ggbond/
[root@localhost 3]# cp /etc/openvpn/easy-rsa-server/3.0.8/pki/issued/ggbond.crt /etc/openvpn/client/ggbond/
[root@localhost 3]# cp /etc/openvpn/easy-rsa-server/3.0.8/pki/ca.crt /etc/openvpn/client/ggbond/
[root@localhost 3]# ll /etc/openvpn/client/ggbond/
total 16
-rw------- 1 root root 1204 Aug 27 11:54 ca.crt
-rw------- 1 root root 4492 Aug 27 11:53 ggbond.crt
-rw------- 1 root root 1704 Aug 27 11:53 ggbond.key
openvpn服务器端配置
[root@localhost ~]# 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 192.168.3.0 255.255.255.0
ifconfig-pool-persist ipp.txt
push "route 10.0.0.0 255.255.255.0"
keepalive 10 120
cipher AES-256-CBC
compress lz4-v2
push "compress lz4-v2"
max-clients 1000
user openvpn
group openvpn
status /var/log/openvpn/openvpn-status.log
log-append /var/log/openvpn/openvpn.log
verb 3
mute 20
[root@localhost ~]# getent passwd openvpn
openvpn:x:975:974:OpenVPN:/etc/openvpn:/sbin/nologin
[root@localhost ~]# mkdir /var/log/openvpn
[root@localhost ~]# chown openvpn. /var/log/openvpn
[root@localhost ~]# ll -d /var/log/openvpn/
drwxr-xr-x 2 openvpn openvpn 6 Aug 27 15:17 /var/log/openvpn/
启动OpenVPN服务
因为centos8缺少unit文件
[root@localhost ~]# rpm -ql openvpn | grep systemd
/usr/lib/systemd/system/openvpn-client@.service
/usr/lib/systemd/system/openvpn-server@.service
/usr/share/doc/openvpn/README.systemd
[root@localhost ~]# tee /usr/lib/systemd/system/openvpn@.service <<EOF
> [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
> EOF
开启服务，端口号为1194
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start openvpn@server
[root@localhost ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 32 0.0.0.0:1194 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 70 *:33060 *:*
LISTEN 0 128 *:3306 *:*
[root@localhost ~]# systemctl status openvpn@server
● openvpn@server.service - OpenVPN Robust And Highly Flexible Tunneling Application On server
Loaded: loaded (/usr/lib/systemd/system/openvpn@.service; disabled; vendor preset: disabled)
Active: active (running) since Sun 2023-08-27 15:28:57 CST; 23s ago
Main PID: 23333 (openvpn)
Status: "Initialization Sequence Completed"
Tasks: 1 (limit: 11046)
Memory: 1.8M
CGroup: /system.slice/system-openvpn.slice/openvpn@server.service
└─23333 /usr/sbin/openvpn --cd /etc/openvpn/ --config server.conf
客户端配置文件
[root@localhost ~]# cat /etc/openvpn/client/ggbond/client.ovpn
client
dev tun
proto tcp //要跟服务器端相同
remote 192.168.3.140 1194
resolv-retry infinite
nobind
#persist-key
#persist-tun
ca ca.crt
cert ggbond.crt
key ggbond.key
remote-cert-tls server
#tls-auth ta.key 1
cipher AES-256-CBC //要跟服务器端相同
verb 3 //此项不能随意更改
compress lz4-v2 //要跟服务器端相同
客户端文件准备
[root@localhost ~]# cd /etc/openvpn/client/ggbond/
[root@localhost ggbond]#
[root@localhost ggbond]# pwd
/etc/openvpn/client/ggbond
[root@localhost ggbond]# ls
ca.crt client.ovpn ggbond.crt ggbond.key
[root@localhost ggbond]# tar cf ggbond.tar ./
tar: ./ggbond.tar: file is the archive; not dumped
[root@localhost ggbond]# ls
ca.crt client.ovpn ggbond.crt ggbond.key ggbond.tar
在window下载openvpn
https://openvpn.net/community-downloads/
根据提示把打包的文件放进文件夹
最后在客户端点击即可。
MySQL如何实现崩溃后恢复
- 二进制日志，记录着数据库更改所有操作，崩溃后可以利用二进制日志恢复。
- 事务日志，对于使用 InnoDB 存储引擎的表，事务日志记录了每个事务的更改，包括对数据的修改。当数据库崩溃后，可以使用事务日志来恢复尚未提交的事务，以保持数据库的一致性。
- 全量备份和增量备份，借助MySQL自身工具或者第三方工具备份。
- 检测和修复工具，mysqlcheck和myisamchk 。在崩溃后，可以使用这些工具来尝试修复损坏的数据文件。
Myisam和Innodb对比
- Myisam适合读多写少，表较小的场景，在写操作时，难以保持数据的完整性和一致性，不支持MVCC高并发，崩溃恢复性差。
- Innodb支持事务，支持MVCC高并发，崩溃恢复性好，行级锁，能保持数据的完整性和一致性，适合电子商务，金融系统等场景。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异