MySQL5.6生产库自动化安装部署

2023-01-03 07:29:50

原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http://suifu.blog.51cto.com/9167728/1846671

自动化运维是一个DBA应该掌握的技术，其中，自动化安装数据库是一项基本的技能，本文中的安装脚本已通过测试，作为生产库来说没有问题，鉴于每个公司存储规划要求不同，可以按需自行修改脚本。

脚本中已经注释说明一些基本的安装信息

本脚本默认启用5.6部分新特性

innodb_buffer_pool_dump_at_shutdown=1 它dump的不是数据，是Id号

innodb_buffer_pool_load_at_startup=1

开启这个两个参数当数据库重启后把这些热数据重新加载回去

只有正常关库才会dump热数据块，宕机和kill -9不会

部分参数按需整改，例如innodb_buffer_pool_size = 512M，本文给的512M，一般给内存的50%-80%。

来看一下脚本的具体情况
[root@HE3 ~]# cat mysql_auto_install.sh

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

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

###### 二进制自动安装数据库脚本root密码MANAGER将脚本和安装包放在/root目录即可###############

######数据库目录/usr/local/mysql############

######数据目录/data/mysql############

######日志目录/log/mysql############

######端口号默认3306其余参数按需自行修改############

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

#author：rrhelei@126.com#

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

#!/bin/bash

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/opt/bin:/opt/sbin:~/bin

export

PATH

# Check

if user is root

if [ $(id

-u) != "0" ]; then

echo "Error: You must be root to run

this script, please use root to install"

exit 1

fi

clear

echo

"========================================================================="

echo

"A tool to auto-compile & install MySQL 5.6.25 on Redhat/CentOS Linux

"

echo

"========================================================================="

cur_dir=$(pwd)

#set

mysql root password

echo "==========================="

mysqlrootpwd="MANAGER"

echo -e "Please input the root password of mysql:"

read -p "(Default password: MANAGER):" mysqlrootpwd

if [ "$mysqlrootpwd" = "" ]; then

mysqlrootpwd="MANAGER"

fi

echo "==========================="

echo "MySQL root password:$mysqlrootpwd"

echo "==========================="

#which

MySQL Version do you want to install?

echo

"==========================="

isinstallmysql56="n"

echo "Install MySQL 5.6.25,Please input y"

read -p "(Please input y , n):" isinstallmysql56

case "$isinstallmysql56" in

y|Y|Yes|YES|yes|yES|yEs|YeS|yeS)

echo "You will install MySQL 5.6.25"

isinstallmysql56="y"

;;

*)

echo "INPUT error,You will exit install MySQL 5.6.25"

isinstallmysql56="n"

exit

esac

get_char()

{

SAVEDSTTY=`stty -g`

stty -echo

stty cbreak

#dd if=/dev/tty bs=1 count=1 2> /dev/null

stty -raw

stty echo

stty $SAVEDSTTY

}

echo ""

echo "Press any key to start...or Press Ctrl+c to cancel"

char=`get_char`

#

Initialize the installation related

content.

function

InitInstall()

{

cat /etc/issue

uname -a

MemTotal=`free -m | grep Mem | awk '{print $2}'`

echo -e "\n Memory is: ${MemTotal} MB "

#Set timezone

rm -rf /etc/localtime

ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

#Delete Old Mysql program

rpm -qa|grep mysql

rpm -e mysql

#yum -y remove mysql-server mysql mysql-libs

#yum -y remove php-mysql

#yum -y install yum-fastestmirror

#yum -y update

#Disable SeLinux

if [ -s /etc/selinux/config ]; then

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

fi

setenforce 0

}

#Installation

of depend on and optimization options.

function

InstallDependsAndOpt()

{

cd

$cur_dir

cat

>>/etc/security/limits.conf<<EOF

* soft

nproc 65535

* hard

nproc 65535

* soft

nofile 65535

* hard

nofile 65535

EOF

echo

"fs.file-max=65535" >> /etc/sysctl.conf

}

#Install

MySQL

function

InstallMySQL56()

