目錄

一、概述

數據庫概念

數據庫的類型

關系型數據庫模型

關系數據庫相關概念

二、安裝

1、mariadb安裝

2、mysql安裝

3、啟動并開機自啟

4、本地連接（本地登錄）

三、mysql數據庫配置與命令

yum安裝后生成的目錄

mysql服務器的啟動腳本

數據存儲目錄的權限

服務啟動時讀取的默認配置文件

mysqld操作命令

mysql命令

mysqladmin命令

四、默認數據庫

五、 SQL語言（背會）

六、mysql數據類型

1、整型

2、浮點型

3、BIT類型（了解）

4、定點型

5、日期時間類型

YEAR類型

DATE類型

TIME類型

DATETIME類型

TIMESTAMP類型

6、字符串型

CHAR和VARCHAR類型

TEXT類型

ENUM類型

SET類型

BINARY和VARBINARY類型

BLOB類型

JSON數據類型

7、特殊的NULL類型

七、數據庫操作命令

庫操作命令

表結構操作命令

alter命令用法

表數據操作

插入數據

where字句運算符

更新數據

刪除數據

查詢數據

八、mysqld用戶權限管理

MySQL數據庫權限分類

MySQL 權限對照表

關鍵權限分類

創建遠程管理用戶

創建遠程web服務數據庫的操作用戶

查看用戶權限

刪除用戶權限

案例

九、mysqld數據庫備份

9.1 數據備份的重要性

9.2 造成數據丟失的原因

9.2 備份需要考慮的問題

9.4 備份類型

1、根據是否需要數據庫離線

1.1、常用備份工具

2、根據要備份的數據集合的范圍

建議的恢復策略

3、根據備份數據或文件

9.5 常見的備份方法

1、物理冷備（完全備份）

2、專用備份工具mysqldump或mysqlhotcopy （完全備份，邏輯備份）

3、第三方工具備份

9.6 備份案例

1、完整備份

所有數據庫的備份

指定數據庫的備份

指定數據表的備份

2、增量備份

配置過程

查看日志文件內容

二進制備份的恢復

模擬數據丟失流程

數據恢復

XtraBackup 8.0.35-33 使用指南

簡介

安裝

在基于 RPM 的系統上安裝

在基于 Debian 的系統上安裝

基本使用

1. 完整備份

2. 準備備份（應用日志）

3. 恢復備份

高級使用案例

案例1：增量備份

案例2：壓縮備份

案例3：并行備份和恢復

案例4：加密備份

案例5：流式備份

常用選項說明

注意事項

最佳實踐

MyDumper 詳細使用指南

安裝 MyDumper

Ubuntu/Debian 系統

CentOS/RHEL 系統

從源碼編譯

基本使用

1. 完整備份數據庫

2. 恢復數據庫

常用參數說明

使用案例

案例1：備份單個數據庫

案例2：備份多個特定表

案例3：多線程備份（8個線程）

案例4：壓縮備份

案例5：按100萬行分割表數據

案例6：備份數據庫結構（不備份數據）

案例7：恢復數據庫到不同名稱

案例8：只恢復特定表

高級功能

1. 一致性快照備份

2. 正則表達式過濾表

3. 備份時排除某些表

4. 長查詢超時設置

5. 只備份數據不備份結構

實際應用場景

場景1：生產環境每日備份

場景2：大數據表部分恢復

場景3：跨服務器遷移數據庫

注意事項

性能優化建議

mysqlhotcopy 使用指南

安裝與準備

檢查是否安裝

確保依賴安裝（Perl 模塊）

基本語法

常用選項

使用案例

案例1：備份單個數據庫

案例2：備份多個數據庫

案例3：使用正則表達式備份匹配的數據庫

案例4：保留舊備份

案例5：遠程備份到其他服務器

案例6：不備份索引文件

案例7：備份后刷新日志

案例8：模擬運行（不實際備份）

實際應用場景

場景1：生產環境每日備份腳本

場景2：備份特定表

場景3：增量備份策略

恢復數據庫

注意事項

性能優化建議

替代方案

附錄 A mysqld配置文件

附錄 B mysql常見內置函數

一、字符串函數

二、日期時間函數

三、數學函數

四、邏輯函數

五、加密函數

---

一、概述

數據庫概念

數據庫(Database)

簡稱DB，按照一定格式存儲數據的一些文件的組合，顧名思義就是存儲數據的倉庫，實際上就是一堆文件。這些文件中存儲了具有特定格式的數據 。數據庫管理數據有諸多優點，如降低存儲數據的冗余度，存儲的數據可以共享，便于維護數據完整性，能夠實現數據的安全性等。數據庫的發展經歷了萌芽、初級、中級、和高級階段。

數據庫管理系統（DateBase Management System）

簡稱DBMS，數據庫管理系統是專門用來管理數據庫中的數據的，可以對數據庫當中的數據進行增、刪、改、查 等操作，常見的數據庫管理系統：MySql、Oracle、MS SQLServer、DB2、sysbase等…

SQL（Structured Query Language）

結構化查詢語言，針對關系型數據庫的一種語言；SQL 是一種操作數據庫的語言，包括創建數據庫、刪除數據庫、查詢記錄、修改記錄、添加字段等。SQL在MySql中可以使用，同時在Oracle中也可以使用，在DB2中也可以使用 。

數據庫的類型

關系型數據庫 （結構化存儲，數據分類清晰，存儲較慢）

• MariaDB/MySQL

• Oracle

• DB2

• MS SQLServer，只能用于windows系統

  國產數據庫

• 達夢

• 人大金倉

• TiDB，分布式關系型數據庫

• polarDB 阿里，協議MySQL、postgreSQL

NOSQL數據庫（非關系型，存儲速度快）

• Redis，緩存數據庫

• MongoDB，文檔型數據庫

關系型數據庫模型

• 層次模型 （上下級）

• 網狀模型

• 關系模型

關系模型的相關概念：

1. 實體（Entity） ：（方框）實體是指現實世界中可以區分的對象，它可以是具體的人、事物或概念，也可以是抽象的物體。在數據庫中，實體通常對應于表中的行，而表則實體的一種集合。例如，在一個學生信息管理系統中，學生、課程和教師都可以被視為實體。

2. 屬性（Attribute）：（圓圈）屬性是實體的特征或性質，它提供了關于實體的具體信息。屬性可以是簡單的數據類型，如姓名、年齡或地址，也可以是更復雜的結構，如電話號碼或電子郵件地址。在數據庫中，屬性通常對應于表中的列，每列代表實體的一種屬性。例如，學生的姓名、學號和年齡都是學生的屬性。

3. 聯系（Relationship）： （菱形）聯系描述了不同實體之間的關系。在現實世界中，實體之間往往存在各種形式的關聯。在數據庫中，聯系通過關系表來實現，這個表通常包含兩個或更多的實體作為表的列。例如，學生選修課程的關系表就包含了學生實體和課程實體的相關信息，描述了哪位學生選修了哪門課程。

在關系模型中，實體和聯系通常通過以下三種類型來進一步分類：

• 一對一（1:1）：這種聯系表示兩個實體之間存在一對一的關系，即每個實體只有一個相關的實體，反之亦然。例如，一個部門與一個負責人之間可能存在一對一的聯系。

• 一對多（1:N）：這種聯系表示一個實體與多個實體相關，但每個相關實體只與一個實體相關。例如，一個教師可以教授多門課程，但每門課程只能由一個教師教授。

• 多對多（M:N）：這種聯系表示兩個實體之間存在多對多的關系，即每個實體可以與多個實體相關，同時每個相關實體也可以與多個實體相關。例如，學生可以選修多門課程，同時每門課程也可以被多個選修。

在設計數據庫時，正確地識別實體、屬性和聯系，以及它們之間的類型，對于創建一個結構良好、能夠有效存儲和檢索數據的數據庫至關重要。

關系數據庫相關概念

1. 庫（Database）： 庫是一個存儲數據的容器，它可以包含多個數據庫。在某些管理系統中，庫等同于數據庫。

2. 表（Table）： 表是數據庫中的一個表格，由行和列組成。表是存儲數據的主要結構，每個表通常對應一個實體類型。表的列名稱為屬性，而表的行名稱為記錄。

3. 行（Record）： 行也稱為記錄，它是表中的一個單元，代表表中的一個具體實例。每一行包含了一組屬性值，這些值共同描述了一個實體的狀態。

4. 列（Column）： 列是表中的一列，它代表了表的一個屬性。每列都有一個數據類型，用于定義存儲在其中的數據的種類和格式。

5. 字段（Field）： 字段通常指的是表中的行與列的交叉點（單元格），它存儲了單個數據項。在數據庫中，每個字段都有其特定的數據類型和用途。

6. 數據（Data）： 數據是存儲在數據庫中的信息。它可以是文本、數字、日期、圖像、聲音等各種形式。數據是數據庫管理和操作的核心。

二、安裝

默認監聽端口號：3306/tcp

1、mariadb安裝

服務端程序： mariadb-server；客戶端程序：mariadb

yum install -y mariadb-server mariadb

2、mysql安裝

服務端程序：mysql-server；客戶端程序：mysql；

mysql的安全系數要比mariadb的高

[root@mysql ~]# yum install -y  mysqld-server mysql	
##編譯安裝，需要cmake環境
關閉防火墻，設置上下文
[root@houfan ~]# systemctl disable --now firewalld
[root@houfan ~]# setenforce 0

3、啟動并開機自啟

[root@mysql ~]# systemctl enable --now mysqld.service 

4、本地連接（本地登錄）

[root@mysql ~]# mysql
Welcome to the mysqld monitor.  Commands end with ; or \g.
Your mysqld connection id is 2
Server version: 5.5.68-mysqld mysqld ServerCopyright (c) 2000, 2018, Oracle, mysqld Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.???????
mariadb 安裝會顯示
mysqld [(none)]> 
##[(none)]：表示當前登錄用戶選擇的數據庫的“空”，沒有在任何庫中
##使用“exit”命令可以退出數據庫登錄

注意：mysqld默認的管理用戶是“root”，與操作系統的“root”沒有任何關系！！！root用戶默認沒有密碼！！

三、mysql數據庫配置與命令

yum安裝后生成的目錄

[root@mysql mysql]# rpm -ql mysqld-server
`/etc/logrotate.d/mysqld     #配置文件
`/etc/my.cnf.d/server.cnf
/usr/bin/innochecksum    #相關命令
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_convert_table_format
/usr/bin/mysql_fix_extensions
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_setpermission
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysql_zap
/usr/bin/mysqlbug
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqld_safe_helper
/usr/bin/mysqldumpslow
/usr/bin/mysqlhotcopy
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
`/usr/lib/systemd/system/mysqld.service #運行腳本
/usr/lib/tmpfiles.d/mysqld.conf
/usr/lib64/mysql/INFO_BIN
/usr/lib64/mysql/INFO_SRC
/usr/lib64/mysql/mysqlbug
/usr/lib64/mysql/plugin
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/auth_0x0100.so
/usr/lib64/mysql/plugin/auth_pam.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/auth_test_plugin.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/dialog_examples.so
/usr/lib64/mysql/plugin/ha_innodb.so
/usr/lib64/mysql/plugin/ha_sphinx.so
/usr/lib64/mysql/plugin/handlersocket.so
/usr/lib64/mysql/plugin/libdaemon_example.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/qa_auth_client.so
/usr/lib64/mysql/plugin/qa_auth_interface.so
/usr/lib64/mysql/plugin/qa_auth_server.so
/usr/lib64/mysql/plugin/query_cache_info.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/server_audit.so
/usr/lib64/mysql/plugin/sphinx.so
/usr/lib64/mysql/plugin/sql_errlog.so
/usr/libexec/mysqld-prepare-db-dir
/usr/libexec/mysqld-wait-ready
`/usr/libexec/mysqld  #myslq 啟動命令
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_convert_table_format.1.gz
/usr/share/man/man1/mysql_fix_extensions.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_plugin.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_setpermission.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysql_zap.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysqlbug.1.gz
/usr/share/man/man1/mysqlcheck.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqlhotcopy.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysqltest.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man1/replace.1.gz
/usr/share/man/man1/resolve_stack_dump.1.gz
/usr/share/man/man1/resolveip.1.gz
/usr/share/man/man8/mysqld.8.gz
/usr/share/mysql/README.mysql-cnf
/usr/share/mysql/errmsg-utf8.txt
/usr/share/mysql/fill_help_tables.sql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/mysql_performance_tables.sql
/usr/share/mysql/mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables_data.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/var/lib/mysql  ##mysqld數據庫的數據存儲目錄
/var/log/mysqld
/var/log/mysqld/mysqld.log
/var/run/mysqld

mysql服務器的啟動腳本

啟動mysql首先初始化

