mysql自动安装脚本（快速部署mysql）_脚本安装部署mysql数据

mysql_install - 适用于生产环境单实例快速部署

MySQL8.0 自动安装脚本

mysql8_install.sh（执行前修改一下脚本里的配置参数，改成你自己的）（博客末尾）

my_test.cnf（博客末尾）（这个是模板文件，基本上不用改，mysql8_install.sh脚本执行的时候会自动替换里面的port，server_id，innodb_buffer_pool_size等）

mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

shell> wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

三个文件放在同一个目录下，例如/root/soft/

---

Centos8 注意事项

shell> yum install jemalloc -y

内存管理器jemalloc库文件名字已经变更，需要建立一个软连接

shell> ln -s /usr/lib64/libjemalloc.so.2 /usr/lib64/libjemalloc.so

---

1）安装并启动mysql进程（主和从库都执行）

shell> /bin/bash mysql8_install.sh

注：my.cnf配置文件默认在/etc/目录下，文件名是以你的数据库名命名，例my_test.cnf，mysql.sock在/tmp目录下。

数据存放在/data/mysql/目录下。
1

2）配置主从复制（从库执行）

shell> /bin/bash mysql8_install.sh repl

3）配置组复制（先在Primary节点上执行，再到Secondary节点上执行）

注：先把3个节点MySQL实例启动后再开始搭建mgr，同时修改脚本里的ip地址和端口和hosts对应的主机名和地址

shell> /bin/bash mysql8_install.sh mgr

注：配置成功后，会在data数据目录下生成mysqld-auto.cnf配置文件。
脚本：
mysql8_install.sh

#!/bin/bash
# 适用于生产环境单实例快速部署，操作系统CentOS 7。

echo "正在安装MySQL软件......."

useradd mysql
useradd nagios
useradd zabbix

sleep 2

######配置参数######
mysql8_version=mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
mysql8_version_dir=mysql-8.0.32-linux-glibc2.12-x86_64

################################主从复制配置################################
######同步复制用户######
repl_user=repl
repl_passwd=sysrepl
#######################

######root密码######
root_passwd=123456
#######################

######DBA管理用户######
dba_user=admin
dba_passwd=hechunyang
#######################

######8.0克隆用户######
clone_user=clone_user
clone_passwd=123456
#######################

################################mgr组复制配置################################

######修改hosts文件######
cat << EOF >> /etc/hosts

192.168.71.11   mgr1
192.168.71.12   mgr2
192.168.71.13   mgr3

EOF
#######################

mysql_port=3306                 # 数据库实例对外请求端口
primary_ip=192.168.71.11        # 每个mgr节点IP
secondary1_ip=192.168.71.12     # 每个mgr节点IP
secondary2_ip=192.168.71.13     # 每个mgr节点IP

primary_port=33061        # mgr primary节点通讯端口
secondary1_port=33062     # mgr secondary1节点通讯端口
secondary2_port=33063     # mgr secondary2节点通讯端口

local_ip=192.168.71.11    # 每个mgr节点IP不相同
local_port=33061          # 每个mgr节点通讯端口port不相同

################################以下代码不用修改################################
if [ "$1" = "repl" ]
then
while true
do
	read -t 30 -p "输入你的主库IP:  " master_ip
	read -t 30 -p "输入你的主库端口号:  " master_port
	if [[ -z $master_ip || -z $master_port ]]
	then
		continue
	else
		echo ""
		echo "主库IP是： $master_ip"
		echo "主库端口号是： $master_port"
		break 
	fi
done

/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$master_port" --connect-expired-password -e "CHANGE MASTER TO MASTER_HOST='$master_ip',MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd',MASTER_PORT=$master_port,MASTER_AUTO_POSITION = 1,MASTER_CONNECT_RETRY=10; START SLAVE;"

	echo "MySQL主从复制同步已经初始化完毕。"
	exit 0
fi

################################################
if [ "$1" = "mgr" ]
then

while true
do
	read -t 30 -p "是Primary吗？是请输入yes，否输入no:  " is_primary
	if [[ -z $is_primary ]]
	then
		continue
	else
		if [ $is_primary == "yes" ] || [ $is_primary == "no" ]
		then
			break 
		else
			 echo "你输入一个错误的字符$is_primary，请重新输入..."
			 continue
		fi
	fi
done

if [ $is_primary == "yes" ]
then
	/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME  'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address =  '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}';SET GLOBAL group_replication_bootstrap_group=ON; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;SET GLOBAL group_replication_bootstrap_group=OFF;"

else
	/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME  'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address =  '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}'; SET GLOBAL group_replication_bootstrap_group=OFF; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;"

fi
	
echo "MySQL Mgr组复制已经初始化完毕。"
exit 0

fi

################################################

ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
	echo "MySQL进程已经启动，无需二次安装。"
	exit 0
fi

if [ ! -d /usr/local/${mysql8_version_dir} ]
then
        yum install xz numactl* jemalloc* libaio* net-tools nload iftop sysstat wget vim ntpdate lrzsz -y
	tar -Jxvf ${mysql8_version} -C /usr/local/
	ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
	chown -R mysql.mysql /usr/local/mysql/
	chown -R mysql.mysql /usr/local/mysql
