파란크리스마스

출처 http://ask.nate.com/qna/view.html?n=4636134

MySQL 4.1.21 설치후 접속 오류 해결

해당오류
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
- ERROR 2003 <HY000>:Can't connect to Mysql server on 'localhost'<10061>
- mysqladmin: connect to server at 'localhost' failed
- error: 'Can't connect to MySQL server on 'localhost' (10061)'

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.21-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Database changed
mysql> update user set password=password('sqldba') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> delete from user where user='';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

출처 : http://www.gpgstudy.com/gpgiki/MySQL%EC%97%90%EC%84%9C%20%EC%82%AC%EC%9A%A9%EC%9E%90%EC%99%80%20%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4%20%EB%A7%8C%EB%93%A4%EA%B8%B0

0. 커맨드 모드 접근

C:\mysql-5.1.37-win32\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.37-community MySQL Community Server (GPL)

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

mysql>

1. 데이터베이스 관리

1-1. 데이터베이스 생성

mysql> create database db_study;
Query OK, 1 row affected (0.00 sec)

1-2. 데이터베이스 목록 조회

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_study           |
| mysql              |
| test               |
+--------------------+

1-3. 데이터베이스 삭제

mysql> drop database db_study;
Query OK, 0 rows affected (0.09 sec)

2. 사용자 관리

2-1. 사용자 추가

mysql> INSERT INTO user (Host,User,Password,
    -> Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,
    -> Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,
    -> Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,
    -> Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv
    -> ,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,
    -> Event_priv,Trigger_priv)
    -> values ('localhost', 'scott', PASSWORD('tiger'),
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO user (Host,User,Password,
    -> Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,
    -> Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,
    -> Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,
    -> Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv
    -> ,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,
    -> Event_priv,Trigger_priv)
    -> values ('%', 'scott', PASSWORD('tiger'),
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

2-2. 사용자 조회

mysql> select host, user, password from user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| 127.0.0.1 | root  |                                           |
| localhost |       |                                           |
| %         | scott | *F2F68D0BB27A773C1D944270E5FAFED515A3FA40 |
| localhost | scott | *F2F68D0BB27A773C1D944270E5FAFED515A3FA40 |
+-----------+-------+-------------------------------------------+
4 rows in set (0.00 sec)

3. 권한부여

3-1. 해당 DB의 사용자에 대한 권한 부여

mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
    -> VALUES('%', 'db_study', 'scott', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
    -> VALUES('localhost', 'db_study', 'scott', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3-2. 조회

mysql> select host, db, user from db;
+-----------+----------+-------+
| host      | db       | user  |
+-----------+----------+-------+
| %         | db_study | scott |
| %         | test     |       |
| %         | test\_%  |       |
| localhost | db_study | scott |
+-----------+----------+-------+
3 rows in set (0.00 sec)

4. 테이블 관리

4-1. 테이블 생성



-end

1. MySQL 다운받기 

경로 : http://dev.mysql.com/downloads/mysql/5.1.html

2. 압축풀기

7z.exe x mysql-noinstall-5.1.37-win32.zip -oC:\

3. C:\Windows\mysql.ini 작성

[mysqld]
# set basedir to your installation path
basedir=C:\mysql-5.0.37-win32
# set datadir to the location of your data directory
datadir=C:\mysql-5.0.37-win32\data

4. 윈도우 서비스에 등록 (관리자 권한으로 실행)

C:\mysql-5.1.37-win32\bin>mysqld.exe --install MySQL --defaults-file=%WINDIR%\mysql.ini
Service successfully installed.

5. 버전확인

C:\mysql-5.1.37-win32\bin>mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.1.37, for Win32 on ia32
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.37-community
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 52 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 15  Flush tables: 1  Open tabl
es: 8  Queries per second avg: 0.19

6. 데이터베이스 목록 조회

C:\mysql-5.1.37-win32\bin>mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| test               |
+--------------------+