记录赤峰项目数据库异常处理办法

1、查看磁盘空间，100%

df -h

2、查找大文件，删除之

du -h --max-depth=1 

3、查看mysql无法启动，日志中出现异常，修改/etc/my.cnf

innodb_force_recovery=4

重新启动，成功

 4、备份数据

date_str=`date +%Y%m%d`

mkdir -p /data2/backup

cd /data2/backup

mysqldump -h localhost -uroot --password=***** -R -E -e  dsideal_db  > /data2/backup/dsideal_db_$date_str.sql

5、清库重新导入

 

# 重新初始化

cd /usr/local/mysql
scripts/mysql_install_db --user=mysql --datadir=/usr/local/db/mysql/data

service mysql start
mysqladmin -u root password 'DsideaL147258369'
mysql -uroot -pDsideaL147258369
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "DsideaL147258369";
flush privileges;

 

#创建新的
create database dsideal_db;

cd  /data2/backup/

mysql -uroot -p******

use dsideal_db;

# 禁用binlog
set session sql_log_bin=0;

# 设置批量插入缓存为256MB
SET  session  BULK_INSERT_BUFFER_SIZE=1024*1024*256;

# 批量插入优化
set global innodb_flush_log_at_trx_commit=0;

# 禁用外键

SET foreign_key_checks = 0;

# 导入数据包
source dsideal_db_20190428.sql;

# 恢复外键

SET foreign_key_checks = 1; 

# 启用binlog
set session sql_log_bin=1;

# 批量插入优化结束
set global innodb_flush_log_at_trx_commit=2;

 # 只还原部分表的办法

# 备份的指令
mysqldump -h127.0.0.1 -uroot -pDsideaL147258369  -R -E -e shl_db  > /usr/local/Backup/shl.sql

#从全库备份中抽取出t表的表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t_kq_info`/!d;q' /usr/local/Backup/shl.sql

# 抽出
grep 'INSERT INTO `t_kq_info`'  /usr/local/Backup/shl.sql >>  /usr/local/Backup/t_kq_info.sql