本文介绍mysql,二进制安装操作
1、创建用户、组、目录
mkdir /u01/mysql_db -p
groupadd mysql
useradd -g mysql -r -s /sbin/nologin -M -d /usr/local/mysql mysql
2.配置环境变量
vi /etc/profile.d/mysql.sh
# set mysql
export MYSQL_BASE=/usr/local/mysql
export PATH=$MYSQL_BASE/bin:$PATH
source /etc/profile.d/mysql.sh
3、my.cnf文件准备/etc/my.cnf
以下是MySQL最安全参数设置,牺牲部分性能,适合强事务、重要数据存储
[client]
port = 3306
loose_default-character-set = utf8mb4
socket = /u01/mysql_db/mysql3306.sock
[mysql]
# 显示 mysql 当前登录的用户 主机 数据库名 时间
prompt = "\\u@\\h : \\d \\r:\\m:\\s> "
default-character-set = utf8mb4
no-auto-rehash
[mysqld_safe]
user = mysql
open-files-limit = 65535
[mysqld]
port = 3306
#bind-address = 10.10.10.10
server-id = 10
socket = /u01/mysql_db/mysql3306.sock
pid-file = /u01/mysql_db/mysql3306.pid
# mysql 安装目录
basedir = /usr/local/mysql
# mysql 数据文件目录
datadir = /u01/mysql_db
# 跳过域名解析
skip-name-resolve
skip-external-locking
# 开启慢日志查询 时间1秒 文件存储 开启错误日志
slow_query_log = ON
long_query_time = 1
slow_query_log_file = mysql-slow.log
log_error = mysql-err.log
log_slow_admin_statements
log_slow_slave_statements
# binglog日志 模式为ROW(强烈推荐) 大小为512M(建议不超过2G、业务繁忙可以增大)
# sync-binlog 此参数对mysql 性能影响非常大 追求 数据最高安全为1 追求性能和数据安全可以设置为50或100 不建议太高设置
# expire_logs_days 强烈建议开启,默认是天 不定时清理 binlog日志
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 512M
sync-binlog = 1
max_binlog_cache_size = 256M
expire_logs_days = 7 # 5.7
# 以下是关于mysql 超时的一些参数,使用以下参数即可
wait_timeout = 172800
connect_timeout = 10
max_connections = 3000
max_connect_errors = 99999
interactive_timeout = 172800
# max_allowed_packet 主备模式下该参数可以调大,甚至1024M
# log_timestamps和default-time-zone 尽量设置时区,注意不设置会根据操作系统的时区
# lower_case_table_names 区分大小写 1 开启不区分 默认是0
# table_open_cache_instances 不建议太大 2 或 4 即可
back_log = 1024
event_scheduler = 0
tmp_table_size = 8M
open_files_limit = 65535
ft_min_word_len = 1
table_open_cache = 4096
max_allowed_packet = 64M
thread_cache_size = 512
log_timestamps = SYSTEM
default-time-zone = '+8:00'
table_definition_cache = 4096
group_concat_max_len = 102400
lower_case_table_names = 1
max_heap_table_size = 8M
table_open_cache_instances = 2
max_length_for_sort_data = 16k
slave_pending_jobs_size_max = 128M
# query_cache_size 强烈建议关闭该参数
# 其他参数不建议过大以下参数即可
query_cache_size = 0 # 5.7
net_buffer_length = 8k
read_buffer_size = 1M
sort_buffer_size = 2M
join_buffer_size = 4M
binlog_cache_size = 4M
slave_parallel_type = LOGICAL_CLOCK # 5.7
read_rnd_buffer_size = 2M
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN' # 5.7
# 默认存储引擎 mysql 5.5以后默认即是 innodb 显示设置一下
default-storage-engine = Innodb
# innodb_fast_shutdown 建议开启 可以加快shutdown速度 需要关闭mysql时 有时会关闭很久
# innodb_log_file_size 根据系统繁忙程度 越繁忙参数越大 512M 1024M 2048M 即可,不宜太大
# innodb_log_files_in_group redo组 默认是2组 可以设置成3组
# innodb_flush_log_at_trx_commit 此参数强烈影响mysql性能 0 1 2
# 1 性能最差 数据安全最高 每事务 flush+fsync 0 交给os默认1s刷新一次 2 写入os缓存(flush) os定时fsync
# 一般配合sync-binlog使用 常说的双1 或者 双标 或者mysql真正的事务支持参数
innodb_fast_shutdown = 1
innodb_log_file_size = 1024M
innodb_log_buffer_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
# innodb_file_per_table 强烈建议开启 每一个表单独产生一个表空间(idb)文件 数据不再存储在系统表空间 优势明显
# innodb_page_cleaners CPU高可以开到 8 默认4 即可
# innodb_purge_threads CPU高可以开到 8 默认4 即可
innodb_file_per_table = ON
innodb_table_locks = 0
innodb_page_cleaners = 4 # 5.7
innodb_purge_threads = 4
innodb_max_purge_lag = 65536
innodb_purge_batch_size = 4
#innodb_undo_tablespaces = 4 # 5.7
innodb_undo_log_truncate = 1
# innodb_temp_data_file_path 强烈建议控制临时表空间的增长 虽然有时候不起作用
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
# transaction_isolation 事务级别 建议使用rc 默认是rr
# innodb_thread_concurrency cpu高可以开到 128
# innodb_rollback_on_timeout 建议开启 事务的回滚
transaction_isolation = READ-COMMITTED
innodb_lock_wait_timeout = 120
innodb_thread_concurrency = 64 # 0-128
innodb_rollback_on_timeout = 1
innodb_support_xa = on # 5.7
# innodb_strict_mode 建议开启严格模式 此时不再warnning提示,直接报错
# innodb_flush_method flush模式,直接跳过操作系统 写入mysql innodb buffer
# innodb_autoinc_lock_mode 控制自增锁 建议2 1为严格模式 2可能会造成自增不连续
# innodb_checksum_algorithm 校验算法 默认是 crc32
innodb_strict_mode = 1
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2 # strict auto 1
innodb_checksum_algorithm = crc32
# IO相关参数
# innodb_io_capacity 可以根据硬盘的iops来设置 默认200 SSD硬盘可以设置2000以上 HDD建议设置2000以下
# innodb_read_io_threads 根据CPU来设置 默认是4
# innodb_write_io_threads 根据CPU来设置 默认是4
# innodb_use_native_aio 开启异步
innodb_io_capacity = 8000
innodb_read_io_threads = 16
innodb_write_io_threads = 8
innodb_use_native_aio = 1
# innodb_sort_buffer_size 不宜过高,2M 4M 8M 即可
# innodb_buffer_pool_size mysql的核心内存参数 os物理内存的50%-60% 甚至更高
# innodb_max_dirty_pages_pct 脏页的百分比 75 或 80 不建议过高或过低 根据yong来调优
# innodb_buffer_pool_instances 不宜过高 2 或 4 不宜超过16
# innodb_buffer_pool_load_at_startup innodb_buffer_pool_dump_at_shutdown 和热数据有关 建议开启
innodb_sort_buffer_size = 2M
innodb_buffer_pool_size = 4096M
innodb_log_buffer_size = 16M
innodb_flush_neighbors = 0
innodb_lru_scan_depth = 4096 # innodb_io_capacity / innodb_buffer_pool_instances
innodb_old_blocks_time = 1000
innodb_change_buffering = all
innodb_adaptive_flushing = on
innodb_max_dirty_pages_pct = 75
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = on
innodb_buffer_pool_dump_at_shutdown = on
# 字符集 强烈建议指定 Hzero 使用utf8mb4和utf8mb4_bin 搭配
init_connect = 'SET NAMES utf8mb4'
collation_server = utf8mb4_bin
character_set_server = utf8mb4
character_set_client_handshake = FALSE
# sql mode 模式
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# 强烈建议开启 和 字段类型timestamp有关 为off update 会更新时间 mysql 8 默认开启 5.7以下默认关闭
explicit_defaults_for_timestamp = on # 5.7
# mysql 系统表默认使用myisam 所以需要为其分配一些内存
# key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads
key_buffer_size = 8M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 8M
myisam_repair_threads = 1
myisam_recover_options = force
myisam_max_sort_file_size = 10G
[mysqldump]
quick
max_allowed_packet = 2G
default-character-set = utf8mb4
[myisamchk]
ft_min_word_len = 1
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
4、安装软件
tar zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql
chown -R mysql:mysql /u01/mysql_db /usr/local/mysql/
5、初始化数据
/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/u01/mysql_db --initialize-insecure --user=mysql
6、设置自启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
chkconfig --level 2345 mysql on
service mysql start
7、修改root密码和创建用户
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test2021';
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'test2021';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
create user 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'test21';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;
(笔者原创文章,转载请注明出处:https://blog.csdn.net/LFCuiYs)谢谢!