Ubuntu에 MySQL 설치

OS/Linux 2014.05.26 17:28 Posted by 파란크리스마스

출처 : mysql - 외부접속 허용하기 
Install/Upgrade to MySQL 5.6 on Ubuntu 12.04 LTS
LINUX에 MySQL 소스 코드로 컴파일하여 설치 & 실행하기
[설치] MySQL 5.5

필요한 라이브러리 설치

sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

다운로드

$ wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.3-m13.tar.gz

MySQL 계정 만들기

sudo groupadd mysql
sudo useradd -g mysql mysql
sudo passwd mysql
sudo mkdir /home/mysql
sudo chown mysql:mysql /home/mysql
tar zxvf mysql-5.7.3-m13.tar.gz
cd mysql-5.7.3-m13

MySQL 소스 컴파일

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_SSL=system 

잘 모르는 옵션이라 빼둠

-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.socket 


sudo make
sudo make install

디렉토리 권한 변경

sudo chown -R mysql:mysql /usr/local/mysql

MySQL Database 생성

$ cd /usr/local/mysql
$ sudo scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 

부팅시 자동 시작 설정

$ sudo cp support-files/mysql.server /etc/init.d/mysqld

/etc/init.d/mysqld 수정 - parse_server_arguments 주석처리 

$ sudo vi /etc/init.d/mysqld

# 주석처리
#parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`

MySQL실행 

$ service mysqld start

부팅시 자동 시작 설정

$ sudo update-rc.d mysqld defaults
$ sudo apt-get install sysv-rc-conf
$ sudo sysv-rc-conf --list

root 암호 설정

$ sudo service mysqld stop
$ sudo bin/mysqld_safe --skip-grant-tables --old-passwords &
mysql> use mysql;
mysql> update user set password=password('sqldba') where user = 'root';
mysql> flush privileges;
mysql> quit
$ sudo service mysqld start
$ sudo bin/mysql -u root -p

ssh 데몬 설치

sudo apt-get install openssh-server
sudo init 6 (재부팅)

MySQL - SQLGate로 SSH 터널링을 통해 연결하기

신고

로컬 PC에서 MySQL 접속이 되지 않을때

Database 2014.01.21 15:55 Posted by 파란크리스마스

출처 : 리눅스 종류 확인, 리눅스 버전 확인
MySQL: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

리눅스버전확인

[root@testserver init.d]# cat /etc/issue
CentOS release 5.9 (Final)
Kernel \r on an \m

로컬 PC에서 접속이 되지 않을때

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

MySQL 서비스 종료

[root@testserver init.d]# service mysqld stop
mysqld 를 정지 중:                                         [  OK  ]

권한 테이블 없이 MySQL 실행하기

[root@testserver init.d]# mysqld_safe --skip-grant-tables &
[1] 4712
[root@testserver init.d]# Starting mysqld daemon with databases from /var/lib/mysql

MySQL 접속

[root@testserver init.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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.

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

기존 root@localhost 사용자가 존재하고 있으면 패스워드만 변경 (패스워드만 분실했을 경우)

mysql> update user set `Password`=PASSWORD('root12');
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

사용자 추가 (권한 변경은 MySQL 재시작후 실행)

mysql> INSERT INTO `user` (`host`, `user`, `password`) VALUES('localhost','root',PASSWORD('root12'));
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> grant all privileges on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MySQL 반영 (내장 캐쉬를 소거)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

MySQL 서비스 다시 종료

[root@testserver init.d]# /etc/init.d/mysqld stop
STOPPING server from pid file /var/run/mysqld/mysqld.pid
140121 16:02:23  mysqld ended

mysqld 를 정지 중:                                         [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables

MySQL 서비스 시작

[root@testserver init.d]# /etc/init.d/mysqld start
mysqld (을)를 시작 중:                                     [  OK  ]

신고
TAG MySQL

JDBC 연결

Programming/Java 2012.06.24 10:08 Posted by 파란크리스마스

MySQL JDBC 연결

import java.sql.Connection;
import java.sql.DriverManager;

public class Test {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName("org.gjt.mm.mysql.Driver"); 
      conn = DriverManager.getConnection("jdbc:mysql://localhost:5515/dbname?user=id&useUnicode=true&characterEncoding=UTF8", "id", "pw");
    } catch (Exception e) {
      e.printStackTrace();
      if (conn!=null) try { conn.close(); } catch (Exception e2) { }
    }
  }
}

