server crash - moving mysql files gets table not found in LOCK_TABLES

Discussion in 'ISPConfig 3 Priority Support' started by craig baker, Sep 8, 2024.

  1. craig baker

    craig baker Member HowtoForge Supporter

    what it says above had old server running centos 7, got hit by lightening, one drive destroyed backup drive had bad sectors.
    copied all the /var/lib/mysql databases to a shiny new deb12 install (I'm looking at YOU, Till) - but though mysql> allows use database and show tables, I cant export the data (its a wordpress database, want to import it to new wordpress instance).
    it gets:
    root@ns1:/var/lib/mysql# mysqldump -u root -p c0americanhalaldb > test.sql
    Enter password:
    mysqldump: Got error: 1932: "Table 'c0americanhalaldb.wp_actionscheduler_actions' doesn't exist in engine" when using LOCK TABLES

    now I did not copy the ib* files from the old datadrive, only the databases themselves (/var/lib/mysql/c0americanhalaldb for example above). ownership and permissions are correct.
    if I understand things the ibdata1 file should NOT be moved from old drive (mounted so as to be available on new deb12) because that would make new databases (like the new dbispconfig) unreadable??? then ispconfig dies

    so I have the original ibdata1 file and the new ibdata1 file. I dont want to replace the new one!


    the tables are absolutely all there:
    whats going on? I suppose big difference in versions from centos 7 to deb 12. but how can I dump the data so I can create a new working db?? or are we just screwed?
    is there a way to 'import' from the old ibdata1 file the TABLESPACE for the c0americanhaladb and incorporate it in the new ibdata1 file without affecting other databases??
    thanks
     
    Last edited: Sep 8, 2024
    craig baker, Sep 8, 2024
    #1
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    if this is a innodb database, then this can't work.

    This is not about ISPConfig at all here, you can not try to restore mysql by leaving the data behind on the old system and just migrating some files that define the structure of tables.

    So if you just have a backup of /var/lib/mysql from old server and you want to export the data that's in there, I would do this:

    1) Get a small cloud server with centOS 7.
    2) Install just mysql or mariadb, so you get the exact same version that you had on the old system.
    3) Stop mysql, copy over /var/lib/mysql from backup and start mysql again.
    4) Now you should have a working mysql server where you can dump databases using mysqldump, which you then can import into the new mysql server on your debian system.
     
    till, Sep 8, 2024
    #2
  3. craig baker

    craig baker Member HowtoForge Supporter

    aha - thanks till that makes perfect sense. recreate a centos7 server, use that to export!
    but - not so fast. I have a centos 7 system I tared and move mysql folder to that system.
    (after stopping mysqld of course).
    I saved the mysql folder so I could restore it.
    but now systemctl restart mysqld fails with error saying:
    InnoDB: The Auto-extending data file './ibdata1' is of a different size 704 pages than specified by innodb_data_file_path
    I have added to my.cnf: under [mysqld]
    innodb_data_file_path = ibdata1:704M:autoextend
    but still fails on restart. any ideas?
     
    Last edited: Sep 8, 2024
    craig baker, Sep 8, 2024
    #3

Share This Page