mysqldump skip comments

Question

I do database dump with mysqldump command. Command that I use:

$mysqldump -uuser -ppassword --host=1.1.1.1 db_name
--add-drop-database --add-drop-table --skip-compact --no-data
--skip-comments --skip-set-charset --skip-dump-date --skip-tz-utc --tab=path

and get result:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `action`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action` (
  `id` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

But I need this dump:

DROP TABLE IF EXISTS `action`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action` (
  `id` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

how to remove those comments:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
...
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

They aren't useless comments - dup http://stackoverflow.com/questions/1916392/how-can-i-get-rid-of-these-comments-in-a-mysql-dump — Dmitry Sadakov, Feb 26 '16 at 16:28
I've already read this question and answers. But, anyway I must remove those comments — Anna Adamchuk, Feb 26 '16 at 16:36
possible duplicate: http://stackoverflow.com/questions/1916392/how-can-i-get-rid-of-these-comments-in-a-mysql-dump — MaxU - stand with Ukraine, Feb 26 '16 at 17:22

score 1 · Accepted Answer · answered Feb 26 '16 at 19:07

1

You can use a post-process Regex to remove the lines since mysqldump does not support removing those comments:

(.*OLD_SQ.*)

with global modifier g.

answered Feb 26 '16 at 19:07

Dmitry Sadakov

2,128
3
19
34

score 1 · Answer 2 · answered Sep 07 '17 at 23:08

1

For the record, the --compact option will remove these comments. You could have used this command:

mysqldump -uuser -ppassword --host=1.1.1.1 db_name --compact --add-drop-table --no-data

Complete sample output:

DROP TABLE IF EXISTS `tester`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tester` (
  `col1` varchar(16) DEFAULT NULL,
  `col2` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

answered Sep 07 '17 at 23:08

miken32

42,008
16
111
154

Any argument to remove the remaining ones? – Daniele Testa Aug 17 '21 at 09:28
`mysqldump --help` – miken32 Aug 17 '21 at 16:27
Pointless comment. None of the options there removes the last comments, hence my question. – Daniele Testa Aug 17 '21 at 19:03
If you already know none of the options can do it, why ask the question? – miken32 Aug 17 '21 at 21:22
Probably pipe it through grep like `mysqldump ... | grep -v character_set_client` or something would be safe if it doesn't collide with a name in your database. – miken32 Aug 17 '21 at 21:23

mysqldump skip comments

2 Answers2