Why this post?
As always I post something when I find that something simple isn’t so simple. Here is the case again. I had done lots of MSQL dumps (for backups) but realized I never had to actually manually recreate one. So then I started and as always, mysql is a bit scary and sometimes counter intuitive. So that’s why this post. Giddy-up!
Assuming We Dumped…
This post assumes you already have the .sql file that you already dumped using a command that looks something like this.
sudo mysqldump -u akauntuser -p akaunting_db> /home/user/akaunting_backups/210531 akaunting_backup_210531.sql
Now, I wanted to talk about this for a second. You see, I had run a command like this to dump and you can see the user (-u akauntuser) is not root (-u root). Keep this in mind for later when I help you overcome something counter-intuitive
Loading the Dumps
I just watched this windows-based tutorial (like why does anyone use windows for this? it’s way harder…I digress) at about 5 minutes in where he starts loading.
At first I set my command up like this and ran it:
mysql -u akauntuser -p akaunting_db < dumpedDatabase.sql
I assumed that MYSQL would be smart enough to:
a) create my user (I provided it in the command, after all…)
b) load it
Wrong. Team MYSQL does things differently….
So you have to slide back in his video more to here where he creates new database first, like so:
CREATE DATABASE akaunting_db;- make sure it’s there:
SHOW DATABASES;
Once that is created and you feel confident it exists in MYSQL, you can fling-a-dump! Whee!
Dummy tip 1! I tried to run this every-day command while logged into the mysql prompt area and if you do that you can’t tab to find your backup file. In fact, I don’t know if it works at all.. but anyways, what I found best is to cd to where your dumped .sql file is and run the full command there. Then you can tab and autocomplete your backup sql file name more intuitively.
Dummy tip 2! I tried to run the command with the main database username. Seemed logical – after all I had it in my dump-fest! But then nothing worked and password failed. Finally, I more diligently paid attention to buddy’s video above and used -u root (ie. root user of the machine) just like him and the root user password to match and then the dump… dumped… so here is that syntax using my example running in the usual, non-mysql terminal zone:
mysql -u root -p db_mynewlycreatedDBname < mydumpedSQLfile.sql
Then I just did these dope vanilla mysql prompts, logged in there, and ran the SHOW DATABASES; command then USE db_mynewlycreatedDBname; and finally SHOW TABLES; to prove to myself the tables were loaded – and they were.
That’s a lot of dump in such a little blog.
Creating the MySql user (especially Word Press!)
Although I was well acquainted with creating a new user for a new MYSQL database, somehow I assumed that when you import the full database in as per above, that it would magically pull in the formerly-associated database user as well.
My assumption was wrong and I was dumb and I got a ‘database connection’ error message and nothing worked.
How it works is this, I guess: A MYSQL database is simply an empty room designed for a lively assembly of tables (and chairs?) and stuff to stick on the tables. But it doesn’t provide the people, the tables (or the chairs), or the stuff that goes in the tables – ever – when you mysqldump it For some reason… anyways, all you have to know is that whenever you move a mysql database, you have to manually recreate the database and the users on the new server. Once those are created (identically) the ‘tables and the chairs’ will arrive magically with the import above. I hope this little teaching saves you some pain. So, as a final step on the database side – and especially if you are moving a Word Press site over – you have the manually create the same user that was working on the original server with this command, inserting your credentials, keeping them identical user name as password as on previous installation to save some pain and time:
- Log into mariadb:
sudo mysql -u root -p - Create your database with the database name you saved to your password manager above:
CREATE DATABASE db_yourdatabasename; - Create the user and password for the database replacing username and password in the following code with yours between the apostrophes:
CREATE USER 'username_here'@'localhost' IDENTIFIED BY 'new_password_here'; - Do whatever this does, lol:
GRANT ALL PRIVILEGES ONdb_yourdatabasename.* TO 'username_here'@'localhost'; - Flush stuff:
FLUSH PRIVILEGES; - Exit stuff:
EXIT;
Hope this all helps
