centos7 安装mysql
数据库安装参考:
http://www.cnblogs.com/longrui/p/6071581.html
https://www.cnblogs.com/yoursoul/p/6264059.html
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
http://www.zhimengzhe.com/shujuku/MySQL/89138.html
1、进入information_schema 数据库
use information_schema;
2、查询所有数据的大小:
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
3、查看指定数据库的大小:
比如查看数据库home的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';
4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';
5、查看表和索引占用空间
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 tables where table_schema='task_db' and table_name = 'adclick_table';
6、查看某库下所有表的大小
select table_schema,table_name,concat(round(DATA_LENGTH/1024/1024,2),'MB') as data from tables where table_schema='数据库名' order by data_length desc;
创建库
create database task;
use task;
alter database task character set utf8;
grant all privileges on task.* to 'root'@'%' identified by 'password';
7、导库数据
数据导出
mysqldump -uroot -p databasename > databasename.sql
数据导入
use dbname;
source databasename.sql
创建索引:
ALTER TABLE `table_name` ADD INDEX index_id ( `id` )
查看索引:
show index from import_data
随机取一条数据:
SELECT * FROM import_data AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM import_data)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1
=====================
数据库锁住处理
查看所有进程
show full processlist;
查看所有事物
select * from information_schema.innodb_trx;
如果有锁住的,kill 掉
=====================
mysql 8.0.12 修改root密码
use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
FLUSH PRIVILEGES;