#如何設置MySQL成為系統服務
[root@mysql ~]# cat /usr/lib/systemd/system/mysqld.service[Unit]
Description=mysqld database server
After=syslog.target
After=network.target[Service]
Type=simple
User=mysql  ##程序運行用戶
Group=mysqlExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
"/usr/lib/systemd/system/mysqld.service" 48L, 1697C                                                                39,1          55%
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F# For example, if you want to increase mysqld's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mysqld.service.d/limits.conf" containing:
#       [Service]
#       LimitNOFILE=10000# Note: /usr/lib/... is recommended in the .include line though /lib/... 
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload[Unit]
Description=mysqld database server
After=syslog.target
After=network.target[Service]
Type=simple
User=mysql
Group=mysqlExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300# Place temp files in a secure directory, not /tmp
PrivateTmp=true[Install]
WantedBy=multi-user.target

啟動之后mysql生成文件目錄

root@houfan mysql]# systemctl start mysqld
[root@houfan mysql]# lsauto.cnf             ibdata1              mysqlx.sock.lockbinlog.000001        ibtmp1               performance_schemabinlog.index        '#innodb_redo'        private_key.pemca-key.pem          '#innodb_temp'        public_key.pemca.pem               mysql                server-cert.pemclient-cert.pem      mysql.ibd            server-key.pemclient-key.pem       mysql.sock           sys
'#ib_16384_0.dblwr'   mysql.sock.lock      undo_001
'#ib_16384_1.dblwr'   mysql_upgrade_info   undo_002ib_buffer_pool       mysqlx.sock
#information schema    提供數據庫元數據訪問(如表、列信息)
#mysql                 存儲用戶權限、系統配置等核心數據
#performance schema    收集數據庫性能指標和監控數據
#sys				   簡化 performance_schema的視圖工具集

數據存儲目錄的權限

[root@mysql mysql]# ls -ld /var/lib/mysql/
drwxr-xr-x 5 mysql mysql 177 1月  31 10:16 /var/lib/mysql/

服務啟動時讀取的默認配置文件

[root@mysql mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql  ##定義數據存儲目錄
socket=/var/lib/mysql/mysql.sock  ##定義連接的網絡接口文件
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]
log-error=/var/log/mysqld/mysqld.log  ##程序運行的錯誤日志
pid-file=/var/run/mysqld/mysqld.pid  ##程序運行的PID文件#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

mysqld操作命令

[root@mysql ~]# ls -l /usr/bin/mysql*
-rwxr-xr-x 1 root root 3543584 10月  2 2020 /usr/bin/mysql
-rwxr-xr-x 1 root root  111971 10月  2 2020 /usr/bin/mysqlaccess
-rwxr-xr-x 1 root root 3096720 10月  2 2020 /usr/bin/mysqladmin
-rwxr-xr-x 1 root root 3259880 10月  2 2020 /usr/bin/mysqlbinlog#MySQL二進制日志
lrwxrwxrwx 1 root root      26 1月  31 10:15 /usr/bin/mysqlbug -> /etc/alternatives/mysqlbug
-rwxr-xr-x 1 root root 3094016 10月  2 2020 /usr/bin/mysqlcheck
-rwxr-xr-x 1 root root    4215 10月  2 2020 /usr/bin/mysql_convert_table_format
-rwxr-xr-x 1 root root   24116 10月  2 2020 /usr/bin/mysqld_multi
-rwxr-xr-x 1 root root   27105 10月  2 2020 /usr/bin/mysqld_safe#安全
-rwxr-xr-x 1 root root 2887144 10月  2 2020 /usr/bin/mysqld_safe_helper
-rwxr-xr-x 1 root root 3176704 10月  2 2020 /usr/bin/mysqldump
-rwxr-xr-x 1 root root    7876 10月  2 2020 /usr/bin/mysqldumpslow
-rwxr-xr-x 1 root root    3288 10月  2 2020 /usr/bin/mysql_find_rows
-rwxr-xr-x 1 root root    1246 10月  2 2020 /usr/bin/mysql_fix_extensions
-rwxr-xr-x 1 root root   34942 10月  2 2020 /usr/bin/mysqlhotcopy
-rwxr-xr-x 1 root root 3088968 10月  2 2020 /usr/bin/mysqlimport
-rwxr-xr-x 1 root root   16701 10月  2 2020 /usr/bin/mysql_install_db
-rwxr-xr-x 1 root root 2926112 10月  2 2020 /usr/bin/mysql_plugin
-rwxr-xr-x 1 root root   12126 10月  2 2020 /usr/bin/mysql_secure_installation#密碼加密方式
-rwxr-xr-x 1 root root   17464 10月  2 2020 /usr/bin/mysql_setpermission
-rwxr-xr-x 1 root root 3087504 10月  2 2020 /usr/bin/mysqlshow
-rwxr-xr-x 1 root root 3106896 10月  2 2020 /usr/bin/mysqlslap
-rwxr-xr-x 1 root root 3449016 10月  2 2020 /usr/bin/mysqltest
-rwxr-xr-x 1 root root 2921448 10月  2 2020 /usr/bin/mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root 2998448 10月  2 2020 /usr/bin/mysql_upgrade
-rwxr-xr-x 1 root root 2912968 10月  2 2020 /usr/bin/mysql_waitpid
-rwxr-xr-x 1 root root    3856 10月  2 2020 /usr/bin/mysql_zap

mysql命令

mysql數據庫登錄的命令行工具，本地登錄時且root用戶沒有密碼，輸入mysql，等效于”mysql -uroot -hlocalhost -P3306“

語法

mysql [options] db_name

常用選項

-u：指定登錄用戶

-p：指定用戶密碼

-h：指定登錄數據庫的IP或者域名

-P：指定登錄數據庫的端口號 #一般使用10000以后的端口號

-e：能夠在終端執行數據庫指令

使用案例

[root@mysql ~]# mysql -uroot -hlocalhost -P3306 
[root@mysql ~]# mysql -p123.com -e "show databases"   ##直接在外部執行查看數據庫的命令
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+##指定登錄的數據庫，只能是一個
[root@mysql mysql]# mysql -p123.com mysql   
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -AWelcome to the mysqld monitor.  Commands end with ; or \g.
Your mysqld connection id is 14
Server version: 5.5.68-mysqld mysqld ServerCopyright (c) 2000, 2018, Oracle, mysqld Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysqld [mysql]> 

mysqladmin命令

超級管理命令

語法

mysqladmin [options] command [command-arg] [command [command-arg]] ...

設置root用戶密碼

yum安裝沒有密碼；編譯安裝自帶密碼

[root@mysql ~]# mysqladmin -uroot password '123.com'
#更改密碼  -p 指定新密碼
[root@houfan ~]# mysqladmin -uroot -p password '1234.com'
Enter password: 

使用密碼登錄

[root@mysql ~]# mysql -uroot -p123.com -h127.0.0.1 -P3306

四、默認數據庫

• information_schema ：信息數據庫，存儲所有的庫、表、列的名稱，任意可登錄數據庫的用戶都可讀；sql注入

表名	作用
CHARACTER_SETS	字符集信息表。用于查看和管理MySQL數據庫中的字符集信息。
CLIENT_STATISTICS	客戶端統計信息表。用于查看和管理客戶端的統計信息，例如連接數、請求次數等。
COLLATIONS	排序規則信息表。用于查看和管理MySQL數據庫中的排序規則信息。
COLLATION_CHARACTER_SET_APPLICABILITY	字符集和排序規則的適用性表。用于查看和管理字符集和排序規則之間的適用關系。
COLUMNS	數據表的列信息表。用于查看和管理MySQL數據庫中數據表的列信息。
COLUMN_PRIVILEGES	列級別的權限信息表。用于查看和管理列級別的權限信息，例如用戶對各個列的SELECT、INSERT、UPDATE等權限。
ENGINES	存儲引擎信息表。用于查看和管理MySQL數據庫中支持的存儲引擎信息。
EVENTS	定時事件信息表。用于查看和管理MySQL數據庫中的定時事件信息。
FILES	文件信息表。用于查看和管理MySQL數據庫服務器上文件的信息。
GLOBAL_STATUS	全局狀態信息表。用于查看和管理MySQL數據庫服務器的全局狀態信息。
GLOBAL_VARIABLES	全局變量信息表。用于查看和管理MySQL數據庫服務器的全局變量信息。
INDEX_STATISTICS	索引統計信息表。用于查看和管理MySQL數據庫中索引的統計信息。
KEY_CACHES	鍵緩存表。用于查看和管理MySQL數據庫中的鍵緩存信息。
KEY_COLUMN_USAGE	鍵列使用表。用于查看和管理鍵列的使用情況。
PARAMETERS	參數表。用于查看和管理MySQL數據庫的參數信息，例如連接超時時間、最大連接數等。
PARTITIONS	分區表。用于查看和管理MySQL數據庫中的分區信息。
PLUGINS	插件表。用于查看和管理MySQL數據庫中的插件信息。
PROCESSLIST	進程列表表。用于查看當前正在運行的MySQL進程，包括連接的客戶端和正在執行的查詢等。
PROFILING	性能分析表。用于啟用性能分析，記錄和分析SQL查詢的性能數據。
REFERENTIAL_CONSTRAINTS	外鍵約束表。用于查看和管理外鍵約束的信息，包括外鍵列和參考列等。
ROUTINES	存儲過程和函數表。用于查看和管理MySQL數據庫中的存儲過程和函數的信息，包括創建時間、修改時間、函數名等。
SCHEMATA	架構表。用于查看和管理MySQL數據庫中的架構信息，包括架構名、架構下的表名等。
SCHEMA_PRIVILEGES	架構權限表。用于查看和管理架構下的權限信息，包括用戶對架構下表的SELECT、INSERT、UPDATE等權限。
SESSION_STATUS	會話狀態信息表。用于查看和管理當前會話的狀態信息，例如會話的連接時間、查詢時間等。
SESSION_VARIABLES	會話變量表。用于查看和管理當前會話的變量信息，例如會話的最大連接數、最大內存使用量等。
STATISTICS	統計信息表。用于查看和管理MySQL數據庫中表的統計信息，包括表的行數、平均行大小等。
TABLES	數據表信息表。用于查看和管理MySQL數據庫中的數據表信息，包括表名、引擎類型等。
TABLESPACES	存儲空間表。用于查看和管理MySQL數據庫中的存儲空間信息，包括存儲空間名、大小等。
TABLE_CONSTRAINTS	表約束信息表。用于查看和管理表的約束信息，包括主鍵約束、外鍵約束等。
TABLE_PRIVILEGES	表級別的權限信息表。用于查看和管理表級別的權限信息，例如用戶對各個表的SELECT、INSERT、UPDATE等權限。
INNODB_CMPMEM_RESET	InnoDB 內存比較器重置表。此表用于記錄 InnoDB 內存比較器（comparison memory）的清除操作。
INNODB_RSEG	InnoDB 重做段表此表用于記錄 InnoDB 數據文件的重做段信息。
INNODB_UNDO_LOGS	InnoDB 撤銷日志表。此表用于記錄 InnoDB 撤銷操作的日志信息。
INNODB_CMPMEM	InnoDB 內存比較器表。此表用于記錄 InnoDB 內存比較器的分配和使用情況。
INNODB_SYS_TABLESTATS	InnoDB 系統表統計信息表。此表用于記錄 InnoDB 系統表的統計信息，如數據量、碎片率等。
INNODB_LOCK_WAITS	InnoDB 鎖等待信息表。此表記錄 InnoDB 鎖等待的情況，包括等待鎖的線程 ID、等待時間等。
INNODB_INDEX_STATS	InnoDB 索引統計信息表。此表用于記錄 InnoDB 索引的統計信息，如索引大小、索引列的數據類型等。
INNODB_CMP	InnoDB 比較器表。此表用于記錄 InnoDB 比較器的分配和使用情況。
INNODB_CHANGED_PAGES	InnoDB 更改頁表。此表用于記錄 InnoDB 數據文件中已更改的頁的信息。
INNODB_BUFFER_POOL_PAGES	InnoDB 緩沖池頁表。此表用于記錄 InnoDB 緩沖池中每個頁的信息，包括頁的類型、頁的狀態等。
INNODB_TRX	InnoDB 事務表。此表用于記錄 InnoDB 事務的信息，如事務 ID、事務狀態等。
INNODB_BUFFER_POOL_PAGES_INDEX	InnoDB 緩沖池頁索引表。此表用于記錄 InnoDB 緩沖池中每個頁的索引信息。
INNODB_LOCKS	InnoDB 鎖表。此表用于記錄 InnoDB 鎖的信息，如鎖的類型、鎖的持有者等。
INNODB_BUFFER_PAGE_LRU	InnoDB 緩沖池頁 LRU 表。此表用于記錄 InnoDB 緩沖池中每個頁的最近最少使用（Least Recently Used，LRU）信息。
INNODB_SYS_TABLES	InnoDB 系統表信息表。此表用于記錄 InnoDB 系統表的信息，如表名、表狀態等。
INNODB_SYS_FIELDS	InnoDB 系統字段信息表。此表用于記錄 InnoDB 系統表中每個字段的信息，如字段名、字段類型等。
INNODB_SYS_COLUMNS	InnoDB 系統列信息表。此表用于記錄 InnoDB 系統表中每個列的信息，如列名、列類型、列長度等。
INNODB_SYS_STATS	InnoDB 系統統計信息表。此表用于記錄 InnoDB 系統統計信息，如表數量、數據量等。
INNODB_SYS_FOREIGN	InnoDB 系統外鍵信息表。此表用于記錄 InnoDB 系統表中外鍵的信息，如外鍵約束條件等。
INNODB_SYS_INDEXES	InnoDB 系統索引信息表。此表用于記錄 InnoDB 系統表中索引的信息，如索引名、索引類型等。

