开启数据库日志功能

SET GLOBAL general_log = 'ON';
SHOW VARIABLES LIKE 'general_log%';
SET GLOBAL slow_query_log_file = '/var/log/mysql/general_log.log';

MySQL 数据库备份与恢复

一、mysqldump 备份工具

1. 基本备份命令

bash
复制
备份整个数据库
mysqldump -u username -p database_name > backup.sql
备份多个数据库
mysqldump -u username -p --databases db1 db2 db3 > multi_backup.sql
备份所有数据库
mysqldump -u username -p --all-databases > all_databases.sql

2. 高级备份选项

bash
复制
添加时间戳（推荐）
mysqldump -u username -p database_name > db_$(date +%Y%m%d_%H%M%S).sql
只备份表结构（不含数据）
mysqldump -u username -p --no-data database_name > schema_only.sql
只备份数据（不含表结构）
mysqldump -u username -p --no-create-info database_name > data_only.sql
备份特定表
mysqldump -u username -p database_name table1 table2 > tables_backup.sql
压缩备份（节省空间）
mysqldump -u username -p database_name | gzip > backup.sql.gz

3. 重要参数说明

二、数据库导入方法

1. 基本导入命令

bash
复制
导入整个数据库
mysql -u username -p database_name < backup.sql
导入压缩的备份文件
gunzip < backup.sql.gz | mysql -u username -p database_name
或者
zcat backup.sql.gz | mysql -u username -p database_name

2. 导入时的重要参数

bash
复制
强制继续执行即使有错误
mysql -u username -p --force database_name < backup.sql
显示导入进度（PV工具需要安装）
pv backup.sql | mysql -u username -p database_name
导入时记录日志
mysql -u username -p database_name < backup.sql > import.log 2>&1

3. 导入部分数据

bash
复制
仅导入表结构
sed '/INSERT INTO/,$d' backup.sql | mysql -u username -p database_name
仅导入特定表的数据（需先创建表结构）
awk '/INSERT INTO table_name/,/^$/' backup.sql | mysql -u username -p database_name

三、生产环境最佳实践

1. 完整备份脚本示例

bash
复制
#!/bin/bash
完整数据库备份脚本
USER="backup_user"
PASS="secure_password"
BACKUP_DIR="/var/mysql_backups"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7
创建备份目录
mkdir -p $BACKUP_DIR/$DATE
备份所有数据库
mysqldump -u$USER -p$PASS \
--single-transaction \
--routines \
--triggers \
--events \
--hex-blob \
--all-databases | gzip > $BACKUP_DIR/$DATE/all_databases_$DATE.sql.gz
备份每个数据库单独文件（可选）
for DB in $(mysql -u$USER -p$PASS -e "SHOW DATABASES;" -s | grep -v Database); do
    if [[ "$DB" != "information_schema" ]] && [[ "$DB" != "performance_schema" ]] && [[ "$DB" != "mysql" ]] && [[ "$DB" != "sys" ]]; then
        mysqldump -u$USER -p$PASS \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --hex-blob \
        $DB | gzip > $BACKUP_DIR/$DATE/${DB}_$DATE.sql.gz
    fi
done
清理旧备份
find $BACKUP_DIR -type d -mtime +$KEEP_DAYS -exec rm -rf {} \;

2. 大型数据库处理技巧

bash
复制
分表备份（适合超大数据库）
for TABLE in $(mysql -u username -p database_name -e "SHOW TABLES;" -s | grep -v Tables_in); do
    mysqldump -u username -p \
    --single-transaction \
    database_name $TABLE | gzip > ${TABLE}.sql.gz
done
并行备份（使用GNU parallel）
parallel -j 4 "mysqldump -u username -p --single-transaction database_name {} | gzip > {}.sql.gz" ::: $(mysql -u username -p database_name -e "SHOW TABLES;" -s | grep -v Tables_in)

3. 恢复大型数据库技巧

bash
复制
禁用外键检查加速导入
mysql -u username -p database_name -e "SET FOREIGN_KEY_CHECKS=0; SOURCE backup.sql; SET FOREIGN_KEY_CHECKS=1;"
分批导入（先结构后数据）
1. 先导入表结构
sed '/INSERT INTO/,$d' large_backup.sql > structure.sql
mysql -u username -p database_name < structure.sql
2. 然后导入数据
grep 'INSERT INTO' large_backup.sql | mysql -u username -p database_name

用户权限

基本权限授予命令

1. 授予用户所有数据库的所有权限（超级用户权限）

-- 创建用户并授予所有权限（MySQL 5.7+） CREATE USER 'username'@'host' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON . TO 'username'@'host' WITH GRANT OPTION; FLUSH PRIVILEGES;

-- 如果用户已存在，只需授权 GRANT ALL PRIVILEGES ON . TO 'username'@'host' WITH GRANT OPTION; FLUSH PRIVILEGES;

2. 允许从任何主机连接

-- 允许从任何IP地址连接
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON . TO 'username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

权限细分控制

1. 仅授予连接权限（无操作权限）


GRANT USAGE ON . TO 'username'@'host';
FLUSH PRIVILEGES;

2. 授予特定数据库的所有权限


GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
FLUSH PRIVILEGES;

3. 授予特定表的特定权限


GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'host';
FLUSH PRIVILEGES;

权限查看与撤销

1. 查看用户权限


SHOW GRANTS FOR 'username'@'host';

2. 撤销所有权限


REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';
FLUSH PRIVILEGES;

3. 撤销特定权限


REVOKE INSERT, UPDATE ON database_name.* FROM 'username'@'host';
FLUSH PRIVILEGES;

SQL查询语句

基础查询命令

1. 简单查询

sql
复制
-- 查询所有列
SELECT * FROM 表名;
-- 查询特定列
SELECT 列1, 列2, 列3 FROM 表名;-- 使用别名
SELECT 列1 AS 别名1, 列2 AS 别名2 FROM 表名;

2. 条件查询 (WHERE)


SELECT * FROM 表名 WHERE 条件;-- 示例
SELECT * FROM users WHERE age > 18;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM orders WHERE status IN ('paid', 'shipped');
SELECT * FROM customers WHERE name LIKE '张%';

3. 排序 (ORDER BY)

sql
复制
-- 单列排序
SELECT * FROM 表名 ORDER BY 列名 [ASC|DESC];-- 多列排序
SELECT * FROM 表名 ORDER BY 列1 ASC, 列2 DESC;

4. 分页 (LIMIT)

sql
复制
-- 基本分页
SELECT * FROM 表名 LIMIT 10;       -- 前10条
SELECT * FROM 表名 LIMIT 5, 10;    -- 从第6条开始，取10条(跳过前5条)-- MySQL 8.0+ 推荐写法
SELECT * FROM 表名 LIMIT 10 OFFSET 5;

高级查询命令

1. 聚合函数


-- 常用聚合函数
SELECT COUNT(*) FROM 表名;         -- 计数
SELECT SUM(列名) FROM 表名;       -- 求和
SELECT AVG(列名) FROM 表名;       -- 平均值
SELECT MAX(列名) FROM 表名;       -- 最大值
SELECT MIN(列名) FROM 表名;       -- 最小值-- 示例
SELECT COUNT(*) AS 总数 FROM users WHERE age > 18;
SELECT AVG(price) AS 平均价格 FROM products;

2. 分组查询 (GROUP BY)


-- 基本分组
SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名;-- 示例
SELECT department, COUNT(*) AS 员工数 FROM employees GROUP BY department;
SELECT category, AVG(price) AS 平均价格 FROM products GROUP BY category;

3. 连接查询 (JOIN)


-- 内连接
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列;
-- 左连接
SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.列 = 表2.列;
-- 右连接
SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.列 = 表2.列;-- 多表连接
SELECT * FROM 表1 
JOIN 表2 ON 表1.列 = 表2.列
JOIN 表3 ON 表2.列 = 表3.列;

4. 子查询

sql
复制
-- WHERE子句中的子查询
SELECT * FROM 表1 WHERE 列 IN (SELECT 列 FROM 表2 WHERE 条件);
-- FROM子句中的子查询
SELECT * FROM (SELECT 列1, 列2 FROM 表名) AS 别名;-- SELECT子句中的子查询
SELECT 列1, (SELECT COUNT(*) FROM 表2 WHERE 表2.列 = 表1.列) AS 计数 FROM 表1;

实用查询技巧

1. 去重查询 (DISTINCT)

sql复制
SELECT DISTINCT 列名 FROM 表名;

2. 条件判断 (CASE WHEN)

