MySQL Workbench Database Export and Import
HTML-код
- Опубликовано: 24 фев 2016
- Learn Database Export and Import using MySQL Workbench. Open MySQL Workbench and logged in to MySQL database server using 'root' username and password. In previous couple of videos we learned how to Create, Alter, Drop Databases and Tables and also how to Insert, Read, Update, Delete data rows using MySQL Workbench. In this lecture we will learn how to Export and Import the database using MySQL Workbench.
First of all to Export the databases or tables, from top menu 'Server' select 'Data Export' Option, which opens a new window of data export settings and options. The left section on the window displays all the existing databases on our MySQL database server. When we select a database by clicking on it, it displays all the respected tables under the selected database. We can select one or multiple database check-boxes to include the database in the Export file. Under 'Export Options' section, You can change the default export path of you choice. I'll keep the default one. By selecting first option that is 'Export to Dump Project Folder'. MySQL Workbench will save the save all tables as separate SQL file under one folder. Which is useful when you will be importing or restoring the export file one by one table. I'll go ahead quickly and first save as 'Export to Dump Project Folder', MySQL Workbench displays the progress bar and log. Now, I'll again export the database using 'Export to Self-contained File' option.
To demonstrate the import first I'll delete the existing 'students' table under 'mytestdb' database. Display database details by clicking on 'i' symbol, select 'tables' tab. Select and right click 'students' table, and click 'Drop Table' option, confirm and delete the table. Click 'refresh' to update the tables section and we have no table in our 'mytestdb' database. Now select the database and from 'data import' window, select 'import from dump folder. 'Load folder contents' and select only 'mytestdb' database and click start import button to import 'students' table again. Go to 'mytestdb' database information window again and click 'refresh' button to reload table list again. and the 'students' table is back.
Check out the whole playlist of MySQL Workbench Introduction Tutorials or Individual video from the following links,
MySQL Workbench Tutorial: Introduction to MySQL Workbench for Beginners Playlist
• MySQL Workbench: Under...
MySQL Workbench: Understand What is MySQL Workbench?
• MySQL Workbench: Under...
MySQL Workbench Add User and Connect to Database
• MySQL Workbench Add Us...
MySQL Workbench Create, Alter, Drop Database
• MySQL Workbench Create...
MySQL Workbench Create, Alter, Drop Table
• MySQL Workbench Create...
MySQL Workbench Insert, Read, Update, Delete Data Rows
• MySQL Workbench Insert...
MySQL Workbench Database Export and Import
• MySQL Workbench Databa...
Subscribe to my channel: / @pntutorialsbypradnyan...
![Jelly Roll - Dead End Road (From Twisters: The Album) [Official Music Video]](http://i.ytimg.com/vi/_Mb8CqKW4R8/mqdefault.jpg)
Thank you. Have been searching how to backup the db and keep getting command line tutorials, you are one of the very few to show how to do it using Workbench GUI.
Thank you for the positive feedback. Glad to know my video tutorial was helpful to you.
Nice! Thanks for this tutorial!
Keep helping people, you're a good man...
Thank you Michael, I appreciate :-) your positive feedback is really motivating. I'm already working on creating new content for RUclips. Soon I'll upload database and programming tutorials. Thanks again for the positive feedback.
Thanks a lot for this. Have been searching so long for this export , import in mysql workbench finally found a clear explanation video. Thanks a lot
Hi PS R, thank you for your feedback. Glad to know that this database export & import video tutorial was helpful to you.
thanks random indian guy from youtube for saving my life
Thank you Carlos ( A random viewer from RUclips 😂 )
Thank you ! Sometimes we think more than we should and we made things more difficult that they actually are, your explanation was very helpful.
You are so welcome, Patricia 😇🙏 You are absolutely true. This applies to everyone's life, we make things unnecessarily complicated.
A really helpful tutorial even in 2021. Thanks for the help!
nice PFP!!!
Glad to know it was helpful to you :-)
Hello,
MySqlWorckbench opening and opening the connection I get an alert box that says something like this:
"Incompatible / no version or server connection standard protocol detected (10.0.28). A connections to this database can be established some MySQL Workbench features may not properly since the database is not fully compatible with the supported version of MySQL. MySQL Workbench is developed and tested for MySql Server 5.1,5.5,5.6 and 5. "
Ok, but how should I proceed? What should I download (link to what?)
Can you help me?
Thank you!
Thanks mate! you did a great job on this tutorial!
Thank you Christopher!
Thank you for the short and sweet tutorial. I would like to import a new table
(not the exported one) which is in csv format. Could you please tell me how to do it.
+Dharmar Gurusamy Check "Importing CSV file using MySQL Workbench" section on this page,
www.mysqltutorial.org/import-csv-file-mysql-table/
Let me know if it works.
Straight to the point, thanks!
Thank you Franck for the positive feedback, I appreciate it :-)
Thank you for the tutorial link for importing csv file into MySQL database.
I got it using command Line. In workbench, when I opened the table, only
Export is appearing. There is no Import. But I got the work done.
Could you please tell me what is the command for clearing the screen in command line client.
+Dharmar Gurusamy If you are in DOS you can clear the screen using "cls" and hitting enter button. but four MySQL command line client, you can not clear the screen.
Excellent! thank you for your explanation...
Thank you Viknesh :-)
Thank you for
detailed video!!!
Thanks for the feedback, I appreciate :-)
Hello, although u selected dump structure & data, but while u had exported the students table had 1 row.(could see it before u deleted) and after u imported, students table had 0 rows..
Why is the data not loaded when u imported ?
Hi Manjunath,
First of all I'm really surprised & impressed with, how carefully you watched the tutorial.
Please Note: Before recording the video, I took a couple of export & import for testing purpose. Also, the video isn't recorded in 1 shot. I've joined export & import videos together. While backup I exported the database table with one row and while import I might have imported the table backup file with no data rows. I'm sorry it caused an unnecessary confusion to you. I should have taken care and displayed the data rows imported successfully. I'll definitely consider these small things while recording the next videos.
The database export & import using MySQL workbench will work just like I've explained in the video. The "Dump Structure Only" will save table structure, "Dump Data Only" will save only data rows and "Dump Structure and Data" will save table structure with data rows. You can test it at your end. If you encounter any issue or didn't understand feel free to ask, I'll do my best to help you ASAP. Plese Note: I've a 11 hours of premium course on SQL using MySQL database on Udemy. This is part of that course. If you are interested I can send you course link with discount coupon.
Thank you for pointing out my mistake and once again sorry for the confusion.
Regards,
Pradnyankur
This is great. Do you have any tutorial on how to open a pgd object (postgress database) in MYSql?
Hi Vinicio, No, this RUclips channel doesn't have video on that topic.
Thanks for the lesson.
Thank you brother, glad to know the video was helpful to you. Hey, very creative design in your username. And, I feel you are not really an arrogant person. Nice username though, hahaha. Thanks once again for the feedback, I appreciate :-)
Thanks you very much. Man
Thanks Adams :-)
Thanks for the video.
How is it possible to import a csv file and update the present data? I tried but it only adds new rows, I can't edit existing data.
Thanks
Thank you Massimo. Yes, you can import the csv files via MySQL workbench. In order to update existing rows, it depends on table structure or which columns/rows you wanna update. I would suggest you to use a programming language, it will update the data rows while/after import of the data.
Awesome thank you so much!!
Thank you too :-)
Very helpful video. Thank you
Thank you Susan :-)
Thank you so much !!
You are welcome Nigel :-)
I'll be adding MySQL video tutorials soon. So, if you are interested in leaning MySQL, subscribe to my channel so that you'll get notifications of newly added videos.
hi everyone
im use ubuntu 16.04. actually i want to import a single database from multiple databases file.first i'm make a diff. file from multiple databases that i want to import in a new database.the file of 1 database is successfully created from multiople databases file but when i'm import that file in new database import command runs successfully and in database there is nothing imported.new database is empty.plz help..........thnx in advance
Thank You!
You're welcome, Wegyu 😊🙏
Best tutorial ever thanks friend
Thank you Zaikore for the positive feedback, I appreciate :-)
How does it work if we have a large Excel data base. Let's say either a large Excel workbook with multiple Worksheet and/ or tables? Can we import? Can we keep the cell types, formats and structure. etc?
Hi M Sadr,
We can import data from .csv file to MySQL database but not from workbook. You can even use programming language form that.
Regards,
Pradnyankur
Thank you all the other websites on the internet suggest weird complicated ways to do this. I was starting to lose faith in our programming progress as humans.
Thank You! You explained very well
Thank you Mamali :-)
Does this work to export database from workbench, to import it to the phpmyadmin then?
Hello Edi bytyqi , Yes the exported database file can be imported by using phpmyadmin
Hi,
I have been using MSSql for over five years and my data size is over 50GB and the ralational tables are quite complex.But now i decided to move in to MySql 8.0. Now i need to migrate all the data to MySql considering relation and large scale data , i tried workbench but its get stuck for such a large migration , is there any easy way, process or tools to migrate such a large data ? Thanks
Hello Rakib, I would recommend you to migrate your data via MySQL command line client tool. There are MySQL import statements which can be executed to import such large data.
Thank you.
Welcome Kevin :-)
should we already need the database to import it from dump file?
Hi Ragavendra,
Yes! In order to use MySQL Workbench and do the database export or import you must have MySQL database server installed on your computer. You can install MySQL database server by following the steps given in following video tutorial of mine. Here is the link,
ruclips.net/video/Ddx13KlW8yQ/видео.html
Let me know if it helps.
Regards,
Pradnyankur
I love indians man. some of my all-knowing professor are so by the book that they fail to express simple tasks like this. My professor told me i had to write a line of code to export...
Thank you for the feedback.
When you work in MySQL workbench, you use graphical user interface. And sometimes, small tasks like export or import seems complicated. It can be done in single statement via command line client windows. You should be familiar with SQL statements for that.
You can checkout my SQL course on Udemy if you are interested in learning SQL.
Thanks again for the feedback, I appreciate it 😊🙏
Could I export the db file to CVS file?
Yes! you can. Checkout the following link,
stackoverflow.com/questions/17113812/how-to-export-table-data-in-mysql-workbench-to-csv
thanks dear , thankyou so much.....
Thank you Sumit :-)
Thanks!
Welcome!
thank you, but you should have made the scripts for the video first, that might have shortened the video under 3 mins. but again thank you, it helped
Thank you Vipual for the positive feedback and suggestion. For upcoming tutorials, I'll try to make the videos short and to the point.
Thank you,
Thank you too :-)
Hi! :) when I try to load tables, it runs and tells me it's over, but it doesn't load them into my selected database ... why don't I see them?Thx :
Hi Darius, Are you getting any error message? Have you tried importing the database tables using MySQL command line client?
sir please make a video on how to import .db file into MySQL by using commands in MySQL coMmand prompt
Hi Deepesh, Thank you for your valuable suggestion, I appreciate. Sure! I'm working on MySQL database topics. And definitely MySQL export & import will be covered.
Thank you
You're welcome
Hey!
I found your video very helpful.
Please, can you provide me the setup of this MySQL workbench and tell me how can I set up exactly like. Because I need the same setup but I am unable to create it.
Good Day
Hi Fiesta,
Checkout my another tutorial where I've given step by step instructions to download and install MySQL server and MySQL workbench. Here is the link,
ruclips.net/video/Ddx13KlW8yQ/видео.html
Let me know if that helps.
Regards,
Pradnyankur
can you tell me how to import log files in mysql server
Hi Anjali,
Sorry for the late response. I've been busy with some offline stuff. Checkout the link below explaining how to see log files in MySQL,
stackoverflow.com/questions/5441972/how-to-see-log-files-in-mysql
Thankyou
You are welcome Abhishek 🙏😊
Ehrenmann
Can i open SQLite db on MYSQL workbench?
Sorry for the late response, I've been busy offline. Importing SQLite database to MySQL workbench is little tricky. You can't directly backup SQLite to .sql file and import it to MySQL. Because there are differences in keywords/syntax. You'll have to convert SQLite to MySQL. You can checkout online solutions for conversion.
@@PnTutorialsbyPradnyankurNikam wow thanks for your input, really appreciate it
merciiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii
I just used Google Translate to read what it mean. It means "thank you" in French language. Wow. I never thought, I would be able to help foreign people with foreign languages. RUclips is awesome.
merci aussi my friend :-)
Hi, informative video!! Nevertheless, do you know what to do when database's tables aren't shown(there is no table to choose but database has ones)??
Hi Albert,
If selected database has tables, it should be visible. Even the tables doesn't have any record, you can at least see table structure. Have you selected correct database and looking for tables? Also, do you have privilege to see the tables? If you are using root user account, you should have full access.
If you think, something isn't working as expected in MySQL workbench, you can check it out via command line client tool.
1) Login to your MySQL server using root account credentials via command line client window.
2) select the database using,
USE databasename;
3) Display list of tables using,
SHOW TABLES;
4) And you can see table records using,
SELECT * FROM tablename;
Let me know if it works.
Regards,
Pradnyankur
@@PnTutorialsbyPradnyankurNikam, yup, I selected database with tables but they aren’t visible.. I’ll try to use command line
Are you using root user account or another account? Confirm, if you have privilege to access database tables?
@@PnTutorialsbyPradnyankurNikam I have added all privileges but nothing changed
Hi Albert,
If you can access the table and it's data from root user and not from other account. It means, you haven't configured privileges properly.
Checkout following link to read more about privileges available in MySQL database.
dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
I hope it helps.
Regards,
Pradnyadnkur
I want to know by exporting I can share it with my friends ?
Hanan Khan, yes, you can backup the database, table and data and share with your friends. They need to import the backup file on their mysql server.
PhpSword we did it , but it didn’t work, do i need to create a new database to import it ? cuz I tried everything but still not working.
Hanan Khan If you took the whole database backup, create a new database on new database server. Select the new database and important the backup file. Are you getting any error while importing? If yes, please paste the error message so that I can take a look and give solution.
This is the error that am getting when i import it ? (this is with an old Xampp version)
16:29:06 Restoring bookmark (accessories)
Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysql --defaults-file="/var/folders/ws/2tl5kd9d6fqfbfv5s3z4sm600000gn/T/tmpt7QU_p/extraparams.cnf" --protocol=tcp --host=127.0.0.1 --user=root --port=3307 --default-character-set=utf8 --comments --database=bookmark < "/Users/Hanan/Downloads/dump/Dump20171119.sql"
ERROR 1005 (HY000) at line 85: Can't create table `bookmark`.`payment operation` (errno: 150 "Foreign key constraint is incorrectly formed")
and every time i try to do a database and enter it i get like a warning saying :
Incompatible/nonstandard server version or connection protocol detected (10.1.10).
A connection to this database can be established but some MySQL Workbench features may not work properly since the database is not fully compatible with the supported versions of MySQL.
MySQL Workbench is developed and tested for MySQL Server versions 5.1, 5.5, 5.6 and 5.7
______
then i downloaded the new XAMPP and there is no connection!
Hello Hanan,
In your database structure, you've referenced the data type of the foreign key incorrectly. In your case the "payment operation" column in "bookmark" table is referencing to another column of another table. That's the reason it's not importing the database. You need to correct the foreign key reference then backup the database and import on your friends MySQL server.
For Examples: The "payment operation" column data type in one table is INT(10) and while defining foreign key you referenced it like INT (5). Once you make the correction in reference, you can backup and import the database.
Before, modifying the foreign key reference, try importing the database using SQL statement by following given steps.
1) On Windows command prompt type, cd "path to mysql bin folder"
2) Now execute, mysql -u root -p {databasename} < "c:\backup\databasename.sql"
If the above statement doesn't work. Modify the foreign key reference, backup and import. Let me know if it works.
I have dump file database not SQL. How can I do?
Hello Sabbr, What is extension of the backup file?
@@PnTutorialsbyPradnyankurNikam .dump
Hi Sabbr Hajri,
Yes! You can import database and tables by using .dump files. Here are the steps.
1) First of all login to your MySQL root user account.
2) Create the "database name" that is going to import.
3) logout of MySQL root user account.
4) From Windows command prompt, change the bin directory to MySQL bin folder like,
cd "location to bin folder"
cd "C:\Program Files\MySQL\bin" (For MySQL 5.7)
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin" (For MySQL 8.0)
5) On bin folder prompt, execute the following SQL statement,
mysql -u root -p databasename < "C:\\backup\\databasename.dump" (Use appropriate path)
6) Press enter and you'll need to enter root user password.
7) If the import is successful, you won't see any error message or success message.
8) Login to root user account to confirm if the database import was successful.
Let me know if it works.
Regards,
Pradnyankur Nikam
This looks nothing like the ipower Mysql dashboard I'm using.. I need to backup my database and I'm stuck.
I've given the steps to import as well as export the database using MySQL workbench. What problem/error you are getting while export. Please provide the details so that I can help you fix it.
@@PnTutorialsbyPradnyankurNikam I sorted it out, thank you for your reply.
COOL
Thanks Dalmas
how can I import an xml file
Hi javanut811, In order to save data to xml or any other file type, you'll have to use some scripting/programming language.
indysara
Not working for me
Voice like a pockemon))) but good job
Hahaha, Great compliment :-) That's really good for me. I can do database development and in free hours, do Pockemon voice dubbing artist job. Anyway, I hope you understood the topic covered in this video. If you encounter any error or didn't understand something feel free to ask questions, I'll do my best to help you ASAP. Thank you for the positive feedback, I appreciate :-)
Your export did not contain data. You fail to show it.
+Rassul Saeedipour This is one of the video from the MySQL workbench playlist. If you browse other videos, I've shown how to create, update delete database and also how to insert data inside the database table using MySQL workbench. In this video I just showed how we can export the data. The data was already in the table we were exporting. Checkout the video where I've shown how to insert the data in the table here, ruclips.net/video/qb7abQ6ROy4/видео.html
Thank you.
Thank you Mayank 😊😇
Thank you
Thank you Felix :-)
Thank you
Thank you Tahir