MySQL 인스톨(binary 설치)

 MySQL 설치 방법

MySQL Binary source 파일로 설치한다.

 

1.3.1 user 및 group 생성

[root@testdb ~]# groupadd -g 501 dba [root@testdb ~]# useradd -g dba -u 501 mysql [root@testdb ~]# [root@testdb ~]# cat /etc/group | grep dba dba:x:501: [root@testdb ~]# [root@testdb ~]# cat /etc/passwd | grep mysql mysql:x:501:501::/home/mysql:/bin/bash

 

1.3.2 MySQL 엔진 심볼릭 링크 생성 및 압축 풀기

[root@testdb MYSQL]# ls LOG  lost+found  mysql-5.5.25a-linux2.6-x86_64.tar.gz [root@testdb MYSQL]# tar xvf mysql-5.5.25a-linux2.6-x86_64.tar.gz [root@testdb MYSQL]# ln -s mysql-5.5.25a-linux2.6-x86_64 mysql [root@testdb MYSQL]# ls -al 합계 182212 drwxr-xr-x  5 root root      4096 11월 28 11:45 . drwxr-xr-x 27 root root      4096 11월 28 11:00 .. drwxr-xr-x  3 root root      4096 11월 26 23:24 LOG drwx------  2 root root     16384 11월 26 23:24 lost+found lrwxrwxrwx  1 root root        29 11월 28 11:45 mysql -> mysql-5.5.25a-linux2.6-x86_64 drwxr-xr-x 13 root root      4096 11월 28 11:44 mysql-5.5.25a-linux2.6-x86_64 -rw-r--r--  1 root root 186355822 11월 28 11:40 mysql-5.5.25a-linux2.6-x86_64.tar.gz

 

1.3.3. 디렉토리 권한 설정

chown 을 사용하여 MySQL 관련 디렉토리를 mysql 계정 dba 그룹으로 설정한다

[root@testdb ~]# chown -R mysql.dba /MYSQL/mysql-5.5.25a-linux2.6-x86_64 [root@testdb ~]# chown -R mysql.dba /MYSQL_DATA/DATA/ [root@testdb ~]# chown -R mysql.dba /MYSQL/LOG/ [root@testdb mysql]# ls -al /MYSQL/mysql-5.5.25a-linux2.6-x86_64 합계 84 drwxr-xr-x 13 mysql dba   4096 11월 28 11:44 . drwxr-xr-x  5 root  root  4096 11월 28 11:45 .. -rw-r--r--  1 mysql dba  17987  6월 29 21:48 COPYING -rw-r--r--  1 mysql dba   7604  6월 29 21:48 INSTALL-BINARY -rw-r--r--  1 mysql dba   2552  6월 29 21:48 README drwxr-xr-x  2 mysql dba   4096 11월 28 11:43 bin drwxr-xr-x  4 mysql dba   4096 11월 28 11:43 data drwxr-xr-x  2 mysql dba   4096 11월 28 11:42 docs drwxr-xr-x  3 mysql dba   4096 11월 28 11:43 include drwxr-xr-x  3 mysql dba   4096 11월 28 11:43 lib drwxr-xr-x  4 mysql dba   4096 11월 28 11:44 man drwxr-xr-x 10 mysql dba   4096 11월 28 11:44 mysql-test drwxr-xr-x  2 mysql dba   4096 11월 28 11:44 scripts drwxr-xr-x 27 mysql dba   4096 11월 28 11:43 share drwxr-xr-x  4 mysql dba   4096 11월 28 11:44 sql-bench drwxr-xr-x  2 mysql dba   4096 11월 28 11:44 support-files

.

1.3.4 mysql_install_db 실행

mysql_install_db 실행하여 database를 생성한다.

