compile install mysql 5.6.10 and optimize for low memory VPS on debian 6


compile

# download mysql-5.6.10 from https://dev.mysql.com/downloads/mysql/
tar zxf mysql-5.6.10
cd mysql-5.6.10

# set default charset to utf8
cmake -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

# install to /usr/local/mysql
make && make install

initialize database

# create user mysql and change owner of /usr/local/mysql to mysql
groupadd mysql
useradd mysql -g mysql
chown -R mysql:mysql /usr/local/mysql

# still in mysql-5.6.10
scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

cd /usr/local/mysql

# copy mysql default configuration
cp support-files/my-default.cnf my.cnf

# start mysql
bin/mysqld_safe --user=mysql &

# change root password and so on
bin/mysql_secure_installation

optimize

edit my.cnf, append configurations below

user=mysql

# Since 5.5.5, mysql uses innodb as default storage engine,
# but innodb requires more memory than MyIASM. 
# So, on my low memory machine, I have to disable it.

# disable innodb and set MyISAM as default storage engine
skip-innodb
default-storage-engine=MyISAM
default-tmp-storage-engine=MEMORY
loose-innodb-trx=0
loose-innodb-locks=0
loose-innodb-lock-waits=0
loose-innodb-cmp=0
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0
loose-innodb-cmpmem=0
loose-innodb-cmpmem-reset=0
loose-innodb-buffer-page=0
loose-innodb-buffer-page-lru=0
loose-innodb-buffer-pool-stats=0
loose-innodb-metrics=0
loose-innodb-ft-default-stopword=0
loose-innodb-ft-inserted=0
loose-innodb-ft-deleted=0
loose-innodb-ft-being-deleted=0
loose-innodb-ft-config=0
loose-innodb-ft-index-cache=0
loose-innodb-ft-index-table=0
loose-innodb-sys-tables=0
loose-innodb-sys-tablestats=0
loose-innodb-sys-indexes=0
loose-innodb-sys-columns=0
loose-innodb-sys-fields=0
loose-innodb-sys-foreign=0
loose-innodb-sys-foreign-cols=0

# optimize for low memory
key_buffer=16K
table_open_cache=4
query_cache_limit=256K
query_cache_size=4M
max_allowed_packet=1M
sort_buffer_size=64K
read_buffer_size=256K
thread_stack=64K

# only local
skip-networking

restart mysql

# in /usr/local/mysql
bin/mysqladmin shutdown -u root -p

# start mysql without user parameter
bin/mysqld_safe &

# check memory usage
# on my machine, mysql takes about 32M 
# in contrast to 60M before optimization
free -m

# check default storage engine
# in /usr/local/mysql
bin/mysql -u root -p

# show engines\G