mysql 5.7手动安装部署

mysql5.7手动、自动安装部署

#上传安装包和配置文件
mkdir /application
cd /application
rz -y mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz  my.cnf
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz  
##安装依赖 
yum -y install numactl.x86_64
##将安装前工作
mv mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql
cd /usr/local/mysql/bin/
##创建启动账号
groupadd mysql 
useradd -g mysql  -s  /sbin/nologin -d /usr/local/mysql/  -M mysql
##创建数据目录
mkdir  /data/mysql/
chown mysql:mysql /data/mysql/
chown mysql:mysql /usr/local/mysql/
##数据库初始化
./mysqld --defaults-file=/etc/my.cnf --initialize --user=user --basedir=/usr/local/mysql/  --datadir=/data/mysql/
##将mysql配置文件上传
##可使用在线mysql配置文件生成工具
## 
cp -r /application/my.cnf /etc/
##添加环境变量
cp -r /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 
echo PATH=$PATH:/usr/local/mysql/bin >>/etc/profile 
source /etc/profile
##启动
/etc/init.d/mysqld start
##登录测试 
mysql -uroot -p 

#改密码
### 添加跳过密码登陆
vim /etc/my.cnf
##添加参数
skip-grant-tables
##重启
/etc/init.d/mysqld restart 
##更改密码
mysql -uroot -p 
UPDATE mysql.user SET authentication_string=PASSWORD('test@123') where USER='root';
ALTER USER USER() IDENTIFIED BY 'test@123';
##授权访问（*.* databasename.tablename）（root@'192.0.0.1' 授权哪个账号通过那个ip或ip段访问）
grant all privileges on *.* to 'root'@'147.1.5.%' identified by 'test@123';
##刷新
FLUSH PRIVILEGES;
##注销跳过密码登陆
vim /etc/my.cnf
#skip-grant-tables
#重启
/etc/init.d/mysqld restart

my.cnf

## my.cnf for MySQL 5.7/8.0
[client]
port    = 3306
socket  = /data/mysql/mysql.sock

[mysql]
prompt="\u@mysqldb \R:\m:\s [\d]> "
auto-rehash

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-grant-tables
user    = mysql
port    = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket  = /data/mysql/mysql.sock
lower_case_table_names=1
pid-file = mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
default_time_zone = "+8:00"
open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 2
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3309
log-bin = /data/mysql/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 9216M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/undolog
innodb_undo_tablespaces = 95

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

#注意：MySQL 8.0.16开始删除该选项
internal_tmp_disk_storage_engine = InnoDB

# some var for MySQL 5.7
innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_size = 0
query_cache_type = 0
innodb_undo_logs = 128

innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

[mysqldump]
quick
max_allowed_packet = 100M

自动化安装脚本

#!/bin/bash
#create by myq
#at 2017-12-12
#any question to call the phone number : 110

clear
echo "###################################################"
echo "Program this scripts is for relase your hands."
echo "Please read this illustrate carefully."
echo "It's auto install or uninstall mysql server."
echo "There is four file in the scripts directory."
echo "my.cnf is mysql configure file,do not remove."
echo "mysqld.service is used for add mysql to systemd on RedHat 7 or Centos 7."
echo "*.tar.gz is mysql binary package."
echo "The mysql server version must be 5.7 or later."
echo "The package is Linux - Generic tar file."
echo "Please carefull enter parameter when hint is appear."
echo "Any question to call the phone number : 110."
echo "May you be happy and prosperous."
echo "###################################################"
echo

