MySQL Database Import And Export Operations

Take Backup With Data

Open Command Prompt (cmd)
# mysqldump -u dbusername -p db_name > /tmp/db_fullbackup.sql

Take Only Metadata Backup


# mysqldump -u dbusername -p –no-data db_name > /tmp/db_backupmetadata.sql
or
# mysqldump -u dbusername -p -d db_name > /tmp/db_backupmetadata.sql

Restore Database Metadata From Backup


# mysql -u dbusername -p db_name < /tmp/db_backupmetadata.sql

Restore Database From Backup


# mysql -u dbusername -p db_name < /tmp/db_fullbackup.sql

Mysql: Import file size limit in PHPMyAdmin

I have changed all the php.ini parameters I know: upload_max_filesize, post_max_size.

Why am I still seeing 2MB?

 Answers

You probably didn't restart your server ;)

Or you modified the wrong php.ini.

Or you actually managed to do both ^^

---

Just change your php.ini(xampp/php/php.ini) file, it worked for me!

max_execution_time = 5000
max_input_time = 5000
memory_limit = 1000M
post_max_size = 750M
upload_max_filesize = 750M

And, don't forget to restart Apache Module from XAMPP Control Panel.

---

Check your all 3:

• upload_max_filesize
• memory_limit
• post_max_size

in the php.ini configuration file

* for those, who are using wamp @windows, you can follow these steps: *

Also it can be adapted to any phpmyadmin installation.

Find your config.inc.php file for PhpMyAdmin configuration (for wamp it's here: C:\wamp\apps\phpmyadminVERSION\config.inc.php

add this line at the end of the file BEFORE "?>":

$cfg['UploadDir'] = 'C:\wamp\sql';

save

create folder at

C:\wamp\sql 

copy your huge sql file there.

Restart server.

Go to your phpmyadmin import tab and you'll see a list of files uploaded to c:\wamp\sql folder.

---

This is how i did it:

1. Locate in the /etc/php5/apache2/php.ini

   post_max_size = 8M
   upload_max_filesize = 2M
   

2. Edit it as

   post_max_size = 48M
   upload_max_filesize = 42M
   

(Which is more then enough)

Restarted the apache:

sudo /etc/init.d/apache2 restart

---

I found the problem and am post hete if anyone followed some blog post out there to create the sort of enviromment I have (win 7 host with ubuntu and zend server ce on virtual box).

The thing is that MySQL is running on Lighttpd, not under Apache. So I had to change the php.ini file under that webserver as well which is in the path:

/usr/local/zend/gui/lighttpd/etc/php-fcgi.ini

In the end, you were right about the files, of course, but I was wrong on what file I had to change :)

---

I had the same problem. My Solution: go to /etc/phpmyadmin and edit apache.conf in the <Directory>[...]</Directory> section you can add

php_value upload_max_filesize 10M
php_value post_max_size 10M

Solved the problem for me!

---

How to import huge amount of Data in Xampp

It’s the best solution to open new, clean database, and import the file in phpMyAdmin. Choose file from local folder and leave all settings as is. That should be it.

But if your file exceeded file size of 2MB (that is default value in xampp installation) than you need to tweak some out. Basically we will just increase the maximum upload file size.

Open your xampp install folder and go to php folder. With some text editor file (notepad++) open the file called php.ini (by type windows describe it as configuration settings) and edit the line (cca. 770, or press ctrl+f in notepad++ to find it):

post_max_size = 2M

instead 2M, put wanted maximum file size (16MB for example but not higher than 128M),

after that go to this line: max_execution_time = 30 instead 30, increase execution time that’s in seconds (90 for example), also you need to change this line:

max_input_time = 60

instead 60, increase input time in seconds (120 for example) and top of all you have to change this line:

upload_max_filesize = 2M

instead of 2M, increase to 16M for example. Save the file, restart apache and mysql in xampp and try to upload again, if you follow correctly you should be able to import files through 16MB (example)

---

You could just use MySQL administrator app or MySQL workbench. Lightweight apps and you can export or import your entire server however the size. Am late to the party here but I hope it helps someone.

---

I found that increasing the upload and post limit in php.ini did not affect the limit in phpmyadmin. This is because my server has a separate setting for cpanel upload limit. If you have access to WHM, you probably have this.

To adjust:

• login to your WHM panel: this is usually located at {your server ip}/whm and you will need your root login details here. If you don't have those, request them from your host.
• once logged in, in the top left search bar, search for "tweak settings"
• On the tweak setting pages, search for "cPanel PHP max upload size"
• Adjust the number and save

No need to restart apache or anything, changes are instant. This process increased the value of max upload file size in phpmyadmin. You can check this by going to phpmyadmin and selecting your database, then clicking "import" at the top. Beside the file selector you will see the upload limit. My server default was 100.

