Mysql Secure Installation for non default socket.

SECURING MYSQL SERVER –

mysql_secure_installation is a script useful to

  • set root password
  • disallowing root login remotely
  • removing anonymous user accounts after first installation
  • removing test database which can be accessed by any users

Above script would work for default socket location i.e /var/lib/mysql/mysql.sock

If the server is started with a non-default socket location, mysql_secure_installation does not consider any socket options you specify and the script would fail (For example -> mysql_secure_installation –socket=/mysql/socket/mysql.sock)

We should also consider this situation in case if we need to install more than one instance of mysql on a single server. Read More

Hive connection using java

Here is a sample java code to connect hive database through JDBC.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveJdbcTest{
  private static String driverName = "org.apache.hive.jdbc.HiveDriver";

  public static void main(String[] args) throws SQLException {
    try {
      Class.forName(driverName);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
      System.exit(1);
    }

    Connection con = DriverManager.getConnection("jdbc:hive2://<hive_server>:10000/<db>", "", "");
    Statement stmt = con.createStatement();
    String sql = "show tables";
    System.out.println("Current query: " + sql);
    ResultSet res = stmt.executeQuery(sql);
    if (res.next()) {
      System.out.println(res.getString(1));
        }

  }
}

If you are facing any trouble compiling or running this program include hive and hadoop libraries to classpath using : Read More

Partition maintainance script for Mysql

A simple way for adding future partitions or purging old partitions is through mysql procedures. Later we can use any scheduler or a cronjob to call this procedure daily.

Here I will provide a sample procedure for maintainance of a daily partitioned table based on a datetime column. Please do look at my previous post on mysql range partitioning in the following post. I have taken the example from this post and modified it based on our requirement.

First we will create a sample table along with a partition for our testing purposes.

mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
mysql> alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16092015 values less than ( to_seconds('2015-09-17 00:00:00') ));

Now lets create a table for holding list of tables and number of partitions to preserve/drop.

mysql> create table test.part_tables(dbname varchar(50),tabname varchar(50),numpart int,nextpart int);

Lets go ahead and insert a sample row there :

mysql> insert into test.part_tables values('test','test_partitions',30,7);

So, we will be preserving only last 30 days partitions and make sure we have next 7 days partitions for ‘test_partitions’ table.

Mysql Procedure 1 (part_maint) : This procedure takes in table list from our meta-table i.e part_tables and runs partition maintainance for each table :

Read More

Mysql range partitioning on date column

Here is a simple example for partitioning a mysql table based on datetime column :

  • Creating a test table
mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
Query OK, 0 rows affected (11.83 sec)
  • Creating a initial partition :
alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16052016 values less than ( to_seconds('2016-05-17 00:00:00') ));
  • Adding new partition:
mysql> alter table test_partitions add partition ( partition test_partitions_17052016 values less than ( to_seconds('2016-05-18 00:00:00') ));
Query OK, 0 rows affected (10.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
  • Verifying data in specific partition

Read More

Character set conversion in Oracle

Here is the procedure to convert Oracle database (11G) from  US7ASCII to  UTF8/ALT32UTF8, we must be using similar steps to convert to a different character set.

In this example we will be using csscan utility to find lossy and convertible data in database and csalter utility to alter characterset.

  • Install csscan utility :
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
  • Verify current characterset :
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
  • Check for invalid objects :
SQL> SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status ='INVALID'
/

Recompile all invalid objects in the database : Read More