Scripting the setting up of a MySQL database - best practices? I am a relative newbie to database administration. I am setting up my first database on my localhost in MySQL and want to understand best practices. This is my current workflow:

# do_everything.sql

SELECT 'CREATING DATABASE STRUCTURE' info;

DROP DATABASE IF EXISTS my_first_db;
CREATE DATABASE IF NOT EXISTS my_first_db;
USE my_first_db;

SET default_storage_engine = innodb;
SELECT CONCAT('storage engine: ', @@default_storage_engine) info;

SELECT 'CREATING TABLE A' info;
-- Code to create table from source_data_A.csv

SELECT 'CREATING LOGGER FOR TABLE A' info;
-- Code to create log table for table A
-- Code to create insert, update, &amp; delete triggers for table A

SELECT 'LOADING SOURCE DATA A' info;
-- Code to load source_data_A.csv into table A

SELECT 'CREATING TABLE B' info;
-- Code to create table from source_data_B.csv

SELECT 'CREATING LOGGER FOR TABLE B' info;
-- Code to create log table for table B
-- Code to create insert, update, &amp; delete triggers for table B

SELECT 'LOADING SOURCE DATA B' info;
-- Code to load source_data_B.csv into table B

SELECT 'DATABASE CREATION COMPLETE' info;


From my Terminal, I then run:

mysql --local-infile=1 -uroot -p &lt; /absolute/path/to/do_everything.sql



Should I be splitting up do_everything.sql into different .sql files according to logical chunks? If so, would I then have a single master shell script that executes all of them? 
If I keep the same workflow, am I following best practices?
Should I be splitting up do_everything.sql into different .sql files according to logical chunks? If so, would I then have a single master shell script that executes all of them?


IMHO, yes, you should split up your do_everything.sql script into smaller logical chunks - modularisation is one of the key tools that we software engineers have at our disposal to help organise our work, and this will lead to fewer bugs and fewer mistakes. You can test module by module rather than having to always deal with a behemoth (or "juggernaut" as someone from the Indian subcontinent might say! :-) ).


If I keep the same workflow, am I following best practices?


See my remarks about modularisation above - I believe that it would be considered best practice to break your script up into logical parts - I used to always do it table by table - that way it was easy to see where any problems arose, and the "sub-scripts" could be either tested one by one or the master script could be checked.

You asked in comments:


Can I modularize the scripts and run them from a single "master" .sql file? If so, having trouble looking up the command to do so.


It's very simple - you simply use the SOURCEkeyword (i.e. SOURCE /path/to/file.sql) in your scripts - you can nest this to many levels (but not too many).  

What I typically did was have a master_script.sql which was really just a "skeleton" - it was just filled with SOURCE statements calling other scripts. Some of those might themselves have had SOURCE statements (within reason). I found this worked very well.