Linux下mysql单节点部署——免安装版本（直接压缩包解压）

原创

zhang好人 2022-10-11 14:22:54 博主文章分类：mysql ©著作权

文章标签 mysql linux 文章分类 运维

©著作权归作者所有：来自51CTO博客作者zhang好人的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、首先下载安装包

链接：https://pan.baidu.com/s/10gE20-v8l2O5vXfKdijNNQ 提取码：qqu9

2、运行下述命令

tar xf Percona-Server-5.7.30-33-Linux.x86_64.ssl101.tar.gz   ##解压压缩包
useradd -s /sbin/nologin mysql  ##创建mysql用户
mkdir -p /data/mysql3306/{logs,data,tmp}   ##新建目录
mkdir -p /etc/mysql   ##新建目录
ln -s /root/Percona-Server-5.7.30-33-Linux.x86_64.ssl101 /usr/local/mysql5.7  ##创建软连接
chown -R mysql.mysql /data/mysql3306  ##赋权

3、配置my.cnf

cat > /etc/mysql/my.cnf << EOF
[mysqld_safe]
log-error=/data/mysql3306/logs/mysqld.log
open-files-limit    =   65535

[mysqld] 
#MySQL Server layer basic setting 
server_id = 1
port = 3306 
socket = /data/mysql3306/tmp/mysql.sock 
slow_query_log_file = /data/mysql33006/logs/mysql.slow
character_set_server= utf8
group_concat_max_len = 102400
lock_wait_timeout = 10
wait_timeout    =       400
skip_name_resolve=1
#MySQL Server layer directory setting 
basedir = /usr/local/mysql5.7
datadir = /data/mysql3306/data 
tmpdir = /data/mysql3306/tmp
log-error=/data/mysql3306/logs/mysqld.log
pid-file = /data/mysql3306/tmp/mysqld3306.pid
gtid-mode = ON
enforce-gtid-consistency = ON

#MySQL Server layer connection setting 
max_connections = 2000 
max_connect_errors = 100000 
max_allowed_packet = 32M 
back_log = 100

#MySQL Server layer binlog setting 
log_bin_trust_function_creators = on 
expire_logs_days = 30 
binlog_cache_size = 20M 
log-bin = /data/mysql3306/logs/mysql-bin 
binlog_format = row

###replication###
#replicate-rewrite-db=cloudaccount->cloudaccount_dz2 
log_slave_updates = 1
relay_log_recovery = 1
relay_log_purge = 0
relay_log = /data/mysql3306/logs/mysql-relay-bin
relay_log_index = /data/mysql3306/logs/mysql-relay-bin.index
skip-slave-start
slave-net-timeout = 10
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_master_wait_no_slave = 1
loose_rpl_semi_sync_master_timeout  = 1000
#loose_rpl_semi_sync_slave_enabled = 1
#slave_skip_errors = 1396
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_pending_jobs_size_max=150M
#MySQL Server layer memory management setting 
tmp_table_size = 1024M 
max_heap_table_size = 1024M
read_buffer_size = 20M 
read_rnd_buffer_size = 50M 
sort_buffer_size = 50M 
join_buffer_size = 160M 
query_cache_size = 0 
thread_stack = 2M
thread_handling =  pool-of-threads
thread_pool_oversubscribe = 8
#MySQL Server layer transaction management setting 
transaction_isolation = READ-COMMITTED 
autocommit = ON 
log_timestamps=system

#MySQL Server layer log related setting 
log_error_verbosity = 3
slow_query_log = ON
long_query_time = 5
slow_query_log_file = /data/mysql3306/logs/mysql.slow
#log_output      =       'file,table'  
log_output      =       'file'  
#log_queries_not_using_indexes = 1 

#MySQL Server layer other behaviour setting 
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off' ##关闭5.7新特性子查询drived_merge 
optimizer_switch = 'derived_merge=off'  ##关闭5.7新特性子查询drived_merge
event_scheduler = ON 
lower_case_table_names = 1 
explicit_defaults_for_timestamp = ON 
default-storage-engine = INNODB 
##ft_min_word_len = 4 

# Statistic 
# userstat = ON 
# thread_statistics = ON 

