We defined the Fabric environment settings and a few functions for administering MySQL remotely. In these functions, instead of calling the mysql executable directly, we send the SQL commands to mysql via echo. This ensures that arguments are passed properly to the mysql executable.

Listing 6.5 gives the code for running MySQL commands remotely, as follows:

#!/usr/bin/env python 
# Python Network Programming Cookbook, Second Edition -- Chapter - 6 
# This program is optimized for Python 2.7.12 and Python 3.5.2. 
# It may run on any other version with/without modifications. 
 
from getpass import getpass  
from fabric.api import run, env, prompt, cd 
  
def remote_server(): 
    env.hosts = ['127.0.0.1'] 
    env.user = prompt('Enter your system username: ') 
    env.password = getpass('Enter your system user password: ') 
    env.mysqlhost = 'localhost' 
    env.mysqluser = prompt('Enter your db username: ') 
    env.mysqlpassword = getpass('Enter your db user password: ') 
    env.db_name = '' 
 
def show_dbs(): 
    """ Wraps mysql show databases cmd""" 
    q = "show databases" 
    run("echo '%s' | mysql -u%s -p%s" %(q, env.mysqluser,
          env.mysqlpassword)) 
 
 
def run_sql(db_name, query): 
    """ Generic function to run sql""" 
    with cd('/tmp'): 
        run("echo '%s' | mysql -u%s -p%s -D %s" %(query,
             env.mysqluser, env.mysqlpassword, db_name)) 
 
def create_db(): 
    """Create a MySQL DB for App version""" 
    if not env.db_name: 
        db_name = prompt("Enter the DB name:") 
    else: 
        db_name = env.db_name 
    run('echo "CREATE DATABASE %s default character set 
         utf8 collate utf8_unicode_ci;"|mysql 
         --batch --user=%s --password=%s --host=%s' 
         % (db_name, env.mysqluser, env.mysqlpassword,
          env.mysqlhost), pty=True) 
 
def ls_db(): 
    """ List a dbs with size in MB """ 
    if not env.db_name: 
        db_name = prompt("Which DB to ls?") 
    else: 
        db_name = env.db_name 
    query = """SELECT table_schema
                               "DB Name",  
       Round(Sum(data_length + index_length) / 1024 / 1024, 1)
        "DB Size in MB"  
        FROM   information_schema.tables          
        WHERE table_schema = "%s"  
        GROUP  BY table_schema """ %db_name 
    run_sql(db_name, query) 
 
 
def empty_db(): 
    """ Empty all tables of a given DB """ 
    db_name = prompt("Enter DB name to empty:") 
    cmd = """ 
    (echo 'SET foreign_key_checks = 0;';  
    (mysqldump -u%s -p%s --add-drop-table --no-data %s |  
     grep ^DROP);  
     echo 'SET foreign_key_checks = 1;') |  
     mysql -u%s -p%s -b %s 
    """ %(env.mysqluser, env.mysqlpassword, db_name,
          env.mysqluser, env.mysqlpassword, db_name) 
    run(cmd) 
 

In order to run this script, you should create a shortcut, fabfile.py. From the command line, you can do this by typing the following command:

$ ln -sfn 6_5_run_mysql_command_remotely.py fabfile.py  

Then, you can call the fab executable in various forms.

The following command will show a list of databases (using the SQL query, show databases):

$ fab remote_server show_dbs 

The following command will create a new MySQL database. If you haven't defined the Fabric environment variable, db_name, a prompt will be shown to enter the target database name. This database will be created using the SQL command, CREATE DATABASE <database_name> default character set utf8 collate utf8_unicode_ci;:

$ fab remote_server create_db  

This Fabric command will show the size of a database:

$ fab remote_server ls_db()  

The following Fabric command will use the mysqldump and mysql executables to empty a database. This behavior of this function is similar to the truncating of a database, except it removes all the tables. The result is as if you created a fresh database without any tables:

$ fab remote_server empty_db() 

The following will be the output:

$ fab remote_server show_dbs
[127.0.0.1] Executing task 'show_dbs'
[127.0.0.1] run: echo 'show databases' | mysql -uroot -p<DELETED>
[127.0.0.1] out: Database
[127.0.0.1] out: information_schema
[127.0.0.1] out: mysql
[127.0.0.1] out: phpmyadmin
[127.0.0.1] out: 
        
Done.
Disconnecting from 127.0.0.1... done.
        
$ fab remote_server create_db
[127.0.0.1] Executing task 'create_db'
Enter the DB name: test123
[127.0.0.1] run: echo "CREATE DATABASE test123 default character set utf8 collate utf8_unicode_ci;"|mysql --batch --user=root --password=<DELETED> --host=localhost
    
Done.
Disconnecting from 127.0.0.1... done.
$ fab remote_server show_dbs
[127.0.0.1] Executing task 'show_dbs'
[127.0.0.1] run: echo 'show databases' | mysql -uroot -p<DELETED>
[127.0.0.1] out: Database
[127.0.0.1] out: information_schema
[127.0.0.1] out: collabtive
[127.0.0.1] out: test123
[127.0.0.1] out: testdb
[127.0.0.1] out: 
    
Done.
Disconnecting from 127.0.0.1... done.