{

echo

"============================Install MySQL

5.6.22=================================="

cd

$cur_dir

#Backup

old my.cnf

#rm -f

/etc/my.cnf

if [ -s

/etc/my.cnf ]; then

mv /etc/my.cnf /etc/my.cnf.`date

+%Y%m%d%H%M%S`.bak

fi

#mysql

directory configuration

groupadd

mysql -g 512

useradd -u 512 -g mysql -s /sbin/nologin -d /home/mysql mysql

tar xvf /root/mysql-5.6.25-linux-glibc2.5-x86_64.tar.gz

mv /root/mysql-5.6.25-linux-glibc2.5-x86_64 /usr/local/mysql

mkdir -p /data/mysql

mkdir -p /log/mysql

chown -R mysql:mysql /data/mysql

chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /log

#edit /etc/my.cnf

SERVERID=`ifconfig eth0 | grep "inet addr" | awk '{ print $2}'| awk -F. '{ print $4"3306"}'`

cat

>>/etc/my.cnf<<EOF

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

open_files_limit = 3072

back_log = 103

max_connections = 800

max_connect_errors = 100000

table_open_cache = 512

external-locking = FALSE

max_allowed_packet = 32M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 51

query_cache_size = 32M

tmp_table_size = 96M

max_heap_table_size = 96M

slow_query_log = 1

slow_query_log_file = /log/mysql/slow.log

log-error = /log/mysql/error.log

long_query_time = 1

server-id = $SERVERID

log-bin = mysql-bin

sync_binlog = 1

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1024M

expire_logs_days = 60

key_buffer_size = 32M

read_buffer_size = 1M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 64M

character-set-server=utf8

default-storage-engine = InnoDB

binlog_format = row

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1

binlog_rows_query_log_events = 1

explicit_defaults_for_timestamp = 1

#log_slave_updates=1

#gtid_mode=on

#enforce_gtid_consistency=1

#innodb_write_io_threads = 8

#innodb_read_io_threads = 8

#innodb_thread_concurrency = 0

transaction_isolation = REPEATABLE-READ

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:1024M:autoextend

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 16M

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_locks_unsafe_for_binlog = 0

wait_timeout = 14400

interactive_timeout = 14400

skip-name-resolve

[mysqldump]

quick

max_allowed_packet = 32M

EOF

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql

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

chmod 700 /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig --level 2345 mysqld on

cat

>> /etc/ld.so.conf.d/mysql-x86_64.conf<<EOF

/usr/local/mysql/lib

EOF

ldconfig

if [ -d "/proc/vz" ];then

ulimit -s unlimited

fi

/etc/init.d/mysqld start

cat >> /etc/profile <<EOF

export PATH=$PATH:/usr/local/mysql/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib

EOF

source /etc/profile

/usr/local/mysql/bin/mysqladmin -u root password $mysqlrootpwd

cat > /tmp/mysql_sec_script<<EOF

use mysql;

delete from mysql.user where user!='root' or host!='localhost';

grant all privileges on *.* to 'sys_admin'@'%' identified by 'MANAGER';

flush privileges;

EOF

/usr/local/mysql/bin/mysql -u root -p$mysqlrootpwd -h localhost < /tmp/mysql_sec_script

#rm -f /tmp/mysql_sec_script

/etc/init.d/mysqld restart

echo

"============================MySQL 5.6.25 install completed========================="

}

function CheckInstall()

{

echo "===================================== Check install ==================================="

clear

ismysql=""

echo

"Checking..."

if [ -s /usr/local/mysql/bin/mysql ] && [ -s /usr/local/mysql/bin/mysqld_safe ] && [ -s /etc/my.cnf ]; then

echo "MySQL: OK"

ismysql="ok"

else

echo "Error: /usr/local/mysql not found!!!MySQL install failed."

fi

if [ "$ismysql" = "ok" ]; then

echo "Install MySQL 5.6.25 completed! enjoy it."

echo "========================================================================="

netstat -ntl

else

echo "Sorry,Failed to install MySQL!"

echo "You can tail /root/mysql-install.log from your server."

fi

}

#The

installation log

InitInstall 2>&1 | tee /root/mysql-install.log

CheckAndDownloadFiles 2>&1 | tee -a /root/mysql-install.log

InstallDependsAndOpt 2>&1 | tee -a /root/mysql-install.log

InstallMySQL56 2>&1 | tee -a /root/mysql-install.log

CheckInstall 2>&1 | tee -a /root/mysql-install.log

执行脚本后，输入用户名密码（默认MANAGER)后登录数据库成功。

至此，MySQL5.6安装完成。

本文出自 “岁伏” 博客，请务必保留此出处http://suifu.blog.51cto.com/9167728/1846671

码农公寓

相关文章