[root@testdb mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/MYSQL_DATA/DATA/ 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:   ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h testdb password 'new-password'   Alternatively you can run: ./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 . ; ./bin/mysqld_safe &   You can test the MySQL daemon with mysql-test-run.pl cd ./mysql-test ; perl mysql-test-run.pl   Please report any problems with the ./bin/mysqlbug script!

 

MYSQL_DATA 디렉토리 DB 생성을 확인한다. (mysql, test, performance_schema)

[root@testdb mysql]# ls -al /MYSQL_DATA/DATA/ 합계 20 drwxr-xr-x 5 mysql dba  4096 11월 28 11:59 . drwxr-xr-x 4 root  root 4096 11월 28 11:52 .. drwx------ 2 mysql root 4096 11월 28 11:59 mysql drwx------ 2 mysql dba  4096 11월 28 11:59 performance_schema drwx------ 2 mysql root 4096 11월 28 11:59 test

 

1.3.5 파라미터 설정

표준 파라미터 적용은 /etc/my.cnf 에서 한다

1.3.5.1

[client] port = 3306 socket = /tmp/mysql.sock   [mysqld] port = 3306 socket = /tmp/mysql.sock basedir = /MYSQL/mysql datadir = /MYSQL_DATA/DATA     # query_cache 설정 (사용하지 않음) query_cache_type = 0 query_cache_size = 16M query_cache_limit = 2M   # Log 파일 설정 log-error = /MYSQL/LOG/mysql.err slow_query_log = 1 slow_query_log_file = /MYSQL/LOG/mysql-slow-query.log long_query_time = 3   # Time out 설정 wait_timeout = 28800 interactive_timeout = 600   # transaction 격리수준 설정 transaction_isolation = READ-COMMITTED   # 기타 환경 설정 back_log = 1024 max_connections = 1024 max_connect_errors = 1024 table_open_cache = 2048 max_allowed_packet = 16M max_heap_table_size = 1024M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 8 thread_concurrency = 36 tmp_table_size = 1024M   # 문자셋 설정 (기본값은 utf8로 설정하나 서비스에 따라 euckr로 변경) init_connect = "SET collation_connection = utf8_general_ci" init_connect = "SET NAMES utf8" character-set-server = utf8   # innodb 설정 innodb_lock_wait_timeout = 50 innodb_file_per_table innodb_thread_sleep_delay = 0 innodb_commit_concurrency = 36 innodb_doublewrite = 0 innodb_flush_log_at_trx_commit = 2 innodb_additional_mem_pool_size = 50M innodb_thread_concurrency = 36 innodb_log_files_in_group = 3   # /MYSQL_DATA/DATA 파티션 크기에 따른 분류 innodb_data_file_path = ibdata1:1024M;ibdata2:1024M;ibdata3:10M:autoextend innodb_log_file_size = 256M   # H/W RAM 용량에 따른 분류 # RAM < 8GB innodb_buffer_pool_size = 2G innodb_log_buffer_size = 32M # RAM > 8GB innodb_buffer_pool_size = 4G innodb_log_buffer_size = 32M   # MyISAM 사용시 key_buffer_size = 512 read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 2G myisam_repair_threads = 1 myisam_recover   # innodb 사용시 key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover   [mysqldump] quick   [mysqlhotcopy] interactive-timeout   [mysqld_safe] open-files-limit = 8192

 

1.3.6 MySQL 실행

MySQL 엔진 디렉토리에서 mysqld_safe 실행하여 mysql 실행한다.

[root@testdb mysql]# ./bin/mysqld_safe --user=mysql & [1] 5350 [root@testdb mysql]# 121128 13:58:13 mysqld_safe Logging to '/MYSQL/LOG/mysql.err'. 121128 13:58:14 mysqld_safe Starting mysqld daemon with databases from /MYSQL_DATA/DATA

 

1.4 보안 적용

1.4.1 기본 보안 적용

MySQL Binary source에서 제공하는 mysql_secure_installation 스크립트를 이용해 기본 보안 적용

root 패스워드 설정, 익명 사용자 삭제, test db 삭제

[root@testdb 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.   Set root password? [Y/n] Y 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] Y  ... 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] n  ... skipping.   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] Y  - Dropping test database...  ... Success!  - 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] Y  ... Success!   Cleaning up...   All done!  If you've completed all of the above steps, your MySQL installation should now be secure.   Thanks for using MySQL!

 

