mySQL : exporting and importing databases


If you are using MySQL or as it’s actually named these days Maria, it would appear MySQL is now paid for solution.

If you wish to manage these databases, many people don’t like command line shells so they will be tempted to install PHPmyadmin - which is essentially a web interface for your database That’s ironically, runs on the same server usually as your Apache instance, I am not a massive fan of allowing a website with remote access to manipulate your databases.

I would rather use the shell, It’s less too haunting, click arrows, and you need to know the command you’re running to get the results you need, This is obviously not full proof you have less chance of making a mistake with a command line interface that fancy website that manages your SQL.

Exporting your database

mysqldump -u username -p database_name > export_db.sql

Check the Export

head -n 5 export_db.sql

When you run this it should start something like this:

SQL dump fragment
-- MySQL dump 10.13  Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost    Database: <database>

Import the database

If you only have one account I would recommend creating a new account, so in this example update the <db_name> with the name of your database, and from there we will create an account called importer with the password of SecretBearPassword:

sudo mysql
GRANT ALL PRIVILEGES ON <db_name>.* TO 'importer'@'localhost' IDENTIFIED BY 'SecretBearPassword';

Then, now you have the new account use this to import the database use this:

mysql -u username -p new_database < export_db.sql

This is successful if you get nothing returned, the import is now complete.

Previous Post Next Post

Ω†Ω…ΩˆΨ°Ψ¬ Ψ§Ω„Ψ§ΨͺΨ΅Ψ§Ω„