docker

查看表占用空间

select TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
where TABLE_SCHEMA='fleets_wealth'
group by TABLE_SCHEMA
;
select TABLE_Name,
concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
where TABLE_SCHEMA='fleets_wealth'
group by TABLE_NAME
order by sum(data_length) desc
;

导出导入数据库表

mysqldump
mysqldump -u username -p database_name table1 table2 table3 > tables.sql
mysqldump -h 127.0.0.1 -P 3307 -u root -p admin_pay > admin_pay.sql
mysqldump -h game-stat.cdwe3wedmyc3.ap-southeast-1.rds.amazonaws.com -P 3306 --no-data -u admin -p game_stat > game_stat.sql
gFp9POKTc4DYsxda

mysql
mysql -u username -p database_name < database.sql
mysql -h 127.0.0.1 -P 3306 -u root -p
    create database admin_pay DEFAULT CHARACTER SET utf8 default COLLATE utf8_general_ci;
mysql -h 127.0.0.1 -P 3306 -u root -p admin_pay < admin_pay.sql
mysql -h 127.0.0.1 -P 3306 -u root -p game_stat < game_stat.sql
mysql -h 127.0.0.1 -P 3307 -u root -p game_stat < game_stat.sql

创建用户并赋权

CREATE USER 'readonly'@'%' IDENTIFIED BY 'xxxxxxxxxxxxxxxxxxxx';
GRANT SELECT ON db_order.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

创建数据库

create database gogs DEFAULT CHARACTER SET utf8 default COLLATE utf8_general_ci;
CREATE USER 'gogs'@'%' IDENTIFIED BY 'gogs';
GRANT all ON gogs.* TO 'gogs'@'%';
FLUSH PRIVILEGES;

状态

show OPEN TABLES where In_use > 0;
show engine innodb status;  -- LATEST DETECTED DEADLOCK  有死锁信息

---

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 jaytp@qq.com