hal9008
Posts: 8
Joined: Tue Oct 30, 2018 2:44 pm

Recover MYSQL data

Tue Jan 08, 2019 1:44 pm

Hello.

Few days ago I posted a message in this forum about a problem with some updates here -->viewtopic.php?t=230257. I was able to solve the problem.

BUT... The problem of the other post was caused by a failure of the hard disk (the operating system of my raspberry is in a USB hard disk). One day the system were not able to boot.

Now I have another disk and the information of the other disk is recovered (all the files). I installed the operating system in the new disk and the raspberry is working... But the problem is .... How can I put the old mysql databases in my new system. I want to take some of the databases but i haven't got the *.sql files. I only have the /var/lib/mysql folder (And i think that all the information in this folder is in good condition). ¿How can i convert the databases that there are in this folder to a .sql file to import in the new system?

Regards.

pfletch101
Posts: 481
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Re: Recover MYSQL data

Tue Jan 08, 2019 9:33 pm

The first thing I would try is shutting down MySQL, copying the contents of the data folder from the old system into /var/lib/mysql on the new system, and restarting MySQL. There should be a good chance that all your old data will now be there and accessible.

User avatar
DougieLawson
Posts: 35540
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Recover MYSQL data

Tue Jan 08, 2019 9:59 pm

One step more than that run sudo /usr/bin/mysqld_safe which attempts to recover tables from journals and logs.
Note: Having anything remotely humorous in your signature is completely banned on this forum. Wear a tinfoil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

hal9008
Posts: 8
Joined: Tue Oct 30, 2018 2:44 pm

Re: Recover MYSQL data

Thu Jan 10, 2019 12:03 am

I still can't recover the databases.

If i put all the folder /var/lib/mysql recovered in the folder of the new installation of mysql, the service mysql cant start. If I try to start mysql service, it says the following.

Code: Select all

[email protected]:/var/lib# sudo service mysql start
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.
[email protected]:/var/lib# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Wed 2019-01-09 23:56:23 UTC; 38s ago
  Process: 583 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 650 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Jan 09 23:56:23 raspberrypi mysql[650]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Jan 09 23:56:23 raspberrypi systemd[1]: mysql.service: control process exited, code=exited status=1
Jan 09 23:56:23 raspberrypi systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Jan 09 23:56:23 raspberrypi systemd[1]: Unit mysql.service entered failed state.
[email protected]:/var/lib# journalctl -xn
-- Logs begin at Wed 2019-01-09 23:06:58 UTC, end at Wed 2019-01-09 23:56:23 UTC. --
Jan 09 23:56:23 raspberrypi /etc/init.d/mysql[1392]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Jan 09 23:56:23 raspberrypi /etc/init.d/mysql[1392]: [61B blob data]
Jan 09 23:56:23 raspberrypi /etc/init.d/mysql[1392]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Jan 09 23:56:23 raspberrypi /etc/init.d/mysql[1392]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Jan 09 23:56:23 raspberrypi /etc/init.d/mysql[1392]: 
Jan 09 23:56:23 raspberrypi mysql[650]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Jan 09 23:56:23 raspberrypi systemd[1]: mysql.service: control process exited, code=exited status=1
Jan 09 23:56:23 raspberrypi systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Jan 09 23:56:23 raspberrypi systemd[1]: Unit mysql.service entered failed state.
Jan 09 23:56:23 raspberrypi sudo[629]: pam_unix(sudo:session): session closed for user root
So I try to copy only the folders with the name of the databases that i want to recover in /var/lib/mysql of the new installation. If i did that, i can start the service, enter in the database and see the tables, but if I try to export the sql file I have an error.

Code: Select all

[email protected]:/var/lib# sudo service mysql start
[email protected]:/var/lib# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.62-0+deb8u1 (Raspbian)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 |
| flopy.es           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use flopy.es;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------------+
| Tables_in_flopy.es           |
+------------------------------+
| flopyescommentmeta           |
| flopyescomments              |
| flopyeslinks                 |
| flopyesoptions               |
| flopyespostmeta              |
| flopyesposts                 |
| flopyesterm_relationships    |
| flopyesterm_taxonomy         |
| flopyestermmeta              |
| flopyesterms                 |
| flopyesusermeta              |
| flopyesusers                 |
| oc_clndr_share_calendar      |
| oc_clndr_share_event         |
| oc_contacts_addressbooks     |
| oc_contacts_cards            |
| oc_contacts_cards_properties |
| oc_documents_invite          |
| oc_documents_member          |
| oc_documents_revisions       |
| oc_documents_session         |
| oc_file_map                  |
| oc_files_trash               |
| oc_files_trashsize           |
| oc_files_versions            |
| oc_gallery_sharing           |
| oc_group_admin               |
| oc_group_user                |
| oc_groups                    |
| oc_jobs                      |
| oc_lucene_status             |
| oc_mimetypes                 |
| oc_permissions               |
| oc_pictures_images_cache     |
| oc_preferences               |
| oc_privatedata               |
| oc_properties                |
| oc_share                     |
| oc_storages                  |
| oc_users                     |
| oc_vcategory                 |
| oc_vcategory_to_object       |
+------------------------------+
42 rows in set (0.00 sec)

mysql> quit;
Bye
[email protected]:/var/lib# sudo mysqldump flopy.es > flopyes.sql
mysqldump: Got error: 1033: Incorrect information in file: './[email protected]/flopyescommentmeta.frm' when using LOCK TABLES
[email protected]:/var/lib# 
I try to use mysqldump as this:

Code: Select all

[email protected]:/var/lib# mysqldump --user=root --password=myspassword flopy.es > /home/pi/flopy.sql
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
But as you can see i still can't download the sql file. I put in the configuration of the new database the same user and password of the old database. I try this in my raspberry and in a laptop with linux mint, but in both computers I can't recover any file.

Any idea to solve this??

pfletch101
Posts: 481
Joined: Sat Feb 24, 2018 4:09 am
Location: Illinois, USA

Re: Recover MYSQL data

Thu Jan 10, 2019 1:13 am

I am out of ideas. This is, of course, why you should keep serial backups of important data on media other than your working disks. I have never had to recover a MySQL installation from scratch, since I have always ben able to revert to the most recent database backups (which will be no more than a day or two old for important data). For really vital and irretrievable data, I also keep separate journals of additions and changes, so that data entered since the last backup can be recovered.

Andyroo
Posts: 3775
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Recover MYSQL data

Thu Jan 10, 2019 1:32 am

You may be able to run a SELECT * across each table and save the result to file and then import the data to another database.

Long winded but it may get you some data out...
Need Pi spray - these things are breeding in my house...

erkc29
Posts: 1
Joined: Thu Jan 10, 2019 6:01 am

Re: Recover MYSQL data

Thu Jan 10, 2019 6:08 am

I hope this post might help you: MySQL Database Repair

Andyroo
Posts: 3775
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Recover MYSQL data

Thu Jan 10, 2019 11:53 am

erkc29 wrote:
Thu Jan 10, 2019 6:08 am
I hope this post might help you: MySQL Database Repair
Tut tut - that’s basically an advert for Data Retainers service.

A better link is to the actual guide https://mariadb.com/kb/en/library/myisamchk/ - and I totally forgot it :oops:

This does depend on the data being in ISAM tables and not InnoDB/XtraDB format though - I would stick to the SELECT commands.
Need Pi spray - these things are breeding in my house...

Return to “Troubleshooting”