mysql

• mysql ：主數據庫，mysqld運行的必須數據庫，用戶與配置信息

表名稱	作用
columns_priv	保存了每個表的列級別的權限信息，包括用戶對各個列的SELECT、INSERT、UPDATE、REFERENCES等權限。
db	保存了每個數據庫的權限信息，包括用戶對每個數據庫的CREATE、ALTER、DROP等權限。
event	保存了MySQL中的事件信息，包括事件的名稱、執行時間、執行語句等。
func	保存了用戶定義的存儲函數的信息，包括函數的名稱、參數、返回類型等。
general_log	記錄了MySQL服務器上所有的日志操作，包括查詢、連接、錯誤日志等。
help_category	保存了MySQL幫助文檔中的分類信息，用于幫助查找和瀏覽文檔。
help_keyword	保存了MySQL幫助文檔的關鍵字信息，用于快速搜索和查找文檔。
help_relation	保存了MySQL幫助文檔中關鍵字之間的關系信息，用于幫助構建文檔的結構。
help_topic	保存了MySQL幫助文檔的具體內容信息，包括每個主題的標題、內容等。
host	保存了MySQL服務器上的主機信息，包括主機名、IP地址、連接權限等。
ndb_binlog_index	保存了使用NDB存儲引擎的MySQL服務器上的二進制日志索引信息。
plugin	保存了MySQL服務器上安裝的插件信息。
proc	保存了用戶定義的存儲過程的信息，包括過程的名稱、參數、語句等。
procs_priv	保存了用戶對存儲過程的訪問權限信息。
proxies_priv	保存了MySQL服務器上的代理用戶的權限信息。
servers	實驗性表，保存了MySQL服務器的外部服務器和復制配置信息。
slow_log	記錄了MySQL服務器上執行時間超過默認閾值的慢查詢日志。
tables_priv	保存了用戶對表的訪問權限信息。
time_zone	保存了MySQL服務器上的時區信息。
time_zone_leap_second	保存了時區閏秒的信息。
time_zone_name	保存了時區的名稱和相關信息。
time_zone_transition	保存了時區的變化規則和信息。
time_zone_transition_type	保存了時區變化類型的信息。
user	保存了MySQL服務器上的用戶賬號信息，包括用戶名、密碼、權限等。

• performance_schema：性能數據庫，存儲mysqld的資源使用、安全策略權限配置信息

表名	作用
cond_instances	條件實例表，該表用于存儲各種條件或鎖定的實例信息
events_waits_current	當前等待事件表，記錄了當前正在等待某個事件發生的線程或會話信息
events_waits_history	歷史等待事件表，記錄了過去一段時間內等待事件的信息，包括等待事件的類型、等待時間等
events_waits_history_long	長期等待事件表，記錄了長時間等待事件的信息，包括等待事件的類型、等待時間、等待時長等
events_waits_summary_by_instance	按實例總結等待事件表，提供了按實例總結的等待事件統計信息
events_waits_summary_by_thread_by_event_name	按線程和事件總結等待事件表，提供了按線程和特定事件總結的等待事件統計信息
events_waits_summary_global_by_event_name	按全局和事件總結等待事件表，提供了按全局和特定事件總結的等待事件統計信息
file_instances	文件實例表，記錄了數據庫中各個文件的信息，包括文件路徑、文件大小等
file_summary_by_event_name	按事件名總結文件表，提供了按特定事件名總結的文件統計信息
file_summary_by_instance	按實例總結文件表，提供了按實例總結的文件統計信息
mutex_instances	互斥體實例表，記錄了數據庫中各個互斥體的信息，包括互斥體的名稱、狀態等
performance_timers	性能計時器表，提供了數據庫性能的計時信息，包括執行時間、資源使用情況等
rwlock_instances	讀寫鎖實例表，記錄了數據庫中各個讀寫鎖的信息；多個人同步操作時會出現鎖，來分配優先級
setup_consumers	設置消費者表，記錄了數據庫設置的各種消費者信息
setup_instruments	設置儀器表，記錄了數據庫設置的各類性能指標信息
setup_timers	設置定時器表，記錄了數據庫設置的各類定時器信息
threads	線程表，記錄了數據庫中的各個線程信息，包括線程ID、線程狀態等

• sys庫

五、 SQL語言（背會）

• DDL：數據定義語言，對數據庫結構操作

create：創建（用戶，庫，表）

alter：改變 (改變表的結構)

drop：刪除

• DML：數據操作語言，對數據表的操作

insert：插入

update：更新

delete：刪除數據

• DCL：數據控制語言，針對用戶權限設置

grant：用戶賦權

revoke：移除用戶權限

• DQL：數據查詢語言，對數據表的操作

select：查詢

六、mysql數據類型

常用的數據類型有：

• 整型

• 浮點型

• BIT類型（大數據類型）

• 定點數

• 日期時間類型

• 字符串

• NULL類型

1、整型

整數類型	占用字節	無符號數的取值范圍	有符號數的取值范圍
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854774808~9223372036854774807

整數列的可選屬性有三個：

• M: 寬度(在0填充的時候才有意義，否則不需要指定)

• unsigned: 無符號類型(非負)

• zerofill: 0填充,(如果某列是zerofill，那么默認就是無符號)，如果指定了zerofill只是表示不夠M位時，用0在左邊填充，如果超過M位，只要不超過數據存儲范圍即可

在 int(M) 中，M 的值跟 int(M) 所占多少存儲空間并無任何關系。 int(3)、int(4)、int(8) 在磁盤上都是占用 4 bytes 的存儲空間。

各整數數據類型的使用場所

數據類型	應用場景
TINYINT	一般用于枚舉數據，比如系統設定取值范圍很小且固定的場景。
SMALLINT	可以用于較小范圍的統計數據，比如統計工廠的固定資產庫存數量等。
MEDIUMINT	用于較大整數的計算，比如車站每日的客流量等。
INT、INTEGER	取值范圍足夠大，一般情況下不用考慮超限問題，用得最多。比如商品編號。
BIGINT	只有當你處理特別巨大的整數時才會用到。比如雙十一的交易量、大型門戶網站點擊量、證券公司衍生產品持倉等。

2、浮點型

數據類型	字節數	取值范圍
FLOAT	4	-2^128~2 ^128，即-3.40E+38~+3.40E+38
DOUBLE	8	-2^1024~ 2^1024，即-1.79E+308~1.79E+308

• 當浮點數類型使用unsigned修飾無符號時，取值范圍將不包含負數。

• 浮點數的取值范圍是理論上的極限值，但根據不同的硬件或操作系統，實際范圍可能會小。

• 浮點數雖然取值范圍很大，但精度并不高。float類型的精度為6位或7位，double類型的精度大約為15位。

• 如果給定的數值超出精度，可能會導致給定的數值與實際保存的數值不一致，發生精度損失。

• 當一個數字的整數部分和小數部分加起來達到7位時，第7位就會進行四舍五入操作。

• 要避免使用“=”來判斷兩個浮點數是否相等，因為浮點數是不準確的，存在精度損失。

3、BIT類型（了解）

函數	函數用途
BIT(M)	存儲二進制數據
ASCll(M)	獲取M的ASCll值
BIN(M)	獲取M的二進制值
LENGTH(M)	獲取M的數字長度

• BIT數據類型可用來保存位字段值。BIT(M)類型允許存儲M位值。M范圍為1~64，默認為1。

• BIT其實就是存入二進制的值，類似010110。如果存入一個BIT類型的值，位數少于M值，則左補0。如果存入一個BIT類型的值，位數多于M值，MySQL的操作取決于此時有效的SQL模式：如果模式未設置，MySQL將值裁剪到范圍的相應端點，并保存裁減好的值。如果模式設置為traditional(“嚴格模式”)，超出范圍的值將被拒絕并提示錯誤，并且根據SQL標準插入會失敗。

• 對于位字段，直接使用SELECT命令將不會看到結果，可以用bin()或hex()函數進行讀取。

4、定點型

• DECIMAL在MySQL內部以字符串形式存放，比浮點數更精確。定點類型占M+2個字節

• DECIMAL(M,D)與浮點型一樣處理規則。M的取值范圍為0~65，D的取值范圍為0~30，而且必須<=M，超出范圍會報錯。

• DECIMAL如果指定精度時，默認的整數位是10，默認的小數位為0。

• NUMERIC等價于DECIMAL。

• 例如，DECIMAL(5,2)表示的取值范圍為-999.99~999.99。

5、日期時間類型

數據類型	字節	取值范圍	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	3	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	3	-838:59:59~838:59:59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

YEAR類型

YEAR類型用來表示年份，在所有的日期時間類型中所占用的存儲空間最小，只需要1個字節的存儲空間，格式為YYYY。

在MySQL中，可使用以下3種格式指定TEAR類型的值：

使用4位字符串或數字表示，為1901'2155或19012155。

例如，輸入2022或2022，插入到數據庫中的值均為2022.

使用兩位字符串表示，為00~99。

00 ~ '69的值會被轉換為2000~2069的YEAR值

70 ~ '99的值會被自動轉換為1970~1999的YEAR值

例如，輸入22，插入到數據表中的值為2022。

使用兩位數字表示，為1~99。

1 ~ 69的值會被轉換為2001~2069的YEAR值

70 ~ 99的值會被自動轉換為1970~1999的YEAR值

例如，輸入22，插入到數據表中的值為2022。

注意：

當使用YEAR類型時，一定要區分0和0。

數字格式的0表示的YEAR值為0000

字符串格式的0表示的YEAR值為2000

DATE類型

DATE類型用來表示日期值，不包含時間部分，需要 3個字節 的存儲空間，且其格式為 YYYY-MM-DD 。其中，YYYY表示年份，MM表示月份，DD表示日期。

在MySQL中，可以使用以下4種格式指定DATE類型的值：

以YYYY-MM-DD或者YYYYMMDD字符串格式表示。

以YY-MM-DD或者YYMMDD字符串格式表示。

以YY-MM-DD或者YYMMDD數字格式表示。

使用CURRENT_DATE或者NOW()輸入當前系統日期。

注意：

通過"SELECT CURRENT_DATE;"或者"SELECT NOW();"可查詢當前日期。

日期中的分隔符"-“，還可以使用”.“”,“”/"等符號來表示。

TIME類型

TIME類型用于表示時間值，它的顯示形式一般為HH:MM:SS，其中HH表示小時，MM表示分，SS表示秒。

在MySQL中，可以使用以下3種格式指定TIME類型的值：

以HHMMSS字符串或者HHMMSS數學格式表示。

以HH:MM:SS字符串格式表示。

使用CURRENT_TIME或NOW()輸入當前系統時間。

DATETIME類型

DATETIME類型在所有的日期時間類型中占用的存儲空間最大，總共需要 8 個字節的存儲空間，用來表示日期和時間，它的顯示形式為YYYY-MM-DD HH:MM:SS。

在MySQL中，可以使用以下4種格式指定DATETIME類型的值：

以YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS字符串格式表示的日期和時間，取值范圍為1000-01-01 00:00:00~9999-12-31-23-59-59。

以YY-MM-DD HH:MM:SS或YYMMDDHHMMSS字符串格式表示的日期和時間，

以YYYYMMDDHHMMSS或YYMMDDHHMMSS數字格式表示的日期和時間，

使用NOW()來輸入當前系統的日期和時間。

TIMESTAMP類型

TIMESTAMP（時間戳）類型用于表示日期和時間，需要4個字節的存儲空間，它的顯示形式與DATETIME類型的相同，但取值范圍比DATETIME類型的小。

TIMESTAMP類型與DATETIME類型的不同形式：

使用CURRENT_TIMESTAMP來輸入系統當前的日期和時間。

無任何輸入，或輸入NULL時，實際保存的是系統當前日期和時間。

6、字符串型

數據類型	類型說明
CHAR	固定長度字符串
VARCHAR（）	可變長度字符串
text	大文本數據
ENUM	枚舉類型
SET	字符串類型
BINARY	固定長度的二進制數據
VARBINARY	可變長度是二進制數據
BLOB	二進制大對象

CHAR和VARCHAR類型

CHAR和VARCHAR類型都是用來保存字符串數據，兩者不同的是，VARCHAR可以存儲可變長度的字符串數據。

字符串類型	特點	長度	長度范圍	占用的存儲空間
CHAR(M)	固定長度	M	0<=M<=255	M個字節
VARCHAR(M)	可變長度	M	0<=M<=65535	(實際長度 + 1) 個字節

TEXT類型

TEXT類型用于保存大文本數據，例如，文章內容，評論等比較長的文本。

