Backing Up and Restoring MySQL Database Using mysqldump

Although not always the most efficient, mysqldump is a handy way to migrate your MySQL server — particularly if you don’t have many schemas and the size of data is small.

To create backup, first list all schemas on the server using

SHOW DATABASES;

I normally avoid backing up performance_schema, information_schema and mysql schema. They contain database configuration, user settings etc. This would mean you have to reconfigure all your settings on the new server

Then take the dump of all schema using this command:

mysqldump -u***** -p***** --databases <space_separated_schema_names> > mybackup_20131107_1223.sql

Then compress, transfer and inflate the sql file into the new server’s host. When you’re ready to restore, just do

mysql -u***** -p***** < mybackup_20131107_1223.sql
Advertisements

VirtualBox, Ubuntu and LAMP Stack

Came accross VirtualBox, a free & excellent virtual machine software. I decided to take it for a spin creating a Ubuntu virtual machine LAMP stack on it..

Here We Go

  1. Download and install VirtualBox
  2. Download latest Ubuntu iso installation file
  3. From VirtualBox create a new Virtual Machine. Select type: Linux and version: Ubuntu. On the next step you will be prompted with dvd drive containing the installaion disk, but instead just select the iso downloaded on step 2
  4. Go through the Ubuntu installation steps
  5. It’s also very helpful to install ssh server so you can ssh into your VM later on: sudo apt-get install openssh-server

Voila! You have ubuntu running on your Windows PC

Host and Guest

In virtualization realm, host indicates your physical PC (Windows 7 in my case), and guest is the virtual machine (Ubuntu). Most of virtual machine software documentation uses host and guest terminology heavily so make sure you’re familiar with it

Networking

This is where things get tricky. Virtual machine comes with virtual network adapters, and you have to do few configuration to setup connectivity between your virtual and physical adapters.

By default VirtualBox allows the guest machine to connect to the internet through NAT, so you can download data, browse internet etc. However if you want to run servers from the guest, it won’t be discoverable by the host or other PC in the host’s network immediately.

One approach to make them discoverable is by setting up port forwarding. You get here by going to networking section on the machine’s setting on Virtual Box

portforwarding

Note that setting port forwarding requires the port is actually free on your host machine. Hence I find it very useful to add an IP to your host’s network interface specifically for the VM so you don’t have port conflicts. In this example I added the IP 192.168.16.201 on my interface:

addip

The “AMP”

So there’s the “L – Linux” done. Now for the Apache, Mysql and Php, it can simply be done by using Ubuntu’s apt-get package manager:

  1. Open a terminal / SSH session to your Ubuntu machine
  2. Elevate into root using sudo su root
  3. apt-get install apache2
  4. apt-get install php5
  5. apt-get install mysql-server mysql-client

Few helpful notes:

  • Default doc root is /var/www
  • To start / stop apache: sudo service apache2 stopsudo service apache2 start
  • To start / stop mysql: sudo service mysql stop / sudo service mysql start

Tomcat 7 JDBC Session Persistence

The default Tomcat session management strategy is in-memory session persisted into file when the server is shutdown gracefully. If the server dies in a cold fashion (eg: kill -9 or power outage), session data might be lost. One approach to mitigate this is to store session data into database using JDBC, aka JDBC Session Persistence.

JDBC Session Persistence can also aid load balancer failover scenario. I’d say this is an alternative to setting up (often cumbersome) TCP session replication. Note that if you have multiple cloud servers like Amazon EC2 it doesn’t come with TCP multicast feature — TCP session replication sounds like a nightmare to setup.

