##
> https://dev.mysql.com/doc/refman/8.0/en/
>
> DML(data manipulation language)SELECTUPDATEINSERTDELETE
>
> DDL(data definition language)CREATEALTERDROPDDL(TABLE)
>
> DCL(Data Control Language)(grant,deny,revoke)sysadmin,dbcreator,db_ownerdb_securityadminDCL
### SQL
```sql
#
select * from tb_name;
#
delete from tb_name where id = 1;
#
insert into tb_name(id,name) values(1, 'a');
#
update tb_name set name='b' where id=1;
#
create database forbearance;
#
create table user(
id int(11),
name varchar(10)
);
# -
alter table tb_name add () [comment ''] [];
# -
alter table tb_name modify ();
# -
alter table tb_name change () [comment ''] [];
#
use mysql
select * from user;
#
create user ''@'' identified by '';
#
alter user ''@'' identified with mysql_native_password by '';
#
drop user ''@'';
#
show grants for ''@'';
#
grant on . to ''@'';
grant all on . to ''@'';
grant select on . to ''@'';
...
#
remove on . from ''@'';
remove all on . from ''@'';
remove select on . from ''@'';
...
```
countsummaxminavgselectgrouphavinggroup byhavinghavinggroup
> where > group by > having > order by
DQL
###
####
####
####
type yearmonthday
####
###
```sql
create table user(
id int primary key auto_increment comment '',
name varchar(10) not null unique comment '',
age int check(age > 0 && age AB
- AB
-
-
-
left join:
right join:
inner join:
join:
left outer join:
####
-
```sql
select ... from 1, 2 where ...;
```
-
```sql
# inner join join
select ... from 1 [inner] join 2 on ;
```
####
- `left join`
```sql
select ... from 1 left join 2 on ...
```
- `right join`
```sql
select ... from 1 right join 2 on ...
```
####
```sql
select ... from A a join A b on ...;
```
#### unionunion all
union
```sql
select ... from A...
union [all]
select ... from B...
```
``
unionunion all
####
SQL select
```sql
select * from t1 where column1 = (select column1 from t2);
```
`insert/update/delete/select`
wherefromselect
#####
=>>= any (select salary from emp where dept_id = (select id from dept where name = ''));
```
#####
=innot in
```sql
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '');
```
#####
in
```sql
select * from emp where (job, salary) in (select job, salary from emp where name = '' or name = '');
select e.*, d.* from (select * from emp where entrydate > '2023-01-01') e left join dept on e.dept_id = d.id;
```
###
MySQL DML MySQL
####
- /
```sql
# 10=
select @@autocommit;
set @@autocommit = 0;
```
-
```sql
start transaction;
#
begin;
```
-
```sql
commit;
```
-
```sql
rollback;
```
#### ACID
- Atomicity
- Consistency
- Isolation
- Durability
####
1.
2.
3. B insert
####
> Oracle Read committed
| | | | |
| ---- | ---- | ---- | ---- |
| Read uncommitted | | | |
| Read committed | | | |
| Repeatable Read | | | |
| Serializable | | | |
`Serializable`
```sql
select @@transaction_isolation;
```
```sql
set [session|gloabl] transaction isolation level READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
```
##
###
#### MySQL
`index`
-
-
SQL SQL
-
MySQL API
-
####
``/
-
```SQL
CREATE TABLE `` (
`id` bigint(1) NOT NULL,
...
n [comment ]
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci [comment ]
-- ENGINECHARSETCOLLATE
```
MySQL5.5`InnoDB`
-
```SQL
SHOW ENGINES;
--
Engine Support Comment Transactions XA Savepoints
------------------ ------- -------------------------------------------------------------- ------------ ------ ------------
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO
CSV YES CSV storage engine NO NO NO
FEDERATED NO Federated MySQL storage engine (NULL) (NULL) (NULL)
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
MyISAM YES MyISAM storage engine NO NO NO
InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys YES YES YES
BLACKHOLE YES /dev/null storage engine (anything you write to it disappears) NO NO NO
ARCHIVE YES Archive storage engine NO NO NO
```
`MyISAM` MySQL
- `MyISAM`
```sql
CREATE TABLE my_myisam(
id INT,
NAME VARCHAR(10)
)ENGINE=MYISAM;
```
- `MEMORY`
```sql
CREATE TABLE my_memory(
id INT,
NAME VARCHAR(10)
)ENGINE=MEMORY;
```
####
- InnoDB
-
- InnoDB MySQL 5.5 InnoDB MySQL
-
- DML ACID ``
- ``
- `` foreign key
-
- xxx.ibdxxxInnoDB frmsdi
- `frm`8.0`sdi``sdi``ibd`
- `innodb_file_per_table`8.0()
```sql
--
SHOW VARIABLES LIKE 'innodb_file_per_table';
--
Variable_name Value
--------------------- --------
innodb_file_per_table ON
```
- MyISAM
- MyISAM MySQL
-
-
-
-
- 
- .sdi
- .MYD
- .MYI
- Memory
- Memory
- hash
-
- .sdi
####
- InnoDBMySLQ InnoDB
- MyISAM MyISAM MongoDB
- MEMORYMEMORY Redis
###
####
index MySQL
- IOCPU
- insertupdatedelete
####
-
- B-Tree()
5b-tree4key5
``
B-tree
- B+Tree
4b-tree
B-Tree
1.
2.
- MySQL B+Tree
MySQL B+Tree B+Tree B+Tree
- Hash
hash hash hash hash
1. Hash =inbetween>20 AND idcard='123456789012345678';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ---------- ------ ------------------- ------------------- ------- ------ ------ -------- -----------------------
1 SIMPLE emp (NULL) range idx_emp_gen_age_idc idx_emp_gen_age_idc 208 (NULL) 3 10.00 Using index condition
EXPLAIN SELECT * FROM emp WHERE gender='' AND age>=20 AND idcard='123456789012345678';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ---------- ------ ------------------- ------------------- ------- ------ ------ -------- -----------------------
1 SIMPLE emp (NULL) range idx_emp_gen_age_idc idx_emp_gen_age_idc 611 (NULL) 4 10.00 Using index condition
```
`>=`
-
1. substring
```sql
--
EXPLAIN SELECT * FROM emp WHERE SUBSTRING(workno, 2, 1) = 1;
```
2.
3.
-
1. or or
or
2. MySQL50%
- SQL
SQL SQL
use index MySQL
ignore index MySQL
force index MySQL
- &
select *
idnameidname `select id name from tab_name;` InnoDB B+name id
SQL SQL gender id gender
-
varchartextIO
```sql
create index idx_xxx on table_name(column(''));
```
1
B+
- &
phone name
MySQL
####
1.
2. whereorder bygroup by
3. id
4.
5.
6.
7. NULL NOT NULL NULL
### SQL
####
- 500-1000
```sql
insert into tb_test values(1, 'a'), (2, 'b'), (3, 'c');
```
-
```sql
start transaction;
insert into tb_test values(1, 'a'), (2, 'b'), (3, 'c');
insert into tb_test values(4, 'a'), (5, 'b'), (6, 'c');
insert into tb_test values(7, 'a'), (8, 'b'), (9, 'c');
commit;
```
-
```sql
9 6 8 3 1 2 7 5 4
1 2 3 4 5 6 7 8 9
```
-
insert MySQL load
```sql
load data local infile '/root/sql1.log' into table `tab_user` fields terminated by ',' lines terminated by '\n'
load data local infile '' into table `` fields terminated by '' lines terminated by ''
```
####
-
InnoDB index organized table IOT
-
100%2-N21
50%
-
flaged
MERGE_THRESHOLD50%InnoDB
MERGE_THRESHOLD
-
#### order by
1. Using filesort `sort buffer` FileSort
2. Using index Using index
3.
4.
5. ASC/DESC
6. filesort `sort_buffer_size`256k
```sql
-- agephone
explain select id, age, phone from tb_user order by age, phone;
--
create index idx_user_age_phone on tb_user(age,phone);
-- agephone
explain select id, age, phone from tb_user order by age, phone;
-- agephone
explain select id, age, phone from tb_user order by age desc, phone desc;
-- agephonephone
explain select id, age, phone from tb_user order by age asc, phone desc;
--
create index idx_user_age_phone on tb_user(age,phone);
-- agephone
explain select id, age, phone from tb_user order by age asc, phone desc;
show variables like 'sort_buffer_size';
```
-
- agephone
- agephone
-
Backward index scanB+ Backward index scan
- agephone phone
-
#### group by
1.
2.
- group by
- where group by
#### limit
limit 2000000, 10MySQL 2000010 2000000-2000010
```sql
select s.* from tb_sku s, (select id from tb_sku order by id limit 2000000, 10) c where s.id = c.id
```
#### count
count
- MyISAM `count(*)` where
- InnoDB `count(*)`
RedisMySQL+1
count()count null
- count
- count()
InnoDB id
- count()
not null InnoDB
not null InnoDB null null
- count(1)
InnoDB 1
- count(*)
InnoDB
count() < count() < count(1) `count(*)` `count(*)`
#### update
update update name update update
InnoDB
### //
####
View
SQL SQL
```sql
--
create [or replace] view [()] as select [with[cascaded | local] check option];
CREATE OR REPLACE VIEW view_t_1 AS SELECT id, workno, age FROM emp;
--
--
show create view ;
--
select * from ;
--
create [or replace] view [()] as select [with[cascaded | local] check option];
alter view [()] as select [with[cascaded | local] check option];
--
drop view[if exists] [()];
```
- cascaded
with check option MySQLMySQLMySQLcascadedlocalcascaded
- cascaded cascaded `with cascaded check option`
- local
-
1. sum()min()max()count()
2. distinct
3. group by
4. having
5. union union all
####
```sql
--
create procedure ([])
begin
-- SQL
--
end;
--
call ([])
--
show create procedure ; --
select * from information_schema.routines where routines_schema = ''; --
--
drop procedure [if exists] ;
```
SQL delimiter SQL
-
MySQLglobalsession
```sql
--
SHOW [session|global] VARIABLES; --
SHOW [session|global] VARIABLES like ''; --
select @@[session|global] ; --
--
set [session|global] = ;
set @@[session|global] = ;
```
session/global session MySQLMySQL
-
`@`
```sql
--
set @val_name = expr [@val_name = expr];
set @val_name := expr [@val_name := expr];
select @val_name := expr [val_name := expr];
select into @val_name from ;
--
select @val_name
```
null
-
begin end declare
```sql
--
declare [default];
--
set = ;
set := ;
select into from ;
```
- if
```sql
--
if 1 then
...
elseif 2 then
...
else
...
end if;
```
- inoutinout
```sql
--
create procedure ([in/out/inout] )
begin
...
end;
in
out
inout
---------------------------------------------
create procedure p5(in score int, out result varchar(10))
begin
set result := '';
end;
call p5(99, @result);
select @result;
---------------------------------------------
---------------------------------------------
create procedure p5(inout score double)
begin
set socre := score * 0.5;
end;
set @score = 99;
call p5(@score);
select @score;
---------------------------------------------
```
- case
```sql
--
case case_value
when when_value1 then
...
when when_value2 then
...
else
...
end case;
--
-- when
case
when search_condition1 then
...
when search_condition2 then
...
else
...
end case;
```
- while
while SQL
```sql
-- true
while do
...
end while;
```
- repeat
repeat
```sql
--
repeat
...
until
end repeat;
```
- loop
loop SQL loop
- leave
- iterate continue
```sql
[begin_label] loop
...
end loop [end_label];
[begin_label]
leave label;
iterate label;
```
- cursor
cursor
```sql
--
declare cursor for ;
--
open ;
--
fetch into 1, 2 ...;
--
close ;
```
- handler
####
in
```sql
create function ([])
returns type [characteristic]
begin
...
return ...;
end;
characteristic
deterministic
no sql SQL
reads sql data
```
####
> Oracle:,
>
> SQL ServerINSTEAD OF
insert/update/delete SQL
old new MySQL
```sql
--
create trigger trigger_name
before/after insert/update/delete
on table_name for each row
begin
...
end;
--
show triggers;
-- schema_name
drop trigger [schema_name].trigger_name;
```
insert
update
delete
###
####
-
```sql
----------------------------
--
mysql> flush tables with read lock;
-- MySQL MySQL sql
-- C:\Users\admin> mysqldump -h 192.168.0.130 -uroot -p123456 > sql
C:\Users\admin> mysqldump -h 192.168.0.130 -uroot -p123456 demo1 > D:/demo1.sql
--
unlock tables;
```
-
1.
2. binlog
InnoDB`--single-transaction`InnoDB
```sql
mysqldump --single-transaction -uroot -p 123456 demo1 > demo1.sql
```
####
>
MyISAMInnoDBBDB
#####
```sql
--
lock tables [] read/write;
--
unlock tables /
```
1. read lock
2. write lock
##### meta data lockMDL
MDL MDL DML DDL
MySQL5.5 MDL MDL MDL
`begin; ... commit;`
```sql
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;
```
#####
DML InnoDB
1. IS select ... lock in share mode readwrite
2. IX insertupdatedeleteselect ... update readwrite
A update updateBA
SQL
```sql
SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks;
```
1
2
InnoDB
####
InnoDB
InnoDB B+Tree
1. Record Lock update delete RCRR
2. Gap Lock insert RR
3. Next-Key Lock Gap RR
#####
InnoDB
1. S
2. X
InnoDB REPEATABLE READ InnoDB next-key
1.
2. InnoDB InnoDB
##### /
InnoDB REPEATABLE READ InnoDB next-key
1.
2. next-key lock
3. next-key lock+
### InnoDB
####
> innodb-architecture-8.0
- ibd MySQL
- Leaf node segmentNon-leaf node segmentRollback segmentInnoDB B+B+ Extent
- 1MInnoDB 16K64
- InnoDB 16KBInnoDB 4-5
- InnoDB
- Trx_idid trx_id
- Roll_pointer undo
####
MySQL5.5 InnoDB InnoDB
##### in-memory-structures
- Buffer Pool
- IO
- PagePagePage
- free pagepage
- clean pagepage
- dirty pagepage
- 80%
- Change Buffer
- DML PageBuffer PoolChange BufferBuffer Pool
- Change Buffer I/O
- Adaptive Hash Index
- Buffer PoolInnoDBhashhash
- `innodb_adaptive_hash_index``SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';`
- Log Buffer
- logredo logundo log16MB`innodb_log_buffer_size`I/O
- `innodb_log_buffer_size`
- `innodb_flush_log_at_trx_commit`
- 1
- 0`innodb_flush_log_at_timeout`
- 2`innodb_flush_log_at_timeout`
- `innodb_flush_log_at_timeout`
##### on-disk-structures
- System Tablespace
- file-per-table MySQL InnoDB
- `innodb_data_file_path``ibdata1`
- File-Per-Table Tablespaces
- file-per-table InnoDBt1.ibdt2.ibd...
- InnoDB file-per-table `innodb_file_per_table``innodb_file_per_table`InnoDB
- General Tablespaces
- InnoDB `CREATE TABLESPACE`
```sql
-- mysql> CREATE TABLESPACE `` ADD DATAFILE '' Engine=InnoDB;
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
-- InnoDB
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
--
mysql> ALTER TABLE t2 TABLESPACE ts1;
```
- Undo Tablespaces
-
- MySQL undo 16M undo log undo_001undo_002
- Temporary Tablespaces
- InnoDB
- Doublewrite Buffer Files
- InnoDBBuffer Pool
- `#ib_16384_0.dblwr``#ib_16384_1.dblwr`#ib_page_size_file_number.dblwr.bdblwrDETECT_ONLYInnoDB
- Redo Log
- redo log bufferredo log
- ib_logfile0ib_logfile1
#####
InnoDB
- Master Thread
- undo
- IO Thread
- InnoDB AIO IO IO Thread IO
- Purge Thread
- undo logundo log Purge Thread
- Page Cleaner Thread
- Master Thread Master Thread
####
> undo log
>
> redo log
>
> undo log + redo log
>
> + MVCC
##### redo log
redo log bufferredo log file
redo log redis
##### undo log
undo log undo log
MVCC
undo logredo logundo log delete undo log insert update update rollback undo log undo log
Undo logundo log undo log MVCC
Undo logundo log rollback segment 1024 undo log segment
#### MVCC
#####
> MVCC MVCC
-
`select ... lock in share mode``select ... for update`updateinsertdelete
-
select
- Read Committed select
- Repeatable Read select
- Serializable
- MVCC
Multi-Version Concurrency Control MySQL MVCC MVCC undo logreadView
#####
InnoDB 2-3
- DB_TRX_IDIDID1
- DB_ROLL_PTR undo log
- DB_ROW_ID
##### undo log
insertupdatedelete
insert undo log
updatedelete undo log
undo log
undo log
undo log readView
##### readView
ReadViewSQLMVCCid
ReadView
- `m_ids`ID
- `min_trx_id`ID
- `max_trx_id`IDID+1ID
- `creator_trx_id`ReadView ID
ReadView
- READ COMMITTEDReadView
- REPEATABLE READReadViewReadView
##### RC
> READ COMMITTED ReadView
ReadViewundo logtrx_id
RCtrx_id2
ReadViewundo logtrx_id
RCtrx_id3
##### RR
> REPEATABLE READ ReadViewReadView
>
>
MVCC-
> MVCC + redo log + undo log
### MySQL
####
MySQL
| | |
| -------- | -------- |
| mysql | MySQL |
| information_schema | |
| performance_schema | MySQL |
| sys | DBA performance_schema |
####
1. mysql
mysql
`mysql [options] [database]`
```sql
-u, --user=name --
-p, --password[=name] --
-h, --host=name -- IP
-P, --port=port --
-e, --execute=name -- SQL
```
`-e` MySQL SQL MySQL
```sql
mysql -uroot -p123456 replication -e "select * from student";
```
2. mysqladmin
mysqladmin
```sql
mysqladmin --help
```
```linux
mysqladmin -uroot -p123456 version;
mysqladmin -uroot -p123456 drop 'db01';
```
mysqladmin mysql
3. mysqlbinlog
mysql
`mysqlbinlog [options] log-file1 log-file2 ...`
```sql
-d, --database=name #
-o, --offset=# # n
-r, result-file=name #
-s, --short-from #
-v # SQL
-vv # SQL
--start-datatime=date1 --stop-datatime=date2 #
--start-postition=pos1 --stop-postition=pos2 #
```
```linux
mysqlbinlog binlog.000002
```
4. mysqlshow
mysqlshow
`mysqlshow [options] [db_name[table_name[col_name]]]`
```sql
--count #
-i #
```
```linux
#
mysqlshow -uroot -p123456 --count
#
+--------------------+--------+--------------+
| Databases | Tables | Total Rows |
+--------------------+--------+--------------+
| db02 | 0 | 0 |
| db03 | 0 | 0 |
| information_schema | 65 | 21888 |
| mysql | 33 | 2908 |
| performance_schema | 102 | 360658 |
| replication | 1 | 2 |
| sys | 101 | 4493 |
+--------------------+--------+--------------+
# replication
mysqlshow -uroot -p123456 replication --count
#
Database: replication
+----------+----------+------------+
| Tables | Columns | Total Rows |
+----------+----------+------------+
| student | 3 | 2 |
+----------+----------+------------+
# replication student
mysqlshow -uroot -p123456 replication student --count
#
Database: replication Table: student Rows: 2
+-------+---------------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+---------------+-----------+------+-----+---------+-------+---------------------------------+---------+
| id | bigint(11) | | NO | PRI | | | select,insert,update,references | |
| name | varbinary(11) | | YES | | | | select,insert,update,references | |
| age | int(3) | | YES | | | | select,insert,update,references | |
+-------+---------------+-----------+------+-----+---------+-------+---------------------------------+---------+
```
5. mysqldump
mysqldump SQL
```linux
#
mysqldump [options] db_name[tables]
mysqldump [options] --database/-B db1 [db2 db3 ...]
mysqldump [options] --all-databases/-A
#
-u, --user=name #
-p, --password=[name] #
-h, --host=name # ip
-P, --port=# #
#
--add-drop-database # drop database
--add-drop-table # drop table --skip-add-drop-table
-n, --no-create-db #
-t, --no-create-info #
-d, --no-data #
-T, --tab=name # .sql .txt
```
```linux
# replication .sql
mysqldump -uroot -p123456 replication > replication.sql
mysqldump -uroot -p123456 -T /var/lib/mysql-files/ replication student
```
6. mysqlimport/source
mysqlimport mysqldump T
```linux
mysqlimport[options] db_name textfile1 [textfile2 ...]
mysqlimport -uroot -p123456 replication /var/lib/mysql-files/student.txt
```
sql source
```linux
# mysql
source /var/lib/mysql-files/student.sql
```
##
###
####
MySQL mysqld
/var/log/ mysqld.log
```sql
--
show variables like '%log_error%'
```
#####
binlogDDLDMLselectshow
12MySQL
MySQL8
```sql
show variables like '%log_bin%'
```
-
MySQL
| | |
| ---- | ---- |
| STATEMENT | SQL SQL SQL |
| ROW | update55 |
| MIXED | STATEMENTROWSTATEMENTROW |
MySQL
```sql
show variables like '%binlog_format%'
```
```sql
--
vim /etc/my.cnf
--
binlog_format=STATEMENT
```
-
| | |
| ---- | ---- |
| reset master | binlog binlog.000001 |
| purge master logs to 'binlog.xxxxxx' | xxxxxx |
| purge master logs before 'yyyy-mm-dd hh24:mi:ss' | 'yyyy-mm-dd hh24:mi:ss' |
MySQL
```sql
show variables like '%binlog_expire_logs_seconds%'; 259200030
```
`binlog_expire_logs_seconds`
#####
DDLDMLDQL ... SQL
```sql
SHOW VARIABLES LIKE '%general%';
-- MySQL
-- 0=1=
general_log=1
-- host_name.log
general_log_file=mysql_query.log
```
#####
long_query_time min_examined_row_limit0 SQL
```sql
slow_query_log=1
long_query_time=2
```
long_query_time 10
```sql
--
log_slow_admin_statements=1
--
log_queries_not_using_index=1
```
###
DDL DML
MySQL Master Salve
MySQL
1.
2.
3.
####
1. Master binlog
2. IO Thread binlogIO Thread Relay log
3. Slave SQL Thread
####
- 3306
```linux
# 3306
firewall-cmd --zone=public --add-port=3306/tcp-permanent
firewall-cmd -reload
#
systemctl stop firewall
#
systemctl disable firewall
```
#####
1. `vim /etc/my.cnf`
```cnf
# MySQL ID1-2^32 -11
server-id=1
# 1=0=
read-only=0
#
# binlog-ignore-db=mysql
#
# binlog-do-db=test
```
2. MySQL
```linux
systemctl restart mysqld
```
3. mysql
```sql
# replication `@'%'` MySQL
create user 'replication'@'%' identified with mysql_native_password by 'Root@123456';
# 'replication'@'%'
grant replication slave on *.* to 'replication'@'%';
```
4.
```sql
show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 668 | | | |
+---------------+----------+--------------+------------------+-------------------+
```
- file
- position
- binlog_ignore_db
#####
1. `vim /etc/my.cnf`
```cnf
# MySQL ID1-2^32 -1
server-id=2
# 1=0=
read-only=1
#
# super-read-only=1
```
2. MySQL
```linux
systemctl restart mysqld
```
3. mysql
```sql
-- 8.0.23
change replication source to source_host='',souce_user='',source_password='',source_log_file='',source_log_pos=xxx;
-- 8.0.23
change master to master_host='',master_user='',master_password='',master_log_file='',master_log_pos=xxx;
```
4.
```sql
# 8.0.22
start replica;
#8.0.22
start slave;
```
5
```sql
# 8.0.22
show replica status;
#8.0.22
show slave status;
```
sql
####
#####
1.
```cnf
#
server-id=1
#
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
#
log-slave-updates
```
`log-slave-updates` log-slave-updatesbinlog
`log-bin`log-binlog-binI0SQLbinlogbinloglog-slave-updates
2. MySQL
```cmd
systemctl restart mysqld
```
3.
4.
5.
6.
7.
```cmd
# 1221
change master to master_host='',master_user='',master_password='',master_log_file='',master_log_pos=xxx;
#
start slave;
```
###
/
1.
2.
3.
1.
2. /
3.
1.
2.
3.
1.
2.
3.
##
#### InnoDB
Windows .ibd
```cmd
C:\ProgramData\MySQL\MySQL Server 8.0\Data\test>ibd2sdi emp.ibd
```
#### InnoDB
####
#### InnoDB B+Tree
1.
2. B-Tree
3. hashB+Tree
#### InnoDBB+tree
1k16InnoDB 6 bigint8.
```text
2
n * 8 + (n + 1) * 6 = 16 * 1024 n 1170
1171 * 16 = 18736
n key (n+1)*6 key 11701171
2
1171 * 1171 * 16 = 21939856
```
####
AASCDDESC
