If you ever need to administer a MySQL server remotely, this recipe is for you. It will show you how to send database commands to a remote MySQL server from a Python script. If you need to set up a web application that relies on a backend database, this recipe can be used as a part of your web application setup process.
This recipe also needs Fabric to be installed first. You can install Fabric using the Python packing tools, pip or easy_install, as shown in the following command. Fabric relies on the paramiko module, which will be installed automatically.
$ pip install fabric
Here, we will connect the remote host using the SSH protocol. So, it's necessary to run the SSH server on the remote end. You also need to run a MySQL server on the remote host. On a Debian/Ubuntu machine, this can be done with the package manager, apt-get, as shown in the following command:
$ sudo apt-get install openssh-server mysql-server
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 7.5 gives the code for running MySQL commands remotely, as follows:
#!/usr/bin/env python
# Python Network Programming Cookbook -- Chapter - 7
# This program is optimized for Python 2.7.
# 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']
# Edit this list to include remote hosts
env.user =prompt('Enter your system username: ')
env.password = getpass('Enter your system user password: ')
env.mysqlhost = 'localhost'
env.mysqluser = 'root'prompt('Enter your db username: ')
env.password = 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 7_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.
This script defines a few functions that are used with Fabric. The first function, remote_server(), sets the environment variables. The local loopback IP (127.0.0.1) is put to the list of hosts. The local system user and MySQL login credentials are set and collected via getpass().
The other function utilizes the Fabric run() function to send MySQL commands to the remote MySQL server by echoing the command to the mysql executable.
The run_sql() function is a generic function that can be used as a wrapper in other functions. For example, the empty_db() function calls it to execute the SQL commands. This can keep your code a bit more organized and cleaner.