The Steps

  1. Ensure org.apache.catalina.session.StandardSession.ACTIVITY_CHECK or org.apache.catalina.STRICT_SERVLET_COMPLIANCE is set to true. Add line similar to following into your Tomcat’s startup.sh (if you’re on UNIX)
    export CATALINA_OPTS="-Dorg.apache.catalina.session.StandardSession.ACTIVITY_CHECK=true"

    Tomcat System Property Reference will explain what do each property means if you’re curious

  2. Create following SQL table (yes you need a database to store the session data)
    create table tomcat_sessions (
      session_id     varchar(100) not null primary key,
      valid_session  char(1) not null,
      max_inactive   int not null,
      last_access    bigint not null,
      app_name       varchar(255),
      session_data   mediumblob,
      KEY kapp_name(app_name)
    );
    
  3. Place a copy of mysql-connector-java.jar (or your DB’s JDBC driver) into $CATALINA_HOME/lib
  4. In your web app, add a META-INF/context.xml file. If you use standard maven layout you have to place it on src/main/webapp/META-INF/context.xml. You can copy the file from $CATALINA_HOME/conf/context.xml as a starting point. Then under <Context> element add following <Manager> element
    <Manager className="org.apache.catalina.session.PersistentManager"
             maxIdleBackup="10">
      <Store className="org.apache.catalina.session.JDBCStore"
             connectionURL="jdbc:mysql://localhost/mytomcat?user=root"
             driverName="com.mysql.jdbc.Driver"
             sessionAppCol="app_name"
             sessionDataCol="session_data"
             sessionIdCol="session_id"
             sessionLastAccessedCol="last_access"
             sessionMaxInactiveCol="max_inactive"
             sessionTable="tomcat_sessions"
             sessionValidCol="valid_session" />
    </Manager>
    

    Notice how the SQL column name corresponds to some of the settings above. In this configuration I used mysql database on localhost with database name “mytomcat” and username “root”. maxIdleBackup=”10″ specifies number of seconds before the in-memory session data is persisted into database.

    There are many other settings you can tweak, have a look at the Tomcat Manager Component Reference.

Fine Prints

This article is tested against Tomcat 7.0.39 but I guess it should also work with Tomcat 6. If you’ve jumped the ship from relational to MongoDB, dawsonsystems published an open source MongoDB Tomcat Session Manager on github. I haven’t got a chance to try it but it looks awesome.

Hibernate One To Many Relationship

Continuing from my post Spring MVC + Hibernate + MySQL Quick Start From Scratch, here is how to setup one to many relationship on Hibernate.

Let’s assume you want to add a new table called topping, each pizza can have several toppings (one to many relationship).

Pizza table
pizzatable

Topping table
topping table

First code the Topping entity class:

@Entity
@Table(name = "topping")
public class Topping {
  @Id
  @GeneratedValue
  private long id;
  
  private String name;
  
  @ManyToOne
  @JoinColumn(name = "pizza_id", referencedColumnName = "id")
  private Pizza pizza;

  // getters & setters..
}

Notice the usage of @ManyToOne and @JoinColumn annotation. This allows the owning Pizza to be queried from the Topping. This also says “in order to find pizza that owns this topping, have a look at pizza_id column on topping table, match it with id column on pizza table”

And let’s have a look at our modified Pizza entity class:

@Entity
@Table(name = "pizza")
public class Pizza {
  @Id @GeneratedValue private long id;
  private String name;
  private double price;
  
  @OneToMany(mappedBy = "pizza", fetch = FetchType.LAZY)
  private List<Topping> toppings;

  // getters & setters
}

This setups a bi-directional relationship where you can obtain list of toppings from a Pizza object, and the owning Pizza from a Topping object.

The FetchType.LAZY setting tells Hibernate not to bother populating the toppings unless we ask it to (this is handy to conserve memory if you have a long chains of relationships with thousands of objects on it).

Next if I want to list my pizzas with its topping, I can write following method on my DAO class:

@Transactional
public List<Pizza> findAllWithToppings() {
  Session session = sessionFactory.getCurrentSession();
  List pizzas = session.createQuery("select distinct p from Pizza as p left join fetch p.toppings").list();
  return pizzas;
}

The above hibernate query will roughly translate into following SQL:

select * from pizza p left outer join topping t on p.id = t.pizza_id;

Download the source code

git clone https://gerrytan@bitbucket.org/gerrytan/pizzashop.git -b one_to_many

Spring MVC + Hibernate + MySQL Quick Start From Scratch

This is a tutorial to build a very simple pizzashop Spring MVC, Hibernate and MySQL application. The main page shows all pizza rows stored on a database table:

03