Oracle thin

import java.sql.Connection;
import java.sql.DriverManager;

public class Test {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
      conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
      
      System.out.println(conn);
      
    } catch (Exception e) {
      e.printStackTrace();
      if (conn!=null) try { conn.close(); } catch (Exception e2) { }
    }
  }
}

Oracle OCI

import java.sql.Connection;
import java.sql.DriverManager;

public class TestOCI {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
      conn = DriverManager.getConnection("jdbc:oracle:oci:@xe", "scott", "tiger");
      
      System.out.println(conn);
      
    } catch (Exception e) {
      e.printStackTrace();
      if (conn!=null) try { conn.close(); } catch (Exception e2) { }
    }
  }
}
신고

PHP, phpMyAdmin 설치

Programming/Apache, PHP 2011.11.20 16:47 Posted by 파란크리스마스

Path 추가

C:\server\php-5.2.17-Win32-VC6-x86
C:\server\php-5.2.17-Win32-VC6-x86\ext

C:\server\Apache2.2\conf\httpd.conf 수정


# For PHP 5 do something like this:
LoadModule php5_module "C:/server/php-5.2.17-Win32-VC6-x86/php5apache2_2.dll"

Alias /mysql "C:/server/phpMyAdmin-3.4.6-all-languages"

-- 생략 --
 
<Directory "C:/server/phpMyAdmin-3.4.6-all-languages">
 Options FollowSymLinks
 DirectoryIndex index.html index.htm index.php *.jsp *.php
 Order allow,deny
 Allow from all
</Directory>

-- 생략 --

    AddType application/x-httpd-php .php .php3 .html .htm .phtml
    AddType application/x-httpd-php-source .phps

php.ini 파일 생성

php.ini-recommended 파일 C:\server\Apache2.2\bin 폴더에 복사

C:\server\Apache2.2\bin\php.ini 수정 (주석제거)

extension=php_bz2.dll
extension=php_mcrypt.dll
extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_openssl.dll
extension=php_pdo_mysql.dll
extension=php_zip.dll

config.inc.php 생성

C:\server\phpMyAdmin-3.4.6-all-languages\config.sample.inc.php 파일 config.inc.php 로 이름 변경

config.inc.php 수정

<?php
/*
 * Generated configuration file
 * Generated by: phpMyAdmin 3.4.6 setup script
 * Date: Tue, 18 Oct 2011 17:43:55 +0900
 */

/* Servers configuration */
$i = 0;

/* Server: cmxdb [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = 'mysql';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = 3306;
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

/* End of servers configuration */

$cfg['blowfish_secret'] = '';
$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
?>

신고

1. 다운로드(mysql-5.5.12-win32.zip)
http://dev.mysql.com/downloads/mysql/5.5.html

2. 압축풀기
압축폴더 : C:\mysql\mysql-5.5.12-win32

3. mysql.ini 파일 작성
경로 : C:\mysql\mysql-5.5.12-win32\mysql.ini
----------------------------------------------------
[mysqld]
port=2233
basedir=C:\mysql\mysql-5.5.12-win32
datadir=C:\mysql\mysql-5.5.12-win32\data
character-set-server=utf8
collation-server=utf8_general_ci

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8
----------------------------------------------------

4. MySQL 서버 NT 서비스에 등록
- 명령 프롬프트 관리자 권한으로 실행

---------------------------------------------------- 
C:\Windows\system32>cd C:\mysql\mysql-5.5.12-win32

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



5. 서비스 시작하기

----------------------------------------------------
C:\mysql\mysql-5.5.12-win32>net start "MySQL 5.5.12"
MySQL 5.5.12 서비스를 시작합니다..
MySQL 5.5.12 서비스가 잘 시작되었습니다.
----------------------------------------------------

