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 <<"EOF"
ifconfig bond0:0 10.1.8.108 netmask 255.255.255.0 up
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