查看数据库后台进程状态
show processlist;只列出前100条，如果想全列出请使用show full processlist; 

查看表结构
show columns from t_user_detail_final;
查看数据库
show databases;
查看表
show tables;

查看innodb_buffer大小

select @@innodb_buffer_pool_size/1024/1024/1024;

设置innodb_buffer大小为16G

set global innodb_buffer_pool_size =17179869184;

查看mysql数据库账号的授权信息

show grants for [email protected]’%’;

查询行数前十的数据量

SELECT table_schema,table_name,table_type,ENGINE,table_rows,avg_row_length,concat(round(data_length/1024/1024,2),’MB’) AS data_length,concat(round(index_length/1024/1024,2),’MB’) AS index_length,concat(round(data_free/1024/1024,2),’MB’) AS data_free,concat(round((data_length+index_length)/1024/1024,2),’MB’) AS total_size FROM information_schema.TABLES WHERE table_schema NOT IN (‘information_schema’,’performance_schema’,’mysql’,’sys’) ORDER BY table_rows DESC LIMIT 10;

账号授权

mysql -uhomed [email protected]@[email protected] “GRANT ALL PRIVILEGES ON *.* TO ‘homed’@’%’ identified by ‘[email protected]@[email protected]’ WITH GRANT OPTION”;

数据库的备份命令

 备份所有的数据库

mysqldump -uroot -pZBGD&123&456 –single-transaction –events –triggers   –set-gtid-purged=OFF –routines –flush-logs –master-data=2 –all-databases > bak_all_first.sql

备份单个数据库

mysqldump -uroot -h192.168.52.205 -p’123&[email protected]&ZbGd’ –set-gtid-purged=OFF homed_ilog t_user_hits_info > t_user_hits_info.sql

mysqldump -hlocalhost -udbbackup -pdbbackup -P3306 –quote-names –skip-opt –add-locks –extended-insert –disable-keys –set-charset –create-options –single-transaction -q –no-autocommit -R -E –default-character-set=utf8 –master-data=2 -A>XXX.sql

使用mydumper工具备份数据库
time /usr/local/bin/mydumper -h 192.168.10.31 -u root -p ‘[email protected]_ipanel123’ –build-empty-files –long-query-guard 300 –kill-long-queries -c –threads=4 –regex ‘^(?!(sys|information_schema|performance_schema|tpcc1000))’ -G -E -R -o /r2/baksql/201907022000_slave1 &
time /usr/local/bin/mydumper -h 192.168.10.33 -u root -p ‘[email protected]_ipanel123’ –build-empty-files –long-query-guard 300 –kill-long-queries -c –threads=4 –regex ‘^(?!(sys|information_schema|performance_schema|tpcc1000))’ -G -E -R -o /r2/baksql/201907022000_slave3 &
time /usr/local/bin/mydumper -h 192.168.10.35 -u root -p ‘[email protected]_ipanel123’ –build-empty-files –long-query-guard 300 –kill-long-queries -c –threads=4 –regex ‘^(?!(sys|information_schema|performance_schema|tpcc1000))’ -G -E -R -o /r2/baksql/201907022000_slave5 &

使用备份的数据恢复数据库

/usr/local/bin/myloader –host=127.0.0.1 –user=root –password=test**** –port=3306 –overwrite-tables -q 50000 –threads 10  –enable-binlog -d /r2/baksql/201809211021/

恢复slave的库可以去掉–enable-binlog

/usr/bin/myloader –host=192.168.1.0 –user=root –password=111111 –port=3306 –overwrite-tables -q 50000 –threads 10 -d /r2/baksql/202009250939/

使用mysqlbinlog工具查看sql

注意:

不要查看当前正在写入的binlog文件

不要加–force参数强制访问

如果binlog格式是行模式的,请加 -vv参数

使用mysqlbinlog工具查看sql
mysqlbinlog –start-datetime=”2018-11-08 17:30:54″ –stop-datetime=”2018-11-08 17:47:53″ binlog.001443 | grep delete | grep user_watching | wc -l

 –set-gtid-purged=OF