一、查看库的大小
1、查看库大小方法一：select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 from information_schema.tables where table_schema='databasename';select table_schema,(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 from information_schema.tables where table_schema not in ('information_schema','performance_schema','mysql') group by table_schema;
2、查看所有库所有数据的大小：select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables;select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'G') from information_schema.tables;
二、查看表的大小
1、查看表大小方法一：select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
from information_schema.tables where
table_schema='asset'
and table_name = 'asset_lender_loan_dtl_log';select table_name ,round((sum(data_length/1024/1024/1024)+sum(index_length/1024/1024/1024)),2) as per_table_length_GB
from information_schema.tables where
table_schema='hengyd' group by table_name order by per_table_length_GB desc;select table_name ,round((sum(data_length/1024/1024)+sum(index_length/1024/1024)),2) as per_table_length_MB
from information_schema.tables where
table_schema='hengyd' group by table_name order by per_table_length_MB desc;
三、导出整个库进行备份库的搭建操作
1、导出mysqldump -udba -p‘密码’ -S /data/mysql/3307/run/mysql.sock --single-transaction --opt --master-data=2 --all-databases --events --routines --triggers > ./mysqldumpfiles_31_3307_201906140936.sql
2、排除某些库mysql -e "show databases;" -uroot -p密码 | grep -Ev "Database|information_schema|mysql|test|performance_schema|tmp" | xargs mysqldump -uroot -p密码 --single-transaction --opt --skip-add-drop-table -d --databases > /root/sqlscripts/mysql_192-168-71-223_dump.sql
3、还原数据库mysql -hhostname -uusername -ppassword databasename
4、还原压缩的MySQL数据库gunzip
5、还原压缩的MySQL数据库gunzip
四、创建用户
CREATE USER 'jeffrey3'@'localhost' IDENTIFIED BY 'lallalal456';
grant all privileges on lalala.* to fsh_grant@192.168.56.70 identified by 'lallalal456';
修改密码
mysql中没有alter user语法，修改密码是：
方法一：mysql> set password for 'usertest'@'localhost'=PASSWORD('passwordtest');
Query OK, 0 rows affected (0.00 sec)
方法二：mysql> update mysql.user SET Password=PASSWORD('usertest') where user='usertest' and host='localhost';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
五、权限管理
查看用户权限
查看当前用户(自己)权限：mysql> show grants;
查看其他 MySQL 用户权限：mysql> show grants for fsh_grant@192.168.1.1;
+----------------------------------------------------------------------------------------------------------------------+
| Grants for fsh_grant@192.168.1.1 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fsh_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '111111111111' |
| GRANT ALL PRIVILEGES ON `lalala`.* TO 'fsh_grant'@'192.168.1.1' |
+----------------------------------------------------------------------------------------------------------------------+
查看表的授予权限：mysql> select * from mysql.tables_priv;
查看库级权限字典表：mysql> select * from mysql.db;
授予权限
授予库级权限mysql> GRANT USAGE ON *.* TO 'fsh_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '111111111111' WITH MAX_QUERIES_PER_HOUR 3 MAX_CONNECTIONS_PER_HOUR 100；
grant 普通 DBA 管理某个 MySQL 数据库的权限。mysql>grant all privileges on testdb to dba@'localhost'
其中，关键字 “privileges” 可以省略。
授予表级权限，授予一个用户一张表的所有权限，包括Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show,view,Trigger权限mysql> grant all on mysql.users to usertest@localhost
回收权限mysql> revoke select on mysql.db from 'fsh_grant'@'192.168.1.1';
注1：USAGE权限是用户一经创建就会有用，并且无法通过REVOKE语句回收。下面的语句虽然执行成功，但是没有回收revoke权限mysql> revoke usage on *.* from 'fsh_grant'@'192.168.1.1' ;
注2：mysql数据库中的权限，操作时授予和回收的权限级别(priv_level)必须对应，否则无法成功收回。mysql> revoke all privileges on *.* from 'fsh_grant'@'192.168.1.1'; --没有收回权限
mysql> REVOKE SELECT ON `mysql`.`db` FROM 'fsh_grant'@'192.168.1.1'; --成功收回权限
注3：回收用户的所有权限(USAGE权限还是没能回收)mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'fsh_grant'@'192.168.1.1';
user/db/host几个字典表中，host列的值对大小写不敏感，User，Password，Db和Table_name几个列值对大小写敏感。Column_name列值对大小写不敏感。
mysql历史命令记录存放在当前操作系统用户的根目录下的.mysql_history文件中。可以通过软连接到/dev/null的方式禁用mysql命令的历史记录，如下所示：ln -f -s /dev/null ~/.mysql_history
六、字符类型转化
1，string类型字符转化为datetime类型
mysql语句中把string类型字段转datetime类型是使用str_to_date()把字符串转换为日期select * from h_hotelcontext where now() between STR_TO_DATE (Start_time,'%Y-%m-%d %H:%i:%s') and STR_TO_DATE(End_time,'%Y-%m-%d %H:%i:%s');
注：'%Y-%m-%d %H:%i:%s'格式为：2012-10-11 16:42:30
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)
返回一个表示形式的unix_timestamp 参数为“YYYY-MM-DD HH：MM：SS'或YYYYMMDDHHMMSS格式的值，根据是否使用该函数在字符串或数字的上下文。该值表示在当前的时区。unix_timestamp是一个内部的时间戳值，例如由UNIX_TIMESTAMP()函数所产生的一样。
如果格式给出，结果被格式化的格式字符串，它是用DATE_FORMAT()函数中的条目相同的方式列出。mysql> SELECT FROM_UNIXTIME(875996580); +---------------------------------------------------------+ | FROM_UNIXTIME(875996580) | +---------------------------------------------------------+ | 1997-10-04 22:23:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec)mysql> SELECT UNIX_TIMESTAMP(); -> 1447431666 mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19'); -> 1447431619 mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012'); -> 1447431619.012
取一开的开始时间SELECTstr_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s');
取第二天的开始时间select DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY)
取一天的结束时间selectDATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY),INTERVAL -1 SECOND);
七、查看存储过程定义
查询数据库中的存储过程
方法一：select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
方法二：show procedure status;
查看存储过程或函数的创建代码show create procedure proc_name;
show create function func_name;
最后于 2019-7-29
被矢量比特编辑
，原因：
8192
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
