MySQL « Stivlo

How to reset MySQL root password

By stefano, on March 31st, 2012%

Once in a while it happens that I forget the root password of a MySQL server. Several methods are explained in details on the MySQL website.

Until now I’ve always used the –skip-grant-tables method, and I was surprised to see a new method using the –init-file option. I tried it, but it didn’t work for . . . → Read More: How to reset MySQL root password

Defining a JNDI DB connection with Tomcat

By stefano, on February 20th, 2012%

1. Install Your JDBC Driver

Copy the driver’s JAR file(s) into the $CATALINA_HOME/lib directory, which makes the driver available both to the resource factory and to your application.

2. Bootstrap the project

If you don’t have a project to play with already, you can bootstrap a project with Spring Roo. I’ve used version 1.2.1. I . . . → Read More: Defining a JNDI DB connection with Tomcat

Hibernate and MySQL

By stefano, on November 21st, 2011%

Since I’ve already wrote about PostgreSQL and H2 Hibernate configuration, I thought it’s a good idea to add MySQL too for my reference.

First I’ve added the dependency to Maven’s pom.xml (see the Maven repository for the latest version).

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.18</version> </dependency>

The second step is Hibernate configuration, here is an example:

. . . → Read More: Hibernate and MySQL

A simple Roundcube optimization

By stefano, on August 6th, 2010%

Roundcube is a nice Ajax based webmail, but I was experiencing slow performance. I checked MySQL slow query log and I found queries taking several seconds. Expecially session and cache-update queries were taking as much as 9 seconds. This is really a long time for an interactive and fluid user experience. I’ve checked Roundcube version . . . → Read More: A simple Roundcube optimization

Resuming replication after duplicate entry error

By stefano, on December 13th, 2008%

MySQL replication was blocked for duplicate entry error, I solved with the following steps.

mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)

Installing proftpd with MySQL support

By stefano, on November 18th, 2008%

On Ubuntu I installed proftpd and proftd-mysql packages.

I add the following tables and the proftpd user to mysql.

CREATE DATABASE `proftpd` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; — – Table structure for table ‘ftpgroup’ — CREATE TABLE ftpgroup ( groupname varchar(16) NOT NULL default ”, gid smallint(6) NOT NULL default ’5500′, members varchar(16) NOT . . . → Read More: Installing proftpd with MySQL support

MySQL replication

By stefano, on November 1st, 2008%

It’s interesting that MySQL replication works also with an unreliable connection. It won’t be up to date all the time, obviously, but it should still work. With this in mind I wanted to keep a copy of a database on my laptop on another computer of the office. I want to keep my laptop on . . . → Read More: MySQL replication

Allowing connection to Mysql from a remote host

By stefano, on September 12th, 2007%

Today I have to change the firewall rules to allow connection to MySQL from a remote server. First I check existing rules on the server that has MySQL and I see where to add the new rule.

# /sbin/iptables –list –line-numbers Chain INPUT (policy DROP) num target prot opt source destination [...] 13 ACCEPT tcp . . . → Read More: Allowing connection to Mysql from a remote host

MySQL: ORDER BY RAND() is slow!

By stefano, on April 26th, 2007%

I knew already, but I was caught again. I was wondering why my Java WebService was slow, I thought to profile the code, but when I checked the process list on the server I saw that MySQL was taking a lot of CPU time.

I looked into queries and I saw that in one of . . . → Read More: MySQL: ORDER BY RAND() is slow!

Stivlo