search

manticoresoftware / manticoresearch

Easy to use open source fast database for search | Good alternative to Elasticsearch now | Drop-in replacement for E in the ELK soon
https://manticoresearch.com
GNU General Public License v3.0
9.08k stars 509 forks source link

mysqldump: keep cluster name #2249

Open sanikolaev opened 6 months ago

sanikolaev commented 6 months ago

Proposal:

When a table is being dumped with mysqldump and the table is in a cluster, let's keep the cluster name. It makes possible using mysqldump to reindex the table in a cluster like this:

mysqldump -P9306 -h0 --replace -etc manticore t|mysql -P9306 -h0

Another option is to make mysqldump ... cluster:table possible:

mysqldump -P9306 -h0 --replace -etc --skip-comments manticore c:t
...
mysqldump: Couldn't find table: "c:t"

Probably it fails at:

mysql> show create table c:t;
ERROR 1064 (42000): You have an error in your query. Please, double-check it.

Checklist:

To be completed by the assignee. Check off tasks that have been completed or are not applicable.

- [x] Task estimated - [x] Specification created, reviewed and approved - [x] Implementation completed - [ ] Tests developed - [x] Documentation updated - [x] Documentation proofread - [x] Changelog updated - [x] OpenAPI YAML updated and issue created to rebuild clients
klirichek commented 2 months ago

Seems to be not possible without sensitive changes. mysqldump start actual processing by calling show tables (db-wide), or show tables like 'FOO' (table-wide). (see test 272 for details).

As long as we return no cluster name in 'show tables' output, mysqldump can't imagine anything about cluster. mysqldump ... cluster:table is also not possible, since show tables like 'cluster:table' will return zero by the same reason.

Starting from this moment, all the rest issues (like mentioned show create table... etc.) are not important, as mysqldump fails immediately after it can't find table.

Since in this call we have no sign of mysqldump (like /*!40001 SQL_NO_CACHE */ directive in select...), dealing is possible only if we change output of 'show tables' globally. But such change may be critical for somebody who also uses show tables for any purposes.

sanikolaev commented 2 months ago 
snikolaev@dev2:~$ mysql -P9306 -h0 -e "set global log_level=debugv"; sudo bash -c 'echo "" > /var/log/manticore/searchd.log'; mysqldump -P9306 -h0 manticore t

snikolaev@dev2:~$ sudo grep LoopClientMySQL /var/log/manticore/searchd.log

shows that before show tables mysqldump sends a few very specific queries:

snikolaev@dev2:~$ sudo grep LoopClientMySQL /var/log/manticore/searchd.log
[Wed Sep 25 04:22:45.571 2024] [1825975] DEBUG: LoopClientMySQL command 3, '/*!40100 SET @@SQL_MODE='' */'
[Wed Sep 25 04:22:45.571 2024] [1825975] DEBUG: LoopClientMySQL command 3, '/*!40103 SET TIME_ZONE='+00:00' */'
[Wed Sep 25 04:22:45.571 2024] [1825975] DEBUG: LoopClientMySQL command 3, '/*!80000 SET SESSION information_schema_stats_expiry=0 */'
[Wed Sep 25 04:22:45.571 2024] [1825975] DEBUG: LoopClientMySQL command 3, 'SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400'
[Wed Sep 25 04:22:45.572 2024] [1825974] DEBUG: LoopClientMySQL command 3, 'SHOW VARIABLES LIKE 'gtid_mode''
[Wed Sep 25 04:22:45.572 2024] [1825950] DEBUG: LoopClientMySQL command 3, 'SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'ndbcluster' AND FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'ndbcluster' AND FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='manticore' AND TABLE_NAME IN ('t'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME'
[Wed Sep 25 04:22:45.576 2024] [1825949] DEBUG: LoopClientMySQL command 3, 'SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='manticore' AND TABLE_NAME IN ('t')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME'
[Wed Sep 25 04:22:45.577 2024] [1825971] DEBUG: LoopClientMySQL command 3, 'SHOW VARIABLES LIKE 'ndbinfo\_version''
[Wed Sep 25 04:22:45.578 2024] [1825946] DEBUG: LoopClientMySQL command 2
[Wed Sep 25 04:22:45.578 2024] [1825946] DEBUG: LoopClientMySQL disposing unused 9 bytes
[Wed Sep 25 04:22:45.578 2024] [1825947] DEBUG: LoopClientMySQL command 3, 'SHOW TABLES LIKE 't''

or without specifying the table (mysqldump -P9306 -h0 manticore|head -100):