sql复制
SELECT 
    列1,
    CASE 
        WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
        ELSE 默认结果
    END AS 别名
FROM 表名;

3. 日期时间查询

sql
复制
-- 当前日期时间
SELECT NOW(), CURDATE(), CURTIME();
-- 日期格式化
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM 表名;-- 日期计算
SELECT DATE_ADD(date_column, INTERVAL 1 DAY) FROM 表名;
SELECT DATEDIFF(date1, date2) FROM 表名;

4. 字符串处理

sql
复制
-- 连接字符串
SELECT CONCAT(列1, ' ', 列2) FROM 表名;
-- 子字符串
SELECT SUBSTRING(列名, 1, 5) FROM 表名;-- 替换
SELECT REPLACE(列名, '旧值', '新值') FROM 表名;

MySQL 高级复杂查询大全

1. 窗口函数（MySQL 8.0+）

sql
复制
-- 排名函数
SELECT 
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) AS price_rank,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_price_rank,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM products;-- 分区窗口
SELECT 
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

2. 递归查询（CTE - Common Table Expressions）


-- 简单CTE
WITH sales_summary AS (
    SELECT 
        product_id, 
        SUM(quantity) AS total_quantity
    FROM order_items
    GROUP BY product_id
)
SELECT 
    p.product_name,
    s.total_quantity
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;-- 递归CTE（查询组织结构）
WITH RECURSIVE org_hierarchy AS (
    -- 基础查询（顶级节点）
    SELECT 
        id, 
        name, 
        parent_id, 
        1 AS level
    FROM employees
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归部分
    SELECT 
        e.id, 
        e.name, 
        e.parent_id, 
        h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.parent_id = h.id
)
SELECT * FROM org_hierarchy ORDER BY level, id;

3. 高级聚合查询

sql
复制
-- GROUPING SETS
SELECT 
    COALESCE(department, '所有部门') AS department,
    COALESCE(job_title, '所有职位') AS job_title,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY GROUPING SETS (
    (department, job_title),
    (department),
    (job_title),
    ()
);-- ROLLUP（层次化聚合）
SELECT 
    YEAR(order_date) AS year,
    QUARTER(order_date) AS quarter,
    MONTH(order_date) AS month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY ROLLUP(YEAR(order_date), QUARTER(order_date), MONTH(order_date));

4. JSON数据处理（MySQL 5.7+）

sql
复制
-- 创建JSON数据
INSERT INTO products (id, name, attributes)
VALUES (1, 'Smartphone', '{"color": "black", "storage": "128GB", "features": ["GPS", "Bluetooth"]}');
-- 查询JSON字段
SELECT 
    name,
    attributes->>"$.color" AS color,
    attributes->>"$.storage" AS storage,
    JSON_EXTRACT(attributes, '$.features[0]') AS primary_feature
FROM products;-- 修改JSON数据
UPDATE products
SET attributes = JSON_SET(
    attributes,
    '$.color', 'blue',
    '$.screen_size', '6.5"'
)
WHERE id = 1;

5. 全文检索

sql
复制
-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);
-- 自然语言搜索
SELECT 
    id, 
    title,
    MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;-- 布尔模式搜索
SELECT 
    id, 
    title
FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

6. 复杂子查询与派生表

sql
复制
-- 相关子查询
SELECT 
    e1.employee_name,
    e1.salary,
    (SELECT COUNT(*) 
     FROM employees e2 
     WHERE e2.salary > e1.salary) AS higher_salary_count
FROM employees e1;-- 多级派生表
SELECT 
    dept_stats.department,
    dept_stats.avg_salary,
    emp_count.employee_count
FROM 
    (SELECT 
        department, 
        AVG(salary) AS avg_salary
     FROM employees
     GROUP BY department) AS dept_stats
JOIN 
    (SELECT 
        department, 
        COUNT(*) AS employee_count
     FROM employees
     GROUP BY department) AS emp_count
ON dept_stats.department = emp_count.department;

7. 高级连接技术

sql
复制
-- 自连接（查找同一部门的员工对）
SELECT 
    e1.employee_name AS employee1,
    e2.employee_name AS employee2,
    e1.department
FROM employees e1
JOIN employees e2 ON 
    e1.department = e2.department AND
    e1.employee_id < e2.employee_id;  -- 避免重复对-- 横向派生表（LATERAL JOIN - MySQL 8.0.14+）
