Tag Archives: MySQL

Backup or Export, Compress and upload mysql database to remote host

Posted by aibrahim on January 4, 2011 4 comments

This is a handy command that i use to backup or export, compress and upload a mysql database to my remote host, very handy when you wanna upload your development database to the remote host after some changes.

/usr/mysql/bin/mysqldump --opt -uUsername -pPassword Databasename  | gzip -c | ssh -q user@remote-host.com 'cat > ~/Databasename.sql.gz'

Replace the following :

  • /usr/mysql/bin/mysqldump : The path for mysqldump on your localmachine
  • Username : Username for local mysql database.
  • Password : Password for local mysql database.
  • Databasename : Name of the database to export.
  • user@remote-host.com : the user and the IP or the address of the remote host.

the above command will upload the compressed database to your home directory on the remote host, to change the directoy, replace ~/ with the path you want.

Build MySQL insert Query from Array that contains fields and values

Posted by aibrahim on February 22, 2009 1 comment

The following function takes table name, and array where its keys are the table fields and it’s values are the values to insert in each field, it constructs the MySQL Query based on the input and executes it to insert the data.

function insertRow($table_name, $input_array){
	$link = mysql_connect(SERVER, USER, PASS);
	mysql_select_db(DATABASE, $link);
	$SQL = "INSERT INTO $table_name ";
	$fields = "(";
	$values = "(";
	foreach ($input_array as $k => $v) {
		$fields .= "`$k` ,";
		$values .= "'$v' ,";
	}
	$fields .="#";
	$values .="#";
	$fields = explode(",#", $fields);
	$fields = $fields[0];
	$values = explode(",#", $values);
	$values = $values[0];
	$fields .= ")";
	$values .= ")";
	$SQL .= $fields . " VALUES " . $values;
	mysql_query($SQL);
	mysql_close($link);
}

Fix the MySQL PHP issue in Leopard (mysql.sock file)

Posted by aibrahim on January 7, 2008 No comments

This is a known issue in Leopard, basically the system is looking for the mysql.sock file in the wrong place. Just need to create a symlink and you should be in business:

sudo mkdir /var/mysql/
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock