MySQL Secure your login using login patch

Setup login path using

mysql_config_editor

MySQL Configuration Utility.
Usage: mysql_config_editor [program options] [command [command options]]
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  -?, --help          Display this help and exit.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE
Where command can be any one of the following :
       set [command options]     Sets user name/password/host name/socket/port
                                 for a given login path (section).
       remove [command options]  Remove a login path from the login file.
       print [command options]   Print all the options for a specified
                                 login path.
       reset [command options]   Deletes the contents of the login file.
       help                      Display this usage/help information.

Help for each option:

mysql_config_editor set -?

MySQL Configuration Utility.

Description: Write a login path to the login file.

Usage: mysql_config_editor [program options] [set [command options]]

  -?, --help          Display this help and exit.

  -h, --host=name     Host name to be entered into the login file.

  -G, --login-path=name

                      Name of the login path to use in the login file. (Default

                      : client)

  -p, --password      Prompt for password to be entered into the login file.

  -u, --user=name     User name to be entered into the login file.

  -S, --socket=name   Socket path to be entered into login file.

  -P, --port=name     Port number to be entered into login file.

  -w, --warn          Warn and ask for confirmation if set command attempts to

                      overwrite an existing login path (enabled by default).

                      (Defaults to on; use --skip-warn to disable.)

Variables (--variable-name=value)

and boolean options {FALSE|TRUE}  Value (after reading options)

--------------------------------- ----------------------------------------

host                              (No default value)

login-path                        client

user                              (No default value)

socket                            (No default value)

port                              (No default value)

warn                              TRUE

Example of command
mysql_config_editor set --login-path=m3306 --user=root --socket=/tmp/mysql.socket  -p

Create orapassword file

in init.ora file add 

remote_login_passwordfile EXCLUSIVE/SHARE

EXCLUSIVE - file is available only to one database and you can have more than one user
SHARE - file is available for multiple databases, only SYS user can be in this file.

For LINUX system file name has to be orapw (11g)

remote_login_passwordfile EXCLUSIVE/SHARE
SQL> show parameter password;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

now I can login to DB using

sqlplus sys/PASS@SID as sysdba

Partitions

Select partition list for table

select * from dba_tab_partitions
where 
table_owner='OWNER' and table_name = 'TABLE_NAME'

Select min/max date in partition 

select min(trx_date),max(trx_date) from TABLE_NAME partition (SYS_P25020)

Split partition base on date

ALTER TABLE P_TRX_TICKETS
    SPLIT PARTITION FOR(TO_DATE('01-MAY-2014','dd-MON-yyyy'))
    AT (TO_DATE('01-May-2014','dd-MON-yyyy'))
    INTO (PARTITION p_trx_12014,PARTITION p_trx_1a2014);

Split partition using partition name

  ALTER TABLE xms.P_TRX_TICKETS
    SPLIT PARTITION SYS_P25020
    AT (TO_DATE('01-May-2014','dd-MON-yyyy'))
    INTO (PARTITION tic_042014,PARTITION tic_05_062014);

Links
Oracle Introduction to Partitioning

Mysql Store Procedure

Use curson and print output in store procedure

DECLARE _output TEXT DEFAULT '';
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
  FETCH cur1 INTO _ID;
  IF NOT done THEN
    SET _output = CONCAT(",", _ID); /*SEE ALT BELOW*/

  END IF;
UNTIL done END REPEAT;
CLOSE cur1;

SELECT _output;

Loop in store procedures
http://www.mysqltutorial.org/stored-procedures-loop.aspx 

Create CSV table

Create CSV table in mysql.

Create database CSV (or you can use any existing DB)

CREATE DATABASE CSV;

USE CSV;

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec) 

INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec

Records: 2 Duplicates: 0 Warnings: 0

SELECT * FROM test;

When you check table DB structure you will see files:

-rw-rw---- 1 mysql mysql 65 Oct 2 13:58 db.opt
-rw-rw---- 1 mysql mysql 8578 Oct 3 11:35 test.frm
-rw-rw---- 1 mysql mysql 35 Oct 3 11:36 test.CSM
-rw-rw---- 1 mysql mysql 60 Oct 3 11:37 test.CSV

test.CSV is a data file.

Now you can replace this file with prepared file with data. 

After upload new file you need execute 

FLUSH TABLE test

to refresh data in table.

CSV table not allow NULL value, you will need replace all null values in csv file to empty string "" or 0 for numeric values.

I used mysqldump to export table from one DB and import it to second DB. null value are display as "\N"
To change "\N" to empty string I used following command:

sed ':a;N;$!ba;s/\\N/""/g' p_trx_tickets.txt > p_trx_tickets.CSV

 After update data file execute:

