centOS mysql 연결안됨.

리눅스에 설치되어 있는 mysql을 재시작했지만.. 

홈페이지에서 DB를 사용할 수 없었다..

음.. 내가 얼마전에 계정권한을 변경하였는데.. 그것 때문인가 싶었지만.. 

한동안 해결을 못했었다.

DB가 8개 였다면 계정권한 수정 후 3개의 DB만 볼 수 있었고..

계정정보도 보려고 했지만 로그인한 계정의 정보 밖에 볼 수 없었다.

또 로그인을 할 때도 

mysql -uroot -p

Enter password: 패스워드 입력

이렇게 로그인을 했었는데.. 이렇게도 안되서

mysql -h주소 -u계정ID -p계정PW

이렇게 해서 로그인을 했다.

▶ 사용자 확인

mysql> use mysql;

mysql> select user, host from user; 이것도 use mysql; 하면 에러가 남.

계정권한을 변경하려고 했다가 우선 기존의 방법대로 로그인이 가능하도록 바꿔야 겠다는 생각이 들었다.

 → quit 명령어로 mysql을 빠져나온 후

1. 

1-1. /etc/init.d/mysqld stop

1-2. mysqld_safe --skip-grant &

1-3. Starting mysqld daemon with databases from /var/lib/mysql

1-4. quit

2.

2-1. mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2-2.

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

2-3.

mysql> update user set password=password('PW입력') where user='계정ID';

Query OK, 1 row affected (0.02 sec)

Rows matched: 5  Changed: 1  Warnings: 0

2-4.

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

2-5.

mysql> quit

Bye

[2]+  Exit 1                  mysqld_safe --skip-grant

3.

3-1.

[root@a~]# mysql -u계쩡ID -p

Enter password:PW입력

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ok로그인됨.

mysql>  SHOW DATABASES;

mysql> use mysql

mysql> select host,user,password from user;

이거 결과다 제대로 나옴.

근데도 DB연결이 안되다고 함.

mysql> GRANT ALL PRIVILEGES ON DB명.* TO '계정ID'@'%' IDENTIFIED BY '계정PW' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

(전에도 계속

mysql> GRANT ALL PRIVILEGES ON *.* TO '계정ID'@'%' IDENTIFIED BY '계정PW' WITH GRANT OPTION;

이렇게 했었는데.. 이렇게 했을 때는 DB연결이 안됐었다..

)

이렇게 하고 다시 홈페이지보니 잘 됨.

http://stackoverflow.com/questions/8348762/access-denied-when-trying-to-access-database-using-root-issue

access denied when trying to access database using root issue

up vote2down votefavorite

I have the following error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'root'@'%' to database 'GUEST_BOOK' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) previously I have done: GRANT ALL PRIVILEGES ON GUEST_BOOK.* TO 'root@%' IDENTIFIED BY 'D7n()st1234' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) FLUSH PRIVILEGES; not sure why it is still giving me that permission error? Any idea?

2down voteaccepted

'root@%' is different than 'root'@'%' in SQL terms Essentially writing 'root@%' probably created an entry for a user called 'root@%' and not a user named root who would be accessing via any means (%) Update your grant statement to read GRANT ALL PRIVILEGES ON GUEST_BOOK.* TO 'root'@'%' IDENTIFIED BY 'D7n()st1234' WITH GRANT OPTION;

http://chunun.com/entry/MySQL-1045%EC%97%90%EB%9F%AC-%EB%B0%9C%EC%83%9D-mysql%EC%9D%84-root%EB%A1%9C-%EC%A0%91%EC%86%8D%ED%95%98%EC%A7%80-%EB%AA%BB%ED%95%98%EB%8A%94-%EA%B2%BD%EC%9A%B0-%ED%95%B4%EA%B2%B0-%EB%B0%A9%EB%B2%95

---

20160407 같은 문제가 발생 했다. 

어.. 위의 방식으로 했지만 .. 특정 데이터베이스를 이용하는 웹페이지는 실행되지 않았다.

(워드프레스)

음.. 그래서.. 뭘까 하다가 관리자 페이지로 로그인을 하니까.

