mysqldump: keep cluster name #2249

sanikolaev · 2024-05-29T03:53:22Z

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.}

The text was updated successfully, but these errors were encountered:

klirichek · 2024-09-24T12:14:06Z

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 · 2024-09-25T04:32:33Z

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 · 2024-09-25T05:37:47Z

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:

Support 'cluster:name' syntax in other statements. (looks significant changes, definitely not 'small' task).
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 · 2024-09-25T07:25:53Z

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 · 2024-09-25T08:06:06Z

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 · 2024-09-27T09:39:09Z

As discussed on yesterday's call:

It may not be easy to remove the cluster:table protection. Please create a separate issue for this.
For now, let's improve the integration with mysqldump. It's fine to change the requirement so that it requires mysqldump -u mysqldump, allowing the daemon to behave differently based on the username.

klirichek · 2024-10-03T09:19:50Z

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 · 2024-10-07T08:51:20Z

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

That is not just cluster:table, but `cluster:table`. For mysqldump-specific commands user should match cluster_user (yes, this is not normal-flavour, this is specific hack). For insert/replace user is not checked (so, replaying dumps doesn't require specific user). That is related to #2249

klirichek · 2024-10-11T10:05:40Z

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)

Related issue #2249

sanikolaev · 2024-10-16T11:13:50Z

Updated docs in 05247a0

sanikolaev · 2024-10-16T11:16:57Z

@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

PavelShilin89 · 2024-12-05T02:05:03Z

@sanikolaev Test updated in PR - #2816.
Review and approval to merge with a master is required.

sanikolaev · 2024-12-10T05:25:38Z

I'm reopening this issue since it's not working (tested on dev2 and in macos). Here's a one-liner which tests it:

➜  ~ mysql -P9306 -h0 -e "delete cluster c"; mysql -P9306 -h0 -f -v -e "create cluster c; drop table if exists t; create table t(f text); alter cluster c add t; insert into c:t values(1, 'abc'); select * from c:t"; mysqldump -P9306 -h0 -ucluster manticore
--------------
create cluster c
--------------

--------------
drop table if exists t
--------------

--------------
create table t(f text)
--------------

--------------
alter cluster c add t
--------------

--------------
insert into c:t values(1, 'abc')
--------------

--------------
select * from c:t
--------------

+------+------+
| id   | f    |
+------+------+
|    1 | abc  |
+------+------+
-- MySQL dump 10.13  Distrib 9.0.1, for macos13.7 (arm64)
--
-- Warning: version string returned by server is incorrect.
-- Host: 0    Database: manticore
-- ------------------------------------------------------
-- Server version	6.3.9

/*!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 */;
-- Warning: column statistics not supported by the server.
mysqldump: Couldn't execute 'show create table `c:t`': You have an error in your query. Please, double-check it. (1064)

dev2:

snikolaev@dev2:~$ mysql -P9306 -h0 -e "delete cluster c"; mysql -P9306 -h0 -f -v -e "create cluster c; drop table if exists t; create table t(f text); alter cluster c add t; insert into c:t values(1, 'abc'); select * from c:t"; mysqldump -P9306 -h0 -ucluster manticore
--------------
create cluster c
--------------

--------------
drop table if exists t
--------------

--------------
create table t(f text)
--------------

--------------
alter cluster c add t
--------------

--------------
insert into c:t values(1, 'abc')
--------------

--------------
select * from c:t
--------------

+------+------+
| id   | f    |
+------+------+
|    1 | abc  |
+------+------+
-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Warning: version string returned by server is incorrect.
-- Host: 0    Database: manticore
-- ------------------------------------------------------
-- Server version	6.3.9

/*!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 */;
-- Warning: column statistics not supported by the server.
mysqldump: Couldn't execute 'show create table `c:t`': You have an error in your query. Please, double-check it. (1064)

Expected: mysqldump doesn't fail.

sanikolaev · 2024-12-10T05:34:24Z

It's turned out this functionality comes with 2 limitations:

flag -t
you can't dump a specific table, only the whole instance

The updated test is:

snikolaev@dev2:~$ mysql -P9306 -h0 -e "delete cluster c"; mysql -P9306 -h0 -f -v -e "create cluster c; drop table if exists t; create table t(f text); alter cluster c add t; insert into c:t values(1, 'abc')"; mysqldump -P9306 -h0 -etc --replace -ucluster manticore
--------------
create cluster c
--------------

--------------
drop table if exists t
--------------

--------------
create table t(f text)
--------------

--------------
alter cluster c add t
--------------

--------------
insert into c:t values(1, 'abc')
--------------

-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Warning: version string returned by server is incorrect.
-- Host: 0    Database: manticore
-- ------------------------------------------------------
-- Server version	6.3.9

