Welcome, Guest. [ Log In ]
Question   How do I dump data into MySQL?
Search KBase


Top 5 in this Area:
1. How do I dump data into MySQL?
2. How do I add a new MySQL database?
3. How do I designate a host name for my MySQL database?
4. How do I add users to my database?
5. How do I manage my mysql database?

 
How do I dump data into MySQL?
There are two ways! Both are done via the unix shell prompt, with one of these two commands:
  1. mysqlimport -L -hHOSTNAME -uUSER -pPASSWORD DATABASE DUMPED_TEXTFILE
  2. mysql -hHOSTNAME -uUSER -pPASSWORD DATABASE < DUMPED_TEXTFILE
Of course make sure to replace all the relevant info above with your correct info!

As for getting the DUMPED_TEXTFILE in the first place, you can get that from our php_myadmin installation, or using the mysql dump command (note, we don't offer direct access to the MySQL datafiles in mysql/data because they reside on dedicated MySQL servers).

Last updated: Jan 28, 2005.

User Post (2004-05-24 09:39:09 by jayeshsheth)
I was originally going to post this note on my weblog, but I felt it would be accessible to more people if I posted it directly to Dreamhost's knowledge base:

If you are using PHPMyAdmin to load a large csv file into a table, and get a weird error from MySQL :

- something like: "File '.\your_database_name\' not found (Errcode: 2)" -

it could be that the maximum upload size specified in your php.ini file is lower than the size of the csv file you are uploading. In such a case, PHP cuts off access to this larger than expected file, leaving MySQL to produce a weird error.

Solution:
If you are on a dedicated server:
increase the size of the 'upload_max_filesize' directive to '20M' or something sufficient large to accomodate the size of the csv file which you are uploading through PHPMyAdmin.

If you are on a shared server (like Dreamhost most customers), you might have to upload the large csv file via FTP to one of your user accounts, and then SSH in and use the command line mysql utility to load the file into the database with a query that looks something like the following:

LOAD DATA INFILE '/path/to/something.txt' INTO TABLE `yourtable` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

More info on LOAD DATA syntax:
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

To use the command line mysql tool, you will have to log in first. Sample steps to get there (once you have uploaded the file and SSHed in):

1) Connect to mysql by typing the following and then enter
mysql -h mysql.yourdomain.com -u yourusername -p

2) You will be prompted for your password. Enter your password.

3) Type the following to select your database:
USE my_database

4) Enter your load query, followed by a semi-colon. So enter something like:
LOAD DATA INFILE '/path/to/something.txt' INTO TABLE `yourtable` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

Replace the path and table names as appropriate.


The LOAD DATA syntax can be replaced with use of the mysqlimport tool:
More information on using the mysqlimport tool:
http://dev.mysql.com/doc/mysql/en/mysqlimport.html

Glossary of terms:

CSV file: A comma separated file, in this case outputted by PHPMyAdmin
MySQL - An open source database. See http://www.mysql.com

PHP - Web Server Scripting language. See http://www.php.net

PHPMyAdmin - A Web-based Administration Tool for MySQL Databases. (It is written in PHP, hence the PHP part in its name). See http://www.phpmyadmin.net/home_page/

php.ini - the main configuration file for the PHP scripting language
Shared Server: If you are hosting your site with a hosting company, then your site is most probably on a computer that is shared with other people. Due to security restrictions of a multi-user environment, you are unable to modify the main php.ini file.

SSH : a way to access a remote computer securely using a text-based interface . For Windows, Putty is a recommended SSH client:
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
User Post (2006-01-16 05:59:11 by bmcclue)
Hi, Does anyone know if your can use the Navicat MySQL tool for connecting to a database on the dreamhosts site?
User Post (2005-09-20 19:32:12 by pharmit)
I have just found a far easier way than any above, if you have a phpMyadmin dumped textfile.
After creating your database, just go to your phpMyAdmin page. Go to SQL tab, then select the CHOOSE FILE option. Select the dumped textfile that is stored on your local computer. It will run as a query and import everything that is necessary. At least it worked for me.

User Post (2005-08-12 20:24:29 by homewit)
My favorite solution : http://www.phpbb.com/kb/article.php?article_id=266 - it's amazingly easy.
User Post (2005-06-06 16:31:28 by draggon)
A better option is to use SQLYog available for a free download from downloads.com. It is a GUI client that can access your MySQL database - to do essentially anything you would like.
User Post (2005-01-19 01:02:25 by marczwan)
I couldn't get RPFsplitter to work on my PHPBB sql dumpfile. This script finally helped me get the 50mb+ file into phpmyadmin:

http://www.ozerov.de/bigdump.php
User Post (2005-01-05 02:22:41 by mjrpes)
If you have Windows and Access, you can dump data to MySQL through ODBC.