FLUSH TABLE p_trx_tickets

Now data are available to do select and insert it to other DB.

When I did't replaced "\N" to "" (empty string) then I was receiving message "Table corrupted, need repair"

Grep commands example

#######################################################################
# This file contains the grep demo commands we did in the pattern
# matching lecture.
#######################################################################




#######################################################################
# the input data files all consist a number of lines, each of which
# has the following format.
#######################################################################
mschreib:02 :Schreiber:Marc H.:10/11/10/60:25.3333:1:1:1:1




#######################################################################
# simplest uses.
#######################################################################

# Looks for the word "David" in the file "rank.txt"
grep David rank.txt

# Looks for the word "Rebecca" in a number of files.
# grep will print the names of the files as well as the lines found.
grep Rebecca ??.txt




#######################################################################
# Interaction with other commands
#######################################################################

# Looks for the total summary line from the "wc" command.
wc ??.txt | grep total

# midterm scores start with a slash ("/") in my file.
# count the number of lines that include "/5", sufficient for 
# finding the number of people who scored above 50 for my purpose.
grep /5 rank.txt | wc

# count the number of people who scored a perfect 60.
grep /6 rank.txt | wc




#######################################################################
# Regular expression special characters
#######################################################################

# Shows what the character "^" does.
# "^s" looks for a line that starts with a login name whose first
# character is "s".
grep s rank.txt
grep ^s rank.txt

# Shows what the character "$" does.
# ":1$" looks for a line that ends with aranking of ":1"
grep 1 rank.txt
grep :1$ rank.txt

# Shows what the character "." does.
# "." allows any letter to take its place in "Cr.nin"
grep Cr.nin rank.txt

# Another example of what "." does.
# "/5.:" is a better way of searching for people who made a score of
# 50 or higher than the one used above because we now require some
# other letter, denoted by the "." to follow the number "5".  This
# prevents a score of "5" being miscounted as above 50.
grep /5.: rank.txt | wc

# Shows what the characters "[]" do.
# Looks for all people who have made scores between 57 and 59.
grep '/5[7-9]:' rank.txt | wc

# Shows what the characters "[^]" do.
# Looks for all people who have not made scores between 57 and 59.
grep '/5[^7-9]:' rank.txt | wc

# Shows what the character "*" does.
# Looks for a login name that repeats the letter "e" zero or more
# times in the form of "anl", "anel", "aneel", or "aneeel".
grep 'ane*l' rank.txt

# Another example of what "*" does.
# Looks for a score between 58-59, followed by a bunch of arbitrary
# letters denoted by ".*", and with a ranking between 1 and 5 at the
# end of the line.
grep '/5[8-9].*:[1-5]$' rank.txt

# Shows the difference between "*" and "+"
# The first one matches a ranking of either 1 or 11.
# The second one matches only a ranking of 11.
egrep ':11*$' rank.txt
egrep ':11+$' rank.txt

# Shows more features of egrep: () |
# This one looks for people who are ranked 5 or 6.
egrep '(:5|:6)$' rank.txt

# Shows the use of escape characters.
# If the intention is to look for numbers of the form "25.XXX", the
# first one doesn't quite work because the dot would be interpreted as
# a special character of regular expressions, which denotes any
# character.  To make it work, it needs to be prefixed by a backward
# slash "\".
grep '25.' rank.txt
grep '25\.' rank.txt




#######################################################################
# More command line options.
#######################################################################

# To find out more information about these commands, type these.
man grep
man egrep


# Shows what "-v" does.
# We look for lines containing the word David, but we want to exclude
# those lines that have the word "Stavens".
grep David rank.txt | grep -v Stavens

# Shows what "-n" does.
# It tells me the line number of a line that is a match.  In this
# case, the line number and the ranking coincides.
grep crloose rank.txt
grep -n crloose rank.txt

Move partition table to different tablespace

This alter command allow you to move not partition table from one tablespace to another tablespace

ALTER TABLE [TABLE NAME to be moved] MOVE TABLESPACE [destination TABLESPACE NAME]


Move partition from one tablespace to another tablespace:

ALTER TABLE XXX MOVE PARTITION PARTITION_1 TABLESPACE YOUR_TABLESPACE;
ALTER TABLE  P_TRX_TEST MOVE PARTITION SYS_P26246 TABLESPACE TSP_DATATRX

If you are using subpartitions:

ALTER TABLE [TABLE NAME] MOVE SUBPARTITION [SUBPARTITION_NAME] TABLESPACE [NEW_TABLESPACE];

Default tablespace is a tablespace which is used when table was created. 
How to change default tablespace this way new partition will be created in requred tablespace?