need some help to recover database

2 posts / 0 new
Last post
9h1ej
need some help to recover database

Hi,
When I open cqrlog I am not getting a list of logs as usual. The MySql.err file shows the following.
 
141003  7:50:42 [Note] Plugin 'FEDERATED' is disabled.141003  7:50:42 InnoDB: The InnoDB memory heap is disabled141003  7:50:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins141003  7:50:42 InnoDB: Compressed tables use zlib 1.2.8141003  7:50:42 InnoDB: Using Linux native AIO141003  7:50:42 InnoDB: Initializing buffer pool, size = 80.0M141003  7:50:42 InnoDB: Completed initialization of buffer pool141003  7:50:42 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!141003  7:50:42  InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...141003  7:50:42  InnoDB: Operating system error number 13 in a file operation.InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.InnoDB: File name .InnoDB: File operation call: 'opendir'.InnoDB: Cannot continue operation.141003  7:54:59 [Note] Plugin 'FEDERATED' is disabled.141003  7:54:59 InnoDB: The InnoDB memory heap is disabled141003  7:54:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins141003  7:54:59 InnoDB: Compressed tables use zlib 1.2.8141003  7:54:59 InnoDB: Using Linux native AIO141003  7:54:59 InnoDB: Initializing buffer pool, size = 80.0M141003  7:54:59 InnoDB: Completed initialization of buffer pool141003  7:54:59 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!141003  7:54:59  InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...141003  7:54:59  InnoDB: Operating system error number 13 in a file operation.InnoDB: The error means mysqld does not have the access rights toInnoDB: the directory.InnoDB: File name .InnoDB: File operation call: 'opendir'.InnoDB: Cannot continue operation.
 
I tried to run cqrlog with --debug=1 and get the following
ray@laptop:~$ cqrlog --debug=1**** DEBUG LEVEL 1 ****Loading libssl: /usr/lib/x86_64-linux-gnu/libssl.soLoading libcrypto: /usr/lib/x86_64-linux-gnu/libcrypto.soLoading libmysqlclient: /usr/lib/x86_64-linux-gnu/libmysqlclient.so.18.0.0Loaded 76626 LoTW usersLoaded 85473 eQSL usersLoaded 44996 SCP calls*User home directory:    /home/ray/Program home directory: /home/ray/.config/cqrlog/Data directory:         /home/ray/.config/cqrlog/database/Memebers directory:     /usr/share/cqrlog/members/ZIP code directory:     /usr/share/cqrlog/zipcodes/Binary dir:             /usr/bin/Share dir:              /usr/share/cqrlog/TConnection to MySQL:   5.5*Closing DXCluster windowClosing TRXControl windowClosing GrayLine windowClosing dDXCCClosing dData(cqrlog:10584): GLib-CRITICAL **: Source ID 27 was not found when attempting to remove it(cqrlog:10584): GLib-CRITICAL **: Source ID 26 was not found when attempting to remove it(cqrlog:10584): GLib-CRITICAL **: Source ID 60 was not found when attempting to remove it(cqrlog:10584): GLib-CRITICAL **: Source ID 59 was not found when attempting to remove it(cqrlog:10584): GLib-CRITICAL **: Source ID 67 was not found when attempting to remove it(cqrlog:10584): GLib-CRITICAL **: Source ID 66 was not found when attempting to remove itClosing ini file ...ray@laptop:~$
Is there a way I can recover the LOG.
 
Thanks
Ray
9h1ej
 
 

ok2cqr
ok2cqr's picture
Re: need some help to recover database

Hi Ray,
 
I have my own step by step howto but not sure it it's clear. I'll try to desribe it in a few points.
 
- backup whole ~/.config/cqrlog directory
 
- open ~/.config/cqrlog/database/mysql.cnf
 
- to [mysqld] section insert innodb_force_recovery = 6 and save the file
 
- run mysqld --defaults-file=/home/ok2cqr/.config/cqrlog/database/mysql.cnf --datadir=/home/ok2cqr/.config/cqrlog/database --socket=/home/ok2cqr/.config/cqrlog/database/sock --port=64000 (instead of ok2cqr insert your user name)
 
- now mysqld should start
 
- with mysqldump -S /home/ok2cqr/.config/cqrlog/database/sock --databases cqrlog001 --single-transaction -f --tables cqrlog_main --no-create-info > cqrlog_main.sql you can backup main table
 
- with mysqldump -S /home/ok2cqr/.config/cqrlog/database/sock --databases cqrlog001 --single-transaction -f --tables cqrlog_config --no-create-info > cqrlog_config.sql you can backup configuration
 
- the same do for all logs you are using (don't forget to rename target sql file)
 
- shutdown mysqld command using CTRL+C
 
- now delete ~/.config/cqrlog directory
 
- to cqrlog_main.sql and cqrlog_config.sql on the top of the file enter this command: use cqrlog001;
 
- start cqrlog again, it will create new .config/cqrlog directory, keep it running
 
- with mysql -S /home/ok2cqr/.config/cqrlog/database/sock < cqrlog_main.sql you can restore man database
 
 
- with mysql -S /home/ok2cqr/.config/cqrlog/database/sock < cqrlog_config.sql you can restore configuration
 
If you have only one log, it's OK, you have all important data restored. If you have more logs, you have to backup them one by one, than run cqrlog, create databases again and restore the data to right logs.
if you have ADIF backup it would be much easier to delete ~/.config/cqrlog and import it to empty database.
The easiest way is to compress ~/.config/cqrlog and send it to my email. I'll do it for you :).
 
73 Petr
 
P.S. you can set innodb_force_recovery to lower value. The 6 means worst case scenario when the database is really broken. Maybe your is DB is not so broken. This high value could cause that you won't have all QSO in the log. It never happened to me but it's possible.