Linux下MYSQL 5.6 源码安装

1 操作系统配置

1.1 RAID 配置

建议使用RAID 10，生产环境建议使用物理机，最好使用SSD。

RAID相关参数推荐设置如下：

1、Write Policy设置为WriteBack（WB）.

2、Read Policy设置为ReadAdaptive或者normal，数据库服务器不建议设置ReadAhead.

3、IO Policy设置为Direct .

1.2 最大打开文件数|进程数

# vim /etc/security/limits.conf
*  soft  nofile    65535
*  hard  nofile   65535
*  soft        nproc         65535
*  hard        nproc         65535

1.3 关闭NUMA

1、可以在bios中关闭（物理机推荐使用这种方式进行修改）；

2、/etc/grub.conf的kernel行最后添加numa=off和/boot/grub/grub.conf 2个文件都要改，最后添加numa=off。

1.4 内核参数优化

# vim  /etc/sysctl.conf
fs.file-max = 65536
vm.min_free_kbytes = 1048576
vm.swappiness = 10
vm.dirty_ratio = 10
vm.vfs_cache_pressure=150
vm.dirty_ratio = 10
vm.overcommit_memory = 1
vm.drop_caches = 1
kernel.panic = 60
net.core.somaxconn = 8192
net.ipv4.tcp_max_syn_backlog = 8192
vm.zone_reclaim_mode = 0

1.5 IO调度算法

推荐使用deadline或者noop调度算法。

命令如下（临时生效）：

# echo {SCHEDULER-NAME} > /sys/block/{DEVICE-NAME}/queue/scheduler

DEVICE-NAME: 如sda、sdb等

然后在/etc/rc.local中加入

echo {SCHEDULER-NAME} > /sys/block/{DEVICE-NAME}/queue/scheduler

1.6 关闭THP

# echo never > /sys/kernel/mm/transparent_hugepage/enabled
# echo never > /sys/kernel/mm/transparent_hugepage/defrag

然后在/etc/rc.local中加入

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

2 数据库安装

2.1 安装系统依赖包

# yum install -y nc rsync screen lrzsz expect cmake make gcc gcc-c++ perl bison bc thread libaio libaio-devel zlib zlib-devel nc python python-devel cmake ncurses-devel scurses sysstat perl-ExtUtils-CBuilder perl-CPAN perl-Log-Dispatch perl-Config-Tiny perl-Parallel-ForkManager perl-Config-IniFiles

2.2 创建用户

# groupadd mysql
# useradd -g mysql mysql

2.3目录规划

数据文件目录:/u02/my3306/data

日志文件目录:/u02/my3306/log/iblog

binlog日志目录: /u02/my3306/log/binlog

临时目录: /u02/my3306/tmp

运行目录: /u02/my3306/run

# mkdir -p /data/my3306
# mkdir -p /u02/my3306/data
# mkdir -p /u02/my3306/log/iblog
# mkdir -p /u02/my3306/log/binlog
# mkdir -p  /u02/my3306/tmp
# mkdir -p /u02/my3306/run# chown -R mysql:mysql /u02/my3306

2.4解压编译

# tar zxvf mysql-5.6.35.tar.gz
# cd mysql-5.6.35/
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6 
-DDEFAULT_CHARSET=utf8mb4 
-DEXTRA_CHARSETS=all 
-DWITH_MYISAM_STORAGE_ENGINE=1 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITH_PARTITION_STORAGE_ENGINE=1 
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 
-DWITH_FEDERATED_STORAGE_ENGINE=1

# make
# make install

2.5 创建参数文件

# vim /u02/my3306/my.cnf
[client]
port=3306
default-character-set=utf8mb4
socket=/u02/my3306/mysql.sock

[mysql]
pid_file=/u02/my3306/run/mysqld.pid
default-character-set=utf8mb4
no-auto-rehash

[mysqld]
default-time-zone = '+8:00'
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true

# GTID
gtid-mode                = ON
enforce_gtid_consistency = 1
log_slave_updates        = 1
 
# system
basedir=/usr/local/mysql5.6
datadir=/u02/my3306/data
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/u02/my3306/run/mysqld.pid
port=3306
server_id=20633306
skip_name_resolve=ON
socket=/u02/my3306/run/mysql.sock
tmpdir=/u02/my3306/tmp
query_cache_type = 0
query_cache_size = 0
read-buffer-size = 32M

#binlog
log_bin=/u02/my3306/log/binlog/binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100

#logging
log_error=/u02/my3306/log/error.log
slow_query_log_file=/u02/my3306/log/slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1
#记录下所有走全表扫描的SQL
log-queries-not-using-indexes = 0

#relay
relay_log=/u02/my3306/log/relaylog
relay_log_index=/u02/my3306/log/relay.index
relay_log_info_file=/u02/my3306/log/relay-log.info

#slave
slave_load_tmpdir=/u02/my3306/tmp
slave_skip_errors=OFF

#innodb
innodb_data_home_dir=/u02/my3306/log/iblog
innodb_log_group_home_dir=/u02/my3306/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8
 
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 4G  #根据机器实际内存配置
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=1048576000
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=75
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10
 
[mysqld_safe]
datadir=/u02/my3306/data

# chown -R mysql.mysql /u02

2.6 初始化数据库

# cd /usr/local/mysql5.6
# ./scripts/mysql_install_db --defaults-file=/u02/my3306/my.cnf --datadir=/u02/my3306/data --user=mysql

2.7 启动数据库

su - mysql -c "/usr/local/mysql5.6/bin/mysqld_safe --defaults-file=/u02/my3306/my.cnf &"

2.8 初始化用户,删除test库

# /usr/local/mysql5.6/bin/mysql -uroot -p -S /u02/my3306/run/mysql.sock  -e "delete from mysql.user where user='';"
Enter password:   ===>直接Enter
mysql> set password=password('xxxxxxx');   
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

2.9 登陆数据库

/usr/local/mysql5.6/bin/mysql -uroot -p -S /u02/my3306/run/mysql.sock   #然后回车输入root用户密码即可

2.10 关闭数据库

/usr/local/mysql5.6/bin/mysqladmin -uroot -p -S /u02/my3306/run/mysql.sock shutdown