1.4.2 /etc/my.cnf 권한은 640, 사용자 및 그룹은 mysql.dba로 설정

chmod 와 chown 을 사용하여 권한 및 사용자 그룹을 변경한다.

[root@testdb mysql]# chmod 640 /etc/my.cnf [root@testdb mysql]# chown mysql.dba /etc/my.cnf [root@testdb mysql]# ls -al /etc/my.cnf -rw-r----- 1 mysql dba 1816 11월 28 13:57 /etc/my.cnf

 

1.4.3 mysqld_safe 와 mysql.server의 권한은 750으로 설정

chmod를 사용하여 권한을 750으로 변경한다.

[root@testdb mysql]# ls -al support-files/mysql.server -rwxr-x--- 1 mysql dba 10650  6월 29 23:29 support-files/mysql.server [root@testdb mysql]# ls -al bin/mysqld_safe -rwxr-x--- 1 mysql dba 23184  6월 29 23:29 bin/mysqld_safe

 

1.4.4 $datadir 디렉토리 및 데이터 파일 접근제한 설정

데이터 파일의 권한이 600 또는 640

$datadir의 접근 권한은 750 이하로 설정

[root@testdb mysql]# cd /MYSQL_DATA/DATA/ [root@testdb DATA]# chmod 750 /MYSQL_DATA/DATA/ drwxr-x--- 4 mysql dba        4096 11월 28 14:04 . drwxr-xr-x 4 root  root       4096 11월 28 11:52 .. -rw-rw---- 1 mysql dba           5 11월 28 14:00 testdb.pid -rw-rw---- 1 mysql dba   268435456 11월 28 14:00 ib_logfile0 -rw-rw---- 1 mysql dba   268435456 11월 28 13:59 ib_logfile1 -rw-rw---- 1 mysql dba   268435456 11월 28 14:00 ib_logfile2 -rw-rw---- 1 mysql dba  1073741824 11월 28 14:00 ibdata1 -rw-rw---- 1 mysql dba  1073741824 11월 28 13:59 ibdata2 -rw-rw---- 1 mysql dba    10485760 11월 28 13:59 ibdata3 drwx------ 2 mysql root       4096 11월 28 11:59 mysql drwx------ 2 mysql dba        4096 11월 28 11:59 performance_schema [root@testdb DATA]# chmod 640 ib_logfile0 ib_logfile1 ib_logfile2 ibdata1 ibdata2 ibdata3[root@testdb DATA]# cd mysql [root@testdb mysql]# chmod 640 * [root@testdb mysql]# cd .. [root@testdb DATA]# cd performance_schema/ [root@testdb performance_schema]# chmod 640 * [root@testdb DATA]# chmod 640 testdb.pid [root@testdb DATA]# ls -al 합계 2896696 drwxr-x--- 4 mysql dba        4096 11월 28 14:04 . drwxr-xr-x 4 root  root       4096 11월 28 11:52 .. -rw-r----- 1 mysql dba           5 11월 28 14:00 testdb.pid -rw-r----- 1 mysql dba   268435456 11월 28 14:00 ib_logfile0 -rw-r----- 1 mysql dba   268435456 11월 28 13:59 ib_logfile1 -rw-r----- 1 mysql dba   268435456 11월 28 14:00 ib_logfile2 -rw-r----- 1 mysql dba  1073741824 11월 28 14:00 ibdata1 -rw-r----- 1 mysql dba  1073741824 11월 28 13:59 ibdata2 -rw-r----- 1 mysql dba    10485760 11월 28 13:59 ibdata3 drwx------ 2 mysql root       4096 11월 28 11:59 mysql drwx------ 2 mysql dba        4096 11월 28 11:59 performance_schema