mysql 常用命令 （备查）

mysql常用操作(备查)

pt-archiver删除

/usr/bin/pt-archiver \
--source h=localhost,u=root,p=mypasswordxxxxx,P=3366,D=database,t=tablename \
--no-check-charset \
--where 'changeDate < 1612108800000' \
--sleep=1 \
--progress 2000 \
--limit 2000 \
--txn-size 500 \
--no-check-charset \
--progress=3000 \
--statistics \
--purge; 

gh加字段

nohup gh-ost --user="dba_ghost" \
--password="!xxxxxxxxxx" \
--host=127.0.0.1 --port=3388 \
--database="databasexxxxxx" \
--table="tablexxxxx" \
--alter="ADD COLUMN name1 decimal(16,2) NOT NULL DEFAULT '0.00' COMMENT '这是一个备注', \
ADD COLUMN name2 int NOT NULL DEFAULT '1' COMMENT '这是一个备注'" \
--panic-flag-file=/tmp/gh-ost.panic.flag \
--allow-on-master \
--replica-server-id=8808  \
--execute &

新建表后校验

select TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.tables where table_name='xxxxxxxxxxx';

修改表结构后校验

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where  table_name='tttttttt' and COLUMN_NAME='cccccccc';

添加索引后

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.COLUMNS where TABLE_NAME='xxxxxx' and COLUMN_NAME in ("xxxxxx","xxxxxx");

查看表创建时间

#所有表
SELECT table_name,create_time FROM information_schema.TABLES;
#指定表
SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name = 'table_name';

KILL数据库链接

下面列举出SQL只是拼接出 kill 链接的语句

• 杀掉空闲时间大于2000s的链接

  select concat('KILL ', id, ';') from information_schema.`processlist` where command = 'Sleep'     and time > 200;
  

• 杀掉处于某状态的链接

  select  concat( 'KILL ' ,id, ';' ) from   information_schema.`processlist` where  state like 'Creating sort index';
  

• 杀掉某个用户的链接

  select  concat( 'KILL ',id, ';' ) from   information_schema.`processlist` where user='root';
  

拼接创建数据库语句（排除系统库）

select concat(
        'create database ',
        '`',
        schema_name,
        '`',
        ' default character set ',
        default_character_set_name,
        ';'
    ) as CreateDatabaseQuery
from information_schema.schemata
where schema_name not in (
        'information_schema',
        'performance_schema',
        'mysql',
        'sys'
    );

拼接创建用户语句 （排除系统用户）

select concat (" create user \'",
        user,
        "\'@\'",
        host,
        "\' IDENTIFIED WITH \'mysql_native_password\' AS \'",
        authentication_string,"\' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;"
    )
from mysql.`user`
where `User` not in ('root', 'mysql.session', 'mysql.sys');

用于批量修改密码

select concat(
        'create user ',
        user,
        '@',
        '`',
        host,
        '`',
        ' identified by ',
        "'",
        right(to_base64(authentication_string), 12),
        "'"
    )
from mysql.user
where host not in('localhost', '127.0.0.1')
    and user not like '%_v1'
order by user;

查看整个实例空间占用大小

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
information_schema.`TABLES`;

查看各个库占用大小

select
 TABLE_SVHEMA,
 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`
group by
 TABLE_SCHEMA;

查看单个库占用空间大小

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
 information_schema.`TABLES`
where
 table_schema = 'test_db';

查看单个表占用空间大小

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
  information_schema.`TABLES`
where
  table_schema = 'test_db'
and table_name = 'tbname';

查看某个库下所有表的碎片情况

select
 t.TABLE_SCHEMA,
 t.TABLE_NAME,
 t.TABLE_ROWS,
 concat( round( t.DATA_LENGTH / 1024 / 1024, 2 ), 'M' ) as size,
 t.INDEX_LENGTH,
 concat( round( t.DATA_FREE / 1024 / 1024, 2 ), 'M' ) as datafree
from
 information_schema.`TABLES` t
where
 t.TABLE_SCHEMA = 'my_test'
order by
 datafree desc;

收缩表，减少碎片

alter table tb_name engine = innodb;
optimize table tb_name;

查找某一个库无主键表

select
 table_schema,
 table_name
from
 information_schema.`TABLES`
where
 table_schema = 'test_db'
and table_name not in (
 select
   table_name
 from
information_schema.table_constraints t
 join information_schema.key_column_usage k USING
(
   constraint_name,
   table_schema,
   table_name
 )
 where
   t.constraint_type = 'PRIMARY KEY'
  and t.table_schema = 'test_db'
);

查找除系统库外 无主键表

select
 t1.table_schema,
 t1.table_name
from
 information_schema.`TABLES` t1
left outer join information_schema.TABLE_CONSTRAINTS t2 on t1.table_schema = t2.TABLE_SCHEMA
and t1.table_name = t2.TABLE_NAME
and t2.CONSTRAINT_NAME in ('PRIMARY')
where
  t2.table_name is NULL
and t1.TABLE_SCHEMA NOT IN (
 'information_schema',
 'performance_schema',
 'mysql',
 'sys'
);