destinations.db.mysql.inc

  1. nittany7 modules/contrib/backup_migrate/includes/destinations.db.mysql.inc
  2. mooc7 modules/contrib/backup_migrate/includes/destinations.db.mysql.inc

Functions to handle the direct to database destination.

Classes

File

modules/contrib/backup_migrate/includes/destinations.db.mysql.inc
View source
  1. <?php
  2. backup_migrate_include('destinations.db');
  3. /**
  4. * @file
  5. * Functions to handle the direct to database destination.
  6. */
  7. /**
  8. * A destination type for saving to a database server.
  9. *
  10. * @ingroup backup_migrate_destinations
  11. */
  12. class backup_migrate_destination_db_mysql extends backup_migrate_destination_db {
  13. function type_name() {
  14. return t("MySQL Database");
  15. }
  16. /**
  17. * Return a list of backup filetypes.
  18. */
  19. function file_types() {
  20. return array(
  21. "sql" => array(
  22. "extension" => "sql",
  23. "filemime" => "text/x-sql",
  24. "backup" => TRUE,
  25. "restore" => TRUE,
  26. ),
  27. "mysql" => array(
  28. "extension" => "mysql",
  29. "filemime" => "text/x-sql",
  30. "backup" => TRUE,
  31. "restore" => TRUE,
  32. ),
  33. );
  34. }
  35. /**
  36. * Declare any mysql databases defined in the settings.php file as a possible destination.
  37. */
  38. function destinations() {
  39. $out = array();
  40. global $databases;
  41. foreach ((array)$databases as $db_key => $target) {
  42. foreach ((array)$target as $tgt_key => $info) {
  43. // Only mysql/mysqli supported by this destination.
  44. $key = $db_key . ':' . $tgt_key;
  45. if ($info['driver'] === 'mysql') {
  46. $url = $info['driver'] . '://' . $info['username'] . ':' . $info['password'] . '@' . $info['host'] . (isset($info['port']) ? ':' . $info['port'] : '') . '/' . $info['database'];
  47. if ($destination = backup_migrate_create_destination('mysql', array('url' => $url))) {
  48. // Make sure the connection is valid.
  49. if ($destination->_get_db_connection()) {
  50. // Treat the default database differently because it is probably the only one available.
  51. if ($key == 'default:default') {
  52. $destination->set_id('db');
  53. $destination->set_name(t('Default Database'));
  54. // Dissalow backing up to the default database because that's confusing and potentially dangerous.
  55. $destination->remove_op('scheduled backup');
  56. $destination->remove_op('manual backup');
  57. }
  58. else {
  59. $destination->set_id('db:'. $key);
  60. $destination->set_name($key .": ". $destination->get_display_location());
  61. }
  62. $out[$destination->get_id()] = $destination;
  63. }
  64. }
  65. }
  66. }
  67. }
  68. return $out;
  69. }
  70. /**
  71. * Get the file type for to backup this destination to.
  72. */
  73. function get_file_type_id() {
  74. return 'mysql';
  75. }
  76. /**
  77. * Get the form for the backup settings for this destination.
  78. */
  79. function backup_settings_form($settings) {
  80. $form = parent::backup_settings_form($settings);
  81. $form['use_mysqldump'] = array(
  82. "#type" => "checkbox",
  83. "#title" => t("Use mysqldump command"),
  84. "#default_value" => !empty($settings['use_mysqldump']),
  85. "#description" => t("Use the mysqldump command line tool if available. This can be faster for large databases but will not work on all servers. EXPERIMENTAL"),
  86. );
  87. return $form;
  88. }
  89. /**
  90. * Backup the databases to a file.
  91. *
  92. * Returns a list of sql commands, one command per line.
  93. * That makes it easier to import without loading the whole file into memory.
  94. * The files are a little harder to read, but human-readability is not a priority
  95. */
  96. function _backup_db_to_file($file, $settings) {
  97. if (!empty($settings->filters['use_mysqldump']) && $this->_backup_db_to_file_mysqldump($file, $settings)) {
  98. return TRUE;
  99. }
  100. $lines = 0;
  101. $exclude = !empty($settings->filters['exclude_tables']) ? $settings->filters['exclude_tables'] : array();
  102. $nodata = !empty($settings->filters['nodata_tables']) ? $settings->filters['nodata_tables'] : array();
  103. if ($file->open(TRUE)) {
  104. $file->write($this->_get_sql_file_header());
  105. $alltables = $this->_get_tables();
  106. foreach ($alltables as $table) {
  107. if (_backup_migrate_check_timeout()) {
  108. return FALSE;
  109. }
  110. if ($table['name'] && !isset($exclude[$table['name']])) {
  111. $file->write($this->_get_table_structure_sql($table));
  112. $lines++;
  113. if (!in_array($table['name'], $nodata)) {
  114. $lines += $this->_dump_table_data_sql_to_file($file, $table);
  115. }
  116. }
  117. }
  118. $file->write($this->_get_sql_file_footer());
  119. $file->close();
  120. return $lines;
  121. }
  122. else {
  123. return FALSE;
  124. }
  125. }
  126. /**
  127. * Backup the databases to a file using the mysqldump command.
  128. */
  129. function _backup_db_to_file_mysqldump($file, $settings) {
  130. $success = FALSE;
  131. $nodata_tables = array();
  132. $alltables = $this->_get_tables();
  133. $command = 'mysqldump --result-file=%file --opt -Q --host=%host --port=%port --user=%user --password=%pass %db';
  134. $args = array(
  135. '%file' => $file->filepath(),
  136. '%host' => $this->dest_url['host'],
  137. '%port' => !empty($this->dest_url['port']) ? $this->dest_url['port'] : '3306',
  138. '%user' => $this->dest_url['user'],
  139. '%pass' => $this->dest_url['pass'],
  140. '%db' => $this->dest_url['path'],
  141. );
  142. // Ignore the excluded and no-data tables.
  143. if (!empty($settings->filters['exclude_tables'])) {
  144. $db = $this->dest_url['path'];
  145. foreach ((array)$settings->filters['exclude_tables'] as $table) {
  146. if (isset($alltables[$table])) {
  147. $command .= ' --ignore-table='. $db .'.'. $table;
  148. }
  149. }
  150. foreach ((array)$settings->filters['nodata_tables'] as $table) {
  151. if (isset($alltables[$table])) {
  152. $nodata_tables[] = $table;
  153. $command .= ' --ignore-table='. $db .'.'. $table;
  154. }
  155. }
  156. }
  157. $success = backup_migrate_exec($command, $args);
  158. // Get the nodata tables.
  159. if ($success && !empty($nodata_tables)) {
  160. $tables = implode(' ', array_unique($nodata_tables));
  161. $command = "mysqldump --no-data --opt -Q --host=%host --port=%port --user=%user --password=%pass %db $tables >> %file";
  162. $success = backup_migrate_exec($command, $args);
  163. }
  164. return $success;
  165. }
  166. /**
  167. * Backup the databases to a file.
  168. */
  169. function _restore_db_from_file($file, $settings) {
  170. $num = 0;
  171. if ($file->open() && $conn = $this->_get_db_connection()) {
  172. // Read one line at a time and run the query.
  173. while ($line = $this->_read_sql_command_from_file($file)) {
  174. if (_backup_migrate_check_timeout()) {
  175. return FALSE;
  176. }
  177. if ($line) {
  178. // Prepeare and exexute the statement instead of the api function to avoid substitution of '{' etc.
  179. $stmt = $conn->prepare($line);
  180. $stmt->execute();
  181. $num++;
  182. }
  183. }
  184. // Close the file with fclose/gzclose.
  185. $file->close();
  186. }
  187. else {
  188. drupal_set_message(t("Unable to open file %file to restore database", array("%file" => $file->filepath())), 'error');
  189. $num = FALSE;
  190. }
  191. return $num;
  192. }
  193. /**
  194. * Read a multiline sql command from a file.
  195. *
  196. * Supports the formatting created by mysqldump, but won't handle multiline comments.
  197. */
  198. function _read_sql_command_from_file($file) {
  199. $out = '';
  200. while ($line = $file->read()) {
  201. $first2 = substr($line, 0, 2);
  202. $first3 = substr($line, 0, 2);
  203. // Ignore single line comments. This function doesn't support multiline comments or inline comments.
  204. if ($first2 != '--' && ($first2 != '/*' || $first3 == '/*!')) {
  205. $out .= ' ' . trim($line);
  206. // If a line ends in ; or */ it is a sql command.
  207. if (substr($out, strlen($out) - 1, 1) == ';') {
  208. return trim($out);
  209. }
  210. }
  211. }
  212. return trim($out);
  213. }
  214. /**
  215. * Get a list of tables in the database.
  216. */
  217. function _get_table_names() {
  218. $out = array();
  219. foreach ($this->_get_tables() as $table) {
  220. $out[$table['name']] = $table['name'];
  221. }
  222. return $out;
  223. }
  224. /**
  225. * Lock the list of given tables in the database.
  226. */
  227. function _lock_tables($tables) {
  228. if ($tables) {
  229. $tables_escaped = array();
  230. foreach ($tables as $table) {
  231. $tables_escaped[] = '`'. db_escape_table($table) .'` WRITE';
  232. }
  233. $this->query('LOCK TABLES '. implode(', ', $tables_escaped));
  234. }
  235. }
  236. /**
  237. * Unlock all tables in the database.
  238. */
  239. function _unlock_tables($settings) {
  240. $this->query('UNLOCK TABLES');
  241. }
  242. /**
  243. * Get a list of tables in the db.
  244. */
  245. function _get_tables() {
  246. $out = array();
  247. // get auto_increment values and names of all tables
  248. $tables = $this->query("show table status", array(), array('fetch' => PDO::FETCH_ASSOC));
  249. foreach ($tables as $table) {
  250. // Lowercase the keys because between Drupal 7.12 and 7.13/14 the default query behavior was changed.
  251. // See: http://drupal.org/node/1171866
  252. $table = array_change_key_case($table);
  253. $out[$table['name']] = $table;
  254. }
  255. return $out;
  256. }
  257. /**
  258. * Get the sql for the structure of the given table.
  259. */
  260. function _get_table_structure_sql($table) {
  261. $out = "";
  262. $result = $this->query("SHOW CREATE TABLE `". $table['name'] ."`", array(), array('fetch' => PDO::FETCH_ASSOC));
  263. foreach ($result as $create) {
  264. // Lowercase the keys because between Drupal 7.12 and 7.13/14 the default query behavior was changed.
  265. // See: http://drupal.org/node/1171866
  266. $create = array_change_key_case($create);
  267. $out .= "DROP TABLE IF EXISTS `". $table['name'] ."`;\n";
  268. // Remove newlines and convert " to ` because PDO seems to convert those for some reason.
  269. $out .= strtr($create['create table'], array("\n" => ' ', '"' => '`'));
  270. if ($table['auto_increment']) {
  271. $out .= " AUTO_INCREMENT=". $table['auto_increment'];
  272. }
  273. $out .= ";\n";
  274. }
  275. return $out;
  276. }
  277. /**
  278. * Get the sql to insert the data for a given table
  279. */
  280. function _dump_table_data_sql_to_file($file, $table) {
  281. $rows_per_line = variable_get('backup_migrate_data_rows_per_line', 30);
  282. $bytes_per_line = variable_get('backup_migrate_data_bytes_per_line', 2000);
  283. $lines = 0;
  284. $data = $this->query("SELECT * FROM `". $table['name'] ."`", array(), array('fetch' => PDO::FETCH_ASSOC));
  285. $rows = $bytes = 0;
  286. // Escape backslashes, PHP code, special chars
  287. $search = array('\\', "'", "\x00", "\x0a", "\x0d", "\x1a");
  288. $replace = array('\\\\', "''", '\0', '\n', '\r', '\Z');
  289. $line = array();
  290. foreach ($data as $row) {
  291. // DB Escape the values.
  292. $items = array();
  293. foreach ($row as $key => $value) {
  294. $items[] = is_null($value) ? "null" : "'". str_replace($search, $replace, $value) ."'";
  295. }
  296. // If there is a row to be added.
  297. if ($items) {
  298. // Start a new line if we need to.
  299. if ($rows == 0) {
  300. $file->write("INSERT INTO `". $table['name'] ."` VALUES ");
  301. $bytes = $rows = 0;
  302. }
  303. // Otherwise add a comma to end the previous entry.
  304. else {
  305. $file->write(",");
  306. }
  307. // Write the data itself.
  308. $sql = implode(',', $items);
  309. $file->write('('. $sql .')');
  310. $bytes += strlen($sql);
  311. $rows++;
  312. // Finish the last line if we've added enough items
  313. if ($rows >= $rows_per_line || $bytes >= $bytes_per_line) {
  314. $file->write(";\n");
  315. $lines++;
  316. $bytes = $rows = 0;
  317. }
  318. }
  319. }
  320. // Finish any unfinished insert statements.
  321. if ($rows > 0) {
  322. $file->write(";\n");
  323. $lines++;
  324. }
  325. return $lines;
  326. }
  327. /**
  328. * Get the db connection for the specified db.
  329. */
  330. function _get_db_connection() {
  331. if (!$this->connection) {
  332. if ($this->connection = parent::_get_db_connection()) {
  333. // Set the sql mode because the default is ANSI,TRADITIONAL which is not aware of collation or storage engine.
  334. $this->connection->exec("SET sql_mode=''");
  335. }
  336. }
  337. return $this->connection;
  338. }
  339. /**
  340. * Run a db query on this destination's db.
  341. */
  342. function query($query, $args = array(), $options = array()) {
  343. if ($conn = $this->_get_db_connection()) {
  344. return $conn->query($query, $args, $options);
  345. }
  346. }
  347. /**
  348. * The header for the top of the sql dump file. These commands set the connection
  349. * character encoding to help prevent encoding conversion issues.
  350. */
  351. function _get_sql_file_header() {
  352. return "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  353. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  354. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  355. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  356. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  357. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;
  358. SET NAMES utf8;
  359. ";
  360. }
  361. /**
  362. * The footer of the sql dump file.
  363. */
  364. function _get_sql_file_footer() {
  365. return "
  366. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  367. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  368. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  369. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  370. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  371. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  372. ";
  373. }
  374. }