mysql部署rainbow_mysql installation

mysql server installation

#!/bin/bash

# disable ctrl-alt-del

#sed -i 's@ca::ctrlaltdel:/sbin/shutdown -t3 -r now@#ca::ctrlaltdel:/sbin/shutdown -t3 -r now@' /etc/inittab

# change runlevel from 5 to 3

#sed -i 's@id:5:initdefault:@id:3:initdefault:@' /etc/inittab

#/sbin/init q

# disable

chkconfig --list | grep 3:on | awk '{print $1}' | egrep -v 'sshd|network|syslog|crond|irqbalance|microcode_ctl|sysstat|xinetd|dataeng|dsm_om_connsvc|dsm_om_shrsvc|instsvcdrv|ntpd' | xargs -i{} chkconfig --level 3 {} off

# reboot system to tack effect.

#!/bin/bash

# modified by jack.geng 2010-07-27

# upload software to /usr/local/

# 1. mysql-5.1.45.tar.gz

# 2. xtrabackup-1.2.tar.gz

# 3. upload mysql configuration my.cnf to /etc/my.cnf

cd

yum -y remove mysql mysql-server

# install vip(10.1.8.108)

cat > bringvip.sh

route add -host 10.1.8.108 dev bond0:0

EOF

chmod +x bringvip.sh

cat > downvip.sh << "EOF"

route del -host 10.1.8.108 dev bond0:0

ifconfig bond0:0 down

EOF

chmod +x downvip.sh

/usr/sbin/groupadd -g 500 malabs

/usr/sbin/useradd -g malabs -u 500 -d /usr/local/mysql mysql

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

# make mysql 5.1.45 from source

cd /usr/local/

tar xfz mysql-5.1.45.tar.gz

cd mysql-5.1.45

./configure --with-plugins=partition,federated,innobase,myisam --without-docs --prefix=/usr/local/mysql

make && make install

cd ../

cp mysql/share/mysql/mysql.server  /etc/init.d/mysql

# install xtrabackup; a bug report for xtrabackup source ,when patch mysql source , compile xtrabackup.c error occured , so use binary of xtrabackup . full backup if ok , but increment backup will cause segment fault.

cd /usr/local/

tar xfz xtrabackup-1.2.tar.gz

mv xtrabackup-1.2 xtrabackup

mv /usr/local/xtrabackup/bin/innobackupex-1.5.1 /usr/local/xtrabackup/bin/innobackupex

cat > /usr/local/mysql/.bash_profile << "EOF"

# .bash_profile

# Get the aliases and functions

if [ -f /etc/bashrc ]; then

. /etc/bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/xtrabackup/bin

export PATH

EOF

chown mysql:malabs /usr/local/mysql/.bash_profile

# mysql_install_db

sed -i  '23s/var/data/' /usr/local/mysql/bin/mysql_install_db

# make mysql.err , binlog and backup directory

mkdir /usr/local/mysql/logs

mkdir /usr/local/mysql/binlog

mkdir /usr/local/mysql/bk

# database backup script,using system account mysql

cat > /usr/local/mysql/fullbackup.sh << "EOF"

#!/bin/bash

export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/opt/dell/srvadmin/bin:/usr/local/mysql/bin:/usr/local/mysql:/usr/local/xtrabackup/bin

echo `date` >> /usr/local/mysql/fullbackup.log

TMPFOLDER=`date +"%Y-%m-%d"`

/usr/local/xtrabackup/bin/innobackupex --user=root --stream=tar /usr/local/mysql/bk/ |gzip > /usr/local/mysql/bk/b"$TMPFOLDER".tar.gz

# cp backup to madb2

scp /usr/local/mysql/bk/b"$TMPFOLDER".tar.gz  mysql@madb2:/usr/local/mysql/bk/

# delete expired backup

RMFOLDER=`date -d "now -7 days" +"%Y-%m-%d"`

rm /usr/local/mysql/bk/b"$RMFOLDER".tar.gz

echo `date` backup finished...  >> /usr/local/mysql/fullbackup.log

EOF

chmod +x /usr/local/mysql/fullbackup.sh

# add bk sync scripts, using system account mysql

cat > /usr/local/mysql/bksync.sh << "EOF"

echo `date` >>/usr/local/mysql/bksync.log

rsync -av --rsh="ssh -l mysql" madb1:'/usr/local/mysql/bk/*' /usr/local/mysql/bk >> /usr/local/mysql/bksync.log

EOF

chmod +x /usr/local/mysql/bksync.sh

cat > /usr/local/mysql/createdb.sh << "EOF"

