Use MySQLdb Python module to apply database migration

  • Posted on: 7 April 2013
  • By: Zhijun Chen

The Python MySQLdb provides great supports to facilitate database migration. Here is an example of connecting to MySQL database:

import MySQLdb
import sys

from optparse import OptionParser

def main():
    usage = 'usage: %prog [options]'
    parser = OptionParser(usage)
    parser.add_option("-n", "--hostname", dest="hostname",
                      help="The hostname of the database to connect to.")
    parser.add_option('-u', '--user', dest='user', type='string',
                      help='The user to use when connecting to the database.')
    parser.add_option('-p', '--password', dest='password', type='string',
                      help='The password of the db user.')
    parser.add_option('-d', '--database', dest='db', type='string',
                      help='The name of the database in the host.')

    (options, args) = parser.parse_args()

    if (options.hostname is None or options.user is None or options.password is None or options.db is None):
        parser.print_help()
        sys.exit()

    conn = MySQLdb.connect (host=options.hostname,
                            user=options.user,
                            passwd=options.password,
                            db=options.db)
    cursor = conn.cursor()
    # perform DDL/DML migration
    _add_constraint_to_tables(cursor)

    # perform TCL operations
    conn.commit()
    conn.close()

if __name__ == "__main__":
    main()
* Useful methods in the module:
  1. cursor.execute(operation)

    This method executes a sql command, e.g. cursor.execute('SELECT * FROM USER WHERE id = %s' % id)

  2. cursor.fetchone()

    This method retrieves the next row of a query result set, returning a single sequence, or None when no more data is available.

  3. cursor.fetchall()

    The method fetches all or remaining rows of a query result set, returning a list of tuples. An empty list is returned when no rows are (anymore) available.

Recover MySQL root password

  • Posted on: 23 March 2013
  • By: Zhijun Chen

I forgot MySQL root password once so I put the steps here for future reference.

* Stop MySQL Service

  The first step is stopping MySQL Service.

  Under Linux/Ubuntu:

sudo /etc/init.d/mysql stop

  Under Mac:

sudo /usr/local/mysql/support-files/mysql.server stop
* Navigate to MySQL folder and start MySQL Server without password

mysqld_safe --skip-grant-tables &
* Connect to MySQL server

mysql -u root
* Update root user password

use mysql;
update user set pasword=PASSWORD(“new_password”) where user = 'root';
flush privileges;
* Restart MySQL server and login with new password

  Under Linux/Ubuntu:

sudo /etc/init.d/mysql restart

  Under Mac:

sudo /usr/local/mysql/support-files/mysql.server restart

  Login with new password

mysql -u root -p

 

Host Your Own Site On Your Own Home Server

  • Posted on: 31 January 2013
  • By: Zhijun Chen

Recently I just purchased an HP server and host my own blog site on it. Here are the main steps I went through to achieve a low cost solution.

* Set Up The Server

A quick way to set up your home server to serve a web site is to install Apache HTTP Server.

Under Ubuntu system, you can install the package using the following command:

sudo apt-get install apache2

Using default configuration, when you open http://localhost/, you should be able to see the following page:

 

 

 

 

 

 

 

For more configurations please refer to Apache HTTP Server Documentation

* Choose a Dynamic DNS Service

If you are hosting the site inside your network where the home router acts as gateway, you will need Dynamic DNS Service because your router's external IP address might change over time.

Dynamic DNS or DDNS is a method of updating, in real time, a DNS to point to a changing IP address on the Internet. This is used to provide a persistent domain name for a resource  that may change location on the network.

Refer to Dynamic DNS Service Provider List for available Dynamic DNS Services.

I had to choose Dyndns.org because it is the only DDNS supported by Sky router, which is a bit awkward...If you choose this service, you can try out Dyndns Pro free first and opt out after 14 days. The basic free version will give you one hostname which is enough.

It is quite easy to register, select one of the free hostname and follow the steps. On the hostname page type in your router's current public IP address which can be obtained from your router's configuration site. Of course you can select to host your own domain and pay annual fee.

* Set Up Home Router

For home router, we need to configure DDNS Service and forward port to the server. Again I will use Sky router for illustration.

Navigate to the router home page and select Security tab, you will need to login as administrator (default username and password are admin and sky). Click on Services and add an custom service for port 80 as shown in the following. 

 

 

 

 

 

 

 

 

 

 