Set up ODBC on your windows machine to link to your db using the MySQL ODBC driver: http://www.mysql.com/products/connector/odbc/ Once installed, you set up the ODBC driver using the same server, username, & password information you got when you created your MySQL database. By default only dreamhost servers can connect to your database... bad! Change this by modifying the user privilage for your database in the control panel; by default is says '%.dreamhost.com', change this to just a percent sign: '%'.

Then use the export function in Access to dump your tables to MySQL. The only thing you need to do is manually add in indexes and primary keys with phpMyAdmin.

Saved me a bunch of time!
User Post (2005-01-01 01:33:18 by rsim)
While not terribly important on DreamHost since you cannot see other users processes with "ps", but you may want to only specify -p on the command line and leave the password BLANK - mysql will then prompt you for your password and as such it will not be visible in the process list (nor show up in your .bash_history file).

So it becomes:
mysql -hHOSTNAME -uUSERNAME -p DATABASE < DUMPED_TEXTFILE

I should also mention that "mysqlimport" did not recognize my password for some reason (it did prompt me however), so I had to use "mysql" instead. Possibly a user error.. :)
User Post (2004-07-30 14:00:12 by timmus)
Just a helpful comment for phpBB (and other users) trying to use SQL Upload in phpMyAdmin to "restore" a database. There is a max limit on the size of the file uploaded, in terms of number of lines and processing time. Dreamhost processes SQL commands at about 200-300/second, and you are ONLY allotted 300 seconds before phpMyAdmin times out (and fails). Thus you will have to split up your SQL dump, ideally to 25,000 lines or less (a large phpBB installation may contain around one million lines). Particularly huge are the phpbb_posts and phpbb_search* tables, and they may need to be brought in individually. There is also a utility called rfsplitter that can help. For more info on this issue see: http://www.phpbb.com/kb/article.php?article_id=59
User Post (2003-03-06 15:32:08 by peterjtracey)
With the web-based PHPMyAdmin, which dreamhost makes available @ your mysql hostname (dreamhost rocks!), you can do a SQL dump of a database structure and/or data and then copy it and run it on the corresponding database you'd like to import the data/schema into (with the PHPMyAdmin run query feature). Quick and easy :)

<br>
FYI Version 4 of MySQL was released alongside a new Control Center application, that's an enterprise manager-like (for those of you familiar with MS SQL Server) GUI to modify a MySQL database. IMHO much better than PHPMyAdmin, but unfortunately doesn't have the sql dump feature of PHPMyAdmin or any other import/export utilities.
<br><br>
Of course you'll need to add an entry with your local address to the user list since it runs locally. Information is at
<a href="http://www.mysql.com/products/mysqlcc/index.html">http://www.mysql.com/pro ducts/mysqlcc/index.html</a>
<br><br>
Peter Tracey<br>
http://www.levelthreesolutions.com/
User Post (2002-09-27 21:42:05 by books247)
Here's a tip on connecting to mysql from Perl - running queries on Mysql from perl, and using DBI.pm


This knowledgebase needs more specific code snippets so we can figure out how to get started faster without doing a bunch of research to get up and running with perl and mysql. With that in mind, I'm posting some cut-and-paste code to help you get moving!
<br>
I finally figured out how to connect to mysql and run commands from inside a per script using DBI. Here's a sample of how you can do this:
\n\n
#use the DBI module
use DBI;
\n\n
$user = "mysql username";
$password = "mysql password";
$database = "database name";
$host_name = "mysql hostname something.domain.something";
\n\n
#Open connection and establish object
$dbh = DBI->connect("DBI:mysql:$database:$host_name", $user, $password);

\n\n
# Store SQL to be run in the $SQL var
$SQL = "SELECT * from contact_list";
\n\n
#Prepare and execute your SQL
$cursor = $dbh->prepare($SQL);
$cursor->execute;
\n\n
#handle any SQL errors
if ($DBI::errstr) {
print "$DBI::errstr $SQL";
exit;
}
\n\n
#Do stuff with your data
while (($first_name, $last_name)=$cursor->fetchrow) {
print "$first_name, $last_name<br>";
}
#Close the cursor
$cursor->finish;
\n\n
#disconnect;
$dbh->disconnect;
\n\n
What you can do to keep your code neat is to store this info in a subroutine called &db_connect, and put it in a .pl or .pm file that you require at the top of every script to make it nice and clean for you.
\n\n
Want more info? Check out the MYSQL Documentation at http://www.mysql.com/doc/en/Perl_DBI_Class.html and DBI documentation at http://www.rrz.uni-hamburg.de/RRZ/Software/DBI.pm/DBI.pm.html