snikolaev@dev2:~$ sudo grep LoopClientMySQL /var/log/manticore/searchd.log
[Wed Sep 25 04:25:21.333 2024] [1825946] DEBUG: LoopClientMySQL command 3, '/*!40100 SET @@SQL_MODE='' */'
[Wed Sep 25 04:25:21.333 2024] [1825946] DEBUG: LoopClientMySQL command 3, '/*!40103 SET TIME_ZONE='+00:00' */'
[Wed Sep 25 04:25:21.333 2024] [1825946] DEBUG: LoopClientMySQL command 3, '/*!80000 SET SESSION information_schema_stats_expiry=0 */'
[Wed Sep 25 04:25:21.333 2024] [1825946] DEBUG: LoopClientMySQL command 3, 'SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400'
[Wed Sep 25 04:25:21.334 2024] [1825947] DEBUG: LoopClientMySQL command 3, 'SHOW VARIABLES LIKE 'gtid_mode''
[Wed Sep 25 04:25:21.334 2024] [1825957] DEBUG: LoopClientMySQL command 3, 'SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'ndbcluster' AND FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'ndbcluster' AND FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('manticore'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME'
[Wed Sep 25 04:25:21.336 2024] [1825968] DEBUG: LoopClientMySQL command 3, 'SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('manticore')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME'
[Wed Sep 25 04:25:21.338 2024] [1825948] DEBUG: LoopClientMySQL command 3, 'SHOW VARIABLES LIKE 'ndbinfo\_version''
[Wed Sep 25 04:25:21.338 2024] [1825966] DEBUG: LoopClientMySQL command 2
[Wed Sep 25 04:25:21.338 2024] [1825966] DEBUG: LoopClientMySQL disposing unused 9 bytes
[Wed Sep 25 04:25:21.338 2024] [1825960] DEBUG: LoopClientMySQL command 3, 'show tables'

Can't we use one of these specific queries (e.g. ... WHERE TABLE_SCHEMA IN ('manticore'))) ...) as a sign of mysqldump to then make show tables return table names along with their cluster names?

klirichek commented 2 months ago

Seems that queries are specific to mysqldump flavour. Mine one doesn't produce couple of selects, but one huge one instead (test 272). And we anyway can't parse/execute such queries, they're too complex. Any 'light' probe with RE easy may cause false positives with unexpected side effects (namely - if just look for 'where table_name...' and too complex query - means, I can write anything with such phrase, and it will trigger the daemon). That looks too unobvious.

We can use, for example, specific username or database name as explicit and obvious mark. Such way is more flexible, since username is not hardly bind to mysqldump, and so, you can also dump your table as if it were not in a cluster also.

Best way would be just to allow plain names, without 'cluster:' prefix. Because otherwise it require further chain of changes - that is, if daemon returns prefixed names in 'show tables' - then mysqldump will use such names in all the other queries, like 'lock tables', 'show table status', and following in the trace. Some of them (like show create table) need special care, because direct replacement of table name to cluster:name is not replayable.

So, there are at least two possible solutions:

  1. Support 'cluster:name' syntax in other statements. (looks significant changes, definitely not 'small' task).
  2. Opposite, support insert/replace into table by name, despite it is part of a cluster or not. (looks as one-line change, as such requirement to write always 'cluster:name' in such case looks very artificial itself).
tomatolog commented 2 months ago

Opposite, support insert/replace into table by name, despite it is part of a cluster or not. (looks as one-line change, as such requirement to write always 'cluster:name' in such case looks very artificial itself).

That cause the user to write into cluster while node is not in the cluster, ie in case of the network issue or cluster got broken user will write into cluster think it works however cluster already gone and that data will not reach the other nodes or the data could conflicts with other nodes data or that data will be replaced by the data from the donor after node got fixed and reconnect back into cluster.

That was some kind of protection to make sure the user make sure it writes data into cluster or into local table.

klirichek commented 2 months ago

Well, that is effectively protect clustered table from being backed up by mysqldump then. To solve this, we need either to undo this 'kind of protection'. Either enrich other commands with the same 'protection'. It looks like a kind of legacy.

sanikolaev commented 2 months ago

As discussed on yesterday's call:

klirichek commented 1 month ago

List of commands which need to be fixed for mysqldump to work with manticore

LOCK TABLES `cluster:name` READ /*!32311 LOCAL */
show table status like 'cluster:name'
show create table `cluster:name`
show fields from `cluster:name`
klirichek commented 1 month ago

mysqldump -h0 -P9315 Manticore test:x -t > out.sql finally produces:

-- MySQL dump 10.13  Distrib 8.3.0, for macos14.4 (arm64)
--
-- Host: 0    Database: Manticore
-- ------------------------------------------------------
-- Server version   6.3.7 2bdc473e1@24100420 dev (knn 2.2.5 478fff2@240417)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `test:x`
--