SELECT 
    d.department_name,
    top_emp.employee_name,
    top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT employee_name, salary
    FROM employees e
    WHERE e.department_id = d.department_id
    ORDER BY salary DESC
    LIMIT 3
) AS top_emp;

8. 复杂条件逻辑

sql
复制
-- 多条件CASE表达式
SELECT 
    order_id,
    customer_id,
    amount,
    CASE 
        WHEN amount > 1000 THEN 'VIP订单'
        WHEN amount > 500 AND customer_id IN (
            SELECT customer_id FROM vip_customers
        ) THEN 'VIP客户普通订单'
        WHEN amount > 300 THEN '大额订单'
        ELSE '普通订单'
    END AS order_type
FROM orders;-- 使用HAVING过滤分组
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5 AND SUM(amount) > 1000;

9. 动态SQL与预处理语句

sql
复制
-- 预处理语句示例
SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE id = ?');
PREPARE stmt FROM @sql;
SET @id = 123;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;-- 动态条件构建
SET @where_clause = '';
IF @filter_name IS NOT NULL THEN
    SET @where_clause = CONCAT(@where_clause, ' AND name LIKE ''%', @filter_name, '%''');
END IF;
-- 然后构建完整SQL执行

10. 性能优化技巧

sql
复制
-- 使用索引提示
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 100 AND order_date > '2023-01-01';
-- 分页优化（避免OFFSET大数值）
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 10000, 1)
ORDER BY id LIMIT 20;-- 使用派生表优化复杂查询
SELECT * FROM (
    SELECT id, name FROM products WHERE category = 'Electronics'
) AS elec_products
JOIN inventory ON elec_products.id = inventory.product_id;

MySQL常用命令

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">开启数据库日志功能</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">MySQL 数据库备份与恢复</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">一、mysqldump 备份工具</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 基本备份命令</font>

备份整个数据库

备份多个数据库

备份所有数据库

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 高级备份选项</font>

添加时间戳（推荐）

只备份表结构（不含数据）

只备份数据（不含表结构）

备份特定表

压缩备份（节省空间）

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 重要参数说明</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">二、数据库导入方法</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 基本导入命令</font>

导入整个数据库

导入压缩的备份文件

或者

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 导入时的重要参数</font>

强制继续执行即使有错误

显示导入进度（PV工具需要安装）

导入时记录日志

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 导入部分数据</font>

仅导入表结构

仅导入特定表的数据（需先创建表结构）

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">三、生产环境最佳实践</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 完整备份脚本示例</font>

完整数据库备份脚本

创建备份目录

备份所有数据库

备份每个数据库单独文件（可选）

清理旧备份

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 大型数据库处理技巧</font>

分表备份（适合超大数据库）

并行备份（使用GNU parallel）

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 恢复大型数据库技巧</font>

禁用外键检查加速导入

分批导入（先结构后数据）

1. 先导入表结构

2. 然后导入数据

用户权限

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">基本权限授予命令</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 授予用户所有数据库的所有权限（超级用户权限）</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 允许从任何主机连接</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">权限细分控制</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 仅授予连接权限（无操作权限）</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 授予特定数据库的所有权限</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 授予特定表的特定权限</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">权限查看与撤销</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 查看用户权限</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 撤销所有权限</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 撤销特定权限</font>

SQL查询语句

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">基础查询命令</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 简单查询</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 条件查询 (WHERE)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 排序 (ORDER BY)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">4. 分页 (LIMIT)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">高级查询命令</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 聚合函数</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 分组查询 (GROUP BY)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 连接查询 (JOIN)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">4. 子查询</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">实用查询技巧</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 去重查询 (DISTINCT)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 条件判断 (CASE WHEN)</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 日期时间查询</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">4. 字符串处理</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">MySQL 高级复杂查询大全</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">1. 窗口函数（MySQL 8.0+）</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">2. 递归查询（CTE - Common Table Expressions）</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">3. 高级聚合查询</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">4. JSON数据处理（MySQL 5.7+）</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">5. 全文检索</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">6. 复杂子查询与派生表</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">7. 高级连接技术</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">8. 复杂条件逻辑</font>

<font style="color:rgba(0, 0, 0, 0.9);background-color:rgb(252, 252, 252);">9. 动态SQL与预处理语句</font>