{"id":359,"date":"2009-02-10T01:19:52","date_gmt":"2009-02-10T07:19:52","guid":{"rendered":"http:\/\/bililite.nfshost.com\/blog\/?p=359"},"modified":"2012-02-01T14:57:55","modified_gmt":"2012-02-01T20:57:55","slug":"the-agony-of-unicode-and-backing-up-mysql","status":"publish","type":"post","link":"https:\/\/bililite.com\/blog\/2009\/02\/10\/the-agony-of-unicode-and-backing-up-mysql\/","title":{"rendered":"The Agony of Unicode (and backing up mySQL)"},"content":{"rendered":"

All I wanted to do was back up the Young Israel databases, some way more amenable to automation than phpMyAdmin<\/a>. There are lots of PHP-based solutions<\/a> on the web, but all seem based on mysqldump<\/a>. I implemented one<\/a> and found myself faced with an eyeful of \u00d7\u0090\u00d7\u00a8\u00d7\u2122\u00d7\u0090\u00d7\u0153 \u00d7\u017e\u00d7\u0090\u00d7\u2122\u00d7\u00a8 \u00d7\u2122\u00d7\u00a2\u00d7\u00a7\u00d7\u2018 \u00d7\u2018\u00d7\u0178 \u00d7\u201c\u00d7\u2022\u00d7\u201c \u00d7\u0090\u00d7\u2018\u00d7\u00a8\u00d7\u201d\u00d7\u009d where the Hebrew names should be. Turns out this a known bug; mysqldump can't handle Unicode<\/a>. There are reports of workarounds<\/a>, but I spent 8 hours not getting anything to work.

\r\n

So I had to write my own backup, going through each database on a server then each table in the database, writing the appropriate INSERT INTO<\/code> commands (thank goodness the SHOW CREATE DATABASE<\/code> and SHOW CREATE TABLE<\/code> commands work correctly). It wasn't terribly miserable (not nearly as miserable it was trying to use someone else's tool that doesn't work), and now I get my  \u05d0\u05e8\u05d9\u05d0\u05dc \u05de\u05d0\u05d9\u05e8 \u05d9\u05e2\u05e7\u05d1 \u05d1\u05df \u05d3\u05d5\u05d3 \u05d0\u05d1\u05e8\u05d4\u05dd just fine, and the backup works. I import the generated file into my local copy of mySQL and it regenerates the databases.<\/p>\r\n\r\n

\r\n\/\/ if this file is called directly from the web, run the backup on the requested databases\r\nif (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME'])) db_backup($_REQUEST['database']);\r\n\r\nfunction db_backup($databases){\r\n\tif (!is_array($databases)) $databases = array($databases);\r\n\t$dir = '\/backup'; \/\/ change this as desired\r\n\tif( !is_dir( $dir ) && !mkdir( $dir )) die('<h1>Could not create backup directory<\/h1>'); \r\n\tforeach ($databases as $database){\r\n\t\tlist($host, $name, $user, $password) = explode('\/', $database);\r\n\t\t$now = date('Y-m-d'); \/\/ change this as desired\r\n\t\t@mysql_connect($host, $user, $password) or die (\"<h1>Could not connect to $host<\/h1>\");\r\n\t\tfile_put_contents(\"$dir\/$name-$now.sql\", dump_dbs());\r\n\t}\r\n}\r\n\r\n\/\/ based on http:\/\/davidwalsh.name\/backup-mysql-database-php\r\nfunction dump_dbs(){\r\n\t$ret = '';\r\n\t$dbs = mysql_list_dbs();\r\n\twhile ($db_row = mysql_fetch_object($dbs)) {\r\n\t\t$db = $db_row->Database;\r\n\t\tif ($db == 'information_schema') continue;\r\n\t\tmysql_select_db ($db);\r\n\t\tmysql_query(\"SET NAMES 'utf8'\");  \/\/ magic command to make mySQL output UTF8 to PHP\r\n\t\t$ret = \"DROP DATABASE IF EXISTS `$db`;\\n\";\r\n\t\t$createdb = mysql_fetch_row(mysql_query(\"SHOW CREATE DATABASE `$db`\"));\r\n\t\t$ret .= $createdb[1].\";\\nUSE `$db`;\\n\";\r\n\t\t$tables = mysql_query('SHOW TABLES');\r\n\t\twhile($table = mysql_fetch_row($tables)){\r\n\t\t\t$ret .= dump_table($table[0]);\r\n\t\t}\r\n\t} \/\/ while\r\n\treturn $ret;\r\n}\r\n\r\nfunction dump_table($table){\r\n\t$ret = \"\\nDROP TABLE IF EXISTS `$table`;\\n\";\r\n\t$createtable = mysql_fetch_row(mysql_query(\"SHOW CREATE TABLE `$table`\"));\r\n\t$ret .= $createtable[1].\";\\n\";\r\n\t$dbresult = mysql_query (\"SELECT * FROM `$table`\");\r\n\twhile($row = mysql_fetch_assoc($dbresult)) {\r\n\t\t$keys = fixkeys(array_keys($row));\r\n\t\t$values = fixvalues(array_values($row));\r\n\t\t$ret .= \"\\tINSERT INTO `$table` (\".join(',', $keys).') VALUES ('.join(',',$values).\");\\n\";\r\n\t}\r\n\treturn $ret;\r\n}\r\nfunction fixkeys ($arr){\r\n\tforeach ($arr as &$i){\r\n\t\t$i = \"`$i`\";\r\n\t}\r\n\treturn $arr;\r\n}\r\nfunction fixvalues($arr){\r\n\tforeach ($arr as &$i){\r\n\t\t$i = is_null($i) ? 'NULL' : \"'\".preg_replace(\"\/\\r\\n|\\n|\\r\/\",'\\n',addslashes($i)).\"'\";\r\n\t}\r\n\treturn $arr;\r\n}\r\n<\/code><\/pre>\r\n
The main function db_backup takes a string or an array of strings that describe the databases to be backed up, of the form 'host\/name\/user\/password'<\/code>. For the databases that my host<\/a> provides it would look like 'db12345.perfora.net\/descriptivename\/dbo45678\/password'<\/code>, where descriptivename<\/code> is whatever you want prepended to the backup file, which is named 'descriptivename-2009-02-09.sql' or whatever the date is.<\/p>\r\n
If it is called directly as a web page, the database strings are taken from the query string with key \"database\". If you want to pass more than one database, don't forget to use database[]=<\/code> to make it into a PHP array. Thus,<\/p> 
http:\/\/example.com\/db_backup.php?database=db12345.perfora.net\/serverdata\/dbo45678\/password<\/code><\/pre>\r\n
or<\/p>\r\n
http:\/\/example.com\/db_backup.php?database[]=db12345.perfora.net\/serverdata\/dbo45678\/password&database[]=localhost\/localdata<\/code><\/pre>\r\n
I hope this saves someone a whole lot of agony!<\/p>","protected":false},"excerpt":{"rendered":"All I wanted to do was back up the Young Israel databases, some way more amenable to automation than phpMyAdmin. There are lots of PHP-based solutions on the web, but all seem based on mysqldump. I implemented one and found myself faced with an eyeful of \u00d7\u0090\u00d7\u00a8\u00d7\u2122\u00d7\u0090\u00d7\u0153 \u00d7\u017e\u00d7\u0090\u00d7\u2122\u00d7\u00a8 \u00d7\u2122\u00d7\u00a2\u00d7\u00a7\u00d7\u2018 \u00d7\u2018\u00d7\u0178 \u00d7\u201c\u00d7\u2022\u00d7\u201c \u00d7\u0090\u00d7\u2018\u00d7\u00a8\u00d7\u201d\u00d7\u009d where the Hebrew […]","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,20],"tags":[],"_links":{"self":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts\/359"}],"collection":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/comments?post=359"}],"version-history":[{"count":20,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts\/359\/revisions"}],"predecessor-version":[{"id":371,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/posts\/359\/revisions\/371"}],"wp:attachment":[{"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/media?parent=359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/categories?post=359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bililite.com\/blog\/wp-json\/wp\/v2\/tags?post=359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}