mysql管理语句以及相关备份恢复

1. 数据库备份语句
1.1 全备份
xtrabackup --defaults-file=/usr/local/mysql/mysql6606.cnf --user=$user --password=$passwd --port=$port --socket=$socket --use-memory=4G --parallel=4 --no-lock --backup --target-dir=$full_backup_dir
1.2 增量备份
1.2.1 第一次增量
innobackupex --defaults-file=/usr/local/mysql/mysql6606.cnf --user=root --password=123 --incremental --incremental-basedir=/backup/full /backup/inc1
1.2.2 第二次增量
innobackupex --defaults-file=/usr/local/mysql/mysql6606.cnf --user=root --password=123 --incremental --incremental-basedir=/backup/inc1 /backup/inc2
1.2.3 逻辑备份
mysqldump -uroot -p -R -E --max-allowed-packet=256M --single-transaction --set-gtid-purged=OFF -P6606 -S/data/mysql_6606/mysql.sock --databases tybss_report >/data/tybss_report0509.sql
1.2.4 备份库，但忽略一些表
mysqldump -uroot -p -R -E --max-allowed-packet=256M --single-transaction --set-gtid-purged=OFF -P6606 -S/data/mysql_6606/mysql.sock panda_rcs --ignore-table=panda_rcs.match_event_info --ignore-table=panda_rcs.standard_sport_market_flowing --ignore-table=panda_rcs.rcs_standard_sport_market_sell_flowing --ignore-table=panda_rcs.bak_match_statistics_info_detail --ignore-table=panda_rcs.standard_sport_market_odds --ignore-table=panda_rcs.rcs_monitor_error_log --ignore-table=panda_rcs.standard_sport_market --ignore-table=panda_rcs.rcs_monitor_mq_info > ./panda_rcs.sql
1.2.5 只备份特定表的表结构
mysqldump -uroot -p -R -E --max-allowed-packet=256M --single-transaction --set-gtid-purged=OFF -P6606 -S/data/mysql_6606/mysql.sock -d panda_rcs match_event_info standard_sport_market_flowing rcs_standard_sport_market_sell_flowing bak_match_statistics_info_detail standard_sport_market_odds rcs_monitor_error_log standard_sport_market rcs_monitor_mq_info match_statistics_info_flowing rcs_monitor_garbage_collector > table.sql

2. 数据库恢复
2.1全备份恢复
xtrabackup --defaults-file=/usr/local/mysql/mysql6606.cnf --prepare --target-dir=$full_backup_dir
xtrabackup --defaults-file=/usr/local/mysql/mysql6606.cnf --host=localhost --user=root --password=mysql123 --port=6606 --datadir=/data/mysql_6606 --copy-back --target-dir=/db_backup/pro_15/backup/6626/full/20200618_053001
2.2 增量备份恢复
2.2.1 全备整理
innobackupex --apply-log --redo-only /backup/3306/full
2.2.2 第一次备份合并full
innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
2.2.3 第二次备份合并第一次
innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full
2.2.4 最后一次整理
innobackupex --apply-log /data/backup/full

3. binlog截取
show master status
show binlog events in 'mybinlog.****'
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
3.1 截取一段节点的binlog，截取这段gtid组的操作。--exclude-gtids排除gtids。这里可以后面跟多个binlog文件。
mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql
3.2 根据位置截取
mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
3.3 根据时间截取
mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004
3.4 查看二进制日志
mysqlbinlog --defaults-file=/usr/local/mysql/mysql6606.cnf --start-datetime='2020-05-01 19:00:00' --stop-datetime='2020-05-01 19:10:00' --base64-output=decode-rows -v mybinlog.000009 > /tmp/8.sql

4. 主从复制
use mysql;select * from slave_master_info;
stop slave;
reset slave all;
change master to master_host='10.5.11.21',master_user='repl',master_port=6606,master_password='RwFdd6ZpoKcfD07VgyHr',master_auto_position=1;
start slave;

5. ceph挂载db_backup
mount -t ceph 10.5.17.202:6789:/ /db_backup -o name=admin,secretfile=./admin.key

6. 数据库启动和关闭
6.1 启动服务
/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/mysql6606.cnf &
6.2 关闭服务
mysqladmin -uroot -p -S /data/mysql_6606/mysql.sock shutdown

6. 主从不一致需要跳过gtid操作
slave:
stop slavel;
set @@session.gtid_next='ed41df0a-05dc-11ea-a4b0-000c293a9237:520485'
begin;commit;
set @@session.gtid_next='automatic';

