centos mysql 非正常关闭导致数据库损坏的恢复及开启自动邮件备份

昨天在进行网站用户维护的时候，发现网站无法登陆，发现是后台mysql没有启动。
反复重启服务器无果，手动启动mysql

service mysql start 

提示
Starting MySQL..The server quit without updating PID file (/usr/local/mysql/var/www.mydomain.com.pid).[FAILED]
搜索了一下 Starting MySQL..The server quit without updating PID file 相关的信息，尝试了一些方法无效，似乎也不对症。
于是查看log：

tail -f -n 100 /usr/local/mysql/var/www.mydomain.com.err

看到如下信息：

181113 12:23:09 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
181113 12:23:09 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.48) starting as process 1204 ...
181113 12:23:09 [Note] Plugin 'FEDERATED' is disabled.
181113 12:23:09 InnoDB: The InnoDB memory heap is disabled
181113 12:23:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
181113 12:23:09 InnoDB: Compressed tables use zlib 1.2.3
181113 12:23:09 InnoDB: Initializing buffer pool, size = 16.0M
181113 12:23:09 InnoDB: Completed initialization of buffer pool
181113 12:23:09 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 8659431036
181113 12:23:09  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 8659436630
InnoDB: Error: trying to access page number 3489660288 in space 0,
InnoDB: space name /usr/local/mysql/var/ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
181113 12:23:09  InnoDB: Assertion failure in thread 140223550441248 in file fil0fil.c line 4578
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
04:23:09 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=0
max_threads=500
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 406149 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0x76193e]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x41a)[0x655caa]
/lib64/libpthread.so.0(+0xf7e0)[0x7f8856a827e0]
/lib64/libc.so.6(gsignal+0x35)[0x7f88557185e5]
/lib64/libc.so.6(abort+0x175)[0x7f8855719dc5]
/usr/local/mysql/bin/mysqld[0x85bc3f]
/usr/local/mysql/bin/mysqld[0x8397fa]
/usr/local/mysql/bin/mysqld[0x83a01f]
/usr/local/mysql/bin/mysqld[0x82e739]
/usr/local/mysql/bin/mysqld[0x80b5f0]
/usr/local/mysql/bin/mysqld[0x800daa]
/usr/local/mysql/bin/mysqld[0x801196]
/usr/local/mysql/bin/mysqld[0x803c27]
/usr/local/mysql/bin/mysqld[0x7f085d]
/usr/local/mysql/bin/mysqld[0x7be1b9]
/usr/local/mysql/bin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x41)[0x6587d1]
/usr/local/mysql/bin/mysqld[0x576f05]
/usr/local/mysql/bin/mysqld(_Z11plugin_initPiPPci+0x7d8)[0x57a268]
/usr/local/mysql/bin/mysqld[0x505ad8]
/usr/local/mysql/bin/mysqld(_Z11mysqld_mainiPPc+0x383)[0x508663]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x7f8855704d1d]
/usr/local/mysql/bin/mysqld[0x4ffd1d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
181113 12:23:09 mysqld_safe mysqld from pid file /usr/local/mysql/var/www.mydomain.com.pid ended

这个提示一般说的是数据库损坏。
搜了很多帖子，尝试了很多次，使用如下方法修复：

1. 首先备份数据库文件夹，重要的事情说三遍：
备份！备份！备份！
(数据库文件夹的具体位置在错误信息的第一句就可以看到，也可以在 /etc/my.cnf 配置文件中可以查到，我的mysql版本应该是lnmp自带的)
cp -R /usr/local/mysql/var /usr/local/mysql/varbackup
为了保险，我把mysql整个目录又备份了一次：
cp -R /usr/local/mysql /usr/local/mysqlbackup

2.编辑 /etc/my.cnf 
在[mysqld]小节添加如下两行：

innodb_force_recover  = 1
innodb_purge_threads = 0

其中innodb_force_recover  从1 开始尝试，最大值为6，根据官方论坛的资料，此值 1 - 3 比较安全，4 - 6 可能造成不可逆转的损坏（备份很重要）。
我一直尝试到6，才把mysql启动成功，即便如此，启动时err log里面会先出现大量的乱码，几十秒后mysql才显示启动成功。

3. 在innodb_force_recover 模式下，是无法对数据库进行修改的。但是通常可以通过mysqldump命令来导出数据库。
尝试使用mysqldump导出关键数据库，在err文件的log看到mysql崩溃重启，无法完成。通过myphpAdmin连接数据库，登录后也会导致mysql重启。
仔细查看log，发现是在读取到某个表的时候，mysql就崩溃了，看来已经无法完整备份数据库。

4. 尝试了很多次以后，有了一个新思路。网站后台最重要的数据是user表，只要把它导出来就好。既然是读取整个数据库有问题，那就单独导出某个表就可了。
于是使用mysqldump单独导出了user表，然后又尝试了增加了其它几个看起来比较重要的表，导出为backup.sql

5. 到var目录下，删除ib*文件，以及出错数据库的文件夹。

6. 屏蔽my.cnf的这两行

innodb_force_recover  = 6
innodb_purge_threads = 0

7. 使用service mysql restart 重启数据库，这时候可以启动了。进入myphpAdmin，按照建站的流程，删除访问数据库的用户，重新建立用户并建立同名数据库（本站架构如此），选中新建的数据库，在本地计算机上找到网站备份，导入建站初始化sql文件以重建数据库表,然后命令行登录mysql, source backup.sql。然后登陆，用户密码OK，但是除了TOS页面，其他页面都无法进入，提示500错误。

8. 修改前端 php配置文件，打开debug
再次登录，发现错误提示：

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'aaa.bbb' doesn't exist (SQL: select * from `bbb` where `user_id` = 1 order by `id` asc)

9. 反复尝试以后，想到前端网站可能更新过，本地计算机上的sql数据表结构可能不是最新的，于是通过scp从前端网站下载新的文件，然后再次重建用户及数据库，选中新建的数据库，然后从myphpAdmin导入建站初始化sql文件，然后再导入backup.sql,可以正常登陆，用户表完全恢复。

10. 修改前端服务器网站的php文件关闭debug

11. 编辑my.cnf打开bin-log，它是最好的本地备份。如果有这个，我就真的不需要折腾24小时了。
 

log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10
max_binlog_size = 200M

12. 启用crond脚本进行数据库邮件备份。
首先163开通smtp/pop3并开通设置授权码
centos主机上,运行如下命令安装sendmail。

yum -y install sendmail
systemctl  start  sendmail
yum install -y mailx

我使用emacs编辑器，所以就使用emacs作为例子了：

emacs /etc/mail.rc

添加

set from=163邮箱名称@163.com
set smtp=smtp.163.com
set smtp-auth-user=163邮箱名称@163.com
set smtp-auth-password=设置的授权码
set smtp-auth=login

开启25端口

emacs /etc/sysconfig/iptables

添加

-A INPUT -p tcp -m tcp --dport 25 -j ACCEPT

然后

service iptables restart

创建一个mailtest.txt 填写信件正文，测试mail 发送邮件

mail -s "test" 测试邮箱@gmail.com<mailtest.txt 

测试发送添加attachment.pdf附件

mail -s "test" -a attachment.pdf 测试邮箱@gmail.com<mailtest.txt 

13.  mysql每日自动备份并发送邮件： 

创建/root/mysqlautobackup.sh脚本 

(参考了 https://www.cnblogs.com/aleda/articles/Linux-Mysql-database-under-the-automatic-daily-backups-sent-to-the-specified-Email.html，把其中的mutt 发送修改为直接使用 mail 发送)

#!/bin/bash
#Script:automysqlbackup.sh
#以下配置信息请自己修改
mysql_user="root" #MySQL备份用户
mysql_password="" #MySQL备份用户的密码
mysql_host="localhost" #要备份的主机，一般为localhost
mysql_port="3306" #MySQL端口号，一般为3306
backup_db_arr=("db1" "db2") #要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
backup_location=/usr/local/mysqlautobackup #备份数据存放位置，末尾请不要带"/",此项可以保持默认，程序会自动创建文件夹
expire_backup_delete="ON" #是否开启过期备份删除 ON为开启 OFF为关闭
expire_days=3 #过期时间天数 默认为三天，此项只有在expire_backup_delete开启时有效
adminmail="我的邮箱@gmail.com"  #接收备份数据的邮箱 也就是管理员的邮箱
subject_msg="MYSQL data auto backup"  #备份邮件主题
subject_msg_error="ERROR: MYSQL data backup"  #备份ERROR邮件主题

#以下配置请保持默认不要修改
backup_time=`date +%Y%m%d%H%M`  #定义备份详细时间
backup_cache_dir=$backup_location/cache  #备份缓存文件夹全路径
backup_data_dir=$backup_location/data  #备份文件夹全路径
backup_data_filename=$backup_data_dir/$backup_time.sql.tar.gz  #备份文件的名称 包括路径
backup_log_dir=$backup_location/log  #备份日志文件夹全路径
backup_log_mailcontent=$backup_log_dir/mailcontent.log  #邮件内容缓存文件全路径
welcome_msg="Welcome to use auto MySQL backup tools!" #欢迎语

#写入欢迎信息
`mkdir -p $backup_log_dir`
`echo $welcome_msg > $backup_log_mailcontent`
    
#判断MYSQL是否启动,mysql没有启动则备份退出
mysql_ps=`ps -ef | grep mysql | wc -l`
mysql_listen=`netstat -an | grep LISTEN | grep $mysql_port | wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
    `echo "ERROR:MySQL is not running! backup stop!" >> $backup_log_mailcontent`
    `mail -s "$subject_msg_error" $adminmail< $backup_log_mailcontent`
    exit
fi

#连接到mysql数据库，无法连接则备份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end

flag=`echo $?`
if [ $flag != "0" ]; then
    `echo "ERROR:Can't connect mysql server! backup stop!" >> $backup_log_mailcontent`
    `mail -s "$subject_msg_error" $adminmail< $backup_log_mailcontent`
    exit
else
    #判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
    if [ "$backup_db_arr" != "" ];then
        #dbnames=$(cut -d ',' -f1-5 $backup_database)
        #echo "arr is (${backup_db_arr[@]})"
        for dbname in ${backup_db_arr[@]}
        do
            `mkdir -p $backup_cache_dir`
            `mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname > $backup_cache_dir/$dbname$backup_time.sql`
            flag=`echo $?`
            if [ $flag != "0" ];then
                `echo "database $dbname backup fail!" >> $backup_log_mailcontent`
            fi
        done
    else
        `echo "ERROR:No database to backup! backup stop" >> $backup_log_mailcontent`
        `send $adminmail -s "$subject_msg_error" < $backup_log_mailcontent`
        exit
    fi

    #如果开启了删除过期备份，则进行删除操作
    backup_data_num=`ls $backup_data_dir | wc -l`
    if [ "$expire_backup_delete" == "ON" -a $backup_data_num!=0 ];then
        `find $backup_data_dir -type f -ctime +$expire_days -exec rm -rf {} \;`
    fi

    #缓存备份目录打包放到数据备份目录 删除缓存备份目录
    backup_cache_num=`ls $backup_cache_dir | wc -l`
    if [ $backup_cache_num!=0 ];then
        `mkdir -p $backup_data_dir`
        `tar zcPf $backup_data_filename $backup_cache_dir/`
        `rm -rf $backup_cache_dir`
    else
        `echo "All database backup fail!" >> $backup_log_mailcontent`
        `mail -s "$subject_msg_error" $adminmail < $backup_log_mailcontent`
        exit
    fi

    `echo "All database backup success! Thank you!" >> $backup_log_mailcontent`
    `mail -s "$subject_msg" -a $backup_data_filename $adminmail< $backup_log_mailcontent`
    exit
fi

#然后给脚本增加权限

chmod +x /root/mysqlautobackup.sh

#然后运行脚本进行测试 

/root/mysqlautobackup.sh

#加入crond定时运行

crontab -e
0 3 * * *  /root/mysqlautobackup.sh

#上述内容表示每天凌晨3点进行备份
#启动crond服务

service crond start

然后把网站前端再做一个备份，基本上就安全了。