mysql

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

Advertisements

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

mysql>...
INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Install MySQL, Nginx, Tomcat7 on centos 6.8

Posted on Updated on

Before installing:
yum update

MySQL

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 (127.0.0.1) by default
  3. Harden MySQL Server
    Run the mysql_secure_installation script to address several security concerns in a default MySQL installation
    mysql_secure_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;
    exit
  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


wget http://mirror.downloadvn.com/apache/tomcat/tomcat-7/v7.0.79/bin/apache-tomcat-7.0.79.tar.gz
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/startup.sh

Sample output:

[root@server1 tomcat7]# sh ./bin/startup.sh
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:
http://your_server_ip:8080

tomcat7-default

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/shutdown.sh

Nginx

Install Nginx:

yum install epel-release
yum install nginx

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

server {
listen 80;
server_name aliviet.vn;
root /var/lib/tomcat7/webapps/aliviet.vn;
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;
proxy_pass http://127.0.0.1:8080/aliviet.vn/;
}
}

source :