Save the service and click on Firewall Rules. Under Inbound rules add new rule for Apache service:

 
 
 
 
 
 
 
 
 
 
 

 

Save the inbound rule, type the router's external IP address in the browser and you should be able to see the apache page.

Navigate to DDNS menu under Advanced, tick use dynamic dns service checkbox, select the ddns service and type in credentials:

 

 

 

 

 

 

 

 

 

 

 

Click on show status and you should see successful message. Type in DDNS hostname in the browser and you should be able to see the apache page.

* Register domain

 Register your domain on your preferred provider. I use Godaddy.com. Follow the registration procedure and you should get an email confirmation from the provider notifying that your domain is registered.

* Set CName of Domain DNS Server

The last step would be editing your CName of DNS Server to point to your DDNS address. For instance, if you register example.co.uk, and you want to use www.example.co.uk as your site address. Under CName you should add host "www" pointing to your DDNS hostname.

After all the configuration you should be able to view your site. It's not a pretty solution but it works if you don't want to pay extra money to use your own domain as DDNS hostname. :)

Set up Git and Jenkins under Ubuntu

  • Posted on: 26 January 2013
  • By: Zhijun Chen
* Install Git and set up Git

Git is a distributed version control system and can be installed using the following one command:

sudo apt-get install git

After it finishes downloading, you will have Git installed and ready to use. Use the following commands to config global username and email.

git config --global user.name "username"
git config --global user.email "email"

Use the following command to see all of your settings:

git config --list
* Set up a new Git project

After installing Git, you should be able to create a new Git project.

There are two types of repositories in Git: development repository and bare repository. A development repository is used for normal, daily development, e.g. editing, adding and deleting files. In contrast, a bare repository has no working directory and it serves as an authoritative basis for collaborative development. You can pull/push changes from/to bare repositories.

Use the following commands to set up a remote bare repository

ssh git@example.com  
mkdir my_test_project.git 
cd my_test_project.git 
git init --bare

After setting up bare repository, we need to init a local project and do a initial "push" to the bare repository. So on local machine, do the following:

cd my_test_project
git init
git add * 
git commit -m "Initial commit message" 
# Add remote origin, specify your own project path 
git remote add origin git@example.com:my_test_project.git
git push -u origin master

Others can clone the remote repository using the following command

git clone git@example.com:my_test_project.git
* Install Jenkins

Install Jenkins using the following command:

wget -q -O - http://pkg.jenkins-ci.org/debian/jenkins-ci.org.key | sudo apt-key add -
sudo sh -c 'echo deb http://pkg.jenkins-ci.org/debian binary/ > /etc/apt/sources.list.d/jenkins.list'
sudo apt-get update
sudo apt-get install jenkins

For more configurations, please refer to Installing Jenkins on Ubuntu

* Install Jenkins Git plugin

After installing Jenkins, you should be able to access Jenkins root page at http://your_jenkins_host:8080/

Next we should install Jenkins Git plugin to allow Jenkins to talk to Git repository. If your Jenkins instance has security enabled, login as a user who has the Overall/Administer permission.

Select Manage Jenkins link on the left-hand side of the screen. Click on Manage Plugins link.

Under Available tab search for "git" then you should be able to select the Git plugin.

Click the Download now and Install after restart button at the bottom.

* Configure job in Jenkins

Navigate to Jenkins instance job or create one if none exists.

Select Configure link on the left hand side of the screen In the Source Code Management select Git option and enter the previous git address which looks like ssh://git@example.com/my_test_project.git. Note: Make sure that the bare git repository is visible to Jenkins otherwise errors would be thrown.

For more information please refer to Jenkins Git Plugin Click Save button at the bottom.

* Configure Jenkins to build automatically after Git push

You may want to trigger Jenkins job to build automatically after each Git push. You can achieve this simply by using Git post-receive hook.

To do this, navigate to job configuration page by clicking on Configure link on the left hand side. Check Poll SCM option under Builder Triggers.

In the machine which holds Git bare repository, inside my_test_project.git folder, go into hooks folder and create a file called post-receive, and type in the following command:

curl http://your_jenkins_host:8080/git/notifyCommit?url=path_to_bare_repository

for instance, curl http://localhost:8080/git/notifyCommit?url=/Projects/myproject.git

Go to your Git development repository and do a Git push, you should see the following message.

"remote: Scheduled polling of my test project"

Pages