mysql 5.7手动安装部署
2021-11-30 本文已影响0人
su酥饼
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
