°ü¸®ÀÚ  |
2015-01-14 23:30:21 |
Hit:230979 |
|
|
÷ºÎÆÄÀÏ #1: mysql¿¡·¯2.txt (24.0 KB), ´Ù¿î·Îµå: 9
http://cafe.naver.com/linuxcare/47305
MYSQL¿¡·¯2 | ÆÁ/±â¼úÁ¤º¸¡¹ 2012.11.02 11:00
[Ãâó] MYSQL¿¡·¯2 (³×À̹ö ¸®´ª½º À¯Àú ±×·ì) |ÀÛ¼ºÀÚ ¹«¸íme (http://cafe.naver.com/ArticleRead.nhn?articleid=47305&clubid=10098758#)
MYSQL¿¡·¯2
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
http://gywn.net/2011/12/mysql-installation-on-linux/
¸®´ª½º¿¡ MySQL ¼³Ä¡Çϱâ(CentOS 5.6)
MySQL DBMS ¸¦ ¼³Ä¡ÇÒ ¶§ Á¦°¡ Àû¿ëÇÏ´Â ³»¿ëÀ» °øÀ¯ÇÕ´Ï´Ù.
root °èÁ¤À¸·Î ¼³Ä¡ Áغñ¸¦ Çϰí, mysql °èÁ¤À¸·Î DB¸¦ ±¸µ¿ÇÕ´Ï´Ù.
ÀÏ´Ü ÇÏ´Ü ³»¿ëµéÀº root°èÁ¤À¸·Î ¼öÇàÀ» ÇÕ´Ï´Ù.
OS °èÁ¤ Ãß°¡
´ÙÀ½°ú °°ÀÌ dba ±×·ìÀ» Ãß°¡ÇÏ°í ±× ¹Ø¿¡ mysql °èÁ¤À» Ãß°¡ÇÕ´Ï´Ù.
groupadd -g 600 dba
useradd -g 600 -u 605 mysql
passwd mysql
Linux ¼³Á¤ º¯°æ
¼¼¼Ç Limit ¸¦ ¼³Á¤ÇÕ´Ï´Ù.
vi /etc/security/limits.conf
##ÇÏ´Ü ³»¿ë Ãß°¡
mysql soft nproc 8192
mysql hard nproc 16384
mysql soft nofile 8192
mysql hard nofile 65536
OS¿¡¼ limits.conf ÆÄÀÏÀ» ÀоîµéÀ̵µ·Ï ¼³Á¤ÇÕ´Ï´Ù. ¾øÀ¸¸é »ý¼ºÇÕ´Ï´Ù.
vi /etc/pam.d/login
## ÇÏ´Ü ³»¿ë Ãß°¡
session required pam_limits.so
/etc/profile ¿¡ ´ÙÀ½ ³»¿ëÀ» Ãß°¡ÇÏ¿© login ½Ã Àû¿ëµÇµµ·Ï ÇÕ´Ï´Ù.
vi /etc/profile
##
if [ $USER = "mysql" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
MySQL µ¥ÀÌÅÍ ÀúÀå µð·ºÅ丮¸¦ »ý¼ºÇÕ´Ï´Ù.
mkdir -p /data/mysql/mysql-data
mkdir -p /data/mysql/mysql-tmp
mkdir -p /data/mysql/mysql-iblog
mkdir -p /data/mysql/mysql-binlog
MySQL ¼³Ä¡ ÆÄÀÏ ´Ù¿î·Îµå
ÇÏ´Ü ½ÇÇà ½Ã x86_64 °¡ ÀÖÀ¸¸é 64ºñÆ®À̰í, i686 ÀÌ ÀÖÀ¸¸é 32ºñÆ®ÀÔ´Ï´Ù.
## OS ¹öÀü È®ÀÎ ##
uname -a
Linux ..Áß·«.. EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
ÀÌÁ¦ MySQL Download ÀÇ ¡°Linux – Generic¡± ÅÇ¿¡¼ ÀÚ½ÅÀÇ OS¿¡ ¸Â´Â MySQL Server ¹ÞÀ¸¼¼¿ä.
ÇöÀç ReleaseµÇ´Â ÁÖ ¹öÀüÀº MySQL 5.5.xÀ̳ª, ¿©±â¼´Â MySQL 5.1.57 64ºñÆ® ¹öÀüÀ¸·Î ¼³¸íµå¸®°Ú½À´Ï´Ù.
±¼¿åÀûÀÌÁö¸¸, Çѱ¹º¸´Ù´Â ÀϺ» mirror¼¹ö¿¡¼ ¹Þ´Â °ÍÀÌ ºü¸£´Ù´Â..-_-;;
cd /usr/local/
## ¼³Ä¡ ÆÄÀÏ ´Ù¿î·Îµå
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/
mysql-5.5.19-linux2.6-x86_64.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/
ÀÌ·¸°Ô ¸í·É¾î¸¦ ¾È¾²°í, ±×³É ±×»çÀÌÆ®¿¡ µé¾î°¡¼ ¹Þ¾Æµµ µË´Ï´Ù.
MySQL ±âº» ¼³Á¤
½Ã½ºÅÛ¿¡ µû¶ó µ¥ÀÌÅÍ ÆÄÀϰú °°Àº ÀϺΠº¯¼ö °ªÀÌ ´Þ¶óÁú ¼ö ÀÖÀ¸´Ï, ÀÚ½ÅÀÇ ½Ã½ºÅÛ¿¡ ¸Â°Ô ¼öÁ¤Çؼ »ç¿ëÇϼ¼¿ä.
vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock
back_log = 100
max_connections = 500
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
bulk_insert_buffer_size = 16M
thread_cache_size = 128
thread_concurrency = 16
query_cache_type = 0
default_storage_engine = innodb
thread_stack = 192K
lower_case_table_names = 1
max_heap_table_size = 128M
tmp_table_size = 128M
local_infile = 0
max_prepared_stmt_count = 256K
event_scheduler = ON
log_bin_trust_function_creators = 1
secure_auth = 1
skip_external_locking
skip_symbolic_links
#skip_name_resolve
## config server and data path
basedir = /usr/local/mysql
datadir = /data/mysql/mysql-data
tmpdir = /data/mysql/mysql-tmp
log_bin = /data/mysql/mysql-binlog/mysql-bin
relay_log = /data/mysql/mysql-binlog/mysql-relay
innodb_data_home_dir = /data/mysql/mysql-data
innodb_log_group_home_dir = /data/mysql/mysql-iblog
## config character set
##utf8
character_set_client_handshake = FALSE
character_set_server = utf8
collation_server = utf8_general_ci
init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"
## bin log
binlog_format = row
binlog_cache_size = 4M
## Replication related settings
server_id = 1
expire_logs_days = 7
slave_net_timeout = 60
log_slave_updates
#read_only
## MyISAM Specific options
key_buffer_size = 32M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP
# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 120
innodb_support_xa = 0
innodb_file_io_threads = 8
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive_timeout
[mysqld_safe]
open_files_limit = 8192
MySQL Server ¼³Ä¡
## ¾ÐÃà ÇØÁ¦
cd /usr/local
tar xzvf mysql-5.5.19-linux2.6-x86_64.tar.gz
## °ü¸®¸¦ À§ÇÑ ½Éº¼¸¯ ¸µÅ© »ý¼º
ln -s mysql-5.5.19-linux2.6-x86_64 mysql
## ¼³Ä¡ ÆÄÀÏ ±ÇÇÑ º¯°æ
chown -R mysql.dba /usr/local/mysql*
## ½ÃÀÛ ½ºÅ©¸³Æ® º¹»ç
cp mysql/support-files/mysql.server /etc/init.d/mysqld
## °ü·Ã ÆÄÀÏ ±ÇÇÑ ¼³Á¤
chown mysql.dba /data/mysql/*
chown mysql.dba /etc/my.cnf
chown mysql.dba /usr/local/mysql*
¿©±â¼ºÎÅÍ´Â ÀÌÁ¦ mysql °èÁ¤À¸·Î ½ÇÇàÀ» ÇÕ´Ï´Ù.
°ü¸®¸¦ À§Çؼ ¸î¸î alias¸¦ ¼³Á¤ÇÏ´Â ºÎºÐÀÔ´Ï´Ù.^^
su - mysql
cat >> ~/.bash_profile
## ÇÏ´Ü ³»¿ë ÀÔ·Â
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin:.
export ADMIN_PWD="ROOT ÆÐ½º¿öµå"
alias ll="ls -al --color=auto"
alias mydba="mysql -uroot -p$ADMIN_PWD"
alias mymaster="mysql -uroot -p$ADMIN_PWD -e'show master status;'"
alias myslave="mysql -uroot -p$ADMIN_PWD -e'show slave status\G'"
alias mh="cd $MYSQL_HOME"
alias md="cd /data/mysql/mysql-data"
alias mt="cd /data/mysql/mysql-tmp"
alias mb="cd /data/mysql/mysql-binlog"
alias mi="cd /data/mysql/mysql-data"
alias dp="cd /data/mysql/mysql-data"
## ȯ°æ º¯¼ö Àû¿ë
. ~/.bash_profile
MySQL Server ±¸µ¿
cd /usr/local/mysql
## ±âº» µ¥ÀÌÅͺ£À̽º ¼³Ä¡
./scripts/mysql_install_db
## MySQL µ¥¸ó Startup
/etc/init.d/mysqld start
MySQL º¸¾È ¼³Á¤
óÀ½ DB¸¦ ¿Ã¸®¸é º¸¾È ¸é¿¡¼ Ãë¾àÇÑ ºÎºÐÀÌ ÀÖ½À´Ï´Ù.
±âº»ÀûÀÎ º¸¾È Á¤Ã¥À» Àû¿ëÇϵµ·Ï ÇÕ´Ï´Ù.
mysql root °èÁ¤ ÆÐ½º¿öµå¸¸ ¼³Á¤ÇÏ°í ³ª¸ÓÁö´Â Enter¸¸ Âß Ä¡¸é µË´Ï´Ù.
cd /usr/local/mysql
./bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
... Failed! Not critical, keep moving...
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
ÀÌÁ¦ MySQL DB ¼³Ä¡°¡ ´Ù ³¡³µ½À´Ï´Ù.
http://gywn.net/2011/12/mysql-installation-on-linux/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
mysql ¼³Ä¡ ¿¡·¯ ·Î±×¿Í ÇØ°áÃ¥
±âŸÀúÀå°í/DB 2012/10/18 14:00
¼¼ÆÃ : mysql 5.x¿Í php 5.x , php 4.x°¡ ±ò·ÁÀÖ´Ù.
PHP 4 ¼³Ä¡
PHP4°¡ ¼³Ä¡µÇÀÖ¾úÁö¸¸, ¼³Á¤À» º¸´Ï GD ¶óÀ̺귯¸®°¡ Ãß°¡µÇÀÖÁö ¾Ê¾Æ À缳ġ Çß´Ù. (±½Àº Ç¥½Ã°¡ gd library)
./configure --prefix=/usr/local/php4 \
--with-apxs2=/usr/local/apache_php4/bin/apxs \
--with-mysql=/usr/local/mysql \
--enable-ftp --with-iconv --disable-debug \
--with-openssl --with-mysql \
--with-gd --with-zlib-dir=/usr/lib \
¾ÆÆÄÄ¡ Àç½ÃÀÛ ÈÄ DB connect ¸¦ ½ÃµµÇÏ¿´À¸³ª ¾Æ·¡¿Í °°Àº ¿¡·¯°¡ ¹ß»ýÇß´Ù.
¿¡·¯ ¸Þ¼¼Áö¿Í ÇÔ²² ÇØ°áÃ¥À» Àû¾îº»´Ù.
1.MySQL Error: Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock' (111)
±Ùº»ÀûÀÎ ¿øÀÎÀº ±ÇÇѹ®Á¦ ÀÎ °Í °°´Ù.
³ª´Â /var/lib/mysql/mysql.sockÀÌ ÀÖÀ½¿¡µµ ÀÌ ¸Þ¼¼Áö°¡ Ãâ·ÂµÇ¾ú´Âµ¥..
¹æ¹ý(2)·Îµµ ÇØ°áµÇ¾úÁö¸¸, ¸µÅ©ÆÄÀÏ »ý¼ºº¸´Ù´Â
¾Æ·¡¿Í °°ÀÌ ±ÇÇÑÀ» ¿Ã¹Ù¸£°Ô º¯°æÇØÁÖ´Â °Ô ÇØ´äÀÏ µí ½Í´Ù.
¹æ¹ý(1)
killall mysqld
chmod 755 -R /var/lib/mysql
chown mysql.mysql -R /var/lib/mysql
safe_mysqld --language=korean &
¹æ¹ý(2) /tmp/mysql.sockÀÇ ÆÄÀÏÀ» ¸µÅ©ÇØÁØ´Ù.
rm -f /var/lib/mysql/mysql.sock
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
2.Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (13)
/usr/local/mysql µð·ºÅ丮ÀÇ ±ÇÇѹ®Á¦
º¸¾È»ó ¹®Á¦°¡ ¾ø´Ù¸é ±ÇÇÑÀ» 777·Î º¯°æÇØÁÖÀÚ.
chmod 777 /usr/local/mysql
3. ERROR 1251: Client does not support authentication protocol
requested by server; consider upgrading MySQL client
MySQL Ŭ¶óÀÌ¾ðÆ® ¹öÀüÀ» ¿Ã¸®¶ó´Â°ÅÀÓ. ¾Æ·¡¿Í °°ÀÌ °èÁ¤À» ±¸ Çü½Ä ÆÐ½º¿öµå·Î º¯°æÇØÁÖ¸é µÈ´Ù.
update user set Password=old_password('**') WHERE User='user';
flush privileges;
3¹øÀÇ Çö»ó ¼³¸íÀº.. ÀÎÅÍ³Ý Ã£´Ù°¡ ¾Æ·¡¿Í °°ÀÌ ³ª¿Ô±æ·¡ ±Ü¾î¿Ô´Ù.
Âü°í·Î ³»°¡ ¼³Ä¡ÇÑ È¯°æÀº Mysql 5.x ¹öÀü¿¡ php4.x¿´À½.
MySQL 5 and old clients (like php4)
Warning: mysql_connect(): Client does not support authentication
protocol requested by server; consider upgrading MySQL client
in /blah/somepage.php on line 123
The reason for this warning is because PHP4 can¡¯t communicate with
a MySQL5 server unless it¡¯s using old passwords
(set by using the OLD_PASSWORD() function below.)
There are two simple ways to fix this. The first would be
to upgrade your version of php.
Unless you *really* need php4 (or below?!) upgrade!
I¡¯m not sure php4 is supported any more and
should be considered a security risk.
If you can¡¯t upgrade, just do this in your database:
update mysql.user set password=OLD_PASSWORD('thepassword') where user = 'theusername';
and: flush privileges;
This should do the trick. Be warned, if you issue any GRANT statements
after this you will have to update mysql.user again.
GD ¶óÀ̺귯¸®¸¦ ±òÁö ¾Ê°í ¾Æ·¡¿Í °°ÀÌ configure ÇÏ¸é ¹ß»ýÇÏÁö ¾Ê¾Ò´ø°Å °°Àºµ¥..
./configure --prefix=/usr/local/php4 \
--with-apxs2=/usr/local/apache_php4/bin/apxs \
--with-mysql=/usr/local/mysql \
--enable-ftp --with-iconv \
--disable-debug --with-openssl \
--with-mysql \
GD ¶óÀ̺귯¸® + PHP4 + Mysql 5.x °£ÀÇ ¿¬µ¿¹®Á¦¿Í °ü·ÃÀÖ´Â °ÍÀϱî?? ±×°Í±îÁø Àß ¸ð¸£°Ú´Ù.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Centos ¿¡¼ MySQL 5.5.9 ¼Ò½º¹öÀü ¼³Ä¡
MySQL Add comments
Mar 152011
CMake ¼³Ä¡
MySQL À» ¼Ò½º ¼³Ä¡ÇÒ °æ¿ì¿¡´Â http://www.cmake.org »çÀÌÆ®¿¡ Á¢¼ÓÇÏ¿© cmake ¸¦ ´Ù¿î·Îµå ¹Þ½À´Ï´Ù.
cmake ´Â ¾ÐÃàÀ» ÇØÁ¦ÇÏ¸é ¹Ù·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
tar xvf cmake-2.8.4-Linux-i386.tar.gz
/etc/profile ¿¡ cmake ÀÇ PATH ¸¦ Ãß°¡ÇÏ¿© ÁÝ´Ï´Ù.
vi /etc/profile
¾Æ·¡ÀÇ ³»¿ëÀ» profile ÇÏ´Ü¿¡ Ãß°¡ÇÏ¿© ÁÝ´Ï´Ù.
export PATH=$PATH:/opt/cmake-2.8.4-Linux-i386/bin
º¯°æµÈ profile À» ½Ã½ºÅÛ¿¡ Àû¿ëÇÕ´Ï´Ù.
source /etc/profile
±×·ì ¹× »ç¿ëÀÚ °èÁ¤ »ý¼º
groupadd dba
useradd -g dba mysql
MySQL ¼³Ä¡
¾ÐÃàÀ» ÇØÁ¦ ÇÕ´Ï´Ù.
tar xvf mysql-5.5.9.tar.gz
¾Æ·¡ÀÇ Package ¸¦ ¼³Ä¡ ÇÕ´Ï´Ù.
yum -y install zlib curl
yum -y install gcc g++ cpp gcc-c++
yum -y install openssl openssl-devel
yum -y install ncurses-devel
yum -y install bzip2-devel
yum -y install libtermcap-devel libc-client-devel
yum -y install bison
cmake ¸¦ »ç¿ëÇÏ¿© ¼³Ä¡¸¦ ÁøÇàÇÕ´Ï´Ù.
cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.5.9 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DMYSQL_DATADIR=/data/database/mysql \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
¾Æ·¡ÀÇ ¸í·ÉÀ» ½ÇÇàÇÏ¿© ¼³Ä¡ÇÕ´Ï´Ù.
make
make install
MySQL ÀÇ ¼³Ä¡Æú´õ ±ÇÇÑÀ» º¯°æÇÕ´Ï´Ù.
chown -R mysql:dba /opt/mysql-5.5.9
MySQL ¼³Ä¡µð·ºÅ丮·Î À̵¿ÇÏ¿© ±âº» µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇÕ´Ï´Ù.
cd /opt/mysql-5.5.9
./scripts/mysql_install_db \
--user=mysql \
--datadir=/data/database/mysql \
¼³Á¤ÆÄÀÏÀ» º¹»çÇÕ´Ï´Ù.
cp support-files/my-medium.cnf my.cnf
¼¹ö ±âµ¿½Ã MySQL ÀÚµ¿ ½ÇÇàÀ» À§ÇÏ¿© ¼ºñ½º·Î µî·ÏÇÕ´Ï´Ù.
cp support-files/mysql.server /etc/init.d/mysqld
¼ºñ½º µî·Ï ÆÄÀÏÀ» vi ÆíÁý±â·Î ¿¾î
datadir ºÎºÐÀ» ã¾Æ ¼³Ä¡½Ã ¼³Á¤Çß´ø Æú´õ¸¦ ÁöÁ¤ÇÕ´Ï´Ù.
vi /etc/init.d/mysqld
¾Æ·¡ÀÇ ³»¿ëÀ» Ãß°¡ÇÕ´Ï´Ù.
datadir=/data/database/mysql
MySQL À» ½ÇÇàÇÕ´Ï´Ù.
service mysqld start
#mysqld_safe --datadir=/data/database/mysql &
¼ºñ±âµ¿½Ã ÀÚµ¿À¸·Î ½ÃÀÛÇÒ ¼ö ÀÖµµ·Ï ¾Æ·¡ÀÇ ¸í·ÉÀ» ½ÇÇà½Ãŵ´Ï´Ù.
chkconfig --add mysqld
chkconfig --level 35 mysqld on
chkconfig --list | grep mysqld
mysqladmin À» ½ÇÇàÇÏ¿© root ÀÇ ÆÐ½º¿öµå¸¦ º¯°æÇÕ´Ï´Ù.
mysqladmin -uroot password
mysql ¿¡ Á¤»óÀûÀ¸·Î Á¢¼ÓÇÏ´ÂÁö È®ÀÎ ÇÕ´Ï´Ù.
@@@@@@@@@@@@@@@@@@@@@@@@
/etc/init.d/mysqld stop
/etc/init.d/mysqld start
MySQL ±âº» ¸í·É¾î Á¤¸®
MySQLÀÇ ±âº» ¸í·É¾î¿Í SQL¹® Á¤¸®. ³»°¡ RDBMS¿¡ ¿ÏÀü ¹®¿ÜÇÑÀ̱⠶§¹®¿¡ ±âº» SQLÄõ¸®±îÁö¡¦
Redhat Linux 7.3, MySQL 3.23.58 ¿¡¼ Å×½ºÆ®ÇÔ.
¼³Ä¡´Â Redhat ±âº» Á¦°ø RPMÀ¸·Î Çß´Ù.
µ¥ÀÌÅͺ£À̽º Á¢¼Ó
mysql -u »ç¿ëÀÚ¸í -p dbname
¼³Ä¡ Á÷ÈÄ¿¡´Â root »ç¿ëÀÚ¿¡ ºñ¹Ð¹øÈ£°¡ ¾øÀ¸¹Ç·Î ´ÙÀ½°ú °°ÀÌ Á¢¼ÓÇÏ¿© MySQLÀ» °ü¸®ÇÒ ¼ö ÀÖ´Ù.
mysql -u root mysql
ºñ¹Ð¹øÈ£ º¯°æ
MySQLÀ» ¼³Ä¡ÇÑ Á÷ÈÄ¿¡´Â root °èÁ¤¿¡ ¾ÏÈ£°¡ ÁöÁ¤µÇ¾î ÀÖÁö ¾Ê´Ù. ´ÙÀ½ ¼¼°¡Áö ¹æ¹ýÀ¸·Î ºñ¹Ð¹øÈ£¸¦ º¯°æ ÇÒ ¼ö ÀÖ´Ù.
# mysqladmin ÀÌ¿ë
mysqladmin -u root password »õºñ¹Ð¹øÈ£
# update¹® ÀÌ¿ë
mysql -u root mysql
----
mysql> UPDATE user SET password=password('»õºñ¹Ð¹øÈ£') WHERE user='root';
mysql> FLUSH PRIVILEGES;
# Set Password ÀÌ¿ë
mysql> SET PASSWORD FOR root=password('»õºñ¹Ð¹øÈ£');
ÀÏ´Ü root ºñ¹Ð¹øÈ£°¡ ¼³Á¤µÈ »óÅ¿¡¼´Â mysqlÀ̳ª mysqladmin
¸í·ÉÀ» ½ÇÇàÇÒ ¶§ -p ¿É¼ÇÀ» ºÙ¿©ÁÖ°í ±âÁ¸ ºñ¹Ð¹øÈ£¸¦ ÀÔ·ÂÇØ¾ß¸¸ ÇÑ´Ù.
»ç¿ëÀÚ Ãß°¡/»èÁ¦
mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';
username À̶ó´Â »ç¿ëÀÚ¸¦ password¶ó´Â ºñ¹Ð¹øÈ£¸¦ °®µµ·Ï ÇÏ¿© Ãß°¡ÇÑ´Ù.
usernameÀº dbnameÀ̶ó´Â µ¥ÀÌŸº£À̽º¿¡ ´ëÇØ ¸ðµç ±ÇÇÑÀ» °¡Áö°í ÀÖ´Ù.
username »ç¿ëÀÚ´Â ·ÎÄ® È£½ºÆ®¿¡¼¸¸ Á¢¼ÓÇÒ ¼ö ÀÖ´Ù. ´Ù¸¥ È£½ºÆ®¿¡¼ Á¢¼ÓÇÏ·Á¸é
mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';
À§¸¦ ¶ÇÇÑ ¹ø ½ÇÇàÇÑ´Ù. '%'¿¡¼ Ȭµû¿ÈÇ¥¸¦ ÁÖÀÇÇÑ´Ù.
MySQL error 1045(28000): Access denied for user ...¿¡ µû¸£¸é '%'¶õ localhost ¸¦
Á¦¿ÜÇÑ ¸ðµç È£½ºÆ®¸¦ ¶æÇÑ´Ù°í ÇÑ´Ù. Áï, localhost¿¡ ´ëÇØ¼´Â ¸í½ÃÀûÀ¸·Î µû·Î ±ÇÇÑÀ» ÁöÁ¤ÇØ¾ß ÇÑ´Ù.
ºÒÇÊ¿äÇÑ »ç¿ëÀÚ »èÁ¦´Â
mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;
µ¥ÀÌÅͺ£À̽º »ý¼º/º¸±â
# µ¥ÀÌÅͺ£À̽º¸¦ »ý¼ºÇϰí,
mysql> CREATE DATABASE dbname;
# ÇöÀç Á¸ÀçÇÏ´Â µ¥ÀÌÅͺ£À̽º ¸ñ·ÏÀ» º¸¿©ÁØ´Ù.
mysql> SHOW DATABASES;
# ƯÁ¤ µ¥ÀÌŸº£À̽º¸¦ »ç¿ëÇÏ°Ú´Ù°í ¼±¾ðÇÑ´Ù.
mysql> USE dbname;
# ¾µ¸ð ¾øÀ¸¸é °ú°¨È÷ »èÁ¦ÇÑ´Ù.
mysql> DROP DATABASE [IF EXISTS] dbname;
IF EXISTS ¿É¼ÇÀº ºñ·Ï µ¥ÀÌŸº£À̽º°¡ ¾ø´õ¶óµµ ¿À·ù¸¦ ¹ß»ý½ÃŰÁö ¸»¶ó´Â ÀǹÌÀÌ´Ù.
Å×ÀÌºí »ý¼º/º¸±â
Å×À̺íÀ» »ý¼ºÇϰí,
mysql> CREATE TABLE tablename (
column_name1 INT,
column_name2 VARCHAR(15),
column_name3 INT );
ÇöÀç µ¥ÀÌŸº£À̽ºÀÇ Å×ÀÌºí ¸ñ·ÏÀ» º¸°í
mysql> SHOW TABLES;
Å×ÀÌºí ±¸Á¶¸¦ »ìÆìº»´Ù.
mysql> EXPLAIN tablesname;
ȤÀº
mysql> DESCRIBE tablename;
À̸§À» À߸ø ÁöÁ¤ÇßÀ¸¸é À̸§À» º¯°æÇÒ ¼öµµ ÀÖ´Ù.
mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];
ÇÊ¿ä ¾øÀ¸¸é »èÁ¦ÇÑ´Ù.
mysql> DROP TABLE [IF EXISTS] tablename;
ÇöÀç »óÅ º¸±â
mysql> STATUS
--------------
mysql Ver 11.18 Distrib 3.23.58, FOR pc-linux (i686)
Connection id: 26
Current DATABASE: study
Current user: study@localhost
Current pager: stdout
USING OUTFILE: ''
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 min 59 sec
Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 FLUSH TABLES: 1
Open TABLES: 1 Queries per second avg: 0.021
--------------
INSERT
mysql> INSERT INTO tablename VALUES(°ª1, °ª2, ...);
ȤÀº
mysql> INSERT INTO tablename (col1, col2, ...) VALUES(°ª1, °ª2, ...);
SELECT
mysql> SELECT col1, col2, ... FROM tablename;
Ä÷³¸íÀ» *·Î ÇÏ¸é ¸ðµç Ä÷³ ÀǹÌ.
mysql> SELECT col1 AS '¼º¸í', col2 AS '±¹¾îÁ¡¼ö' FROM grade;
Ä÷³ÀÇ À̸§À» ¹Ù²ã¼ Ãâ·Â.
mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS 'ÃÑÁ¡' FROM tablename ORDER BY 'ÃÑÁ¡' ASC;
DESC´Â ³»¸²Â÷¼ø ASC´Â ¿À¸§Â÷¼ø.
mysql> SELECT * FROM grade WHERE korean < 90;
°á°úÁß Ã³À½ºÎÅÍ 10°³¸¸ °¡Á®¿À±â
mysql> SELECT * FROM grade LIMIT 10;
°á°úÁß 100¹øÂ°ºÎÅÍ 10°³¸¸ °¡Á®¿À±â. ù¹øÂ° ·¹ÄÚµå´Â 0¹ø ºÎÅÍ ½ÃÀÛÇÑ´Ù.
mysql> SELECT * FROM grade LIMIT 100, 10;
UPDATE
mysql> UPDATE tablename SET col1=»õ°ª WEHER Á¶°Ç
DELETE
mysql> DELETE FROM tablename WEHRE Á¶°Ç
mysql¿¡¼ Äõ¸® °á°ú ¼¼·Î·Î º¸±â
-E ¿É¼ÇÀ» Á༠½ÇÇàÇÑ´Ù.
mysql -E -u root -p mysql
mysql¿¡¼ ¹ß»ýÇÑ ¿À·ù³ª °æ°í ´Ù½Ã º¸±â
mysql> SHOW errors;
mysql> SHOW warnings;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
/etc/init.d/mysqld stop
/etc/init.d/mysqld start
DBÁ¢¼Ó
> mysql -u °èÁ¤ID -p °èÁ¤PW;
DB ¸ñ·Ïº¸±â
> show databases;
mysql> show databases;
DB ¼±ÅÃÇϱâ
> use DB¸í;
DB »ý¼º
> mysqladmin -u root -p root°èÁ¤ºñ¹Ð¹øÈ£ create database »õ·Î¿îDB¸í;
DB »èÁ¦
> mysqladmin -u root -p root°èÁ¤ºñ¹Ð¹øÈ£ drop database »õ·Î¿îDB¸í;
°èÁ¤Ãß°¡
> mysql -u »õ·Î¿î¾ÆÀ̵ð ±ÇÇѼ³Á¤(select, insert, delete, alter, drop, create µî) -p »ç¿ëÇÒºñ¹Ð¹øÈ£;
Å×ÀÌºí »ý¼º
>create table Å×À̺í¸í(Ä÷³¸í ŸÀÔ Á¶°Ç);
ex.) create table test(no int NOT NULL, t_name varchar(20), content TEXT(255));
Å×ÀÌºí ¸ñ·Ïº¸±â
> show tables;
Å×À̺í À̸§ º¯°æ
> alter table Å×À̺í¸í rename º¯°æÇÒÅ×À̺í¸í;
Å×ÀÌºí »èÁ¦
> drop table Å×À̺í¸í;
Å×ÀÌºí »ý¼º
>create table Å×À̺í¸í(Ä÷³¸í ŸÀÔ Á¶°Ç);
Å×ÀÌºí ±¸Á¶ È®ÀÎ
> desc Å×À̺í¸í;
> explain Å×À̺í¸í;
Å×À̺í Ä÷³(Çʵå) Ãß°¡
> alter table Å×À̺í¸í add Ãß°¡ÇÒÇʵå¸í ŸÀÔ Á¶°Ç first ¶Ç´Â after Çʵå¸í;
ex) alter table test add name varchar(20) after age;
// test Å×ÀÌºí¿¡ name Çʵ带 ageµÚ¿¡ »ý¼ºÇÑ´Ù.
Å×À̺í Ä÷³(Çʵå) »èÁ¦
> alter table Å×À̺í¸í drop »èÁ¦ÇÒÇʵå¸í;
µ¥ÀÌÅÍ ÀÔ·Â
> inset into Å×À̺í¸í values('Çʵ尪','Çʵ尪','Çʵ尪');
µ¥ÀÌÅÍ ¼öÁ¤
> update Å×À̺í¸í set ¼öÁ¤ÇÒÇʵå¸í='¼öÁ¤ÇÒÇʵ尪' where Á¶°ÇÇʵå¸í='Á¶°Çµ¥ÀÌÅͰª';
ex) where Á¶°Ç½Ä¿¡ ÀÇÇÑ Å×ÀÌÅ͸¦ ÁöÁ¤ÇÏÁö ¾ÊÀ» °æ¿ì ÇØ´ç ÇʵåÀÇ ¼öÁ¤°ªÀÌ ¸ðµç µ¥ÀÌÅÍ¿¡ ¹Ý¿µµÇ´Ï ÁÖÀÇ.
ex) update test set age='35' where name='ÃÖ¹ÎÈ£';
//test Å×À̺íÀÇ name°ªÀÌ ÃÖ¹ÎÈ£ÀÎ µ¥ÀÌÅÍÀÇ age °ªÀ» 35·Î ¼öÁ¤ÇÑ´Ù.
µ¥ÀÌÅÍ »èÁ¦
> delete from Å×À̺í¸í where Á¶°Ç°Ë»öÇʵå¸í='Á¶°Ç°Ë»öµ¥ÀÌÅͰª';
µ¥ÀÌÅÍ °Ë»ö
> select * from Å×À̺í¸í; // ¸ðµç ÇÊµå µ¥ÀÌÅ͸¦ °Ë»ö
> select Çʵå¸í,Çʵå¸í from Å×À̺í¸í; // ÁöÁ¤ÇÑ ÇʵåÀÇ µ¥ÀÌÅ͸¸ °Ë»ö
µ¥ÀÌÅÍ °Ë»ö Á¶°Ç
> select * from Å×À̺í¸í where Á¶°Ç;
ex) select * from test where age='35'; // test Å×ÀÌºí¿¡¼ age°¡ 35ÀÎ µ¥ÀÌÅ͸¦ °Ë»öÇÑ´Ù.
ex) select * from test where age<'35'
ex) select * from test where age >'25' and age<'35';
ex) select * from test where age='35' and name='ÃÖ¹ÎÈ£' and sex='³²';
ex) select * from test where name like '¹Î%'; // '¹Î'À¸·Î ½ÃÀÛÇÏ´Â µ¥ÀÌÅ͸¦ °Ë»ö
ex) select * from test where name like '%¹Î'; // '¹Î'À¸·Î ³¡³ª´Â µ¥ÀÌÅ͸¦ °Ë»ö
ex) select * from test where name like '%¹Î%'; // '¹Î'À» Æ÷ÇÔÇϰí ÀÖ´Â µ¥ÀÌÅ͸¦ °Ë»ö
µ¥ÀÌÅÍ Á¤·Ä
> select * from Å×À̺í¸í order by Á¤·Ä±âÁØÇʵå¸í
ex) select * from test order by no // no Çʵ尪 ±âÁØÀ¸·Î ¿À¸§¼ø Á¤·Ä
ex) select * from test order by no desc // no Çʵ尪 ±âÁØÀ¸·Î ³»¸²¼ø Á¤·Ä
°Ë»öµÈ µ¥ÀÌÅÍÀÇ ÀϺθ¸ °¡Á®¿À±â
> select * from Å×À̺í¸í ±âÁØÇʵå¸í asc limit ½ÃÀÛ¹øÈ£,°Ë»öÇÒµ¥ÀÌÅͼö;
ex) select * from test num asc limit 0,20;
// test Å×ÀÌºí¿¡¼ numÇÊµå °ªÀ» ±âÁØÀ¸·Î ù¹øÂ°(0)ºÎÅÍ 20°³ÀÇ µ¥ÀÌÅ͸¦ °Ë»öÇÑ´Ù.
ex) select * from test were age<'35' order by num desc, num asc limit 0,20
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
PHP¿¡¼ MySQL°ú socket ¿¬°áÀÌ ¾ÈµÉ¶§ ÇØ°á¹æ¹ý
PHP¿¡¼ mysql_connect()¸¦ ÀÌ¿ëÇØ MySQL°ú ¿¬°áÇÏ·Á Çß´õ´Ï Çæ Á¢¼ÓÀÌ ¾ÊµÇ³×¿ä.
¾Ö·¯ ¹®±¸´Â ¾Æ·¡¿Í °°ÀÌ ³ª¿À³×¿ä.
Warning: mysql_connect() [function.mysql-connect]: Can¡¯t connect to
local MySQL server through socket ¡®/tmp/mysql.sock¡¯ (2) ¡¦
¹°·Ð MySQLÀÌ Á×¾î ÀÖÀ¸¸é ´ç¿¬È÷ ¾ÈµÇ°ÚÁö¸¸ ¸ÖÂÄÇÏ°Ô »ì¾Æ ÀÖ°í Å͹̳ο¡¼ command line¿¡¼
Á¢¼Óµµ Àß µÇ´Âµ¥ PHP¸¸ ¾ÈµÇ³×¿ä.
±×·¡¼ ã¾Æ ºÃ´õ´Ï ¹®Á¦´Â mysql.sock ÆÄÀÏÀ» ¾û¶×ÇѰÍÀ» »ç¿ëÇØ¼¶ø´Ï´Ù.
¼³Ä¡ÇÒ¶§ÀÇ »óÅÂ¿Í ½ÇÇà½ÃÀÇ »óŰ¡ ´Þ¶óÁ®¼ MySQLÀÌ ½ÇÇàÇÒ¶§ mysql.sock ÆÄÀÏÀ»
´Ù¸¥ °÷¿¡ Àִ°ÍÀ¸·Î »ç¿ëÇØ¼¶ó´Â±º¿ä. ¹¹ ¾Ïư, PHP°¡ ¾û¶×ÇÑ ÆÄÀÏÀ» ã¾Æ¼
mysql.sock ÆÄÀÏÀÌ ¾ø´Ù´Â ¹¹ ±×·± ¿À·ùÀä.
ÇØ°á¹æ¹ýÀº ¾Æ·¡¿Í °°Áö 3°¡Áö ÀÖ´Ù°í ÇÕ´Ï´Ù.
¡°±Ù¿øe¡±´Ô °¨»çÇÕ´Ï´Ù. ´öºÐ¿¡ ½±°Ô ÇØ°áÇß½À´Ï´Ù.
php ¼Ò½º ¼öÁ¤
mysql_connect('localhost:/tmp/mysql.sock', 'username', 'password');
À§¿Í °°ÀÌ host °ª¿¡ ¼ÒÄÏ ÆÄÀÏ À§Ä¡¸¦ ¸í½ÃÇÏ´Â ¹æ¹ýÀÔ´Ï´Ù.
¼¹ö ȯ°æ ÆÄÀÏÀ» Á÷Á¢ ¼öÁ¤ÇÏÁö ¸øÇÏ´Â °æ¿ì »ç¿ëÇÏ½Ã¸é µË´Ï´Ù.
2. php.ini ¼öÁ¤
[MySQL]
mysql.default_socket = /tmp/mysql.sock
À§¿Í °°ÀÌ php.iniÆÄÀÏ¿¡ mysql.default_socket ¼³Á¤ Ç׸ñÀÌ ÀÖ½À´Ï´Ù. ±×°÷¿¡ Á¤ÀÇÇØ ÁÖ½Ã¸é µË´Ï´Ù.
MySQLi µµ »ç¿ëÇϽŴٸé mysqli¿¡µµ default_socket¿¡ ½áÁÖ½Ã¸é µË´Ï´Ù.
3. my.ini ¼öÁ¤
[mysqld]
socket = /tmp/mysql.sock
ÀÌ°Ç mysql ȯ°æ ¼³Á¤À» ¹Ù²Ù´Â °Çµ¥¿ä. ½ÇÁ¦·Î mysql ȯ°æÀ» ¹Ù²ã¾ß ÇÒÀÏÀÌ ÀÖÀ»Áö´Â ¸ð¸£°Ú³×¿ä.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
| |
|