The process of importing and exporting databases occurs frequently during the course of creating, updating and moving Web Apps. If you lose or damage your data, data dumps can be used to restore it. Data migration is another option that may be taken advantage of using these tools.
There are a number of ways to work with MySQL or MariaDB database dumps in this tutorial (the commands are interchangeable). You will import a database from a dump file once you have exported a database.
To import or export a MySQL or MariaDB database, you will need root Access to a cloud VPS server or Dedicated server.
Normally small databases can be imported and exported through phpMyAdmin. but in case of big databease files you will face many issues such as File upload size limit, Server time out. Those issues can easily overcome below method.
Login into your web server with root user using SSH.
Use mysqldump to export your database:
mysqldump -u username -p database_name > data-dump.sql
username
is the username you can log in to the database withdatabase_name
is the name of the database to exportdata-dump.sql
is the file in the current directory that stores the output.Above mysql Dump is saved in root folder of server. to access mysqldump you need to move dump file from root folder to /var/www/html/DB folder.
You must create a new database in MySQL or MariaDB to import an existing dump file. The imported data will be stored in this database.
In our case uploaded the file in directory : /var/www/html/DB
OR
/var/www/html/DB
cd /var/www/html/DB wget http://your_old_server_ip/data-dump.sql
To begin, log in as root or another user with adequate access to add new databases to MySQL:
mysql -u root -p
This command will open the MySQL shell.
Create a new database with the command below. In this case, new_database and then use use command to use new_database import SQL DUMP using Source command inside MySQL shell:
CREATE DATABASE new_database; use new_database; source /var/www/html/DB/data-dump.sql;
mysql -u root -p
CREATE DATABASE new_database; exit
mysql -u username -p new_database < /var/www/html/DB/data-dump.sql
username
is the username you can log in to the database withnewdatabase
is the name of the freshly created databasedata
-
dump
.
sql
is the data dump database file to be imported, located in the current directory