MySQL: backup DB, import CSV
Blogs20112011-03-20
Backup MySQL Database
Here are 2 ways to backup MySQL DB.
- 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.
- 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.sqlMySQL 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:
<php
// define HOST, USER, PASS to connect MySQL.
mysql_connect(HOST, USER, PASS) or die(mysql_error());
mysql_select_db(DB_NAME);
$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();