7. 慢日志，以查询数和查询行数排序
mysqldumpslow -s cr slow.log

8. 用户管理：
8.1 创建用户
create user test@'%' identified by '123';
grant select on *.* to test@'%';
8.2 更改密码
alter user test@'%' identified by '456'
8.3 查看用户权限
show grants for root@'localhost';
8.4 取消权限
revoke select on *.* from test@'%';

9. 死锁管理命令：
9.1 查看死锁状态
show status like 'innodb_row_lock%';
9.2 查看锁源
use sys
select locked_table,waiting_trx_id,waiting_pid,blocking_trx_id,blocking_pid from innodb_lock_waits\G
9.3 查找锁源id
select thread_id from performance_schema.threads where processlist_id=25;
9.4 找到锁源的sql语句
select * from performance_schema.events_statements_current where thread_id=65\G

10. cetus管理
10.1 启动
/usr/local/cetus/bin/cetus --defaults-file=/usr/local/cetus/conf/proxy.conf &
10.2 管理接口
/usr/bin/mysql -h10.5.22.42 -uadmin -P9606 -pRt57qHm8Xp91aFo3uExD
select * from backends
10.3 手动切换写读
/usr/bin/mysql -h10.5.22.42 -uadmin -P9606 -pRt57qHm8Xp91aFo3uExD -e update backends set state='up' , type='rw' where address='10.5.22.41:6616';

11. 删除二进制日志
mysql> show master logs;
+-----------------+------------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+------------+-----------+
| mybinlog.004356 | 1073742346 | No |
| mybinlog.004357 | 1073749103 | No |
| mybinlog.004358 | 1073742486 | No |
| mybinlog.004359 | 1073747316 | No |
| mybinlog.004360 | 554989663 | No |
| mybinlog.004361 | 1073741982 | No |
| mybinlog.004362 | 1073743396 | No |
| mybinlog.004363 | 1073769737 | No |
| mybinlog.004364 | 1073742162 | No |
| mybinlog.004365 | 1073755841 | No |
| mybinlog.004366 | 1073742882 | No |
| mybinlog.004367 | 1073780415 | No |
| mybinlog.004368 | 1073743509 | No |
| mybinlog.004369 | 1073741875 | No |

查看当前binlog日志文件
mysql> show master status;'
+-----------------+-----------+--------------+------------------+---------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+-----------+--------------+------------------+---------------------------------------------------+
| mybinlog.004385 | 115387258 | | | 31654e9c-1197-11ea-8f74-000c29d32f03:1-4466952829 |
+-----------------+-----------+--------------+------------------+---------------------------------------------------+

删除二进制日志
mysql> purge binary logs to 'mybinlog.004383';

12. 初始化数据库
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/opt/app1/data/mysql_6666

1、登录到mysql查看binlog
只查看第一个binlog文件的内容
mysql> show binlog events;
查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000002';
获取binlog文件列表
mysql> show binary logs;
如果需要过滤，只查询insert，update，delete的语句，可以这样写：
mysqlbinlog --no-defaults --database=raceEnroll binlogs.000078 |grep update |more
如果需要查询2017-09-17 07:21:09到2017-09-19 07:59:50 数据库为geeRunner 的操作日志，输入如下命令将数据写入到一个备用的txt即可。
mysqlbinlog --no-defaults --database=geeRunner --start-datetime=2017-09-17 07:21:09 --stop-datetime=2017-09-19 07:59:50 binlogs.000080 > sanjiaomao.txt
--insecure-registry 192.168.111.143:8088
查看数据库的执行频率
show 【session|global】 status;
查询自本次登陆以来的数据库操作，主要关心值的获取
show session status like 'Com_insert%';
show session status like 'Com_select%'
show session status like 'Com_update%';
show session status like 'Com_delete%';
查询自数据库服务启动以来的数据库操作，主要关心值的获取
show global status like 'Com_delete%';
show global status like 'Com_insert%';
show global status like 'Com_update%';
show global status like 'Com_select%';
查询自数据库innodb引擎的数据库操作，主要关心值的获取的是影响的行数
show session status like 'InnoDB_rows%';
show global status like 'InnoDB_rows%';
查看数据库的链接次数包含成功与不成功
show global status like 'connections';
show session status like 'connections';
查看数据库的工作的时间秒数
show session status like 'Uptime';
show global status like 'Uptime';
查看数据库的满查询的次数
show global status like 'Slow_queries';