MySQL 5.7.17 standalone server install and configuration
1 install mysql with YUM
2 edit /etc/my.cnf
[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
datadir=/var/lib/mysql #Modify it if necessary
innodb_buffer_pool_size = 2G #after VM provisioned, change this parameter according to the physical memory, usually 60-70% physical mem.
innodb_buffer_pool_instances = 2 #depend on buffer_pool_size, buffer_pool_size/2 when > 1G
innodb_log_file_size = 2000M #2000M is a good starting point.
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
max_connections=2000 #modify it according to the buffer_pool_size.
innodb_undo_log_truncate=1
innodb_undo_logs=128
innodb_undo_tablespaces=3
autocommit=0
port = 8080 #Modify it according to the port standard.
server-id=3 #Modify it to a unique value before a new instance started
expire-logs-days=7
log-bin = mysql-bin
log-bin-index=mysql-bin-index
binlog_format = row
explicit_defaults_for_timestamp=1
gtid_mode=on
log_slave_updates
enforce_gtid_consistency
lower_case_table_names=1
transaction_isolation = READ-COMMITTED
innodb_stats_persistent = 1
innodb_stats_persistent_sample_pages = 200
innodb_read_io_threads = 16
innodb_write_io_threads = 4
table_open_cache_instances = 16
metadata_locks_hash_instances = 256
innodb_page_size = 16384
#slow log enable
long_query_time=1 #the threshold of long running sql in seconds. Default is 10s
slow_query_log=1 #enalbe slow log
#For Slave
#replicate-rewrite-db=zdbtest->mass
#slave-parallel-type=LOGICAL_CLOCK
performance_schema_instrument=transaction=ON
performance_schema_instrument=wait/lock/metadata/sql/mdl=ON
performance_schema_instrument=memory/%=COUNTED
performance_schema_consumer_events_statements_history_long=ON
performance_schema_consumer_events_transactions_current=ON
performance_schema_consumer_events_transactions_history=ON
|
3 start mysql service
service mysqld start
systemctl enable mysqld
|
4 Remove default "root" account
#Get the initial password for root via
cat /var/log/mysqld.log | grep passw
mysql -u root -p
ALTER USER root@localhost identified by '<new password>';
#CREATE A NEW DBA account
CREATE USER 'system'@'%' identified by '<new password>';
GRANT ALL PRIVILEGES ON *.* TO 'system'@'%' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'system'@'%' WITH GRANT OPTION;
DROP USER 'root'@'localhost';
|
5 Transfer the DBA account to bastion host.
6 Create application users and bastion users
#please use the gensql.sh(attached at bottom) to generate the create database and user statements. You need to edit the dbs.txt to add what database you are going to create.
#after you ran the shell script, the SQL statements would be output on your screen and the user credentials will be in a file called xls(csv format). Please give the username/password to the corresponding team.
|