Using % or localhost for host when creating MySQL user

Posted on

‘localhost’ is special in mysql, it means a connection over a unix socket (or named pipes on windows I believe) as opposed to a TCP/IP socket, using % as the host does not include ‘localhost’

If you want connect to user@’%’ from localhost use mysql -h192.168.0.1 -uuser -p


Restore data from frm files mysql

Posted on Updated on

1. Stop service mysqld
2. cp -R /old/var/lib/mysql/your_db /var/lib/mysql
chrown -R mysql:mysql /var/lib/mysql/your_db
cp /old/var/lib/mysql/ibdata1, ib_logfile0, ib_logfile1 /var/lib/mysql
override? yes
3. start service mysqld
4. Done

Vài chiêu với MySQL

Posted on Updated on

Xuất file csv bằng command line hoặc sql query

mysql -u user -ppass [database_name] | sed 's/\t/,/g' > out.csv

INTO OUTFILE 'out.csv'

Install MySQL, Nginx, Tomcat7 on centos 6.8

Posted on Updated on

Before installing:
yum update


Install MySQL

  1. Installing MySQL and tell it which runlevels to start on:
    yum install mysql-server
    /sbin/chkconfig --levels 235 mysqld on
  2. Start the MySQL:
    service mysqld start
    MySQL will bind to localhost ( by default
  3. Harden MySQL Server
    Run the mysql_secure_installation script to address several security concerns in a default MySQL installation

Mysql Root Login:

mysql -u root -p
then type your root password

Create new user and database

create new database with name newDB, new user with name newUser and newPassword is newUser‘s password:
create database newDB;
create user 'newUser'@'localhost' identified by 'newPassword';
grant all on newDB.* to 'newUser' identified by 'newPassword';

and exit root mysql to login as newUser:

mysql -u newUser -p

and type newUser’s password and if it show mysql>, you have succeed

Reset the MySQL Root Password

If you forget your root MySQL password, it can be flushed and then reset.

  1. Stop the current MySQL server instance, then restart it with an option to not ask for a password.

    sudo /etc/init.d/mysqld stop
    sudo mysqld_safe --skip-grant-tables &
  2. Reconnect to the MySQL server with the MySQL root account.
    mysql -u root
  3. Use the following commands to reset root’s password. Replace password with a strong password.

    use mysql;
    update user SET PASSWORD=PASSWORD("password") WHERE USER='root';
    flush privileges;
  4. Then restart MySQL.

    service mysqld restart

You’ll now be able to log in again using mysql -u root -p.

Dump and restore backup

$ mysqldump –opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Tomcat 7

Check java version:
java -version

If you have not installed JDK, install java 8 on centos 6.8
Download Tomcat7 

at Official download page

tar xzf apache-tomcat-7.0.79.tar.gz
mv apache-tomcat-7.0.79 /var/lib/tomcat7

Starting Tomcat7

cd /var/lib/tomcat7
sh ./bin/

Sample output:

[root@server1 tomcat7]# sh ./bin/
Using CATALINA_BASE: /var/lib/tomcat7
Using CATALINA_HOME: /var/lib/tomcat7
Using CATALINA_TMPDIR: /var/lib/tomcat7/temp
Using JRE_HOME: /opt/jdk1.8.0_131/jre
Using CLASSPATH: /var/lib/tomcat7/bin/bootstrap.jar:/var/lib/tomcat7/bin/tomcat-juli.jar
Tomcat started.

Access Tomcat on browser:

Tomcat server works on port 8080 default. To access Tomcat on the web browser by connecting your server on port 8080:


Setup Users Account

Finally we need to create user accounts to secure and access admin/manager pages. Edit conf/tomcat-users.xml file in your editor and paste inside tags.

Stop Tomcat

Finally, if you feel that there are no need of Tomcat in your system, You can simply stop it using below command from tomcat home directory.
sh ./bin/


Install Nginx:

yum install epel-release
yum install nginx

Starting Nginx
/etc/init.d/nginx start
Configuring Nginx and Tomcat7

server {
listen 80;
root /var/lib/tomcat7/webapps/;
location / {
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Forwarded-Server $host;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

source :

Install Java 8 on Centos 6.8

Posted on


Download Java SDK 8

You need to add option --no-cookies --no-check-certificate --header "Cookie:; oraclelicense=accept-securebackup-cookie" to download

example with linux-64

wget --no-cookies --no-check-certificate --header "Cookie:; oraclelicense=accept-securebackup-cookie" ""
tar xzf jdk-8u131-linux-x64.tar.gz
mv jdk1.8.0_131 /opt/

Install Java with Alternatives

After extracting archive file use alternatives command to install it. alternatives command is available in chkconfig package.

cd /opt/jdk1.8.0_131/
alternatives --install /usr/bin/java java /opt/jdk1.8.0_131/bin/java 2
alternatives --config java
There is 1 program that provides 'java'.

Selection Command
*+ 1 /opt/jdk1.8.0_131/bin/java

Enter to keep the current selection[+], or type selection number: 1

At this point JAVA 8 has been successfully installed on your system. We also recommend to setup javac and jar commands path using alternatives

alternatives --install /usr/bin/jar jar /opt/jdk1.8.0_131/bin/jar 2
alternatives --install /usr/bin/javac javac /opt/jdk1.8.0_131/bin/javac 2
alternatives --set jar /opt/jdk1.8.0_131/bin/jar
alternatives --set javac /opt/jdk1.8.0_131/bin/javac

Check Installed Java version

[root@server1 jdk1.8.0_131]# java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)

Configuring Enviroment Variables:

  • Setup JAVA_HOME: 
    export JAVA_HOME=/opt/jdk1.8.0_131
  • Setup JRE_HOME: 
    export JRE_HOME=/opt/jdk1.8.0_131/jre
  • Setup PATH: 
    export PATH=$PATH:/opt/jdk1.8.0_131/bin:/opt/jdk1.8.0_131/jre/bin

Also put all above environment variables in /etc/environment file for auto loading on system boot