$ mysql -uroot -p > file.sql instead of
$ mysqldump -uroot -p > file.sql

And not checking the result file before reinstalling my server 😭😭

So I was waiting on a mysqldump for over a halfhour wondering why it was taking way to fcking long.

But then I saw I typed mysql -u user -p database > dump.sql

Instead of using the mysqldump command xD at moments like this you realize how important sleep is xD

exec("mysqldump -u root -pxxxx ".$_GET["table"]);

Client be like:
Hello,

could you please restore our database from today's backup?

At a first glance - nothing out of the ordinary. Daily backups are standard...

Until we get the backed up snapshot running.

MySQLDump is somehow... Stuck. It... Doesn't seem to be doing... Like, anything. For ages. Wtf.

So we check the database. Connect, change scheme and... The commandline tool gets stuck, too. Weird.

So a layer lower, we check the datadir and... ls... After also getting stuck for a bit, lists about 500k files O_o

Yea, dumping a database with roughly ~250k tables is not fun. No wonder it takes ages.

Sleepless night
mysql -uroot -ppassword database | xz > dump.tar.xz

And I keep staring at the screen for 30mins wondering how fkin big the database is!

Pulled my hair out over one today (and a week ago when I first saw the issue)

Setting up development environment. Created test user and test database and used mysqldump to copy data over.

MySQL was executing a function as the wrong user. Checked my config files, checked my config reader, checked my database connection, checked checked checked. Checked everything twice, I felt like Santa.

Changed the password in the config file to make sure it was logging in right. It threw an error still but not one I had expected so I figured the login still worked (My bias was that I thought the config file was not working or the mysql library was caching authentication. Both were wrong but this blinded my debugging. Foolish, I have forgotten my training)

Logged into the database directly via client. *didn't bother executing the function because I was only testing auth*

Think
Think
Think

Search entire project for database username. It's gotta be hard coded by accident SOMEWHERE.

It's not.

Why
Why
Why

Wait.

-- Flashback to how the test db was created -- What's actually in this damn script?

DEFINER `production_user` CREATE PROCEDURE `old_db`.`procedure_name`

Two issues: definer is old user (this is the error I was seeing) and its creating the procedure on the old db (this would be the next error I would have found if I kept going)

Fuck mysqldump. Install mysqldbcopy. Works

Put hair back in head.

How do you restore partial data from a mysql backup? Don't worry, nothing is wrong, I'm just thinking about how would I restore something if shit hits the fan.

Our current strategy for database backups is to just run mysqldump during the night, using a cronjob (feel freue to suggest a better way ;))

1) Restoring the full db: just read that sql file into the mysql command.

2) Restoring just one (or some) tables: open the file in an IDE, just select the lines you're after, copy them to a new file, read that one (possible issues: let's say we have a table B to which entries of table A are related and we just want to restore table A. We can't nuke table B too, as also table C is refering to it, so we have to do some orphant removal in B afterwards)

3) Restoring selected entries in specific tables: setup a new db, read the full backup in there, dump these entries to a new file and read that into the real db

How do you so it? Any better aproaches/tools?

mysqldump db > db.sql
yum install mariadb
*config*
mysql -u username --password pass db < db.sql

Why do I control mariadb with the mysql word? I installed it, I want to use it....

I'm just dumping 10 GB of data remotely from a mysql db, because my el cheapo VPS run out of space

can you suggest a good book?
oh, actually I already found one, the title is "Prepare your fucking server/workspace properly if you want to play around with a lot of data"

The feeling when someone decided that it was a good idea to give all databases the same name across environments. So you have to:

drop database prod

On your MySQL prompt to restore the test environment from the latest mysqldump.

Never thought naming could be so hard...