mysql工具

# 备份单库
mysqldump -u root -p -B db1 \
    --master-data=2 --single-transaction --flush-logs > db1-2020-05-09.sql

mysqldump -u wait -p \
    --single-transaction \
    --set-gtid-purged=OFF --databases db1 > local_wp.sqk


# 备份表结构
mysqldump -u root -p -d --add-drop-table db1 > 2020-05-11_db1.sql

mysqldump -u root -p --set-gtid-purged=OFF me_data_move > 0805-me_data_move.sql

# 只导出 insert 语句, 不生成创建表的动作
mysqldump --no-create-info db1 t1 t2 t3 > t_123.sql

# 备份 多个库
mysqldump -u root -p --set-gtid-purged=OFF -B db1 db2 db3 > 0806-all.sql

# 备份多个库
mysqldump -h db-xxx-uat.xxxx.ap-southeast-1.rds.amazonaws.com -P 13306 -u admin -p \
  --databases db1 db2 db3  > uat-db-20240813.sql

# 恢复数据
mysql -u root -p < 0806-all.sql

# 恢复数据
mysql -u root -p $db_pw -S /data/mysql_13306/mysql.sock  < newsql.sql

-- 创建用户
CREATE USER 'user1'@'10.255.10.%' identified by 'passw0rd';

-- 早期方式
CREATE USER 'user1'@'10.255.10.%' IDENTIFIED WITH mysql_native_password;
grant all on db1.* to user1@'10.255.246.%' identified by '123456';

-- 删除用户
delete from user where user='user1' and host='10.255.246.%';

-- 刷新权限
flush privileges;

-- 查看权限
show grants for user@'%';

-- 授权
GRANT ALL ON *.* TO 'wait'@'%';

-- 修改密码
alter user 'root'@'localhost' identified by 'passw0rd';


set password=password('KdpTkiChO0MD!U&jyV@R');
-- 修改当前用户的密码

-- 单独设定指定用户的的连接数
ALTER USER 'root'@'%' WITH MAX_USER_CONNECTIONS 100;

# 临时启动数据库
nohup mysqld_safe --defaults-file=/home/wait/mysqldb/my.cnf &

# 停止服务
mysqladmin -u root -p -P 3306 shutdown


# 关闭数据库 - 对于 mysqld_safe 启动的情况
mysqladmin -u root -p -S mysqldb/mysql.sock shutdown

-- 临时设置为只读 - 所有库
set read_only=on;
set super_read_only=on;

-- 临时配置
set global sync_binlog=0;
set global innodb_flush_log_at_trx_commit=0;
set global max_connections=8000;

-- 持久化修改系统变量
-- 8.0+ 的 persist 将修改会持久化到临时文件中, 重启有效, 自动写入 mysqld-auto.cnf 文件
SET PERSIST max_connections = 200;

set persist read_only=on;
set persist super_read_only=on;

SET PERSIST_ONLY max_connections = 200;     -- 只持久化, 重启才生效

-- 清空持久化的配置
-- 只是清空 mysqld-auto.cnf 和 performance_schema.persisted_variables 中的内容
reset persist

show databases;               -- 显示所有数据库
use dbname;                   -- 进入数据库

create database if not exists localtest character set 'utf8mb4';


drop database db1;           -- 删除数据库

show tables;                  -- 显示all表

--- 修改表结构
alter table tablename modify id int(10) not null primary key auto_increment;

truncate table t_name;
drop table t_name;

desc tables;                    -- 查看表结构
show create table t_1;          -- 查看表 DDL


CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    owner VARCHAR(20),
    sex CHAR(1),
    birth DATE
);

