MySQL 각종 에러 해결 방법

<< 2013년 1월 9일 최초 작성 >>

1.

- 에러 : ERROR 145 (HY000): Table './mysql/proc' is marked as crashed and should be repaired

- 해결 : mysql> repair table proc

 

2.

- 에러 : ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

- 해결 : shell> mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root mysql -p

 

3. 2013년 1월 16일 정확한 에러 메세지 추가

- 에러 : rpm 설치시 아래와 같은 에러가 발생시

shell> rpm -Uvh MySQL-server-5.5.28-1.linux2.6.x86_64.rpm

Preparing...                ########################################### [100%]

        file /usr/share/mysql/charsets/README from install of MySQL-server-5.5.28-1.linux2.6.x86_64 conflicts with file from package mysql-libs-5.1.61-4.el6.x86_64

- 해결 :

shell> rpm -qa | grep -i '^mysql-'

shell> rpm --nodeps -ev <<package name>>

 

4.

- 에러 : ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

- 해결 :

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

 

5. 2013년 1월 21일 추가, 2014년 7월 30일 해결방법 수정 및 내용추가

- 에러 : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

- 해결방법 1. CentOS기반 MySQL 5.5.28

shell> mysqld --skip-grant & [2] 27941 130121 18:30:39 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!   130121 18:30:39 [ERROR] Aborting   130121 18:30:39 [Note] mysqld: Shutdown complete [2]-  Exit 1                  mysqld --skip-grant shell> mysql -uroot Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.28-log MySQL Community Server (GPL)   Copyright (c) 2000, 2012, 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.   mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'newpassword' with grant option;

 

 

해결방법 2. Ubuntu기반 MySQL 5.6.17

shell> ps -ef | grep mysql root      3309     1  1 02:00 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/dbdata/data --pid-file=/dbdata/data/mysql.pid mysql     3827  3309 11 02:00 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/dbdata/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/dbdata/log/mysql-err.err --pid-file=/dbdata/data/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306 root      3856  2672  0 02:00 pts/0    00:00:00 grep --color=auto mysql shell> service mysql stop Shutting down MySQL ..* shell> mysqld_safe --skip-grant --datadir=/dbdata/data --pid-file=/dbdata/data/mysql.pid &   shell> mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.17-log MySQL Community Server (GPL)    Copyright (c) 2000, 2014, 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.   mysql> use mysql Database changed mysql> update user set password=password('newpassword') where user='root'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4  Changed: 4  Warnings: 0   mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)   mysql> commit; Query OK, 0 rows affected (0.00 sec)

계속해서 추가할 예정.