數據類型	存儲范圍	存儲空間占用量	特點
TINYTEXT(tinytext)	0~2^8-1	文本長度+2	小文本，可變長度
TEXT(text)	0~2^16-1	文本長度+2	文本，可變長度
MEDIUMTEXT(mediumtext)	0~2^24-1	文本長度+3	中等文本，可變長度
LONGTEXT(longtext)	0~2^32-1	文本長度+4	大文本，可變長度

ENUM類型

ENUM類型又稱為枚舉類型，其定義格式為：

ENUM('值1','值2','值3','值4','值5',....,'值n')

1. ENUM類型的取值范圍需要在定義字段時進行指定。

2. 設置字段值時，ENUM類型只允許從成員中選取單個值，不能一次選取多個值。

3. 其所需要的存儲空間由定義ENUM類型時指定的成員個數決定。

   • 當ENUM類型包含1~255個成員時，需要1個字節的存儲空間

   • 當ENUM類型包含256~65535個成員時，需要2個字節的存儲空間

   • ENUM類型的成員個數的上限為65535個

SET類型

SET類型用于保存字符串對象，其定義格式與ENUM類型相似。

SET('值1','值2','值3','值4','值5',....,'值n')

1. 其所需要的存儲空間由定SET類型時指定的成員個數決定。

   SET類型包含的成員數	存儲空間
   1~8	1個字節
   9~16	2個字節
   17~24	3個字節
   25~32	4個字節
   33~64	8個字節

2. SET類型在存儲數據時成員個數越多，其占用的存儲空間越大。

3. SET類型在選取成員時，與ENUM類型不同，其可以一次選擇多個成員。

BINARY和VARBINARY類型

BINARY和VARBINARY類型類似于CHAR和VARCHAR，不同的是，它們所表示的是二進制數據。

類型	特點	長度	長度范圍	占用的存儲空間
BINARY(M)	固定長度	M	0<=M<=255	M個字節
VARBINARY(M)	可變長度	M	0<=M<=65535	(M+ 1) 個字節

BLOB類型

BLOB類型用于保存數據量比較大的二進制數據，如圖片，PDF文檔等。

數據類型	存儲范圍	占用空間
TINYBLOB	0~2^8 -1字節	len+1個字節
BLOB	0~2^16 -1字節（相當于64KB）	len+2個字節
MEDIUMBLOB	0~2^24 -1字節（相當于16MB）	len+3個字節
LONGBLOB	0~2^32 -1字節（相當于4GB）	len+4個字節

JSON數據類型

JSON（JavaScript Object Notation）是一種輕量級的數據交換格式 ，簡潔和清晰的層次結構使得 JSON 成為理想的數據交換語言。它易于人閱讀和編寫，同時也易于機器解析和生成，并有效地提升網絡傳輸效率。

JSON 可以將 JavaScript 對象中表示的一組數據轉換為字符串，然后就可以在網絡或者程序之間輕松地傳遞這個字符串，并在需要的時候將它還原為各編程語言所支持的數據格式。

在MySQL 5.7中，就已經支持JSON數據類型。在MySQL中，JSON類型常見的表示方式有2種，分別為JSON數組和JSON對象。

#JSON數組
['abc',10,null,true]
#JSON對象
{"k1":"value","k2":10}

7、特殊的NULL類型

• 所有的類型的值都可以是null，包括int、float等數據類型

• 空字符串””，不等于null，0也不等于null，false也不等于null

• 任何運算符,判斷符碰到NULL,都得NULL

• NULL的判斷只能用is null,is not null

• NULL 影響查詢速度,一般避免使值為NULL

七、數據庫操作命令

客戶端 → 連接器 → [查詢緩存] → 解析器 → 預處理器 → 優化器 → 執行引擎 → 存儲引擎 → 返回結果

庫操作命令

• 查看數據庫

show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

• 查看所在數據庫

select database();
mysql> select database();
+------------+
| database() |
+------------+
| db2        |
+------------+
1 row in set (0.00 sec)

• 查看當前登錄用戶

select user();
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

• 查看所有用戶和主機名

mysql> select user,host from mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| zhangsan         | 192.168.80.150 |
| lisi             | localhost      |
| mysql.infoschema | localhost      |
| mysql.session    | localhost      |
| mysql.sys        | localhost      |
| root             | localhost      |
+------------------+----------------+
6 rows in set (0.00 sec)

• 查看當前數據庫版本

select version();
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41    |
+-----------+
1 row in set (0.00 sec)

• 查看用戶權限列表

show privileges  [\G];
\G表示查看所有權限

• 查看指定用戶的權限

show grants for root@localhost\G;

• 切換數據庫

use databaseName[;]
mysql> use db1
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

• 創建數據庫

create database databaseName  [character set utf8];
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

• 刪除數據庫

