MySQL構築 - のんびりSE備忘録 @ ウィキ

• グループ・ユーザ作成

# groupadd -g 100 mysql
# useradd -u 100 -g 100 -d /var/lib/mysql mysql

• RPMインストール

# rpm -ivh --nopost MySQL-server-advanced-gpl-5.1.31-0.rhel4.x86_64.rpm
# rpm -ivh MySQL-client-advanced-gpl-5.1.31-0.rhel4.x86_64.rpm

• ディレクトリ作成

# mkdir /var/log/mysql ; chmod 775 /var/log/mysql ; chown mysql:mysql /var/log/mysql
# mkdir /var/lib/mysql ; chmod 750 /var/lib/mysql ; chown mysql:mysql /var/lib/mysql
# mkdir /var/lib/mysql/data ; chmod 750 /var/lib/mysql/data ; chown mysql:mysql /var/lib/mysql/data
# mkdir /var/lib/mysql/binlog ; chmod 750 /var/lib/mysql/binlog ; chown mysql:mysql /var/lib/mysql/binlog

• my.cnf作成

# vi /var/lib/mysql/my.cnf
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
character-set-server = utf8
datadir = /var/lib/mysql/data
back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin = /var/lib/mysql/binlog/mysql-bin
binlog_format = mixed
long_query_time = 2
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
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 768M
innodb_data_home_dir = /var/lib/mysql/data
innodb_data_file_path = ibdata1:256M;ibdata2:256M:autoextend
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table
pid-file = /var/lib/mysql/mysqld.pid
general_log_file = /var/log/mysql/general.log
log-error = /var/log/mysql/mysqld.err
slow_query_log_file = /var/log/mysql/mysqld-slow.log
sync_binlog = 1
skip-character-set-client-handshake
log-bin-trust-function-creators
server-id = 1

#user request
general_log
slow_query_log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
log-error = /var/log/mysql/mysqld.err

# chown mysql:mysql /var/lib/mysql/my.cnf ; chmod 640 /var/lib/mysql/my.cnf
# vi /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

# chown mysql:mysql /etc/my.cnf ; chmod 640 /etc/my.cnf

• 初期DB作成

# su - mysql -c "/usr/bin/mysql_install_db --datadir=/var/lib/mysql/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:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h bgsx9182 password 'new-password'

Alternatively you can run:
/usr/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/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 /usr/bin/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

• MySQL起動

# /etc/init.d/mysql start