MySQL

MySQL and MariaDB are popular open-source relational database systems. DataMeans supports SQL dump file extraction for schema and data migration.

What You Can Upload

• .sql dump files from mysqldump
• ZIP archives containing .sql or .dump dump files, in any folder
• Several dump files in one ZIP: the CREATE TABLE statements in one file and the rows in another, or one file per table. Every file is read into one set of tables, so a data file that sorts before the schema file is fine
• A script that loads other files with the mysql client's source command, such as the Employees sample database's employees.sql, which creates its tables and then runs source load_departments.dump ;, source load_employees.dump ; and so on. Put the script and the files it loads in the same ZIP

What You Get Out

DataMeans converts your data into multiple modern formats:

How to Export / Obtain Files

Export with mysqldump:

mysqldump -u username -p database_name > dump.sql

With schema and data:

mysqldump -u username -p --databases database_name > dump.sql

For all databases:

mysqldump -u username -p --all-databases > dump.sql

Supported Features

• CREATE TABLE schema extraction (columns and types)
• INSERT INTO data extraction, including multi-row inserts, with no limit on rows per table
• Every dump file in the ZIP read together: the tables and keys of all the files first, then the rows, so a table's rows can be in a different file from its CREATE TABLE. When several files give rows to one table, a script's files go in the order the script loads them, straight after the script, and files that no script loads go in the order of their file names
• source (or \.) lines in a script: a file that is in the ZIP is read where the script loads it, once, and a line that names a file outside the ZIP is never followed. The script is read, not run: only its CREATE TABLE, ALTER TABLE, INSERT and REPLACE statements and its source lines count
• A name written as a path on your own machine (source /home/me/load.dump;, source C:\data\load.dump;) is matched to the dump file of that name in the ZIP, wherever it sits in the ZIP, and is not reported as missing
• A file a script loads that is not in the ZIP is listed in a note on the preview and in the job report, and the rows only that file holds are missing. For a script uploaded on its own the note says "This file runs ...", and tells you to add the files it loads to the same ZIP
• If two files in the ZIP both create the same table (two dated backups, say), the rows of both are added together and the preview names the files; upload just one if they are copies of the same data. When the two files give the table different columns, the note says so: the rows of the earlier file are placed by the columns of the last file and may sit in the wrong columns
• Dumps made with --routines or --triggers: each procedure and trigger is skipped whole, and the tables around them are read
• Character sets from the dump's SET NAMES, read byte for byte
• Types carried into each SQL dialect
• Each table's primary key, over one column or several, becomes its primary key in the SQL files, wherever the data satisfies it
• FOREIGN KEY constraints in the dump, in CREATE TABLE or in a later ALTER TABLE as phpMyAdmin writes them, become foreign keys in the SQL files, wherever the data satisfies them; the SQL file lists any it leaves out, and why
• Other relationships between tables are suggested from the data; the ones you confirm on the job page become foreign keys too

Known Limitations

• Raw MySQL data directories (.frm, .MYD, .MYI, .ibd files) are not supported
• Live database connections are not supported (use mysqldump)
• Indexes other than the primary key are not carried over, nor foreign keys over more than one column
• Views, triggers and stored procedures are not converted
• Other statements in a dump or script (SELECT, USE, SET, DROP, CREATE DATABASE) are skipped
• A script is followed only so far: a very long list of different source names, or scripts that run scripts many levels deep, are cut off, and the preview names what was left out
• Zero dates (0000-00-00) become empty values, with a note in the report

Troubleshooting

Note on the Excel formats. An .xlsx sheet holds at most 1,048,575 rows of data and 16,384 columns, and a legacy .xls sheet 65,535 rows and 256 columns. Rather than ship a file that looks complete but is silently cut short, DataMeans leaves a table out of any Excel format it does not fit and notes it in the job report. The .xlsx copy is also left out for tables of more than 8,000,000 cells (1,048,575 rows of 12 columns is 12.6 million), which take too long and too much memory to write, and the .xls copy for tables of more than 150,000 cells, which the legacy format takes far too long to write. The CSV, JSON and SQL outputs always contain every row.

Overview

MySQL is an open-source relational database management system developed by Oracle Corporation, featuring multiple storage engines for different use cases. It uses a client-server architecture with SQL as the query language, supporting ACID transactions, replication, and high availability. MySQL stores data in various file formats depending on the storage engine, with InnoDB as the default for transactional workloads and MyISAM available for read-heavy, non-transactional workloads.

History and Background

• 1995: First version of MySQL developed by Michael Widenius and David Axmark; MySQL AB founded in Sweden with Allan Larsson.
• 1996: MySQL 3.11.1 released to the public, initially for Solaris and soon after for Linux.
• 2000: MySQL source code released under the GNU General Public License.
• 2001: MySQL 3.23 reaches general availability; the InnoDB storage engine, bundled from 3.23.34a, adds transactions.
• 2003: MySQL 4.0 adds UNION queries and multi-table DELETE.
• 2004: MySQL 4.1 adds subqueries and prepared statements.
• 2005: MySQL 5.0 introduces stored procedures, triggers, and views.
• 2008: Sun Microsystems acquires MySQL AB for approximately $1 billion.
• 2010: Oracle acquires Sun Microsystems, gains MySQL ownership.
• 2010: MySQL 5.5 makes InnoDB the default storage engine.
• 2015: MySQL 5.7 adds a native JSON data type and generated columns.
• 2018: MySQL 8.0 introduces window functions and common table expressions.
• 2023: MySQL 8.1 released as the first Innovation release under the new release model.
• 2024: MySQL 8.4 released as the first long-term support (LTS) version, followed by MySQL 9.0.

File Format Specifications

MySQL uses different file formats based on the storage engine selected.

InnoDB Files:

• .ibd: Tablespace files containing data and indexes
• ibdata1: System tablespace for InnoDB metadata
• .frm: Table definition files (removed in 8.0)
• ibtmp1: Temporary tablespace for temporary data
• undo logs: Transaction rollback segments

MyISAM Files:

• .MYD: Data files containing table rows
• .MYI: Index files containing B-tree indexes
• .frm: Table definition files (replaced by the data dictionary in 8.0)

File Structure:

• Tablespaces: Logical containers for database objects
• Pages: Fixed-size blocks (16 KB default for InnoDB)
• Extents: Groups of pages for allocation efficiency
• Segments: Collections of extents for tables and indexes
• Redo logs: Transaction durability through WAL

Key Components:

• Storage Engines: Pluggable architecture for different access methods
• Buffer Pool: In-memory cache for frequently accessed data
• Query Cache: Result caching for identical queries (removed in 8.0)
• Replication: Asynchronous source-replica (master-slave) topologies
• Partitioning: Horizontal data distribution across files

Data Types and Structures

MySQL supports comprehensive data types across different categories:

Database Structure:

• Databases contain tables, views, stored procedures, triggers
• Tables use storage engines with different capabilities
• Indexes support B-tree, hash, full-text, spatial types
• Foreign key constraints supported in InnoDB
• Partitioning available for large tables

Version Differences

Compatibility Notes:

• Major version upgrades may require dump/restore
• Storage engine changes affect file compatibility
• Client libraries version-dependent
• Oracle MySQL differs from community forks (MariaDB, Percona)

Technical References

• MySQL Documentation
• Wikipedia: MySQL
• InnoDB Architecture
• MyISAM Storage Engine
• MySQL Data Types