用户及权限管理

-- 创建新用户（localhost仅支持本地访问，%代表允许外网访问）
CREATE USER 'zhang'@'%' IDENTIFIED BY 'hanzhe';

-- 赋予该用户 test 库所有表的查询权限（SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROP....）
GRANT SELECT ON test.* TO 'zhang'@'%';

-- 赋予该用户所有库所有表的所有权限，并允许该用户在权限范围内可以给其他用户授权
GRANT ALL PRIVILEGES ON *.* TO 'zhang'@'%' WITH GRANT OPTION;

-- 撤销该用户在所有库所有表中，给其他用户授权的权限
REVOKE GRANT OPTION ON *.* FROM 'zhang'@'%';

-- 撤销该用户所有库所有表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhang'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 修改用户密码 MySQL8 以前的版本
SET PASSWORD FOR 'zhang'@'%' = PASSWORD('123456');
-- 修改用户密码 MySQL8 之后的版本
ALTER USER USER() IDENTIFIED BY '123456';       -- 首次安装需要重置密码
ALTER USER 'zhang'@'%' IDENTIFIED BY '123456';  -- 修改密码

-- 删除用户
DROP USER 'zhang'@'%';

DUMP备份与恢复

数据导出

-- 导出test整个数据库（dddddump 为手动创建的文件夹）
mysqldump -uroot -p test > /var/lib/mysql/dddddump/test.sql

-- 导出test库的三张表
mysqldump -uroot -p test sys_user sys_role sys_menu > /var/lib/mysql/dddddump/test.sql

-- 更多参数（--也要加上）
mysqldump -uroot -p [OPTIONS]  test > /var/lib/mysql/dddddump/test.sql
    --no-data               # 只导出表结构，不包含数据
    --no-create-info        # 只导出数据，不包含表结构
    --add-drop-database     # 在CREATE DATABASE前添加DROP DATABASE语句
    --databases             # 导出的SQL中包含建库语句（默认不包含）
    --all-databases         # 导出所有数据库
    --skip-lock-tables      # 不锁表（适合生产环境大表备份）
    --single-transaction    # 使用事务保证一致性（仅InnoDB）
    --routines              # 包含存储过程和函数
    --triggers              # 包含触发器
    --events                # 包含事件

数据导入

-- 先创建数据库，如果数据库已存在可跳过该步骤
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci"

-- 执行SQL文件还原数据库
mysql -u root -p test < /var/lib/mysql/dddddump/test.sql

MySQL锁

添加行锁

查询指定数据，数据不存在则插入，这种SQL在并发下可能会出现多次插入，可以使用FOR UPDATE对查询的部分数据加上行锁

1. InnoDB引擎才支持行锁，MyISAM只支持表锁
2. 行锁必须在事务中使用，且只对事务生效，否则会立即释放
3. 确保查询使用了索引，否则可能锁表而不是锁行

START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR UPDATE;
COMMIT;

终止现成释放锁

如果锁一直被占用无法释放，可以查询事务列表，找到对应的事务通过线程ID终止

SELECT * FROM information_schema.innodb_trx;
KILL [thread_id];