香港云服务器的MySQL数据库的安装部署

已关闭留言

香港云服务器的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)创建databinlog 目录,并修改权限

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 —