0

I have a Raspberry Pi that does not boot anymore on which I have an hosted website.

My plan is to reinstall the whole Raspbian system on it, but before that, I would like to recover my Wordpress website that is installed on it. So I managed to mount the SD on my laptop (under Manjaro Linux), from which I have to recover the website folder that I already manage to copy (/var/www/html/mywebsite).

The problem concerns the SQL database linked to this website that is stored into /var/lib/mysql/mywebsitedb. I would like to save it as a .sql file. But I cannot use the mysqldump function that I could use for it if I was able to run the Raspberry Pi and ssh access.

The option I found was to clone the database from the folder of the SD card /run/media/$USER/rootfs/var/www/html/mywebsite, to my laptop repository /var/lib/mysql this way :

rsync -av /run/media/$USER/rootfs/var/lib/mysql/mywebsitedb /var/lib/mysql
sudo chown --recursive mysql:mysql /var/lib/mysql/mywebsitedb # Giving access to mysql

I did it and it worked without any problem. When I do,

USE mywebsitedb;
SHOW TABLES;

I have no problem to see the tables. But the problem comes when I try to extract mywebsitedb with mysqldump this way :

mysqldump -u root -p mywebsitedb > /home/$USER/mywebsitedb.sql

I receive the following error message :

mysqldump: Got error: 1932: "Table 'mywebsitedb.mywebsite_commentmeta' doesn't exist in engine" when using LOCK TABLES

And I did not find a solution to this problem until now. Thanks in advance !

EDIT

The tables are made of a mix of .idb and .frm files. For each .idb file there is a .frm file of the same name.

ls -lah mywebsitedb  # Gives the following :

drwx------ 2 mysql mysql 4,0K 21 nov.  21:52 .
drwx------ 6 mysql mysql 4,0K 12 déc.  14:02 ..
[...]
-rw-rw---- 1 mysql mysql 3,6K 21 nov.  21:52 mywebsite_terms.frm
-rw-rw---- 1 mysql mysql 128K  4 déc.  18:05 mywebsite_terms.ibd
-rw-rw---- 1 mysql mysql 2,2K 21 nov.  21:52 mywebsite_term_taxonomy.frm
-rw-rw---- 1 mysql mysql 128K  4 déc.  18:05 mywebsite_term_taxonomy.ibd
-rw-rw---- 1 mysql mysql 3,0K 21 nov.  21:52 mywebsite_usermeta.frm
-rw-rw---- 1 mysql mysql 128K 10 déc.  21:28 mywebsite_usermeta.ibd
-rw-rw---- 1 mysql mysql 6,8K 21 nov.  21:52 mywebsite_users.frm
-rw-rw---- 1 mysql mysql 144K  4 déc.  18:05 mywebsite_users.ibd
-rw-rw---- 1 mysql mysql   67 21 nov.  21:49 db.opt
Théo GUILLERMINET
  • 11
  • 3
  • Are/were both MySQL instances running the same version? Are they MyISAM or InnoDB tables? If all your files are ibd files then they are InnoDB tables. If you have a mix of frm, myi and myd files then they are MyISAM tables. If they are MyISAM tables try running `REPAIR TABLE mywebsitedb.mywebsite_commentmeta`. – user1191247 Dec 12 '21 at 16:50
  • Does this answer your question? [Restore table structure from frm and ibd files](https://stackoverflow.com/questions/26868956/restore-table-structure-from-frm-and-ibd-files) – user1191247 Dec 12 '21 at 17:26
  • Thanks for your reply and for the links :) For the file formats, it's a mix of .frm and .idb (see EDIT in my question). For the MySQL versions : (1) on my laptop, MySQL v15.1 / 10.6.5-MariaDB, (2) on the RPi, I don't manage to get the version from the non-bootable system :/ – Théo GUILLERMINET Dec 12 '21 at 17:38
  • Oh, thanks for the link ! I'll try it and come back to you – Théo GUILLERMINET Dec 12 '21 at 17:41
  • The method to this link [Restore table structure from frm and ibd files](https://stackoverflow.com/questions/26868956/restore-table-structure-from-frm-and-ibd-files) does not work anymore because it looking like the _mysql-utilities_ package is not maintained. I'll still try to inspire myself from what is proposed to the link. Thanks ! – Théo GUILLERMINET Dec 12 '21 at 21:32
  • Do you have any other source for all the DDL to create the tables? Given that it is a WP site it should be easy enough to get the CREATE TABLE statements for all the tables. Then you can follow the rest of the process. – user1191247 Dec 12 '21 at 21:50
  • Good idea ! So I created a new empty/new database, in which I added empty/default WP tables with [this code](https://gist.github.com/rnagle/8844808ec324ac1eefca). After that, I followed what was proposed in the link you sent me @nnichols . At the end, when I do `ALTER TABLE example_table IMPORT TABLESPACE;` for each table, I have the following error : `ERROR 1815 (HY000): Internal error: Drop all secondary indexes before importing table database/example_table when .cfg file is missing.` But I never saw any .cfg files in the database directory. So I don't undertand :) – Théo GUILLERMINET Dec 27 '21 at 21:56
  • Your best bet may be to copy the entire /var/lib/mysql to somewhere safe, rebuild your Raspberry Pi and then copy the old /var/lib/mysql back to the rebuilt device. Your current ibd files may not be easily recoverable on your Linux laptop as they may not be binary compatible. – user1191247 Dec 27 '21 at 23:53
  • Yes, thanks, that is the best idea. I let you know. And thanks again for your time ! Best, Théo – Théo GUILLERMINET Dec 28 '21 at 21:22

0 Answers0