---

Simply, run this to find out which php.ini you need to edit.

<?php phpinfo();?>

---

I think if your php version is above 5.5, let say it is 5.6, then your php.ini file is in following folder

/etc/php/5.6/apache2

so you have to apply your changes like post_max_size,upload_max_filesize and memory_limit there.

Hope it will help you.

---

The first things to check (or ask your host provider to check) are the values of max_execution_time, upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP.

Please note that post_max_size needs to be larger than upload_max_filesize.

Import Multiple .sql dump files into mysql database from shell

I have a directory with a bunch of .sql files that mysql dumps of each database on my server.

e.g.

database1-2011-01-15.sql
database2-2011-01-15.sql
...

There are quite a lot of them actually.

I need to create a shell script or single line probably that will import each database.

I'm running on a Linux Debian machine.

I thinking there is some way to pipe in the results of a ls into some find command or something..

any help and education is much appreciated.

EDIT

So ultimately I want to automatically import one file at a time into the database.

E.g. if I did it manually on one it would be:

mysql -u root -ppassword < database1-2011-01-15.sql

 Answers

cat *.sql | mysql? Do you need them in any specific order?

If you have too many to handle this way, then try something like:

find . -name '*.sql' | awk '{ print "source",$0 }' | mysql --batch

This also gets around some problems with passing script input through a pipeline though you shouldn't have any problems with pipeline processing under Linux. The nice thing about this approach is that the mysql utility reads in each file instead of having it read from stdin.

---

There is superb little script at https://thiscode4u.blogspot.com/2018/11/how-to-log-in-to-mysql-and-query.html which will take a huge mysqldump file and split it into a single file for each table. Then you can run this very simple script to load the database from those files:

for i in *.sql
do
  echo "file=$i"
  mysql -u admin_privileged_user --password=whatever your_database_here < $i
done

mydumpsplitter even works on .gz files, but it is much, much slower than gunzipping first, then running it on the uncompressed file.

I say huge, but I guess everything is relative. It took about 6-8 minutes to split a 2000-table, 200MB dump file for me.

---

I created a script some time ago to do precisely this, which I called (completely uncreatively) "myload". It loads SQL files into MySQL.

Here it is on GitHub

It's simple and straight-forward; allows you to specify mysql connection parameters, and will decompress gzip'ed sql files on-the-fly. It assumes you have a file per database, and the base of the filename is the desired database name.

So:

myload foo.sql bar.sql.gz

Will create (if not exist) databases called "foo" and "bar", and import the sql file into each.

For the other side of the process, I wrote this script (mydumpall) which creates the corresponding sql (or sql.gz) files for each database (or some subset specified either by name or regex).

How to import CSV file to MySQL table

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column of the CSV file. The CSV contains 99 columns , so this was a hard enough task in itself:

CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);

No constraints are on the table, and all the fields hold VARCHAR(256) values, except the columns which contain counts (represented by INT), yes/no (represented by BIT), prices (represented by DECIMAL), and text blurbs (represented by TEXT).

I tried to load data into the file:

LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023  Deleted: 0  Skipped: 0  Warnings: 198256
SELECT * FROM CSVImport;
| NULL             | NULL        | NULL           | NULL | NULL               | 
...

The whole table is filled with NULL.

I think the problem is that the text blurbs contain more than one line, and MySQL is parsing the file as if each new line would correspond to one databazse row. I can load the file into OpenOffice without a problem.

The clientdata.csv file contains 2593 lines, and 570 records. The first line contains column names. I think it is comma delimited, and text is apparently delimited with doublequote.

UPDATE:

When in doubt, read the manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

I added some information to the LOAD DATA statement that OpenOffice was smart enough to infer, and now it loads the correct number of records:

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

But still there are lots of completely NULL records, and none of the data that got loaded seems to be in the right place.






Answers

The core of your problem seems to be matching the columns in the CSV file to those in the table.

Many graphical mySQL clients have very nice import dialogs for this kind of thing.

My favourite for the job is Windows based HeidiSQL. It gives you a graphical interface to build the LOAD DATA command; you can re-use it programmatically later.

Screenshot: "Import textfile" dialog

To open the Import textfile" dialog, go to Tools > Import CSV file:

---

Simplest way which I have imported 200+ rows is below command in phpmyadmin sql window

I have a simple table of country with two columns CountryId,CountryName

here is .csv data

here is command:

LOAD DATA INFILE 'c:/country.csv' 
INTO TABLE country 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Keep one thing in mind, never appear , in second column, otherwise your import will stop

---

phpMyAdmin can handle CSV import. Here are the steps:

