数据库操作笔记

MySQL日常操作记录

背景说明

不是一个DBA,在我司DBA离职之后代为处理数据库日常事项,写此文档记录一些常用命令。

binlog清理

1
2
show binary logs;
purge binary logs to 'mysql-bin.001769';

命令行执行SQL

dba@localhost:(none) 15:37:15>source /var/lib/mysql-files/hc_cli_clinic_center.sql

如果报错一般为目录没权限,使用/tmp临时目录再执行

数据库授权

需要root用户下操作

1
GRANT SELECT, INSERT, UPDATE, DELETE ON `database_name`.* TO user@'192.168.1.%'  identified by 'password';

导出数据为CSV文件

1
select concat('\'',id) as 订单号, status as 订单状态, create_time  where abc = 1111 and  create_time >= '2021-09-28 00:00:00' and create_time <= '2021-10-22 00:00:00' and status != 'U' into outfile '/var/lib/mysql-files/output_filename.csv';

统计表大小

1
2
3
4
5
6
7
8
9
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='database_name'
order by data_length desc, index_length desc ;

列出所有已经产生碎片的表

1
2
3
select table_schema db, table_name, data_free, engine     
from information_schema.tables 
where table_schema not in ('information_schema', 'mysql')  and data_free > 0;

表空间整理

1
alter table `database`.`table` engine=InnoDB;

物理备份到其他机器

  • 安装innobackupex工具
1
2
3
yum install epel-release -y
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
yum install percona-xtrabackup-24 percona-toolkit.x86_64 -y
  • 进行备份
1
2
3
4
5
6
7
# 备份到本地
innobackupex --defaults-file="/etc/my.cnf" --user=dba --password='M' --stream=tar "/tmp/backup" 2> /tmp/backup.log | gzip > /mnt/backup/user_all.tar.gz
# 备份到远端
innobackupex --defaults-file="/etc/my.cnf" --user=dba --password='M' --stream=tar "/tmp/backup" 2> /tmp/backup.log | ssh root@192.168.21.209 "cat - > /mnt/user_all_backup.tar"
# 备份到远端并压缩
innobackupex --defaults-file='/etc/my.cnf' -u'dba' -p'Mxxx' --stream=tar /tmp/backup |ssh root@192.168.21.244 "gzip -> /volume1/db_back/innobackupex/full_20210909.tar.gz"

  • 还原
1
2
3
4
5
rm -rf /var/lib/mysql/*
innobackupex --defaults-file='/etc/my.cnf' --apply-log /mnt/backup/
innobackupex --defaults-file='/etc/my.cnf' --copy-back /mnt/backup/
chown -R mysql:mysql /var/lib/mysql
service mysqld start
updatedupdated2021-12-132021-12-13