# MyISAM Engine related setting 
key_buffer_size = 32M 
bulk_insert_buffer_size = 64M 
myisam_sort_buffer_size = 128M 
myisam_max_sort_file_size = 2048   
##mysql允许用于MyISAM表重建索引的最大存储空间（在磁盘），如果文件的大小超过辞职，索引会用keycache代替，这样会慢些；默认是2G，如果MyISAM表的索引文件大小超过此值并且磁盘空间充裕，增加此变量的值可以提升效率，必须在原有的分许上有磁盘空间

myisam_repair_threads = 1 
#myisam_recover 

#InnoDB Engine related setting 
#InnoDB memory management related setting 
innodb_buffer_pool_size = 12G 
innodb_max_dirty_pages_pct = 90 
innodb_sync_array_size = 16 
innodb_max_dirty_pages_pct = 90 
innodb_sync_array_size = 16 

#table open cache related 
table_open_cache = 6144
table_open_cache_instances = 16 
#innodb_additional_mem_pool_size = 16M ##This variable have been removed MySQL 5.7 

#innodb_numa_interleave = 1 ##Only work with Percona 5.6.27 and later 

##InnoDB engine I/O related setting 
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_flush_method = O_DIRECT 

#InnoDB engine File management related setting 
innodb_data_file_path = ibdata1:12M:autoextend #10M-->12M, 12M is default values, it is meaningless to set it to 10M 
innodb_file_per_table = 1 #this value have been the default value in MySQL 5.7 

#InnoDB engine undo log related setting 
innodb_undo_directory = /data/mysql3306/data
#innodb_undo_tablespaces = 4 
innodb_purge_batch_size = 5000 
innodb_purge_threads = 8 
innodb_page_cleaners = 8

#InnoDB engine redo log related setting 
innodb_flush_log_at_trx_commit = 1  
innodb_log_buffer_size = 20M 
innodb_log_file_size = 256M 
innodb_log_files_in_group = 3 
innodb_log_group_home_dir = /data/mysql3306/logs

#InnoDB engine lock and transaction management setting 
innodb_lock_wait_timeout = 60

#InnoDB engine other behaviour setting 
innodb_large_prefix = ON ##this value have been the default value in MySQL 5.7 
innodb_strict_mode = ON ##this value have been the default value in MySQL 5.7 
innodb_checksum_algorithm = crc32 ##this value have been the default value in MySQL 5.7 


[mysqldump] 
quick 
max_allowed_packet = 160M 

[mysql] 
no-auto-rehash 
# Only allow UPDATEs and DELETEs that use keys. 
#safe-updates 

[myisamchk] 
key_buffer_size = 512M 
sort_buffer_size = 512M 
read_buffer = 8M 
write_buffer = 8M 

[mysqlhotcopy] 
interactive-timeout 

EOF

4、启动mysql

/usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql
nohup /usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf &> /data/mysql3306/logs/runlog.log &

5、修改mysql的root用户密码

defaltpass="$(awk '/temporary password is/{print $NF}' /data/mysql3306/logs/mysqld.log)"

HISTCONTROL=ignorespace
  /usr/local/mysql5.7/bin/mysql --connect-expired-password -u root -p"$defaltpass" -S /data/mysql3306/tmp/mysql.sock  -e"set password=password('Zhanghaha123');"
  /usr/local/mysql5.7/bin/mysql -u root -p'Nuonuo888' -S /data/mysql3306/tmp/mysql.sock  -e"flush privileges;"

6、新建用户

/usr/local/mysql5.7/bin/mysql -u root -p'Zhanghaha123' -S /data/mysql3306/tmp/mysql.sock  -e"create  user test@'%' identified by 'test@2022';"

7、创建数据库+赋权

/usr/local/mysql5.7/bin/mysql -u root -p'Zhanghaha123' -S /data/mysql3306/tmp/mysql.sock  -e"create database test;"
    /usr/local/mysql5.7/bin/mysql -u root -p'Zhanghaha123' -S /data/mysql3306/tmp/mysql.sock  -e"grant select ,update,delete,insert on  .* to test@'%';"

8、配置开机自启动

chmod +x /etc/rc.d/rc.local
grep "mysqld_safe" /etc/rc.d/rc.local > /dev/null
echo "nohup /usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf &> /data/mysql3306/logs/runlog.log &" >> /etc/rc.d/rc.local