MariaDB and MySQL DDL example export or extraction

Introduction

This page explains how to export DDL from a MariaDB and MySQL database for use with the SQL Analyzer extension.

Add a specific export user 

Ask someone with sufficient privileges to add this user for you. You should only create this user if you are exporting the source code yourself and you don’t have access to a root user account.

/* add export / extraction user */
CREATE USER <YOUR USER> IDENTIFIED BY 'YOUR USER PASSWORD';
 
/*give him privileges to export routines */
GRANT SELECT ON mysql.proc to 'YOUR_USER';
 
/* and all other objects*/
GRANT SHOW VIEW, SELECT, EVENT, INDEX, TRIGGER, LOCK TABLES ON `<DATABASE YOU SHOULD EXTRACT>`.* TO '<YOUR USER>'@'%';

Extraction example - Windows

  • Open a Command Prompt with “Run as Administrator” rights.

  • Switch to the MariaDB / MySQL bin folder. For example MariaDB: 

cd "C:\Program Files\MariaDB 10.1\bin"
  • Export a specific database:
REM mysqldump.exe -d -u <YOUR USER> -p<YOUR USER PASSWORD> -h <host name> -E -R > "<Your SQL file>"

mysqldump.exe -d -u toto -ptoto -h localhost castpubs -E -R > "c:\temp\example_toto.sql"

The parameter -d, -no-data means do not dump table contents.

The parameter -E means it will export events and -R routines.

Official documentation

Have a look at the official documentation for more details/parameters and examples: