mysql5.6安装

删除 mysql 5.x 旧版本
查看是否存在旧的 mysql 服务（mysql-libs安装更新后卸载，避免依赖报错）

rpm -qa | grep -i mysql
rpm -qa | grep -i MySQL | xargs yum remove -y

如果包存在，按照下列顺序卸载
按照顺序卸载

rpm -e MySQL-shared-advanced
rpm -e MySQL-test-advanced
rpm -e MySQL-embedded-advanced
rpm -e MySQL-server-advanced
rpm -e MySQL-devel-advanced
rpm -e MySQL-client-advanced

该依赖包有依赖时候不需要卸载

#rpm -e MySQL-shared-compat-advanced

#必要情况下清理无用的文件和目录

rm -f /usr/my*.cnf
rm -rf /u02/mysql/*
rm -f /var/lock/subsys/mysql
rm -rf /var/lib/mysql/
rm -rf /etc/my.cnf
rm -rf /root/.mysql_secret

1、安装
安装基础包

wget http://10.0.42.1:4201/db-mysql/mysql-5.6.27-r6.zip
unzip mysql-5.6.27-r6.zip

rpm -ivh MySQL-shared-compat-advanced-5.6.27-1.el6.x86_64*.rpm
rpm -e mysql-libs-5.1.73-5.el6_6.x86_64                                

rpm -ivh MySQL-client-advanced-5.6.27-1.el6.x86_64*.rpm
rpm -ivh MySQL-devel-advanced-5.6.27-1.el6.x86_64*.rpm
rpm -ivh MySQL-server-advanced-5.6.27-1.el6.x86_64*.rpm
rpm -ivh MySQL-embedded-advanced-5.6.27-1.el6.x86_64*.rpm
rpm -ivh MySQL-shared-advanced-5.6.27-1.el6.x86_64*.rpm
rpm -ivh MySQL-test-advanced-5.6.27-1.el6.x86_64*.rpm

2、优化
配置大内存页（必要情况需要重启服务器）
#针对 128G 内存的服务器，配置 96G 使用比例（其他内存单独计算)

id mysql
vi /etc/sysctl.conf
kernel.shmmax = 105824387072
kernel.shmall = 25836032
vm.nr_hugepages = 50461
vm.hugetlb_shm_group = 498

sysctl -p

vi /etc/sysctl.conf
kernel.shmmax = 105824387072
kernel.shmall = 25836032
vm.nr_hugepages = 50461
vm.hugetlb_shm_group = 498

sysctl -p

3、配置
获取配置文件 128G 版本

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
# 1 general config
port=3306
socket=/tmp/mysql.sock
datadir=/u02/mysql
binlog_cache_size=1M
default-storage-engine=innodb
event_scheduler=1
federated
join_buffer_size=64M
local-infile=1
long_query_time=10
slow_query_log
lower_case_table_names=1
max_heap_table_size=2048M
max_length_for_sort_data=2048
query_cache_size=0
query_cache_type=0
skip-name-resolve
thread_cache_size=1024
thread_stack=256K
tmp_table_size=512M
sort_buffer_size=2M
large-pages=1
table_open_cache=10000
innodb_open_files=5000

# 2 network config
back_log=1000
max_connect_errors=500
max_connections=1000
max_user_connections=1000
max_delayed_threads=200
max_prepared_stmt_count=65528
max_allowed_packet=128M
net_read_timeout=36000
net_write_timeout=36000
wait_timeout=43200

# 3 myisam config
concurrent_insert=2
ft_min_word_len=4
myisam_recover
myisam_repair_threads=1
skip-external-locking

# 4 innodb config
innodb_file_per_table
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=12G
innodb_data_file_path=ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=120
innodb_log_buffer_size=256M
innodb_log_file_size=2048M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=95
innodb_read_io_threads=16
innodb_thread_concurrency=16
innodb_write_io_threads=16
innodb_data_home_dir=/u02/mysql
innodb_log_group_home_dir=/u02/mysql
innodb_old_blocks_time=1000
innodb_flush_method=O_DIRECT

# ssd
innodb_flush_neighbors=0
table_open_cache_instances=16
innodb_buffer_pool_instances=32
innodb_io_capacity=1000
innodb_io_capacity_max=2500
innodb_log_compressed_pages=0
innodb_sort_buffer_size=2M
innodb_doublewrite=0
metadata_locks_hash_instances=256
innodb_stats_persistent_sample_pages=1000
#innodb_page_size=4096
#innodb_change_buffer_max_size=50

# 5 replication config
expire_logs_days=7
#log-slave-updates
slave-skip-errors=all
slave_net_timeout=36000
server-id={}

## for master
#log-bin=yd{}
#relay-log=yd{}-relay-bin

## for slave master
#log-slave-updates 

## for slave
#replicate-do-db=ydserver
#replicate-do-table=ydserver.czy

[mysql]
no-auto-rehash

[mysqldump]
quick
max_allowed_packet=128M

#作为主服务器需要开启日志（默认不需要）

log-bin = yd131
relay-log=yd131-relay-bin

需要传递上级同步，需要增加（默认不需要）

log-slave-updates

cp my128G.cnf /etc/my.cnf
rm -f /usr/my*.cnf

迁移应用数据目录

mkdir -p /u02/mysql
cp -ar /var/lib/mysql/* /u02/mysql/
chown -R mysql.mysql /u02/mysql
rm -f /u02/mysql/ib*

初始化配置

cat /root/.mysql_secret
g1aY8PC075flFmkj
service mysql start

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
/usr/bin/mysql_secure_installation
enter y <pwd> y y y y

加密码----

service mysql restart
mysql -uroot --password=IINJvig6PyiMT3fd
SET PASSWORD = PASSWORD('3QgXSno9TEpBK2rz');
GRANT ALL PRIVILEGES ON *.* TO 'ggs'@'%' IDENTIFIED BY 'V0nwW7MKjsFsDgFmAcHk' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

配置自动启动

chkconfig mysql on