drop database databaseName;
mysql> 
mysql> drop database db1;
Query OK, 0 rows affected (0.02 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

表結構操作命令

• 查看數據表，當前庫下沒有表時會提示錯誤（1046）

show tables;
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t2            |
+---------------+
1 row in set (0.00 sec)

• 查看表結構

desc（describe） tableName;
mysql> desc t2;
[root@mysqld ~]# mysql -e "desc mysql.user"
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int unsigned                      | NO   |     | 0                     |       |
| max_updates              | int unsigned                      | NO   |     | 0                     |       |
| max_connections          | int unsigned                      | NO   |     | 0                     |       |
| max_user_connections     | int unsigned                      | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint unsigned                 | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint unsigned                 | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

• 創建數據表

create table tableName(columnName（列名稱） dataType（數據類型）, ............);mysql> create table t1(id int,name varchar(50), sex varchar(50), phone int);
Query OK, 0 rows affected (0.03 sec)
varchar 定義字符串長度
int 定義整數類型
mysql> select * from t1; #只能看到數據，不能看到表結構
Empty set (0.02 sec)mysql> describe t1;   #查看表結構

• 刪除數據表

drop table tableName;

alter命令用法

語法

ALTER TABLE <表名> [修改選項]

常見用法

#增加列，默認在表的最后一列 
alter table 表名 ADD column 列名 數據類型；
#修改列名和列類型
alter table 表名 change column 舊列名 新列名 新數據類型；
#修改列的默認值：為某一列設置默認值，或者刪除該列的默認值
alter table 表名 alter column 列名 set DEFAULT 默認值;
alter table 表名 alter column 列名 drop default;#如果在插入數據時沒有指定 列 的值，MySQL 會將其設置為 null（前提是該列允許 null 值）
#修改列的數據類型：修改表的某一列的數據類型（但不修改列名）
alter table 表名 modify column 列名 新數據類型 [約束條件];
#刪除列：從表中刪除某一列
alter table 表名 drop column 列名;
#重命名表：將表的名字修改為新的名稱
rename table 舊表名 to 新表名;
#修改表的字符集：為表設置新的字符集
alter table 表名 character set 字符集名;
#修改表的校對規則：為表設置新的校對規則（Collation），用于控制字符串比較和排序的方式
COLLATE <校對規則名> > 

案例

• 創建測試表

create table stu_score(id tinyint, name varchar(50), score decimal(4,2), object varchar(50));

• 修改表名 rename table 舊表名 to 新表名;

mysql> alter table table3 rename to t3;
Query OK, 0 rows affected (0.03 sec)mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t2            |
| table3        |
+---------------+
2 rows in set (0.00 sec)

• 修改字段類型(修改字段相對位置) :alter table 表名 modify column 列名 新數據類型 ;

mysql> alter table t3 modify  column id int;
mysql> alter table t3 modify id tinyint;

• 修改字段默認值/是否為空/自動增長

#alter table tab_name modify field type not null/default ="未知"/auto_increment;
#自動增長
alter table t3 modify id int primary key;#將 id 列設置為主鍵
alter table t3 modify id int auto_increment;#表示將 id 列設置為自動遞增屬性

• 修改字段名/字段類型：alter table 表名 change column 舊列名 新列名 新數據類型；

#alter table tab_name change field  newfield newtype;
#修改字段類型
alter table t3 modify id int;
#修改字段名及類型
alter table t3 change column object subject varchar(50);
#通過alter modify 修改字段是否為null或者默認值!
alter table t3 modify column name varchar(50) not null default "未知";

• 添加字段：alter table 表名 ADD column 列名 數據類型；

#alter table tab_name add field type (first/before/after field1);
alter table t3 add column extra text;
#添加到第一列；first：將新字段插入表的第一列
alter table t3 add i int first; 
#添加到某一字段后面，在name字段后面 通過after添加sex varchar
alter table t3 add sex varchar(10) after name; 

• 刪除字段

#alter table tab_name drop field;
alter table t3 drop id;

表數據操作

插入數據

• 單行插入

mysql> insert t3 value(1,'zhangsan','nan','123456');

• 批量插入

mysql> insert t3 values(3,'lihua','nv',147258),(4,'wangyuan','nv',369258);#指定列名插入 into可以不寫
insert into t3 (name, phone, sex) values('張三', '11011011011', '男'),('王五', '12011011011', '女'),('里斯', '12011011099', '男'); 

注意：只書寫表名稱，表示全表插入，即，全字段插入！ 插入的數據數量與字段數量一定要相等！

where字句運算符

比較運算符

運算符	說明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的結果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的結果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范圍匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一個，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多個（包括 0 個）任意字符；_ 表示任意一個字符

邏輯運算符

運算符	說明
AND （&&）	多個條件必須都為 TRUE(1)，結果才是 TRUE(1)
OR （||）	任意一個條件為 TRUE(1), 結果為 TRUE(1)
NOT （！）	條件為 TRUE(1)，結果為 FALSE(0)

更新數據

• 全表更新：UPDATE 表名 SET 列名=新值;

mysql> update t3 set sex='nv';

• 條件更新：UPDATE 表名 SET 列名=新值 WHERE 條件;

mysql> update t3 set sex='nan' where name='zhangsan';
update stuS set sex='女' where name='鐵蛋' and subject='中國武術';

刪除數據

• 全表刪除

delete from t3;

• 條件刪除

delete from stuS where id=6;

查詢數據

select在開頭，limit在最后，順序不能亂，where在分組前使用

生成測試表

#表結構
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(50)  | YES  |     | NULL    |       |
| class | int(11)      | YES  |     | NULL    |       |
| score | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+mysql -p123 -e "create database if not exists jx2412; create table if not exists jx2412.sc(name varchar(50),class int,score int)"
for i in `cat 2409.txt` ;do echo "insert into jx2409.sc value('$i',$[$RANDOM % 7 + 1],$[$RANDOM % 100]);" > sc.sql;done
create database jx2409;
use jx2409;
create table sc(name varchar(50), class int, score decimal(5,2));
mysql jx2409 < sc.sql

• 全表查詢

select * from tableName;

• 去重查詢select distinct 列名1, 列名2, ... from 表名;

#distinctselect  distinct class from stu;
+-------+
| class |
+-------+
|     2 |
|     7 |
|     1 |
|     5 |
|     4 |
|     6 |
|     3 |
+-------+

• 統計查詢，非空數據：select count(disrinct 列名) from 表名;

#count()select count(distinct class) from stu;
+-----------------------+
| count(distinct class) |
+-----------------------+
|                     7 |
+-----------------------+

• 重新定義查詢結果表中的列名稱

#asselect count(*) [as] count from stu;
#改變結果表中的字段名稱
select name  姓名, class  班級, score  成績 from sc;
SELECT name AS 姓名, class AS 班級, score AS 成績 FROM sc;
+-----------+--------+--------+
| 姓名      | 班級   | 成績   |
+-----------+--------+--------+
| 魏嘉辰    |      2 |  98.00 |
| 陶沛宏    |      7 |  64.00 |

• 聚合函數查詢;常用函數包括 SUM、AVG、MAX、MIN

#sum()：計算和值
select sum(score) 總成績 from sc;
+-----------+
| 總成績    |
+-----------+
|   1422.00 |
+-----------+

#avg()：計算平均值
mysqld [jx2409]> select avg(score) 平均成績 from sc;
+--------------+
| 平均成績     |
+--------------+
|    50.785714 |
+--------------+

#max()：最大值
mysqld [jx2409]> select max(score) 最高成績 from sc;
+--------------+
| 最高成績     |
+--------------+
|        99.00 |

#min()：最小值
select min(score) 最低成績 from sc;
+--------------+
| 最低成績     |
+--------------+
|         0.00 |
+--------------+

• 分組查詢;select 列名, 聚合函數(列名) form 表名 group by 列名 ;

#group by
##每個班的總成績
select class,sum(score) from sc group by class;
+-------+------------+
| class | sum(score) |
+-------+------------+
|     1 |     151.00 |
|     2 |     201.00 |
|     3 |     261.00 |
|     4 |     199.00 |
|     5 |     174.00 |
|     6 |     149.00 |
|     7 |     287.00 |
+-------+------------+
##排除2班的每個班的總成績
select class,sum(score) from sc where class!=2 group by class;
+-------+------------+
| class | sum(score) |
+-------+------------+
|     1 |     151.00 |
|     3 |     261.00 |
|     4 |     199.00 |
|     5 |     174.00 |
|     6 |     149.00 |
|     7 |     287.00 |
+-------+------------+

#分組后的條件：having##查詢總成績大于200分的班級
select class,sum(score) from sc where class!=2 group by class having sum(score) > 200.00;
+-------+------------+
| class | sum(score) |
+-------+------------+
|     3 |     261.00 |
|     7 |     287.00 |
+-------+------------+

• 排序查詢;select 列名 from 表名 order by 列名 [ASC/DESC]; asc升序，desc降序

#order by
#升序
#select * from stu order by age [asc];
#查詢總成績的升序排列結果
select class,sum(score) from sc group by class order by sum(score);
+-------+------------+
| class | sum(score) |
+-------+------------+
|     6 |     149.00 |
|     1 |     151.00 |
|     5 |     174.00 |
|     4 |     199.00 |
|     2 |     201.00 |
|     3 |     261.00 |
|     7 |     287.00 |
+-------+------------+

#降序
#select * from stu order by age desc;
#查詢總成績的降序排列結果
select class,sum(score) from sc group by class order by sum(score) desc;
+-------+------------+
| class | sum(score) |
+-------+------------+
|     7 |     287.00 |
|     3 |     261.00 |
|     2 |     201.00 |
|     4 |     199.00 |
|     5 |     174.00 |
|     1 |     151.00 |
|     6 |     149.00 |
+-------+------------+

• 分頁查詢；select 列名 from 表名 limit n;

#limit  number#顯示前number行#select * from stu limit 2;
##查詢總成績第一名
select class,sum(score) from sc group by class order by sum(score) desc limit 1;
+-------+------------+
| class | sum(score) |
+-------+------------+
|     7 |     287.00 |
+-------+------------+#limit [startNumber,顯示的數據行數]#select * from stu limit 2,1;
#只查看總成績的第二名
select class,sum(score) from sc group by class order by sum(score) desc limit 1,1;
+-------+------------+
| class | sum(score) |
+-------+------------+
|     3 |     261.00 |
+-------+------------+

• 子查詢，在一個完整的查詢語句中，嵌套若干小查詢

#select name,score from stu where name in (select name from info);
#查詢在 class2 表中存在的學生姓名和成績
select name,score from sc where name in (select name from class2);
+-----------+-------+
| name      | score |
+-----------+-------+
| 魏嘉辰    | 98.00 |
| 丁睿      | 89.00 |
| 張毅      | 14.00 |
+-----------+-------+

• union聯合查詢，臨時添加內容

####字段個數必須相同
mysql> select * from sc  union select 1,2,3;#可做SQL注入
+-----------+-------+-------+
| name      | class | score |
+-----------+-------+-------+
| 俞亮      |     1 | 70.00 |
| 劉博實    |     4 | 54.00 |
| 劉振堯    |     6 | 73.00 |
| 張霖杰    |     6 | 16.00 |
| 陳金招    |     5 | 31.00 |
| 武光斌    |     3 | 99.00 |
| 張浩      |     1 | 70.00 |
| 李松旺    |     3 | 43.00 |
| 潘圳航    |     5 | 28.00 |
| 米博宇    |     7 | 88.00 |
| 李振輝    |     4 |  0.00 |
| 王晨宇    |     5 | 57.00 |
| 謝輝      |     2 | 84.00 |
| 1         |     2 |  3.00 |
+-----------+-------+-------+
14 rows in set (0.01 sec)

###
mysql> select * from sc  union select '鐵錘',2,45;
+-----------+-------+-------+
| name      | class | score |
+-----------+-------+-------+
| 俞亮      |     1 | 70.00 |
| 劉博實    |     4 | 54.00 |
| 劉振堯    |     6 | 73.00 |
| 張霖杰    |     6 | 16.00 |
| 陳金招    |     5 | 31.00 |
| 武光斌    |     3 | 99.00 |
| 張浩      |     1 | 70.00 |
| 李松旺    |     3 | 43.00 |
| 潘圳航    |     5 | 28.00 |
| 米博宇    |     7 | 88.00 |
| 李振輝    |     4 |  0.00 |
| 王晨宇    |     5 | 57.00 |
| 謝輝      |     2 | 84.00 |
| 鐵錘      |     2 | 45.00 |
+-----------+-------+-------+
14 rows in set (0.00 sec)###
select * from sc union (select 1,name,3 from info limit 1);

• 多表查詢

內連接查詢

隱式內連接：

#select A.name as name, A.school, B.job from B,A where A.name=B.name;#select A.name as name, A.school, B.job from B [inner] join A on A.name=B.name;select class2.id, class2.name, sc.class, sc.score from sc  join class2 on sc.name=class2.name;
+------+-----------+-------+-------+
| id   | name      | class | score |
+------+-----------+-------+-------+
|    1 | 魏嘉辰    |     2 | 98.00 |
|    2 | 丁睿      |     2 | 89.00 |
|    3 | 張毅      |     2 | 14.00 |
+------+-----------+-------+-------+

外連接查詢

• 左外連接 left [ outer] join

  select 列 from 表A left [outer] join 表B 條件；

#select A.name as nameA, A.school, B.name as nameB, B.job from B  left join  A on A.name=B.name;
select class2.id, class2.name, sc.class, sc.score from sc left join class2 on sc.name=class2.name;
+------+-----------+-------+-------+
| id   | name      | class | score |
+------+-----------+-------+-------+
|    1 | 魏嘉辰    |     2 | 98.00 |
|    2 | 丁睿      |     2 | 89.00 |
|    3 | 張毅      |     2 | 14.00 |
| NULL | NULL      |     7 | 64.00 |
| NULL | NULL      |     1 | 52.00 |
| NULL | NULL      |     7 | 99.00 |
| NULL | NULL      |     7 | 28.00 |
| NULL | NULL      |     5 | 91.00 |
| NULL | NULL      |     1 | 38.00 |
| NULL | NULL      |     4 | 29.00 |
| NULL | NULL      |     6 |  1.00 |
| NULL | NULL      |     3 | 74.00 |
| NULL | NULL      |     6 |  0.00 |
| NULL | NULL      |     3 | 25.00 |
| NULL | NULL      |     6 | 84.00 |
| NULL | NULL      |     1 |  1.00 |
| NULL | NULL      |     1 | 53.00 |
| NULL | NULL      |     4 | 84.00 |
| NULL | NULL      |     3 | 79.00 |
| NULL | NULL      |     3 | 30.00 |
| NULL | NULL      |     7 | 96.00 |
| NULL | NULL      |     6 | 64.00 |
| NULL | NULL      |     3 | 12.00 |
| NULL | NULL      |     5 | 78.00 |
| NULL | NULL      |     1 |  7.00 |
| NULL | NULL      |     5 |  5.00 |
| NULL | NULL      |     4 | 86.00 |
| NULL | NULL      |     3 | 41.00 |
+------+-----------+-------+-------+

• 右外連接 select 列 from 表A right [outer] join 表B 條件；

#select A.name as nameA, A.school, B.name as nameB, B.job from B  right join  A on A.name=B.name;
select class2.id, class2.name, sc.class, sc.score from sc right join class2 on sc.name=class2.name;
+------+-----------+-------+-------+
| id   | name      | class | score |
+------+-----------+-------+-------+
|    1 | 魏嘉辰    |     2 | 98.00 |
|    2 | 丁睿      |     2 | 89.00 |
|    3 | 張毅      |     2 | 14.00 |
|    4 | Rose      |  NULL |  NULL |
+------+-----------+-------+-------+

案例練習

1.1、統計每個學院老師數量

1.2、統計每個學院專業數量

1.3、統計計算機學院男生數量

1.4、統計每個學院年級數量

1.5、統計每個學院班級數量

2.1、2021級計算機科學與技術成績最高分姓名成績

2.2、2021級計算機科學與技術各個班的平均分

2.3、2023級會計學每個班總平均分

2.4、機械工程學院2023級總分第一名?

八、mysqld用戶權限管理

MySQL數據庫權限分類

MySQL 權限對照表

權限 (Privilege)	上下文 (Context)	注釋 (Comment)
Alter	表 (Tables)	修改表結構
Alter routine	函數、存儲過程 (Functions, Procedures)	修改或刪除存儲函數/存儲過程
Create	數據庫、表、索引 (Databases, Tables, Indexes)	創建新數據庫、表和索引
Create routine	數據庫 (Databases)	使用 CREATE FUNCTION/PROCEDURE 創建存儲過程和函數
Create temporary tables	數據庫 (Databases)	創建臨時表
Create view	表 (Tables)	創建視圖
Create user	服務器管理 (Server Admin)	創建新用戶
Delete	表 (Tables)	刪除表中現有數據
Drop	數據庫、表、視圖 (Databases, Tables, Views)	刪除數據庫、表和視圖
Event	服務器管理 (Server Admin)	創建、修改、刪除和執行事件
Execute	函數、存儲過程 (Functions, Procedures)	執行存儲過程和函數
File	文件訪問 (File access on server)	讀寫服務器文件系統
Grant option	數據庫、表、函數、存儲過程 (Databases, Tables, Functions, Procedures)	授予其他用戶你擁有的權限
Index	表 (Tables)	創建或刪除索引
Insert	表 (Tables)	向表中插入數據
Lock tables	數據庫 (Databases)	使用 LOCK TABLES 鎖定表（需與 SELECT 權限配合）
Process	服務器管理 (Server Admin)	查看當前正在執行的查詢的明文
Proxy	服務器管理 (Server Admin)	允許代理用戶身份
References	數據庫、表 (Databases, Tables)	在表之間創建外鍵約束
Reload	服務器管理 (Server Admin)	重新加載權限、刷新日志等
Replication client	服務器管理 (Server Admin)	查看從庫（Slave）或主庫（Master）的位置
Replication slave	服務器管理 (Server Admin)	從主庫讀取二進制日志事件
Select	表 (Tables)	查詢表中數據
Show databases	服務器管理 (Server Admin)	使用 SHOW DATABASES 查看所有數據庫
Show view	表 (Tables)	使用 SHOW CREATE VIE 查看視圖定義
Shutdown	服務器管理 (Server Admin)	關閉 MySQL 服務器
Super	服務器管理 (Server Admin)	執行高級操作（如 KILL 線程、設置全局變量、變更主從復制配置等）
Trigger	表 (Tables)	創建和使用觸發器
Create tablespace	服務器管理 (Server Admin)	創建/修改/刪除表空間
Update	表 (Tables)	更新表中現有數據
Usage	服務器管理 (Server Admin)	無權限（僅允許連接數據庫）

---

關鍵權限分類

1. 表級權限（如 Alter、Insert）：直接操作表結構和數據

2. 數據庫級權限（如 Create、Drop）：管理數據庫整體

3. 服務器管理權限（如 Shutdown、Super）：高風險操作，需嚴格限制

4. 高級功能權限（如 Replication、Trigger）：涉及數據同步和自動化邏輯

中文釋義

在MySQL中，權限控制是通過Grant和Revoke命令來管理用戶和角色的權限的。
- Alter：修改。允許用戶修改數據庫中的表結構。
- Alter routine：修改存儲過程或函數。允許用戶修改已創建的存儲過程或函數。
- Create：創建。允許用戶創建新的數據庫對象，如表、視圖、存儲過程等。
- Create routine：創建存儲過程或函數。允許用戶創建新的存儲過程或函數。
- Create temporary tables：創建臨時表。允許用戶創建臨時表，這些表在會話結束時會自動刪除。
- Create view：創建視圖。允許用戶創建新的視圖。
- Create user：創建用戶。允許用戶創建新的數據庫用戶賬戶。
- Delete：刪除。允許用戶刪除表中的數據。
- Drop：刪除。允許用戶刪除數據庫中的對象，如表、視圖等。
- Event：事件。允許用戶管理和修改事件調度器中的事件。
- Execute：執行。允許用戶執行存儲過程或函數。
- File：文件。允許用戶訪問數據庫服務器的文件系統。
- Grant option：授予選項。允許用戶將權限授予其他用戶。
- Index：索引。允許用戶在表上創建、修改或刪除索引。
- Insert：插入。允許用戶向表中插入數據。
- Lock tables：鎖定表。允許用戶鎖定數據庫中的表，以防止其他用戶同時訪問這些表。
- Process：進程。允許查看或殺死數據庫服務器上的進程。
- Proxy：代理。允許用戶作為代理登錄，代表其他用戶執行操作。
- References：引用。允許用戶創建外鍵約束。
- Reload：重新加載。允許用戶重新加載配置文件或刷新權限表。
- Replication client：復制客戶端。允許用戶從主服務器接收數據以進行復制。
- Replication slave：復制從機。允許用戶作為數據復制的從服務器。
- Select：選擇。允許用戶查詢表中的數據。
- Show databases：顯示數據庫。允許用戶查看用的數據庫列表。
- Show view：顯示視圖。允許用戶查看數據庫中的視圖定義。
- Shutdown：關閉。允許用戶關閉數據庫服務器。
- Super：超級。允許用戶所有的權限，并且可以不受限制地執行許多系統命令。
- Trigger：觸發器。允許用戶創建觸發器，以在特定事件發生時自動執行代碼。
- Create tablespace：創建表空間。允許用戶創建新的表空間，用于存儲數據庫對象。
- Update：更新。允許用戶更新表中的數據。
- Usage：使用。這個權限通常用于限制用戶只能查看自己的權限，不能進行任何實際的數據庫操作。

user_name	host_name	說明
zhx	198.51.100.166	zhx，只能從此ip連接
zhx	198.51.100.%	zhx，從192.168.115子網中的任何主機
zhx	%	zhx，任何主機可連

創建遠程管理用戶

create user webuser@'192.168.115.%' identified by '123.com';  -- 只創建的用戶默認具有USAGE權限！
-- 更改密碼
alter user jx@192.168.166.9 identified by '123';
-- 在mysql8之前的數據庫，grant命令運行的時候，發現用戶不存在，則隱式創建！-- 查看權限
show grants for root@localhost\G;-- 賦權 all表示所有權限 ； *.*代表所有庫的所有表
grant all on *.* to 'root'@'192.168.115.130';
-- 刷新權限
flush privileges;

創建遠程web服務數據庫的操作用戶

-- 對jx庫所有具有管理權 
grant all on jx.* to 'zhangsan'@'192.168.80.%' identified by '123.com';
flush privileges;
-- 張三用戶對表jx.info 有查詢權限
grant select on jx.info to 'zhangsan'@'192.168.80.%';

查看用戶權限

show grants for  'testuser'@'192.168.115.%';
mysqld [mysql]> show grants for webuser@'192.168.115.%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for webuser@192.168.115.%                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'192.168.115.%' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

刪除用戶權限

revoke all on 庫.表  from 'username'@'ipaddress';
flush privileges;

案例

-- 刪除用戶的連接權限
revoke usage on *.* from 'username'@'ipaddress';
-- 無法直接刪除，只能刪除賬戶

#本地登錄
mysql> create user 'zhangsan'@'localhost'
#來自某個主機登錄
mysql> create user 'zhangsan'@'192.168.80.150' identified by '123.com';
Query OK, 0 rows affected (0.02 sec)
#identified by
mysql> show grants for zhangsan@192.168.80.150;#指定zhangsan用戶登錄
+---------------------------------------------------+
| Grants for zhangsan@192.168.80.150                |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`192.168.80.150` |
+---------------------------------------------------+
1 row in set (0.00 sec)
#USAGE 允許登陸的權限

#更改張三的密碼
mysql> alter user zhangsan@192.168.80.150 identified by '123';
Query OK, 0 rows affected (0.02 sec)#使用遠程登錄張三用戶，啥也干不了
[root@localhost ~]# mysql -h192.168.80.129 -uzhangsan -p
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)mysql> create databass db1; #創建的db1庫失敗
ERROR 1044 (42000): Access denied for user 'zhangsan'@'192.168.80.150' to database 'db1'

