MySQL基础实践

发表于 更新于 分类于 阅读次数：

持续更新中

查看数据库

  • 显示数据库线程信息

    1
    SHOW FULL PROCESSLIST;

  • 输出用户信息

    1
    SELECT now(), user(), version();

  • 查看数据库变量信息

    1
    SHOW VARIABLES;

  • 过滤字段

    1
    2
    show variables like 'char%';
    SHOW VARIABLES LIKE 'character_set_%';

  • 查看慢查询状态

    1
    2
    show global status like ‘%slow%’;
    show variables like '%slow%';

  • 输出数据库信息

    1
    2
    SHOW DATABASES LIKE '%wiki%';
    SHOW CREATE DATABASE mediawiki;

修改数据库配置

  • 修改数据库的默认排序规则

    1
    ALTER DATABASE wiki CHARACTER SET utf8 COLLATE utf8_bin;

  • 授权所有主机远程连接数据库

    1
    2
    3
    4
    5
    CREATE USER 'myuser'@'%' IDENTIFIED BY 'passwd';
    UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';  # 打开授权
    FLUSH PRIVILEGES;
    GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
    eg: GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
    • mysqladmin -u用户名 -p旧密码 password 新密码

  • MySQL强制重置密码方法一

    • 停掉mysql服务

      /etc/init.d/mysqld stop

    • 强制以root身份免认证登录

      /usr/sbin/mysqld –user=root –skip-grant-tables &

    • 重置密码：

      update mysql.user set password=PASSWORD(‘123456’) where User=’root’;

      flush privileges;

      quit;

      • 启动MySQL

      /etc/init.d/mysql start

  • MySQL强制重置密码方法二

    mysqld –skip-grant-tables

    mysql

    use mysql;

    update user set password=password(“admin10000.com”) where user=”root”;

    flush privileges;

  • 修改root密码

    • 方法一

      mysqladmin -u用户名 -p旧密码 password 新密码

    • 方法二

      set password for 用户名@localhost = password(‘新密码’);

    • 方法三

      use mysql;

      update user set password=password(‘新密码’) where user=’root’ and host=’localhost’;

      flush privileges;

  • MySQL5.7 关闭密码策略

    1
    2
    3
    4
    5
    set global validate_password_policy=0;

    update mysql.user set authentication_string=PASSWORD('123456') where user='root';

    alter user 'root'@'localhost' identified by 'passwd';

  • 设置密码永不过期

    1
    ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

  • 远程授权：

    1
    grant all privileges on DBName.* to 'myuser'@'%' identified by 'passwd';

参考文档