Results 1 to 9 of 9

Thread: Restore mysql databases from files

  1. #1
    Join Date
    Sep 2015
    Location
    Italia
    Beans
    138
    Distro
    Ubuntu 18.04 Bionic Beaver

    Restore mysql databases from files

    Hi everyone,

    a few days ago I lost my server. The motherboard no longer worked, but the hdd worked normally. The old server was ubuntu 16.04.

    So I installed ubuntu 22.04 server on a new computer, and I want to restore mysql databases from /etc/mysql folder.

    Is there a way?

    Thanks
    Last edited by dam034; May 17th, 2024 at 08:24 AM. Reason: I fixed

  2. #2
    Join Date
    Mar 2010
    Location
    Been there, meh.
    Beans
    Hidden!
    Distro
    Ubuntu

    Re: Restore mysql databases from files

    /etc/mysql is just for MySQL settings. The actual DBMS files are under /var/llib/mysql (usually). BTW, there have been huge mySQL changes over the years, so you'll need to look up the migration steps to get from the version you had to the version you will be running. That's what release notes are all about. READ THEM!

    With the release of 24.04 yesterday, people will begin practicing the production server migrations from 22.04 to 24.04 ... practicing ... since they don't want any data corruption or data loss, but going back to 16.04 is so long ago that most of us have forgotten any issues. Heck, we stopped using MySQL and switched to MariaDB sometime between 2026 and 2018, so there are other migrations you may want to consider.

    Today, the default DBMS is MariaDB and MySQL is deprecated for a number of years on Ubuntu.

    Sometimes the best method is to perform a mysqldump to get text files for the entire DB, then restore them into a new system with a new DBMS engine.

  3. #3
    Join Date
    Sep 2015
    Location
    Italia
    Beans
    138
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Restore mysql databases from files

    I also have the folder /var/llb/mysql.

    All the tables have three files: frm, myd and myi.

    Is there a way to restore the tables?

    Thanks

  4. #4
    Join Date
    Mar 2010
    Location
    Been there, meh.
    Beans
    Hidden!
    Distro
    Ubuntu

    Re: Restore mysql databases from files

    A way? Yes.

    Install 16.04 somewhere, place the DB on that system in the expected location, open the DB using normal DBMS commands, then use mysqldump to create a backup for everything in the DB. All tables, views, triggers, constraints, external functions, everything. Take that dump to the new system and use the opposite of mysqldump to recreate the DB under the new OS.

    It might be easiest to create a virtual machine with 16.04 on the new hardware. It doesn't need to be a huge VM, just large enough to run the DB for 1 user and do the mysqldump.

    https://linuxize.com/post/how-to-bac...ith-mysqldump/ shows how to backup and restore.
    MySQL 5.x --> 8.x migration: https://dba.stackexchange.com/questi...-via-mysqldump
    Google will find lots of examples, if you need others.

  5. #5
    Join Date
    Sep 2015
    Location
    Italia
    Beans
    138
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Restore mysql databases from files

    So I created a VM with ubuntu 16.04, installed via tasksel the lamp server.

    Now, I only have to copy the files in the folder? Or is there some other to do?

    Thanks

  6. #6
    Join Date
    Sep 2015
    Location
    Italia
    Beans
    138
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Restore mysql databases from files

    Now I have some issues.

    I copied the folders, each with a database name, in /var/lib/mysql, and used the console and phpmyadmin.

    When I select a specific database, it seems to be corrupt.

    Here the console:

    Code:
    root@old-ub16:# mysql -h localhost -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.7.33-0ubuntu0.16.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2021, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | as***              |
    | cretiww2           |
    | dati               |
    | g***               |
    | g***               |
    | l*********         |
    | mediatomb          |
    | mtasa              |
    | mysql              |
    | performance_schema |
    | sc*********        |
    | scambiointe        |
    | soldi              |
    | sys                |
    | tut******          |
    +--------------------+
    16 rows in set (0.00 sec)
    
    mysql> use soldi;
    Database changed
    mysql> show tables;
    +-----------------+
    | Tables_in_soldi |
    +-----------------+
    | avvisi          |
    | cassa           |
    | movimenti       |
    | utenti          |
    +-----------------+
    4 rows in set (0.00 sec)
    
    mysql> select * from avvisi;
    ERROR 1146 (42S02): Table 'soldi.avvisi' doesn't exist
    mysql> select * from cassa;
    ERROR 1146 (42S02): Table 'soldi.cassa' doesn't exist
    As you can see, I can see the tables, but they don't exist.

    Also when I use phpmyadmin, I try to visit the database "soldi", in the side menu I can see the tables, but in the body it tells me that there are no tables:
    f1.jpg
    Instead if I visit the database "cretiww2", I can see only one table, although there is a nice list in the side menu:
    f2.jpg
    Lastly, the database "scambiointe" works correctly.

    Now I can't understand why there are some tables which work, and some which don't work.

    I want to point out that I have available the entire /var/lib/mysql folder of the old server.

    How can I fix?

    Thanks

  7. #7
    Join Date
    Mar 2010
    Location
    Been there, meh.
    Beans
    Hidden!
    Distro
    Ubuntu

    Re: Restore mysql databases from files

    https://linuxize.com/post/how-to-bac...ith-mysqldump/ is all I know. Did you also restore the config files from the old system? When we backup, we need to ensure that our restore process actually works. Sometimes it is as easy as coping files, but often, there are specific config requirements, access tool requirements, and definitely user, group, permission requirements which all need to be handled properly.

    I've never used phpmyadmin after seeing all the hacking attempts for it in my web servers. Some tools are worse than the original problem. I avoid any php-based admin tools and generally avoid any web-based admin tools that aren't built-into the software they are supposed to admin. That's how I roll.

    How can I fix?
    IDK. Some tables work and others don't seems really vague. You do know that DBMS systems have different types of file organization, so you need to ensure that the type used in the original version is also setup for the new location. That's a shot in the dark. Did you look at the log files?

    Remember, I wrote this:
    16.04 is so long ago that most of us have forgotten any issues.
    I have trouble remembering problems from last week, much less 2018.

  8. #8
    Join Date
    Sep 2015
    Location
    Italia
    Beans
    138
    Distro
    Ubuntu 18.04 Bionic Beaver

    Re: Restore mysql databases from files

    So I also copied these files:

    ibdata1
    ibtmp1


    And now it works.

    Thanks for the help

  9. #9
    Join Date
    Jun 2014
    Beans
    1

    Re: Restore mysql databases from files

    Sorry for bumping an older thread.

    What if I don't have my ib* files?

    I accidentally got over-zealous doing a chmod and it included ../* effing up all my system files (litterally everything). Yeah, I know...

    After fixing perms I still couldn't get stuff to reconnect properly. I tried a apt/reinstall and that seemed to screw everything up more.

    What I have:
    - My old /var/lib/mysql/database folders
    -- Some are innodb, some myisam
    - A few fresh mysql installation

    Am I screwed? Anyway to do this?

    I have several WP sites dead in the water at the moment.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •