496
Linux平台使用二進製包將MySQL 5.1安裝到個人目錄簡易指南
1. 解壓二進製包到個人目錄，例如/home/liyh/local/mysql；2. 拷貝一份係統目錄的my.cnf配置（或者後麵的配置樣例也可以），按照如下方式修改：
2.1 將其中所有目錄相關的配置全部改為個人目錄，且要用絕對路徑，不能用$HOME. 例如data, log（注意二進製包解壓後缺省並沒有log、tmp目錄，需要自己手工創建），為了提高性能，建議數據目錄和日誌目錄分開到不同的盤不同的目錄，例如：
datadir = /home1/liyh/mysql/data/
log-error = /home/liyh/local/mysql/log/mysqld.err
log-bin=/home/liyh/local/mysql/log/mysql-bin
innodb_data_home_dir = /home1/liyh/mysql/data
2.2 修改[mysqld]下的port為你自己的端口；
2.3 修改[mysqld]下的user為你的用戶名；
3. 將mysql的目錄權限修改為你的用戶，例如：
chgrp -R liyh mysql;
chown -R liyh mysql;
chmod 775 mysql;
4. 到mysql目錄下的scripts目錄，執行如下語句：
./mysql_install_db --defaults-file=/home/liyh/local/mysql/my.cnf --user=liyh --basedir=/home/liyh/local/mysql
5. 執行如下語句啟動：
/home/liyh/local/mysql/bin/mysqld_safe --defaults-file=/home/liyh/local/mysql/my.cnf --basedir=/home/liyh/local/mysql &
【特別注意】為了提高InnoDB引擎的性能，記得一定要按照如下方式修改兩個參數，否則你的MySQL insert和update慢得如蝸牛，但高性能同時也是有損失的，這樣配置會降低MySQL的可靠性，某些場景下可能丟失數據，實際應用需要你自己評估是否要這樣配置：
sync_binlog=0 1代表每次執行一條insert/update/delete都同步一下磁盤，0代表由操作係統決定何時同步磁盤，大於0代表達到多少條後再同步磁盤，如果可靠性要求較低，建議設置為0或者100以上
innodb_flush_log_at_trx_commit = 2 默認為1，意思是每次事務提交時都同步日誌到磁盤，0代表每秒同步一次，事務提交時不同步，2代表隻將事務日誌寫入緩存，每秒由係統同步磁盤；
==================配置樣例如下====================================
[client]
port = 19816
socket = /home/liyh/local/mysql/tmp/mysql.sock
default-character-set = utf8
[mysqld]
character-set-server = utf8
port = 19816
socket = /home/liyh/local/mysql/tmp/mysql.sock
user = liyh
skip-external-locking
datadir = /home1/liyh/mysql/data/
log-error = /home/liyh/local/mysql/log/mysqld.err
pid-file = /home/liyh/local/mysql/tmp/mysql.pid
#autocommit = off
#read-only
key_buffer_size = 64M
myisam_sort_buffer_size = 300M
max_allowed_packet = 16M
table_open_cache = 4096
sort_buffer_size = 5M
join_buffer_size = 5M
read_buffer_size = 5M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 64M
query_cache_size = 8M
query_cache_limit=8M
query_cache_min_res_unit = 2k
tmp_table_size=64M
max_heap_table_size = 128M
open_files_limit=102400
thread_concurrency = 16
max_connections=3000
max_connect_errors=3000
back_log = 600
table_cache = 4096
thread_cache_size = 100
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
skip-name-resolve
####log variables
server-id = 1
binlog_format=mixed
#log-slave-updates
log-bin=/home/liyh/local/mysql/log/mysql-bin
relay_log=/home/liyh/local/mysql/log/relay-bin
max_binlog_size = 500M
binlog_cache_size = 1M
max_binlog_cache_size = 64M
#binlog-ignore-db = mysql
replicate-ignore-db = mysql
expire-logs-days = 10
sync_binlog=0
slow_query_log=1
slow_query_log_file=/home/liyh/local/mysql/log/slowquery.log
long_query_time=1
general_log=off
general_log_file=/home/liyh/local/mysql/log/general.log
#relay_log_purge=OFF
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
#auto_increment_increment=1
#auto_increment_offset=1
####innodb variables
#skip-innodb
innodb_data_home_dir = /home1/liyh/mysql/data
innodb_data_file_path = ibdata1:2000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /home1/liyh/mysql/data
#innodb_log_arch_dir = /home/liyh/local/mysql/data/
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 200M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
#innodb_file_io_threads = 4
innodb_thread_concurrency = 16
default-storage-engine=innodb
#innodb_max_dirty_pages_pct = 90
#innodb_lock_wait_timeout = 60
#innodb_file_per_table = 0
#interactive_timeout = 600
#wait_timeout = 900
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 5M
write_buffer = 5M
[mysqlhotcopy]
interactive-timeout
最後更新：2017-04-02 06:52:12