# create database

mysqladmin create malabs;

mysqladmin create erp;

mysqladmin create prodlib;

EOF

chmod +x /usr/local/mysql/createdb.sh

cat > /usr/local/mysql/createpriv.sql << "EOF"

# create user/privileges

GRANT ALL PRIVILEGES ON malabs.* TO 'user_malabs'@'%' IDENTIFIED BY 'malabs!40glab';

GRANT select  ON erp.* TO 'user_malabs'@'%';

GRANT ALL PRIVILEGES ON erp.* TO 'user_erp'@'%' IDENTIFIED BY 'erp!40glab';

GRANT ALL PRIVILEGES ON *.* TO 'user_prodlib'@'%' IDENTIFIED BY 'prodlib!40glab';

# GRANT select ON erp.* TO 'user_prodlib'@'%';

# GRANT create,drop,alter,delete,index,insert,select,update ON malabs.* TO 'user_prodlib'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'admin!40glab';

# create user/privileges for replication

GRANT REPLICATION SLAVE ON *.* TO 'mysqlslave'@'madb2' IDENTIFIED BY 'slavepass';

GRANT REPLICATION SLAVE ON *.* TO 'mysqlslave'@'madb1' IDENTIFIED BY 'slavepass';

flush PRIVILEGES;

EOF

# mark it start when OS started

chkconfig --add mysql

chkconfig --level 3 mysql on

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

# on mydb1 server , start mysql service, mydb2 run as slave ,so the database is copied to it.

# on mydb2 server , replication function is implemented to load data from mydb1, don't run the

# following scripts

#su - mysql

#$ ./bin/mysql_install_db --user=mysql

# service mysql start

# create database

#su - mysql

#$ cd /usr/local/mysql/

#$ ./createdb.sh

#$ mysql -uroot < createpriv.sql

# add backup task to crontab, refer document: <01Building Mysql server.docx>

# before running bksync.sh , make mysql user auto ssh login first

#$ login mydb1,

#$ ssh-keygen -t dsa

#$ scp id_dsa.pub to mysql user home dir of mydb2, login on mydb2 as mysql user, cat id_dsa.pub >> ~/.ssh/authorized_keys

# now mysql user of madb1 can auto ssh login madb2 without input password.

# run the same steps , mysql user on madb2 can auto ssh login madb1

#su - mysql

#$ crontab -e

# running on master

# 0      23       *       *       *      /usr/local/mysql/fullbackup.sh >/dev/null 2>&1

# runing on slave

# 40 23 * * * /usr/local/mysql/bksync.sh >/dev/null 2>&1

# finished.

my.cnf

[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysqld]

federated

port            = 3306

socket          = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

default-storage-engine = MyISAM

character_set_server = utf8

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 16M

table_open_cache = 128

sort_buffer_size = 2M

read_buffer_size = 2M

net_buffer_length = 8K

read_rnd_buffer_size = 4M

query_cache_size         = 512M

query_cache_limit        = 1M

thread_concurrency       = 16

lower_case_table_names = 1

long_query_time      = 1

# slow_query_log

slow_query_log_file  = /usr/local/mysql/logs/slow_query.log

log-error       = /usr/local/mysql/logs/mysql-error.log

pid-file        = /usr/local/mysql/logs/mysql.pid

expire_logs_days      = 60

log-bin=/usr/local/mysql/binlog/mysql-bin

log-bin-index=/usr/local/mysql/binlog/mysql-bin.index

binlog_format=mixed

max_binlog_size       = 1G

relay-log               = /usr/local/mysql/binlog/mysql-relay-bin

relay-log-index         = /usr/local/mysql/binlog/mysql-relay-bin.index

relay-log-info-file     = /usr/local/mysql/binlog/relay-log.info

myisam_sort_buffer_size = 256M

myisam_max_sort_file_size = 1G

myisam_repair_threads     = 1

myisam_recover

server-id       = 1

# master-host           = madb2

# master-user           = mysqlslave

# master-password       = slavepass

# master-port           = 3306

skip-slave-start

innodb_data_home_dir = /usr/local/mysql/data/

innodb_data_file_path = ibdata1:256M:autoextend

innodb_buffer_pool_size = 1G

innodb_log_buffer_size = 8M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 0

innodb_fast_shutdown = 0

[mysqldump]

opt

max_allowed_packet = 16M

dump-date

skip-quote-names

default-character-set = utf8

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

[xtrabackup]

target-dir = /usr/local/mysql/bk

阅读(1370) | 评论(0) | 转发(0) |