CREATE TABLE `t_test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '入库时间',
    `key` VARCHAR(50) NOT NULL COMMENT '名称',
    `num` INTEGER NOT NULL default '0',
    `body` VARCHAR(50) NOT NULL COMMENT '名称'
);

-- 生成 TRUNCATE 全部表的 sql
SELECT CONCAT('TRUNCATE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'db1';

-- 测试慢查询是否成功记录
select sleep(6);

SET GLOBAL general_log = 1;

-- 查看审计日志文件路径
show variables like 'general%log%';

-- 查看是否开启自动提交
show global variables like '%autocommit%';

-- 查看运行中的事务详细信息
select * from information_schema.INNODB_TRX \G;

-- 查看事务线程id和客户端信息
show processlist

-- 关闭某个线程id
kill 469273630

# 之后所有查询都会写入此文件
mysql> pager cat > /tmp/test.txt;

# login-path 设置快速登录
mysql_config_editor set --login-path=logdb --user=user1 --host=10.27.10.10 --port=3306 --password

mysql_config_editor print --all

mysql_config_editor remove --login-path=logdb

mysql --login-path=logdb    # 快捷登录

select version();
show global status like 'uptime';   -- 查看运行时长
show databases;         -- 显示所有数据库
show processlist;       -- 查看会话信息
show warnings;          -- 查看最近的报错信息
show variables;         -- 查看所有参数变量
show global variables;
show global status;     -- 状态

show engine innodb status\G

-- 连接数设置
show global variables like '%max_connect%';
show global variables like 'thread_cache_size%';     -- 线程池设定大小

show status like 'Connections';         -- 启动以来的连接总次数
show status like 'threads%';
SHOW GLOBAL STATUS LIKE 'Threads_connected';    -- 查看活跃连接数

-- 查看 root 用户的最大连接数量, - 如果为0则使用全局配置
SELECT USER, HOST, MAX_USER_CONNECTIONS FROM mysql.user WHERE USER = 'root';

-- 活跃用户 - 排除repl
select count(1) from processlist where state <> '' and user <> 'repl' and time > 2;

select * from socket_instances;
select * from performance_schema.users;
select * from performance_schema.hosts;

mysql> show status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 3     |       # 此刻已经建立的线程中有多少空闲线程
| Threads_connected | 807   |       # 当前已经建立的连接数
| Threads_created   | 810   |       # 代表从最近一次服务启动，已创建线程的数量
| Threads_running   | 2     |       # 代表当前激活的（非睡眠状态）线程数

mysql> show variables like 'log_bin';
mysql> show variables like 'sync_binlog';
mysql> show variables like 'innodb_flush_log_at_trx_commit';

SHOW GLOBAL VARIABLES LIKE 'open_files_limit';
SHOW GLOBAL VARIABLES LIKE 'flush';
SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';
SHOW GLOBAL VARIABLES LIKE '%buffer_size%';

show variables like 'innodb_commit_concurrency';
show variables like 'innodb_page_cleaners';
show variables like '%page_cleaner%';

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW GLOBAL VARIABLES LIKE 'innodb_io_capacity';
SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';

-- 查看数据库大小
SELECT
    table_schema AS '数据库名',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS '索引大小(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;


-- 查看数据库大小 - 含表数量
SELECT
    table_schema AS 'Database',
    COUNT(*) AS 'Tables',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'Index Size (MB)',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)',
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free Space (MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;


-- 查询表行数 和 数据大小 和 索引大小
SELECT table_schema, table_name, table_rows,
    data_length/1024/1024 as SIZE_T_MB,
    index_length/1024/1024 as SIZE_I_MB
FROM information_schema.tables WHERE table_schema = 'mytest';


-- 查询自增id的使用情况
select
    table_schema,table_name,engine,Auto_increment
from
    information_schema.tables
where
    INFORMATION_SCHEMA.tables.TABLE_SCHEMA not in ("information_schema","performance_schema","sys","mysql");

mysql -uroot -p -e "show processlist" |grep -v Sleep

SELECT * FROM information_schema.`PROCESSLIST` WHERE command != "Sleep" ORDER BY time DESC;

-- 生成 kill 执行时间超过 5 分钟的sql
SELECT
    concat( 'kill ', id, ';' )
FROM
    information_schema.PROCESSLIST
WHERE
    command != 'Sleep'
    AND time > 5 * 60
ORDER BY
    time DESC;

-- 查看当前未关闭的事务
select * from information_schema.INNODB_TRX \G;

INSERT INTO t1 VALUES ('cwx', 'chenwx', 'n', '1990-03-30');

insert into tablesname(字段1,字段2,字段3) values(值,值,值);
insert into tablesname(字段1,字段2,字段3) values(值,值,值),(值,值,值),(值,值,值);

UPDATE t1 SET name = 'cwx1' where owner = 'chenwx';

DELETE FROM t1 where name = 'cwx';

select * from t_name limit 5;

-- 去重显示(即每项目只显示一次)
SELECT DISTINCT c1 FROM teacher;


-- 多查询
SELECT * FROM t1 WHERE degree IN (85, 86, 88);

-- 区间查询
SELECT * FROM t1 WHERE degree BETWEEN 60 AND 80;
SELECT * FROM t1 WHERE degree > 60 AND degree < 80;

-- 按月分组
SELECT DATE_FORMAT(insert_time,'%Y%m') months,count(id) from t1 group by months;

-- 分渠道去重
select  t.spid,count(distinct t.login_name) from t1 t group by t.spid;


-- 子查询
select name,math from t1 where math=(select max(math) from t1);
select a,b,c from a where a IN (select d from b where c='3');

select a.name from
    (select * from table_name where classess="数学") a,
    (select * from table_name where classess="语文") b
    where a.court<b.court and a.name=b.name;

-- 统计
SELECT
    SUM(CASE WHEN is_logoff = 0 THEN 1 ELSE 0 END) AS 有效用户,
    SUM(CASE WHEN is_logoff = 1 THEN 1 ELSE 0 END) AS 注销用户
FROM
    t_user;

-- 查询重复行
SELECT ctime, COUNT(*) AS count FROM t1 GROUP BY ctime HAVING COUNT(*) > 1;

-- 列出重复行
SELECT * FROM t1 WHERE ctime IN (SELECT ctime FROM t1 GROUP BY ctime HAVING COUNT(*) > 1);


-- 查找具有重复 code 值的条目
select * from products group by code having count(code)>1;
    -- 按 group by 分组后, 就已经完成了合并
    -- having count(code)>1  表示过滤出数量大于 1 条的内容

-- 查找重复的数据
select * from t_host_info where 1=1 group by random_num having count(random_num) >1;

-- 类型转化
CAST(t.body AS CHAR)

inet_aton()     -- 将ip地址转换成数字型 10.1.249.55 -> 167901495
inet_ntoa()     -- 将数字型转换成ip地址

show master status;         -- 查看当前binlog位置
show slave status\G         -- 查看复制状态
show master logs;           -- 查看 binlog 文件
flush logs;                 -- 轮转binlog日志

-- 清理之前的 binlog
PURGE MASTER LOGS TO 'mysql-binlog.000336';

reset master;

-- 查看跳过错误的配置
show variables like 'slave_skip_errors';

show variables like '%gtid%';

-- 创建
CHANGE MASTER TO
  MASTER_HOST='10.2.1.5',
  MASTER_PORT=3306,
  MASTER_USER='sync',
  MASTER_PASSWORD='passw0rd',
  GET_MASTER_PUBLIC_KEY=1,              -- 8.x 非 ssl 同步时需要获取公钥
  MASTER_LOG_FILE='binlog.000022',
  MASTER_LOG_POS=476432;

mysql -u root -p -e "show slave status\G;" | grep _Running