Room for Fabby and Ionică Bizău

« first day (2 days earlier) ← previous day last day (15 days later) »

Fabby

06:51

Awake.

Rebooting, be back later. Ping me please.

2 hours later…

Fabby

09:03

Back. Was making backups.

Ionică Bizău

09:31

@Fabby Hey!

I came to the point to import databases back.

Not sure how to access the /var/lib/mysql folder as it wasn't archived via tar because of permissions I assume.

How can I get access to it?

I have the partition clone.

Fabby

I don't understand the question.

chown

root has access to everything

Ionică Bizău

OK, trying in a moment

Right!

I can see the files.

Should I simply copy them and override the current ones from /var/lib/mysql?

Fabby

:-) :-)

yup.

Ionică Bizău

Copying... :)

Man... if that works... haha

Ha! The corrupted ones are only the ones belonging to the company I coillaborate with, haha

Fabby

Ionică Bizău

09:37

I can request a snapshot at anytime from them.

My own data which is important seems to be fine.

ERROR 1018 (HY000): Can't read dir of './that_db/' (errno: 13 - Permission denied)

What permissions should I give...?

drwxr-x--- 2 root  root       4096 apr 23 12:36 doesn_t_work

drwxr-x--- 2 mysql mysql      4096 apr 23 12:38 works

# chown -R mysql:mysql /var/lib/mysql seems it fixed it

Fabby

reading

yup

Ionică Bizău

ERROR 1146 (42S02): Table 'that_db.thatColumn' doesn't exist

Is this because of corruption or I can do something?

I feel something is messed up because I copied the raw files, but have no idea what.

show tables works.

Fabby

Now we're going deep into MySQL recovery, no more disk recovery.

Ionică Bizău

Yes...

Are you familiar with MySQL?

Fabby

I'd need to read up on the MySQL recovery docs.

you can get a copy you say?

Ionică Bizău

09:43

Yes, the raw files seem to be fine.

The size of the db is 2GB

Fabby

Yeah, 5 years ago was the last time I had to recover a MySQL DB

so if you can get a copy, that will save us a day of work...

to recover a database, I need its original structure

create new DB with correct structure,

copy data only, no indexes

(as much as possible)

rebuild indexes

rebuild triggers and constraints

Entire day of work with good documentation.

Without docs?

1 week and I need to sit behind the machine trying stuff out.

Ionică Bizău

Thanks! Playing with it.

Fabby

10:00

I'm going to eat something.

Ionică Bizău

10:21

Q: How to restore the MySQL databases from a corrupted SSD?

My SSD got corrupted. I made a clone of it on an HDD and I have access to all original files from /var/lib/mysql/that_database. I want to import that_database back, but I can't. I tried to copy the raw files, and it didn't work: show tables works, but queries end with that_database.TableName doe...

Asked here @Fabby

Enjoy your meal

2 hours later…

Ionică Bizău

12:18

@Fabby So you think there is any hope for this? If you can solve it I'll give you yet another bounty :D

6 hours later…

Fabby

18:02

There is always hope, but I'd need to know more about the database to be able to recover it

and read up on the docs of the file structure of a DB.

If I would have physical access to your machine: 1 day?

can you get a copy from the customer?

Ionică Bizău

Just in case it helps, I have a backup of all the databases which were there, but it's a bit old.

Fabby

I would need documentation.

Ionică Bizău

Not the company data is the problem, but my data.

Fabby

What do you have

Ionică Bizău

It's an SQL dump.

Which I contains all the databases.

Fabby

18:04

Urgh.

I mean documentation: this table is for this, has these keys, these triggers, this is what they're for, ...

Ionică Bizău

Would be nice to recover that part...

Fabby

I mean, I'd have to see what the DB looked like before starting to recover.

recovering blindly would take an entire week.

not impossible, but tiresome.

Ionică Bizău

I see.

Thank you!

Fabby

An SQL Dump would help, but I'd have to reverse-engineer the database, document it and then start recovering.

Last time I did a DB recovery was actually in 2009 and before that in 2002

Ionică Bizău

I see.

Fabby

18:08

I got back 100% in 2009

and 99.8% in 2002

(there was less than 1h of data lost)

Ionică Bizău

1h?

Hundred?

I see

Fabby

1 hour :D

but, Data was lost and the customer was very unhappy about that...

Ionică Bizău

No way, I did it I think!

@Fabby ^

Fabby

Good! How?

Ionică Bizău

I imported the databases, copied the raw files of the table

Then got the erorr:

Tablespace is missing for table...

Then I did:

ALTER TABLE ActivityEvents DISCARD TABLESPACE

and ALTER TABLE ActivityEvents IMPORT TABLESPACE

Fabby

18:21

:-)

Ionică Bizău

then it hanged a while and I waited

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '42' at line 1
mysql> select count(*) from ActivityEvents;
+----------+
| count(*) |
+----------+
| 29260671 |

And look ^

In the backup I had only 10M documents

Fabby

and then :D

Ionică Bizău

Now checking the integrity

Fabby

looks like you're on the right track.

I would export the data I have now,

drop table

recreate table

Ionică Bizău

mysql> select * from ActivityEvents order by created_at desc limit 1;
+----------+------+--------+---------------------+---------------------+-----------+
| id       | raw  | active | created_at          | updated_at          | friend_id |
+----------+------+--------+---------------------+---------------------+-----------+
| 29276780 |      |      1 | 2018-04-20 13:26:03 | 2018-04-20 13:26:03 |         3 |
+----------+------+--------+---------------------+---------------------+-----------+
1 row in set (6.28 sec)

Fabby

18:23

import data back

Ionică Bizău

See ^

Fabby

Ah...

only one row

Ionică Bizău

According to my astronomical calculations, that was on Friday, when things got bad. Hahaha

Fabby

Forget what I said.

Ionică Bizău

order by desc limit 1

Fabby

18:23

I thought you had 29M rows instead of 10M

Ionică Bizău

Yes, I have.

I will make a backup once I get the others.

Fabby

Look like you're on the right track after all.

Ionică Bizău

Thanks a lot.

Oh, and there is the Mongo stuff. I hope to recover that too. Hahaha

Ionică Bizău

18:37

OK, full database recovered.

31M records.

This is what a bot collected in 3 months.

And I was like... all right... gonna start from scratch again.

« first day (2 days earlier) ← previous day last day (15 days later) »

all rooms

Transcript for

Apr22

Apr '1823

May8

Room for Fabby and Ionică Bizău

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC