Saturday, January 10, 2015

Essential Admin Tasks in MySQL

Exporting DDL


Within MySQL Workbench, go to
Server > Data Export
and select the schema of interest:



I generally prefer to select the checkbox which states "Skip Table Data (no-data)".

 This will dump the DDL only to a series of *.sql files:




Importing DDL


Open MySQL Workbench, and go to Data Import/Restore:




Importing DDL (terminal session)


Assumption: I have a schema named soundex, and a username/password with identical values.

This simple python script creates a table for each alpha character:
import string

f = open('tables.ddl','w')

f.write("use soundex;")
for letter in string.ascii_lowercase:  
 f.write("CREATE TABLE tb" + letter + " ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );\n")
f.close()

I run this script using
$ python generate-ddl.py

And the output looks like this:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE tba ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbb ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbc ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbd ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbe ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbf ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbg ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbh ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbi ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbj ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbk ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbl ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbm ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbn ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbo ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbp ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbq ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbr ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbs ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbt ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbu ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbv ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbw ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbx ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tby ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );
CREATE TABLE tbz ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );

And I can import this into MySQL using this command:
mysql -u soundex -psoundex -hlocalhost < tables.ddl

Within MySQL I can verify that this worked successfully:
$ mysql -u soundex -psoundex -hlocalhost 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 83
Server version: 5.5.41-0ubuntu0.14.10.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use soundex;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_soundex |
+-------------------+
| tba               |
| tbb               |
| tbc               |
| tbd               |
| tbe               |
| tbf               |
| tbg               |
| tbh               |
| tbi               |
| tbj               |
| tbk               |
| tbl               |
| tbm               |
| tbn               |
| tbo               |
| tbp               |
| tbq               |
| tbr               |
| tbs               |
| tbt               |
| tbu               |
| tbv               |
| tbw               |
| tbx               |
| tby               |
| tbz               |
+-------------------+
26 rows in set (0.00 sec)

mysql> describe tba;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | smallint(6)  | NO   | PRI | NULL    | auto_increment |
| k     | varchar(256) | NO   |     | NULL    |                |
| v     | varchar(256) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> 

No comments:

Post a Comment