目录
一,常用基础SQL
查看数据库版本
select version();
登陆数据库
mysql -u queuecloud -p
linux执行SQL
source 路径/datebase.sql(SQL文件)
show databases;(查看数据库)
show tables;(查看选中数据库下的所有表)
use mysql(使用数据库);
数据库打开防火墙端口3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
查询用户(表)详细信息
select Host,User,Create_priv,password_last_changed from user;
查看mysql.db表 指定用户的库权限
select Host, Db ,User from mysql.db where User='zhangshuai';
查看用户对单个数据列的权限
select Host,User from mysql.columns_priv where User like 'zhangshuai';
查看用户对单个表的权限
select Host,User from mysql.tables_priv where User like 'zhangshuai';
查询数据库用户网段
select user,host from mysql.db where user='zzzd';
查看用户权限
#show grants for 用户@'网段';
show grants for zhzcdb@'10.253.163.%';
增加用户权限
#grant 权限级别 on `库名` . * to '用户名'@'用户访问范围';
grant select,insert,update,delete,create on `db_zzd` . * to `username`@'10.209.xx.%';
revoke撤销用户权限
#revoke..alter on..from
revoke insert,select,update,delete,drop,create,alter on huanqiu.* from wang@'%';
#revoke..from
revoke SELECT,INSERT,UPDATE,DELETE,CREATE ON `zzzddb`.* from 'zhzcdb'@'10.253.163.%';
mysql刷新权限,提交命令
FLUSH PRIVILEGES; #flush privileges;权限刷新
commit;#提交
修改用户访问IP范围
#rename user ..to
rename user zhzcdb@'%' to zhzcdb@'10.253.163.%';
修改数据库用户密码
UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
#5.7版本以下
update mysql.user set authentication_string=password('密码') where User="用户" and Host="10.209.6.%";
数据库备份
数据库备份恢复:mysql -u root -p test</home/test1.sql
数据库备份(忽略gtid信息):mysqldump -u root -p --set-gtid-purged=OFF userdb > userdb.sql
单库备份:mysqldump -uroot -p test >/download/testbak_$(date +%F).sql
压缩备份:mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
多库备份;mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz
单表备份:mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql
备份脚本
#!/bin/sh
##########################################
#this scripts create by root of mingongge
#create at 2016-11-11
#######################################
ip=`grep 'IPADDR' /etc/sysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}'`
#定义服务器IP变量
BAKDIR=/backup
#定义备份路径
[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
#判断如果不存在这个路径就创建一个,为了服务器多的时候方便看
DB_PWD="mingongge"
DB_USER="root"
MYSQL="/application/mysql/bin/mysql"
MYSQL_DUMP="/application/mysql/bin/mysqldump"
DATA=`date +%F`
####bak data of test's databses####
DB_NAME=`$MYSQL -u$DB_USER -p$DB_PWD -e "show databases;"|sed '1,5d'`
#定义数据库变量
for name in $DB_NAME
#for循环语句取库名
do
$MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz
#全库备份
[ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name}
#判断这个路径,为了区别哪个库的备份文件
for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`
#for循环语句取表名
do
$MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz
#分表备份
done
done
参考:MySql数据库备份的几种方式 - KillerTwo - 博客园
查看存储过程函数
查询数据库中的存储过程和函数
select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' //存储过程
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' //函数
show procedure status; //存储过程
show function status; //函数
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
查看视图
SELECT * from information_schema.VIEWS //视图
SELECT * from information_schema.TABLES //表
查看触发器
SHOW TRIGGERS [FROM db_name] [LIKE expr]
SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G
检查是否开启binlog
#查看mysql的配置文件/etc/my.cnf
show variables like '%log_bin%';
# 查看详细的日志配置信息
show global variables like '%log%';
# mysql数据存储目录
show variables like '%dir%';
# 查看binlog的目录
show global variables like "%log_bin%";
# 查看当前服务器使用的biglog文件及大小
show binary logs;
# 查看主服务器使用的biglog文件及大小
# 查看最新一个binlog日志文件名称和Position
show master status;
# 事件查询命令
# IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] :偏移量(不指定就是0)
# row_count :查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 查看 binlog 内容
show binlog events;
# 查看具体一个binlog文件的内容 (in 后面为binlog的文件名)
show binlog events in 'master.000003';
# 设置binlog文件保存事件,过期删除,单位天
set global expire_log_days=3;
# 删除当前的binlog文件
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';
# 删除指定日志文件
purge master logs to 'master.000003';
参考:mysql查看binlog日志 - 沧海一滴 - 博客园
返回表的详细信息
show columns from 表名;
创建/删除数据库
create database 数据库名称 ;(注意分号结尾)
drop database old_db;
创建数据库用户
grant all privileges on 数据库名称.* to 数据库用户名@'授权范围' identified by '数据库用户名密码';
grant all privileges on wxactivity.* to mysqluser@'%' identified by 'mysqluseradmin';(这是例子,%代表授权全网,localhost代表授权本机)
修改数据库表字段
ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型(修改后的长度);
新增默认为空的字段
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 DEFAULT NULL;
新增不为空的字段
ALTER TABLE 表名ADD COLUMN 字段名 字段类型 NOT NULL;
删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;
修改表名
rename table 原表名 to 新表名;
#使用RENAME TABLE命令修改表名,将表移动到新的库里
rename table old_db.tb to new_db.tb;
参考:安全快速修改Mysql数据库名的5种方法 - 星朝 - 博客园
数据库容量
查看所有数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
查看所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
查看指定数据库容量大小
#例:查看mysql库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';
查看指定数据库各表容量大小
#例:查看mysql库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;
mysql查看当前的字符编码方法
show variables like'character%';
mysql删除某个用户
DROP USER 'linuxidc'@'localhost';
mysql删除某个库中的所有表
SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'databasesname';
查单个表空间情况
select df.BYTES/1024/1024/1024,df.* from dba_data_files df where df.TABLESPACE_NAME='CPOE_DATA'
查看表空间使用情况
select a.tablespace_name as "表空间名","最大空间(GB)","占用空间(GB)",("占用空间(GB)"-"剩余空间(GB)") as "使用空间(GB)",round(("占用空间(GB)"-"剩余空间(GB)")/"占用空间(GB)"*100,2) as "使用率1(%)"
,round(("占用空间(GB)"-"剩余空间(GB)")/"最大空间(GB)"*100,2) as "使用率2(%)"
from (select tablespace_name,sum(decode(AUTOEXTENSIBLE,'NO',bytes,'YES',maxbytes))/1024/1024/1024 as "最大空间(GB)",sum(bytes)/1024/1024/1024 as "占用空间(GB)" from dba_data_files group by tablespace_name) a
,(select tablespace_name,sum(bytes)/1024/1024/1024 as "剩余空间(GB)" from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 6 desc;