1、mysqldump 备份并压缩sql文件
mysqldump -h主机ip -u用户名 -p密码 数据库名 | gzip > 压缩后文件位置
mysqldump -hxxxx -uxxx -pxxxx -Pxxx --set-gtid-purged=OFF --max_allowed_packet=512M --databases xxxx --tables xxx > xxxx.sql
2、mysql直接用压缩文件恢复
gunzip < backupfile.sql.gz | mysql -u用户名 -p密码 数据库名
nohup mysql -hxxxx -uxxx -Pxxx -pxxxx --default-character-set=utf8 -f --database xxxx a.log &
--------导出csv 通过-N来去掉输出结果中列名，-s去掉输出结果中的各种划线
mysql -h xxxx -uxxx -Pxxx -pxxxx -e "select * from xxx.xxxxx" -N -s > test.csv
mysql -u -p -h -P database_name 结果.csv
简单语句就直接：
mysql -h xxxx -Pxxx -uxxx -pxxxx xxx -e "select * from xxx.xxxxx" > xxxxx.csv
-----------------------------------------------------------------------------
mydumper/myloader
--导出全库
nohup mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /data/xxx/xxx/xxx/dmp -G -c -R -E -t 8 &
----导出表结构 -d 选项
nohup mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /data/xxx/xxx/xxx/dmp -d -G -c -R -E -t 8 &
-----过滤部分库 -c 压缩
mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /home/xxxx/xxx/ --regex '^(?!(information_schema|mysql|performance_schema|sys))' -G -R -E -t 8 -c
-----只导出指定库
mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /home/xxxx/xxxx/ --regex '^(xxxx\.|xxxx\.)' -G -R -E -t 8 -c
---导出单个库
mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -B xxx -o /data/xxx/xxxx/dmp -G -c -R -E -t 8
---导出指定表
mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /data/xxx/xxx/dmp -B xxx -T xxx -G -R -E -t 8
------导出多个表
mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /data/xxx/xxx/dmp -B xxx -T xxx,xxx,xxx -G -R -E -t 8
----排除指定表
mydumper -h xxxx -P xxx -u xxx -p xxxx --skip-tz-utc -o /data/xxx/xxx/bak/ -B xxx -x '^(?!(xxx.xxxx|xxx.xxx))' -G -R -E -t 8
---------------------------------------------------
-----导入 ---存在的表会删除重建
nohup myloader -h xxxx -u xxx -P xxx -p xxxx -d /data/xxxx/dmp/ -e -o -t 8 &
---导入到新的库
-s 原来的database_name
-B 目标的database_name
-d 数据备份出来存放的目录，目录中不能包含其他无关文件或子目录
myloader -h xxxx -P xxx -u xxx -p xxxx -s xxx -B xxx_new -d /data/xxxx/dmp -e -o -t 8
-------------------------------------------------------------------------------------------------------------------
---my2sql:生成指定表指定时间段的回滚SQL或者redoSQL
--------------------------------------------
----正向解析：（2sql正常解析）
./my2sql -user xxx -password xxxx -host xxxx -port xxx -databases xxx -tables xxx -mode repl -work-type 2sql -start-file mysql-bin.000009 -start-datetime "2022-11-17 22:45:00" -stop-datetime "2022-11-17 22:50:00" -output-dir ./
----反向解析：（rollback反向解析）
./my2sql -user xxx -password xxxx -host xxxx -port xxx -databases xxx -tables xxx -mode repl -work-type rollback -start-file mysql-bin.000009 -start-datetime "2022-11-17 22:45:00" -stop-datetime "2022-11-17 22:50:00" -output-dir ./
---多个表
./my2sql -host xxxx -port xxx -user xxx -password xxxx -databases xxx -tables xxxx,xxxxx,xxx -mode repl -work-type rollback -start-file mysql-bin.000159 -start-datetime "2023-06-12 15:45:00" -stop-datetime "2023-06-12 17:45:00" -output-dir ./tmpdir0612
-------------从库报错处理
select * from performance_schema.replication_applier_status_by_worker;
-------------查看从库延时
show relaylog events in 'relay-log.001675' from 200191228
-------------跳过事务
stop slave ;
set global sql_slave_skip_counter=1;
start slave;
---------------------------
set @@session.gtid_next='5996b8aa-8b1d-11ec-b22d-fa16208c873e:1-3970391898';
begin;
commit;
set @@session.gtid_next=automatic;
start slave;
--------------------------------------------------------------------------------
---解析binlog
mysqlbinlog -vv mysql-bin.005649 > tt.txt
mysqlbinlog -vv --base64-output=decode-rows --start-position=338179 mysql-bin.000003 | less
--------------追加binlog
nohup /xxxxx/bin/mysqlbinlog mysql-bin.000992 --start-position=321118994 --stop-datetime="2022-10-19 16:30:00" |mysql -hxxxx -Pxxx -uxxx -pxxxxx &
-------------------------------------------修复主从gtid
stop slave;
reset master;
set global gtid_purged='1a03272e-ddbe-11ed-bec3-fa169799b197:1-429'; ----与主库对应
flush privileges;
start slave;
show slave status\G
-----CHANGE MASTER TO MASTER_HOST = 'xxxx', MASTER_USER = 'xxx', MASTER_PASSWORD = 'xxxx', MASTER_PORT =xxx,MASTER_AUTO_POSITION = 1;
----------------------pt online加字段
pt-online-schema-change --host=xxxx --user=xxx --password=xxxx P=xxx --alter="add xxx varchar(30) comment 'xxx',add xxx tinyint(1) comment'xxx'" D=xxx,t=xxx --no-check-replication-filters --execute
--------------------分析慢SQL
pt-query-digest slow.log --since '2023-07-05 11:30:00' --until '2023-07-05 12:00:00' > analyze.log
----------------------查看sql trace
SET SESSION optimizer_trace="enabled=on";
EXPLAIN select count(*) from t1 where id=2;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
----------------查看未提交的会话
select * from information_schema.innodb_trx where trx_operation_state is null\G
select * from information_schema.innodb_trx where trx_state='LOCK WAIT';
select * from information_schema.innodb_trx where trx_state='RUNNING';
-----查看锁
select * from information_schema.processlist where STATE='Waiting for table metadata lock';
------------
select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;
show variables like 'innodb_buffer_pool_size';
show engine innodb status \G
----------------------------------批量kill会话
select CONCAT('KILL ',id,';') FROM information_schema.`PROCESSLIST` where db = 'xxxx' and user='xxx';
-------------查看当前活跃会话
select ID,USER,HOST,DB,COMMAND,TIME,STATE,substr(INFO,1,100) from information_schema.processlist
where COMMAND not like 'Sleep%'
and COMMAND not like 'Binlog%'
and COMMAND not like 'Daemon%';
------------------------------------------------------------------------------------------------
-----查所有数据大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),’MB’) as data from TABLES;
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 = 'xxxx'
ORDER BY data_length DESC,index_length DESC;
---------------------
--大表
select table_schema,table_name,count(PARTITION_NAME) part_num,round(sum(data_length/1024/1024),2) as data_mb from information_schema.partitions s where partition_name is null group by s.table_schema,table_name order by 4 desc limit 10;
--字符集
select table_collation,count(1) from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') GROUP BY table_collation;
--存储引擎
select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') and engine'InnoDB';
-----索引 冗余索引：
select TABLE_SCHEMA,TABLE_NAME,redundant_index_name,redundant_index_columns from sys.schema_redundant_indexes;
无主键表：
select table_schema,
table_name,
round(sum(s.DATA_LENGTH / 1024 / 1024), 2) as data_MB
from information_schema.tables s
where table_name not in
(select distinct table_name
from information_schema.columns where column_key = "PRI")
AND table_schema
not in('mysql', 'information_schema', 'sys', 'performance_schema')
and table_type not in ('VIEW')
group by table_schema, table_name
order by 3 desc;
----碎片表
SELECT CONCAT(table_schema, '.', table_name) AS TABLE_NAME
,engine AS TABLE_ENGINE
,table_rows AS TABLE_ROWS
,CONCAT(ROUND(data_length / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE
,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE
,CONCAT(ROUND((data_length + index_length )
/ ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
,CASE WHEN data_length =0 THEN 0
ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE
,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB') AS TB_DATA_FREE
,CASE WHEN (data_length + index_length) = 0 THEN 0
ELSE ROUND(data_free/(data_free+data_length + index_length),2)
END AS TB_FRAG_RATE
FROM information_schema.TABLES
WHERE table_type like '%TABLE%'
ORDER BY data_free DESC limit 10;
-----------------------------------------------
innodb_thread_concurrency=cpu*2
innodb_buffer_pool_size内存的50%
show variables like 'innodb_thread_concurrency';
show variables like 'innodb_buffer_pool_size';
