在生产环境中需要安装多台mysql实例，单台操作太麻烦所以写一个脚本实现自动化。
脚本实现功能：
- 安装mysql实例并进行初始化
- 自动编写配置文件
- 安装xtrabackup
- 安装qpress
- 修改备份脚本
- 添加定时任务
- 修改mysql实例root密码，创建数据库名，并新建多类型账户并授权
- 将mysql用户及密码输出到同目录文件password.txt
使用方法：
将安装包及脚本放在同级目录，执行脚本即可。
目录类型如下：1
2
3
4
5
6
7
8
9
10
11
12# pwd
/data/backup
# ll -a
total 913696
drwxr-xr-x 7 root root 4096 Apr 29 06:12 .
drwxr-xr-x 7 root root 72 Apr 29 05:49 ..
-rwxr-xr-x 1 root root 7367 Apr 29 05:58 deploy_mysql.sh
-rw-r--r-- 1 root root 644862820 Apr 28 02:05 mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 285 Apr 29 05:49 password.txt
-rw-r--r-- 1 root root 90282874 Apr 28 02:02 percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
-rw-r--r-- 1 root root 75684 Apr 28 02:01 qpress
-rw-r--r-- 1 root root 3706 Apr 28 02:01 scripts.tar.gz
脚本内容为：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# cat deploy_mysql.sh
read -p "请输入新建数据库名:" dbname
home=$(cd `dirname $0`; pwd)
function generate_random()
{
openssl rand -base64 14 | cut -c 1-16
}
password1=$(generate_random)
echo "root : $password1" >> password.txt
password2=$(generate_random)
echo "repl : $password2" >> password.txt
password3=$(generate_random)
echo "appuser : $password3" >> password.txt
password4=$(generate_random)
echo "readuser : $password4" >> password.txt
echo "zbx : zabbix" >> password.txt
password5=$(generate_random)
echo "backup : $password5" >> password.txt
mysql_install(){
#编写配置文件
cat > /etc/my.cnf << EOF
[client]
port = 3306
user = root
socket = /data/mysql/data/mysql_3306.sock
password =
[mysql]
prompt ='[\c][Conn:\C][\R:\\m:\\s][\U][\d]> '
default-character-set = utf8mb4
#prompt ='\U[\d]> '
tee = /data/mysql/mysql_operation.log
[mysqld]
# Base Config
server_id = 108
basedir = /usr/local/mysql
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
secure_file_priv = /data/mysql/tmp
pid-file = mysqld.pid
socket = /data/mysql/data/mysql_3306.sock
user = mysql
port = 3306
default_storage_engine = InnoDB
character_set_server = utf8mb4
skip_slave_start = 1
skip-name-resolve = 1
skip-external-locking = 1
lower_case_table_names = 1
query_cache_type = 0
query_cache_size = 0
max_connections = 1000
default-time-zone = '+8:00'
log_timestamps = SYSTEM
# InnoDB config
innodb_strict_mode = 1
innodb_file_per_table = 1
innodb_stats_on_metadata = 0
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_max_dirty_pages_pct = 60
innodb_io_capacity = 400
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
# Cache config
key_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 1000
table_open_cache = 2048
open_files_limit = 65535
max_allowed_packet = 64M
# Log config
log_error = mysql-error.log
slow_query_log_file = mysql-slow.log
relay-log = mysql-relay
log-bin = mysql-bin
slow_query_log = 1
long_query_time = 0.2
#log_slow_admin_statements = 1
#log_slow_slave_statements = 1
# Semisync config
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
# Replication config
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
expire_logs_days = 14
binlog_format = row
log_slave_updates = ON
binlog_checksum = NONE
max_binlog_size = 250M
binlog_cache_size = 2M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
relay-log-info-repository = TABLE
master_info_repository = TABLE
relay_log_recovery = 1
binlog_rows_query_log_events = 1
log_bin_trust_function_creators = 1
# GTID
gtid-mode = ON
enforce-gtid-consistency = 1
# Performance Schema
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
EOF
## 安装软件依赖
yum install libaio -y
## 创建用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
## 解压软件包并创建软连接
tar -zxvf mysql-*-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-*-linux-glibc2.12-x86_64 mysql
## 修改软件目录权限为mysql用户
cd /usr/local/mysql
chown -R mysql:mysql .
## 创建数据目录权限并修改权限为mysql用户
mkdir -p /data/mysql/{data,tmp,backup,slowlog_format}
chown -R mysql:mysql /data/mysql/
## 拷贝启动脚本至系统启动目录
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
## 执行数据库初始化操作
cd /usr/local/mysql
bin/mysqld --initialize --user=mysql
## 启动MySQL
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
#systemctl disable mysqld
/etc/init.d/mysqld start
#数据库账户配置
tempassword=`grep 'A temporary password' /data/mysql/data/mysql-error.log | awk -F"root@localhost: " '{ print $2}' `
mysql -S /data/mysql/data/mysql_3306.sock -uroot -p${tempassword} --connect-expired-password <<EOF
set password='${password1}';
exit
EOF
echo "***********************************************8"
#创建数据库及账户
mysql -S /data/mysql/data/mysql_3306.sock -uroot -p${password1} --connect-expired-password <<EOF
grant REPLICATION CLIENT,replication slave on *.* to 'repl'@'%' identified by '${password2}';
create database ${dbname};
create user 'appuser'@'%' identified by '${password3}';
grant select,update,delete,insert on ${dbname}.* to 'appuser'@'%';
create user 'readuser'@'%' identified by '${password4}';
grant select on $dbname.* to 'readuser'@'%';
GRANT SELECT,SHOW VIEW,EVENT,TRIGGER,LOCK TABLES,RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'backup'@'127.0.0.1' identified by '${password5}';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'zbx'@'127.0.0.1' identified by 'zabbix';
exit
EOF
#重启mysql
#/etc/init.d/mysqld restart
#配置环境变量
echo "#set mysql environment" >> ~/.bash_profile
echo "export MYSQL_HOME=/usr/local/mysql" >> ~/.bash_profile
echo "export PATH=\$PATH:\$HOME/bin:\$MYSQL_HOME/bin" >> ~/.bash_profile
source ~/.bash_profile
#安装percona-xtrabackup
cd $home
tar -zxvf percona-xtrabackup-*-Linux-x86_64.tar.gz
cp percona-xtrabackup-*-Linux-x86_64/bin/* /usr/local/bin/
rm percona-xtrabackup-*-Linux-x86_64 -rf
cp qpress /usr/local/bin/
tar -zxvf scripts.tar.gz
cp -r scripts /usr/local/mysql/
#修改全备和增备的脚本文件中备份用户的端口和密码即可使用
sed -i "1,10s|^MYSQL_BACKUP_PASS.*|MYSQL_BACKUP_PASS='${password5}'|" /usr/local/mysql/scripts/mysqldump_schema_backup.sh
sed -i "1,10s|^MYSQL_BACKUP_PASS.*|MYSQL_BACKUP_PASS='${password5}'|" /usr/local/mysql/scripts/slow_log_format.sh
sed -i "1,10s|^MYSQL_BACKUP_PASS.*|MYSQL_BACKUP_PASS='${password5}'|" /usr/local/mysql/scripts/xtrabackup_checksum.sh
sed -i "1,10s|^MYSQL_BACKUP_PASS.*|MYSQL_BACKUP_PASS='${password5}'|" /usr/local/mysql/scripts/xtrabackup_full_backup.sh
sed -i "1,10s|^MYSQL_BACKUP_PASS.*|MYSQL_BACKUP_PASS='${password5}'|" /usr/local/mysql/scripts/xtrabackup_incr_backup.sh
sed -i "1,10s|^MYSQL_BACKUP_PASS.*|MYSQL_BACKUP_PASS='${password5}'|" /usr/local/mysql/scripts/xtrabackup_recovery_prepare.sh
#添加定时任务
echo "0 0 * * 0 /usr/local/mysql/scripts/xtrabackup_full_backup.sh" >> /var/spool/cron/root
echo "0 0 * * 1-6 /usr/local/mysql/scripts/xtrabackup_incr_backup.sh" >> /var/spool/cron/root
echo "0 3 * * * /usr/local/mysql/scripts/slow_log_format.sh" >> /var/spool/cron/root
echo "0 4 * * * /usr/local/mysql/scripts/mysqldump_backup.sh" >> /var/spool/cron/root
}
main(){
mysql_install
}
main
生成的密码文件如下：1
2
3
4
5
6
7# cat password.txt
root : o/FpOMHcM2YHt8GQ
repl : TqOeLuFy1NO5zHLn
appuser : 5GQzCFH6Qvc8VJ91
readuser : NqMHHw0CZHsk5MzK
zbx : zabbix
backup : DClBRGqCfg8MF26L
待优化！
