mysql – Daghosoft

Solve problem with mysql closed pool by server

This configuration solve problem with following error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.RecoverableDataAccessException: PreparedStatementCallback; SQL XXXXXXXXXXXXXXXXXX; Communications link failure

The last packet successfully received from the server was 30,184,929 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,184,929 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

<Resource auth="Container1"
          driverClassName="com.mysql.jdbc.Driver"
          maxActive="20"
          maxIdle="10"
          maxWait="120"
          testWhileIdle="true"
          validationQuery="select 1"
          name="jdbc/myapp"
          password="passwd" 
          type="javax.sql.DataSource"
          url="jdbc:mysql://127.0.0.1/mydb?autoReconnect=true&amp;useOldAliasMetadataBehavior=true"
          username="user"/>

In detail this lines validate connections before use and eliminates closed ones:

maxActive=”20″
maxIdle=”10″
maxWait=”120″
testWhileIdle=”true”
validationQuery=”select 1″

How to backup al database in mysql

This script will create a backup for every database in $DEST folder.

#!/bin/sh

NOW=$(date +”%u”)
DEST=/backup/$HOSTNAME

echo “######################## Start Backup ”
mkdir -p $DEST

DBS=”$(mysql -Bse ‘show databases’)”

for db in $DBS
do
mysqldump $db > $DEST/$db-$NOW.sql
echo $db “Status : ” $?
done

Mysql Usefull Commands

Here some usefull command to administer Mysql Database Server:
#Access command line:
mysql -uroot -upassword

#Viev all user:
select user,password,host from mysql.user;

#Enable root remote login :
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;
exit;

#Execute Mysql command from bash:
mysql -uroot -e “create database prod”
mysql -uroot -e “CREATE USER ‘prod’@’%’ IDENTIFIED BY ‘MyProd016′”
mysql -uroot -e “GRANT ALL PRIVILEGES ON prod.* TO ‘prod’@’%’ WITH GRANT OPTION”
mysql -uroot -e “FLUSH PRIVILEGES”
exit;