codestacker

   1  Export
   2  mysqldump -u username -p -h mysqlhostname databasename > databasedump.sql
   3  
   4  Import
   5  mysql -u username -p -h mysqlhostname databasename < databasedump.sql

   1  sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist
   2  sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plis

   1  <?php
   2  
   3  function backup_tables($host,$user,$pass,$name,$tables = '*'){
   4  	
   5  	$link = mysql_connect($host,$user,$pass);
   6  	mysql_select_db($name,$link);
   7  	
   8  	//get all of the tables
   9  	if($tables == '*')
  10  	{
  11  		$tables = array();
  12  		$result = mysql_query('SHOW TABLES');
  13  		while($row = mysql_fetch_row($result))
  14  		{
  15  			$tables[] = $row[0];
  16  		}
  17  	}
  18  	else
  19  	{
  20  		$tables = is_array($tables) ? $tables : explode(',',$tables);
  21  	}
  22  	
  23  	//cycle through
  24  	foreach($tables as $table)
  25  	{
  26  		$result = mysql_query('SELECT * FROM '.$table);
  27  		$num_fields = mysql_num_fields($result);
  28  		for ($i = 0; $i < $num_fields; $i++) 
  29  		{
  30  			$return.= 'DROP TABLE '.$table.';';
  31  			
  32  			$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
  33  			$return.= "\n\n".$row2[1].";\n\n";
  34  			
  35  			while($row = mysql_fetch_row($result))
  36  			{
  37  				$return.= 'INSERT INTO '.$table.' VALUES(';
  38  				for($j=0; $j<$num_fields; $j++) 
  39  				{
  40  					$row[$j] = addslashes($row[$j]);
  41  					$row[$j] = ereg_replace("\n","\\n",$row[$j]);
  42  					if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
  43  					if ($j<($num_fields-1)) { $return.= ','; }
  44  				}
  45  				$return.= ");\n";
  46  			}
  47  		}
  48  		$return.="\n\n\n";
  49  	}
  50  	
  51  	//save file
  52  	$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
  53  	fwrite($handle,$return);
  54  	fclose($handle);
  55  }
  56  
  57  backup_tables('localhost','username','password','blog');
  58  
  59  ?>

   1  mysql -u username -p -h localhost data-base-name < data.sql

   1  echo DROP TABLE `echo 'SHOW TABLES;' | mysql -u USER -p DATABASE | tail -n +2 | sed -e 's/$/,/'` | sed -e 's/,$/;/'

   1  mysqldump --al-databases -u -p > backup.sql

   1  ALTER TABLE tablename AUTO_INCREMENT = 1