Login light

数据和索引占用空间

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M'),concat(round(sum(index_length/1024/1024),2),'M')
from information_schema.tables
where table_schema='bfb_stat_db' AND table_name like 't_pay%';

人工rowsnumber,来自cy讨论组大神的方案

UPDATE test t1, 
(
select
 *, (select count(*) from test where a = t.a and sys_no <= t.sys_no) as rowNums
from test t
) t2
set t1.b = t2.rowNums
where t1.sys_no = t2.sys_no

导出

mysqldump -uroot -p123456 -d dbname > dbname.sql
mysqldump -uroot -p123456 -t dbname > dbname.sql
-- 导出dbname表结构和数据
mysqldump -uroot -p123456 dbname > dbname.sql

设置密码

use mysql;
update user set password=password('root') where user='root';
flush privileges;
exit;

查看所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;