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.
Hope this helps!