6. 데이터베이스 접속

----------------------------------------------------
mysql -h localhost -u root -P 2233
----------------------------------------------------

7. 데이터베이스 생성

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

8. 사용자 추가

----------------------------------------------------
mysql> use mysql;
Database changed
mysql> create user 'test'@'%' identified by 'test00';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql>
----------------------------------------------------

JDBC 접속 테스트

----------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCConnectTest {

 public static void main(String[] args) throws Exception {
  Connection conn = null;
  
  String jdbc_driver = "org.gjt.mm.mysql.Driver";
  String jdbc_url = "jdbc:mysql://192.168.0.62:2233/db_test";
  
  try {
   Class.forName(jdbc_driver);
   conn = DriverManager.getConnection(jdbc_url, "test", "test00");
   System.out.println(conn);   
   conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}
----------------------------------------------------

NT 서비스 지우기
- 명령 프롬프트 관리자 권한으로 실행

----------------------------------------------------
C:\mysql\mysql-5.5.12-win32>bin\mysqld.exe --remove "MySQL 5.5.12"
Service successfully removed.
----------------------------------------------------

신고

쿠분투(우분투) 10.04 / MySQL 5.5 컴파일/설치/데몬

OS/Linux 2010.07.11 11:56 Posted by 파란크리스마스

필요한 라이브러리 설치

sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

다운로드

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.3-m3.tar.gz/from/http://mysql.he.net/

MySQL 계정 만들기

sudo groupadd mysql
sudo useradd -g mysql mysql
sudo passwd mysql
sudo mkdir /home/mysql
sudo chown mysql:mysql /home/mysql
tar zxvf mysql-5.5.3-m3.tar.gz
cd mysql-5.5.3-m3/

cmake 설치

$ sudo apt-get install cmake

MySQL 소스 컴파일

./configure --prefix=/usr/local/mysql --localstatedir=/data/mysql/data --enable-assembler --enable-static --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock --without-innodb --without-isam --without-ndb-debug --without-debug --with-big-tables --with-extra-charsers=gbk,gb2312,utf8 --with-pthread --with-plugins=partition,innobase,myisammrg --with-charset=utf8 --with-readline

sudo make
sudo make install

디렉토리 생성

sudo mkdir -p /data/mysql/data/
sudo mkdir -p /data/mysql/log/
sudo mkdir /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

my.cof 복사

참조 :
http://blog.naver.com/iamfreeman/50006780132
http://blog.dasom.pe.kr/35

sudo cp /usr/local/mysql/share/mysql/my-medium.cnf /data/mysql/my.cnf

디렉토리 권한 변경

sudo chown -R mysql:mysql /usr/local/mysql
sudo chown -R mysql:mysql /data/mysql/

MySQL Database 생성 
 
user1@ubuntu:~/mysql-5.5.3-m3$ sudo /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h ubuntu password 'new-password'

Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

user1@ubuntu:~/mysql-5.5.3-m3$

부팅시 자동 시작 설정

sudo cp /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysqld
sudo ln -s /etc/init.d/mysqld /etc/rc2.d/S99mysql
sudo ln -s /etc/init.d/mysqld /etc/rc2.d/K99mysql
sudo ln -s /etc/init.d/mysqld /etc/rc3.d/S99mysql
sudo ln -s /etc/init.d/mysqld /etc/rc3.d/K99mysql

mysqld 수정

sudo vi /etc/init.d/mysqld

1. parse_server_arguments 주석 처리 한다.
=> #parse_server_arguments

2. $bindir/mysqld_safe 수정

      #$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

기존 내용은 주석으로 하고 아래와 같이 추가 한다.

      $bindir/mysqld_safe --defaults-file=/data/mysql/my.cnf 2>&1 > /dev/null &

재부팅

sudo sync
sudo sync
sudo init 6

실행확인

user1@ubuntu:~$ ps -ef | grep mysql
root      2506     1  0 12:09 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/my.cnf
mysql     2738  2506  0 12:09 pts/1    00:00:00 /usr/local/mysql/libexec/mysqld --defaults-file=/data/mysql/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/mysql/plugin --user=mysql --log-error=/data/mysql/data/ubuntu.err --pid-file=/data/mysql/data/ubuntu.pid --socket=/usr/local/mysql/tmp/mysql.sock --port=3306
user1     2751  2104  0 12:09 pts/1    00:00:00 grep --color=auto mysql

user1@ubuntu:~$

root 암호 설정

/usr/local/mysql/bin/mysqladmin -u root password 'root!@' -S /usr/local/mysql/tmp/mysql.sock

MySQL 외부에서 접속하기

user1@ubuntu:~$ /usr/local/mysql/bin/mysql -u root -p -S /usr/local/mysql/tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.3-m3-log Source distribution

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

mysql>  grant all privileges on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql>

ssh 데몬 설치

sudo apt-get install openssh-server
sudo init 6 (재부팅)

MySQL - SQLGate로 SSH 터널링을 통해 연결하기

신고

MySQL - SQLGate로 SSH 터널링을 통해 연결하기

Database 2010.02.17 12:10 Posted by 파란크리스마스
SQLGate2010 For MySQL을 통해서 SSH 터널링을 통해 MySQL 연결하기

1. [SSH 터널링] 체크를 선택하여 SSH 텝을 활성화 시킨다.


2. SSH 접속 정보를 입력한다.


3. [SSH 연결 테스트] 버튼을 선택하여 SSH 연결이 접근이 되는지 확인한다. (옵션)

4. 처음 SSH 서버에 접근시 확인창에서 [OK] 버튼을 선택한다.


5. SSH 연결을 확인 한다.


6. [로그인] 텝으로 이동한다.


7. 로그인 정보를 입력한다.


8. [연결 테스트] 버튼을 선택하여 연결이 잘 되는지 테스트 해본다. (옵션)

9. [연결하기] 버튼을 선택하여 데이터베이스엔 연결한다.
신고

출처 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>

신고
TAG 28000, MySQL

MySQL - 사용자, 그룹, 데이터베이스, 테이블 관리

Database 2009.08.24 01:08 Posted by 파란크리스마스

출처 : 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


신고
TAG MySQL

MySQL 수동설치

Database 2009.08.23 16:08 Posted by 파란크리스마스

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               |
+--------------------+

신고
TAG MySQL

수동으로 MySQL 설치

Database 2007.08.01 08:09 Posted by 파란크리스마스
수동으로 MySQL  설치하는 방법입니다.
 
http://www.mysql.org/downloads/mysql/5.0.html#win32
 
실행

cd C:\mysql-5.0.37-win32\bin

C:\mysql-5.0.37-win32\bin>mysqld.exe
 
설치 확인

cd C:\mysql-5.0.37-win32\bin

C:\mysql-5.0.37-win32\bin>mysqladmin version
mysqladmin  Ver 8.41 Distrib 5.0.37, for Win32 on ia32
Copyright (C) 2000-2006 MySQL AB
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.0.37-community
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 43 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 12  Flush tables: 1  Open tabl
es: 6  Queries per second avg: 0.023
 
mysqlshow

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

C:\mysql-5.0.37-win32\bin>mysqlshow test
Database: test
+--------+
| Tables |
+--------+
+--------+
 
MY_DB라는 데이타베이스 생성

C:\mysql-5.0.37-win32\bin>mysqladmin -uroot create MY_DB

C:\mysql-5.0.37-win32\bin>mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| test               |
+--------------------+
 
중지

C:\mysql-5.0.37-win32\bin>mysqladmin -u root shutdown
 
PATH 에 추가

C:\mysql-5.0.37-win32\bin
 
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
 
NT 서비스 등록

C:\mysql-5.0.37-win32\bin>mysqld-nt.exe --install MySQL --defaults-file=%WINDIR%\mysql.ini
Service successfully installed.
 
NT 서비스 제거

C:\mysql-5.0.37-win32\bin>mysqld-nt.exe --remove
Service successfully removed.
신고


 

티스토리 툴바