Linux运维笔记|自动化运维攻城狮

MySQL 8 安装部署详细文档

mysql 8  

添加MySQL账户

groupadd mysql
useradd -gmysql -s /sbin/nologin -M mysql

删除系统自带的MySQL及配置

rm -rf /etc/my.cnf /etc/my.cnf.d/
rpm -e mariadb-libs mysql-libs --nodeps

安装依赖

yum install -y gcc gcc-c++ ncurses ncurses-devel libaio libaio-devel bison bison-devel bzip2 
yum remove -y cmake

安装Cmake3版本

wget https://cmake.org/files/v3.14/cmake-3.14.1.tar.gz
tar xzf cmake-3.14.1.tar.gz
cd cmake-3.14.1/
./bootstrap
gmake
gmake install
ln -s /usr/local/bin/cmake /usr/bin
cd ..

安装scl库，并安装GCC6，暂时切换到GCC6版本

yum -y install centos-release-scl
yum -y install devtoolset-6-gcc devtoolset-6-gcc-c++ devtoolset-6-binutils
scl enable devtoolset-6 bash

编译MySQL

wget http://ftp.kaist.ac.kr/mysql/Downloads/MySQL-8.0/mysql-boost-8.0.16.tar.gz
tar -xzf mysql-boost-8.0.16.tar.gz
cd mysql-8.0.16
mkdir bld
cd bld

cmake .. -DWITH_BOOST=../boost \
-DCMAKE_INSTALL_PREFIX=/data/soft/mysql \
-DMYSQL_DATADIR=/data/db/mysql \
-DMYSQL_UNIX_ADDR=/data/tmp/mysql.sock \
-DMYSQL_TCP_PORT=3306

make -j4
make install

配置数据库环境变量

echo 'export PATH=$PATH:/data/soft/mysql/bin' >> /etc/bashrc

ln -s /data/soft/mysql/bin/mysql /usr/bin/
ln -s /data/soft/mysql/bin/mysqladmin /usr/bin/
ln -s /data/soft/mysql/bin/mysqlbinlog /usr/bin/
ln -s /data/soft/mysql/bin/mysqldump /usr/bin/

创建数据目录

mkdir -p /data/db/mysql/relaylog
mkdir -p /data/db/mysql/binlog
mkdir -p /data/db/mysql/logs
mkdir -p /data/db/mysql/data
chown -R mysql.mysql /data/db/mysql/

启动脚本

复制启动脚本

cp support-files/mysql.server /etc/init.d/

增加执行权限

chmod u+x /etc/init.d/mysql.server

添加为服务启动

chkconfig --add mysql.server
chkconfig mysql.server on
cp /etc/init.d/mysql.server /data/db/mysql/
chown mysql.mysql /data/db/mysql/mysql.server

mysql默认配置my.cnf

增加配置文件

vim /etc/my.cnf

需要注意修改端口、serverid、数据目录、innodb内存、线程数等位置

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

[mysqld]
character_set_server = utf8mb4
init_connect = 'SET NAMES utf8mb4'

user            = mysql
port            = 3306
socket          = /data/tmp/mysql.sock
datadir         = /data/db/mysql/data
log_error       = /data/db/mysql/logs/mysql_error.log
log_bin         = /data/db/mysql/binlog/binlog
relay_log       = /data/db/mysql/relaylog/relaylog

max_connections = 10000
back_log = 3000
max_connect_errors = 7000

interactive_timeout = 60
wait_timeout = 60

auto_increment_increment = 3
auto_increment_offset = 1

max_allowed_packet = 32M
thread_cache_size = 300

replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = test.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%

default_authentication_plugin=mysql_native_password
log_timestamps = SYSTEM
default_time_zone = '+8:00'
skip_external_locking = false
skip_name_resolve
explicit_defaults_for_timestamp=true
log_slave_updates = ON

slow_query_log = on
slow_query_log_file = /data/db/mysql/logs/slow.log
long_query_time = 1


innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size=16M
innodb_buffer_pool_size=4096M
innodb_log_file_size=2048M
innodb_file_per_table = 1
innodb_open_files = 10240
innodb_flush_method=O_DIRECT

sync_binlog = 2
binlog_format = mixed
binlog_cache_size = 4M
max_binlog_size = 1G
binlog_expire_logs_seconds = 604800

server_id = 1057


[mysqldump]
quick
max_allowed_packet = 64M

初始化数据库

cd /data/soft/mysql/bin/
./mysqld --initialize --user=mysql --basedir=/data/soft/mysql --datadir=/data/db/mysql/data
./mysql_ssl_rsa_setup

配置密码、清空TEMP库、空密码 ==

  • 启动数据库
service mysql.server start
  • 查询root密码
grep temporary password /data/db/mysql/logs/mysql_error.log
  • 根据提示完成修改root密码等操作
cd /data/soft/mysql/bin/
./mysql_secure_installation
  • 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码' PASSWORD EXPIRE NEVER;
  • 创建用户并授权
# 创建用户test和密码为123456的用户：
# 本地登录用户
create user 'test'@'localhost' identified by '123456'  
# 远程登录用户
create user 'test'@'%' identified by '123456';

#授权数据库所有表的所有操作给test用户：

#本地使用
grant all privileges on *.* to 'test'@'localhost'

#远程使用
grant all privileges on *.* to 'test'@'%'

时间同步服务

  • '''注意'''
  • '''如果已经配置有NTP服务器，则需要先卸载NTP'''
  • centos 7 系统
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
  • centos 6 系统
yum -y install chrony
chkconfig chronyd on
service chronyd start