部署percona_mysql5.7

二进制部署Percona-Server-5.7.38

一、官网下载percona-Server-5.7.38

https://www.percona.com/

由于网络原因无法下载，私信留言

二、步骤

1.创建目录，准备好centos7环境

创建目录，卸载本机原有的mysql mariadb环境：

mkdir -p /app/percona57
mkdir -p /app/percona57_data
mkdir -p /app/percona57_log

2.编辑配置文件

[client]
port=3306
socket	= /app/percona57/percona_3306.sock

[mysql]
no-beep
prompt="\u@percona3306 \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8

[mysqld]
########basic settings########
server-id=3306
port=3306
user=root
#此处使用mysql用户启动则改为mysql
bind_address= 192.168.241.104
basedir=/app/percona57/percona/bin
datadir=/app/percona57_data
socket	= /app/percona57/percona_3306.sock
pid-file = /app/percona57/percona_3306.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general_log = 0
general_log_file= /app/percona57_log/log/itpuxdb-general.log
slow_query_log = ON
slow_query_log_file= /app/percona57_log/log/itpuxdb-query.log
long_query_time=10
log-error= /app/percona57_log/log/itpuxdb-error.log

log-bin = /app/percona57_log/log/binlog/itpuxdb-bin.log
log-bin-index = /app/percona57_log/log/binlog/itpuxdb-bin.index
binlog_format = row 
max_binlog_cache_size = 1M
max_binlog_size = 200M

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
#log_bin = "/log/bin_log/binlog"

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = /data/log/bin_log/binlog
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row 
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors



########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 120M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2 
innodb_log_buffer_size = 16M

innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
#生产环境建议2个1G，最后再弄个自动扩展5G，根据自己的事物对定。

[mysqld_safe]
log-error=/app/percona57_log/log/itpuxdb-error.log
pid-file=/app/percona57/percona_3306.pid

3.初始化percona5.7

/app/percona57/percona/bin/mysqld --defaults-file=/app/percona57/percona/etc/my.cnf --initialize-insecure --user=root

4.启动数据库

/app/percona57/percona/bin/mysqld_safe --defaults-file=/app/percona57/percona/etc/my.cnf &

总结

提示：观察mysql日志：

可在mysqld底下增加参数，跳过密码认证
skip-grant-tables

创建root远程用户
create user ‘root’@‘%’ IDENTIFIED by ‘root’;