============================基础=======================================
基础
查看数据库
show databases;
简单建立数据库
create database lijinghua; #这样直接建立所采用的字符集和编译安装指定的字符集相同
指定字符集建库
CREATE DATABASE oldgirl CHARACTER SET gbk COLLATE gbk_chinese_ci; ### COLLATE为校队规则
查看建库语句
show create database oldgirl\G
修改字符集
ALTER DATABASE oldgirl CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
删库
drop database lijinghua;
切库
use lijinghua;
查看当前所在的库
select database();
查看库里的表
show tables;
查看用户
select user,host from mysql.user;
查看表的字段
desc lijinghua;
进入mysql中使用linux命令？
mysql> system pwd
/root
创建用户
============================创建用户====================================
限制网段创建用户及授权所有权限
CREATE USER 'rootziyoudao'@'172.18.252.%' IDENTIFIED BY '123456';
grant all privileges on *.* to 'rootziyoudao' identified by '123456';
flush privileges;
不限制网段创建用户及授权database1查询权限
CREATE USER 'rootziyoudao'@'%' IDENTIFIED BY '123456';
grant select on database1.* to 'rootziyoudao' identified by '123456';
flush privileges;
运维人员常用
grant all on *.* to 'rootziyoudao'@'localhost' identified by '123456';
flush privileges;
注意
其实用户可主机域都可以不带单引号的，但是密码必须加单引号，另外的话，创建用户不需要flush privileges。
删除用户
============================删除用户==========================
drop删除
drop user 用户名@'%';
delete删除
如果drop删除不了用户，很可是是用户或主机部分是特殊字符或大写内容等，此时可以用下面方式删除，以bbs用户，172.16.1.%主机为例，具体处理命令及操作过程为：
delete from mysql.user where user='bbs' and host='172.16.1.%';
flush privileges;
更改密码
=============================更改密码======================================
跳表(重启)
skip-grant-tables
MySQL5.7更改密码
use mysql;
update user set authentication_string=PASSWORD("这里输入你要改的密码") where User='root'; #更改密码
update user set plugin="mysql_native_password"; #如果没这一行可能也会报一个错误，因此需要运行这一行
flush privileges; #更新所有操作权限
use mysql;
update user set authentication_string=PASSWORD("123456") where User='root';
update user set plugin="mysql_native_password";
flush privileges;
#登录进去 还需要重新set下密码
SET PASSWORD = PASSWORD('123456');
flush privileges;
MySQL5.6及以下
update mysql.user set password=password('123456') where user='root' and host='localhost';
flush privileges;
update mysql.user set password=password('123456') where user='root' and host='%';
flush privileges;
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456" WITH GRANT OPTION;
flush privileges;
查看
=================================查看=========================
MySQL的变量分为以下两种：
1）系统变量：配置MySQL服务器的运行环境，可以用show variables查看
2）状态变量：监控MySQL服务器的运行状态，可以用show status查看
查看所有存在的用户及授权的地址范围
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
select user,host from mysql.user;
查看用户拥有什么权限（注意区段等）
show grants for dbuser@'%';
show grants for dbuser@'localhost';
注意哦，不同的区段可以拥有不同的密码及所授予的权限
查看binlog
只查看第一个binlog文件的内容
show binlog events;
查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';
查看当前正在写入的binlog文件
show master status\G
获取binlog文件列表
show binary logs;
授权
============================授权=====================================
远程授权用户所有权限
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "swl19960706" WITH GRANT OPTION;
flush privileges;
远程授权用户查询权限
grant select on *.* to @'%' identified by "123456" WITH GRANT OPTION ;
flush privileges;
限制网段授权用户权限
grant select,create,insert,update,delete,alter,drop,lock tables on dbname.* to deployop@'192.168.1.%' identified by 'password';
flush privileges;
花样授权操作
给个别的库权限
grant select,create,insert,update,delete,alter,drop,lock tables on dbname.* to deployop@localhost identified by 'memect';
flush privileges;
给连接外网权限
grant select,create,insert,update,delete,alter,drop,lock tables on dbname.* to deployop@'%' identified by 'memect';
flush privileges;
给所有的库权限
·
grant select,create,insert,update,delete,alter,drop,lock tables on *.* to deployop@'localhost' identified by 'memect';
flush privileges;
取消权限
======================取消授权============================================
收回权限(当然要先知道他有什么权限)
REVOKE INSERT ON *.* FROM rootziyoudao@localhost;
flush privileges;
注意
我们可以在一些场景下取消删除的权限
revoke create,drop on *.* from 'rootziyoudao'@'172.16.1.%';
End
修改于2018.07.26
for name in mysql -e "show databases;"|sed 1d
do
mysqldump -uroot -p'password' -B $name
done