Linux系統下Mysql資料庫安裝配置整理

Mysql安裝

1、通過官網下載mysql源碼包。http://dev.mysql.com/downloads/ 點擊MySQL Community Server，選擇Source Code， 點擊 Generic Linux

(Architecture Independent), Compressed TAR Archive後的Download# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.20.tar.gz# tar zxvf mysql-5.6.20.tar.gz # cd mysql-5.6.20

2、 安裝cmake（mysql5.5以後源碼安裝都得通過cmake編譯，並安裝了ncurses ncurses-devel

# yum -y install cmake ncurses ncurses-devel# groupadd mysql# useradd -g mysql mysql

3、編譯並安裝

# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql -DMYSQL_DATADIR=/usr/local/webserver/mysql -DSYSCONFDIR=/usr/local/webserver/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1# make && make install

參數說明：
-DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql //指定安裝目錄
-DINSTALL_DATADIR=/usr/local/webserver/mysql //指定資料存放目錄
-DSYSCONFDIR=/usr/local/webserver/mysql //指定設定檔目錄（本例的設定檔為/opt/mysql/my.cnf）
-DDEFAULT_CHARSET=utf8 //指定字元集
-DDEFAULT_COLLATION=utf8_general_ci //指定校正字元
-DEXTRA_CHARSETS=all //安裝所有擴充字元集
-DENABLED_LOCAL_INFILE=1 //允許從本地匯入資料

編譯出錯需刪掉CMakeCache.txt

# rm CMakeCache.txt

拷貝mysql設定檔，並進行相應配置，這裡是伺服器是阿里雲的最低配置，單核 512M記憶體。

# cd /usr/local/webserver/mysql# chown -R mysql:mysql data/# cp support-files/my-default.cnf  my.cnf# vi my.cnf

編輯my.cnf

[mysqld] innodb_buffer_pool_size = 100M basedir = /usr/local/webserver/mysqldatadir = /usr/local/webserver/mysql/dataport = 3306server_id = 1socket = /tmp/mysql.sock join_buffer_size = 10Msort_buffer_size = 10Mread_rnd_buffer_size = 12M  query_cache_size = 32Mtmp_table_size = 32Mkey_buffer_size = 32M performance_schema_max_table_instances=1000table_definition_cache=800table_open_cache=512 long_query_time=1slow_query_log=1slow_query_log_file=/usr/loca/webserver/mysql/data/slow-queries.loglog_queries_not_using_indexes=1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

初始化Mysql資料庫

/usr/loca/webserver/mysql/scripts/mysql_install_db --user=mysql

啟動Mysql

# ./support-files/mysql.server start
報錯

Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/webserver/mysql/data/AY121218115148c506503.pid).
 
2014-08-14 11:29:38 1678 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2014-08-14 11:29:38 1678 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-14 11:29:38 1678 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2014-08-14 11:29:38 1678 [Note] InnoDB: Memory barrier is not used
2014-08-14 11:29:38 1678 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-14 11:29:38 1678 [Note] InnoDB: Not using CPU crc32 instructions
2014-08-14 11:29:38 1678 [Note] InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: mmap(106840064 bytes) failed; errno 12
2014-08-14 11:29:38 1678 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2014-08-14 11:29:38 1678 [ERROR] Plugin 'InnoDB' init function returned error.
2014-08-14 11:29:38 1678 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-08-14 11:29:38 1678 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-08-14 11:29:38 1678 [ERROR] Aborting
無法給innodb_buffer_pool_size分配100M記憶體，但啟動Mysql之前實際上是有記憶體的。
Mysql5.6有幾個預設值，按照這些值啟動需要消耗幾百兆記憶體，然後再分配給innodb_buffer_pool_size就不足了，伺服器上可憐的512M記憶體。。。

performance_schema_max_table_instances = 12500table_definition_cache = 1400table_open_cache = 2000

調整一下

performance_schema_max_table_instances=600table_definition_cache=400table_open_cache=256

就只使用40---60M左右的記憶體了,重新啟動mysql

# ./support-files/mysql.server startStarting MySQL. SUCCESS!  # cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld# chmod 755 /etc/init.d/mysqld # chkconfig mysqld on