else
	ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
	chown -R mysql.mysql /usr/local/mysql/
	chown -R mysql.mysql /usr/local/mysql
fi 

while true
do
	read -t 30 -p "输入你的数据库名:  " dbname
	read -t 30 -p "输入你的数据库端口号:  " dbport
	read -t 30 -p "输入MySQL serverId:  " serverId
	read -t 30 -p "输入innodb_buffer_pool_size大小，单位G:  " innodb_bp_size
	if [[ -z $dbname || -z $dbport || -z $serverId || -z $innodb_bp_size ]]
	then
		continue
	else
		echo "数据库名字是： $dbname"
		echo "数据库端口是： $dbport"
		echo "MySQL serverId： $serverId"
		echo "BP大小是： $innodb_bp_size GB"
		break 
	fi
done

sed "s/test/$dbname/g;s/3306/$dbport/;s/413306/$serverId/;/innodb_buffer_pool_size/s/1/$innodb_bp_size/" my_test.cnf > /etc/my_$dbname.cnf

DATA_DIR=/data/mysql/$dbname
[ ! -d $DATA_DIR ] && mkdir -p $DATA_DIR/{data,binlog,relaylog,tmp,slowlog,log}; touch $DATA_DIR/log/error.log; chown -R mysql.mysql /data/mysql/


if [ `ls -A $DATA_DIR/data/ | wc -w` -eq 0 ]
then
	cd /usr/local/mysql
	echo ""
	echo "初始化MySQL数据目录......"
	echo ""
	bin/mysqld --defaults-file=/etc/my_$dbname.cnf --initialize --lower-case-table-names=1 --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/$dbname/data
	sleep 2
	bin/mysqld_safe --defaults-file=/etc/my_$dbname.cnf --user=mysql &
fi

while true
do
	 netstat -ntlp | grep $dbport
	 if [ $? -eq 1 ]
	 then
        echo "MySQL启动中，稍等......"
        sleep 5
        continue
	 else
        if [ ! -e "/tmp/mysql_${dbname}.sock" ];then
            echo "MySQL启动中，稍等......"
            sleep 5
            continue
        fi
		break
	 fi
done

ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
        echo "MySQL安装完毕。"
else
	echo "MySQL安装失败。"
        exit 1
fi

###更改root账号随机密码
random_passwd=`grep 'temporary password' $DATA_DIR/log/error.log | awk -F 'root@localhost: ' '{print $2}'`

/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$random_passwd" --connect-expired-password -e "alter user root@'localhost' identified by '$root_passwd';" 
if [ $? -eq 0 ]
then
    echo "root账号随机密码更改完毕。"
else
    echo "密码更改失败，再次重试更改。"
    sleep 5
    /usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$random_passwd" --connect-expired-password -e "alter user root@'localhost'identified by '$root_passwd';"
    if [ $? -eq 0 ]
    then
       echo "root账号随机密码更改完毕。"
    else
       echo "密码更改失败"
       exit 1
    fi
fi

###创建同步账号和管理员账号
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$root_passwd" -e "set sql_log_bin=0;create user '$repl_user'@'%' IDENTIFIED BY '$repl_passwd'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$repl_user'@'%'; create user '$dba_user'@'%' IDENTIFIED BY '$dba_passwd'; GRANT ALL on *.* to '$dba_user'@'%' WITH GRANT OPTION;"

sed -i -r "s/(PATH=)/\1\/usr\/local\/mysql\/bin:/" /root/.bash_profile
source /root/.bash_profile

echo "MySQL账号初始化完毕。"

###安装clone插件
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$root_passwd" -e "set sql_log_bin=0;INSTALL PLUGIN CLONE SONAME 'mysql_clone.so'; CREATE USER '$clone_user'@'%' IDENTIFIED BY '$clone_passwd';GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO '$clone_user'@'%';"

echo ""
echo "clone克隆插件安装完毕。"

### 最后一步清空binlog文件，恢复起始文件mysql-bin.000001和Position位置号153 
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock  -p"$root_passwd" -e "reset master;"
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238

my_test.cnf

[client]
port		=  3306
socket   =  /tmp/mysql_test.sock
#default-character-set = utf8 
prompt=(\\u@\\h) [\\d]>\\_

# The MySQL server
#########Basic##################
[mysqld]
##bind_address = 127.0.0.1
server-id	=  413306
port	        =  3306
user            =  mysql
basedir		=  /usr/local/mysql
datadir		=  /data/mysql/test/data
tmpdir		=  /data/mysql/test/tmp
secure_file_priv =  /data/mysql/test/tmp
socket		=  /tmp/mysql_test.sock
skip-external-locking
skip-name-resolve
skip-ssl
##init_connect = 'SET global sql_mode = ""'
sql_mode = ''
default_authentication_plugin = mysql_native_password
activate_all_roles_on_login = 1

