mysql一些基本操作

0. 一些配置(my.conf)

0.1 给mysql绑定在某个ip地址上

bind-address=10.166.224.33

• 通常情况下，mysql应该是只有内网才能访问。所以绑定一个本机的内网地址。
• 如果不绑定地址，则该主机的所有地址都可以访问mysql。不安全。

0.2 给mysql的表设置默认字符集

# 设置数据库表的默认字符集
character_set_server=utf8mb4

# 设置数据库表的默认字符集的比较规则: 不区分大小写
collation-server=utf8mb4_general_ci

# 设置数据库表的默认字符集的比较规则: 区分大小写
collation-server=utf8mb4_bin

 0.3 设置用于查询排序的缓冲区大小

# 设置每个会话的查询排序的默认大小
sort_buffer_size=32M

0.4 设置Innodb存储引擎的表数据和索引数据的最大内存缓冲区大小

# 配置innodb存储引擎的表数据和索引数据占用内存缓冲区大小
innodb_buffer_pool_size=1G

0.6 设置在mysql关于临时表所占内存缓冲区大小

# 设置临时表所占内存缓冲区大小(每个会话都会分配32M)
tmp_table_size=32M

 0.7 设置mysql中joinbuffer的内存缓冲区大小

# 设置多表连接时，若不走索引，其所用到的内存缓冲区大小。
join_buffer_size=10M

• 每次不走索引的两表连接，都会分配一个joinbuffer内存缓冲区

0.8 设置mysql开启binlog日志

bin_log=on

0.8 设置mysql中每次事务提交时redo log同步到磁盘的情况

innodb_flush_log_at_trx_commit=1

• 0：表示每次提交事务时只是将redo log日志写入内存的redo log buffer中
• 1：表示每次提交事务时将redo log日志写入到磁盘redo log日志文件中（默认）
• 2：表示每次提交事务时将redo log 日志写入操作系统缓存。这样如果数据库宕机但是操作系统没有宕机，数据页不会丢失。

0.9 设置mysql中每次事务提交时binlog 日志同步到磁盘的情况

sync_binlog=1

• 0：表示每次提交事务时写入操作系统缓存。（调用fwrite函数）
• 1：表示每次提交事务时写入磁盘二进制文件中。（调用fsync函数）（默认）

1. 权限管理

1.1 创建一个用户

create user 'ljs'@'%' identified by '123';

• 用户名：ljs
• %代表任何IP地址都可以访问
• 123：访问密码

create user 'ljs'@'127.0.0.%' identified by '123';

• 在某个网段才能使用该账户密码访问。

1.2 删除一个用户

drop user 'ljs'@'%';

1.3 查看用户权限

• 查看当前用户权限

show grants;

• 查看其他用户权限

show grants for ljs;

show grants for 'ljs'@'%';

1.4 向某个用户授权

grant select,update,insert,delete on test.* to 'ljs'@'%';

grant create,index on test.* to 'ljs'@'%';

grant all privileges on test.* to 'ljs'@'%';

grant select,update,insert,delete on text.* to 'ljs'@'%' with grant option;

•  with grant option: 可以将它拥有的权限授权给其他用户

1.5 修改某个用户的密码

alter user 'ljs'@'%' identified by '456';

1.6 删除某个用户的某些权限

revoke create,index on test.* from 'ljs'@'%';

revoke all on *.* from 'ljs'@'%';

1.7 创建一个角色

create role senior_dba;

-- 给角色授权
create all on *.* to senior_dba with grant option;

-- 用户与角色绑定
grant senior_dba to 'ljs'@'%';

2. 表管理

2.1 修改某张表的字符集

alter table t convert to character set utf8mb4;

• 会将表的原有列上的字符集都更改为utf8mb4
• 代价很大。要阻塞整张表。
• utf8mb4只对utf8做扩展

3. 设置排序内存缓冲区

3.1 查看排序缓冲区大小

show variables like 'sort_buffer_size';

3.2 修改排序缓冲区大小

-- 设置会话级别的，单位为字节
set sort_buffer_size=256*1024*1024;

-- 设置全局级别
set global sort_buffer_size=256*1024*1024;

• 如果排序没有利用的索引，则会用到该缓冲区
• 系统会为每个会话分配一个缓冲区，用于查询排序。

3.3 查看mysql与排序相关的status信息

show status like 'sort%';

4. 查看数据库信息（客户端下）

4.1 查询系统参数

-- 会话参数
show variables like 'sort_buffer_size';

-- 全局参数
show global variables like 'sort_buffer_size';

4.2 查看mysql某些信息的状态

-- 全局信息
SHOW GLOBAL STATUS like 'sort%';

-- 会话信息
show status like 'sort%'

5. 查看系统关于临时表的状态信息

show status like '%tmp%';