William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

MySQL: backup DB, import CSV

Backup MySQL Database

Here are 2 ways to backup MySQL DB.

  1. Using PHPMyAdmin web interface
    PHPMyAdmin is a good choice to backup Database, by using ‘Export’ tab. The issue is that for big Database, it eats huge memory, and normally leads to fail.
  2. Use MySQL’s mysqldump:
    mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

The following is my way for backup/restore to clean up Database:

-- Backup
$ mysqldump -u william -p -h localhost test_db > whole_db.sql

-- Restore Database:
$ mysql --user=william --password=...  test_db < whole_db.sql

MySQL and CSV files

If import CSV files to MySQL, follow the steps:

-- to remove all '\r' from the file to descrime the potential problem.
$ dos2unix csvfile.csv

$ mysql -u william -p -h localhost
mysql> use test_db
mysql> > load data infile '/home/william/mysql/dependant.csv' 
into table t_users1 
fields terminated by ',' 
enclosed by '"' 
escaped by '\\'
lines terminated by '\n';

PHP: get MySQL table structures

The following PHP codes is to get Database table structures:

// define HOST, USER, PASS to connect MySQL.
mysql_connect(HOST, USER, PASS) or die(mysql_error());
$ary =  array(
"select column_name from information_schema.columns where table_name='test1'",
"select column_name from information_schema.columns where table_name='test2'",
"select column_name from information_schema.columns where table_name='test3'");
foreach($ary as $f) {
 $tt = '';
 $res = mysql_query($f);
 while ($row = mysql_fetch_assoc($res)) {
        $tt .= $row['column_name']. ',';
 $tt = substr($tt, 0, strlen($tt)-1);
 echo $tt;
 echo "
\n"; } mysql_close();

One response to “MySQL: backup DB, import CSV

  1. John admin 03/21/2011 at 11:48 pm

    You can also use a program to make MySQL backup like Handy Backup (http://www.handybackup.net)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: