How to convert java.util.Date to java.sql.Date

java.sql.Date is usually used in communication between Java application and MySQL. Specifically, It is used when you want to store DateTime into database, the problem is you usually use java.util.Date to present DateTime in application layer. This post will show you how to convert java.util.Date to java.sql.Date for storing into database

java.util.Date utilDate = new java.util.Date();
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

Done!

Read more »

Where Are MySQL Files Stored in UNIX / Linux

Usually /var/lib/mysql or /var/db/mysql directory used to store database and tales under UNIX like operating systems.
You can use the following command to locate MySQL datadir:

grep datadir /etc/my.cnf

Sample outputs:

datadir=/var/lib/mysql

Another command that will display datadir:

ps -eo cmd,args | grep mysql

Sample outputs:

/bin/sh /usr/bin/mysqld_saf /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
/usr/libexec/mysqld --based /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

FreeBSD UNIX specific ps example:

ps -ao pid,command | grep mysql

Sample outputs:

5343 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --socket=/tmp/mysql.sock --pid-file
 5381 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --pid-file=/var/db/mysq

References

• cyberciti.biz

Read more »

How to remove MySQL completely on CentOS

Step 1: List the mysql rpm which is installed on server

[root@localhost ~]# rpm -qa | grep mysql
mysql-5.0.95-5.el5_9
mysql-server-5.0.95-5.el5_9

Step 2: Remove Mysql

[root@localhost ~]# yum remove mysql-5.0.95-5.el5_9

Step 3: Search for Mysql Related Files on server

[root@localhost ~]# find / -iname '*mysql*'

Step 4: Remove all mysql related files

[root@localhost ~]# find / -iname ‘*mysql*’ -exec rm -rf {} \;

Step 5: Remove this folder

[root@localhost ~]# rm -rf /var/lib/mysql

Complete!

Read more »

How to install the latest MySQL on CentOS

The previous post I show you how to install MySQL on CentOS from its repository but it is not the latest MySQL version. Today, on this post, I will show you how to install the latest MySQL on CentOS.

Downloading the MySQL Yum Repository Release Package

[phuongvu@localhost ~]$ wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
--2014-08-28 22:59:50--  http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
Resolving dev.mysql.com... 137.254.60.11
Connecting to dev.mysql.com|137.254.60.11|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm [following]
--2014-08-28 22:59:50--  http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
Resolving repo.mysql.com... 23.202.216.96
Connecting to repo.mysql.com|23.202.216.96|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5824 (5.7K) [application/x-redhat-package-manager]
Saving to: `mysql-community-release-el6-5.noarch.rpm'

100%[====================================>] 5,824       --.-K/s   in 0s     

2014-08-28 22:59:51 (741 MB/s) - `mysql-community-release-el6-5.noarch.rpm' saved [5824/5824]

[phuongvu@localhost ~]$ ls
mysql-community-release-el6-5.noarch.rpm

We have a rpm file.

Installing the MySQL Yum Repository Release Package

[phuongvu@localhost~]$ sudo yum localinstall mysql-community-release-el6-5.noarch.rpm

Installed:
  mysql-community-release.noarch 0:el6-5

Complete!

Checking the Version of the Available Mysql

[phuongvu@localhost~]$ yum info mysql-community-server

Now, you will see the result

Available Packages
Name        : mysql-server
Arch        : i686
Version     : 5.1.73
Release     : 3.el6_5
Size        : 8.8 M
Repo        : updates
Summary     : The MySQL server and related files
URL         : http://www.mysql.com
License     : GPLv2 with exceptions
Available Packages
Name        : mysql-community-server
Arch        : i686
Version     : 5.6.20
Release     : 4.el6
Size        : 51 M
Repo        : mysql56-community
Summary     : A very fast and reliable SQL database server
URL         : http://www.mysql.com/
License     : Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Under GPLv2 license as shown in the Description field

Notice that there are two packages:

1. mysql-server
2. mysql-community-server

mysql-server is the one provided by the default yum repositories. mysql-community-server is the one provided by our newly installed MySQL yum repository, which should be the latest stable release.

Installing

Use this command to install:

[phuongvu@localhost~]$ yum install mysql-community-server

warning: rpmts_HdrFromFdno: V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Retrieving key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid : MySQL Release Engineering <mysql-build@oss.oracle.com>
 Package: mysql-community-release-el6-5.noarch (@/mysql-community-release-el6-5.noarch)
 From   : file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]:

Enter "y" to finish

Complete!

Starting MySQL

[phuongvu@localhost~]$ sudo service mysqld start
Initializing MySQL database:
Starting mysqld:                                           [  OK  ]

Securing the Installation

As mentioned in the above output, if we are running this on a production server then we should do a secure installation as follows:

[phuongvu@localhost~]$ mysql_secure_installation


NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!



In order to log into MySQL to secure it, we'll need the current

password for the root user.  If you've just installed MySQL, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.



Enter current password for root (enter for none):



This should be the first time, so we shouldn’t have a password yet. Just press enter.



OK, successfully used password, moving on...



Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.



Set root password? [Y/n]



Enter Y and enter your new password:



New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

 ... Success!





By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them.  This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a

production environment.



Remove anonymous users? [Y/n]



If you are running on a production server then you should definetely press Y.



Normally, root should only be allowed to connect from 'localhost'.  This

ensures that someone cannot guess at the root password from the network.



Disallow root login remotely? [Y/n]



Once again if you are in production then enter Y. In fact enter Y for all the questions if you are in production:



By default, MySQL comes with a database named 'test' that anyone can

access.  This is also intended only for testing, and should be removed

before moving into a production environment.



Remove test database and access to it? [Y/n] Y

 - Dropping test database...

ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist

 ... Failed!  Not critical, keep moving...

 - Removing privileges on test database...

 ... Success!



Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.



Reload privilege tables now? [Y/n] Y

 ... Success!









All done!  If you've completed all of the above steps, your MySQL

installation should now be secure.

Congratulations you are done. Now let’s see if mysql will survive a reboot:

Surviving a reboot

[phuongvu@localhost~]$ sudo chkconfig --level 3 mysqld on

[phuongvu@localhost~]$ chkconfig --list | grep mysql

mysqld          0:off   1:off   2:off   3:on    4:off   5:off   6:off

Now let’s reboot again and test:

[phuongvu@localhost~]$ sudo service mysqld status

mysqld is stopped

[phuongvu@localhost~]$ sudo service mysqld start

Starting mysqld:                                           [  OK  ]

[phuongvu@localhost~]$ sudo service mysqld status

mysqld (pid  1159) is running...

[phuongvu@localhost~]$ sudo reboot

Now let’s test:

[phuongvu@localhost~]$ sudo service mysqld status

mysqld (pid  733) is running...

Looks like it works fine.

Read more »

How to install MySQL on CentOS from its repository

Installation

This tutorial will show you how to install MySQL on CentOS from its repository. Because it is installed from CentOS's repository then I'm sure the latest version of MySQL will be installed.
Open terminal and type with root privileges

yum -y install mysql-server

Make MySQL run once computer started

chkconfig mysqld on

Make MySQL run as service

service mysqld start

Change Root Password

Login MySQL with root account (Blank for password by default)

mysql -u root -p

Use this command to change root's password

use mysql;

UPDATE user SET Password=PASSWORD('MyNewPass') WHERE User='root';

Make an external access

GRANT ALL ON *.* to 'user1'@'theIP' IDENTIFIED BY 'password1';

flush privileges;

After this command executed, user1 has been created for theIP access to MySQL with password is password1. NOTE: if you want all IP address can be accessed MySQL just replace 'theIP' to '%'

Read more »