Technology in Java world moves very fast. Using straight Hibernate might no longer be preferable since Java EE 5 introduces JPA (Java Persistence API) but it’s still good to learn anyway. Spring MVC and Hibernate is one of the most popular Java libraries used out there.

Tools / Environment Required
If you’re just starting fresh and don’t have most of the tools below just install JDK, STS (Springsource Tools Suite) and MySQL server, everything else is bundled within them.

Project Setup and Boilerplate

  1. First, create new Maven project on STS. Skip archetype selection. Pick maven group id, artifact id and select packaging to war.01 02
  2. By default Maven will use JDK 1.5, re-configure it to use 1.6. Add following maven-compiler-plugin section to pom.xml under <pom> element. Ensure the change takes effect by updating maven project settings (right click on project -> Maven -> Update project…).
    <build>
      <plugins>
        <plugin>
          <groupId>org.apache.maven.plugins</groupId>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.1</version>
          <configuration>
            <source>1.6</source>
            <target>1.6</target>
          </configuration>
        </plugin>
      </plugins>
    </build>
    
  3. Add Spring, Hibernate, Java EE and MySQL maven dependencies to pom.xml. Place following under <pom> element.
    <properties>
      <spring.version>3.2.3.RELEASE</spring.version>
      <hibernate.version>4.2.2.Final</hibernate.version>
    </properties>
    
    <dependencies>
      <!-- Spring -->
      <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
      </dependency>
      <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>${spring.version}</version>
      </dependency>
      <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>${spring.version}</version>
      </dependency>
      
      <!-- Hibernate -->
      <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>${hibernate.version}</version>
      </dependency>
      
      <!-- Java EE -->
      <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>servlet-api</artifactId>
        <version>2.5</version>
        <scope>provided</scope>
      </dependency>
      <dependency>
        <groupId>jstl</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
      </dependency>
      
      <!-- Others -->
      <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
      </dependency>
      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.25</version>
      </dependency>
      
    </dependencies>
    
  4. Create a web.xml descriptor file with Spring MVC servlet setup on it, place it on src/main/webapp/WEB-INF
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    
      <servlet>
        <servlet-name>appServlet</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
          <param-name>contextConfigLocation</param-name>
          <param-value>/WEB-INF/servlet-context.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
      </servlet>
       
      <servlet-mapping>
        <servlet-name>appServlet</servlet-name>
        <url-pattern>/</url-pattern>
      </servlet-mapping>
      
    </web-app>
    
  5. Create src/main/webapp/WEB-INF/servlet-context.xml Spring bean config file for dispatcher servlet. We configure few important stuffs in here:
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:beans="http://www.springframework.org/schema/beans"
      xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
      xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">
    
      <!-- Enable @Controller annotation support -->
      <mvc:annotation-driven />
    
      <!-- Map simple view name such as "test" into /WEB-INF/views/test.jsp -->
      <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/" />
        <property name="suffix" value=".jsp" />
      </bean>
      
      <!-- Scan classpath for annotations (eg: @Service, @Repository etc) -->
      <context:component-scan base-package="com.gerrytan.pizzashop"/>
      
      <!-- JDBC Data Source. It is assumed you have MySQL running on localhost port 3306 with 
           username root and blank password. Change below if it's not the case -->
      <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/pizzashop"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
        <property name="validationQuery" value="SELECT 1"/>
      </bean>
      
      <!-- Hibernate Session Factory -->
      <bean id="mySessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="myDataSource"/>
        <property name="packagesToScan">
          <array>
            <value>com.gerrytan.pizzashop</value>
          </array>
        </property>
        <property name="hibernateProperties">
          <value>
            hibernate.dialect=org.hibernate.dialect.MySQLDialect
          </value>
        </property>
      </bean>
      
      <!-- Hibernate Transaction Manager -->
      <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="mySessionFactory"/>
      </bean>
      
      <!-- Activates annotation based transaction management -->
      <tx:annotation-driven transaction-manager="transactionManager"/>
    </beans>
    
    


