4. MySQL

yum -y install mysql mysql-*

-----> 5.1 버전으로 깔림

 

# error

MySQL: Error 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)

 

I’ve upgraded MySQL server version to 5.1.54-1ubuntu4 and when I try to connect to the database I’m getting this error:

1 2 3

$ mysql -u root Error 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Maybe updating the package the updater overwrote the root password.

To restore it:

• Stop mysqld deamons.

1	$ sudo service mysql stop

• Go to mysql/bin directory

1	$ cd /usr/bin

• Start a mysql deamon with this option:

1	$ sudo mysqld_safe --skip-grant-tables

• Open another terminal and open a mysql session to execute this:

1 2 3 4

$ mysql mysql> use mysql; mysql> UPDATE user SET password=PASSWORD('YOUR_NEW_PASSWORD_HERE') WHERE user = 'root'; mysql> exit;

• Now kill the mysqld_safe process and restart mysqld normally:

1	$ sudo service mysql start

 

 

MySQL 5.6 버전으로 다시 깔기

http://blog.naver.com/PostView.nhn?blogId=goddes4&logNo=30182829115

 

5.6버전 -- 추가해야될 내용

 

ALTER table mysql.user
ADD `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N' AFTER `Trigger_priv`,
ADD `plugin` char(64) COLLATE utf8_bin DEFAULT '',
ADD `authentication_string` text COLLATE utf8_bin;

 

 

UPDATE mysql.user set Create_tablespace_priv='Y' where user='root';

 

DDL, DML 쿼리 실행

 

 

Error:

Below issue generally comes with database incompatibilities with the current version of MySQL. It may happen due to MySQL server upgrade or downgrade.

Error in query (1548): Cannot load from mysql.proc. The table is probably corrupted

Solution:

This issue may resolve by repairing mysql databse using following command.

# mysqlcheck -r mysql proc -u root -p
Enter password:
mysql.proc                                         OK

In case you are still getting same error while accessing proc table, You need to execute mysql_upgrade command to fix issue. Read more about mysql_upgrade

# mysql_upgrade -u root -p

 

 

Linux --- 쿼리문 대소문자를 구별함...

구별안하게 수정

/etc/my.cnf

lower_case_table_names=1

확인) show variables like 'lower_case_table_names';

 

 

## 생성한 db에 접근권한설정 (naonekp)

--> grant select on *.* to root@'localhost' identified by 'password';

Grant 를 통한 계정 부여 방법
- 전체 데이터베이스에 한해서 권한 부여
- grant를 이용하면, flush privileges;(권한 갱신) 를 따로 해줄 필요 없음.

Ex) grant select on *.* to aaa@'localhost' identified by '1234';
- 아이디 aaa 에 패스워드 1234를 가진 계정에 select 기능만을 부여.
- *.* ( DB_NAME.TABLE_NAME) == 모든 데이터 베이스, 모든 테이블에 권한 부여

Ex) grant select on test05.student to bbb@'localhost' identified by '4444';
- 아이디 aaa에 패스워드 4444를 가진 계정은 test05의 student 테이블만 select 할수 있음.

 

 

 

MySQL -- tomcat 연동시

 -->  mysql connector _ jar파일 톰캣 lib에 복사