### Percona ###
#extra_port = 13306
#extra_max_connections = 100

###skip-networking
default-storage-engine = INNODB
character-set-server = utf8
wait_timeout  =  3600
connect_timeout  =  20
interactive_timeout  =  3600
back_log  =  500
#event_scheduler  =  ON
open_files_limit = 65535
#thread_handling  =  pool-of-threads

lower_case_table_names  =  1

###### binlog ######
log-bin  =  /data/mysql/test/binlog/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_row_metadata = FULL
binlog_checksum = NONE
binlog_transaction_dependency_tracking = WRITESET
sync_binlog      =   1
gtid_mode = on
enforce_gtid_consistency = on
log_slave_updates   =  1
master_info_repository = TABLE
relay_log_info_repository = TABLE
max_binlog_size  =  128M
binlog_cache_size  =  1M
expire-logs-days  =  7

#########replication#############
relay-log               =  /data/mysql/test/relaylog/relay-log
slave-net-timeout                   =  10
#rpl_semi_sync_master_enabled        =  1
#rpl_semi_sync_master_wait_no_slave   =  1
#rpl_semi_sync_master_timeout        =  1000
#rpl_semi_sync_slave_enabled         =  1
#skip-slave-start
log_slave_updates                  =  1
relay_log_recovery                 =  1
#slave_skip_errors = 1062
read_only                          =  0

###### Mgr config ######
loose-transaction_write_set_extraction = XXHASH64
loose-group_replication_start_on_boot = ON ###是否随mysql启动Group Replication
loose-group_replication_bootstrap_group = OFF  ###是否是Group Replication的引导节点，初次搭建集群的时候需要有一个节点设置为ON来启动Group Replication
					       ###参数设置为ON，是为了标示以后加入集群的服务器都已这台服务器为基准。以后加入的就不需要进行设置
					    
loose-group_replication_group_name = aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa                             
#loose-group_replication_local_address = 192.168.148.41:33061					       
#loose-group_replication_group_seeds = 192.168.148.41:33061,192.168.148.42:33072,192.168.148.39:33083					       
loose-group_replication_single_primary_mode = ON	
loose-group_replication_exit_state_action = OFFLINE_MODE
loose-group_replication_transaction_size_limit = 150000000 ###默认143M事务大小，最大值2147483647（大约2G），当此系统变量设置为0时，该组接受的事务大小没有限制。
loose-group_replication_enforce_update_everywhere_checks = OFF ###在单主模式下设置为OFF，多主模式下设置为ON。
								
###offline_mode = ON

###MHA ############
relay_log_purge            =  1
###################

###parallel replication####
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 24
slave_preserve_commit_order = ON
##################################

#########slow log#############
slow_query_log  =  1
slow_query_log_file  =  /data/mysql/test/log/mysql-slow.log
#log-slow-verbosity = query_plan,explain
long_query_time  =  5

#########error log#############
log-error   =  /data/mysql/test/log/error.log
log_timestamps = system
 
#######per_thread_buffers############
max_connections=4100
max_user_connections=4000
max_connect_errors=100000000
max_allowed_packet = 256M
table_open_cache = 6144
table_definition_cache = 4096
sort_buffer_size = 128K
read_buffer_size = 128K
read_rnd_buffer_size = 128k
join_buffer_size = 128K
tmp_table_size = 64M
max_heap_table_size = 64M
bulk_insert_buffer_size = 32M
thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 256K

##MyISAM## 
key_buffer_size = 64M
myisam_sort_buffer_size = 8M
concurrent_insert=2
low_priority_updates=1
myisam_recover_options=BACKUP,FORCE

######### InnoDB #############
sql_generate_invisible_primary_key = 1
innodb_adaptive_hash_index = 0
innodb_autoinc_lock_mode = 2
innodb_numa_interleave = 1
innodb_sort_buffer_size = 32M
innodb_rollback_on_timeout = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances	= 16
innodb_lru_scan_depth = 2048
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 95
innodb_ddl_threads = 16
innodb_ddl_buffer_size = 1G
innodb_online_alter_log_max_size = 1G
innodb_parallel_read_threads = 32
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
#innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_use_native_aio = 1
innodb_io_capacity = 800
innodb_purge_threads = 16
innodb_purge_batch_size = 32
innodb_old_blocks_time = 1000
innodb_change_buffering = all
transaction_isolation = READ-COMMITTED
innodb_stats_on_metadata = 0
performance-schema = 1
performance-schema-instrument = wait/lock/metadata/sql/mdl=ON

####### NEW ########
##innodb_kill_idle_transaction = 5

###### SSD #########
#innodb_flush_neighbors = 0
#innodb_log_block_size = 4096
####################

[mysqldump]
quick
max_allowed_packet = 128M

[mysql]
no-auto-rehash
#prompt=(\\u@\\h) [\\d]>\\_
prompt=(\\u@\\h) [\\d]>\\_

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 65535
#flush_caches = 1
#numa_interleave = 1
malloc-lib = /usr/lib64/libjemalloc.so
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209