Translated from 日本語 • View original Hello.
This time, I will mainly talk about mysql database recovery (in this blog).
The person in charge
"It seems that Myisam is faster than innodb in Waordpress, so
Before I changed it, I tinkered with the parameters of innodb's my.cnf and tried to benchmark it."
The data in the DB was broken.
Forget about MySQL 5.6, it would be faster if you could modify it to use reference-only transactions without using MyIsam. (5.5)
I couldn't read the DB data and couldn't log in to the wordpress admin screen.
---------------------------------------------------
130528 11:59:24 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
130528 11:59:24 [ERROR] Native table 'performance_schema'.' events_waits_current' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' events_waits_history' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' events_waits_history_long' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' setup_consumers' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' setup_instruments' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' setup_timers' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' performance_timers' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' threads' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' events_waits_summary_by_thread_by_event_name' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' events_waits_summary_by_instance' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' events_waits_summary_global_by_event_name' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' file_summary_by_event_name' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' file_summary_by_instance' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' mutex_instances' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' rwlock_instances' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' cond_instances' has the wrong structure
130528 11:59:24 [ERROR] Native table 'performance_schema'.' file_instances' has the wrong structure
130528 11:59:24 [Note] Event Scheduler: Loaded 0 events
130528 11:59:24 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.30-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
130528 11:59:50 [ERROR] Cannot find or open table xxxxxx_db/dl_options from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.);
---------------------------------------------------
I was told that there was only a .frm file in the data directory, but
my.cnf didn't have innodb_file_per_table, so maybe ibdata1 or something like that contains metadata.
Speaking of inconsistencies caused by tinkering with innodb parameters, it seems possible to change innodb_log_file_size and so on.
You won't know the details until you look closely at the log like licking it.
Situation:
・There are backups as of April, and there are no daily backups.
・Update information from April onwards can be restored from the web cache and records by adding 4 articles and users.
・The amount of binary log data is small, and the timestamps are newly cut and disappeared, so roll-forward recovery seems impossible.
- All tables with missing data are innodb and not myisam, so repair table tablename USE_FRM; I can't do it
・I can't mysqldump even if I use innodb_force_recovery = 6.
・I don't have time to check binary logs, analyze logs in detail, and respond to error searches each time, which is a waste of man-hours.
So I deleted the table, loaded the data as of April, and from there, I restored the manpower with the stored cache.
To the policy. (I can't put the admin screen in it, so I said goodbye to the two things I was editing.) I'll scratch again. ）
Done: [php]ls -l /home/xxxxxx-op/xxxxxx_db.bak.sql.bz2 man bzip2 bunzip2 -c /home/xxxxxx-op/xxxxxx_db.bak.sql.bz2 > /root/xxxxxx_db.bak.sql vi /etc/my.cnf #innodb_force_ recovery = 6 service mysqld restart ps -ef|grep mysql netstat -lnpt mysql -u root -p drop database xxxxxx_db; drop database performance_schema; create database xxxxxx_db; mysql -u root -p xxxxxx_db < xxxxxx_db.bak.sql[/php] それからエラーログ大丈夫そうかとサイトの表示確認を。
あとはの更新はキャッシュから復旧しておいてね的な。
lesson this time:
If you are doing something you are not used to, you will need to back it up for the time being.
If you have a blog with a small number of users like this one, the composition is of course single, so
It is better to take backups every day because they can be made at any time.
(If it is for service purposes, consistency, character codes, etc. will be taken into account in the early morning, etc. when there are few connections.)
If there are more than two units, it is prohibited to only use replication as a backup as a countermeasure against operation errors.
(Because replication also reflects mistakes.) Replication that is intentionally delayed is different, but I think it would be better to have a backup. ）
Reference: No person who mistakenly deleted the data in the customer DB