Generate insert statements of data from tables in MariaDB / MySQL

Following statements will only create insert statements belonging to the data it will not include the created statements. For both we need to remove –no-create-info from command, we show an example at last.

Example: skip locks statements during insert statements –skip-add-locks use for skip locking on tables.

mysqldump --no-create-info --skip-add-locks -u root -p database_name > location_of_file

C:\WINDOWS\system32>mysqldump  --no-create-info  --skip-add-locks -u root -p  test > C:\d\testdata.txt
Enter password: ********

Output:
-- MariaDB dump 10.19  Distrib 10.6.5-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	10.6.5-MariaDB
--
-- Dumping data for table `c1`
--

/*!40000 ALTER TABLE `c1` DISABLE KEYS */;
INSERT INTO `c1` VALUES (2,1),(1,2),(3,5);
/*!40000 ALTER TABLE `c1` ENABLE KEYS */;

Example: Don’t skip locks statements during insert statements:

mysqldump --no-create-info -u root -p database_name > location_of_file

C:\WINDOWS\system32>mysqldump  --no-create-info -u root -p  test > C:\d\testdata.txt
Enter password: ********

Output:
-- MariaDB dump 10.19  Distrib 10.6.5-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	10.6.5-MariaDB

-- Dumping data for table `c1`
--

LOCK TABLES `c1` WRITE;
/*!40000 ALTER TABLE `c1` DISABLE KEYS */;
INSERT INTO `c1` VALUES (2,1),(1,2),(3,5);
/*!40000 ALTER TABLE `c1` ENABLE KEYS */;
UNLOCK TABLES;

Example like a full backup of MySQL or MariaDB:

mysqldump -u root -p database_name > location_of_file

C:\WINDOWS\system32>mysqldump  -u root -p  test > C:\d\testdata.txt
Enter password: ********

Output:
-- MariaDB dump 10.19  Distrib 10.6.5-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	10.6.5-MariaDB

--
-- Table structure for table `c1`
--

DROP TABLE IF EXISTS `c1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `c1` (
  `cid` int(11) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`),
  KEY `pid` (`pid`),
  CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `c1`
--

LOCK TABLES `c1` WRITE;
/*!40000 ALTER TABLE `c1` DISABLE KEYS */;
INSERT INTO `c1` VALUES (2,1),(1,2),(3,5);
/*!40000 ALTER TABLE `c1` ENABLE KEYS */;
UNLOCK TABLES;