MySQL

Connecting To Remote MySQL From Hibernate Using Tunnelling Over SSH

  • Posted on: 12 December 2014
  • By: Zhijun Chen

For certain reasons sometimes you may want to connect to remote MySQL server over SSH tunnel when you maintain web applications, e.g. debug a production issue which is hard to replicate locally. Most IDEs like Eclipse offer the debug tools which you can put break points and step through. You can find out the problem once connecting to remote database and debug with caution using local code copy.

The following steps illustrate how to achieve this for Hibernate based web applications (Presume you have the user set up for SSH and database connection).

  • Open MySQL tunnel over SSH

 You will only need the following command to open a tunnel:

ssh -L 3333:db.remotehost.com:3306 username@remotehost.com

This will open port 3333 on local machine to connect to MySQL port 3306 on remote server, enter your password on remote host once prompted.

  • Update Hibernate configuration file

Once we've got the tunnel, the next step would be updating Hibernate configuration file hibernate.cfg.xml to connect to the port 3333 as follows:

<hibernate-configuration>
  <session-factory>
    ...
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3333/remotedb</property>
    <property name="hibernate.connection.username">remote_user</property>
    <property name="hibernate.connection.password">remote_password</property>
    ...
  </session-factory>
</hibernate-configuration>

The remotedb is the database to connect on remote server, remote_user/remote_password is the username and password for that database on remote MySQL database.

Now you can open your web application and it should use the remote database you specified.

Create MySQL Test Data For Varbinary Column

  • Posted on: 16 August 2014
  • By: Zhijun Chen

I recently came across a scenario where I needed to create hundreds of test data for a MySQL table with varbinary column defined.

Here is the table schema:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `password` varchar(100) NOT NULL,
  `company_id` int(11) NOT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dtype` varchar(10) NOT NULL,
  `uuid` varbinary(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_company_key` (`company_id`),
  CONSTRAINT `user_company_key` 
  FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) 
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I used uuid() method to generate column value, I got an error of "Data too long for column".  Finally I managed to get around the problem using the following procedure.

DELIMITER $$
USE test_db$$
DROP PROCEDURE IF EXISTS addUserTest$$
CREATE PROCEDURE addUserTest()
	BEGIN
		DECLARE x INT;
		SET x = 1;
		WHILE x <= 500 DO
			INSERT INTO user (`user_name`, `first_name`, `last_name`, 
				`password`, `company_id`, `dtype`, `uuid`) VALUES 
				(CONCAT('test_user', x, '@example.com'), 'Test', 'User', 
				'LKJMoSCO3eQSFoHlni9ludxTBkBHpPmk', 1, 'USER', 
				UNHEX(REPLACE(UUID(),'-','')));
			SET x = x + 1;
		END WHILE;
	END$$
DELIMITER ;
CALL addUserTest;

unhex(replace(uuid(), '-', '')) generates suitable values for varbinary(16) column.
The concat() method concatenates strings together in MySQL while replace() method replaces one string with another.

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