九、mysqld數據庫備份

9.1 數據備份的重要性

• 備份的主要目的是災難恢復。

• 在生產環境中，數據的安全性至關重要。

• 任何數據的丟失都可能產生嚴重的后果。

9.2 造成數據丟失的原因

• 程序錯誤

• 人為操作錯誤

• 運算錯誤

• 磁盤故障

• 災難（如火災、地震）和盜竊

9.2 備份需要考慮的問題

1. 可以容忍丟失多長時間的數據；

2. 恢復數據要在多長時間內完成；

3. 恢復的時候是否需要持續提供服務；

4. 恢復的對象，是整個庫，多個表，還是單個庫，單個表。

9.4 備份類型

1、根據是否需要數據庫離線

• 冷備（cold backup）：需要關mysql服務，讀寫請求均不允許狀態下進行；

• 溫備（warm backup）：服務在線，但僅支持讀（select）請求，不允許寫請求；

• 熱備（hot backup）：備份的同時，業務不受影響。

注意：

這種類型的備份，取決于業務的需求，而不是備份工具

MyISAM不支持熱備，InnoDB支持熱備，但是需要專門的工具

1.1、常用備份工具

商業工具

• Navicat for MySQL

  • 功能特點：它提供了直觀的圖形化界面，方便用戶輕松地創建備份任務、設置備份計劃以及恢復數據。支持多種備份類型，如完整備份、增量備份等，還能對備份文件進行加密和壓縮，以確保數據的安全性和減小備份文件的大小。

  • 適用場景：適用于各種規模的企業和開發團隊，尤其是那些需要頻繁進行數據庫備份和恢復操作，且對操作的便捷性和可視化有較高要求的用戶。

• SQLyog Ultimate

  • 功能特點：具有強大的備份和恢復功能，支持定時備份，可以按照設定的時間周期自動執行備份任務。同時，它還提供了數據同步功能，能夠在不同的 MySQL 數據庫之間進行數據同步，方便數據遷移和數據一致性維護。

  • 適用場景：受 MySQL 開發者和數據庫管理員的歡迎，常用于開發環境和生產環境中的數據庫管理與備份工作，對于需要進行數據庫版本控制和數據遷移的場景也非常實用。

開源工具

• Percona XtraBackup

  • 功能特點：它是一款開源的熱備份工具，能夠在不停止 MySQL 服務的情況下進行備份，支持對 InnoDB 和 XtraDB 存儲引擎的備份和恢復，備份過程中不會阻塞數據庫的正常讀寫操作，從而保證了業務的連續性。此外，它還提供了增量備份和部分備份功能，可有效減少備份時間和備份文件的大小。

  • 適用場景：因其高效、靈活的特點，廣泛應用于各種對業務連續性要求較高的生產環境，特別適合大型數據庫和高并發應用場景下的備份需求 。

• MyDumper

  • 功能特點：是一款多線程的備份和恢復工具，能夠充分利用系統資源，大大提高備份和恢復的速度。它支持將數據庫備份為多個文件，便于在恢復時進行靈活的選擇和操作，同時還可以對備份文件進行壓縮，節省存儲空間。

  • 適用場景：適用于需要快速備份和恢復大型 MySQL 數據庫的場景，如互聯網公司的大規模數據存儲和處理環境，以及對備份效率有較高要求的企業級應用。

2、根據要備份的數據集合的范圍

• 完全備份：full backup，備份全部字符集（全部數據庫），每次備份都會進行完全備份，會導致備份文件占用大量的磁盤空間，并且有大量的重復數據，只適合第一次備份，不常用。

• 差異備份: incremental backup，要先進行一次完全備份，每次差異備份都會備份上一次完全備份后的數據，可能會出現備份的重復數據，導致占用大量的磁盤空間；

• 增量備份：differential backup，要先執行一次完全備份，每一次增量備份的數據都是備份在上一次完全備份或者上一次增量備份后的數據，不會出現重復數據，也不會占用額外的磁盤空間

建議的恢復策略

• 完全+增量+二進制日志

• 完全+差異+二進制日志

3、根據備份數據或文件

物理備份：直接備份數據文件

• 優點：備份和恢復操作都比較簡單，能夠支持兼容的mysql版本；恢復速度快，屬于文件系統級別的。

• 建議：不要假設備份一定可用，要測試 mysql>check tables；檢測表是否可用。

邏輯備份: 備份表中的數據和代碼

• 優點：恢復簡單；備份的結果為ASCII文件，可以編輯；與存儲引擎無關；可以通過網絡備份和恢復；

• 缺點：備份或恢復都需要mysql服務器進程參與；備份結果占據更多的空間；浮點數可能會丟失精度；還原之后，縮影需要重建

9.5 常見的備份方法

1、物理冷備（完全備份）

備份時數據庫處于關閉狀態，直接打包數據庫文件

備份速度快，恢復時也是最簡單的

2、專用備份工具mysqldump或mysqlhotcopy （完全備份，邏輯備份）

mysqldump常用的邏輯備份工具 （導出為sql腳本）

mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表

啟用二進制日志進行增量備份 （增量備份）

進行增量備份，需要刷新二進制日志

3、第三方工具備份

免費的MySQL熱備份軟件Percona XtraBackup（阿里云的工具：dts，支持熱遷移）

9.6 備份案例

mysqldump工具

1、完整備份

所有數據庫的備份

備份

mysqldump --all-databases -uroot > all.sql
#mysql 設置密碼 必須-p 輸入密碼才能備份
[root@houfan ~]# mysqldump --all-databases -uroot -p > all.sql

恢復

mysql -uroot < all.sql

指定數據庫的備份

備份

mysqldump --databases jx -uroot > jx.sql
[root@houfan ~]# mysqldump --databases db2 -uroot -p > db2.sql

恢復

mysql -uroot < jx.sql

指定數據表的備份

備份

mysqldump  jx stu > jx.stu.sql
[root@houfan ~]# mysqldump db2 t2 -uroot -p > db2.t2.sql
[root@houfan ~]# mysql -uroot -p -e 'create database db3';
#備份的表可以在其他庫數據可恢復備份
[root@houfan ~]# mysql -uroot -p  db3 < db2.t2.sql
Enter password: 
[root@houfan ~]# mysql -uroot -p -e 'use db3;show tables'
Enter password: 
+---------------+
| Tables_in_db3 |
+---------------+
| t2 ? ? ? ? ?  |
+---------------+

恢復

mysql -uroot jx< jx.sql

2、增量備份

二進制備份

注意：在做二進制備份的時候現做一個完備，再進行二進制備份

配置過程

[root@houfan my.cnf.d]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
log-bin=mysql-bin
binlog_format="statement"
~ ?
?
#重啟MySQL  會出現 mysql-bin 文件
[root@houfan mysql]# systemctl restart mysqld
[root@houfan mysql]# ls
?
#刷新日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

查看日志文件內容

##在數據庫中查看日志文件及書寫位置
show master status;
######
mysqlbinlog mysql-bin.000001 |less

二進制備份的恢復

模擬數據丟失流程

flush logs; ###刷新日志文件，產生新的日志文件
?
-- 創建數據表及插入數據
mysql> insert t2 value(5,'lisi','nan','123456','dd');
Query OK, 1 row affected (0.00 sec)
flush logs;
?
-- 執行刪除操作
mysql> delete from t2 where id=5;
Query OK, 1 row affected (0.00 sec)
?
#查看二進制日志文件
?
# at 916
#250705 22:48:57 server id 1  end_log_pos 1036 CRC32 0x2b6ed943 ? ? ? ? Querythread_id=9 ? ?  exec_time=0 ? ? error_code=0
SET TIMESTAMP=1751770137/*!*/;
insert t2 value(5,'lisi','nan','123456','dd')
/*!*/;
# at 1036

數據恢復

基于位置

#基于開始位置
?
mysqlbinlog --start-position="245"  mysql-bin.000003 | mysql -uroot
?
#基于結束位置
?
mysqlbinlog --stop-position="632"  mysql-bin.000003 | mysql -uroot
?
#基于中間位置
?
mysqlbinlog --start-position="245" --stop-position="632"  mysql-bin.000003 | mysql -uroot

模擬操作

1、查看指定位置二進制文件內容

[root@houfan mysql]# mysqlbinlog --start-position=916 --stop-position=1067 mysql-bin.000002
# at 916
#250705 22:48:57 server id 1  end_log_pos 1036 CRC32 0x2b6ed943 ? ? ? ? Querythread_id=9 ? ?  exec_time=0 ? ? error_code=0
use `db3`/*!*/;
SET TIMESTAMP=1751770137/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
insert t2 value(5,'lisi','nan','123456','dd')
/*!*/;
# at 1036

2、對指定區域內的數據進行恢復

[root@houfan mysql]# mysqlbinlog --start-position=916 --stop-position=1067 mysql-bin.000002 | mysql -uroot -p
Enter password: 
#查看數據是否更新
mysql> select * from t2;
+------+----------+------+--------+-------+
| id ? | name ? ? | sex  | phone  | exter |
+------+----------+------+--------+-------+
| ? ?1 | llll ? ? | 男 ? | ? NULL | NULL  |
| ? ?3 | wangyuan | 女 ? | ? NULL | NULL  |
| ? ?4 | zhangsan | 男 ? | ? NULL | NULL  |
| ? ?5 | lisi ? ? | nan  | 123456 | dd ?  |
+------+----------+------+--------+-------+
4 rows in set (0.00 sec)

基于時間

#基于開始時間
?
mysqlbinlog --start-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot
?
#基于結束時間
?
mysqlbinlog --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot
?
#基于中間時間
?
mysqlbinlog --start-datetime="2022-12-23 16:17:59" --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot

XtraBackup 8.0.35-33 使用指南

簡介

XtraBackup 8.0.35-33 是 Percona 提供的一個開源熱備份工具，用于 MySQL 8.0 數據庫的物理備份和恢復。它是 MySQL 企業級備份解決方案的重要組成部分。

安裝

在基于 RPM 的系統上安裝

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install percona-xtrabackup-80

在基于 Debian 的系統上安裝

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo percona-release enable-only tools release
sudo apt-get update
sudo apt-get install percona-xtrabackup-80

