Jump to content

need to backup mysql databases but mysql is broken


vailixi

Recommended Posts

I have SSH into this Ubuntu machine I'm checking out. I've tried just about everything to start mysql. But it's just broken. Some of the errors lead me to believe it's a file system issue like the system is out of space but it's not even close. I cleared the caches thinking that would do the trick after fussing with permissions and ownership stuff. There were some errors that made me think it was configurations. I haven't looked at dude's command line history but I'm pretty sure he broke it.

What is the best way to, well firstly back up the data?

Secondly copy all of the folders and contents from the mysql directory to another directory?

And finally completely wipe mysql reinstall?

Link to comment
Share on other sites

Regarding the backup, where would you want to back it up to? Full system or just the MySQL stuff?

One thing you could do is post the errors and see if someone here has any thoughts about repair options to try. I don't expect to be one of them though, as I only use PostgreSQL.

Link to comment
Share on other sites

I will go through and take some screen shots when I get back into it this evening. I was looking up error messages and fixes until late last night.

One of the issues it was returning was an error that the partition didn't have enough free space but when I checked it out with df it was only at 11%. So I thought it was a cache issue so I cleared all of the caches.

There was something wrong with a line in the the cnf file. It was key_buffer or key_buffer_size.

I think they broke the install then tried to fix it and now it is just all f*%ked up.

Probably I will end up just reinstalling. I'm still weighing the options.

Link to comment
Share on other sites

Well, you need it working to make a sensible backup. And unless you're a qualified repair man, you shouldn't try to repair. Was this business or pleasure? You'd expect a nightly backup of the data to be in place already for a paid gig at least.

Link to comment
Share on other sites

Well, you need it working to make a sensible backup. And unless you're a qualified repair man, you shouldn't try to repair. Was this business or pleasure? You'd expect a nightly backup of the data to be in place already for a paid gig at least.

It's not a paid gig. But it should be. It's more like an audition. The guys is a web page designer and publisher. There are like a dozen websites hosted on the server. Nothing major. But yeah I would expect a backup too. So at some point there is some way to restore the installation.

Link to comment
Share on other sites

So I went ahead and force deleted the mysql directory and uninstall the client server and commons packages. Then I reinstalled mysql.

The guy dumped all of the databases to one sql file. I'm not really sure how to load this thing back into mysql.

All of the procedures say I need to create a table with x tables x collumns charsets an all of that stuff then load the databases. I have no idea how many columns.

Is there an easy way to do this?

Link to comment
Share on other sites

Postgres does this too, but their SQL export file also creates any tables and/or indexes that weren't there yet. It then basically kills any constraints, does a bulk-insert and then re-initiates the constraints before committing the transaction. Um... MySQL these days DOES have transactions right? It's why I *HATED* MySQL back in the day. A database without transactions is like a fishbowl without water - sure you can add some fish, but you'll notice the errors of your way quite swiftly.

I'd say print the first X lines of the file to see if the schema was exported along with the data. If the schema wasn't, ask them to provide it (they should be able to) and if they fail this suggest to the client to think of someone to fire over this, because that's the appropriate response to this level of incompetence.

Link to comment
Share on other sites

I'm sure it's in one of the users' command line history. So I tried to restore this through the command line. Then I went ahead and installed phpmyadmin and tried to do it that way. But I could not import the data because phpmyadmin will only let you import files up to 2048kb. Is there a way to change that? I might be able to do this with phpmyadmin if I can change the default max upload size.

Link to comment
Share on other sites

There are 2 ways to go about this:

1. Attempt to resurrect based on the things you know, talking as little to the client as possible.

2. Involve the client in the resurrection process, let them understand how badly they fucked up, where their responsibilities actually end and what they're supposed to do to keep a system live.

By going with 1, which it feels like you're doing, you may save a little bit of time, but the client will not know what you did so he will not understand the amount of effort you're putting into this. As a result, whatever you say afterwards about it being difficult will fall on deaf ears. By going with 2 you actually educate the customer, keep him informed, display both skill and professionalism and you'll breed some understanding of the shitstorm they've unleashed upon themselves by not having their shit together. Note that if they DO have their shit together and they DO have a schema somewhere, they'll be mighty pissed when the schema you come up with doesn't match theirs.

Link to comment
Share on other sites

Look in your sql dump and see if it contains some "create database" statements? if not then you'll need to create the databases in MySQL before loading the dump. After that you should just be able to load it with the command line client

Assuming that you want to load it as MySQL's root user then you'll want to use a command like this: (-u parameter is the user and -p indicates that you want to be prompted for a password).

mysql -uroot -p < dumpfile.sql

As to your original errors about not having enough space on your drive even though you were only using 11%, did you check how many inodes you had free? as running out of inodes can be reported as no space on the device in some situations. You can check your inode usage with df:

df -i
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...