查询数据库里没有主键的表
SELECT table_schema, table_name,TABLE_ROWS
FROM information_schema.tables
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = 'PRI'
)
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');
mysql 连接数据库常用
-p --password 连接数据库密码
-P --port 连接数据库端口号
-u --user 指定连接的用户名
-h --host 指定主机信息
-S 指定mysql.sock
-e "" 执行SQL语句
mysql -uroot -p111111 -S /data/3306/mysql.sock testdb < 123.sql 可以指定库执行SQL语句
mysql -uroot -p'111111' -h 10.10.10.190 -P 3309 # 远程登录mysql
-- 不跳出mysql命令行执行系统命令
mysql> system cd /root
mysql> system pwd
/data/3307
-- 清除mysql操作历史记录
> /root/.mysql_history
mysql查看基本信息和状态
# 命令使用帮助
help drop user
help grant
select version(); # 查看数据库版本
select user(); # 查看当前登陆用户
select now(); # 查看当前时间
show master status; # 查看当前binlog
show engine innodb status\G # 来查看死锁日志
reset master; # 清除binlog日志文件(如果有数据库主从,一定不能执行此命令*****************)
flush logs; # 刷新log日志,自此刻开始产生一个新编号的binlog日志文件,mysqld服务重启时,会自动执行此命令,刷新binlog日志,刷新不影响数据库主从,备份的时候也可以加-F 参数,
grep -E -v "#|\/|^$|--" t483_shangtua_c_20190827_171324.sql # 筛选SQL语句
mysql -uroot -p111111 -e "show full processlist;"|egrep -vi "sleep" # 不进入数据库查看慢语句
show full processlist # 进入数据库查看慢语句,可以通过kill id 杀死语句
SELECT SQL_NO_CACHE * FROM TEST WHERE NUM =7645; 测试sql语句的效率,不用缓存来查询
mysql -uroot -p111111 -U # 在mysql命令加上选项-U后,当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时,mysql程序拒绝执行
flush tables with read lock; # 数据库只读锁定命令,防止导出数据库的时候有数据写入
unlock tables # 数据库解除锁定
数据备份和恢复
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -E -F -R --single-transaction --master-data=2 --triggers --ignore-table=linyaohong.test linyaohong|gzip >/backup/all.sql.gz
# 恢复的时候首先确定备份的时候有没有加-B参数
gunzip < /backup/all.sql.gz|mysql -uroot -p111111 -S /data/3306/mysql.sock
mysql -uroot -p111111 wordpress2018</home/bak_wordpress2018-12-23.sql
mysql用户管理 密码等操作
# 设置root密码,
update user set password=password(111111) where user='root';
# 无密码设置密码
mysqladmin -u root password 111111 -S /data/3309/mysql.sock
# 有密码设置密码
mysqladmin -u root -p123123 password 111111 -S /data/3309/mysql.sock
# 5.7方式不同
update mysql.user set authentication_string=password('Aa111111') where user='root'
# 忘记root密码
skip-grant-tables # 配置文件里添加 启动免授权服务端
# 查看mysql所有的用户
select distinct user,host from mysql.user;
select * from mysql.user \G #比较详细(包含各种权限)
MySQL [mysql]> select Host,User,Password,Select_priv,Grant_priv from user;
+-------------+------------+-------------------------------------------+-------------+------------+
| Host | User | Password | Select_priv | Grant_priv |
+-------------+------------+-------------------------------------------+-------------+------------+
| localhost | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y | Y |
| 127.0.0.1 | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y | N |
| % | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y | N |
| % | linyaohong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | N |
| localhost | linyaohong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | N |
| 127.0.0.1 | linyaohong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | N |
+-------------+------------+-------------------------------------------+-------------+------------+
9 rows in set (0.00 sec)
# Select_priv 确定用户是否可以通过SELECT命令选择数据
# Grant_priv 确定用户是否可以将已经授予给该用户自己的权限再授予其他用户。
例如,如果用户可以插入、选择和删除foo数据库中的信息,并且授予了GRANT权限,
则该用户就可以将其任何或全部权限授予系统中的任何其他用户
# 删除用户
delete from mysql.user where user="root" and host="localhost";
delete from mysql.user where not (user='root');
delete from mysql.user where user='';
drop database test;
use mysql;
delete from db; # 选择mysql数据库以后 清空db表
# 或者
use mysql;
drop user linyaohong@'%'; # 删除用户任意IP链接的权限
drop user linyaohong@'localhost';
drop user linyaohong@'127.0.0.1';
show databases; # 显示所有的数据库
select database(); # #查看当前所在的库
use day03; # 进入(选择)day03数据库
show tables; # 显示day03库里所有的表
SELECT * from auth_user # 查看表里所有的数据,后面加 \G 代表分组
mysql> desc userinfo; # 查看表结构 看索引不是很详细/可使用show index from userinfo;
# 备注:
# Key -- RPI primary key 主键 (一般情况下只有一个主键)
如果表中有两个主键则 两个字段作为联合主键
# Key -- UNI 唯一键(唯一索引)
# key -- MUL 基本上是一个索引,它既不是主键,也不是唯一键
mysql创建数据库和用户权限等
# 创建数据库
create database wordpress default character SET utf8 COLLATE utf8_general_ci;
create database testdb charset "utf8";
create database linyao;
show create table userinfo\G # 查看创建表的语句
show create database linyao; #查看数据库创建的语句(包含创建的编码等)
drop table if exists student; # 删除钱判断数据库是否存在
drop database student # 删除数据库
#清空数据库中的表 userinfo为表名
use 数据库名;
delete from userinfo; # 清空userinfo表,插入新数据时,id从原来的基础上自增
truncate table userinfo; # 清空userinfo表,插入新数据时,id从1开始自增
drop table userinfo # 删除表
# 授权用户权限(如果用户不存在同时创建用户) #授权全部权限 ALL privileges或者只写ALL
grant ALL privileges on myweb.* to 'linyaohong'@'localhost' IDENTIFIED BY '123456';
grant SELECT on myweb.* to 'linyaohong'@'127.0.0.1' IDENTIFIED BY '123456';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on myweb.* to 'linyaohong'@'%' IDENTIFIED BY '123456';
# % 可以指定IP段 10.0.% 10.0.0.%
# with grant option 代表用户有权限再授权给其他用户
grant ALL privileges on myweb.* to 'linyaohong'@'localhost' IDENTIFIED BY '123456' with grant option;
grant SELECT on myweb.* to 'linyaohong'@'127.0.0.1' IDENTIFIED BY '123456' with grant option;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on myweb.* to 'linyaohong'@'%' IDENTIFIED BY '123456' with grant option;
# 或者分成两步
mysql> create user linyaohong@'10.10.10.%' identified by '111111';
mysql> grant select on *.* to linyaohong@'10.10.10.%';
# 刷新授权结果
flush privileges;
# 回收权限
revoke all privileges on *.* from 'yearning'@'%';
revoke all privileges on *.* from 'yearning'@'127.0.0.1';
revoke all privileges on *.* from 'yearning'@'localhost';
# 查看授权结果
# ALL: 允许做任何事(和root一样)
# USAGE: 只允许登录--其它什么也不允许做
show grants for linyaohong@'localhost';
show grants for linyaohong@'127.0.0.1';
show grants for linyaohong@'%';
select * from mysql.user\G
# 删除用户的授权
revoke ALL privileges ON myweb.* from 'linyaohong'@'localhost';
mysqladmin常用命令
mysqladmin -u root password 111111 -S /data/3307/mysql.sock # 无密码设置密码
mysqladmin -uroot -p111111 password 111111 -S /data/3306/mysql.sock # 有修改密码
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock shutdown # 关闭mysql
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock flush-logs # 重新生成二进制日志,或者重新生成慢日志
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock status # 查看MYSQL少量状态
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock -i 1 status # 没1秒刷新一次状态显示
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock extended-status # 查看MYSQL全部状态
watch -d -n1 mysqladmin -uroot -p111111 -S /data/3306/mysql.sock processlist # 没1秒查看一次慢日志
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock processlist -i 1 # 没1秒查看一次慢日志
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock variables|grep query_log # show variables
# mysql 每秒钟查询次数、插入次数、删除次数、更新次数的统计 (可以做定时任务按天取出,然后对比)
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock extended-status|grep -Ew "Com_select|Com_insert|Com_update|Com_update|Com_delete"
# 只取查询次数
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock extended-status|grep -w Com_select|awk '{print $4}'
mysql查看一些状态变量是否生效
mysql -uroot -p111111 -S /data/3306/mysql.sock -e "show variables"
mysql -uroot -p111111 -S /data/3306/mysql.sock -e "show variables like 'server_id%'"
mysql -uroot -p111111 -S /data/3306/mysql.sock -e "show variables like '%slow%'"
mysql -uroot -p111111 -S /data/3306/mysql.sock -e "show variables like 'log_bin%'" # 查看当前是否开启binlog
show variables like 'character%'; # 查看数据库编码
mysql> show global variables like "key_buffer_size";
mysql> set global key_buffer_size=1024*1024*64; # 动态调整,不重启mysql生效
mysql> show global variables like "key_buffer_size";