基本使用

1. 完整備份

xtrabackup --backup --target-dir=/path/to/backup --user=username --password=password

2. 準備備份（應用日志）

xtrabackup --prepare --target-dir=/path/to/backup

3. 恢復備份

xtrabackup --copy-back --target-dir=/path/to/backup

高級使用案例

案例1：增量備份

1. 首先進行完整備份：

xtrabackup --backup --target-dir=/backups/full --user=root --password=yourpassword

1. 進行第一次增量備份：

xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/full --user=root --password=yourpassword

1. 進行第二次增量備份：

xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1 --user=root --password=yourpassword

1. 準備完整備份：

xtrabackup --prepare --apply-log-only --target-dir=/backups/full

1. 應用第一個增量備份：

xtrabackup --prepare --apply-log-only --target-dir=/backups/full --incremental-dir=/backups/inc1

1. 應用第二個增量備份：

xtrabackup --prepare --target-dir=/backups/full --incremental-dir=/backups/inc2

案例2：壓縮備份

xtrabackup --backup --compress --target-dir=/backups/compressed --user=root --password=yourpassword

解壓并準備備份：

xtrabackup --decompress --target-dir=/backups/compressed
xtrabackup --prepare --target-dir=/backups/compressed

案例3：并行備份和恢復

xtrabackup --backup --parallel=4 --target-dir=/backups/parallel --user=root --password=yourpassword

并行壓縮：

xtrabackup --backup --compress --compress-threads=4 --parallel=4 --target-dir=/backups/parallel_compressed --user=root --password=yourpassword

案例4：加密備份

1. 生成加密密鑰：

openssl rand -base64 24 > /backups/encryption_key

1. 創建加密備份：

xtrabackup --backup --target-dir=/backups/encrypted --encrypt=AES256 --encrypt-key-file=/backups/encryption_key --user=root --password=yourpassword

1. 解密備份：

xtrabackup --decrypt=AES256 --encrypt-key-file=/backups/encryption_key --target-dir=/backups/encrypted

案例5：流式備份

備份到單個壓縮文件：

xtrabackup --backup --stream=xbstream --user=root --password=yourpassword | gzip > /backups/backup.xbstream.gz

從流式備份恢復：

gunzip -c /backups/backup.xbstream.gz | xbstream -x -C /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql

常用選項說明

選項	描述
--backup	執行備份操作
--prepare	準備備份以進行恢復
--copy-back	將備份復制回原始位置
--target-dir	指定備份目標目錄
--user	MySQL 用戶名
--password	MySQL 密碼
--host	MySQL 主機地址
--port	MySQL 端口
--parallel	并行線程數
--compress	啟用壓縮
--compress-threads	壓縮線程數
--encrypt	加密算法
--encrypt-key-file	加密密鑰文件
--incremental-basedir	增量備份的基礎目錄
--incremental-dir	增量備份目錄
--stream	流式備份格式 (xbstream)

注意事項

1. 確保有足夠的磁盤空間存放備份文件

2. 備份期間數據庫負載可能會增加

3. 恢復前確保 MySQL 服務已停止

4. 恢復后可能需要調整文件權限

5. 定期測試備份的可用性

最佳實踐

1. 定期進行完整備份和增量備份

2. 備份文件存儲在不同于數據庫服務器的位置

3. 實施備份驗證流程

4. 記錄備份和恢復過程

5. 監控備份作業的狀態和持續時間

通過以上指南，您可以充分利用 XtraBackup 8.0.35-33 的強大功能來保護您的 MySQL 數據。

MyDumper 詳細使用指南

MyDumper 是一個高性能的 MySQL 邏輯備份工具，相比傳統的 mysqldump 具有并行備份、快照一致性等優勢。以下是 MyDumper 的詳細使用方式和案例。

安裝 MyDumper

Ubuntu/Debian 系統

sudo apt-get install mydumper

CentOS/RHEL 系統

sudo yum install mydumper

從源碼編譯

git clone https://github.com/mydumper/mydumper.git
cd mydumper
mkdir build
cd build
cmake ..
make
make install

基本使用

1. 完整備份數據庫

mydumper -u [username] -p [password] -h [host] -P [port] -o /backup/directory

2. 恢復數據庫

myloader -u [username] -p [password] -h [host] -P [port] -d /backup/directory

常用參數說明

參數	描述	示例
-u	用戶名	-u root
-p	密碼	-p secret
-h	主機地址	-h 127.0.0.1
-P	端口號	-P 3306
-o	輸出目錄	-o /backups
-d	恢復時指定備份目錄	-d /backups
-B	指定備份的數據庫	-B db1,db2
-T	指定備份的表	-T db1.table1
-t	線程數	-t 8
-c	壓縮輸出	-c
-v	詳細輸出	-v 3
-C	壓縮備份文件	-C
-e	備份表結構	-e
-r	分割表的行數	-r 100000
-F	按大小分割備份文件(MB)	-F 256
-s	一致性快照	-s

使用案例

案例1：備份單個數據庫

mydumper -u root -p password -h localhost -B mydatabase -o /backups/mydatabase

案例2：備份多個特定表

mydumper -u root -p password -h localhost -T db1.table1,db1.table2 -o /backups/tables

案例3：多線程備份（8個線程）

mydumper -u root -p password -h localhost -t 8 -o /backups/full

案例4：壓縮備份

mydumper -u root -p password -h localhost -c -o /backups/compressed

案例5：按100萬行分割表數據

mydumper -u root -p password -h localhost -r 1000000 -o /backups/split

案例6：備份數據庫結構（不備份數據）

mydumper -u root -p password -h localhost -e -o /backups/schema

案例7：恢復數據庫到不同名稱

myloader -u root -p password -h localhost -d /backups/full -B new_db_name

案例8：只恢復特定表

myloader -u root -p password -h localhost -d /backups/full -T db1.table1

高級功能

1. 一致性快照備份

mydumper -u root -p password -h localhost -s -o /backups/snapshot

2. 正則表達式過濾表

mydumper -u root -p password -h localhost -x '^sakila\.(actor|film)' -o /backups/regex

3. 備份時排除某些表

mydumper -u root -p password -h localhost -B sakila -X '^sakila\.film_text' -o /backups/exclude

4. 長查詢超時設置

mydumper -u root -p password -h localhost --long-query-retries=10 --long-query-retry-interval=30 -o /backups/timeout

5. 只備份數據不備份結構

mydumper -u root -p password -h localhost --no-schemas -o /backups/data_only

實際應用場景

場景1：生產環境每日備份

# 備份腳本
#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/backups/mysql/$DATE"
LOG_FILE="/var/log/mydumper_${DATE}.log"mydumper -u backup_user -p backup_pass -h 10.0.0.1 -P 3306 -t 4 -c -v 3 -o $BACKUP_DIR > $LOG_FILE 2>&1# 保留7天備份
find /backups/mysql/ -type d -mtime +7 -exec rm -rf {} \;

場景2：大數據表部分恢復

# 只恢復用戶表的前100萬條數據
myloader -u root -p password -h localhost -d /backups/full -T db.users --rows=1000000

場景3：跨服務器遷移數據庫

# 源服務器
mydumper -u root -p password -h source_host -B db_to_migrate -c -o /tmp/db_backup# 目標服務器
myloader -u root -p password -h target_host -d /tmp/db_backup

注意事項

1. 確保備份用戶有足夠的權限（至少需要 SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT 權限）

2. 大數據庫備份時考慮磁盤空間

3. 備份期間可能會對生產數據庫性能產生影響

4. 恢復前最好在測試環境驗證備份文件

5. 考慮使用--no-locks選項減少鎖表時間（但可能影響一致性）

性能優化建議

1. 根據服務器CPU核心數設置適當的線程數(-t)

2. 對大表使用-r參數分割備份文件

3. 使用-c或-C參數壓縮備份減少存儲空間

4. 考慮使用--no-locks選項減少鎖表時間（如果允許不一致）

5. 對于InnoDB表，使用-s參數獲取一致性快照

通過合理配置MyDumper，您可以高效地完成MySQL數據庫的備份和恢復工作，滿足各種業務場景的需求。

mysqlhotcopy 使用指南

mysqlhotcopy 是 MySQL 提供的一個 Perl 腳本工具，用于快速備份 MyISAM 和 ARCHIVE 表。它通過直接復制數據庫文件來實現快速備份，比邏輯備份工具如 mysqldump 更快，但只適用于特定存儲引擎。

安裝與準備

mysqlhotcopy 通常隨 MySQL 客戶端一起安裝，位于 MySQL 的 bin 目錄下。

檢查是否安裝

which mysqlhotcopy

確保依賴安裝（Perl 模塊）

sudo apt-get install perl-dbi perl-dbd-mysql ?# Debian/Ubuntu
sudo yum install perl-DBI perl-DBD-Mysql ? ? # CentOS/RHEL

基本語法

mysqlhotcopy [options] db_name [/path/to/backup/directory]

常用選項

選項	描述
--user=	MySQL 用戶名
--password=	MySQL 密碼
--host=	MySQL 主機
--port=	MySQL 端口
--socket=	MySQL socket 文件
--regexp=	使用正則表達式匹配數據庫
--allowold	不覆蓋現有備份，添加 _old 后綴
--keepold	不刪除被覆蓋的備份
--noindices	不備份索引文件
--method=	復制方法 (cp 或 scp)
--flushlog	備份后刷新日志
--resetmaster	備份后重置二進制日志
--resetslave	備份后重置從庫信息
--addtodest	添加而不是替換目標目錄
--dryrun	模擬執行，不實際復制

使用案例

案例1：備份單個數據庫

mysqlhotcopy --user=root --password=yourpassword mydatabase /backup/mysql

案例2：備份多個數據庫

mysqlhotcopy --user=root --password=yourpassword db1 db2 db3 /backup/mysql

案例3：使用正則表達式備份匹配的數據庫

mysqlhotcopy --user=root --password=yourpassword --regexp='^test_' /backup/mysql

案例4：保留舊備份

mysqlhotcopy --user=root --password=yourpassword --allowold --keepold mydatabase /backup/mysql

案例5：遠程備份到其他服務器

mysqlhotcopy --user=root --password=yourpassword --method=scp mydatabase user@remotehost:/remote/backup/dir

案例6：不備份索引文件

mysqlhotcopy --user=root --password=yourpassword --noindices mydatabase /backup/mysql

案例7：備份后刷新日志

mysqlhotcopy --user=root --password=yourpassword --flushlog mydatabase /backup/mysql

案例8：模擬運行（不實際備份）

mysqlhotcopy --user=root --password=yourpassword --dryrun mydatabase /backup/mysql

實際應用場景

場景1：生產環境每日備份腳本

#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/backup/mysql/$DATE"
LOG_FILE="/var/log/mysqlhotcopy_${DATE}.log"# 確保備份目錄存在
mkdir -p $BACKUP_DIR# 備份所有數據庫
mysqlhotcopy --user=backup_user --password=backup_pass \--allowold --keepold \--flushlog \--regexp='.*' \$BACKUP_DIR > $LOG_FILE 2>&1# 刪除7天前的備份
find /backup/mysql/ -type d -mtime +7 -exec rm -rf {} \;

場景2：備份特定表

# 備份 mydatabase 中的 table1 和 table2
mysqlhotcopy --user=root --password=yourpassword \mydatabase.table1 mydatabase.table2 \/backup/mysql

場景3：增量備份策略

#!/bin/bash
DATE=$(date +%Y%m%d)
FULL_BACKUP_DIR="/backup/mysql/full"
INC_BACKUP_DIR="/backup/mysql/inc_$DATE"
?
# 每周日做完整備份
if [ $(date +%u) -eq 7 ]; thenmysqlhotcopy --user=backup_user --password=backup_pass \--allowold --keepold \--flushlog \--regexp='.*' \$FULL_BACKUP_DIR
else# 其他日子做增量備份mysqlhotcopy --user=backup_user --password=backup_pass \--addtodest \--regexp='.*' \$INC_BACKUP_DIR
fi

恢復數據庫

mysqlhotcopy 的恢復是通過直接復制文件回原始位置實現的：

1. 停止 MySQL 服務

systemctl stop mysql

1. 復制備份文件到 MySQL 數據目錄

cp -R /backup/mysql/mydatabase /var/lib/mysql/

1. 確保文件權限正確

chown -R mysql:mysql /var/lib/mysql/mydatabase

1. 啟動 MySQL 服務

systemctl start mysql

注意事項

1. 存儲引擎限制：mysqlhotcopy 只適用于 MyISAM 和 ARCHIVE 表，不適用于 InnoDB

2. 鎖表：mysqlhotcopy 在備份期間會鎖定表，可能導致應用程序短暫阻塞

3. 備份一致性：對于正在寫入的表，備份可能不一致

4. 權限要求：

   • 執行用戶需要有讀取數據庫文件的權限

   • MySQL 用戶需要 SELECT、RELOAD、LOCK TABLES 權限

5. 備份完整性：備份后建議驗證備份文件的完整性

6. 版本兼容性：確保備份和恢復使用相同版本的 MySQL

