数据库常用命令

1、展示数据库

show DATABASES;

2、进入information_schema 数据库（存放了其他的数据库的信息）

use information_schema;

3、查询所有数据的大小：

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

4、查看指定数据库的大小：

比如查看数据库home的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='jianhaihug_67test';

5、查看指定数据库的某个表的大小

比如查看数据库home中 members 表的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';
6、查看指定数据库的每个表的大小

SELECT table_name, data_length + index_length AS len, table_rows,CONCAT(ROUND((data_length + index_length)/1024/1024,2),'MB') AS datas
FROM information_schema.tables
WHERE table_schema = 'sys';

7.查询photos数据库中的albums_index_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='photos' AND TABLE_NAME='albums_index_1' ;

 

redis 使用命令

获取列表长度
LLEN key 
获取列表指定范围内的元素
LRANGE key start stop 
test-hug-service-work test-hug-service-warn test-hug-service-task test-hug-service-special test-hug-service-social test-hug-service-selfarchive test-hug-service-resource test-hug-service-maternalchildbook test-hug-service-dynamic test-hug-service-disease test-hug-service-call

查看数据类型
type key

查看hash的值
hkeys commonParam:064172514
查看hash的值的key的值
hmget commonParam:064172514 ACCESS_BY_DTE

数据库表分区相关语句功能
#判断当前MySQL是否支持分区？
show variables like '%partition%';
#查看表分区情况
select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions  where table_schema = schema()  and table_name='test'; 
#创建新表，并且表分区，以月为单位
CREATE TABLE `apdailysts_p` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ap_id` INT(11) NOT NULL,
  `mac` VARCHAR(17) NOT NULL,
  `liveinfo` LONGTEXT NOT NULL,
  `livetime` INT(11) NOT NULL,
  `stsdate` DATE NOT NULL,
  `lastmodified` DATETIME NOT NULL,
   PRIMARY KEY (`id`, `stsdate`)
) 
PARTITION BY RANGE COLUMNS(stsdate) (
    PARTITION p0 VALUES LESS THAN ('2016-06-01'),
    PARTITION p1 VALUES LESS THAN ('2016-07-01'),
    PARTITION p2 VALUES LESS THAN ('2016-08-01'),
    PARTITION p3 VALUES LESS THAN ('2016-09-01'),
    PARTITION p15 VALUES LESS THAN MAXVALUE
)

#直接修改表分区，会创建临时表，耗费资源，覆盖式修改以前的分区
#需要将字段修改为date 类型，并添加至主键
ALTER TABLE t_interface_questionnaire PARTITION BY RANGE (Month(send_time))
(   
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2017-10-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2017-11-01')), 
PARTITION p_Dec VALUES LESS THAN MAXVALUE )

统计指定库每张表的大小
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='lanniu' ORDER BY data desc;
统计指定库的大小
select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size from information_schema.tables where table_schema = 'freeswitch';

mysql 5.6 创建用户并赋权

CREATE USER 'admin_sa'@'%' IDENTIFIED BY 'M2Yz?NGY3Mjdh';
GRANT ALL PRIVILEGES ON *.* TO 'admin_sa'@'%' IDENTIFIED BY 'M2Yz?NGY3Mjdh';
FLUSH PRIVILEGES;

给mysql管理员配置密码

方法1： 用SET PASSWORD命令

　　mysql -u root

　　mysql> SET PASSWORD FOR 'admin'@'%' = PASSWORD('newadmin');

方法2：用mysqladmin

　　mysqladmin -u root password "M2Yz_NGY3Mjdh"

　　如果root已经设置过密码，采用如下方法

　　mysqladmin -u root -p123456 password "newpass"

方法3： 用UPDATE直接编辑user表

　　mysql -u root

　　mysql> use mysql;

　　mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

　　mysql> FLUSH PRIVILEGES;

 

在丢失root密码的时候，可以这样
1.在[mysqld]中添加
skip-grant-tables
2.先停止数据库，再命令行执行
mysqld_safe --skip-grant-tables

　　mysql -u root mysql

　　mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

　　mysql> FLUSH PRIVILEGES;
iptables -I INPUT -p tcp --dport 3306 -j ACCEPT