MySQL Dump examples

Here will post several MySQL dump examples wich can be useful in different cases:

Dump a concrete database:

mysqldump -u user -p my-database

Dump a concrete database and compress it (using bzip2):

mysqldump -u user -p my-database | bzip2 > my-database-dump-date.bz2

Dump a concrete table:

mysqldump -t -u user -p my-database mytable  > my-table-dump-date.sql

Dump a concrete table with some query:

mysqldump -t -u user -p my-database my-table --where="mt-date<='2015-04-03'" > my-table-till_20150403_dump.sql

Dump without locking tables
mysqldump -u user -p --skip-lock-tables --single-transaction my-database

Replace in MySQL

In case you need to replace a text field of several rows in MySQL there is function REPLACE wich can help you to do so, one example below:

update table set my_field=replace(my_field,'string_to_modify','string_modified') where...

Reference:

https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_replace

Export SQL query result into CSV file (on MySQL)

Quite often we want to run an SQL script which can take a long time and analize the data retrieved. In order to do so we can run the SQL query and export the data gathered into a CSV file that can be analized later on. For this you can simple do the following as an example:

SELECT your_date, count(your_counting_field) FROM your_table WHERE your_condition_field=your_condition

INTO OUTFILE '/tmp/your_csv_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The bolded part is the one that performs the export of the result. This can be customized depending on your needs but basically this is it.

NOTE: this example is orientated to be done on mysql command shell

References:

https://dev.mysql.com/doc/refman/5.6/en/select-into.html

Send GET HTTP Request over Java

To send an HTTP request over Java via GET the following code example can be used:



  HttpURLConnection connection = null;
  PrintWriter out;
BufferedReader in;
String inputLine;
String pageText = "";

try {

connection = (HttpURLConnection) gateway.openConnection();
connection.setConnectTimeout(timeout);
connection.setReadTimeout(timeout);
connection.setDefaultUseCaches(false);
connection.setInstanceFollowRedirects(true);
connection.setUseCaches(false);
connection.setRequestMethod("GET");

if (contentType != null) {
connection.setRequestProperty("CONTENT-TYPE", contentType);
}

in = new BufferedReader(new InputStreamReader(
connection.getInputStream()));

while ((inputLine = in.readLine()) != null) {
pageText = pageText + inputLine;
}

log.info(pageText);

} catch (Exception e) {
log.error(e)
  }

Dates over Axis - TimeZones

If you use dates over a Web Service using Apache Axis you may find a quite tricky issue when dealing with them.

Imagine you want to send over a Web Service call a date time using Calendar class.

Calendar myCalendar = new GregorianCalendar();

and want to pass your calendar variable over the Web Service.

If you log the XML send over Axis

http://rafareyeslopez.blogspot.com/2015/04/logging-axis-xml.html


You will notice that the date time is passed in UTC

Example:
2002-05-30T09:30:10Z

So if the other endpoint does not do anything to check about time zones you get in trouble, specially if time is close to midnight and the correct date has to be used.


After trying several approaches such us specify the Time Zone for the Calendar such as:


TimeZone tz = TimeZone.getTimeZone("Europe/Madrid");
Calendar date = Calendar.GetInstance(tz);
date.setTimeInMillis( millis );

It didn't go well

Best solution I found after struggling setting up the right Time Zone to the Calendar passed to the Web Service (always setting time zone and then the different calendar values) is use this method:

public static Calendar convertToGmt(Calendar cal) {

Date date = cal.getTime();
TimeZone tz = cal.getTimeZone();



// Returns the number of milliseconds since January 1, 1970, 00:00:00
// GMT
long msFromEpochGmt = date.getTime();

// gives you the current offset in ms from GMT at the current date
int offsetFromUTC = tz.getOffset(msFromEpochGmt);


// create a new calendar in GMT timezone, set to this date and add the
// offset
Calendar gmtCal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
gmtCal.setTime(date);
gmtCal.add(Calendar.MILLISECOND, offsetFromUTC);



return gmtCal;
}

Logging Axis XML

In case you have a Web service running using Apache Axis and you want to know the XML that is being transported there is an easy way to set up the loggin in log4j.

You just can add to your log4j config file:

<appender name="AXIS" class="org.apache.log4j.FileAppender">
   <param name="File" value="/etc/log/axis.log" />
   <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%d %-5p [%c] %m%n" />
   </layout>
</appender>


 <category name="org.apache.axis.transport.http.HTTPSender">
<priority value="DEBUG" />
<appender-ref ref="AXIS" />
</category>

Like that in log file axis,log you will be able to see the XML that is being transported.

Create a Web Service Client in Eclipse

Create new Web Service Client in Eclipse becomes quite easy thanks to the features this IDE brings to us along with the amount of existing plug-ins.

Lets say we have the URL of a published Web Service and we want to interact with it from our project. We just would need to go to New -> Other

Below the Web Services optin we have several options:

We choose Web Service Client.

In the n ext screen just insert the WSDL URL such as http://localhost:8080/Simple_webservice/HelloWorldWS?wsdl

NOTE: we used here http://www.webservicex.com/globalweather.asmx?wsdl a public global weather web service.

We just need to click next

Choose the output folder and thats it. We have in our project the needed classes to access to the Webservice from our code (stub, locator,etc.)

You just need to use the methods published in the Web Service.