MySQL日常运维常用命令 Mysql

Wordjian 4月前 113

一、查看库的大小


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 < backupfile.sql

4、还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

5、还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename


四、创建用户


 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;
最后于 4月前 被愚人乙编辑 ,原因:
最新回复 (0)
    • 运维开源项目互助社区—致敬开源
      2
        立即登录 立即注册 
返回