由于网上很多都是CentOS6安装MySQL5.6的教程，并且没有写在生产环境下的优化方案，所以写了这篇教程，试用于项目初期，只需要一个MySQL数据库的情况。教程更新于2017年6月，支持Mariadb10.0版本，可直接用于生产环境。

（可能有些懂技术的朋友会问，生产环境下不需要主从或集群么，答案是肯定的。而对于一个产品人，你只需要会在Linux下安装单服务器的MySQL就足够了，剩下的就交给专业的技术人员去处理吧）


1、利用CentOS自带的yum命令安装、升级所需的程序库

yum -y upgrade

yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel readline-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers bison libevent-devel cmake iptables-services


2、下载Mariadb10.1的源码包

wget http://downloads.mariadb.org/f/mariadb-10.1.24/source/mariadb-10.1.24.tar.gz

wget http://github.com/jemalloc/jemalloc/releases/download/4.5.0/jemalloc-4.5.0.tar.bz2

如果下载慢请参考《CentOS更改DNS加速国外资源下载》


3、编译安装Jemalloc

tar jxvf jemalloc-4.5.0.tar.bz2

cd jemalloc-4.5.0/

./configure

make

make install

cd ../

ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib/libjemalloc.so.2

ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.2


4、编译安装Mariadb

创建mysql用户与用户组

/usr/sbin/groupadd mysql

/usr/sbin/useradd -g mysql mysql

编译安装MariaDB

tar zxvf mariadb-10.1.24.tar.gz

cd mariadb-10.1.24/

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql -DMYSQL_UNIX_ADDR=/data0/mysql/3306/mysql.sock -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DEXTRA_CHARSETS=complex -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DCMAKE_EXE_LINKER_FLAGS=\"-ljemalloc\" -DWITH_SAFEMALLOC=OFF -DMYSQL_DATADIR=/data0/mysql/3306/data -DMYSQL_USER=mysql

make

make install

ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18

chmod +w /usr/local/webserver/mysql

chown -R mysql:mysql /usr/local/webserver/mysql

如果编译出错将参数 -DWITH_EMBEDDED_SERVER=1 删除并执行 make clean 后重新编译


5、一些相关设置

创建MySQL数据库存放目录

mkdir -p /data0/mysql/3306/data/

mkdir -p /data0/mysql/3306/binlog/

mkdir -p /data0/mysql/3306/relaylog/

chown -R mysql:mysql /data0/mysql/

以mysql用户帐号的身份建立数据表

/usr/local/webserver/mysql/scripts/mysql_install_db --collation-server=utf8_general_ci --basedir=/usr/local/webserver/mysql --datadir=/data0/mysql/3306/data --user=mysql

创建my.cnf配置文件

vi /usr/local/webserver/mysql/my.cnf

输入以下内容（适合2G内存以上）

# Example MySQL config file for very large systems.

#

# This is for a large system with memory of 1G-2G where the system runs mainly

# MySQL.

#

# MySQL programs look for option files in a set of

# locations which depend on the deployment platform.

# You can copy this option file to one of those

# locations. For information about these locations, see:

# http://dev.mysql.com/doc/mysql/en/option-files.html

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the \"--help\" option.


# The following options will be passed to all MySQL clients

[client]

character-set-server = utf8

port                = 3306

socket                = /tmp/mysql.sock



# The MySQL server

[mysqld]

character-set-server = utf8

replicate-ignore-db = mysql

replicate-ignore-db = test

replicate-ignore-db = information_schema

user                = mysql

port                = 3306

socket                = /tmp/mysql.sock

basedir         = /usr/local/webserver/mysql

datadir         = /data0/mysql/3306/data

log-error         = /data0/mysql/3306/mysql_error.log

pid-file         = /data0/mysql/3306/mysql.pid

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

table_cache = 512

external-locking = FALSE

key_buffer_size = 256M

max_allowed_packet = 32M

table_open_cache = 512

sort_buffer_size = 1M

join_buffer_size = 1M

read_buffer_size = 1M

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

thread_cache_size = 300

query_cache_size = 512M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

# Try number of CPU\'s*2 for thread_concurrency

thread_concurrency = 8

default-storage-engine = InnoDB

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 246M

max_heap_table_size = 246M

long_query_time = 2


# binary logging is required for replication

log-slave-updates

log-bin=/data0/mysql/3306/binlog/binlog

binlog_cache_size = 4M

binlog_format = MIXED

max_binlog_cache_size = 8M

max_binlog_size = 1G


relay-log-index = /data0/mysql/3306/relaylog/relaylog

relay-log-info-file = /data0/mysql/3306/relaylog/relaylog

relay-log = /data0/mysql/3306/relaylog/relaylog

expire_logs_days = 30


interactive_timeout = 120

wait_timeout = 120


# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id        = 1


skip-name-resolve

#master-connect-retry = 10

slave-skip-errors = 1032,1062,126,1114,1146,1048,1396


# The replication master for this slave - required

#master-host     =   <hostname>

#

# The username the slave will use for authentication when connecting

# to the master - required

#master-user     =   <username>

#

# The password the slave will authenticate with when connecting to

# the master - required

#master-password =   <password>

#

# The port the master is listening on.

# optional - defaults to 3306

#master-port     =  3306


# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /data0/mysql/3306/data

innodb_data_file_path = ibdata1:256M:autoextend

#innodb_log_group_home_dir = /data0/mysql/3306/data

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1G

innodb_additional_mem_pool_size = 16M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 128M

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 120

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_file_per_table = 1

#log-slow-queries = /data0/mysql/3306/slow.log

#long_query_time = 10


[mysqldump]

quick

max_allowed_packet = 32M


#[mysql]

#no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates


#[myisamchk]

#key_buffer_size = 256M

#sort_buffer_size = 256M

#read_buffer = 2M

#write_buffer = 2M


#[mysqlhotcopy]

#interactive-timeout

小于2G内存，一般使用MySQL自带的配置文件即可 @注意：按上文方法配置的请跳过这一步

cp /usr/local/webserver/mysql/support-files/my-large.cnf /usr/local/webserver/mysql/my.cnf


6、配置MySQL服务脚本：

安装服务脚本

cd support-files/

cp mysql.server /etc/rc.d/init.d/mysqld

chmod +x /etc/init.d/mysqld

cd ../

make clean

cd ../

编辑服务脚本文件

vi /etc/init.d/mysqld

查找并修改以下变量内容

basedir=/usr/local/webserver/mysql

datadir=/data0/mysql/3306/data

加入启动项

chkconfig --add mysqld

chkconfig --level 345 mysqld on

启动MySQL服务

service mysqld start

设置数据库root用户密码 @友情提醒：最后的密码就不要跟着复制了 - -！

/usr/local/webserver/mysql/bin/mysqladmin password 12345678