• MySQL-底层设置


    目录

    一、字符集

    1.1、字符集设置

    1.2、各级别的字符集

    二、大小写规范

    2.1、查看系统设置

    三、mysql数据目录(linux)

    四、用户与权限管理

    五、角色(mysql8.0有)


    一、字符集

    1.1、字符集设置

    • 查看数据库字符集
      1. show create database table_name;
      2. show variables like 'character_%';
    • 查看数据库比较级
      show variables like 'collation_%';
    • 修改字符集(mysql8.0不需要修改,默认字符集为utf-8)
    • 查看mysql安装目录
      1. select @@datadir;
      2. select @@basedir;
    • 修改配置文件 查看my.cnf应该存在的位置
      mysql --help|grep 'my.cnf'
    • mac版本默认没有my.cnf配置文件,可以找一个配置文件,将配置文件拷贝到上面查到的目录中的一个地址
      1. # Example MySQL config file for small systems.
      2. #
      3. # This is for a system with little memory (<= 64M) where MySQL is only used
      4. # from time to time and it's important that the mysqld daemon
      5. # doesn't use much resources.
      6. #
      7. # MySQL programs look for option files in a set of
      8. # locations which depend on the deployment platform.
      9. # You can copy this option file to one of those
      10. # locations. For information about these locations, see:
      11. # http://dev.mysql.com/doc/mysql/en/option-files.html
      12. #
      13. # In this file, you can use all long options that a program supports.
      14. # If you want to know which options a program supports, run the program
      15. # with the "--help" option.
      16. # The following options will be passed to all MySQL clients
      17. [client]
      18. default-character-set=utf8
      19. #password = your_password
      20. port = 3306
      21. socket = /tmp/mysql.sock
      22. # Here follows entries for some specific programs
      23. # The MySQL server
      24. [mysqld]
      25. default-storage-engine=INNODB
      26. character-set-server=utf8
      27. collation-server=utf8_general_ci
      28. port = 3306
      29. socket = /tmp/mysql.sock
      30. skip-external-locking
      31. key_buffer_size = 16K
      32. max_allowed_packet = 1M
      33. table_open_cache = 4
      34. sort_buffer_size = 64K
      35. read_buffer_size = 256K
      36. read_rnd_buffer_size = 256K
      37. net_buffer_length = 2K
      38. thread_stack = 128K
      39. # Don't listen on a TCP/IP port at all. This can be a security enhancement,
      40. # if all processes that need to connect to mysqld run on the same host.
      41. # All interaction with mysqld must be made via Unix sockets or named pipes.
      42. # Note that using this option without enabling named pipes on Windows
      43. # (using the "enable-named-pipe" option) will render mysqld useless!
      44. #
      45. #skip-networking
      46. server-id = 1
      47. # Uncomment the following if you want to log updates
      48. log-bin=mysql-bin
      49. # binary logging format - mixed recommended
      50. #binlog_format=mixed
      51. # Causes updates to non-transactional engines using statement format to be
      52. # written directly to binary log. Before using this option make sure that
      53. # there are no dependencies between transactional and non-transactional
      54. # tables such as in the statement INSERT INTO t_myisam SELECT * FROM
      55. # t_innodb; otherwise, slaves may diverge from the master.
      56. #binlog_direct_non_transactional_updates=TRUE
      57. # Uncomment the following if you are using InnoDB tables
      58. #innodb_data_home_dir = /usr/local/mysql/data
      59. #innodb_data_file_path = ibdata1:10M:autoextend
      60. #innodb_log_group_home_dir = /usr/local/mysql/data
      61. # You can set .._buffer_pool_size up to 50 - 80 %
      62. # of RAM but beware of setting memory usage too high
      63. #innodb_buffer_pool_size = 16M
      64. #innodb_additional_mem_pool_size = 2M
      65. # Set .._log_file_size to 25 % of buffer pool size
      66. #innodb_log_file_size = 5M
      67. #innodb_log_buffer_size = 8M
      68. #innodb_flush_log_at_trx_commit = 1
      69. #innodb_lock_wait_timeout = 50
      70. [mysqldump]
      71. quick
      72. max_allowed_packet = 16M
      73. [mysql]
      74. no-auto-rehash
      75. # Remove the next comment character if you are not familiar with SQL
      76. #safe-updates
      77. [myisamchk]
      78. key_buffer_size = 8M
      79. sort_buffer_size = 8M
      80. [mysqlhotcopy]
      81. interactive-timeout
      82. [mysqld]
      83. transaction-isolation=READ-COMMITTED
    • 修改文件权限,不然启动会报错
      sudo chmod 664 my.cnf    
    • 重新启动mysql,启动失败的话就重新启动一下电脑;
      systemctl restart mysqld;
    • 登录mysql,执行
      show variables like 'character_%';
    • mysql字符修改成功
    • 修改数据库或表字符
      1. alter database database_name charset utf8;
      2. alter table table_name charset utf8;
    • 查看数据库和表字符
      1. show create database database_name;
      2. show create table table_name;

    1.2、各级别的字符集

    MySQL有4个级别的字符集和比较规则,分别是:

    • 服务器级别;
    • 数据库级别;
    • 表级别;
    • 列级别;

    注意:创建数据库表或者列时可以在后面增加一个charcter set来指定字符集,mysql8默认是utf-8。客户端编码必须和服务器character_set_client一致,解码必须和服务器character_set_results一致。

    二、大小写规范

    2.1、查看系统设置

    1. show variables like '%lower_case_table_name%';
    2. # 5.7版本下修改:在my.cnf文件下[mysqld]下增加然后重启服务器
    3. lower_case_table_names=1
    4. #8.0版本下修改:
    5. 停止mysql服务;
    6. 删除数据目录
    7. /var/lib/mysql 目录
    8. 在mysql配置文件 my.cnf中添加 lower_case_table_names=1
    9. 启动服务器。
    • 0(默认):大小写敏感;
    • 1:大小写不敏感,sql语句和数据库表都是以小写形式存储在磁盘上;
    • 2:创建的表和数据库依据语句上格式存放,凡是查找都是转换为小写进行。

    三、mysql数据目录(linux)

    四、用户与权限管理

    1. -- 创建用户
    2. create user 'kanlina' identified by '123456';
    3. create user 'kanlina'@'localhost' identified by '123456';
    4. -- 修改用户
    5. update mysql.user set user = 'kanlina1' where user = 'kanlina';
    6. flush privileges;
    7. -- 删除用户
    8. drop user 'kanlina1';
    9. drop user 'kanlina'@'localhost';
    10. -- 设置当前用户密码 以下@'localhost'可省略
    11. alter user user() identified by '123456';
    12. set password = ******; -- mysql 8.0取消
    13. -- 修改其他用户密码
    14. alter user '用户名'@'localhost' identified by '密码';
    15. set password for '用户名'@'hostname'='密码';
    16. -- 密码过期策略
    17. alter user 'kanlina'@'localhost' password expire;
    18. -- 设置过期时间
    19. create user 'kanlina'@'localhost' password expire interval 90 day;
    20. alter user 'kanlina'@'localhost' password expire interval 90 day;
    21. -- 永不过期
    22. create user 'kanlina'@'localhost' password expire interval never;
    23. alter user 'kanlina'@'localhost' password expire interval never;
    24. -- 延用全局过期策略
    25. create user 'kanlina'@'localhost' password expire interval default;
    26. alter user 'kanlina'@'localhost' password expire interval default;
    27. -- 或者配置文件中加入
    28. default_password_lifetime
    29. -- 密码重用策略
    30. set persist password_history = 6; #设置不能选择最近使用过的6个密码
    31. set persist password_reuse_interval = 365; #设置不能选择最近一年内的密码
    32. -- 系统变量设置密码
    33. password_history --规定密码重用数量
    34. password_reuse_interval --规定密码重用的周期
    35. -- 授予权限
    36. grant 权限1,权限2,...权限n on 数据库名称.表名称 to 用户名@用户地址 [identified by '密码口令']; #如果没有该用户则创建新用户
    37. grant select,update,delete,insert on test.acc_line_mapping to 'kanlina';
    38. grant all privileges on *.* to 'kanlina'@'%'; #和root权限一样,但是无法赋予其他用户权限
    39. -- 查看权限
    40. show grants;
    41. -- 回收权限
    42. revoke 权限1,权限2,...权限n on 数据库名称.表名称 from '用户名';

    五、角色(mysql8.0有)

    1. -- 创建角色
    2. create role 'manager','stoker';
    3. --给角色分配权限
    4. grant privileges on table_name to 'role_name'[@'localhost'];
    5. --查询权限
    6. show privileges\G;
    7. --查看角色的权限
    8. show grants for 'manager'@'%';
    9. --回收角色权限
    10. revoke update on 数据库.表 from '角色名';
    11. --删除角色
    12. drop role '角色名';
    13. --给用户赋予角色
    14. grant '角色名' to '用户';
    15. --激活角色
    16. set default role all to '角色名';
    17. --查询已激活角色
    18. select current_role();
    19. --显示是否永久激活
    20. show variables like 'activate_all_roles_on_login';
    21. --设置永久激活
    22. set global activate_all_roles_on_login=on;

     

  • 相关阅读:
    fastfds扩容全部操作过程-全是干货
    1024程序员节献礼,火山引擎ByteHouse带来三重产品福利
    JavaScript中的箭头函数
    【Python零基础入门篇 · 7】:列表、元组的相关操作(完整版)
    FPGA 芯片点亮标准?
    learning vim
    分布式进化算法
    【LeetCode】链表题总结(持续更新)
    [Swift]定义一个全局的可管理的计时器
    自学黑客(网络安全),一般人我劝你还是算了吧
  • 原文地址:https://blog.csdn.net/kk_lina/article/details/126111215