1
2

$ groupadd mysql
$ useradd mysql -g mysql

1
2
3
4

$ mkdir /service/data
$ mkdir /service/binlog
$ chown -R mysql:mysql /service/data
$ chown -R mysql:mysql /service/binlog

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

[client]
port            = 3306
socket          = /service/data/mysqld.sock

[mysqld]
##BASIC CONFIGURATION
server-id = 5220
port= 33006
socket = /service/data/mysqld.sock
basedir = /usr/local/mysql
datadir = /service/data
skip_name_resolve = 1
default-storage-engine = INNODB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
back_log = 500
wait_timeout = 600
interactive_timeout = 600
connect_timeout=120
plugin_load = validate_password.so
symbolic-links = 0
lower_case_table_names = 1
local_infile=OFF
skip_networking=OFF
skip_show_database=OFF
max_allowed_packet = 512M
event_scheduler=OFF
explicit_defaults_for_timestamp = 1
group_concat_max_len = 102400
log_error_verbosity=2

##BINLOG CONFIGURATION
log-bin = /service/binlog/mysql-bin
log_bin_trust_function_creators = ON
binlog_format = row
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 512M
expire_logs_days = 20

##LOG CONFIGURATION
slow_query_log = 1
slow_query_log_file = /service/data/mysqlslow.log
long_query_time = 1
log_slow_admin_statements = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log-error = /service/data/mysqld.log

##SESSION CONFIGURATION
max_connections = 2000
max_user_connections = 2000
max_connect_errors = 100000
table_open_cache = 2048
table_definition_cache = 4096
table_open_cache_instances = 32
read_buffer_size = 16M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
tmp_table_size = 128M
thread_cache_size = 64
thread_stack = 256K
query_cache_type = 0
query_cache_size = 0
open_files_limit = 65535
max_prepared_stmt_count=1048576

##GTID CONFIGURATION
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
slave_allow_batching = 1
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
slave-parallel-workers=4
relay_log_purge = 1
relay_log_recovery = 1

##INNODB CONFIGURATION
innodb_buffer_pool_size = 140G  #(65%-75% Physical memory)
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 100
innodb_thread_concurrency = 1000
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 4
innodb_write_io_threads = 16
innodb_io_capacity = 4000
innodb_io_capacity_max = 10000
innodb_file_per_table = 1
innodb_strict_mode = 1
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_purge_batch_size = 32
innodb_change_buffering = all
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 0
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 67108864
innodb_stats_persistent_sample_pages = 64
innodb_online_alter_log_max_size=1G
transaction_isolation = READ-COMMITTED
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysql]
prompt = "\u@\d \R:\m:\s "
default-character-set = utf8mb4
no-auto-rehash

1
2
3
4
5

$ cat /etc/security/limits.conf
mysql soft nproc 10240
mysql hard nproc 10240
mysql soft nofile 65535
mysql hard nofile 65535

1
2
3

[root@t-luhxdb01-p-szzb ~]# vi /etc/selinux/config
SELINUX=disabled
[root@t-luhxdb01-p-szzb ~]# setenforce 0

1
2
3
4

$ echo never >  /sys/kernel/mm/transparent_hugepage/enabled
$ echo never > /sys/kernel/mm/transparent_hugepage/defrag
$ echo "echo never >  /sys/kernel/mm/transparent_hugepage/enabled" >> /etc/rc.local
$ echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag" >> /etc/rc.local

1
2
3

$ tar -xvf  mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
$ mv mysql-5.7.17-linux-glibc2.5-x86_64   /usr/local/mysql
$ chown -R root.mysql /usr/local/mysql

1
2

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

1

$ mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/data

1
2

$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
$ /etc/init.d/mysqld start

1
2
3

$ mysql -uroot -p
root@(none) 15:16> set password=password('NewPassword');
root@(none) 15:16> flush privileges;

1
2
3
4
5

[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db1/data -p
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db1/binlog -p
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db2/binlog -p
[root@t-luhx02-v-szzb mysql]# mkdir /service/mysql/db2/data -p
[root@t-luhx02-v-szzb mysql]# chown -R mysql.mysql /service/mysql/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /service/mysql/mysqld_multi.log
user=multi_admin
pass=ceshi@123

[mysqld33006]
server_id=111
port=33006
datadir=/service/mysql/db1/data
log_error=/servie/mysql/db1/data/mysqld.log
log_bin=/service/mysql/db1/binlog/mysql-bin
pid-file = /service/mysql/db1/data/mysqld.pid
socket = /service/mysql/db1/data/mysql.sock

[mysqld33007]
server_id=222
port=33007
datadir=/service/mysql/db2/data
log_error=/servie/mysql/db2/data/mysqld.log
log_bin=/service/mysql/db2/binlog/mysql-bin
pid-file = /service/mysql/db2/data/mysqld.pid
socket = /service/mysql/db2/data/mysql.sock

1
2

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/mysql/db1/data
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/service/mysql/db2/data

1
2
3
4

[root@t-luhx02-v-szzb mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld33006 is not running
MySQL server from group: mysqld33007 is not running

1
2

[root@t-luhx02-v-szzb mysql]# mysqld_multi start 33006
[root@t-luhx02-v-szzb mysql]# mysqld_multi start 33007

1
2
3

> create user 'multi_admin'@'localhost' identified by 'ceshi@123';
> grant shutdown on *.* to 'multi_admin'@'localhost';
> flush privileges;

1
2

[root@t-luhx02-v-szzb mysql]# mysqld_multi stop 33006
[root@t-luhx02-v-szzb mysql]# mysqld_multi stop 33007