/*!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 */;
-- Warning: column statistics not supported by the server.

--
-- Dumping data for table `c:t`
--

LOCK TABLES `c:t` WRITE;
/*!40000 ALTER TABLE `c:t` DISABLE KEYS */;
REPLACE INTO `c:t` (`id`, `f`) VALUES ('1','abc');
/*!40000 ALTER TABLE `c:t` 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-12-10  5:34:05

sanikolaev · 2024-12-10T05:46:55Z

It's turned out

you can't dump a specific table, only the whole instance

is not the requirement. Vice versa, it's recommended to dump a specific table, but you need to use the c:t syntax. The updated test is:

snikolaev@dev2:~$ mysql -P9306 -h0 -e "delete cluster c"; mysql -P9306 -h0 -f -v -e "create cluster c; drop table if exists t; create table t(f text); alter cluster c add t; insert into c:t values(1, 'abc')"; mysqldump -P9306 -h0 -etc --replace -ucluster manticore c:t
--------------
create cluster c
--------------

--------------
drop table if exists t
--------------

--------------
create table t(f text)
--------------

--------------
alter cluster c add t
--------------

--------------
insert into c:t values(1, 'abc')
--------------

-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Warning: version string returned by server is incorrect.
-- Host: 0    Database: manticore
-- ------------------------------------------------------
-- Server version	6.3.9

/*!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 */;
-- Warning: column statistics not supported by the server.

--
-- Dumping data for table `c:t`
--

LOCK TABLES `c:t` WRITE;
/*!40000 ALTER TABLE `c:t` DISABLE KEYS */;
REPLACE INTO `c:t` (`id`, `f`) VALUES ('1','abc');
/*!40000 ALTER TABLE `c:t` 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-12-10  5:45:20

Related issue #2249

sanikolaev · 2024-12-10T06:15:59Z

@PavelShilin89 I've updated the docs. Pls continue with preparing a test for this issue. In your PR you don't specify -u cluster which is wrong.

sanikolaev assigned klirichek May 29, 2024

klirichek linked a pull request Oct 11, 2024 that will close this issue

Mysqldump hacks #2640

Merged

klirichek closed this as completed in #2640 Oct 14, 2024

klirichek reopened this Oct 14, 2024

sanikolaev added a commit that referenced this issue Oct 16, 2024

manual: about replication mode for mysqldump

05247a0

Related issue #2249

sanikolaev assigned PavelShilin89 and unassigned klirichek Oct 16, 2024

sanikolaev mentioned this issue Oct 17, 2024

Regression: restoring from a MariaDB dump fails in the new version #2662

Closed

5 tasks

PavelShilin89 linked a pull request Dec 5, 2024 that will close this issue

Test mysqldump with replication #2816

Open

PavelShilin89 linked a pull request Dec 8, 2024 that will close this issue

Test mysqldump with replication #2816

Open

PavelShilin89 removed a link to a pull request Dec 8, 2024

Mysqldump hacks #2640

Merged

sanikolaev added a commit that referenced this issue Dec 10, 2024

manual: changes related with mysqldump vs replicated tables

ffb8445

Related issue #2249

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mysqldump: keep cluster name #2249

mysqldump: keep cluster name #2249

sanikolaev commented May 29, 2024 •

edited

Loading

klirichek commented Sep 24, 2024

sanikolaev commented Sep 25, 2024

klirichek commented Sep 25, 2024

tomatolog commented Sep 25, 2024

klirichek commented Sep 25, 2024

sanikolaev commented Sep 27, 2024

klirichek commented Oct 3, 2024

klirichek commented Oct 7, 2024 •

edited

Loading

klirichek commented Oct 11, 2024

sanikolaev commented Oct 16, 2024

sanikolaev commented Oct 16, 2024

PavelShilin89 commented Dec 5, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented Dec 10, 2024

mysqldump: keep cluster name #2249

mysqldump: keep cluster name #2249

Comments

sanikolaev commented May 29, 2024 • edited Loading

Proposal:

Checklist:

klirichek commented Sep 24, 2024

sanikolaev commented Sep 25, 2024

klirichek commented Sep 25, 2024

tomatolog commented Sep 25, 2024

klirichek commented Sep 25, 2024

sanikolaev commented Sep 27, 2024

klirichek commented Oct 3, 2024

klirichek commented Oct 7, 2024 • edited Loading

klirichek commented Oct 11, 2024

sanikolaev commented Oct 16, 2024

sanikolaev commented Oct 16, 2024

PavelShilin89 commented Dec 5, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented Dec 10, 2024

sanikolaev commented May 29, 2024 •

edited

Loading

klirichek commented Oct 7, 2024 •

edited

Loading