LOCK TABLES `test:x` WRITE;
/*!40000 ALTER TABLE `test:x` DISABLE KEYS */;
INSERT INTO `test:x` VALUES ('1',1),('2',2),('3',3),('4',1),('5',2),('6',3);
/*!40000 ALTER TABLE `test:x` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-10-07 15:49:15

for reference, dump WITHOUT cluster, from command mysqldump -h0 -P9315 -utest Manticore x -t > out.sql

-- MySQL dump 10.13  Distrib 8.3.0, for macos14.4 (arm64)
--
-- Host: 0    Database: Manticore
-- ------------------------------------------------------
-- Server version   6.3.7 2bdc473e1@24100420 dev (knn 2.2.5 478fff2@240417)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `x`
--

LOCK TABLES `x` WRITE;
/*!40000 ALTER TABLE `x` DISABLE KEYS */;
INSERT INTO `x` VALUES ('1',1),('2',2),('3',3),('4',1),('5',2),('6',3);
/*!40000 ALTER TABLE `x` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-10-07 15:52:35

and dump WITHOUT cluster, from command mysqldump -h0 -P9315 -utest Manticore x > out.sql

-- MySQL dump 10.13  Distrib 8.3.0, for macos14.4 (arm64)
--
-- Host: 0    Database: Manticore
-- ------------------------------------------------------
-- Server version   6.3.7 2bdc473e1@24100420 dev (knn 2.2.5 478fff2@240417)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `x`
--

DROP TABLE IF EXISTS `x`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `x` (
`id` bigint,
`tag` bigint
) jieba_hmm='0' charset_table='non_cjk';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `x`
--

LOCK TABLES `x` WRITE;
/*!40000 ALTER TABLE `x` DISABLE KEYS */;
INSERT INTO `x` VALUES ('1',1),('2',2),('3',3),('4',1),('5',2),('6',3);
/*!40000 ALTER TABLE `x` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-10-07 15:51:56
klirichek commented 1 month ago

There are couple of things m.b. need to be added into documentation.

mysql> show variables;
+------------------------------+----------------+
| Variable_name                | Value          |
+------------------------------+----------------+
| autocommit                   | 1              |
| auto_optimize                | 1              |
| optimize_cutoff              | 24             |
| collation_connection         | libc_ci        |
| query_log_format             | sphinxql       |
| session_read_only            | 0              |
| log_level                    | debug          |
| max_allowed_packet           | 134217728      |
| character_set_client         | utf8           |
| character_set_connection     | utf8           |
| grouping_in_utc              | 0              |
| timezone                     | /etc/localtime |
| last_insert_id               |                |
| pseudo_sharding              | 1              |
| secondary_indexes            | 0              |
| accurate_aggregation         | 0              |
| distinct_precision_threshold | 3500           |
| threads_ex_effective         |                |
| cluster_user                 | cluster        |
| thread_stack                 | 1048576        |
| threads_ex                   |                |
| user                         | alexey         |
+------------------------------+----------------+
22 rows in set (0,00 sec)

Notice new values - cluster_user and user. Last one is just for reference - mysql CLI provides current user name even without notice. cluster_user is the one which will see the prefixed tables. It can be redefined for convenience to another name by

mysql -h0 -P9315 -e 'set global cluster_user=alexey'

That name is instance-wide, and doesn't require vip. Also, you don't need to relogin. Here is small demo:

mysql> show tables;
+----------------+-------------+
| Index          | Type        |
+----------------+-------------+
| dist_table     | distributed |
| dname          | distributed |
| index2001      | rt          |
| index2002      | rt          |
| index2003      | rt          |
| index2004      | rt          |
| index201       | rt          |
| index202       | rt          |
| index203       | rt          |
| index204       | rt          |
| mcl_only_json  | rt          |
| name           | rt          |
| no_mcl_json    | rt          |
| no_mcl_multi   | rt          |
| no_mcl_multi64 | rt          |
| oned           | distributed |
| products       | percolate   |
| rr             | rt          |
| t              | rt          |
| t1             | rt          |
| test           | rt          |
| test1          | rt          |
| test_template  | rt          |
| tt             | rt          |
| x              | rt          |
+----------------+-------------+
25 rows in set (0,00 sec)

mysql> set global cluster_user=alexey;
Query OK, 0 rows affected (0,00 sec)

mysql> show tables;
+----------------+-------------+
| Index          | Type        |
+----------------+-------------+
| dist_table     | distributed |
| dname          | distributed |
| index2001      | rt          |
| index2002      | rt          |
| index2003      | rt          |
| index2004      | rt          |
| index201       | rt          |
| index202       | rt          |
| index203       | rt          |
| index204       | rt          |
| mcl_only_json  | rt          |
| test:name      | rt          |
| no_mcl_json    | rt          |
| no_mcl_multi   | rt          |
| no_mcl_multi64 | rt          |
| oned           | distributed |
| products       | percolate   |
| rr             | rt          |
| t              | rt          |
| t1             | rt          |
| test           | rt          |
| test1          | rt          |
| test_template  | rt          |
| tt             | rt          |
| test:x         | rt          |
+----------------+-------------+
25 rows in set (0,00 sec)
sanikolaev commented 1 month ago

Updated docs in https://github.com/manticoresoftware/manticoresearch/commit/05247a0e6a68107903b6188a6d29ce5008d5371e

sanikolaev commented 1 month ago

@PavelShilin89 pls update the mysqldump CLT test, so it tests dump/restore in replication mode too. See details in the docs - https://manual.manticoresearch.com/dev/Securing_and_compacting_a_table/Backup_and_restore#Backup-and-restore-with-mysqldump