ð¢ð¢ð¢ð£ð£ð£ åå½ï¼å¤§å®¶å¥½ï¼ææ¯ãITé¦å¾·ãï¼æ±æ¹äººç§°ï¼10ä½å¹´DBAå·¥ä½ç»éª
ä¸ä½ä¸è¿å¿å足çã大æ°æ®é¢åå主ãï¼ððð
ä¸å½DBAèç(ACDU)æåï¼ç®åä»äºDBAåç¨åºç¼ç¨
æ é¿ä¸»æµæ°æ®OracleãMySQLãPG è¿ç»´å¼åï¼å¤ä»½æ¢å¤ï¼å®è£ è¿ç§»ï¼æ§è½ä¼åãæ éåºæ¥å¤ççã
⨠å¦ææ对ãæ°æ®åºãæå ´è¶£çãå°å¯ç±ãï¼æ¬¢è¿å ³æ³¨ãITé¦å¾·ãððð
â¤ï¸â¤ï¸â¤ï¸æè°¢åä½å¤§å¯ç±å°å¯ç±ï¼â¤ï¸â¤ï¸â¤ï¸
æç« ç®å½
- ââåè¨ââ
- ââð£ 1.å®è£ MySQL 8.0.31ââ
- ââð¢ 1.1 repoä¸è½½ââ
- ââð¢ 1.2 æ¥è¯¢yuméçMySQLçæ¬ââ
- ââð¢ 1.3 é ç½®å®è£ çMySQLççæ¬ââ
- ââð¢ 1.4 å®è£ MySQLââ
- ââð¢ 1.5 ç»éMySQLââ
- ââð£ 2.8.0.30 or Higheræ°ç¹æ§ââ
- ââð¢ 2.1 Redo Logââ
- ââð¢ 2.2 GIPKââ
- ââð¢ 2.3 å¤çº§å«ç ORDER BY or LIMITââ
- ââð¢ 2.4 innodb_doublewriteââ
- ââð¢ 2.5 mysqldumpââ
åè¨
MySQL 8.0.30 or Higheråå¸åï¼æ»ç»çä¸äºæ°ç¹æ§å享ç»å¤§å®¶
ð£ 1.å®è£ MySQL 8.0.31
æ¬æ¬¡éç¨yumå¨çº¿å®è£ é¨ç½²
ð¢ 1.1 repoä¸è½½
[root@jeames ~]# rpm -Uvh https://repo.mysql.com//mysql80-community-release-el8.rpm
Retrieving https://repo.mysql.com//mysql80-community-release-el8.rpm
warning: /var/tmp/rpm-tmp.SiEZMj: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:mysql80-community-release-el8-4 ################################# [100%]
ð¢ 1.2 æ¥è¯¢yuméçMySQLçæ¬
[root@jeames ~]# yum repolist all | grep mysql
Failed to set locale, defaulting to C.UTF-8
mysql-cluster-8.0-community MySQL Cluster 8.0 Community disabled
mysql-cluster-8.0-community-debuginfo MySQL Cluster 8.0 Community - Deb disabled
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - Sou disabled
mysql-connectors-community MySQL Connectors Community enabled
mysql-connectors-community-debuginfo MySQL Connectors Community - Debu disabled
mysql-connectors-community-source MySQL Connectors Community - Sour disabled
mysql-tools-community MySQL Tools Community enabled
mysql-tools-community-debuginfo MySQL Tools Community - Debuginfo disabled
mysql-tools-community-source MySQL Tools Community - Source disabled
mysql-tools-preview MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview - Source disabled
mysql80-community MySQL 8.0 Community Server enabled
mysql80-community-debuginfo MySQL 8.0 Community Server - Debu disabled
mysql80-community-source MySQL 8.0 Community Server - Sour disabled
ð¢ 1.3 é ç½®å®è£ çMySQLççæ¬
[root@jeames ~]# yum -y install yum-utils
[root@jeames ~]# yum-config-manager --enable mysql80-community
#æ¥è¯¢å®è£
çMySQLççæ¬
[root@jeames ~]# yum repolist enabled | grep mysql
mysql-connectors-community MySQL Connectors Community
mysql-tools-community MySQL Tools Community
mysql80-community MySQL 8.0 Community Server
ð¢ 1.4 å®è£ MySQL
å
æ§è¡ï¼yum module disable mysql
åæ§è¡ï¼yum -y install mysql-community-server
#åå§åMySQL
[root@jeames ~]# systemctl start mysqld
#æ¥çMySQLç¶æ
[root@jeames ~]# systemctl status mysqld
ð¢ 1.5 ç»éMySQL
#æ¥ç临æ¶å¯ç
[root@jeames ~]# grep 'temporary password' /var/log/mysqld.log
#ç»éMySQLåä¿®æ¹å¯ç ,è®°å¾ä¸å®è¦ç¨identified with mysql_native_password
[root@jeames ~]# mysql -uroot -p
mysql> ALTER USER root@'localhost' identified with mysql_native_password BY '********';
mysql> ALTER USER root@'%' identified with mysql_native_password BY '********';
mysql> grant all on *.* to root@'%' with grant option;
mysql> flush privileges;
mysql> status
ð£ 2.8.0.30 or Higheræ°ç¹æ§
ð¢ 2.1 Redo Log
â¨â¨ innodb_redo_log_capacityåæ°
å¨MySQL 8.0.30ä¸ï¼innodb_redo_log_capacityç³»ç»åéæ§å¶éåæ¥å¿æ件å ç¨çç£ç空é´éã
å¯ä»¥å¨å¯å¨æè¿è¡æ¶ä½¿ç¨set GLOBALè¯å¥å¨é项æ件ä¸è®¾ç½®æ¤åéï¼
ä¾å¦ï¼ä»¥ä¸è¯å¥å°éåæ¥å¿å®¹é设置为8GBï¼
SET GLOBAL innodb_redo_log_capacity = 8589934592;
说æï¼
innodb_redo_log_capacityåéå代äºå·²å¼ç¨çinnodb_ log_files_in_groupåinnodb _log_file_sizeåéã
å®ä¹innodb_redo_log_capacity设置æ¶ï¼å°å¿½ç¥innodb _log_files_in_groupåinnodb_ log_file_size设置ï¼
å¦åï¼è¿äºè®¾ç½®å°ç¨äºè®¡ç®innodb_redo_log_capacity设置
innodb.log_files_in_group*innodblog_file_size=innodb_do_log_capacity
å¦æ没æ设置è¿äºåéï¼åéåæ¥å¿å®¹éå°è®¾ç½®ä¸ºinnodb_redo_log_capacityé»è®¤å¼ï¼å³104857600åèï¼100MBï¼ã
æ大éåæ¥å¿å®¹é为128GB
â¨â¨ éåæ¥å¿æ件
å¨MySQL 8.0.30ä¹åï¼InnoDBé»è®¤å¨æ°æ®ç®å½ä¸å建两个éåæ¥å¿æ件ï¼
åå«å为ib_logfile0åib_logfile1ï¼å¹¶ä»¥å¾ªç¯æ¹å¼åå ¥è¿äºæ件ã
éåæ¥å¿æ件使ç¨#ib_redoNå½å约å®ï¼å
¶ä¸Næ¯éåæ¥å¿çæ件å·ãå¤ç¨éåæ¥å¿æ件ç±_tmpåç¼è¡¨ç¤ºã
ä¸é¢ç示ä¾æ¾ç¤ºäº#innodb_redoç®å½ä¸çéåæ¥å¿æ件ï¼å
¶ä¸æ1个活å¨éåæ¥å¿å31个å¤ç¨éåæ¥å¿ï¼æ顺åºç¼å·ã
é¤éinnodb_log_group_home_diråéæå®äºä¸åçç®å½ï¼å¦åéåæ¥å¿æ件ä½äºæ°æ®ç®å½ç#innodb_ Redoç®å½ä¸ã
å¦æå®ä¹äºinnodb_log_group_home_dirï¼åéåæ¥å¿æ件ä½äºè¯¥ç®å½ä¸ç#innodb_ redoç®å½ä¸ã
æ两ç§ç±»åçéåæ¥å¿æ件ï¼æ®éåå¤ç¨ãæ®éçéåæ¥å¿æ件就æ¯æ£å¨ä½¿ç¨çé£äºæ件ã
å¤ç¨éåæ¥å¿æ件æ¯é£äºçå¾
使ç¨çæ件ã
InnoDBå°è¯ç»´æ¤æ»å
±32个éåæ¥å¿æ件ï¼æ¯ä¸ªæ件ç大å°çäº1/32*InnoDB_redo_log_capacityï¼
mysql> select @@innodb_log_group_home_dir;
+-----------------------------+
| @@innodb_log_group_home_dir |
+-----------------------------+
| ./ |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT FILE_NAME, START_LSN, END_LSN FROM performance_schema.innodb_redo_log_files;
+--------------------------+-----------+----------+
| FILE_NAME | START_LSN | END_LSN |
+--------------------------+-----------+----------+
| ./#innodb_redo/#ib_redo6 | 19656704 | 22931456 |
+--------------------------+-----------+----------+
说æï¼
1.æ¯ä¸ªæ®ééåæ¥å¿æ件é½ä¸ç¹å®èå´çLSNå¼ç¸å
³èï¼ä»¥ä¸æ¥è¯¢æ¾ç¤ºäºååºçæ´»å¨éåæ¥å¿æ件çSTART_LSNåEND_LSNå¼
2.æ§è¡æ£æ¥ç¹æ¶ï¼InnoDBå°æ£æ¥ç¹LSNåå¨å¨å
å«è¯¥LSNçæ件ç头ä¸ï¼
å¨æ¢å¤æé´ï¼å°æ£æ¥ææéåæ¥å¿æ件ï¼å¹¶ä»ææ°çæ£æ¥ç¹LSNå¼å§æ¢å¤ã
ð¢ 2.2 GIPK
ä»MySQL 8.0.30å¼å§ï¼MySQLæ¯æå¨GIPK模å¼ä¸è¿è¡æ¶çæçä¸å¯è§ä¸»é®ã
å¨è¿ç§æ¨¡å¼ä¸è¿è¡æ¶ï¼å¯¹äºä»»ä½æ²¡ææ¾å¼ä¸»é®å建çInnoDB表ï¼MySQLæå¡å¨ä¼èªå¨å表ä¸æ·»å çæçä¸å¯è§ä¸»é®ï¼GIPKï¼
æ°çæ¬ä¸ºæ们æä¾äºä¸ä¸ªä»¤äººæåçç¹æ§ -ï¼Generated Invisible Primary Keysï¼ç®ç§°GIPK ã
ä¸å¥æ¦åµå°±æ¯: å½å¼å¯GIPK模å¼åï¼MySQL ä¼å¨æ²¡ææ¾ç¤ºå®ä¹ä¸»é®çInnoDB表ä¸èªå¨çæä¸å¯è§ç主é®ã
å¦æ没æ主é®ï¼éå°load dataï¼å¤§äºå¡ï¼ddl çæ大é表æ°æ®è¡æ«æçè¡ä¸ºæ¶ï¼
ä¼å¸¦æ¥ä¸¥éç主ä»å»¶è¿ï¼ç»æ°æ®åºç¨³å®æ§åæ°æ®ä¸è´æ§å¸¦æ¥éæ£ï¼é£ä¹GIPK解å³äºè¿ä¸ªé®é¢ã
mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
| 0 |
+--------------------------------------+
说æï¼
GIPK模å¼ç±sql_generate_invisible_primary_keyæå¡å¨ç³»ç»åéæ§å¶ã
é»è®¤æ
åµä¸ï¼è¯¥åéçå¼ä¸ºOFFï¼è¿æå³çç¦ç¨äºGIPK模å¼ï¼è¦å¯ç¨GIPK模å¼ï¼è¯·å°åé设置为ON
æ¥ä¸æ¥å°±æ¼ç¤ºä¸GIPKçç¹æ§
##GIPK模å¼é»è®¤å
³é
mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> use jeames
mysql> CREATE TABLE auto_n1 (c1 VARCHAR(50), c2 INT);
##å¼å¯GIPK模å¼
mysql> SET sql_generate_invisible_primary_key=ON;
mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
| 1 |
+--------------------------------------+
mysql> CREATE TABLE auto_n2 (c1 VARCHAR(50), c2 INT);
## 使ç¨SHOW CREATE TABLEæ¥ç表å®é
å建æ¹å¼çå·®å¼
mysql> SHOW CREATE TABLE auto_n1\G
*************************** 1. row ***************************
Table: auto_n1
Create Table: CREATE TABLE `auto_n1` (
`c1` varchar(50) DEFAULT NULL,
`c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE auto_n2\G
*************************** 1. row ***************************
Table: auto_n2
Create Table: CREATE TABLE `auto_n2` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`c1` varchar(50) DEFAULT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
说æï¼
1.ç±äºauto_n2没æç¨äºå建å®çCREATE TABLEè¯å¥æå®ç主é®ï¼å æ¤GIPK模å¼ä½¿MySQLå°ä¸å¯è§çåmy_row_idå该åä¸ç主é®æ·»å å°æ¤è¡¨ä¸ã
ç±äºå¨å建auto_n1æ¶ç¦ç¨äºGIPK模å¼ï¼å æ¤æªå¨è¯¥è¡¨ä¸æ§è¡æ¤ç±»æ·»å ã
2.å½æå¡å¨ä»¥GIPK模å¼å°ä¸»é®æ·»å å°è¡¨ä¸æ¶ï¼ååé®å称å§ç»ä¸ºmy_row_idã
å æ¤ï¼å½å¯ç¨GIPK模å¼æ¶ï¼é¤é表å建è¯å¥è¿æå®äºæ¾å¼ä¸»é®ï¼å¦åä¸è½å建å
·æå为my_row_idçåç表ã
ï¼å¨è¿ç§æ
åµä¸ï¼ä¸éè¦ä¸ºåæé®å½åmy_row_idãï¼
## VISIBLEåINVISIBLEä¹é´åæ¢
å½GIPK模å¼çææ¶ï¼çæç主é®ä¸è½æ´æ¹ï¼åªè½å¨VISIBLEåINVISIBLEä¹é´åæ¢ã
è¦ä½¿auto_n2ä¸çæçä¸å¯è§ä¸»é®å¯è§ï¼è¯·æ§è¡ä»¥ä¸ALTER TABLEè¯å¥ï¼
mysql> ALTER TABLE auto_n2 ALTER COLUMN my_row_id SET VISIBLE;
mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "auto_n2";
è¦ä½¿çæç主é®å次ä¸å¯è§ï¼è¯·æ§è¡
ALTER TABLE auto_1 ALTER COLUMN my_row_id SET invisibleã
å建æå¯¼å ¥ä½¿ç¨GIPK模å¼çå®è£ å¤ä»½æ¶ï¼å¯ä»¥æé¤çæçä¸å¯è§PKååå¼ã
mysqldumpçâskipçæçä¸å¯è§ä¸»é®é项ä¼å¯¼è´GIPKä¿¡æ¯è¢«æé¤å¨ç¨åºçè¾åºä¸ã
å¦æè¦å¯¼å ¥å å«GIPKé®åå¼ç转å¨æ件ï¼è¿å¯ä»¥ä½¿ç¨mysqlpumpä¸çâskipçæçä¸å¯è§ä¸»é®æ¥æå¶è¿äºé®åå¼ï¼ä»èä¸å¯¼å ¥ï¼ã
å°ä¼ä¼´ä»¬æ¯ä¸æ¯æè§è¿ä¸ªç¹æ°å¾æ£
ð¢ 2.3 å¤çº§å«ç ORDER BY or LIMIT
å¨ MySQL 8.0.31 ä¹åï¼å¸¦æ¬å·çæ¥è¯¢è¡¨è¾¾å¼ä¸å 许å¤ä¸ªçº§å«ç ORDER BY or LIMIT æä½ï¼æ¥è¯¢ä¼è¢«æç»ã
å¨ MySQL 8.0.31 åæ´é«çæ¬ä¸ï¼åæ¶äºæ¤éå¶ï¼å¹¶å 许åµå¥ç带æ¬å·çæ¥è¯¢è¡¨è¾¾å¼ã
æ¯æçæ大åµå¥çº§å«ä¸º 63ï¼è¿æ¯å¨è§£æå¨æ§è¡ä»»ä½ç®åæå并ä¹åã
以ä¸æ¯ç¤ºä¾:
mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 2) LIMIT 3;
(
(SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2
) ORDER BY c LIMIT 1;
ð¢ 2.4 innodb_doublewrite
ç³»ç»åéæ¯æ DETECT_ONLY å DETECT_AND_RECOVER 设置ã
使ç¨è¯¥ DETECT_ONLY设置ï¼æ°æ®åºé¡µé¢å 容ä¸ä¼åå ¥ååç¼å²åºï¼å¹¶ä¸æ¢å¤ä¸ä¼ä½¿ç¨ååç¼å²åºæ¥ä¿®å¤ä¸å®æ´ç页é¢åå ¥ã
æ¤è½»éçº§è®¾ç½®ä» ç¨äºæ£æµä¸å®æ´ç页é¢åå ¥ã
该 DETECT_AND_RECOVER 设置çåäºç°æON 设置ã
ð¢ 2.5 mysqldump
mysqldump æ§è¡å ¨è¡¨æ«æï¼è¿æå³çå®çæ¥è¯¢é常ä¼è¶ è¿ long_query_time
对常è§æ¥è¯¢æç¨ç设置ãä» MySQL 8.0.30 å¼å§ï¼å¦æè¦ä»æ ¢æ¥è¯¢æ¥å¿ä¸æé¤å¤§é¨åæå ¨é¨ mysqldump 产ççæ ¢æ¥è¯¢ï¼
å¯ä»¥è®¾ç½® mysqldump ç --mysqld-long-query-time
å½ä»¤è¡é项ï¼å°ç³»ç»åéç session å¼æ´æ¹ä¸ºæ´é«çå¼ã