MySQL数据库的安装部署

1 单节点部署

1.1 tar.gz包安装方式

1）安装依赖

yum -y install gcc glibc libaio libstdc++ libstdc libncurses ld-linux

2）准备软件包，解压

tar -xvf Percona-Server-5.7.19-17-Linux.x86_64.ssl101.tar.gz -C /usr/local/

3）建立软链接

ln -s /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101 /usr/local/mysql

4）添加mysql 用户与用户组

groupadd mysql
useradd -r -g mysql mysql

5）修改目录属性权限

chown -R mysql:mysql  /usr/local/mysql

6）添加环境变量

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

7）创建data和binlog 目录，并修改权限

mkdir -p /mysql/{data,binlog}
chown -R mysql:mysql /mysql/

8）创建配置文件my.cnf

[mysql]
#CLIENT #
port                           = 3306
socket                         = /db2data/mysql/data/mysql.sock
[mysqld]
#thread_pool_stall_limit=100
# GENERAL #
user                                      = mysql
port                                      = 3306
default_storage_engine          = InnoDB
basedir                                   = /usr/local/mysql
socket                                    = /db2data/mysql/data/mysql.sock
pid_file                                  = /db2data/mysql/data/mysql.pid
character_set_server                      = utf8mb4
skip_name_resolve                         = ON              
lower_case_table_names                    = 1
back_log                                  = 50
read_buffer_size                          = 1M
read_rnd_buffer_size=17825792
sort_buffer_size                          = 16M
join_buffer_size                          = 16M
explicit_defaults_for_timestamp           = TRUE
server_id = 183750931
ignore_db_dir                             = lost+found
default_time_zone                         = '+8:00'
core_file
local_infile                              = 1
log_timestamps                            = system
thread_stack                              = 512K
#log_warnings                              = 2
performance_schema                        = 0      
# MyISAM #
key_buffer_size                           = 8M
myisam_recover_options                    = FORCE,BACKUP
keep_files_on_create                      = ON
# SAFETY #
max_allowed_packet                        = 64M
max_connect_errors                        = 300
sql_mode                                  = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate_is_now                            = 1
#innodb                                   = FORCE
innodb_strict_mode                        = 1
# DATA STORAGE #           
datadir                                   = /db2data/mysql/data/
# BINARY LOGGING #           
log_bin                                   = /db2data/mysql/binlog/mysql-bin
binlog_format                             = row
expire_logs_days                          = 14
sync_binlog                               = 1
binlog_cache_size                         = 1M
max_binlog_cache_size                     = 2G
# CACHES AND LIMITS #
tmp_table_size                            = 32M
max_heap_table_size                       = 32M
query_cache_type                          = 0
query_cache_size                          = 0
max_connections=1000
#max_connections=1000
thread_cache_size                         = 50
open_files_limit                          = 65535
table_definition_cache                    = 1024
table_open_cache                          = 4096
secure_file_priv=''
# INNODB #           
innodb_flush_method                       = O_DIRECT
innodb_log_files_in_group                 = 2
innodb_log_file_size                      = 1G
innodb_flush_log_at_trx_commit            = 1 
innodb_file_per_table                     = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances              = 8
transaction_isolation                     = READ-COMMITTED
innodb_data_file_path                     = ibdata1:512M;ibdata2:512M:autoextend
innodb_write_io_threads                   = 8
innodb_read_io_threads                    = 8
innodb_thread_concurrency                 = 32
innodb_max_dirty_pages_pct                = 75
innodb_lock_wait_timeout                  = 120
#innodb_rollback_on_timeout                = 1
innodb_io_capacity                        = 2000
innodb_io_capacity_max                    = 6000
innodb_lru_scan_depth                     = 2000
innodb_flush_neighbors                    = 0
innodb_print_all_deadlocks                = 1
innodb_purge_threads                      = 4
#innodb_undo_tablespaces                   = 3 
innodb_sort_buffer_size                   = 16M
innodb_online_alter_log_max_size          = 1G
innodb_disable_sort_file_cache            = ON
innodb_sync_array_size                    = 16
innodb_page_cleaners                      = 8
internal_tmp_disk_storage_engine          = INNODB
innodb_checksum_algorithm                 = crc32
#innodb_file_format                        = Barracuda
#innodb_file_format_max                    = Barracuda
innodb_stats_on_metadata                  = 0
innodb_sync_spin_loops                    = 100
innodb_spin_wait_delay                    = 30
# LOGGING #
log_error_verbosity                       = 2
log_error                                 = /db2data/mysql/data/mysql_error.log
log_queries_not_using_indexes             = 1
log_throttle_queries_not_using_indexes    = 20
min_examined_row_limit                    = 100
log_slow_admin_statements                 = 1
log_slow_slave_statements                 = 1
slow_query_log                            = 1
long_query_time                           = 0.5
slow_query_log_file                       = /db2data/mysql/data/mysql_slow.log
#relay_log                                = /db2data/mysql/log/relay_log/mysql-relay-bin
#general_log_file                         = /db2data/mysql/log/general.log
innodb_undo_log_truncate                  = 1
innodb_max_undo_log_size                  = 4G
# REPLICATION
#plugin-load                               = #"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
log_slave_updates=1
gtid_mode                                 = ON
#gtid_mode                                 = OFF
#rpl_semi_sync_master_enabled = 0
#rpl_semi_sync_master_timeout = 25920000000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_wait_for_slave_count = 1
#rpl_semi_sync_master_wait_point = AFTER_SYNC
#super_read_only=1
enforce_gtid_consistency                  = ON 
binlog_gtid_simple_recovery               = 1
master_info_repository                    = TABLE
relay_log_info_repository                 = TABLE
relay_log_recovery                        = ON
slave_net_timeout                         = 60
innodb_buffer_pool_dump_at_shutdown       = ON
innodb_buffer_pool_load_at_startup        = ON
innodb_buffer_pool_dump_pct               = 50
slave_exec_mode                           = STRICT
slave_rows_search_algorithms              = 'TABLE_SCAN,INDEX_SCAN'
slave_parallel_type                       = LOGICAL_CLOCK
slave_parallel_workers                    = 16
slave_preserve_commit_order               = 1
slave_transaction_retries                 = 128
#slave_compressed_protocol                 = 0
#THREAD_POOL
#thread_handling                           = pool-of-threads
#thread_pool_oversubscribe                 = 10
#thread_pool_size = 3

9）初始化

/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/mysql/data/ --user=mysql --initialize