8. Connecting MMBase to MySQL using MySQL Connector/J

We can connect MMBase to our MySQL database server using MySQL Connector/J. This JDBC driver for MySQL can be downloaded from:

  http://www.mysql.com/downloads/api-jdbc.html

Copy the JDBC driver to the MMBase lib/ directory:

  
  cp mysql-connector-java-xxx-bin.jar /usr/local/tomcat/webapps/mmbase-webapp/WEB-INF/lib/
  chown tomcat:tomcat /usr/local/tomcat/webapps/mmbase-webapp/WEB-INF/lib/mysql-connector-java-xxx-bin.jar
  chmod 664 /usr/local/tomcat/webapps/mmbase-webapp/WEB-INF/lib/mysql-connector-java-xxx-bin.jar
  
  

Make this your JDBC driver for MMBase by editing /usr/local/tomcat/webapps/mmbase-webapp/WEB-INF/config/modules/jdbc.xml. Substitute the database name (mmbase), user (mmuser) and password (mmpass) with the database settings you'd like MMBase to use.

  
  <property name="url">jdbc:mysql://$HOST:$PORT/$DBM</property>
  <property name="user">mmuser</property>
  <property name="password">mmpass</property>
  <property name="supportclass">org.mmbase.module.database.DatabaseSupportShim</property>
  <property name="database">mmbase</property>
  <property name="connections">20</property>
  <property name="host">localhost</property>
  <property name="driver">com.mysql.jdbc.Driver</property>
  <property name="port">3306</property>
  <property name="querys">256</property>
  <property name="probetime">30</property>
  
  

Set the ownership and access rights of this configuration file. Since it contains your database name, user name and password, make sure other users can not read this file.

  
  chown tomcat:tomcat /usr/local/tomcat/webapps/mmbase-webapp/WEB-INF/config/modules/jdbc.xml
  chmod 640 /usr/local/tomcat/webapps/mmbase-webapp/WEB-INF/config/modules/jdbc.xml
  
  

Now create the MySQL database you've just defined for MMBase:

  
  mysql -u root -p
  
  


  
  CREATE DATABASE <mmbase>;
  USE <mmbase>;
  GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON <mmbase>.* TO
      <mmuser>@'%' IDENTIFIED BY '<password>';
  GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON <mmbase>.* TO
      <mmuser>@localhost IDENTIFIED BY '<password>';
  GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON <mmbase>.* TO
      <mmuser>@localhost.<domainname> IDENTIFIED BY '<password>';
    # RH bug fix
  GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON <mmbase>.* TO
      <mmuser>@<hostname> IDENTIFIED BY '<password>';
  flush privileges;
  exit;
  
  

Substitute the hostname (<hostname>) and domain name (<domainname>) with your own hostname and domain name, and the database name (<mmbase>), user (<mmuser>) and password (<password>) with the values you just entered in the JDBC driver configuration file.

Tip

To quickly empty your MySQL database, i.e. after you've updated your MMBase configuration or application (replace the database name <mmbase> with your own):

  
  mysqladmin -u root -p drop <mmbase>
  mysqladmin -u root -p create <mmbase>
  
  

Tip

If you are already using quite some MySQL connections (for example for authentication by and as a back-end to your mail daemons and Apache servers), the MySQL server might run out of connections (its maximum number defaults to 100). Then Tomcat could simply refuse to start or give an error message when initiating the MySQL connection pool, or your IMAP or POP servers will have problems authenticating their clients.

You can increase the maximum number of connections to MySQL by adding to the configuration file /etc/my.cnf:

  
  [mysqld]
  set-variable = max_connections=200