XAMPP phpMyAdmin: Importing of Large SQL files to database
HTML-код
- Опубликовано: 7 сен 2015
- phpMyAdmin Error: "You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit."
Here's a simple tutorial of importing large sql files to your phpMyAdmin database.
Summary:
1. Modify the my.ini of the mysql module from the XAMPP Control Panel. The only thing to modify from the my.ini file is the buffer size. Change 8M to whatever value that you want for as long as it is more than the file size that you are trying to import. Then save the changes.
2. Restart the MySQL module from the XAMPP Control Panel to enable the changes that you made.
3. Open Command Prompt.
4. Change your directory to where the bin folder of the mysql module of your XAMPP is located. e.g. "C:\xampp\mysql\bin"
5.In your command prompt, type in "mysql -u username -p database [less_than_symbol] sql.file"
*Make sure you are using the less-than symbol to import. Using greater-than symbol will export files. Make sure the database is already created*
6. Provide username's password
7. Press enter and wait for a couple of seconds to minutes. It takes longer when file size is huge.
8. Verify the database if the files are imported.
Thank you for watching this video tutorial.
Don't forget to drop comments, hit like, and subscribe to this RUclips Channel :D
Credits to Reev Music for the background music.
• Kygo feat. Conrad Fire...
I know you did this years ago, but still very useful. It worked. Thank you!
I've come back and watched this video probably 5 times since I started my new job. Thanks again
I'm so glad to hear about that! Good luck and cheers!
I always forget this command. So glad for this video still being here. Thanks!
Absolutely great video! Thank you so much Cebuana, you ROCK :)
+Dan DeBiase thank you so much! ^_^
Excuse me , but How can l know my password for mysql when it ask me to? sorry
@cryinranked, if you haven't created any users with passwords yet, there are no passwords for the default user called 'root'. What you will do is add a new user with your desired password first. Here's a tutorial for creating a new user ruclips.net/video/JDuJ5F5SYyY/видео.html
Geeky Cebuana Thanks it's working , you are awesome , i liked and suscribed
+cryinranked that's great! thank you for the support! ^_^
You have a nice voice, and your tutorial was easy to follow thank you.
You saved my bacon. GREAT VIDEO! Thanks so much for taking the time to make it.
glad I could help :) you're welcome!
More power to your tutorials .. this helped me a lot .. ほんとうにありがとうござました！。。「Thank u very much」
Thank you for this video! You helped me a lot!
Thank you! Very clear and concise
Awesome, this video helpful me greatly...thank you!!
Thank you! this video helped me a lot!
I was able to upload my 1.1 GB database to Xampp, thanks for the tutorial.
I'm glad to hear that! ^_^
password where u get that
what attribute of my.ini needs to change if i want to save 64MB of BLOB inside a column?
Its a nice one, I was worried but it git me,, bulls eye, thanks for posting
you're welcome!
Thanks, u so much. It is very Helpful.
thank you thank you for this ma'am its very helpful for programmer beginners love you :)
You're welcome! ^_^
Thank you so much, this helped me a lot! :)
Glad to hear that! You're welcome! :D
Thanks a lot it work I ahve about 215MB of online sql database and I successfully import in my loacl Xampp Server with help of your describe procedure. Thanks
It was really nice!
Thank you :)
Helped me so so so much. thanks a million!
+Zane Christenson Thank you for watching too! ^_^
Thanks done in a simple and understanding method
You deserve a like! Thanks!
Thank you very very much you are seriously a life saver 1000000 likes(y)
This should work, however when I do this, it states that mysql could not be recognise mysql as a task, program or batchfile etc.
\mysql -u root -p ag-ni-ex-2.0 < ag-ni-ex-2.3.0.sql
it says access denied for user after entering the password. pls help
thank you! so useful for me!
I got an error message saying " Access denied for user ''@'localhost' to database '"
Thank You. Work for me.
What happens if your installation of MySQL doesn't have a password? If I leave it blank, it fails.
Great Video. Thank you so much !
Please zoom the command prompt window, difficult to read the commands
Thank! It's very useful.
+วิทวัส วงศ์หาญกุล (Jappy) you're welcome! Good day!
@Geeky Cebuana great tutorial tnx
Thanks, it works!
that was a good guide , thanks
Thanks, you save my day :)
please help me ,I am getting error in and 1045 , access denied for user
thanks alot really helpful
great video! Thank you so much..................................
Very helpful thanks :)
Just Perfect !!!!! Thanks !
hey, what if i forgot the password? what should I do ?
Awesome..Great help
that's good help, thank you :)
Awesome.. it works
How to run this command by php
Thanks a lot Dude
thank you it worked
Wow.... best tutorial ever made. I want to give you many kisses. Thank you very much. Now I can use our old database for our game :)
+Yorisico You're welcome! Glad I could help and thank you too for your feedback! Cheers! :D
Thank you sir
Thank you so much Cebuana
THANK YOU!
Hello, i know this is late but how do u know your username? I'm kinda new to this whole xampp thing.
Your help is much appreciated. Thnks.
Hi there. Thanks for creating this very informative video. Quick question. Within CMD I keep getting errors for my password. I"ve even changed it and still getting errors. ERROR 1045 and ERROR 2003 . Just so strange because I've imported data on the host server using PHPMYADMIN without any issues. But just trying to run Xampp locally is so much problems.
+edgi55 Hi! I'm sorry for the late response. Please try restarting your Xampp, and try adding a new user to your phpMyAdmin then use that new user and password instead. Please let me know or email me at geekycebuana@gmail.com
+Geeky Cebuana ok thanks for your advice will try and keep you posted
you're welcome! Please do comment it below when you solve it. Thanks :-)
Thank you for the help
can someone help as i do not know the password and how to get it
Thank You a lot
now I changed my password over phpmyadmin dashboard and when I enter my password in command line, it shows ERROR 1049 (42000): Unknown database 'sakila'.
Sakila is the name of my database.
HAKAN YORGANCI Hi there! Did you create a database? That error shows up if the database doesn't exist. Please run this in your mysql command line "create database sakila" or create a database using the phpmyadmin. then do everything again in this tutorial video ^_^
best tutorial ❤❤❤❤❤❤
what to do if i don't know my password? i haven't set any
many thanks
thanks man!
Thank you . it worked
what user and password do i need to type? thanks
How long you waited to finish the 15MB import ? Mine is 10MB and its like 5 mins already i guess n still waiting :)
+Najeeb Puthiyallam I think it was about 5 mins. The larger the file is, the longer it will take. And it also depends on your processor. Hope everything is well now :)
Like your Video....very useful
Noaman Murudkar glad that I can help!
how to fix the problem
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
I like your voice...
THANK YOU SO MUCH
Hi,i dont give any password in my database. what i do the password column
If you haven't created any users/admins to your database, the default user name is root and there is no password. I advice you to create a user with admin rights so that you can import. Here's a tutorial on how to create a new user ruclips.net/video/JDuJ5F5SYyY/видео.html
i am getting an error after entering the password. Kindly help
What if the sql dump file I'm trying to import is 16GB? ...I've tried the usual Google trash suggestions and after 3 days the import had only progressed to about 20% or so.
Hi! 16GB doesn't take days. It will just take hours as what I've experienced. Probably it hanged in the middle because it probably got interrupted in between the uploading of file(network problem). One work-a-round that I can suggest for much much bigger files is that put the file in the same folder as where the Big Dump file is. Then send the path to Big Dump.
Hi again, sometimes when l try to import database it says ERROR 1049 (42000): Unknown database 'db'
( the name of my database is db.sql and l followed all the steps ):
Never mind it is fixed, just i didn't had a database named test on phpmyadmin ! thanks for awesome tutorials will make ads to your channel
Nice! You're welcome!
That is great i love it
thank you.
Hello, i need some help: what password should i insert into the step of the cmd?
Hi there! Freshly installed doesn't have passwords so you need to create a user. Here's a tutorial I made on how to create a user ruclips.net/video/JDuJ5F5SYyY/видео.html
Thank you so much
life saver sis 1000 likes
ellehu@yahoo.com No worries! ^_^
good job. thanks
how do i know the passwd for the database
IDRIS AJAD hi there! Freshly installed doesn't have passwords so you need to create a user. Here's a tutorial I made on how to create a user ruclips.net/video/JDuJ5F5SYyY/видео.html
Nice Video and nice voice
Wow nice :D
can it also apply to a 200GB of sql file?
Hi James! Theoretically, you can apply a 200GB sql file and you should place the file inside the folder of where you put bigdump. It is not advisable to upload the file from your local to your remote site because there will be network issues and might cause lags(or it might hang) when you upload the file.
in case thati don't have password what should i do ??
if you don't have any password then keep it blank and enter
"MY SEQUEL" oh god ... Thanks though, it helped me onm MY SEQUEL dump
Sir Hunningham Hi there! The reason why I said it that way is because it is easier for me. I got tongue twisted many times when I said es-que-el when I was recording this ^_^
Thank you.
great one
What is the password you are typing in CMD?
I followed all the instructions properly. No error in the cmd. But when I went back to phpmyadmin database is still empty. my file size(2,705 KB) is also very small. Help me!!! :(
Hi! That's really weird... Have you tried it again? Have you checked the database name if you entered the correct one?
I tried it more than 10 times but still same result.
+Chirag Panchal please take screenshots of every step and send it to geekucebuana@gmail.com and let's troubleshoot from there tomorrow
I tried different solution and it's working.
I changed this in php.ini
post_max_size=500M
upload_max_filesize=500M
memory_limit=900M
Oh yeah! I've noted that at @01:38 that you can set it to whatever value that you want for as long as it is more than the file size that you are trying to import ^_^ That's great that you've solved it *thumbs up* Happy coding!
salamat kaayo
what about live domain other than localhost ??
+Spartan 300 that's a good question! It's going to be a bit of a challenge but I'll see what I can do and provide a tutorial in the future. I'll add it to the list of all the upcoming tutorials I will make. Thanks for the question!
thanks will be waiting for it
+Spartan 300 I've created a tutorial on how to import large SQL file to a live domain. You can check it out here: ruclips.net/video/Riel5UNvJkY/видео.html
helpufull
access denied for user 'user'@'localhost'
+Kimberly Billones Hi! That error showed up because the username or password were incorrect. You can add another user and provide a password, and try it again please :D
+Kimberly Billones And please also make sure that the user has all the privileges to update,insert,alter, etc. the database
+Geeky Cebuana I've already added a new user. username: shin pword: test still gets the same error I've already checked all the privileges.
+Geeky Cebuana at line 80: duplicate entry '1' for key 'PRIMARY' new error. YOY?
+Geeky Cebuana hey! thanks I've already imported the database with the database name test haha! silly me didn't get it right but thanks :** life saver.
Thank you :)
you're welcome Clenisa! ^_^
sorry ,I still not able to do
It's working until now.
Thaaaaannkkkssssssss!!!! (y)
Kevin Dudeja no probs!
Instead click " Apache - Config " Pick " php.ini " in there find " post_max_size " and " upload_max_filesize" set size you need or you want. You are ready to upload!
It is very helpful :) Thanks But background music was so loud
thanks for the feedback! I'll minimize the background music in the future :)
:) Many Thanks :)
+Kamal Kant Maurya you're welcome! Happy coding ^_^
Mam, paturo naman po kung paano mag upload ng sql file sa ubuntu server. Thanks
Hi there! Please email me at geekycebuana@gmail.com. I'll help you from there :)
Hello Mam. I've already resolved the issue. I did not use Phymyadmin to import a database file. I used webmin(web based gui for ubuntu server) as an alternative. Thank you 😊😊
thanks
+NispaaraProjects G you're welcome!
Hi, great tutorials ^ ^
i got little problem when i try to import .sql file in cmd i get this: Error 1064 (42000) You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version... xD
i did like this: mysql -u root -p test < filename.sql; and filename.sql is located in xamp/mysql/bin folder
+Alex Speedy oh i fixed it xD
+Alex Speedy that's great! ^_^