" 하나 이상의 데이터베이스 테이블이 불가능합니다. 데이터베이스는 수리가 필요할 수도 있습니다. "

라고 ..화면에 보였다..

워드프레스의 데이터베이스를 한번도 들여다 본적이 없어서.. 뭘 어떻게 해야 하나.. 하다가

무작정 토드로 접속을 해봤다. 

오..일단 접속은 했고. 테이블을 하나씩 열어봤더니.

하나의 테이블에서 에러가 보였다.

Table './테이블명' is marked as crashed and should be repaired

음.. 검색.

http://forcecore.tistory.com/428

http://www.databasejournal.com/features/mysql/article.php/10897_3300511_2

따라해본다. 

mysql 접속 후에

에러가 나는 테이블을 백업해두고

mysql> repair table product; //"product"는 오류가 생긴 테이블 입니다.

mysql>analyze table product;를 실행하면 테이블에 대한 보고서가 작성됩니다.

mysql>repair table product;를 실행하면 복원이 됩니다.

이렇게 하고 웹페이지 보니.. 아직 그대로

 myisamchk -r 테이블파일 

도 따라해봤지만 그대로..

 myisamchk -r 테이블파일 하기전에 

quit 를 통해 mysql에서 빠져 나오고

myisamchk -r /var/lib/mysql/데이터베이스명/깨진테이블명

하고 웹페이지 접속하니 화면이 잘 나온다.

다행..이다..ㅜ.ㅠ 

[root@plantid ~]# myisamchk -r /var/lib/mysql/데이터베이스명/깨진테이블명

- recovering (with sort) MyISAM-table '/var/lib/mysql/데이터베이스명/깨진테이블명'

Data records: 380

- Fixing index 1

- Fixing index 2

Data records: 376

http://forcecore.tistory.com/428

위키 글을 쓰다가 깜짝 놀랐다.
글을 적고나니까 이런 메시지가 출력되었다:

데이터베이스 쿼리 구문 오류가 발생했습니다. 소프트웨어의 버그가 있을 수 있습니다. 마지막으로 요청한 데이터베이스 쿼리는 "SearchMySQL4::update" 함수에서 쓰인
    (SQL query hidden)
입니다. MySQL은 "145: Table './wikidb/eedoc_searchindex' is marked as crashed and should be repaired (localhost)" 오류를 냈습니다.

어쩌노 <(-_-)>

http://blueb.net/blog/346
다행히 누가 이미 겪었다.
일단 자료 백업을 해놓고 들어가시오. DB의 바이너리파일 어딘가 복사.

DB에서
mysql> repair table product; //"product"는 오류가 생긴 테이블 입니다.
하시기 전에
mysql>analyze table product;를 실행하면 테이블에 대한 보고서가 작성됩니다.
그리고 나서
mysql>repair table product;를 실행하면 복원이 됩니다.

라고 한다.
그래도 안 통하는 경우가 있다.
필자는 통하는 경우, 안 통하는 경우 둘 다 겪어봤다 ㅠㅠ;;

역시  반드시 백업을 해두고...
http://www.databasejournal.com/features/mysql/article.php/10897_3300511_2

$ myisamchk -r 테이블파일

을 수행시킨다.
필자는 여기서 살릴 수 있었다...

http://www.databasejournal.com/features/mysql/article.php/10897_3300511_2

Featured Database Articles

MYSQL

Posted Jan 21, 2004

Repairing Database Corruption in MySQL - Page 2

By Ian Gilfillan

Repairing tables

In most cases, only the index will be corrupted (the index is a separate, smaller, file with records that point to the main data file) - actual data corruption is extremely rare. Fixing most forms of corruption is relatively easy. As with checking, there are three ways to repair tables. These all only work with MyISAM tables - to repair corruption of the other table types, you will need to restore from backup:

• The REPAIR TABLE SQL statement (obviously the server must be running for this)
• The mysqlcheck command-line utility (the server can be running)
• The myisamchk command-line utility (the server must be down, or the tables inactive)

Repairing a table requires twice as much disk space as the original table (a copy of the data is made), so make sure you are not going to run out of disk space before you start.

Repairing a table with REPAIR TABLE