性能優化建議

1. 在低峰期執行備份操作

2. 對于大型數據庫，考慮分批備份

3. 使用 --noindices 選項可以加快備份速度（但恢復時需要重建索引）

4. 考慮使用 --method=scp 直接備份到遠程服務器，減少本地磁盤IO

5. 對于頻繁更新的表，考慮結合 FLUSH TABLES 命令確保數據一致性

替代方案

由于 mysqlhotcopy 的局限性，現代 MySQL 環境通常使用以下替代方案：

• 對于 InnoDB 表：使用 Percona XtraBackup

• 邏輯備份：使用 mysqldump 或 mydumper

• 文件系統快照：LVM 快照或存儲設備快照功能

mysqlhotcopy 最適合用于 MyISAM 表的快速備份場景，特別是當數據庫大小適中且可以接受短暫鎖表的情況下。

附錄 A mysqld配置文件

# mysqld配置文件
?
# ----------------- 客戶端配置 -----------------
[client]
port = 3306 # 客戶端連接數據庫的端口，默認為3306
socket = /var/lib/mysql/mysql.sock # 服務器socket文件的路徑，默認為/var/lib/mysql/mysql.sock
?
# ----------------- MySQL客戶端工具配置 -----------------
[mysql]
default-character-set=utf8mb4 # 客戶端連接數據庫時的默認字符集，這里使用utf8mb4
?
# ----------------- 服務器配置 -----------------
[mysqld]
user = mysql # 運行數據庫服務器的系統用戶，默認為mysql
port = 3306 # 服務器監聽的端口，默認為3306
socket = /var/lib/mysql/mysql.sock # 服務器socket文件的路徑，默認為/var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid # 服務器進程ID保存的文件路徑，默認為/var/lib/mysql/mysql.pid
bind-address = 127.0.0.1 # 服務器綁定的IP地址，默認為本地回環地址
?
# ----------------- 日志配置 -----------------
log-error = /var/log/mysql/error.log # 錯誤日志文件路徑，默認為/var/log/mysql/error.log
slow-query-log = 1 # 是否啟用慢查詢日志，1表示啟用，0表示禁用，默認為禁用
slow-query-log-file = /var/log/mysql/slow.log # 慢查詢日志文件路徑，默認為/var/log/mysql/slow.log
?
# ----------------- 緩存配置 -----------------
query_cache_type = 1 # 查詢緩存類型，1表示啟用，0表示禁用，默認為禁用
query_cache_size = 32M # 查詢緩存大小，默認為32M
query_cache_limit = 2M # 查詢緩存單個查詢的最大緩存大小，默認為2M
?
# ----------------- 字符集配置 -----------------
character-set-server = utf8mb4 # 服務器使用的字符集，默認為utf8mb4
collation-server = utf8mb4_unicode_ci # 服務器使用的字符集排序規則，默認為utf8mb4_unicode_ci
?
# ----------------- 默認存儲引擎 -----------------
default-storage-engine = InnoDB # 默認使用的存儲引擎，默認為InnoDB
?
# ----------------- InnoDB配置 -----------------
innodb_buffer_pool_size = 256M # InnoDB緩沖池大小，默認為256M
innodb_flush_log_at_trx_commit = 2 # 日志刷新策略，2表示每秒刷新，默認為每次事務提交刷新
innodb_log_buffer_size = 8M # InnoDB日志緩沖區大小，默認為8M
innodb_file_per_table = 1 # 是否為每個InnoDB表使用單獨的表空間，1表示啟用，0表示禁用，默認為啟用
innodb_open_files = 400 # InnoDB打開的文件數量，默認為400
?
# ----------------- 網絡和連接配置 -----------------
max_connections = 1000 # 最大并發連接數，默認為1000
max_allowed_packet = 16M # 允許的最大數據包大小，默認為16M
skip_external_locking = 1 # 是否禁用外部鎖定，默認為是
?
# ----------------- 安全性配置 -----------------
secure-file-priv = /var/lib/mysql-files # 加載數據文件的安全目錄，默認為/var/lib/mysql-files
sql-mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # 服務器的SQL模式，默認為NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
?

附錄 B mysql常見內置函數

一、字符串函數

-- ASCII(str):返回字符串str的最左面字符的ASCII代碼值。如果str是空字符串，返回0。如果str是NULL，返回NULL
SELECT ASCII("dd");-- 100
SELECT ASCII("dc");-- 100-- CONCAT(str1,str2,...)：返回來自于參數連結的字符串。如果任何參數是NULL，返回NULL。可以有超過2個的參數。一個數字參數被變換為等價的字符串形式
select CONCAT('My', 'S', 'QL'); -- MySQL
select CONCAT('My', NULL, 'QL'); -- NULL
select CONCAT(14.3); -- 14.3-- LENGTH(str):返回字符串str的字節長度
select LENGTH('text'); -- 4
select LENGTH('字符串');-- 9-- CHAR_LENGTH(str):用于獲取字符串長度
select CHAR_LENGTH('text'); -- 4
select CHAR_LENGTH('字符串');-- 3-- LOCATE(substr,str):返回子串substr在字符串str第一個出現的位置，如果substr不是在str里面，返回0
select LOCATE('bar', 'foobarbar'); -- 4
select LOCATE('xbar', 'foobar'); -- 0-- INSTR(str,substr):返回子串substr在字符串str中的第一個出現的位置
select INSTR('foobarbar', 'bar'); -- 4
select INSTR('xbar', 'foobar'); -- 0-- LEFT(str,len)/RIGHT(str,len):返回字符串str的最左/右面len個字符
select LEFT('foobarbar', 5); -- fooba
select RIGHT('foobarbar', 4); -- rbar-- SUBSTRING(str,pos):從字符串str的起始位置pos返回一個子串
select SUBSTRING('foobarbar',5);-- arbar-- TRIM(str):返回字符串str，所有前綴或后綴被刪除了
select TRIM(' xbar ');-- xbar-- LTRIM(str)/RTRIM(str)：返回刪除了其前/后置空格字符的字符串str。
select LTRIM(' xbar');-- xbar
select RTRIM('xbar ');--xbar-- REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出現由字符串to_str代替
select REPLACE('xbar', 'x', 'bar');-- barbar-- REPEAT(str,count):返回由重復countTimes次的字符串str組成的一個字符串。如果count <= 0，返回一個空字符串。如果str或count是NULL，返回NULL
select REPEAT('bar', 3);-- barbarbar-- REVERSE(str):返回顛倒字符順序的字符串str。
select REVERSE('bar');-- rab-- INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len個字符長的子串由字符串newstr代替。 
select INSERT(whatareyou', 5, 3, is');-- whatisyou-- strcmp(str1,str2):用于比較兩個字符串的大小。左大于右時返回1，左等于右時返回0，，左小于于右時返回-1
SELECT strcmp('ab','ac');-- -1-- 大寫：upper(x),ucase(x)；小寫lower(x),lcase(x):字母大小寫轉換函數;
SELECT UPPER("abc");-- ABC
SELECT UCASE("abc");-- ABC
SELECT LOWER("ABC");-- abc
SELECT LCASE("ABC");-- abc-- find_in_set(str1,str2):返回字符串str1在str2中的位置，str2包含若干個以逗號分隔的字符串（可以把str2看出一個列表，元素是多個字符串，查找結果是str1在str2這個列表中的索引位置，從1開始）
SELECT FIND_IN_SET('abc','123,456,abc');-- 3-- field(str,str1,str2,str3…):與find_in_set類似，但str2由一個類似列表的字符串變成了多個字符串，返回str在str1,str2,str3…中的位置。
SELECT FIELD('abc','123','456','abc');-- 3-- elt(index,str1,str2,str3…):獲取指定位置的字符串
SELECT elt(3,'123','456','abc');-- abc

二、日期時間函數

-- curdate()/current_date():獲取當前日期
SELECT curdate();-- 2018-08-09
SELECT current_date();-- 2018-08-09-- curtime()/current_time()：獲取當前時間
SELECT curtime();-- 15:38:54
SELECT current_time();-- 15:38:54-- now():獲取當前日期時間
select now();-- 2018-08-09 15:40:09-- month(date),monthname(date):獲取日期月份
SELECT MONTH (now());-- 8
SELECT monthname(now());-- August-- week(date)：獲取日期周數
select week(now());-- 31-- year(date):獲取日期年數
select year(now());-- 2018-- hour(time):獲取時間時刻
select hour(now());-- 15-- minute(time):獲取時間分鐘數
select minute(now());-- 47-- DAYOFWEEK(date)/DAYNAME(date)/WEEKDAY(date):獲取時間星期數
select DAYOFWEEK(NOW());-- 5
select DAYNAME(now());-- Thursday
select WEEKDAY(now());-- 3-- DATE_ADD(date,INTERVAL expr type)/DATE_SUB(date,INTERVAL expr type):進行日期增加/減少的操作，可以精確到秒
SELECT '2018-08-09'+INTERVAL 1 DAY;-- 2018-08-10
SELECT '2018-08-09'-INTERVAL 1 SECOND;-- 2018-08-08 23:59:59
SELECT DATE_ADD('2018-08-08 23:59:59',INTERVAL 1 SECOND);-- 2018-08-09 00:00:00
SELECT DATE_SUB('2018-08-09 00:01:01',INTERVAL '1:1' MINUTE_SECOND);-- 2018-08-09 00:00:00-- date_format('time','format')/time_format(time,format):日期時間轉換為字符串
select date_format(now(), '%Y%m%d%H%i%s');-- 20180809160315
select time_format(now(),'%H:%i:%s');--16:03:15-- str_to_date(str, format)：字符串轉換為日期
select str_to_date('08.09.2018 16:06:30', '%m.%d.%Y %H:%i:%s');-- 2018-08-09 16:06:30-- makdedate(year,dayofyear)/maketime(hour,minute,second)：拼湊日期、時間函數
select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'-- unix_timestamp()/unix_timestamp(date)/from_unixtime(unix_timestamp)/from_unixtime(unix_timestamp,format):Unix 時間戳、日期轉換函數
select unix_timestamp(); -- 1533802315
select unix_timestamp(now());-- 1533802315
select from_unixtime(1533802315); -- 2018-08-09 16:11:55
select from_unixtime(1533802315, '%Y%m%d%H%i%s'); -- 20180809161155

三、數學函數

-- ABS(X):返回X的絕對值
select ABS(-32);-- 32-- MOD(N,M)或%:返回N被M除的余數
select MOD(15,7);-- 1
select 15 % 7; -- 1-- FLOOR(X):返回不大于X的最大整數值
select FLOOR(1.23); -- 2
select FLOOR(-1.23); -- -2-- CEILING(X)/ceil(x):返回不小于X的最小整數值
select CEILING(1.23);-- 2 
select CEILING(-1.23); -- -1-- ROUND(X) :返回參數X的四舍五入的一個整數。 
select ROUND(1.58); -- 2
select ROUND(-1.58); -- -2-- round(x,y):返回數值x帶有y為小數結果的數值（四舍五入）
SELECT round(3.1415926,2);-- 3.14-- rand():返回隨機數
select rand();-- 0.5911854436538978-- truncate(x,y):返回數值x截取y位小數的結果（不四舍五入）
select truncate(3.1415926,4);-- 3.1415

四、邏輯函數

-- CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END：在第一個方案的返回結果中， value=compare-value。而第二個方案的返回結果是第一種情況的真實結果。如果沒有匹配的結果值，則返回結果為ELSE后的結果，如果沒有ELSE 部分，則返回值為 NULL。
SELECTCASE 1
WHEN 1 THEN2
ELSE3
END;-- 2-- IF(expr1,expr2,expr3)：如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，則 IF()的返回值為expr2; 否則返回值則為 expr3。IF() 的返回值為數字值或字符串值，具體情況視其所在語境而定
SELECT IF(1>2,2,3);-- 3-- STRCMP(expr1,expr2)：如果字符串相同，STRCMP()返回0，如果第一參數根據當前的排序次序小于第二個，返回-1，否則返回1
select STRCMP('text', 'text2');-- -1-- SELECT IFNULL(expr1,expr2)：如果expr1為空則返回expr2否則返回expr1
SELECT IFNULL(1,2);-- 2

五、加密函數

-- MD5(str)：函數可以對字符串str進行加密。MD5(str)函數主要對普通的數據進行加密。下面使用MD5(str)函數為字符串“abcd”加密
SELECT MD5('abcd');-- e2fc714c4727ee9395f324cd2e7f331f-- ENCODE(str,pswd_str)：函數可以使用字符串pswd_str來加密字符串str。加密的結果是一個二進制數，必須使用BLOB類型的字段來保存它。
SELECT ENCODE("abcd","evan");-- DECODE(crypt_str,pswd_str)函數可以使用字符串pswd_str來為crypt_str解密。crypt_str是通過ENCODE(str,pswd_str)加密后的二進制數據。字符串pswd_str應該與加密時的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)為ENCODE(str,pswd_str)加密的數據解密。
SELECT DECODE(ENCODE("abcd","evan"),"evan");