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

待我称王封你为后i 2024-03-25 22:17 74阅读 0赞

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';