记录赤峰项目数据库异常处理办法
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;
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
