Namespaces

Classes

Interfaces

1: <?php
2: declare(strict_types=1);
3: /**
4: * +------------------------------------------------------------+
5: * | apnscp |
6: * +------------------------------------------------------------+
7: * | Copyright (c) Apis Networks |
8: * +------------------------------------------------------------+
9: * | Licensed under Artistic License 2.0 |
10: * +------------------------------------------------------------+
11: * | Author: Matt Saladna (msaladna@apisnetworks.com) |
12: * +------------------------------------------------------------+
13: */
14:
15: use Daphnie\Collector;
16: use Daphnie\Metrics\Mysql as MysqlMetrics;
17: use Module\Skeleton\Contracts\Tasking;
18: use Module\Support\Sql;
19: use Opcenter\Database\MySQL\DefinerRemap;
20: use Opcenter\Net\IpCommon;
21:
22: /**
23: * MySQL and PostgreSQL operations
24: *
25: * @package core
26: */
27: class Mysql_Module extends Sql implements Tasking
28: {
29: const DEPENDENCY_MAP = [
30: 'siteinfo',
31: 'diskquota'
32: ];
33: const MYSQL_DATADIR = '/var/lib/mysql';
34: // @var int minimum MySQL version to use new API
35: const NEW_API_VERSION = 50720;
36: // @var int default concurrency setting for users
37: const DEFAULT_CONCURRENCY_LIMIT = 10;
38: // @var int maximum concurrent connections per user
39: const MAX_CONCURRENCY_LIMIT = MYSQL_CONCURRENCY_LIMIT ?: DATABASE_CONCURRENCY_LIMIT;
40:
41: // @var string MySQL export command
42: const EXPORT_CMD = '/usr/bin/mysqldump --quick --single-transaction --add-drop-table --add-drop-trigger --triggers --events -q -R';
43:
44: protected $exportedFunctions = array(
45: '*' => PRIVILEGE_SITE,
46: 'version' => PRIVILEGE_ALL,
47: 'get_elevated_password_backend' => PRIVILEGE_ALL | PRIVILEGE_SERVER_EXEC,
48: 'create_database_backend' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
49: 'delete_database_backend' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
50: 'get_uptime' => PRIVILEGE_ALL,
51: 'assert_permissions' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
52: 'set_option' => PRIVILEGE_ALL,
53: 'get_option' => PRIVILEGE_ALL,
54: 'export_pipe_real' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
55: 'enabled' => PRIVILEGE_SITE | PRIVILEGE_USER,
56: 'repair_database' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
57: 'get_prefix' => PRIVILEGE_SITE | PRIVILEGE_USER,
58:
59: // necessary for DB backup routines
60: 'get_database_size' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
61: 'database_exists' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
62: '_export_old' => PRIVILEGE_SITE | PRIVILEGE_SERVER_EXEC,
63: 'resolve_site_from_database' => PRIVILEGE_SITE | PRIVILEGE_ADMIN,
64: 'recover_innodb_from_disk' => PRIVILEGE_ADMIN,
65: 'stats' => PRIVILEGE_ALL
66: );
67:
68: public function __destruct()
69: {
70: foreach ($this->_tempUsers as $user) {
71: if (!$this->user_exists($user)) {
72: continue;
73: }
74: $this->_delete_temp_user($user);
75: }
76: }
77:
78: public function user_exists($user, $host = 'localhost')
79: {
80: if (!$user) {
81: return false;
82: }
83: $conn = $this->_connect_root();
84: $prefix = $this->get_prefix();
85: if (!($this->permission_level & PRIVILEGE_ADMIN) && $user !== $this->databaseAdmin() &&
86: 0 !== strpos($user, $prefix)
87: ) {
88: $user = $prefix . $user;
89: }
90:
91: $q = $conn->query("SELECT user FROM user WHERE user = '" .
92: $conn->escape_string($user) . "' AND host = '" . $conn->escape_string($host) . "'");
93:
94: return !$q || $q->num_rows > 0;
95: }
96:
97: public function get_prefix()
98: {
99: return $this->getServiceValue('mysql', 'dbaseprefix');
100: }
101:
102: /**
103: * Delete a temporary MySQL user
104: *
105: * @warn do not invoke directly, use wrapper _delete_temp_user()
106: * @param string $user
107: * @return bool
108: */
109: private function _delete_temp_user($user)
110: {
111: if (!Opcenter\Database\MySQL::deleteUser($user, 'localhost')) {
112: return false;
113: }
114:
115:
116: $idx = array_search($user, $this->_tempUsers, true);
117: if ($idx !== false) {
118: unset($this->_tempUsers[$idx]);
119: }
120:
121: return true;
122: }
123:
124: /**
125: * bool delete_mysql_user(string, string[, bool = false])
126: * Delete a MySQL user
127: *
128: * @param string $user username
129: * @param string $host hostname
130: */
131: public function delete_user($user, $host)
132: {
133: if ($user === $this->databaseAdmin() && !Util_Account_Hooks::is_mode('delete')) {
134: return error('Cannot remove main user');
135: } else if (!$this->user_exists($user, $host)) {
136: return error("user `%s' on `%s' does not exist", $user, $host);
137: }
138: $prefix = $this->get_prefix();
139: if ($user !== $this->databaseAdmin() && strpos($user, $prefix) !== 0) {
140: $user = $prefix . $user;
141: }
142: if (\Opcenter\Database\MySQL::version() >= static::NEW_API_VERSION) {
143: return Opcenter\Database\MySQL::deleteUser($user, $host);
144: }
145: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
146: $conn->select_db('mysql');
147: $stmt = $conn->prepare('DELETE FROM user WHERE user = ? AND host = ?');
148: $stmt->bind_param('ss', $user, $host);
149: $stmt->execute();
150: if ($stmt->error) {
151: return new MySQLError('Invalid query, ' . $stmt->error);
152: }
153:
154: $stmt2 = $conn->prepare('DELETE FROM db WHERE user = ? AND host = ?');
155: $stmt2->bind_param('ss', $user, $host);
156: $stmt2->execute();
157: if (!$stmt2->error) {
158: $conn->query('FLUSH PRIVILEGES');
159: } else {
160: return new MySQLError('Invalid query, ' . $stmt2->error);
161: }
162:
163: return ($stmt->affected_rows > 0);
164:
165: }
166:
167: /**
168: * bool store_sql_password (string, string)
169: *
170: * @param string $sqlpasswd plaintext password
171: * @return bool
172: */
173: public function store_password($sqlpasswd)
174: {
175: return $this->set_option('password', $sqlpasswd, 'client');
176: }
177:
178: /**
179: * Set MySQL client option
180: *
181: * Reads from ~/.my.cnf and creates if necessary.
182: * Specify a value of null to remove an option.
183: *
184: * @param string $option
185: * @param string $value
186: * @param string $group
187: * @return bool
188: */
189: public function set_option(string $option, string $value = null, string $group = 'client')
190: {
191: if (DEMO_ADMIN_LOCK && posix_getuid()) {
192: return error("Demo may not alter ~/.my.cnf");
193: }
194:
195: if (!IS_CLI) {
196: return $this->query('mysql_set_option', $option, $value, $group);
197: }
198: $home = $this->user_get_user_home();
199: $path = $this->domain_fs_path() . "{$home}/.my.cnf";
200: if (is_link($path) && (
201: false === ($link = readlink($path)) ||
202: 0 !== strpos($this->domain_fs_path(), realpath($link)))
203: )
204: {
205: unlink($path);
206: }
207: if (!file_exists($path)) {
208: \Opcenter\Filesystem::touch($path, $this->user_id, $this->group_id, 0600);
209: }
210:
211: return \Opcenter\Database\MySQL::setUserConfigurationField($path, $option, $value, $group);
212: }
213:
214: /**
215: * string retrieve_sql_password (string)
216: *
217: * @return string
218: */
219: public function get_password()
220: {
221: return $this->get_option('password');
222: }
223:
224: /**
225: * Get option from MySQL client/server configuration
226: *
227: * @param string $option option name
228: * @param string $group option group
229: * @return mixed option value, false on failure, null on non-existent value
230: */
231: public function get_option($option, $group = 'client')
232: {
233: if (DEMO_ADMIN_LOCK && ($this->permission_level & PRIVILEGE_ADMIN) && posix_getuid()) {
234: return error("Demo may not alter ~/.my.cnf");
235: }
236:
237: if (!IS_CLI) {
238: return $this->query('mysql_get_option', $option, $group);
239: }
240: $home = $this->user_get_user_home();
241: $paths = [
242: $this->domain_fs_path() . "{$home}/.my.cnf",
243: '/etc/my.cnf'
244: ];
245: foreach ($paths as $path) {
246: if (is_link($path) && 0 !== strpos($this->domain_fs_path(), realpath(readlink($path)))) {
247: // naughty!
248: continue;
249: }
250: if (null !== ($val = array_get(\Opcenter\Database\MySQL::getUserConfiguration($path),
251: "{$group}.{$option}", null))) {
252: return $val;
253: }
254: }
255:
256: return null;
257: }
258:
259: public function get_elevated_password_backend()
260: {
261: if (!IS_CLI) {
262: fatal('needs execution from backend');
263: }
264:
265: return Opcenter\Database\MySQL::rootPassword();
266: }
267:
268: /**
269: * Import a database from a dump
270: *
271: * @see Mysql_Module::export()
272: *
273: * @param string $db database name
274: * @param string $file filename
275: * @return bool
276: */
277: public function import($db, $file)
278: {
279: if (!IS_CLI) {
280: return $this->query('mysql_import', $db, $file);
281: }
282:
283: $prefix = $this->get_prefix();
284: // db name passed without prefix
285: if (strncmp($db, $prefix, strlen($prefix))) {
286: $db = $prefix . $db;
287: }
288:
289: $dbs = $this->list_databases();
290: if (false === array_search($db, $dbs, true)) {
291: return error("database `%s' does not exist", $db);
292: }
293: $unlink = null;
294: if (false === ($realfile = $this->_preImport($file, $unlink))) {
295: return false;
296: }
297:
298: $tempUser = $this->_create_temp_user($db);
299: if (!$tempUser) {
300: $this->_postImport($unlink);
301: return error('unable to import database');
302: }
303:
304: /**
305: * Tricky spot! triggers/events are dumped with definer information, which conflicts
306: * with anonymous user imports. Util_Process doesn't handle callbacks on piped input (@todo)
307: * Perform an intermediate sed on statements with DEFINER, change to current user, outputting previous,
308: * then depending upon whether the user exists (non-prefixed match), apply a second query to translate
309: * all triggers/events/procedures to the new user
310: *
311: * Only one remap is supported at this time
312: */
313:
314: $bulk = [];
315: // @todo refactor to utility class
316: if (filetype($realfile) === 'file') {
317: $ret = Util_Process_Safe::exec('sed -i %s %s', [
318: '{
319: /DEFINER=[^* ]*\(\*\| \)/w /dev/stdout
320: s/DEFINER=[^* ]*\(\*\| \)/\1/g
321: }',
322: $realfile
323: ]);
324:
325: if (!$ret['success']) {
326: return error("Aborting import from `%s'. Failed to perform DEFINER pass: %s", $file, $ret['error']);
327: }
328:
329: // make a "best effort" at determining the new ownership
330: if (preg_match_all(Regex::SQL_MYSQL_DEFINER, $ret['stdout'], $matches, PREG_SET_ORDER)) {
331: // default to admin@localhost
332: $bulkChangeCredentials = [];
333: $prefix = $this->get_prefix();
334: $users = [];
335: foreach ($this->list_users() as $user => $hosts) {
336: if (0 === strpos($user, $prefix)) {
337: $user = substr($user, \strlen($prefix));
338: }
339: $users[$user] = [];
340: foreach (array_keys($hosts) as $host) {
341: $users[$user][$host] = 1;
342: }
343: }
344:
345: foreach ($matches as $gm) {
346: // PROCEDURE/FUNCTION is the same
347: if ($gm['type'] === 'FUNCTION') {
348: $gm['type'] = 'PROCEDURE';
349: }
350:
351: if (false !== ($pos = strpos($gm['user'], '_'))) {
352: $gm['user'] = substr($gm['user'], ++$pos);
353: }
354:
355: if (!isset($users[$gm['user']])) {
356: warn(
357: 'User %s not found in user table. Converting %s grant for %s to %s',
358: $gm['user'], $gm['type'], $gm['name'], $this->databaseAdmin()
359: );
360: $gm['user'] = $this->databaseAdmin();
361: $gm['host'] = 'localhost';
362: } else if (!isset($users[$gm['user']][$gm['host']])) {
363: warn(
364: 'Host %s not found in user table. Converting %s grant for %s to localhost',
365: $gm['host'], $gm['type'], $gm['user']
366: );
367: $gm['host'] = 'localhost';
368: }
369:
370: if (empty($bulkChangeCredentials)) {
371: $bulkChangeCredentials = [
372: $gm['user'],
373: $gm['host']
374: ];
375: } else if ($bulkChangeCredentials !== [$gm['user'], $gm['host']]) {
376: warn(
377: 'Import only supports 1 DEFINER adjustment. Converting %s (type: %s) from `%s`@`%s` to `%s`@`%s`',
378: $gm['name'], $gm['type'], $gm['user'], $gm['host'], $bulkChangeCredentials[0],
379: $bulkChangeCredentials[1]
380: );
381: }
382: if (empty($bulk[$gm['type']])) {
383: $bulk[$gm['type']] = [];
384: }
385: $bulk[$gm['type']][] = $gm['name'];
386: }
387: // slip prefix back into user if not main
388: if (isset($bulkChangeCredentials[0]) && $bulkChangeCredentials[0] !== $this->databaseAdmin()) {
389: $bulkChangeCredentials[0] = $this->getConfig('mysql',
390: 'dbaseprefix') . $bulkChangeCredentials[0];
391: }
392: }
393: }
394:
395:
396: $safe = new Util_Process_Safe();
397: $safe->setDescriptor(0, 'file', [$realfile, 'rb'], null, ['mute_stdin' => false]);
398: $safe->setPriority(19);
399: $safe->addCallback(function() use ($unlink, $tempUser) {
400: $this->_postImport($unlink);
401: $this->_delete_temp_user($tempUser);
402: return true;
403: }, 'close');
404:
405: $status = $safe->run('mysql -u %s %s', $tempUser, $db);
406: if (!$status['success']) {
407: return false;
408: }
409: if ($bulk) {
410: $handler = new DefinerRemap($db, $this->_connect_root(true));
411: // spray and pray
412: $handler->remapAll(...$bulkChangeCredentials);
413: }
414:
415: return $status['success'];
416: }
417:
418: /**
419: * Resolve database from site
420: *
421: * @param string $db
422: * @return string|null
423: */
424: public function resolve_site_from_database(string $db): ?string
425: {
426: $db = strtok($db, '/');
427: $prefix = strtok($db, '_') . '_';
428:
429: if ($this->site) {
430: return $this->database_exists($db) ? $this->site : null;
431: }
432:
433: if (!preg_match(Regex::SQL_DATABASE, $db)) {
434: error("Invalid database name");
435: return null;
436: }
437:
438: if ($db === $prefix) {
439: $path = realpath(self::MYSQL_DATADIR . '/' . $prefix);
440: if (!$path || !str_starts_with($path, FILESYSTEM_VIRTBASE)) {
441: return null;
442: }
443: $site = strtok(substr($path, strlen(FILESYSTEM_VIRTBASE)), '/');
444: } else {
445: $site = Opcenter\Map::read(\Opcenter\Database\MySQL::PREFIX_MAP)[$prefix];
446: }
447:
448: if (!$site && $this->site && !str_starts_with($db, $prefix)) {
449: $db = $this->get_prefix() . $db;
450:
451: return $this->resolve_site_from_database($db);
452: }
453:
454: return $site ?: null;
455: }
456:
457: /**
458: * Recover InnoDB from .ibd files
459: *
460: * Existing .ibd are NOT overwritten.
461: *
462: * @param string $db target database
463: * @param string $srcdir source directory containing .ibd files
464: * @param bool $force overwrite .ibd if it exists
465: * @return bool
466: */
467: public function recover_innodb_from_disk(string $db, string $srcdir, bool $force = false): bool
468: {
469: if (!IS_CLI) {
470: return $this->query('mysql_recover_innodb_from_disk', $db, $srcdir, $force);
471: }
472:
473: if (!\Opcenter\Database\MySQL::databaseExists($db)) {
474: return error("Database `%s' does not exist", $db);
475: }
476: if (!is_dir($srcdir)) {
477: return error("Source path `%s' is not a directory", $srcdir);
478: }
479:
480: $glob = array_map(
481: static function ($file) { return basename($file, '.ibd'); },
482: glob("{$srcdir}/*.ibd", GLOB_NOESCAPE)
483: );
484:
485: if (empty($glob)) {
486: return error("No matching *.ibd files found in `%s'", $srcdir);
487: }
488: $tables = \Opcenter\Database\MySQL::getTablesFromDatabase($db);
489:
490: // missing from $dir and cannot be recovered
491: $missingSource = array_values(array_diff($tables, $glob));
492: // present in $dir but missing in database - weird.
493: $missingSchema = array_values(array_diff($glob, $tables));
494:
495: $user = $this->_create_temp_user($db);
496:
497: $datadir = realpath(self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db));
498: if (!$datadir || !($stat = stat($datadir))) {
499: return error(
500: "Unable to stat `%s'",
501: self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db)
502: );
503: }
504:
505: $conn = MySQL::stub();
506: $conn->connect('localhost', $user, null, $db);
507: $conn->query('SET FOREIGN_KEY_CHECKS=0');
508:
509: $importer = static function($table) use ($conn, $srcdir, $datadir, $stat): bool {
510: $ex = \Error_Reporter::exception_upgrade(\Error_Reporter::E_FATAL);
511: try {
512: $conn->query("LOCK TABLES `{$table}` WRITE");
513: } catch (apnscpException $e) {
514: // pass
515: } finally {
516: \Error_Reporter::exception_upgrade($ex);
517: }
518:
519: $conn->query("ALTER TABLE `{$table}` DISCARD TABLESPACE");
520:
521: if (!copy("{$srcdir}/{$table}.ibd", "{$datadir}/{$table}.ibd")) {
522: return false;
523: }
524: if (!\Opcenter\Filesystem::chogp("{$datadir}/{$table}.ibd", $stat['uid'], $stat['gid'], 0600)) {
525: return false;
526: }
527: return (bool)$conn->query("ALTER TABLE `{$table}` IMPORT TABLESPACE");
528: };
529:
530: try {
531: // these files have both a .ibd and exist in table schema, EZ
532: foreach (array_intersect($glob, $tables) as $table) {
533: if (!$force && file_exists("{$datadir}/{$table}.ibd")) {
534: info("InnoDB file `%(path)s/%(table)s.ibd' already exists - skipping %(db)s.%(table)s",
535: ['path' => $datadir, 'table' => $table, 'db' => $db]);
536: continue;
537: }
538: if (!$importer($table)) {
539: warn('Failed to replace table %(table)s in %(db)s',
540: ['table' => $table, 'db' => $db]);
541: } else {
542: info('Restored %(db)s.%(table)s', ['db' => $db, 'table' => $table]);
543: }
544: }
545:
546: // these files exist in the backup, but lack a .frm in data directory - let's try anyway!
547: foreach ($missingSchema as $table) {
548: if (!file_exists("$srcdir/{$table}.frm")) {
549: // it's fucked
550: continue;
551: }
552: warn('%s.%s present in backup, .frm present in data directory, but .ibd missing - continuing import', $db, $table);
553: foreach (['ibd', 'frm'] as $ext) {
554: if (!$force && file_exists("{$datadir}/{$table}.{$ext}")) {
555: info("InnoDB file `%s/%s.%s' already exists - skipping %s.%s",
556: $datadir, $table, $ext, $db, $table);
557: continue;
558: }
559: $ret = copy("{$srcdir}/{$table}.{$ext}", "{$datadir}/{$table}.{$ext}") &&
560: \Opcenter\Filesystem::chogp("{$datadir}/{$table}.{$ext}", $stat['uid'], $stat['gid'], 0600);
561:
562: if (!$ret) {
563: warn('Failed to replace table %s in %s', $table, $db);
564: continue 2;
565: }
566: }
567: // @TODO import/discard tablespace?
568: info('Restored %s.%s', $db, $table);
569: }
570:
571: foreach ($missingSource as $table) {
572: if (file_exists("{$datadir}/{$table}.ibd") || file_exists("{$datadir}/{$table}.MYI")) {
573: continue;
574: }
575: warn("%(db)s.%(table)s reported in `%(path)s' but no backup in %(srcdir)s",
576: ['db' => $db, 'table' => $table, 'path' => $datadir, 'srcdir' => $srcdir]);
577: }
578: } catch (\Throwable $e) {
579: return error("Failed to restore backup into `%(db)s': %(err)s",
580: ['db' => $db, 'err' => $e->getMessage()]);
581: } finally {
582: $conn->query('SET FOREIGN_KEY_CHECKS=1');
583: }
584: // lock is released anyway
585: $conn->query('UNLOCK TABLES');
586: return true;
587: }
588:
589: /**
590: * array list_mysql_databases ()
591: * Queries the db table in the mysql database for applicable grants
592: *
593: * @return array list of databases
594: */
595: public function list_databases()
596: {
597: $prefix = $this->get_prefix();
598: if (!$prefix) {
599: // compatibility with DTSS behavior in MySQL
600: if (!$this->enabled()) {
601: return [];
602: }
603: report('Prefixless site - bug');
604: return [];
605: }
606: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
607: $conn->select_db('mysql');
608: $q = $conn->query("SELECT DISTINCT(REPLACE(db,'\\_','_')) AS db FROM db WHERE db LIKE '" . str_replace('_', '\\_', $prefix) . "%' OR db LIKE '" . str_replace('_', '\\\\_', $prefix) . "%' OR user = '" . $this->databaseAdmin() . "'");
609: $dbs = array();
610: while (null !== ($row = $q->fetch_object())) {
611: $dbs[] = $row->db;
612: }
613:
614: $conn->close();
615:
616: return $dbs;
617: }
618:
619: /**
620: * Create a temporary mysql user
621: *
622: * @param string $db
623: * @return string|object
624: */
625: private function _create_temp_user($db)
626: {
627: // allow use by admin
628: $prefix = (string)$this->get_prefix();
629: $maxlen = \Opcenter\Database\MySQL::fieldLength('user') - strlen($prefix);
630: if ($maxlen < 1) {
631: warn('temp mysql user exceeds field length, cannot create user');
632: return false;
633: }
634: $chars = array(
635: 'a',
636: 'b',
637: 'c',
638: 'd',
639: 'e',
640: 'f',
641: '0',
642: '1',
643: '2',
644: '3',
645: '4',
646: '5',
647: '6',
648: '7',
649: '8',
650: '9',
651: );
652: $maxlen = min(8, $maxlen);
653:
654: $user = $prefix;
655: for ($i = 0; $i < $maxlen; $i++) {
656: $n = random_int(0, 15);
657: $user .= $chars[$n];
658: }
659:
660: // could be handled via add_mysql_user()
661: $sqldb = $this->_connect_root();
662: $q = "SELECT user FROM user WHERE user = '" . $user . "'";
663: $rs = $sqldb->query($q);
664: if ($rs->num_rows > 0) {
665: return error('cannot create temp mysql user');
666: }
667: $q = "CREATE USER '" . $user . "'@'localhost' IDENTIFIED BY ''";
668: $rs = $sqldb->query($q);
669: if (!$rs) {
670: return error('failed to create temp mysql user');
671: }
672: $q = 'GRANT ALL ON `' . $db . "`.* to '" . $sqldb->escape_string($user) . "'@localhost";
673: $rs = $sqldb->query($q);
674: if (!$rs) {
675: return error('failed to create temp mysql user');
676: }
677: $q = "GRANT SELECT ON mysql.proc TO '" . $sqldb->escape_string($user) . "'@localhost";
678: $sqldb->query($q);
679: $this->_register_temp_user($user);
680:
681: return $user;
682: }
683:
684: /**
685: * Change account database prefix
686: *
687: * @param string $prefix
688: * @return bool
689: */
690: public function change_prefix($prefix)
691: {
692: return error('use sql_change_prefix');
693: }
694:
695: public function get_sql_prefix()
696: {
697: deprecated('use sql_get_prefix');
698:
699: return $this->get_prefix();
700: }
701:
702: /**
703: * array list_mysql_users ()
704: * Lists all created users for MySQL
705: */
706: public function list_users()
707: {
708: // meta is corrupted, let's bail
709: if (!$prefix = $this->getServiceValue('mysql', 'dbaseprefix')) {
710: return [];
711: }
712:
713: $prefix = str_replace('_', '\_', $prefix);
714: if (!$prefix) {
715: report('Prefixless site - bug');
716:
717: return [];
718: }
719: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
720: $conn->select_db('mysql');
721: $q = $conn->query("SELECT host,
722: user,
723: password,
724: ssl_type,
725: ssl_cipher,
726: x509_issuer,
727: x509_subject,
728: max_questions,
729: max_updates,
730: max_connections,
731: max_user_connections FROM user WHERE user = '" . $this->databaseAdmin() . "' OR user LIKE '" . $prefix . "%'");
732: $users = array();
733: while (null !== ($row = $q->fetch_array(MYSQLI_ASSOC))) {
734: $row = array_change_key_case($row, CASE_LOWER);
735: $user = $row['user']; $host = $row['host'];
736: $users[$user][$host] = array(
737: 'ssl_type' => $row['ssl_type'],
738: 'ssl_cipher' => $row['ssl_cipher'],
739: 'x509_issuer' => $row['x509_issuer'],
740: 'x509_subject' => $row['x509_subject'],
741: 'max_questions' => (int)$row['max_questions'],
742: 'max_updates' => (int)$row['max_updates'],
743: 'max_user_connections' => (int)$row['max_user_connections'],
744: 'max_connections' => (int)$row['max_connections'],
745: 'password' => $row['password'],
746: );
747: }
748:
749: return $users;
750: }
751:
752: /**
753: * bool add_mysql_user(string, string, string[, int[, int[, int[, string[, string[, string[, string]]]]]]])
754: *
755: * @param $user
756: * @param $host
757: * @param $password
758: * @param int $maxconn
759: * @param int $maxupdates
760: * @param int $maxquery
761: * @param string $ssl
762: * @param string $cipher
763: * @param string $issuer
764: * @param string $subject
765: * @return bool|MySQLError|void
766: */
767: public function add_user(
768: $user,
769: $host,
770: $password,
771: $maxconn = self::DEFAULT_CONCURRENCY_LIMIT,
772: $maxupdates = 0,
773: $maxquery = 0,
774: $ssl = '',
775: $cipher = '',
776: $issuer = '',
777: $subject = ''
778: ) {
779: if (!$this->enabled()) {
780: return error("%(service)s is disabled", 'mysql');
781: }
782:
783: if (!$user) {
784: return error('no username specified');
785: }
786: $dbaseadmin = $this->databaseAdmin();
787: if ($user === $dbaseadmin && !IS_SOAP) {
788: return error("cannot name user after primary account user, `%s'", $dbaseadmin);
789: }
790:
791: $ssl = strtoupper($ssl);
792: if (!$maxconn) {
793: $maxconn = self::DEFAULT_CONCURRENCY_LIMIT;
794: }
795: $host = trim($host);
796: if ($host != 'localhost' && !IpCommon::valid($host) && !preg_match(Regex::SQL_MYSQL_IP_WILDCARD, $host)) {
797: return error("rejected host `%s': only numeric IP addresses are permitted, not hostnames", $host);
798: }
799: if (strlen($password) < self::MIN_PASSWORD_LENGTH) {
800: return error('Password must be at least %d characters', self::MIN_PASSWORD_LENGTH);
801: } else if ($ssl !== '' && $ssl != 'ANY' && $ssl != 'X509' && $ssl != 'SPECIFIED') {
802: return error('Invalid SSL type');
803: } else if ($maxconn < 1 || $maxquery < 0 || $maxupdates < 0) {
804: return error('Max connections, queries, and updates must be greater than 0');
805: } else if ($maxconn > static::MAX_CONCURRENCY_LIMIT) {
806: return error('Max concurrent connections cannot exceed %d. ' .
807: 'Contact support if you need more than %d.',
808: static::MAX_CONCURRENCY_LIMIT, static::MAX_CONCURRENCY_LIMIT);
809: } else if ($this->user_exists($user, $host)) {
810: return error("mysql user `$user' on `$host' exists");
811: }
812: $conn = $this->_connect_root();
813: $prefix = $this->get_prefix();
814: if ($user !== $this->databaseAdmin() && 0 !== strpos($user, $prefix)) {
815: // add the prefix if prefix is not provided, this is to workaround cases where user
816: // is equal to prefixprefixuser
817: $user = $prefix . $user;
818: }
819: if (Opcenter\Database\MySQL::version() >= static::NEW_API_VERSION) {
820: return \Opcenter\Database\MySQL::createUser(
821: $user,
822: $password,
823: $host,
824: [
825: 'ssl' => $ssl,
826: 'cipher' => $cipher,
827: 'issuer' => $issuer,
828: 'subject' => $subject
829: ],
830: [
831: 'connections' => (int)$maxconn,
832: 'query' => (int)$maxquery,
833: 'updates' => (int)$maxupdates
834: ]
835: ) ?: error("User creation failed on `%s@%s'", $user, $host);
836: }
837: $pwclause = 'password(?)';
838: // password is encrypted in new pw form or old
839: if ((\strlen($password) === 16 && ctype_xdigit($password)) ||
840: ($password[0] == '*' && \strlen($password) === 41
841: && ctype_xdigit(substr($password, 1))))
842: {
843: $pwclause = '?';
844: }
845: $needAuth = $conn->columnExists('authentication_string', 'user');
846: $query = 'INSERT INTO user
847: (host,
848: user,
849: password,
850: ssl_type,
851: ssl_cipher,
852: x509_issuer,
853: x509_subject,
854: max_questions,
855: max_updates,
856: max_user_connections' . ($needAuth ? ', authentication_string' : '') . ')
857: VALUES
858: (?,
859: ?,
860: ' . $pwclause . ',
861: ?,
862: ?,
863: ?,
864: ?,
865: ?,
866: ?,
867: ?' . ($needAuth ? ',""' : '') . ');';
868: $stmt = $conn->prepare($query);
869: $stmt->bind_param('sssssssiii', $host, $user, $password, $ssl, $cipher,
870: $issuer, $subject, $maxquery, $maxupdates, $maxconn);
871: $stmt->execute();
872: if ($stmt->error) {
873: return new MySQLError('Invalid query, ' . $stmt->error);
874: }
875: $conn->query('FLUSH PRIVILEGES;');
876: if ($stmt->affected_rows < 1) {
877: return error("user creation `%s@%s' failed", $user, $host);
878: }
879:
880: return true;
881: }
882:
883: /**
884: * Get charset from database
885: *
886: * @param $db
887: * @return null|string
888: */
889: public function get_database_charset(string $db): ?string
890: {
891: if ($this->permission_level & (PRIVILEGE_USER|PRIVILEGE_SITE)) {
892: $prefix = $this->getServiceValue('mysql', 'dbaseprefix');
893: if (0 !== strpos($db, $prefix)) {
894: $db = $prefix . $db;
895: }
896: }
897: if (!\in_array($db, $this->list_databases(), true)) {
898: error('Invalid database %s', $db);
899: return null;
900: }
901: $conn = $this->_connect_root();
902: $q = "SELECT default_character_set_name FROM information_schema.SCHEMATA
903: WHERE schema_name = '" . $conn->escape_string($db). "';";
904: $rs = $conn->query($q);
905:
906: if (!$rs->num_rows) {
907: return null;
908: }
909: return $rs->fetch_object()->default_character_set_name;
910: }
911:
912: /**
913: * Create a new mysql database
914: *
915: * @param string $db
916: * @param string $charset optional default charset
917: * @param string $collation optional default collation
918: * @return bool creation succeeded
919: */
920:
921: public function create_database($db, $charset = 'latin1', $collation = 'latin1_swedish_ci')
922: {
923: if (!IS_CLI) {
924: return $this->query('mysql_create_database', $db, $charset, $collation);
925: }
926:
927: if (!$this->enabled()) {
928: return error("%(service)s is disabled", 'mysql');
929: }
930:
931: $charset = strtolower($charset);
932: $collation = strtolower($collation);
933:
934: if (!preg_match(Regex::SQL_DATABASE, $db)) {
935: return error("invalid database name `%s'", $db);
936: }
937: if (!$this->charset_valid($charset)) {
938: return error("unrecognized mysql charset `%s'", $charset);
939: }
940: if (!$this->collation_valid($collation)) {
941: return error("invalid mysql collation `%s'", $collation);
942: } else if (!$this->collation_compatible($collation, $charset)) {
943: warn("collation `%s' for charset `%s' not sensible", $collation, $charset);
944: }
945:
946: $prefix = $this->get_prefix();
947:
948: // db name passed without prefix
949: if (0 !== strpos($db, $prefix)) {
950: $db = $prefix . $db;
951: }
952:
953: if ($this->database_exists($db)) {
954: return error("database `$db' exists");
955: }
956:
957: if (null !== ($limit = $this->getConfig('mysql', 'dbasenum', null)) && $limit >= 0) {
958: $count = \count($this->list_databases());
959: if ($count >= $limit) {
960: return error("Database limit `%d' reached - cannot create additional databases", $limit);
961: }
962: }
963:
964: $status = $this->query('mysql_create_database_backend', $db, $charset, $collation);
965: if (!$status) {
966: return $status;
967: }
968:
969: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
970: $conn->select_db('mysql');
971: $conn->query('GRANT ALL ON `' . str_replace('_', '\\_', $db) . "`.* to '" . $conn->escape_string($this->databaseAdmin()) . "'@localhost;");
972: if ($conn->error) {
973: \Opcenter\Database\MySQL::dropDatabase($db);
974:
975: return error("failed to create db `%s'. Error while applying grants: `%s' " .
976: "- is control user `%s' missing?",
977: $db,
978: $conn->error,
979: $this->databaseAdmin()
980: );
981: }
982:
983: return info("created database `%s'", $db);
984:
985: }
986:
987: public function charset_valid($charset): bool
988: {
989: $charset = strtolower($charset);
990: $charsets = $this->get_supported_charsets();
991:
992: return array_key_exists($charset, $charsets);
993: }
994:
995: public function get_supported_charsets(): array
996: {
997: $cache = Cache_Global::spawn();
998: $key = 's:mysql.char';
999: $charsets = $cache->get($key);
1000: if ($charsets) {
1001: return $charsets;
1002: }
1003: $db = MySQL::initialize();
1004: $rs = $db->query('SELECT CHARACTER_SET_NAME AS charset, DESCRIPTION AS `desc` FROM INFORMATION_SCHEMA.character_sets');
1005: $charsets = array();
1006: while (null !== ($row = $rs->fetch_object())) {
1007: $charsets[$row->charset] = $row->desc;
1008: }
1009: $cache->set($key, $charsets);
1010:
1011: return $charsets;
1012: }
1013:
1014: /**
1015: * Validate collation name
1016: *
1017: * @param string $collation
1018: * @return bool
1019: */
1020: public function collation_valid($collation): bool
1021: {
1022: $collations = $this->get_supported_collations();
1023: $collation = strtolower($collation);
1024: foreach ($collations as $c) {
1025: if ($c['collation'] === $collation) {
1026: return true;
1027: }
1028: }
1029:
1030: return false;
1031: }
1032:
1033: public function get_supported_collations()
1034: {
1035: $cache = Cache_Global::spawn();
1036: $key = 's:mysql.coll';
1037: $collations = $cache->get($key);
1038: if ($collations) {
1039: return $collations;
1040: }
1041: $collations = [];
1042: $db = MySQL::initialize();
1043: $q = 'SELECT collation_name, character_set_name FROM ' .
1044: "INFORMATION_SCHEMA.collations WHERE is_compiled = 'Yes'";
1045: $rs = $db->query($q);
1046: if (!$rs) {
1047: return $collations;
1048: }
1049:
1050: while (null !== ($row = $rs->fetch_object())) {
1051: $collations[] = array(
1052: 'collation' => $row->collation_name,
1053: 'charset' => $row->character_set_name
1054: );
1055: }
1056: $cache->set($key, $collations);
1057:
1058: return $collations;
1059: }
1060:
1061: /**
1062: * Verify collation + charset combination are compatible
1063: *
1064: * @param string $collation
1065: * @param string $charset
1066: *
1067: * @return bool
1068: */
1069: public function collation_compatible($collation, $charset): bool
1070: {
1071: $db = MySQL::initialize();
1072: $q = 'SELECT 1 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY ' .
1073: "WHERE collation_name = '" . $db->escape($collation) . "' AND " .
1074: "character_set_name = '" . $db->escape($charset) . "'";
1075: $rs = $db->query($q);
1076: if (!$rs) {
1077: return false;
1078: }
1079:
1080: return $rs->num_rows > 0;
1081: }
1082:
1083: /**
1084: * Query information_schema for existence of MySQL database
1085: *
1086: * @param string $db database name
1087: * @return bool
1088: */
1089: public function database_exists($db): bool
1090: {
1091: if (!IS_CLI) {
1092: return $this->query('mysql_database_exists', $db);
1093: }
1094: if (!$db) {
1095: return false;
1096: }
1097: $prefix = '';
1098: if ($this->permission_level & (PRIVILEGE_SITE | PRIVILEGE_USER)) {
1099: $sqlroot = $this->domain_shadow_path() . self::MYSQL_DATADIR;
1100: $normal = \Opcenter\Database\MySQL::canonicalize($db);
1101: $prefix = $this->get_prefix();
1102: if (!file_exists($sqlroot . '/' . $normal)) {
1103: // tut-tut. Resolve db with prefix in mind
1104: $db = $prefix . $db;
1105: }
1106: }
1107: if (\Opcenter\Database\MySQL::databaseExists($db)) {
1108: return true;
1109: } else if ($this->permission_level & PRIVILEGE_ADMIN) {
1110: // used by db backup routine, in future the task should be
1111: // removed from backup, but leave this as it is for now
1112: return false;
1113: }
1114: $user = $this->databaseAdmin();
1115: // double prefix, remove first prefix, then check one last time
1116: if (0 === strpos($db, $prefix . $prefix)) {
1117: $db = (string)substr($db, strlen($prefix));
1118: }
1119: return \Opcenter\Database\MySQL::databaseExists($db, $user);
1120: }
1121:
1122: /**
1123: * bool create_mysql_database_backend (string)
1124: * {@link create_mysql_database}
1125: */
1126: public function create_database_backend($db, $charset, $collation)
1127: {
1128: $dboptData = 'default-character-set=' . $charset . "\n" .
1129: 'default-collation=' . $collation;
1130: /**
1131: * use shadow/ on OverlayFS platforms too. mysqldump
1132: * communicates with mysqld to dump tables, so there's
1133: * no risk of ghosting as seen if we write directly to shadow/
1134: * and query from the composite path fst/
1135: */
1136: $path = $this->domain_shadow_path();
1137: if (!Opcenter\Database\MySQL::prepBackend($path, $db)) {
1138: return error('Failed to prepare database backend storage');
1139: }
1140: $dbRoot = $path . self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db);
1141: $fp = fopen($dbRoot . '/db.opt', 'w');
1142: fwrite($fp, $dboptData);
1143: fclose($fp);
1144: \Opcenter\Filesystem::chogp($dbRoot . '/db.opt', 'mysql', (int)$this->group_id, 0640);
1145:
1146: return file_exists(self::MYSQL_DATADIR . '/' . Opcenter\Database\MySQL::canonicalize($db)) && file_exists($path);
1147: }
1148:
1149: /**
1150: * bool add_mysql_user_permissions (string, string, string, array)
1151: *
1152: * @deprecated
1153: * @see Mysql_Module::set_privileges()
1154: *
1155: * @param string $user
1156: * @param string $host
1157: * @param string $db
1158: * @param array $opts
1159: * @return bool
1160: */
1161: public function add_user_permissions($user, $host, $db, array $opts)
1162: {
1163: deprecated_func('use set_mysql_privileges()');
1164:
1165: return $this->set_privileges($user, $host, $db, $opts);
1166: }
1167:
1168: /**
1169: * Set grants for a MySQL user
1170: *
1171: * @param string $user
1172: * @param string $host
1173: * @param string $db name or '%' for all
1174: * @param array $privileges
1175: * @return bool
1176: */
1177: public function set_privileges(string $user, string $host, string $db, array $privileges): bool
1178: {
1179: if (!$host) {
1180: return error("invalid hostname `$host'");
1181: }
1182:
1183: $privileges = array_change_key_case($privileges);
1184: $prefix = $this->get_prefix();
1185: if ($user != $this->databaseAdmin() && strncmp($user, $prefix, strlen($prefix))) {
1186: $user = $prefix . $user;
1187: }
1188: if ($user != $this->databaseAdmin() && !preg_match('/^' . $prefix . '/', $user)) {
1189: return error("invalid user `%s'", $user);
1190: }
1191: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1192: $conn->select_db('mysql');
1193: /** ignore prefixed dbs as they should have ownership rights */
1194: if ($db === '%') {
1195: if ($user === $this->databaseAdmin()) {
1196: return warn('User %s already has permission to all databases - ignoring', $this->databaseAdmin());
1197: }
1198: // _ is escaped later on
1199: $db = $prefix . '%';
1200: } else if (0 === strpos($db, $prefix)) {
1201: $rs = $conn->query("SELECT 1 FROM db WHERE user = '" . $this->databaseAdmin() . "' AND (db = '" . $db . "' OR db = '" . str_replace('_', '\\_', $db) . "')");
1202: if ($rs->num_rows < 1) {
1203: return error("No grants found in database on `%s' for user `%s'", $db, $this->databaseAdmin());
1204: }
1205: }
1206:
1207: $valid_opts = array(
1208: 'select' => false,
1209: 'insert' => false,
1210: 'update' => false,
1211: 'delete' => false,
1212: 'create' => false,
1213: 'drop' => false,
1214: 'grant' => false,
1215: 'references' => false,
1216: 'index' => false,
1217: 'alter' => false,
1218: 'create_tmp_table' => false,
1219: 'show_view' => false,
1220: 'create_view' => false,
1221: 'create_routine' => false,
1222: 'alter_routine' => false,
1223: 'lock_tables' => false,
1224: 'execute' => false,
1225: 'event' => false,
1226: 'trigger' => false
1227: );
1228:
1229: if (count($privileges) <= 2 && (isset($privileges['read']) || isset($privileges['write']))) {
1230: // simplified mode
1231: $tmp = array();
1232: if (!empty($privileges['read'])) {
1233: $tmp['select'] = $tmp['show_view'] = $tmp['execute'] = true;
1234: }
1235:
1236: if (!empty($privileges['write'])) {
1237: $write = array_diff(array_keys($valid_opts), array('select', 'show_view', 'execute'));
1238: $tmp2 = array_fill_keys($write, true);
1239: $tmp = array_merge($tmp, $tmp2);
1240: }
1241: $privileges = $tmp;
1242: }
1243:
1244: $opts_copy = $valid_opts;
1245: foreach ($valid_opts as $opt => $enabled) {
1246: if (isset($privileges[($opt)]) && $privileges[$opt]) {
1247: $valid_opts[$opt] = $opt . '_priv';
1248: } else {
1249: unset($valid_opts[$opt]);
1250: }
1251: }
1252:
1253: $revoke_opts = array_diff_key($opts_copy, $valid_opts);
1254: foreach (array_keys($opts_copy) as $name) {
1255: $opts[] = (isset($valid_opts[$name])) ? 'Y' : 'N';
1256: }
1257:
1258: array_walk($opts_copy, static function (&$key, $val) {
1259: $key = $val . '_priv';
1260: });
1261: if ($db !== $prefix . '%') {
1262: $rs = $conn->query("SELECT db FROM db WHERE user = '" . $user . "' AND host = '" . $host . "' AND db = '" . str_replace('_', '\\_', $prefix) . '%' . "'");
1263: if ($rs->num_rows > 0) {
1264: return error('User %s@%s has wildcard grants applied. Per-database grants cannot be applied over wildcard grant. User may only be deleted.', $user, $host);
1265: }
1266: }
1267: // check if old format
1268: $rs = $conn->query("SELECT 1 FROM db where user = '" . $user . "' AND host = '" . $host . "' AND db = '" . str_replace('_',
1269: '\\_', $db) . "'");
1270: if ($db === $prefix . '%' || $rs->num_rows > 0) {
1271: $db = str_replace('_', '\\_', $db);
1272: }
1273:
1274: $conn->query('REPLACE INTO db (' . implode(', ',
1275: $opts_copy) . ", `host`, `db`, `user`) VALUES ('" . implode("', '", $opts) .
1276: "', '" . $host . "', '" . $db . "', '" . $user . "');");
1277: $ar = $conn->affected_rows;
1278:
1279: if ($conn->error) {
1280: return error('Error when applying grants, ' . $conn->error);
1281: }
1282:
1283: $conn->query('FLUSH PRIVILEGES;');
1284:
1285: return $ar > 0;
1286: }
1287:
1288: /**
1289: *
1290: * @deprecated
1291: * @see Mysql_Module::revoke_privileges()
1292: */
1293: public function delete_user_permissions($user, $host, $db)
1294: {
1295: deprecated_func('use revoke_from_mysql_db()');
1296:
1297: return $this->revoke_privileges($user, $host, $db);
1298: }
1299:
1300: /**
1301: * Revoke all privileges on a database from a MySQL user
1302: *
1303: * @param string $user
1304: * @param string $host
1305: * @param string $db
1306: * @return bool
1307: */
1308: public function revoke_privileges($user, $host, $db)
1309: {
1310: $prefix = $this->get_prefix();
1311: if ($user != $this->databaseAdmin() && strncmp($user, $prefix, strlen($prefix))) {
1312: $user = $prefix . $user;
1313: }
1314: if ($user != $this->databaseAdmin() && !preg_match('/^' . $prefix . '/', $user)) {
1315: return error("invalid user `$user'");
1316: }
1317: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1318: $conn->select_db('mysql');
1319: $stmt = $conn->prepare('DELETE FROM db WHERE user = ? AND host = ? AND (db = ? OR db = ?)');
1320: $canonical = str_replace('_', '\_', $db);
1321: $stmt->bind_param('ssss', $user, $host, $db, $canonical);
1322: $res = $stmt->execute();
1323: if (!$res) {
1324: return error('Query error while dropping permissions, ' . $stmt->error);
1325: }
1326:
1327: $conn->query('FLUSH PRIVILEGES;');
1328:
1329: return $stmt->affected_rows > 0;
1330: }
1331:
1332: // {{{ enabled()
1333:
1334: public function get_user_permissions($user, $host, $db)
1335: {
1336: deprecated_func('use get_privileges()');
1337:
1338: return $this->get_privileges($user, $host, $db);
1339: }
1340:
1341: // }}}
1342:
1343: /**
1344: * Get MySQL grants for a user on a database
1345: *
1346: * @param string $user
1347: * @param string $host
1348: * @param string $db
1349: * @return array
1350: */
1351: public function get_privileges($user, $host, $db)
1352: {
1353: $prefix = $this->get_prefix();
1354: if ($user != $this->databaseAdmin() && strncmp($user, $prefix, strlen($prefix))) {
1355: $user = $prefix . $user;
1356: }
1357: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1358: $conn->select_db('mysql');
1359:
1360: $stmt = $conn->prepare('SELECT Select_priv, Insert_priv, Update_priv, DELETE_PRIV,
1361: CREATE_PRIV, DROP_PRIV, GRANT_PRIV, REFERENCES_PRIV,
1362: INDEX_PRIV, ALTER_PRIV, CREATE_TMP_TABLE_PRIV,
1363: LOCK_TABLES_PRIV, CREATE_VIEW_PRIV, SHOW_VIEW_PRIV,
1364: CREATE_ROUTINE_PRIV, ALTER_ROUTINE_PRIV, EXECUTE_PRIV,
1365: EVENT_PRIV, TRIGGER_PRIV FROM db WHERE user = ? AND db IN (?,?,?) AND host = ?');
1366: $dbSafe = str_replace('_', '\\_', $db);
1367: $wcSafe = str_replace('_', '\\_', $prefix . '%');
1368: $stmt->bind_param('sssss', $user, $db, $dbSafe, $wcSafe, $host);
1369: $stmt->execute();
1370: $select = $insert = $update = $delete = $create = $drop = $domain =
1371: $grant = $references = $index = $alter = $create_tmp_table =
1372: $lock_tables = $create_view = $show_view = $create_routine =
1373: $alter_routine = $execute = $event = $trigger = null;
1374: $stmt->bind_result($select, $insert, $update, $delete, $create, $drop, $grant,
1375: $references, $index, $alter, $create_tmp_table, $lock_tables,
1376: $create_view, $show_view, $create_routine, $alter_routine,
1377: $execute, $event, $trigger);
1378:
1379: if ($stmt->fetch()) {
1380: $priv = array(
1381: 'select' => $select,
1382: 'insert' => $insert,
1383: 'update' => $update,
1384: 'delete' => $delete,
1385: 'create' => $create,
1386: 'drop' => $drop,
1387: 'grant' => $grant,
1388: 'references' => $references,
1389: 'index' => $index,
1390: 'alter' => $alter,
1391: 'create_tmp_table' => $create_tmp_table,
1392: 'lock_tables' => $lock_tables,
1393: 'create_view' => $create_view,
1394: 'show_view' => $show_view,
1395: 'create_routine' => $create_routine,
1396: 'alter_routine' => $alter_routine,
1397: 'execute' => $execute,
1398: 'trigger' => $trigger,
1399: 'event' => $event
1400: );
1401: array_walk($priv, static function (&$key, $val) {
1402: $key = $key == 'Y';
1403: });
1404: $stmt->close();
1405: } else {
1406: $priv = array(
1407: 'select' => false,
1408: 'insert' => false,
1409: 'update' => false,
1410: 'delete' => false,
1411: 'create' => false,
1412: 'drop' => false,
1413: 'grant' => false,
1414: 'references' => false,
1415: 'index' => false,
1416: 'alter' => false,
1417: 'create_tmp_table' => false,
1418: 'lock_tables' => false,
1419: 'create_view' => false,
1420: 'show_view' => false,
1421: 'create_routine' => false,
1422: 'alter_routine' => false,
1423: 'execute' => false,
1424: );
1425: }
1426: return $priv;
1427: }
1428:
1429: /**
1430: * Returns the version of the MySQL server as an integer
1431: *
1432: * The form of this version number is
1433: * main_version * 10000 + minor_version * 100 + sub_version
1434: * (i.e. version 4.1.0 is 40100)
1435: *
1436: * @param $pretty bool pretty-print version
1437: *
1438: * @return int|string integer representing the server version or string
1439: */
1440: public function version($pretty = false)
1441: {
1442: $version = \Opcenter\Database\MySQL::version();
1443: if (!$pretty) {
1444: return $version;
1445: }
1446:
1447: $mysqlver = array();
1448: foreach (array('patch', 'minor', 'major') as $v) {
1449: $mysqlver[$v] = (int)$version % 100;
1450: $version /= 100;
1451: }
1452:
1453: return $mysqlver['major'] . '.' . $mysqlver['minor'] . '.' .
1454: $mysqlver['patch'];
1455:
1456: }
1457:
1458: /**
1459: * Delete MySQL database from system
1460: *
1461: * @param string $db database
1462: * @return bool
1463: */
1464: public function delete_database($db)
1465: {
1466: $db = str_replace('\\\\', '\\', $db);
1467: $prefix = $this->get_prefix();
1468: if (!$prefix) {
1469: report('Prefixless site - bug');
1470:
1471: return false;
1472: }
1473: $admin = $this->databaseAdmin();
1474: $prefixwc = str_replace('_', '\_', $prefix) . '%';
1475: $conn = new mysqli('localhost', self::MASTER_USER, $this->_get_elevated_password());
1476: $conn->select_db('mysql');
1477: $stmt = $conn->prepare('SELECT user FROM db WHERE (user = ? OR db LIKE ?) AND (db = ? OR db = ?)');
1478: $canonical = str_replace('_', '\_', $db);
1479: $stmt->bind_param('ssss', $admin, $prefixwc, $db, $canonical);
1480: $stmt->execute();
1481: $stmt->store_result();
1482: if ($stmt->num_rows < 1) {
1483: $stmt->free_result();
1484: // db name passed without prefix, db not found,
1485: // don't coerce db to prefix + db unless db deletion failed
1486: if (strncmp($db, $prefix, strlen($prefix))) {
1487: $db = $prefix . $db;
1488:
1489: return $this->delete_database($db);
1490: }
1491: if (!in_array($db, $this->list_databases(), true)) {
1492: // extra lookup for orphaned databases
1493: return error("Unknown database `%s'", $db);
1494: }
1495: }
1496: $stmt->free_result();
1497: $this->query('mysql_assert_permissions');
1498: \Opcenter\Database\MySQL::dropDatabaseGrants($db);
1499: // FLUSH is necessary, because MySQL will permit a separate CREATE DATABASE
1500: // query by the former owner bypassing this requirement
1501: // (it also bypasses filesystem namespacing + quota attribution)
1502: \Opcenter\Database\MySQL::flush();
1503: \Opcenter\Database\MySQL::dropDatabase($db);
1504:
1505: $this->query('mysql_delete_database_backend', $db);
1506:
1507: if (in_array($db, $this->list_backups(), true)) {
1508: $this->delete_backup($db);
1509: }
1510:
1511:
1512: if ($conn->error) {
1513: return error("error while removing database `$db' - " . $conn->error);
1514: }
1515:
1516: return true;
1517: }
1518:
1519: /**
1520: * Remove MySQL Backup
1521: *
1522: * @param string $db
1523: * @return bool
1524: */
1525: public function delete_backup($db)
1526: {
1527: return parent::delete_backup_real('mysql', $db);
1528: }
1529:
1530: /**
1531: * Ensure that /var/lib/mysql/ has mysql:<group id> ownership
1532: */
1533: public function assert_permissions()
1534: {
1535: if (!file_exists($this->domain_fs_path() . self::MYSQL_DATADIR)) {
1536: return false;
1537: }
1538:
1539: chown($this->domain_fs_path() . self::MYSQL_DATADIR, 'mysql');
1540: chgrp($this->domain_fs_path() . self::MYSQL_DATADIR, $this->group_id);
1541:
1542: return true;
1543: }
1544:
1545: public function delete_database_backend($db)
1546: {
1547: $db = \Opcenter\Database\MySQL::canonicalize($db);
1548: if (is_link(self::MYSQL_DATADIR . '/' . $db)) {
1549: @unlink(self::MYSQL_DATADIR . '/' . $db);
1550: }
1551:
1552: return true;
1553:
1554: }
1555:
1556: /**
1557: * bool edit_mysql_user(string, string, array)
1558: * Note when calling through SOAP, all options must be given, otherwise
1559: * the will default to server preferences.
1560: *
1561: * @param string $user user
1562: * @param string $host hostname
1563: * @param array $opts array of options, valid indexes:
1564: * host
1565: * password
1566: * max_user_connections
1567: * max_updates
1568: * max_questions
1569: * use_ssl
1570: * ssl_type
1571: * ssl_cipher
1572: * x509_subject
1573: * x509_issuer
1574: * @return bool query succeeded
1575: */
1576: public function edit_user(string $user, string $host, array $opts): bool
1577: {
1578: $prefix = $this->get_prefix();
1579: if ($user !== $this->databaseAdmin() && 0 !== strpos($user, $prefix)) {
1580: $user = $prefix . $user;
1581: }
1582: if (!is_array($opts)) {
1583: return error('Options must be an array');
1584: }
1585: if (isset($opts['ssl_type'])) {
1586: $opts['ssl_type'] = strtoupper($opts['ssl_type']);
1587: if ($opts['ssl_type'] != '' && $opts['ssl_type'] != 'ANY' &&
1588: $opts['ssl_type'] != 'SPECIFIED' && $opts['ssl_type'] != 'X509'
1589: ) {
1590: return error('Invalid cipher type');
1591: }
1592: }
1593:
1594: if (isset($opts['host']) && $opts['host'] != 'localhost') {
1595:
1596: if (!IpCommon::valid($opts['host']) && !preg_match(Regex::SQL_MYSQL_IP_WILDCARD, $opts['host'])) {
1597: return error("rejected host `%s': only numeric IP addresses are permitted, not hostnames",
1598: $opts['host']);
1599: }
1600: }
1601:
1602: $defaults = array(
1603: 'host' => $host,
1604: 'password' => null,
1605: 'max_user_connections' => self::DEFAULT_CONCURRENCY_LIMIT,
1606: 'max_updates' => 0,
1607: 'max_questions' => 0,
1608: 'use_ssl' => false,
1609: 'ssl_type' => '',
1610: 'ssl_cipher' => '',
1611: 'x509_subject' => '',
1612: 'x509_issuer' => ''
1613: );
1614:
1615: // make copy to check for max_user_connections change
1616: // later in event of password update
1617: $mergeopts = $opts;
1618: foreach ($defaults as $def_nam => $def_val) {
1619: if (!isset($mergeopts[$def_nam])) {
1620: $mergeopts[$def_nam] = $def_val;
1621: }
1622: }
1623: /** if we're not using SSL, blank it out */
1624: if (!$mergeopts['use_ssl']) {
1625: $mergeopts['x509_subject'] = $mergeopts['x509_issuer'] = $mergeopts['ssl_cipher'] = $mergeopts['ssl_type'] = '';
1626: } else if (!isset($mergeopts['ssl_type'])) {
1627: $mergeopts['ssl_type'] = 'ANY';
1628: }
1629:
1630: if ($mergeopts['max_user_connections'] < 1) {
1631: $mergeopts['max_user_connections'] = self::DEFAULT_CONCURRENCY_LIMIT;
1632: }
1633:
1634: if ($mergeopts['max_questions'] < 0 || $mergeopts['max_updates'] < 0) {
1635: return error('Max queries and updates must be greater than 0');
1636: }
1637: if (isset($opts['max_user_connections']) && $opts['max_user_connections'] > static::MAX_CONCURRENCY_LIMIT) {
1638: return error('Max connection limit %d. Must file a ticket justifying need. ' .
1639: 'Check index placements first.', static::MAX_CONCURRENCY_LIMIT);
1640: }
1641: if (!is_null($mergeopts['password']) && strlen($mergeopts['password']) < self::MIN_PASSWORD_LENGTH) {
1642: return error('password must be at least %d characters long', self::MIN_PASSWORD_LENGTH);
1643: }
1644:
1645: if (!empty($mergeopts['use_ssl']) && $user === $this->databaseAdmin() && $host === 'localhost') {
1646: warn("Enabling SSL for localhost will break phpMyAdmin access on this account");
1647: }
1648:
1649: $conn = $this->_connect_root();
1650:
1651: $stmt = $conn->prepare('SELECT user FROM user WHERE user = ? AND host = ?');
1652: $stmt->bind_param('ss', $user, $host);
1653: $stmt->execute();
1654: $stmt->store_result();
1655: if ($stmt->num_rows < 1) {
1656: $stmt->free_result();
1657: return error('invalid user@host specified: %s@%s', $user, $host);
1658: }
1659: $stmt->free_result();
1660:
1661: if ($host === 'localhost' &&
1662: $user === $this->databaseAdmin() && ($mergeopts['host'] ?: $host) !== $host)
1663: {
1664: return error("Cannot modify the hostname attribute for %(user)s on %(host)s", ['user' => $user, 'host' => $host]);
1665: }
1666:
1667: if (Opcenter\Database\MySQL::version() >= self::NEW_API_VERSION) {
1668: $params = [
1669: $user,
1670: $host,
1671: [
1672: 'ssl' => $mergeopts['use_ssl'] ?: false,
1673: 'subject' => $mergeopts['x509_subject'] ?: null,
1674: 'issuer' => $mergeopts['x509_issuer'] ?: null,
1675: 'ssl_type' => $mergeopts['ssl_type'] ?: null,
1676: 'password' => $mergeopts['password'] ?: null,
1677: 'connections' => (int)($mergeopts['max_user_connections'] ?: 0),
1678: 'updates' => (int)($mergeopts['max_updates'] ?: 0),
1679: 'query' => (int)($mergeopts['max_questions'] ?: 0),
1680: 'host' => $mergeopts['host'] ?: null
1681: ]
1682: ];
1683: if (!Opcenter\Database\MySQL::alterUser(...$params)) {
1684: return false;
1685: }
1686: } else {
1687: $stmt = $conn->prepare('UPDATE user
1688: SET
1689: host = ?,
1690: ssl_type = ?,
1691: ssl_cipher = ?,
1692: x509_issuer = ? ,
1693: x509_subject = ?,
1694: max_questions = ?,
1695: max_updates = ?,
1696: max_user_connections = ?
1697: WHERE
1698: user = ?
1699: AND
1700: host = ?');
1701:
1702: $stmt->bind_param('sssssiiiss',
1703: $mergeopts['host'],
1704: $mergeopts['ssl_type'],
1705: $mergeopts['ssl_cipher'],
1706: $mergeopts['x509_issuer'],
1707: $mergeopts['x509_subject'],
1708: $mergeopts['max_questions'],
1709: $mergeopts['max_updates'],
1710: $mergeopts['max_user_connections'],
1711: $user,
1712: $host
1713: );
1714: $stmt->execute();
1715: if ($stmt->error) {
1716: return new MySQLError('Invalid query, ' . $stmt->error);
1717: }
1718:
1719: if ($mergeopts['host'] !== $defaults['host']) {
1720: $stmt = $conn->prepare('UPDATE db SET host = ? WHERE user = ? AND host = ?');
1721: $stmt->bind_param('sss', $mergeopts['host'], $user, $defaults['host']);
1722: $stmt->execute();
1723: if ($stmt->error) {
1724: return error('error while updating DB grants, %s', $stmt->error);
1725: }
1726: }
1727: /** finally update the password if changed */
1728: if ($mergeopts['password']) {
1729: $pwclause = 'password(?)';
1730: $password = $mergeopts['password'];
1731: // password is encrypted in new pw form or old
1732: if ($password[0] == '*' && strlen($password) == 41
1733: && ctype_xdigit(substr($password, 1)) ||
1734: /** only accept old-style passwords on platforms <v6 */
1735: strlen($password) == 16 && ctype_xdigit($password) && version_compare(platform_version(), '6',
1736: '<')
1737: ) {
1738: $pwclause = '?';
1739: }
1740: $stmt2 = $conn->prepare('UPDATE user SET password = ' . $pwclause . ' WHERE user = ? AND host = ?;');
1741:
1742: $stmt2->bind_param('sss', $password, $user, $mergeopts['host']);
1743: $stmt2->execute();
1744: if ($stmt2->error) {
1745: return new MySQLError('Query error while updating password, ' . $stmt2->error);
1746: }
1747: }
1748: }
1749:
1750: $conn->query('FLUSH PRIVILEGES');
1751:
1752: if ($user === $this->databaseAdmin() && $mergeopts['password']) {
1753: $this->set_option('user', $this->databaseAdmin(), 'client');
1754: $this->set_option('password',
1755: str_replace(array('"'), array('\"'), $mergeopts['password']),
1756: 'client'
1757: );
1758: }
1759:
1760: return true;
1761: }
1762:
1763: /**
1764: * bool service_enabled (string)
1765: *
1766: * Checks to see if a service is enabled
1767: *
1768: * @deprecated
1769: * @see Mysql_Module::enabled()
1770: * @return bool
1771: */
1772: public function service_enabled()
1773: {
1774: deprecated('use enabled()');
1775:
1776: return $this->enabled();
1777: }
1778:
1779: /**
1780: * MySQL/PostgreSQL service enabled on account
1781: *
1782: * Checks to see if either MySQL or PostgreSQL is enabled on an account
1783: *
1784: * @return bool
1785: */
1786: public function enabled()
1787: {
1788: return parent::svc_enabled('mysql');
1789: }
1790:
1791: public function truncate_database($db)
1792: {
1793:
1794: return $this->_mysql_empty_truncate_wrapper($db, 'truncate');
1795: }
1796:
1797: private function _mysql_empty_truncate_wrapper($db, $mode)
1798: {
1799: if ($mode != 'truncate' && $mode != 'empty') {
1800: return error("unknown mode `%s'", $mode);
1801: }
1802: if ($mode == 'empty') {
1803: // semantically more correct
1804: $mode = 'drop';
1805: }
1806:
1807: $prefix = $this->getServiceValue('mysql', 'dbaseprefix');
1808: if (0 !== strpos($db, $prefix)) {
1809: $db = $prefix . $db;
1810: }
1811:
1812: if (!$this->database_exists($db)) {
1813: return error("unknown database, `%s'", $db);
1814: }
1815:
1816: $user = $this->_create_temp_user($db);
1817: if (!$user) {
1818: return error("failed to %s db `%s'", $mode, $db);
1819: }
1820: $conn = new mysqli('localhost', $user);
1821: $conn->set_charset('utf8mb4');
1822: if (!$conn->select_db($db)) {
1823: return error("unable to establish db connection for user `%s' on db `%s'", $user, $db);
1824: }
1825:
1826: $conn->query('SET FOREIGN_KEY_CHECKS=0');
1827:
1828: $q = "SELECT CONCAT('" . strtoupper($mode) . " TABLE ','`', table_schema,'`','.','`',TABLE_NAME,'`', ';')
1829: FROM INFORMATION_SCHEMA.TABLES where table_schema in ('" . $conn->escape_string($db) . "');";
1830: $res = $conn->query($q);
1831: while (null !== ($rs = $res->fetch_row())) {
1832: if (!$conn->query($rs[0])) {
1833: warn("failed to %s table `%s'", $mode, $rs[0]);
1834: }
1835: }
1836:
1837: $conn->query('SET @@FOREIGN_KEY_CHECKS=1;');
1838: if (!$res) {
1839: return error("%s failed on database `%s': `%s'", $mode, $db, $conn->error);
1840: }
1841: $this->_delete_temp_user($user);
1842:
1843: return true;
1844: }
1845:
1846: /**
1847: * Database is empty
1848: *
1849: * @param $db
1850: * @return bool
1851: */
1852: public function empty($db) {
1853: if (!$this->database_exists($db)) {
1854: return true;
1855: }
1856:
1857: return \count(\Opcenter\Database\MySQL::getTablesFromDatabase($db)) === 0;
1858: }
1859:
1860: public function empty_database($db)
1861: {
1862: return $this->_mysql_empty_truncate_wrapper($db, 'empty');
1863: }
1864:
1865: /**
1866: * Export a MySQL database
1867: *
1868: * @param string $db
1869: * @param string|null $file optional filename
1870: * @return mixed path of export or false on failure
1871: */
1872: public function export($db, $file = null)
1873: {
1874: if (!IS_CLI) {
1875: return $this->query('mysql_export', $db, $file);
1876: }
1877:
1878: if (is_null($file)) {
1879: $file = $db . '.sql';
1880: }
1881: if (!in_array($db, $this->list_databases(), true)) {
1882: return error('Invalid database ' . $db);
1883: }
1884: if ($file[0] !== '/' && $file[0] !== '.' && $file[0] !== '~') {
1885: $file = '/tmp/' . $file;
1886: }
1887: $pdir = dirname($file);
1888: if (!$this->file_exists($pdir) && !$this->file_create_directory($pdir, 0755, true)) {
1889: return error("failed to create parent directory, `%s'", $pdir);
1890: }
1891: $path = $this->file_make_path($file);
1892: if (!$path) {
1893: return error("invalid file `%s'", $file);
1894: }
1895:
1896: if (file_exists($path) &&
1897: (filesize($path) > 0 || realpath($path) !== $path || fileowner($path) < USER_MIN_UID))
1898: {
1899: return error('%s: file exists, cannot overwrite', $file);
1900: }
1901: $user = $this->_create_temp_user($db);
1902: $cmd = 'umask 077 && ' . self::EXPORT_CMD . ' -u %s %s > %s';
1903: if (!$user) {
1904: $user = self::MASTER_USER;
1905: $rootpw = escapeshellarg($this->_get_elevated_password());
1906: $cmd = str_replace('-u %s', '-u %s -p' . $rootpw, $cmd);
1907: }
1908:
1909: $fsizelimit = Util_Ulimit::get('fsize');
1910: if ($this->get_database_size($db) > $fsizelimit / self::DB_BIN2TXT_MULT) {
1911: // make sure ulimit accommodates the db dump
1912: Util_Ulimit::set('fsize', 'unlimited');
1913: } else {
1914: // no need to change this then
1915: $fsizelimit = null;
1916: }
1917: $proc = new Util_Process_Safe();
1918: // gid must come first
1919: $proc->setSgid($this->group_id);
1920: $proc->setSuid($this->user_id);
1921: $proc->setPriority(19);
1922:
1923: $status = $proc->run($cmd,
1924: $user,
1925: $db,
1926: $path
1927: );
1928:
1929: if ($user != self::MASTER_USER) {
1930: $this->_delete_temp_user($user);
1931: }
1932:
1933: if (!is_null($fsizelimit)) {
1934: Util_Ulimit::set('fsize', $fsizelimit);
1935: }
1936:
1937: if (!$status['success'] || !file_exists($path)) {
1938: return error('export failed: %s', $status['stderr']);
1939: }
1940:
1941: if (!$status['success']) {
1942: return error('export failed: %s', $status['stderr']);
1943: }
1944:
1945: return $this->file_unmake_path($path);
1946: }
1947:
1948: /**
1949: * Clone a database
1950: *
1951: * @param string $from source database
1952: * @param string $to target database
1953: * @return bool
1954: */
1955: public function clone(string $from, string $to): bool
1956: {
1957: if ($this->database_exists($to) && !$this->empty($to)) {
1958: return error("Database `%s' already exists", $to);
1959: }
1960: if (!$this->database_exists($from)) {
1961: return error("Database `%s' does not exist", $from);
1962: }
1963: if (!$this->database_exists($to) && !$this->create_database($to)) {
1964: return false;
1965: }
1966: $pipe = $this->export_pipe($from);
1967: defer($_, static function () use ($pipe) {
1968: if (file_exists($pipe)) {
1969: unlink($pipe);
1970: }
1971: });
1972: return $this->import($to, $this->file_unmake_path($pipe));
1973: }
1974:
1975: // {{{ delete_mysql_backup()
1976:
1977: /**
1978: * Get disk space occupied by database
1979: *
1980: * @param string $db database name
1981: * @return int storage in bytes
1982: */
1983: public function get_database_size($db)
1984: {
1985: if (!IS_CLI) {
1986: $resp = $this->query('mysql_get_database_size', $db);
1987:
1988: return (int)$resp;
1989: }
1990:
1991: // mysql type
1992: $dir = self::MYSQL_DATADIR . '/' . \Opcenter\Database\MySQL::canonicalize($db);
1993: // database created as directory in /var/lib/mysql
1994: // instead of under fst
1995: if (($this->permission_level & (PRIVILEGE_SITE | PRIVILEGE_USER)) && !is_link($dir)) {
1996: $this->_move_db($db);
1997: }
1998: if (!file_exists($dir) || (is_link($dir) && !readlink($dir))) {
1999: warn($db . ': database does not exist');
2000:
2001: return 0;
2002: }
2003:
2004: $space = 0;
2005: $dh = opendir($dir);
2006: if (!$dh) {
2007: error("failed to open database directory `%s'", $dir);
2008:
2009: return 0;
2010: }
2011: while (($file = readdir($dh)) !== false) {
2012: if ($file == '.' || $file == '..') {
2013: continue;
2014: }
2015: $space += filesize($dir . '/' . $file);
2016: }
2017: closedir($dh);
2018:
2019: return $space;
2020: }
2021:
2022: // }}}
2023:
2024: private function _move_db($db)
2025: {
2026:
2027: /**
2028: * make sure the DB is accessed by the correct user
2029: * otherwise the DB will be relocated under the caller's fs
2030: */
2031: $prefix = $this->databaseAdmin();
2032: if (strncmp($db, $prefix, strlen($prefix))) {
2033: return true;
2034: }
2035: $dbfs = \Opcenter\Database\MySQL::canonicalize($db);
2036: $src = self::MYSQL_DATADIR . '/' . $dbfs;
2037: $dest = $this->domain_fs_path() . self::MYSQL_DATADIR . '/' . $dbfs;
2038: if (is_dir($dest)) {
2039: report('dest db exists - %s', $dest);
2040:
2041: return false;
2042: }
2043: \Opcenter\Filesystem::mkdir($dest, 'mysql', $this->group_id, 02750);
2044: /* a read lock should be used in this situation to ensure
2045: * absolutely no data is lost in the process
2046: * only a global flush tables would work, but this can
2047: * cause a 15+ second hang while tables are flushed
2048: * as a consequence, forgo the read lock and just move the files
2049: *
2050: * this process is also quicker than an export/import routine
2051: */
2052:
2053: $dh = opendir($src);
2054: if (!$dh) {
2055: return error("could not relocate database `%s'", $db);
2056: }
2057:
2058: while (false !== ($file = readdir($dh))) {
2059: if ($file == '.' || $file == '..') {
2060: continue;
2061: }
2062: rename($src . '/' . $file, $dest . '/' . $file);
2063: chown($dest . '/' . $file, 'mysql');
2064: chgrp($dest . '/' . $file, $this->group_id);
2065: }
2066:
2067: rmdir($src);
2068: symlink($dest, $src);
2069: warn("database `%s' relocated under account filesystem root", $db);
2070: $db = $this->_connect_root();
2071: $db->query('FLUSH TABLES');
2072:
2073: return true;
2074: }
2075: // }}}
2076:
2077: // {{{ get_mysql_backup_config()
2078:
2079: /**
2080: * Export a db to a named pipe for immediate download
2081: *
2082: * @param $db
2083: * @return bool|void
2084: */
2085: public function export_pipe($db)
2086: {
2087: if (version_compare(platform_version(), '4.5', '<=')) {
2088: return error('platform version too old to support download feature');
2089: }
2090:
2091: $dbs = $this->list_databases();
2092: if (!\in_array($db, $dbs, true)) {
2093: $originalDb = $db;
2094: $db = $this->get_prefix() . $db;
2095: if (!\in_array($db, $dbs, true)) {
2096: return error('Invalid database %s', $originalDb);
2097: }
2098: }
2099:
2100: $user = $this->_create_temp_user($db);
2101:
2102: return $this->query('mysql_export_pipe_real', $db, $user);
2103: }
2104:
2105: // }}}
2106:
2107: // {{{ get_mysql_backup_config()
2108:
2109: /**
2110: * Export a database to a named pipe
2111: *
2112: * Differs from export_pipe in that it may only be called internally
2113: * or from backend, no API access
2114: *
2115: * @param $db
2116: * @param $user if empty use superuser
2117: * @return bool|string|void
2118: */
2119: public function export_pipe_real($db, $user)
2120: {
2121: if (!IS_CLI) {
2122: return $this->query('mysql_export_pipe_real', $db, $user);
2123: }
2124:
2125:
2126: // automatically cleaned up on exit()/destruct
2127:
2128: $cmd = self::EXPORT_CMD . ' -u %s %s -r%s';
2129: if (!$user) {
2130: $user = self::MASTER_USER;
2131: $rootpw = escapeshellarg($this->_get_elevated_password());
2132: $cmd = str_replace('-u %s', '-u %s --password=' . $rootpw, $cmd);
2133: }
2134:
2135: // @XXX potential race condition
2136: $fifo = tempnam($this->domain_fs_path('/tmp'), 'id-' . $this->site);
2137: unlink($fifo);
2138: if (!posix_mkfifo($fifo, 0600)) {
2139: return error('failed to ready pipe for export');
2140: }
2141: chown($fifo, File_Module::UPLOAD_UID);
2142: $proc = new Util_Process_Fork();
2143:
2144: // lowest priority
2145: $proc->setPriority(19);
2146: $proc->setUser(APNSCP_SYSTEM_USER);
2147:
2148: $status = $proc->run($cmd,
2149: $user,
2150: $db,
2151: $fifo
2152: );
2153:
2154: if (!$status['success'] || !file_exists($fifo)) {
2155: return error('export failed: %s', $status['stderr']);
2156: }
2157: register_shutdown_function(static function () use ($fifo) {
2158: if (file_exists($fifo)) {
2159: unlink($fifo);
2160: }
2161:
2162: });
2163:
2164: return $fifo;
2165: }
2166:
2167: // }}}
2168:
2169: /**
2170: * int get_mysql_uptime
2171: *
2172: * @return int time in seconds
2173: */
2174: public function get_uptime(): int
2175: {
2176: $db = MySQL::initialize();
2177:
2178: return $db->query("SHOW status LIKE 'uptime'")->fetch_object()->value;
2179:
2180: }
2181:
2182: // {{{ mysql_database_exists()
2183:
2184: public function add_backup(
2185: string $db,
2186: string $extension = DATABASE_BACKUP_EXTENSION,
2187: int $span = DATABASE_BACKUP_SPAN,
2188: int $preserve = DATABASE_BACKUP_PRESERVE,
2189: $email = ''
2190: )
2191: {
2192: return parent::add_backup_real('mysql', $db, $extension, $span, $preserve, $email);
2193: }
2194:
2195: public function edit_backup(
2196: string $db,
2197: string $extension = DATABASE_BACKUP_EXTENSION,
2198: int $span = DATABASE_BACKUP_SPAN,
2199: int $preserve = DATABASE_BACKUP_PRESERVE,
2200: $email = ''
2201: )
2202: {
2203: return $this->edit_backup_real('mysql', $db, $extension, $span, $preserve, $email);
2204: }
2205:
2206: public function list_backups()
2207: {
2208: return parent::list_backups_real('mysql');
2209: }
2210:
2211: /**
2212: * Fetch MySQL backup task information
2213: *
2214: * span => (integer) days between backups
2215: * hold => (integer) number of backups to preserve
2216: * next => (integer) unix timestamp of next backup
2217: * ext => (string) extension of backup
2218: * email => (string) notify address after backup
2219: *
2220: * @param string $db database name
2221: * @return array
2222: */
2223: public function get_backup_config($db)
2224: {
2225: return parent::get_backup_config_real('mysql', $db);
2226: }
2227:
2228: public function repair_database($db)
2229: {
2230: if (!IS_CLI) {
2231: return $this->query('mysql_repair_database', $db);
2232: }
2233:
2234: if (!$this->database_exists($db)) {
2235: return error("unknown database `%s'", $db);
2236: }
2237:
2238: $sqlroot = $this->domain_fs_path() . self::MYSQL_DATADIR;
2239: if (!file_exists($sqlroot . '/' . $db)) {
2240: // tut-tut. Resolve db with prefix in mind
2241: $prefix = $this->get_prefix();
2242: $db = $prefix . $db;
2243: }
2244: // make sure there are tables in this database to actually check...
2245: $files = glob($sqlroot . '/' . $db . '/*');
2246: if (count($files) < 2) {
2247: return true;
2248: }
2249:
2250: // negotiate to use mysqlcheck or myisamchk
2251: $quota = $this->site_get_account_quota();
2252: $conn = $this->_connect_root();
2253: $q = 'SELECT MAX(Data_length) AS max FROM ' .
2254: "information_schema.tables WHERE table_schema = '" .
2255: $conn->real_escape_string($db) . "'";
2256: $rs = $conn->query($q);
2257: $row = $rs->fetch_object();
2258: $tblsz = $row->max / 1024 * 1.25; //working room
2259:
2260: $qfree = $quota['qhard'] - $quota['qused'];
2261: $cmd = 'env HOME=/root mysqlcheck --auto-repair %s';
2262: if ($tblsz > $qfree) {
2263: warn('not enough storage to safely use mysqlcheck (need %d KB have %d KB free): reverting to direct IO',
2264: $tblsz, $qfree
2265: );
2266: $cmd = 'myisamchk -r -c ' . $sqlroot . '/%s/*.MYI';
2267: }
2268: $fsizelimit = Util_Ulimit::get('fsize');
2269: Util_Ulimit::set('fsize', 'unlimited');
2270: $ret = Util_Process_Safe::exec($cmd, array($db), ['mute_stderr' => true]);
2271: Util_Ulimit::set('fsize', $fsizelimit);
2272: if (!$ret['success'] && false === strpos($ret['stderr'], "doesn't exist")) {
2273: return error("`%s' repair failed:\n%s", $db, $ret['stderr']);
2274: }
2275:
2276: return info("`%s' repair succeeded:\n%s", $db, $ret['output']);
2277: }
2278:
2279: /**
2280: * Kill a mysql connection
2281: *
2282: * @link mysql_processlist
2283: * @param integer $id
2284: * @return bool
2285: */
2286: public function kill($id)
2287: {
2288: $db = $this->_connect_root();
2289: $id = intval($id);
2290: $procs = $this->get_processlist();
2291: $found = 0;
2292: foreach ($procs as $p) {
2293: if ($p['id'] == $id) {
2294: $found = 1;
2295: break;
2296: }
2297: }
2298: if (!$found) {
2299: return error("`%d': invalid query id specified", $id);
2300: }
2301: $q = "KILL $id";
2302: $rs = $db->query($q);
2303:
2304: return (bool)$rs;
2305: }
2306:
2307: /**
2308: * Get active mysql connections
2309: *
2310: * Array
2311: * (
2312: * [0] => Array
2313: * (
2314: * [id] => 11024
2315: * [user] => debug
2316: * [host] => localhost
2317: * [db] => debug
2318: * [command] => Query
2319: * [state] => User sleep
2320: * [info] => select sleep(1000)
2321: * )
2322: * )
2323: *
2324: * @return array
2325: */
2326: public function get_processlist()
2327: {
2328: $conns = array();
2329: $db = $this->_connect_root();
2330: $user = $this->databaseAdmin();
2331: $prefix = $this->get_prefix();
2332: $q = 'SELECT id, user, host, db, command, time, state, info FROM ' .
2333: "information_schema.processlist WHERE user = '" .
2334: $db->real_escape_string($user) . "' OR user LIKE '" . $db->real_escape_string($prefix) . "%'";
2335: $rs = $db->query($q);
2336: while (null != ($row = $rs->fetch_object())) {
2337: $conns[] = array(
2338: 'id' => $row->id,
2339: 'user' => $row->user,
2340: 'host' => $row->host,
2341: 'db' => $row->db,
2342: 'command' => $row->command,
2343: 'state' => $row->state,
2344: 'info' => $row->info
2345: );
2346: }
2347:
2348: return $conns;
2349: }
2350:
2351: /**
2352: * Get basic MySQL server statistics
2353: *
2354: * @return array
2355: */
2356: public function stats(): array {
2357: $conn = \MySQL::initialize();
2358: $vars = [
2359: 'Queries',
2360: 'Uptime',
2361: 'Connections',
2362: 'Questions',
2363: 'TABLE_OPEN_CACHE_HITS',
2364: 'MAX_USED_CONNECTIONS',
2365: 'TABLE_OPEN_CACHE_MISSES',
2366: 'OPENED_FILES',
2367: 'OPENED_TABLES',
2368: 'THREADS_RUNNING',
2369: 'THREADS_CREATED'
2370: ];
2371:
2372: $fields = implode(',', array_map(static function ($var) use ($conn) {
2373: return "'$var'";
2374: }, $vars));
2375: $rs = $conn->query("SELECT LOWER(variable_name) AS name, variable_value AS val FROM information_schema.global_status WHERE variable_name IN($fields);");
2376:
2377: $items = array_build($rs->fetch_all(\MYSQLI_ASSOC), static function ($k, $v) {
2378: return [$v['name'], (int)$v['val']];
2379: });
2380:
2381: $items['observed_queries_per_second'] = null;
2382: $items['queries_per_second'] = round($items['queries']/$items['uptime'], 4);
2383:
2384: $key = 'mysql.qpslocf';
2385: $cache = \Cache_Global::spawn();
2386: if (false !== ($locf = $cache->get($key))) {
2387: if ($items['uptime'] > $locf[0]) {
2388: // check if server reset
2389: $items['observed_queries_per_second'] = round(($items['queries'] - $locf[1]) / ($items['uptime'] - $locf[0]), 4);
2390: $cache->del($key);
2391: }
2392: return $items;
2393: } else {
2394: $cache->set($key, [$items['uptime'], $items['queries']]);
2395: }
2396:
2397:
2398: return $items;
2399: }
2400:
2401: public function _cron(Cronus $c)
2402: {
2403: if (!TELEMETRY_ENABLED) {
2404: return;
2405: }
2406:
2407: $collector = new Collector(PostgreSQL::pdo());
2408: $status = $this->stats();
2409: foreach (MysqlMetrics::getAttributeMap() as $attr => $metric) {
2410: if (!isset($status[$metric])) {
2411: // MariaDB 10.3.20 lacks table cache hits
2412: continue;
2413: }
2414: $val = $status[$metric];
2415:
2416: if ($val instanceof Closure) {
2417: $val = $val($status);
2418: }
2419: $collector->add("mysql-{$attr}", null, $val);
2420: }
2421:
2422: }
2423:
2424: /**
2425: * Get max length of a column in mysql schema
2426: *
2427: * @param string $field
2428: * @return int|null
2429: */
2430: public function schema_column_maxlen($field): ?int
2431: {
2432: if (!IS_CLI) {
2433: return $this->query('mysql_schema_column_maxlen', $field);
2434: }
2435: if ($field !== 'user' && $field !== 'db') {
2436: error("unsupported field `%s' requested", $field);
2437: return null;
2438: }
2439: return \Opcenter\Database\MySQL::fieldLength($field);
2440: }
2441: }
ApisCP Modules API documentation generated by ApiGen v7.0.0-alpha.6