innodb从ibd文件恢复
1.重命名已经丢失frm文件的数据库安装目录
cd /app
mv mariadb mariadb-bak
2.安装一个空数据库,注意安装的新数据库要与丢失frm文件的数据库版本一致
#!/bin/bash
yum install -y libaio
rpm -qa | grep mysql
rpm -e `rpm –qa | grep mysql` --nodeps
rm -rf /etc/my.cnf
useradd mysql;
cd /app/software;
tar xvf mariadb-10.3.8-linux-x86_64.tar.gz;
mv mariadb-10.3.8-linux-x86_64 /app/mariadb;
cd /app/mariadb;
./scripts/mysql_install_db --basedir=/app/mariadb --datadir=/app/mariadb/data --user=mysql;
chown -R mysql /app/mariadb/;
sed -i "s@^basedir=@basedir=/app/mariadb@g" /app/mariadb/support-files/mysql.server
sed -i "s@^datadir=@datadir=/app/mariadb/data@g" /app/mariadb/support-files/mysql.server
current=`date "+%Y-%m-%d %H:%M:%S"`
timeStamp=`date -d "$current" +%s`
cat > /app/mariadb/my.cnf << EOF
[mysqld]
skip-name-resolve
lower_case_table_names=1
port=3306
max_connections=8000
character-set-server=utf8
max_allowed_packet =134217728
autocommit=1
innodb-file-per-table=1
innodb-open-file=20000
innodb_log_file_size=512M
innodb_log_buffer_size=8M
innodb_buffer_pool_size=2G
innodb_log_files_in_group=3
innodb_lock_wait_timeout = 600
sort_buffer_size=12M
join_buffer_size=12M
thread_cache_size=200
tmp_table_size = 2022715136
max_heap_table_size = 2022715136
tmpdir=/app/mariadb
binlog-format=ROW
log-bin = mysql-bin
server-id = ${timeStamp}
binlog-ignore-db=mysql
binlog-ignore-db=report-binlog
binlog-ignore-db=quick-report
expire_logs_days = 7
innodb_flush_log_at_trx_commit =2
innodb_log_buffer_size = 64M
innodb_read_io_threads = 16
innodb_write_io_threads = 16
query_cache_size = 0
EOF
chown -R mysql /app/mariadb/;
/app/mariadb/support-files/mysql.server start;
if [ $? -eq 0 ];then
/app/mariadb/bin/mysql -uroot -e "delete from mysql.user where user='';"
/app/mariadb/bin/mysql -uroot -e "GRANT ALL PRIVILEGES ON *.* TO root@'%' identified by 'dreamtechIT2017';"
/app/mariadb/bin/mysql -uroot -e "UPDATE mysql.user SET Password=PASSWORD('dreamtechIT2017') where USER='root';"
/app/mariadb/bin/mysql -uroot -e "flush privileges;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "flush privileges;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`ask-bar\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`human-resource\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`course-study\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`cloud-center\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`quick-report\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`system\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`train\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`zxy-log\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`zxy-pay\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`report-binlog\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`exam\`;"
fi
2.导出数据库表结构,注意表结构要跟丢失frm文件数据库的表结构一模一样，否则将无法恢复
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d ask-bar>/app/dbbackup/ask-bar.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d human-resource>/app/dbbackup/human-resource.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d course-study>/app/dbbackup/course-study.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d cloud-center>/app/dbbackup/cloud-center.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d quick-report>/app/dbbackup/quick-report.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d system>/app/dbbackup/system.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d train>/app/dbbackup/train.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d zxy-log>/app/dbbackup/zxy-log.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d zxy-pay>/app/dbbackup/zxy-pay.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3307 -h10.117.58.76 -d exam>/app/dbbackup/exam.sql --single-transaction;
3.连接新数据库，建立新的表结构
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 ask-bar </app/dbbackup/ask-bar.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 human-resource </app/dbbackup/human-resource.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 course-study </app/dbbackup/course-study.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 cloud-center </app/dbbackup/cloud-center.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 quick-report </app/dbbackup/quick-report.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 system </app/dbbackup/system.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 train </app/dbbackup/train.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 zxy-log </app/dbbackup/zxy-log.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 zxy-pay </app/dbbackup/zxy-pay.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 exam </app/dbbackup/exam.sql;
4.将重新生成的frm文件拷贝到丢失了frm文件数据库的data目录
/app/mariadb/support-files/mysql.server stop
cd /app
mv mariadb mariadb-clean
cp -r mariadb-bak mariadb
cp /app/mariadb-clean/data/mysql/* /app/mariadb/data/mysql/
cp /app/mariadb-clean/data/ask@002dbar/*.frm /app/mariadb/data/ask@002dbar/
cp /app/mariadb-clean/data/cloud@002dcenter/*.frm /app/mariadb/data/cloud@002dcenter/
cp /app/mariadb-clean/data/course@002dstudy/*.frm /app/mariadb/data/course@002dstudy/
cp /app/mariadb-clean/data/exam/*.frm /app/mariadb/data/exam/
cp /app/mariadb-clean/data/human@002dresource/*.frm /app/mariadb/data/human@002dresource/
cp /app/mariadb-clean/data/quick@002dreport/*.frm /app/mariadb/data/quick@002dreport/
cp /app/mariadb-clean/data/report@002dbinlog/*.frm /app/mariadb/data/report@002dbinlog/
cp /app/mariadb-clean/data/system/*.frm /app/mariadb/data/system/
cp /app/mariadb-clean/data/train/*.frm /app/mariadb/data/train/
cp /app/mariadb-clean/data/zxy@002dlog/*.frm /app/mariadb/data/zxy@002dlog/
cp /app/mariadb-clean/data/zxy@002dpay/*.frm /app/mariadb/data/zxy@002dpay/
chown -R mysql:mysql /app/mariadb
5.在/app/mariadb/my.cnf添加如下一行
innodb_force_recovery= 4
然后运行如下命令启动数据库
/app/mariadb/support-files/mysql.server start
运行如下命令导出恢复的数据库表结构与表数据
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 ask-bar>/app/dbbackup2/ask-bar.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 human-resource>/app/dbbackup2/human-resource.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 course-study>/app/dbbackup2/course-study.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 cloud-center>/app/dbbackup2/cloud-center.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 quick-report>/app/dbbackup2/quick-report.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 system>/app/dbbackup2/system.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 train>/app/dbbackup2/train.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 zxy-log>/app/dbbackup2/zxy-log.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 zxy-pay>/app/dbbackup2/zxy-pay.sql --single-transaction;
/app/mariadb/bin/mysqldump -uroot --default-character-set=utf8 -pdreamtechIT2017 -P3306 -h10.25.168.155 exam>/app/dbbackup2/exam.sql --single-transaction;
6.执行以下命令将恢复的数据库删除
/app/mariadb/support-files/mysql.server stop
rm -rf /app/mariadb
执行以下命令重新安装一个空数据库
#!/bin/bash
yum install -y libaio
rpm -qa | grep mysql
rpm -e `rpm –qa | grep mysql` --nodeps
rm -rf /etc/my.cnf
useradd mysql;
cd /app/software;
tar xvf mariadb-10.3.8-linux-x86_64.tar.gz;
mv mariadb-10.3.8-linux-x86_64 /app/mariadb;
cd /app/mariadb;
./scripts/mysql_install_db --basedir=/app/mariadb --datadir=/app/mariadb/data --user=mysql;
chown -R mysql /app/mariadb/;
sed -i "s@^basedir=@basedir=/app/mariadb@g" /app/mariadb/support-files/mysql.server
sed -i "s@^datadir=@datadir=/app/mariadb/data@g" /app/mariadb/support-files/mysql.server
current=`date "+%Y-%m-%d %H:%M:%S"`
timeStamp=`date -d "$current" +%s`
cat > /app/mariadb/my.cnf << EOF
[mysqld]
skip-name-resolve
lower_case_table_names=1
port=3306
max_connections=8000
character-set-server=utf8
max_allowed_packet =134217728
autocommit=1
innodb-file-per-table=1
innodb-open-file=20000
innodb_log_file_size=512M
innodb_log_buffer_size=8M
innodb_buffer_pool_size=2G
innodb_log_files_in_group=3
innodb_lock_wait_timeout = 600
sort_buffer_size=12M
join_buffer_size=12M
thread_cache_size=200
tmp_table_size = 2022715136
max_heap_table_size = 2022715136
tmpdir=/app/mariadb
binlog-format=ROW
log-bin = mysql-bin
server-id = ${timeStamp}
binlog-ignore-db=mysql
binlog-ignore-db=report-binlog
binlog-ignore-db=quick-report
expire_logs_days = 7
innodb_flush_log_at_trx_commit =2
innodb_log_buffer_size = 64M
innodb_read_io_threads = 16
innodb_write_io_threads = 16
query_cache_size = 0
EOF
chown -R mysql /app/mariadb/;
/app/mariadb/support-files/mysql.server start;
if [ $? -eq 0 ];then
/app/mariadb/bin/mysql -uroot -e "delete from mysql.user where user='';"
/app/mariadb/bin/mysql -uroot -e "GRANT ALL PRIVILEGES ON *.* TO root@'%' identified by 'dreamtechIT2017';"
/app/mariadb/bin/mysql -uroot -e "UPDATE mysql.user SET Password=PASSWORD('dreamtechIT2017') where USER='root';"
/app/mariadb/bin/mysql -uroot -e "flush privileges;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "flush privileges;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`ask-bar\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`human-resource\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`course-study\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`cloud-center\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`quick-report\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`system\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`train\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`zxy-log\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`zxy-pay\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`report-binlog\`;"
/app/mariadb/bin/mysql -uroot -p'dreamtechIT2017' -e "create database \`exam\`;"
fi
7.连接空数据库，导入恢复的数据,检查每个表，只要是提示表不存在的，就是由于frm文件与ibd文件的表结构对应不上导致
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 ask-bar </app/dbbackup2/ask-bar.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 human-resource </app/dbbackup2/human-resource.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 course-study </app/dbbackup2/course-study.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 cloud-center </app/dbbackup2/cloud-center.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 quick-report </app/dbbackup2/quick-report.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 system </app/dbbackup2/system.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 train </app/dbbackup2/train.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 zxy-log </app/dbbackup2/zxy-log.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 zxy-pay </app/dbbackup2/zxy-pay.sql;
/app/mariadb/bin/mysql -uroot -pdreamtechIT2017 -P3306 -h10.25.168.155 exam </app/dbbackup2/exam.sql;
