Converting a mysqldump sql file

General
#1

hello -

is there any way to quickly convert a mysqldump sql file to be interpreted for cockroach sql?

also, i am not sure if this question was properly posted or not.

#2

Hi @edwardsmarkf,

What format is the mysqldump file? If it is a CSV or in some other tabular format, you should be able to easily import it into CockroachDB using IMPORT: https://www.cockroachlabs.com/docs/stable/import.html.

– Becca

#3

hi - mysqldump has LOTS of extra stuff in it that is specific to mariadb.

rather than use an editor on all these mariadb mysqldump sql files, i was wondering if cockroachdb has some sort of magic filter to convert them.

if this seems like a dumb question, its because cockroachdb has so many totally awesome features that i am spoiled rotten. its as if i just imagine something and cockroachdb does it. so i am a victim of your success. (humorous Monday AM note :wink:

#4

Glad to hear we are spoiling you :slight_smile:

I’m not familiar with the mysqldump format of MariaDB, so please correct me if this answer doesn’t make sense:

If the file is in a tabular format (i.e., the file contains text with fields separated by a comma or some other delimiter), IMPORT should work for you. After you import the file, you should be able to drop columns from the imported table, or create a new table using CREATE TABLE AS SELECT ... FROM <imported table> WHERE ....

Does this help?

#5

hi becca -

below is a small example of what “mysqldump” produces (this is from a wordpress db). with this file, i can easily “recreate” a database with a simple mariadb sql command such as:

mysql --username=mark --password=iWantToSwitchToCockroach \
beccaDatabase < mysqldumpstuff.sql ;

mysqldump file below:

– MySQL dump 10.16 Distrib 10.2.14-MariaDB, for Linux (x86_64)
–
– Host: localhost Database: compTrainers1216
– ------------------------------------------------------
– Server version 10.2.14-MariaDB

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 `wp_100_commentmeta`
--

DROP TABLE IF EXISTS `wp_100_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_100_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext DEFAULT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=72 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wp_100_commentmeta`
--

LOCK TABLES `wp_100_commentmeta` WRITE;
/*!40000 ALTER TABLE `wp_100_commentmeta` DISABLE KEYS */;
/*!40000 ALTER TABLE `wp_100_commentmeta` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `wp_100_comments`
--

DROP TABLE IF EXISTS `wp_100_comments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
#6

Hi Mark,

As is, this file won’t work with CockroachDB. But looking at the mysqldump documentation for MariaDB, it looks like you can specify that the output should be compatible with PostgreSQL using --compatible=postgresql: https://mariadb.com/kb/en/library/mysqldump/. CockroachDB should be able to import any files that are compatible with PostgreSQL. Are you able to rerun the mysqldump command?

– Becca

#7

sure i will try it.

maybe a future article “Converting from mariadb to cockroach” ? i should think this sort of thing will be very useful to others.

#8

Great - let me know how it goes. Good idea about the article on converting from MariaDB to CockroachDB. I’ll suggest it to the team :slight_smile: