Cloudbooklet
  • News
  • Artificial Intelligence
  • Applications
  • Linux
No Result
View All Result
Cloudbooklet
  • News
  • Artificial Intelligence
  • Applications
  • Linux
No Result
View All Result
Cloudbooklet
No Result
View All Result
Home Linux

How to Import Export Database in MYSQL MariaDB

by Cloudbooklet
2 years ago
in Linux, MySQL
How To Import Export Database In Mysql Mariadb
ShareTweetSendShare
Readers like you help support Cloudbooklet. When you make a purchase using links on our site, we may earn an affiliate commission.

Import or Export database from MySQL or MariaDB database from MySQL shell or command line. This is the easiest method to export database or particular table from a database. If you have a huge database which is more than 500Mb, it will be very hard to export which takes more time to import or export […]

ADVERTISEMENT

Import or Export database from MySQL or MariaDB database from MySQL shell or command line. This is the easiest method to export database or particular table from a database.

If you have a huge database which is more than 500Mb, it will be very hard to export which takes more time to import or export the entire database which may result in timeout error.

In this guide you are going to learn how to import or export database using command line from MySQL shell. You will also learn some fixes to the problems where you cannot export your database.

ADVERTISEMENT

Prerequisites

  • Server SSH access.
  • MySQL username, database name, password to export database.
  • MySQL user and password who has privileges to create database for importing.

Export MySQL Database from Command Line

Login to your server SSH and take note of your database name, username and password. Make sure your user has full access to the database you wish to export.

You might also like

&Quot; Systemd Service On Linux

How to Create a New systemd Service on Linux: A Step-by-Step Guide

3 months ago
List Groups In Linux

How to List Groups in Linux: A Guide for Beginners

3 months ago

You can use the following command to export the entire database.

mysqldump -u username -p database_name > filename.sql

You will be prompted to enter the password for the username.

ADVERTISEMENT

Once you have entered the password your database will be exported to a .sql format file and stored in your current directory.

Problems where you Can’t Export Database

Access denied error.

ADVERTISEMENT
  1. You can try login as root user sudo su and export the database.
  2. May be password mismatch.
  3. May be user don’t have sufficient privileges to the database.

Access denied PROCESS privilege(s) error.

You need to use the --no-tablespaces option with the mysqldump command. So the command will look like the one below.

ADVERTISEMENT
mysqldump --no-tablespaces -u username -p database_name > filename.sql

Export Only Certain tables

To export specific tables of a database from command line you need to pass the table names as shown below.

mysqldump -u username -p database_name table1 table2 > filename.sql

The above command will export only the table names specified.

ADVERTISEMENT

Import MySQL Data from Command Line

This is the best method to import a huge database file without any errors.

Upload the .sql file to your server. Login to SSH and navigate to the directory where you have uploaded the database file.

If you don’t have a database you need to login as a user like root to create a database.

CREATE DATABASE database_name /*\!40100 DEFAULT CHARACTER SET utf8mb4 */;

To import database you can use the following command.

mysql -u username -p database_name < backup.sql

Make sure you have enough privileges on your database.

Caution: If you use > instead of <. All the data in the backup.sql will get erased or corrupted.

If your file size is big the time taken will be high for importing or exporting.

Conclusion

Now you have learned how to export and import MySQL database from command line.

Thanks for your time. If you face any problem or any feedback, please leave a comment below.

Tags: MariaDBMySQL
Share9Tweet6SendShare
Cloudbooklet

Cloudbooklet

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Related Posts

Hostname In Linux

How to Modify the Hostname in Linux

3 months ago
Linux Systems

Linux systems Hacked with OpenSSH Malware

3 months ago
Install Iptables On Ubuntu

How to Install Iptables on Linux

3 months ago
Open Port In Linux

How to Open Port in Linux: Simple Step-by-Step Guide

3 months ago

Follow Us

Trending Articles

Ai Girl Generator

7 Best AI Girl Generators for Creating Realistic and Beautiful AI Girls

September 19, 2023

5 Free AI Soulmate Maker: Create Your Perfect Match

How to Block YouTube Ads on Android TV in 2023 (6 Easy Methods)

Microsoft Surface Event: The Most Exciting and Innovative Launches and Updates

Google Bard Extensions: How to Link Your Gmail, Docs, Maps, and More to an AI Chatbot

Create High Quality AI Cover Song with Covers AI

Popular Articles

Notepad++ V8.5.7

Notepad++ v8.5.7: What’s New and How to Download It

September 13, 2023

Google Dark Web Report: Protect Your Personal Information

Remodeled AI: How to Transform Your Home with AI Interior Design

10 FREE Cleanup AI Tools to Remove Objects and Cleanup Pictures

7 Free Undress AI Tools to Remove Clothes from Images

Ideogram AI Art Generator: The Ultimate Tool for Generating Amazing Artworks

Subscribe Now

loader

Subscribe to our mailing list to receives daily updates!

Email Address*

Name

Cloudbooklet Logo

Welcome to our technology blog, where we explore the latest advancements in the field of artificial intelligence (AI) and how they are revolutionizing cloud computing. In this blog, we dive into the powerful capabilities of cloud platforms like Google Cloud Platform (GCP), Amazon Web Services (AWS), and Microsoft Azure, and how they are accelerating the adoption and deployment of AI solutions across various industries. Join us on this exciting journey as we explore the endless possibilities of AI and cloud computing.

  • About
  • Contact
  • Disclaimer
  • Privacy Policy

Cloudbooklet © 2023 All rights reserved.

No Result
View All Result
  • News
  • Artificial Intelligence
  • Applications
  • Linux

Cloudbooklet © 2023 All rights reserved.