目录
- show create database table_name;
- show variables like 'character_%';
show variables like 'collation_%';
- select @@datadir;
- select @@basedir;
mysql --help|grep 'my.cnf'
- # Example MySQL config file for small systems.
- #
- # This is for a system with little memory (<= 64M) where MySQL is only used
- # from time to time and it's important that the mysqld daemon
- # doesn't use much resources.
- #
- # MySQL programs look for option files in a set of
- # locations which depend on the deployment platform.
- # You can copy this option file to one of those
- # locations. For information about these locations, see:
- # http://dev.mysql.com/doc/mysql/en/option-files.html
- #
- # In this file, you can use all long options that a program supports.
- # If you want to know which options a program supports, run the program
- # with the "--help" option.
-
- # The following options will be passed to all MySQL clients
- [client]
- default-character-set=utf8
- #password = your_password
- port = 3306
- socket = /tmp/mysql.sock
-
- # Here follows entries for some specific programs
-
- # The MySQL server
- [mysqld]
- default-storage-engine=INNODB
- character-set-server=utf8
- collation-server=utf8_general_ci
- port = 3306
- socket = /tmp/mysql.sock
- skip-external-locking
- key_buffer_size = 16K
- max_allowed_packet = 1M
- table_open_cache = 4
- sort_buffer_size = 64K
- read_buffer_size = 256K
- read_rnd_buffer_size = 256K
- net_buffer_length = 2K
- thread_stack = 128K
-
- # Don't listen on a TCP/IP port at all. This can be a security enhancement,
- # if all processes that need to connect to mysqld run on the same host.
- # All interaction with mysqld must be made via Unix sockets or named pipes.
- # Note that using this option without enabling named pipes on Windows
- # (using the "enable-named-pipe" option) will render mysqld useless!
- #
- #skip-networking
- server-id = 1
-
- # Uncomment the following if you want to log updates
- log-bin=mysql-bin
-
- # binary logging format - mixed recommended
- #binlog_format=mixed
-
- # Causes updates to non-transactional engines using statement format to be
- # written directly to binary log. Before using this option make sure that
- # there are no dependencies between transactional and non-transactional
- # tables such as in the statement INSERT INTO t_myisam SELECT * FROM
- # t_innodb; otherwise, slaves may diverge from the master.
- #binlog_direct_non_transactional_updates=TRUE
-
- # Uncomment the following if you are using InnoDB tables
- #innodb_data_home_dir = /usr/local/mysql/data
- #innodb_data_file_path = ibdata1:10M:autoextend
- #innodb_log_group_home_dir = /usr/local/mysql/data
- # You can set .._buffer_pool_size up to 50 - 80 %
- # of RAM but beware of setting memory usage too high
- #innodb_buffer_pool_size = 16M
- #innodb_additional_mem_pool_size = 2M
- # Set .._log_file_size to 25 % of buffer pool size
- #innodb_log_file_size = 5M
- #innodb_log_buffer_size = 8M
- #innodb_flush_log_at_trx_commit = 1
- #innodb_lock_wait_timeout = 50
-
- [mysqldump]
- quick
- max_allowed_packet = 16M
-
- [mysql]
- no-auto-rehash
- # Remove the next comment character if you are not familiar with SQL
- #safe-updates
-
- [myisamchk]
- key_buffer_size = 8M
- sort_buffer_size = 8M
-
- [mysqlhotcopy]
- interactive-timeout
-
- [mysqld]
- transaction-isolation=READ-COMMITTED
sudo chmod 664 my.cnf
systemctl restart mysqld;
show variables like 'character_%';
- alter database database_name charset utf8;
- alter table table_name charset utf8;
- show create database database_name;
- show create table table_name;
MySQL有4个级别的字符集和比较规则,分别是:
注意:创建数据库表或者列时可以在后面增加一个charcter set来指定字符集,mysql8默认是utf-8。客户端编码必须和服务器character_set_client一致,解码必须和服务器character_set_results一致。
- show variables like '%lower_case_table_name%';
-
- # 5.7版本下修改:在my.cnf文件下[mysqld]下增加然后重启服务器
- lower_case_table_names=1
-
- #8.0版本下修改:
- 停止mysql服务;
- 删除数据目录
- /var/lib/mysql 目录
- 在mysql配置文件 my.cnf中添加 lower_case_table_names=1
- 启动服务器。
- -- 创建用户
- create user 'kanlina' identified by '123456';
- create user 'kanlina'@'localhost' identified by '123456';
-
- -- 修改用户
- update mysql.user set user = 'kanlina1' where user = 'kanlina';
- flush privileges;
-
- -- 删除用户
- drop user 'kanlina1';
- drop user 'kanlina'@'localhost';
- -- 设置当前用户密码 以下@'localhost'可省略
- alter user user() identified by '123456';
- set password = ******; -- mysql 8.0取消
-
- -- 修改其他用户密码
- alter user '用户名'@'localhost' identified by '密码';
- set password for '用户名'@'hostname'='密码';
-
- -- 密码过期策略
- alter user 'kanlina'@'localhost' password expire;
-
- -- 设置过期时间
- create user 'kanlina'@'localhost' password expire interval 90 day;
- alter user 'kanlina'@'localhost' password expire interval 90 day;
- -- 永不过期
- create user 'kanlina'@'localhost' password expire interval never;
- alter user 'kanlina'@'localhost' password expire interval never;
- -- 延用全局过期策略
- create user 'kanlina'@'localhost' password expire interval default;
- alter user 'kanlina'@'localhost' password expire interval default;
- -- 或者配置文件中加入
- default_password_lifetime
-
- -- 密码重用策略
- set persist password_history = 6; #设置不能选择最近使用过的6个密码
- set persist password_reuse_interval = 365; #设置不能选择最近一年内的密码
- -- 系统变量设置密码
- password_history --规定密码重用数量
- password_reuse_interval --规定密码重用的周期
-
- -- 授予权限
- grant 权限1,权限2,...权限n on 数据库名称.表名称 to 用户名@用户地址 [identified by '密码口令']; #如果没有该用户则创建新用户
- grant select,update,delete,insert on test.acc_line_mapping to 'kanlina';
- grant all privileges on *.* to 'kanlina'@'%'; #和root权限一样,但是无法赋予其他用户权限
-
- -- 查看权限
- show grants;
-
- -- 回收权限
- revoke 权限1,权限2,...权限n on 数据库名称.表名称 from '用户名';
- -- 创建角色
- create role 'manager','stoker';
-
- --给角色分配权限
- grant privileges on table_name to 'role_name'[@'localhost'];
-
- --查询权限
- show privileges\G;
-
- --查看角色的权限
- show grants for 'manager'@'%';
-
- --回收角色权限
- revoke update on 数据库.表 from '角色名';
-
- --删除角色
- drop role '角色名';
-
- --给用户赋予角色
- grant '角色名' to '用户';
- --激活角色
- set default role all to '角色名';
-
- --查询已激活角色
- select current_role();
-
- --显示是否永久激活
- show variables like 'activate_all_roles_on_login';
- --设置永久激活
- set global activate_all_roles_on_login=on;