The Business Functionality

  1. Now all the boilerplate code done, we can start coding the business functionality. For this simple app we will have a pizza database table with just id, name and price column. Create the Pizza entity class representing the table
    package com.gerrytan.pizzashop;
    // imports ..
    
    @Entity
    @Table(name = "pizza")
    public class Pizza {
      @Id @GeneratedValue private long id;
      private String name;
      private double price;
      /* getters & setters */
    }
    
  2. Create a DAO class to obtain Pizza entity persisted on database. Note that we won’t create service layer classes for the sake of simplicity (on real-life complex business application adding service layer is a good practice).
    package com.gerrytan.pizzashop;
    // imports..
    
    @Repository
    @SuppressWarnings({"unchecked", "rawtypes"})
    public class PizzaDAO {
      @Autowired private SessionFactory sessionFactory;
      
      /**
       * @Transactional annotation below will trigger Spring Hibernate transaction manager to automatically create
       * a hibernate session. See src/main/webapp/WEB-INF/servlet-context.xml
       */
      @Transactional
      public List<Pizza> findAll() {
        Session session = sessionFactory.getCurrentSession();
        List pizzas = session.createQuery("from Pizza").list();
        return pizzas;
      }
    }
    
  3. Create a Spring MVC controller to handle request from the main page. Note that PizzaDAO reference is injected, and after collection of Pizza entity objects are obtained it’s passed to the view using Model object.
    package com.gerrytan.pizzashop;
    // imports..
    
    @Controller
    @RequestMapping("/")
    public class PizzaController {
      
      @Autowired private PizzaDAO pizzaDAO;
      
      /**
       * This handler method is invoked when
       * http://localhost:8080/pizzashop is requested.
       * The method returns view name "index"
       * which will be resolved into /WEB-INF/index.jsp.
       *  See src/main/webapp/WEB-INF/servlet-context.xml
       */
      @RequestMapping(method = RequestMethod.GET)
      public String list(Model model) {
        List<Pizza> pizzas = pizzaDAO.findAll();
        model.addAttribute("pizzas", pizzas);
        return "index";
      }
    }
    
  4. Add a JSP view to list all the pizza entities passed by the controller
    src/main/webapp/WEB-INF/index.jsp

    <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Pizzashop</title>
    </head>
    <body>
    	<h1>List of All Pizzas</h1>
    	<ul>
    		<c:forEach var="p" items="${pizzas}">
    			<li>${p.id} - ${p.name} - ${p.price}</li>
    		</c:forEach>
    	</ul>
    </body>
    </html>
    
  5. And finally prepare MySQL pizzashop database schema:
    CREATE SCHEMA `pizzashop`;

    And pizza table:

    CREATE  TABLE `pizzashop`.`pizza` (
      `id` BIGINT NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NULL ,
      `price` DOUBLE NULL ,
      PRIMARY KEY (`id`) );
    

    And insert some data into it

    INSERT INTO `pizzashop`.`pizza` (`id`, `name`, `price`) VALUES ('I', 'Italian', '7.5');
    INSERT INTO `pizzashop`.`pizza` (`id`, `name`, `price`) VALUES ('2', 'Thin Crust', '6');
    INSERT INTO `pizzashop`.`pizza` (`id`, `name`, `price`) VALUES ('3', 'Pepperoni', '6.2');
    

    It is assumed you have MySQL server running on your local machine (localhost) on the default port 3306. The name of the schema is pizzashop and table pizza. Have a look at Spring beans configuration section above to reconfigure this.

Running The Code

  1. The easiest way to run your code is using in-memory Maven Tomcat plugin. It will launch on-the-fly Tomcat server with your code deployed. Make sure your project is selected on the project exploded and setup a maven run configuration on STS. Go to Run -> Run Configurations.. and create a new maven build entry like this:
    maven goal
  2. Click Run and navigate your browser to http://localhost:8080/pizzashop

Download The Source Code

Source code for this demonstration can be obtained using git:

git clone https://bitbucket.org/gerrytan/pizzashop.git -b basic

Congratz!

Well done on making it this far. Hopefully that was a quick and nice introduction to Spring MVC + Hibernate + MySQL and you can see how the tedious database to java class mapping is now simplified. You might have lots of questions in your mind by now — feel free to ask in the comment section below. Following are few official references and community article links you can browse around:

Also checkout my next tutorial about Hibernate One To Many Entity Relationship.