#defind variables
#get hostname
hn=$(hostname)
#set mysql's birnary file directory
azdir=/usr/local
#set mysql's directory for datafile,logfile and so on
yxdir=/data
csdir=$(echo ${yxdir:1})
#set the binary install file directory
sourcedir=$(pwd)
#get binary install file name
filename=$(ls $sourcedir/mysql*.tar.gz | awk -F '/' '{print $3}')
#get memory info
mem=$(free -m|grep -i mem|awk '{print $2}')
smem=$(echo "$mem*0.8"|bc)
fmem=$(echo ${smem%.*}M)
#get os release
osrel=$(grep -o '[[:digit:]]' /etc/redhat-release|sed '2,$d')
#get install or uninstall message
echo "Are you want to install or unistall? 'i' for install; 'u' for uninstall:"
read isins
if [ $isins == 'i' ]; then
    #clear installed mysql
    echo "clear installed mysql"
    rpm -e mysql --nodeps > /dev/null 2>&1
    rpm -e mysql-devel --nodeps > /dev/null 2>&1
    rpm -e mysql-server --nodeps > /dev/null 2>&1
    echo "done..........................."
    echo ""

    #create user running mysql
    echo "create user running mysql"
      userdel -r mysql > /dev/null 2>&1
      useradd -s /sbin/nologin mysql
    echo "done..........................."
    echo ""

    #copy mysql source install package
    echo "copy mysql source install package"
      rm -rf $azdir/mysql
      cp $sourcedir/$filename $azdir/
    if [ $? == 0 ]; then
      echo "done..........................."
    else
      echo "copy failed!!!"
      exit 1
    fi
      echo ""

    #untar install package
    echo "untar install package"
    cd $azdir
    tar -zxf $azdir/$filename
    rm -f $azdir/$filename
    echo "done..........................."
    echo ""

    #change mode on install dir
    echo "change mode $azdir/mysql"
    mv $azdir/mysql-* $azdir/mysql
    if [ $? == 0 ]; then
      chown -R mysql.mysql $azdir/mysql
      ln -fs $azdir/mysql/bin/* /usr/bin/
      echo "done..........................."
    else
      echo "operation failed!!!"
      exit 1
    fi
    echo ""

    #create data relate directory
    echo "create data relate directory"
    mkdir -p $yxdir/mysql/data
    mkdir -p $yxdir/mysql/logs
    mkdir -p $yxdir/mysql/script
    mkdir -p $yxdir/mysql/backup
    if [ $? == 0 ]; then
      chown -R mysql.mysql $yxdir/mysql
      echo "done..........................."
    else
      echo "create dir failed !!!"
      exit 1
    fi
      echo ""

    if [ $osrel == '7' ]; then
        #add to services
        echo "add to services"
        cp -f $sourcedir/mysqld.service /etc/systemd/system
        if [ $? == 0 ]; then
          systemctl enable mysqld.service > /dev/null 2>&1
            if [ $? == 0 ]; then
              chkconfig mysqld off
            else
              echo "add mysqld service failed!!!"
              exit 1
            fi
          echo "done..........................."
        else
          echo "create dir failed !!!"
          exit 1
        fi
        echo ""
    else
        #add to services
        echo "add to services"
        cp -f $azdir/mysql/support-files/mysql.server /etc/init.d/mysqld
        if [ $? == 0 ]; then
          chkconfig --add mysqld
            if [ $? == 0 ]; then
              chkconfig mysqld off
            else
              echo "add mysqld service failed!!!"
              exit 1
            fi
          echo "done..........................."
        else
          echo "create dir failed !!!"
          exit 1
        fi
        echo ""
    fi

    #add error log file
    echo "add error log file"
    touch $yxdir/mysql/logs/$hn.err
    chown mysql:mysql $yxdir/mysql/logs/$hn.err
    echo "done..........................."
    echo ""

    #initialize mysql
    echo "initialize mysql"
    $azdir/mysql/bin/mysqld --initialize --basedir=$azdir/mysql --datadir=$yxdir/mysql/data --user=mysql > /tmp/myqpwd.tt 2>&1
    echo "done..........................."
    echo ""

    #copy configure file to /etc
    echo "copy configure file to /etc"
    rm -f /etc/my.cnf
    cp $sourcedir/my.cnf /etc/
    echo "done..........................."
    echo ""

    #modify my.cnf
    echo "modify configure file"
    #sed -i -e "s/db1/$hn/g" /etc/my.cnf
    sid="1"$(date +"%H%M%S")
    sed -i -e "s/server_id=1/server_id=$sid/g" /etc/my.cnf
    sed -i -e "s/innodb_buffer_pool_size=256M/innodb_buffer_pool_size=$fmem/g" /etc/my.cnf
        sed -i -e "s/datadir=\/var\/mysql\/data/datadir=\/$csdir\/mysql\/data/g" /etc/my.cnf
        sed -i -e "s/pid-file=\/var\/mysql\/logs\/db1.pid/pid-file=\/$csdir\/mysql\/logs\/$hn.pid/g" /etc/my.cnf
        sed -i -e "s/general_log_file=\/var\/mysql\/logs\/db1.general/general_log_file=\/$csdir\/mysql\/logs\/$hn.general/g" /etc/my.cnf
        sed -i -e "s/log-bin=\/var\/mysql\/logs\/db1-bin/log-bin=\/$csdir\/mysql\/logs\/$hn-bin/g" /etc/my.cnf
        sed -i -e "s/log_bin_index=\/var\/mysql\/logs\/db1.index/log_bin_index=\/$csdir\/mysql\/logs\/$hn.index/g" /etc/my.cnf
        sed -i -e "s/slow_query_log_file=\/var\/mysql\/logs\/db1.slow/slow_query_log_file=\/$csdir\/mysql\/logs\/$hn.slow/g" /etc/my.cnf
        sed -i -e "s/log_error=\/var\/mysql\/logs\/db1.err/log_error=\/$csdir\/mysql\/logs\/$hn.err/g" /etc/my.cnf
    sed -i -e "s/relay_log=\/var\/mysql\/logs\/db1-relay/relay_log=\/$csdir\/mysql\/logs\/$hn-relay/g" /etc/my.cnf
    echo "done..........................."
    echo ""

    #change root password
    echo "change root password"
    if [ $osrel == '7' ]; then
        systemctl start mysqld.service
    else
        service mysqld start
    fi
    if [ $? == 0 ]; then
      pwd=$(cat /tmp/myqpwd.tt | grep password | awk '{print $11}')
      /usr/bin/mysqladmin -u root -p$pwd password 123 > /dev/null 2>&1
      echo "done..........................."
    else
      echo "mysql start failed!!!"
      exit 1
    fi
    echo ""

    #install  plugin and set parameters
    echo "install semi plugin"
    /usr/bin/mysql -e "install plugin rpl_semi_sync_master soname 'semisync_master.so';"
    /usr/bin/mysql -e "install plugin rpl_semi_sync_slave soname 'semisync_slave.so';"
    /usr/bin/mysql -e "reset master;"
    sed -i -e "s/#rpl_semi_sync_master_enabled=on/rpl_semi_sync_master_enabled=on/g" /etc/my.cnf
    sed -i -e "s/#rpl_semi_sync_master_timeout=1000/rpl_semi_sync_master_timeout=1000/g" /etc/my.cnf
    sed -i -e "s/#rpl_semi_sync_slave_enabled=on/rpl_semi_sync_slave_enabled=on/g" /etc/my.cnf
    echo "done.........................."
    echo ""

    #restart mysql server
        rm -f /tmp/myqpwd.tt
        /usr/bin/mysql -e "reset master;"
    if [ $osrel == '7' ]; then
        systemctl stop mysqld.service
        rm -f $yxdir/mysql/logs/*bin* $yxdir/mysql/logs/*.general $yxdir/mysql/logs/*.slow $yxdir/mysql/logs/*.index $yxdir/my        sql/logs/*relay*
        systemctl start mysqld.service
    else
                 service mysqld stop
                 rm -f $yxdir/mysql/logs/*bin* $yxdir/mysql/logs/*.general $yxdir/mysql/logs/*.slow $yxdir/mysql/logs/*.index $yxdir/mysql/logs/*relay*
                 service mysqld start
    fi
        echo "Congratulations,mysql install successful!!!"
        echo "Install directory is /usr/local/mysql"
        echo "Data directory is /var/mysql"
        echo "Initial root password is : 123"
                echo "On linux 6,use 'service mysqld start|stop' to manage mysql service"
                echo "On linux 7,use 'systemctl start|stop mysqld.service' to manage mysql service"
        

elif [ $isins == 'u' ]; then
    #clear installed mysql
    echo "clear installed mysql"
    rpm -e mysql --nodeps > /dev/null 2>&1
    rpm -e mysql-devel --nodeps > /dev/null 2>&1
    rpm -e mysql-server --nodeps > /dev/null 2>&1
    echo "done..........................."
    echo ""

    #stop mysql service
    if [ $osrel == '7' ]; then
        systemctl stop mysqld.service > /dev/null 2>&1
        #clear mysql service
        echo "clear mysql service ................"
        systemctl disable mysqld.service
        rm -f /etc/systemd/system/mysqld.service 
    else
        service mysqld stop > /dev/null 2>&1
        chkconfig --del mysqld > /dev/null 2>&1
        rm -f /etc/init.d/mysqld 
    fi
    echo "done.............................."
    echo ""

    #remove data dir
    echo "remove data dir ................"
    rm -rf $yxdir/mysql
    echo "done.............................."
    echo ""

    #remove install dir
    echo "remove install dir ................"
    rm -rf $azdir/mysql
    echo "done.............................."
    echo ""

    #remove confiugre file
    echo "remove confiugre file ................"
    rm -rf /etc/my.cnf*
    rm -f /usr/bin/mysql*
    echo "done.............................."
    echo ""

    #remove mysql user
    echo "remove mysql user ................"
    userdel -r mysql > /dev/null 2>&1
    echo "done.............................."
    echo ""
    echo "mysql uninstall done............."

else
    clear
        echo "Are you want to fly to the sky ..."
    echo "Are you want to fly to the sky ..."
    echo "Are you want to fly to the sky ..."
    echo "Are you want to fly to the sky ..."
    echo "Are you want to fly to the sky ..."
fi

配置文件

[client]
#default login user
user=root
#default password
password=123

[mysqld]
#########genarel config##########
#the user runnging mysqld process
user=mysql
#base directory
basedir=/usr/local/mysql
#data directory
datadir=/var/mysql/data
#db server character set
character_set_server=utf8
#default engine
default_storage_engine=innodb
#server id
server_id=1
#service port
port=3306
#max connections
max_connections=1000
#skip resolve name
#skip_name_resolve=on
#pid file's directory
pid-file=/var/mysql/logs/db1.pid
#ignore table name case
lower_case_table_names=1
log_bin_trust_function_creators=1


######log part####
#swith on genarel log
#general_log=on
#name and directory of genarel log
general_log_file=/var/mysql/logs/db1.general
#swith on binnary log and set the file
log-bin=/var/mysql/logs/db1-bin
#index of bannary log
log_bin_index=/var/mysql/logs/db1.index
#swith bannary log
slow_query_log=on
#name and directory of slow query log
slow_query_log_file=/var/mysql/logs/db1.slow
#the unit of slow log ，second
long_query_time=1
#record query not use index into slow log
log_queries_not_using_indexes=1
#error log setting
log_error=/var/mysql/logs/db1.err
#auto clear binnary log , day
expire_logs_days=14
log_timestamps=SYSTEM

######innodb setting ######
#innodb memory size，byte
innodb_buffer_pool_size=256M
#innodb instance number
innodb_buffer_pool_instances=2
#dump cache from memory to disk when server shutdown
innodb_buffer_pool_dump_at_shutdown=on
#record page cache immediate
innodb_buffer_pool_dump_now=on
#import cache from disk to memory when server startup
innodb_buffer_pool_load_at_startup=on
#immediate cache buffer_pool
innodb_buffer_pool_load_now=on
#log buffer 8M to 32M
innodb_log_buffer_size=8M
#the size of binnary log
innodb_log_file_size=256M
#the action of write log to disk:0--flush per second;1--flush on tranction commit(default); 2--0 and 1
innodb_flush_log_at_trx_commit=1
#enable innodb monitor
innodb_monitor_enable=all

######MyIsam setting######
key_buffer_size=256M
read_buffer_size=256K
read_rnd_buffer_size=256K
sort_buffer_size=256K
join_buffer_size=256K


####replication setting#########
log-bin-trust-function-creators=1
#enable gtid mode
gtid_mode=ON
#enforce gtid consistency
enforce_gtid_consistency=ON
#master info storage in table
master_info_repository=TABLE
#relay log info storage in table
relay_log_info_repository=TABLE
#relay log file name
relay_log=/var/mysql/logs/db1-relay
#write binlog when slave apply relay-log
log_slave_updates=ON
#binlog format
binlog_format=ROW
#semi_sync
#rpl_semi_sync_master_enabled=on
#rpl_semi_sync_master_timeout=1000
#rpl_semi_sync_slave_enabled=on
#multi thread
slave_parallel_type=logical_clock
slave_parallel_workers=4

#####group replication####
#slave_preserve_commit_order=1
#binlog_checksum=NONE
#collection trasaction information
#transaction_write_set_extraction=XXHASH64
#name of relication group
#loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#do nothing when server start
#loose-group_replication_start_on_boot=off
#local host address and port
#loose-group_replication_local_address="10.2.5.15:33061"
#address and port of server in the replication group
#loose-group_replication_group_seeds="10.2.5.15:33061,10.2.5.16:33061,10.2.5.17:33061,10.2.5.18:33061"
#the init server set on,any time only one swtich on
#loose-group_replication_bootstrap_group=off

启动文件

[Unit]
Description=Mysql server 5.7.20
Documentation=http://doc.mysql.com

[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/usr/local/mysql/support-files/mysql.server reload

[Install]
WantedBy=multi-user.target

自动换安装流程

上传依赖包
rpm -ivh libaio* 
上述三个文件放到同一个目录
执行脚本

安装完成后根据配置信息更改(datadir，buffer_pool） #脚本默认datadir为/data