在CentOS7上二进制包安装MySQL5.6

CentOS7上二进制包安装MySQL5.6

平时工作中都会用到MySQL，所以本次分享通过二进制包的形式安装MySQL5.6的方法

一、下载MySQL5.6二进制包

1. 打开mysql的官网www.mysql.com，点击"DOWNLOADS"，点击"Community"
   
2. 点击左侧的"MySQL Community Server"
   
3. 点击右侧的"MySQL Community Server 5.6"
   
4. 选择适合自己主机系统的二进制安装包
   
5. 这里有个小技巧，一般从官网下载二进制包会比较慢，这里可以在选择好要下载的二进制安装包之后，复制该包名，这里是"mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz"，然后去国内的一些镜像源里面找对应的包。这里我在清华大学镜像源中找到mysql这一项后搜索该文件名，即可快速找到并高速下载该文件。
   

二、主机方面的优化MySQL安装

1. 克隆一台CentOS7的主机，参考《CentOS7实验模板机搭建》
2. 配置hostname

# 配置hostname
HOSTNAME=mysql
hostnamectl set-hostname "$HOSTNAME"
echo "$HOSTNAME">/etc/hostname
echo "$(grep -E '127|::1' /etc/hosts)">/etc/hosts
echo "$(ip a|grep "inet "|grep -v 127|awk -F'[ /]' '{print $6}') $HOSTNAME">>/etc/hosts

3. 设置系统资源限制量

# 设置系统资源限制
echo -e '*\thard\tnproc \t65535'>>/etc/security/limits.conf
echo -e '*\tsoft\tnproc \t65535'>>/etc/security/limits.conf
echo -e '*\thard\tnofile\t65535'>>/etc/security/limits.conf
echo -e '*\tsoft\tnofile\t65535'>>/etc/security/limits.conf

4. 关闭numa：建议从BIOS设置中关闭numa，CentOS中检测BIOS层面是否关闭numa的命令

grep -i numa /var/log/dmesg
# No NUMA configuration found
# 显示此信息即为BIOS层面已经关numa
# 可以在OS层面关闭numa：
# sed -i 's/\<kernel.*$/& numa=off/g' /boot/grub/grub.conf

5. 重启主机

reboot

三、安装MySQL5.6二进制安装包

1. 创建系统用户

groupadd mysql
useradd -g mysql -s /sbin/nologin mysql

2. 下载二进制包，解压

cd /tmp
wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.6/mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz
cd /usr/local
tar -xf /tmp/mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.6.45-linux-glibc2.12-x86_64 mysql
chown -R mysql: mysql-5.6.45-linux-glibc2.12-x86_64
chown -R mysql: mysql
echo 'export PATH=$PATH:/usr/local/mysql/bin'>>/etc/profile 
export PATH=$PATH:/usr/local/mysql/bin

3. 创建数据目录

mkdir -pv /data/mysql/{run,log,data,binlog}
chown -R mysql: /data/mysql

4. 创建配置文件

SERVERID=3306
MEMSIZE=$(grep 'MemTotal' /proc/meminfo |awk '{printf ("%d\n",$2/1024*0.8)}')M
cat >/etc/my.cnf<<EOF
[client]
port = 3306
socket = /data/mysql/run/mysql.sock
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port = 3306
socket = /data/mysql/run/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data
open_files_limit = 65535
back_log = 100
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
log-error = /data/mysql/log/error.log
long_query_time = 0.5
server-id = ${SERVERID}
log-bin = /data/mysql/binlog/binlog${SERVERID}
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server=utf8
default-storage-engine=InnoDB
binlog_format=row
# gtid_mode=on
# log_slave_updates=1
# enforce_gtid_consistency=1
interactive_timeout=300
wait_timeout=300
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = ${MEMSIZE}
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M
EOF

5. 初始化数据库

cd /usr/local/mysql/scripts
yum -y install numactl perl libaio autoconf
./mysql_install_db --basedir=/usr/local/mysql \
--datadir=/data/mysql/data --defaults-file=/etc/my.cnf \
--user=mysql

6. 启动和配置开机启动，重启主机

cd /usr/local/mysql/bin
./mysqld_safe --defaults-file=/etc/my.cnf &
echo '/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &'>>/etc/rc.local
mysqladmin shutdown
reboot

7. 配置root密码

PASS=123456
mysqladmin -u root password "$PASS"
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '$PASS' WITH GRANT OPTION;"|mysql -uroot -p"$PASS" -Dmysql
echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '$PASS' WITH GRANT OPTION;"|mysql -uroot -p"$PASS" -Dmysql
echo "update user set password=password('$PASS') where user='root';"|mysql -uroot -p"$PASS" -Dmysql
echo "delete from user where user!='root';"|mysql -uroot -p"$PASS" -Dmysql
echo "drop database test;"| mysql -uroot -p"$PASS" -Dmysql
echo "flush privileges;"| mysql -uroot -p"$PASS" -Dmysql