💾 Archived View for wireheadmechanist.com › buster › whm21371.gmi captured on 2023-06-14 at 14:20:03. Gemini links have been rewritten to link to archived content
⬅️ Previous capture (2022-07-16)
-=-=-=-=-=-=-
all database yay.
So this was the goal: to take a database dump from my webserver where a database should not ever be and import it into the replication set.
This may be ghost-specific.
These are the things to learn today:
- dump the database into a SQL file
- learn how I make shit harder
- use scp to transfer from the web to the DB
- discover that mysqlimport is the wrong damned tool
- use MySQL to discover I have a collation issue
- resolve that collation issue with sed
-bounce the connection, sip whiskey, and grin like a mofo
The first thing is to rip the database from the onboard database because that is the thing you want to eliminate. Your command will look like this:
mysqldump -u {username} -p {databasename} > thefileresult.sql
it will probably ask for a password if it's polite. They usually are. But then you have that lovely SQL file sitting in your home directory, all dressed and noplace to go.
"Well, just copy that bad boy over to the database server and let's get on with this madness!"
Not so fast, Cochise.
Remember this girl?
[Tuesday meme]
Tuesday is a badass chick.
So Tuesday is my girl. She's a smoking hot Debian 10, and she isn't up to 11 yet, but that's okay. She handles the heavy lifting and keeps all the outside baddies from getting into things.
My database servers have no outside access on any port. Tuesday talks to the webby things and the data buckets. The webbies talk to the buckets only on the database port. No SSH access between them.
So the way to get around this and move the file where it needs to go is going to be a three-way scp command. If you have tldr then you already know this, if you don't, here's that nifty command:
scp -3 {{host1}}:{{path/to/remote_file}} {{host2}}:{{path/to/remote_directory}}
You'll probably have to enter a password each time for both servers. But it works, and it made me feel all sexy a little bit.
I got the file onto the database server and logged in ready to pull this puppy over to the curb and call it.
LOL.
Nope.
I tried to use mysqlimport like a moron. That's not what that does. You'll want to use MySQL to push it up.
mysql --user {{user}} --password {{database_name}} < {{path/to/backup.sql}}
Oh! How cool! The beers are a poppin'!
Not yet. I got to deal with this angry monkey:
Error 1273 (HY000) at line 25 Unknown collation: 'utf8mb4_0900_ai_ci'
Nice.
[Downey meme]
And here the phun begins yet again. But it turns out that there's a solution for that, too.
You're gonna have to play with the SQL file a little, with a friend named sed.
Here is the solution to that stupidity. And for you bastards without patience, do this:
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql
These correct the stupidity that has brought a tear to your eye and made your pecker, should you have one, shrivel just a bit. No apologies, it just sucks to have these errors when you're trying to do something useful.
Like that guy told you that had less teeth than you have children that time in the parking lot when you left the headlights on all day...
Nevermind that.
Go ahead and spark up that import again and watch it finally complete like it's supposed to.
Now we can go ahead, sit back, sip whiskey (mine is either Evan Williams Honey or Celtic Honey) and call it a day. Try the Celtic Honey. I shipped some to my brother-in-law, and he swears by it.
He knows a thing or two about stuff like that.
https://sipwhiskey.com/collections/celtic-honey-liqueur/liqueur
