Using command-line interface to export or import database is very important, usually if your site have huge database table. So. you can’t just rely on database manager GUI like phpMyAdmin or Adminer to import/export database. It can stuck if you importing large size of database file via phpMyAdmin or Adminer.
Now, in today guide I will teach you on how to import/export database via command-line interface. This simple snippet code can be using on almost distribution system, including RHEL/CentOS/Fedora or Debian/Ubuntu server.
Follow this guide to import/export DB file on your machine.
REQUIREMENTS:
* Server IPv4 Address
* Full Root Access
* Password Root
* Gnome Terminal or PuTTy
* Root Access to Database Server
1.) Login to Your Server via CLI
$ ssh root@server_IPv4_address
2.) Login to Your Database Server
$ mysql -u root -p Enter password:
3.) To export a database, simpy run the following command.
$ mysqldump -u [username] -p [database name] > [database name].sql
4.) To import a database, run this command. Make sure you have previously create a new database and database user along with its password.
$ mysql -u [username] -p newdatabase < [database name].sql
5.) If you want a create new database and database user, you can using the following command.
$ mysql -u root -p
$ CREATE DATABASE databasename;
$ CREATE USER databaseuser@localhost;
$ SET PASSWORD FOR databaseuser@localhost= PASSWORD("password");
$ GRANT ALL PRIVILEGES ON databasename.* TO databaseuser@localhost IDENTIFIED BY 'password';
$ FLUSH PRIVILEGES;
$ exit