The syntax is, as would be expected, REPAIR TABLE tablename[,tablename1...] [options]. This method only works with MyISAM tables. The following options are available.

QUICK	The quickest, as the data file is not modified.
EXTENDED	Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.
USE_FRM	To be used if the .MYI file is missing or has a corrupted header. Uses the .frm file definitions to rebuild the indexes.

In most cases, a simple REPAIR without any options should work fine. An unusual case is when the .MYI is missing. Here is what would happen:

mysql> REPAIR TABLE fixtures;
+-------------------------+--------+----------+---------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                    |
+-------------------------+--------+----------+---------------------------------------------+
| sports_results.fixtures | repair | error    | Can't find file: 'fixtures.MYI' (errno: 2)  |
+-------------------------+--------+----------+---------------------------------------------+

The repair has failed because the index file is missing or has a corrupted header. To use the definition file to repair, use the USE_FRM option, as follows:

mysql> REPAIR TABLE fixtures USE_FRM;
+-------------------------+--------+----------+------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                           |
+-------------------------+--------+----------+------------------------------------+
| sports_results.fixtures | repair | warning  | Number of rows changed from 0 to 2 |
| sports_results.fixtures | repair | status   | OK                                 |
+-------------------------+--------+----------+------------------------------------+

Everything has gone smoothly this time, as indicated by the OK Msg_text.

Repairing tables with mysqlcheck

The mysqlcheck command-line utility can be used while the server is running, and, like all the methods of repair, only works with MyISAM tables. The syntax is:

%mysqlcheck -r sports_results fixtures -uuser -ppass
sports_results.fixtures                            OK

You can also repair multiple tables in a database, by listing them after the database name, or all tables in a database by just passing the database name, for example:

%mysqlcheck -r sports_results fixtures events -uuser -ppass
sports_results.fixtures                            OK
sports_results.events                              OK

%mysqlcheck -r sports_results -uuser -ppass
sports_results.fixtures                            OK
sports_results.events                              OK
...

Repairing tables with myisamchk

The server must be down, or the tables inactive (which is ensured if the --skip-external-locking option is not in use). The syntax is myisamchk [options[ [tablenames]. Remember again that you must be in, or specify, the path to the relevant .MYI files. The following options are available:

--backup, -B	Makes a .BAK backup of the table before repairing it
--correct-checksum	Corrects the checksum
--data-file-length=#, -D #	Specifies the maximum length of the data file, when recreating
--extend-check, -e	Attempts to recover every possible row from the data file. This option should not be used except as a last resort, as it may produce garbage rows.
--force, -f	Overwrites old temporary .TMD files instead of aborting if it encounters a pre-existing one.
keys-used=#, -k #	Can make the process faster by specifying which keys to use. Each binary bit stands for one key starting at 0 for the first key.
--recover, -r	The most commonly used option, which repairs most corruption. If you have enough memory, increase the sort_buffer_size to make the recover go more quickly. Will not recover from the rare form of corruption where a unique key is not unique.
--safe-recover, -o	More thorough, yet slower repair option than -r, usually only used only if -r fails. Reads through all rows and rebuilds the indexes based on the rows. This also uses slightly less disk space than a -r repair since a sort buffer is not created. You should increase the key_buffer_size value to improve repair speed if there is available memory.
--sort-recover, -n	MySQL uses sorting to resolve the indexes, even if the resulting temporary files are very large.
--character-sets-dir=...	The directory containing the character sets
--set-character-set=name	Specifies a new character set for the index
--tmpdir=path, -t	Passes a new path for storing temporary files if you dont want to use the contents of the TMPDIR environment variable
--quick, -q	The fastest repair, since the data file is not modified. A second -q will modify the data file if there are duplicate keys. Also uses much less disk space since the data file is not modified.
--unpack, -u	Unpacks a file that has been packed with the myisampack utility.

An example of its usage:

% myisamchk -r fixtures
- recovering (with keycache) MyISAM-table 'fixtures.MYI'
Data records: 0

I hope that you will never have to do any repairing, but I am sure at least some of you reading this article (at least those who got this far) are here precisely because you've got some corruption. I hope that your repairs will be as painless as all mine have been. Good luck!