1. Prepare the CSV file to have the fields in the same order as the MySQL table fields.
2. Remove the header row from the CSV (if any), so that only the data is in the file.
3. Go to the phpMyAdmin interface.
4. Select the table in the left menu.
5. Click the import button at the top.
6. Browse to the CSV file.
7. Select the option "CSV using LOAD DATA".
8. Enter "," in the "fields terminated by".
9. Enter the column names in the same order as they are in the database table.
10. Click the go button and you are done.

This is a note that I prepared for my future use, and sharing here if someone else can benefit.

---

The mysql command line is prone to too many problems on import. Here is how you do it:

• use excel to edit the header names to have no spaces
• save as .csv
• use free Navicat Lite Sql Browser to import and auto create a new table (give it a name)
• open the new table insert a primary auto number column for ID
• change the type of the columns as desired.
• done!

---

Try this, it worked for me

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

IGNORE 1 ROWS here ignores the first row which contains the fieldnames. Note that for the filename you must type the absolute path of the file.

---

Change servername,username, password,dbname,path of your file, tablename and the field which is in your database you want to insert

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "bd_dashboard";
    //For create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    $query = "LOAD DATA LOCAL INFILE 
                'C:/Users/lenovo/Desktop/my_data.csv'
                INTO TABLE test_tab
                FIELDS TERMINATED BY ','
                LINES TERMINATED BY '\n'
                IGNORE 1 LINES
                (name,mob)";
    if (!$result = mysqli_query($conn, $query)){
        echo '<script>alert("Oops... Some Error occured.");</script>';
        exit();
            //exit(mysqli_error());
       }else{
        echo '<script>alert("Data Inserted Successfully.");</script>'
       }
    ?>

---

Here is sample excel file screen shot:

Save as and choose .csv.

And you will have as shown below .csv data screen shot if you open using notepad++ or any other notepad.

Make sure you remove header and have column alignment in .csv as in mysql Table. Replace folder_name by your folder name

LOAD DATA LOCAL INFILE
'D:/folder_name/myfilename.csv' INTO TABLE mail FIELDS TERMINATED BY ',' (fname,lname ,email, phone);

If big data, you can take coffee and have it load!.

Thats all you need.

Mysql: phpmyadmin “no data received to import” error, how to fix?

I am using XAMPP on a pc atwork to host a database. I exported a backup ("bintra.sql") using phpmybackuppro. I use xampp on a mac at home, and when I try to import the sql file located on my desktop, I get this error.

No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.

Now, the file size of bintra.sql is 922kb. The max size allowed indicated on the phpmyadmin screen is 3,072KiB, so I don't think it is the size that is preventing the import.

I'm using phpmyadmin v2.11.7

Does anyone have any ideas why no data is being received to import?

Comment Responses:

These are my upload settings from php.ini

;Whether to allow HTTP file uploads.
file_uploads = On
;Temporary directory for HTTP uploaded files (will use system default if not specified). 
//upload_tmp_dir =
;Maximum allowed size for uploaded
files. 

upload_max_filesize = 3M
;Maximum size of POST data that PHP will accept.
post_max_size = 8M

EDIT:

Tried using Mamp instead. Works fine with the same sql file. I don't know why.

 Answers

I had the same problem on Windows. Turns out it was caused by the temporary directory PHP uses for uploads. By default this is C:\Windows\Temp, which is not writable for PHP.

In php.ini, add:

upload_tmp_dir = C:\inetpub\temp

Make sure to remove any other upload_tmp_dir settings. Set permissions on C:\inetpub\temp so IUSR and IIS_IUSRS have write permission. Restart the web server and you should be fine.

---

Check permissions for you upload directory. You can find its path inside /etc/phpmyadmin/apache.conf file.

In my case (Ubuntu 14.04) it was:

php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp

So I checked permissions for /var/lib/phpmyadmin/tmp and it turns out that the directory wasn't writable for my Apache user (which is by default www-data). It could be the case especially if you changed your apache user like I do.

---

I never succeeded importing dumb files using phpmyadmin or phpMyBackupPro better is to go to console or command line ( whatever it's called in mac ) and do the following:

mysql -u username -p databasename

replace username with the username you use to connect to mysql, then it will ask you to enter the password for that username, and that's it

you can import any size of dumb using this method

---

No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.

These are my upload settings from php.ini

upload_tmp_dir = "D:\xampp\xampp\tmp"       ;//set these for temp file storing

; Maximum allowed size for uploaded files.
; http://php.net/upload-max-filesize
upload_max_filesize = 10M    ;//change it according to max file upload size

I am sure your problem will be short out using this instructions.

 upload_tmp_dir = "D:\xampp\xampp\tmp"

Here you can set any directory that can hold temp file, I have installed in D: drive xampp so I set it "D:\xampp\xampp\tmp".