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
mysqldump -hdbbk-paykeydb.siod-mdc.serv -uwangyusen_r -pT1QgajPr28L6yj -P6020 --database bfb_db --tables t_bank_channels t_channel_info --skip-lock-tables>  paykeydb_bfbdb.sql
mysql -h127.0.0.1 -uroot -proot -P3306 --database bfb_db < paykeydb_bfbdb.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;