MariaDB 笔记

MariaDB 统计表大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='hs_spin'
order by data_length desc, index_length desc;

# 统计下所有库下的所有表的个数
SELECT TABLE_SCHEMA, COUNT(*) cnt FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA ORDER BY cnt desc;

MariaDB 数据批量备份

select concat("mysqldump -uroot -poldboy123  ",
            table_schema," ",table_name,">>","/backup/",
            table_schema,"_",table_name,".bak.sql")
            from information_schema.tables
            where table_schema='hs_spin';

select concat("mysqldump -uroot -poldboy123  ",
            table_schema," ",table_name,">>","/backup/",
            table_schema,"_",table_name,".bak.sql")
            from information_schema.tables
            where table_schema='test2';

SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
             TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
             TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = 'test2';

MySQL--导出表名及其列（包含注释）

-- MySQL--导出表名及其列（包含注释）
SELECT
    DD.*,
    TT.`表注释` 
FROM
    (
    SELECT
        TABLE_NAME 表名,
        COLUMN_NAME 列名,
        COLUMN_TYPE 数据类型,
        IS_NULLABLE 是否为空,
        COLUMN_DEFAULT 默认值,
        COLUMN_COMMENT 备注 
    FROM
        information_schema.COLUMNS 
    WHERE
--         (
--             TABLE_NAME LIKE 'plm_%' 
--         OR TABLE_NAME IN ( 'country', 'currency', 'warehouse' ))  AND 
        TABLE_SCHEMA = 'hs_spin'  ## 数据库
AND (TABLE_NAME IN ( 'hs_norm_config','mid_config' ))
    ) DD
    LEFT JOIN (
    SELECT
        table_name 表名,
        TABLE_COMMENT 表注释 
    FROM
        INFORMATION_SCHEMA.TABLES 
    WHERE
        table_schema = 'hs_spin' ## 数据库
AND (TABLE_NAME IN ( 'hs_norm_config','mid_config' ))) TT ON DD.`表名` = TT.`表名`
;

1、查询一个表中有多少个字段：

SELECT COUNT(*) FROM information_schema. COLUMNS
WHERE table_schema = '数据库名'
AND table_name = '表名';

2、查询一个数据库中有多少张表：

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   WHERE table_schema = '数据库名' GROUP BY table_schema;

3、查询一个数据库中一共有多少个字段：

SELECT COUNT(column_name) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '数据库名';

4、查询一个数据库中的所有表和所有字段、字段类型及注释等信息：

SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名' ;



统计MySQL中某个数据库中有多少张表
SELECT count(*) TABLES, table_schema FROM information_schema.TABLES   
where table_schema = '数据库名称' GROUP BY table_schema; 
 
统计MySQL中某个数据库中表记录数
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'pims_ptemp' order by table_rows desc;


select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'hs_spin' order by table_rows desc;




select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;



select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;


select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='hs_spin'
order by data_length desc, index_length desc;



//导出所有库
mysqldump -u 用户名 -p --all-databases > ~/xxx.sql
//一次可以导出多个库
mysqldump -u 用户名 -p --databases db1[db2] > ~/xxx.sql
//导出库或者库里面的某张表
mysqldump -u 用户名 -p dbname [tablename]> ~/xxx.sql

mysql -u 用户名 -p -D school -e 'select * from user where age>10' > ~/user.txt
source ~/school.sql
cat ~/data.sql|mysql -u root -p

select group_concat(COLUMN_NAME) from information_schema.COLUMNS where table_name = 'test_hu2';

##查看所有表信息
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'hs_spin';
##查看各个表数据量
SELECT table_name,table_rows FROM information_schema.tables
WHERE TABLE_SCHEMA = 'hs_spin' ORDER BY table_rows DESC;

MySQL 参数

show status like 'Com_______';
show global status like 'Com_______';
show session status like 'Com_______';

show status like 'Innodb_rows_%';
show global status like 'Innodb_rows_%';

show status like 'Connections%';
show global status like 'Connections%';

-- 以秒为单位
show status like 'Uptime%';
show global status like 'Uptime%';

-- 28.93488426
SELECT 2499974/3600/24;

show status like 'Slow_queries%';
show global status like 'Slow_queries%';


select @@have_profiling ;
-- set profiling=1; //开启profiling 开关；
show profiles;
-- show  profile for  query  query_id;
show  profile for  query  67;
show  profile cpu for  query  67;

-- all、cpu、block io 、context switch、page faults

select * from host;





542235273600
5422 3527 3600

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

select * from information_schema.optimizer_trace;


show status like 'Handler_read%';    --当前会话级别

show global status like 'Handler_read%';    --全局级别
show global variables like 'local_infile';
show variables like '%sort_buffer_size%'; 
SELECT 1048576/1024/1024; -- 1MB
SELECT 2097152/1024/1024; -- 2MB
SELECT 134217728/1024/1024; -- 128MB
SELECT 8589934592/1024/1024; -- 8192MB
SELECT 8192/1024; -- 8GB

show variables like 'max_length_for_sort_data'; 

SHOW VARIABLES LIKE 'have_query_cache';    
SHOW VARIABLES LIKE 'query_cache_type'; 
SHOW VARIABLES LIKE 'query_cache_size';
SHOW STATUS LIKE 'Qcache%';

select * from information_schema.engines;

-- now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

select now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() ;

SHOW VARIABLES LIKE 'key_buffer_size'; -- 查看大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- 查看大小

SHOW VARIABLES LIKE 'table_open_cache' -- 查看大小
show open tables；

show status like 'Table_locks%';
show variables like 'tx_isolation';
--  --查看当前表的索引 ：
 show  index  from test_innodb_lock ;
show  index  from host ;

show  status like 'innodb_row_lock%';

select sign(12),sign(-12);
select sign(-12);
-- mysql -h 127.0.0.1 -P 3306 -u root -p
    
-- mysql -h127.0.0.1 -P3306 -uroot -p2143    -- 可不加空格
mysql -uroot -p2143 db01 -e "select * from tb_book";
mysqladmin -uroot -p2143 create 'test01';  
mysqladmin -uroot -p2143 drop 'test01';
mysqladmin -uroot -p2143 version;

mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
    
mysqldump -uroot -p2143 -T /tmp test city
mysqlimport -uroot -p2143 test /tmp/city.txt
source /root/tb_book.sql
#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p2143 --count

#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count

#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count
show variables like 'log_error%';
tail -f /mysql/data/DBG01-DB-01.err

mysqlbinlog -vv mysqlbin.000002 
mysqldumpslow 
mysqldumpslow java-63-slow.log


show variables like 'long%';

show slave status;
show variables like '%slow_query_log%';
show global status like '%slow_queries%';

/* 模拟慢查询 */
select sleep(5);
select sleep(4);
select sleep(3);

/* 获取返回记录最多的 3 个 SQL */
mysqldumpslow -s r -t 3 /var/lib/mysql/bigdata01-slow.log

/* 获取访问次数最多的 3 个 SQL */
mysqldumpslow -s c -t 3 /var/lib/mysql/bigdata01-slow.log

/* 按照时间排序，前 10 条包含 left join 查询语句的 SQL */
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/bigdata01-slow.log

-- mysqldumpslow
-- 	常用参数
-- 	s 排序方式
-- 	r 逆序
-- 	l 锁定时间
-- 	g 正则匹配模式
-- 	标准语法
-- mysqldumpslow 各种参数 慢查询日志文件路径
-- 


show variables like '%profiling%';
show profiles;

/* 精确查询更多详情，Query_Id 参考上个语句的查询结果 */
show profile all for query 2;
show profile cpu, block io for query 2;

show variables like '%general_log%';

select * from mysql.general_log;
show open tables;


show status like '%table%'
show status like '%Table_locks_%'

# Table_locks_immediate 能够获取到的锁
# Table_locks_waited 需要等待的锁


-- Table_locks_immediate/Table_locks_waited> 5000
-- 建议采用 InnoDB 引擎
-- 否则使用 MyISAM 引擎
-- 能够获取到的资源充分时，使用行锁，因此采用 InnoDB
-- 

show status like '%innodb_row_lock%';

MySQL xxx