部署前准备：

iptables -F

/etc/init.d/iptables save

/etc/init.d/iptables stop

chkconfig iptables off

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

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

setenforce 0

/etc/init.d/sshd start

chkconfig sshd on

chkconfig network on

yum install -y ntp

ntpdate asia.pool.ntp.org

 

vi /etc/security/limits.conf

* soft nproc 65535

* hard nproc 65535

* soft nofile 65535

* hard nofile 65535

 

1、数据库安装

MySQL8.0.11安装（脚本）：

先将安装包上传到/usr/local/，其中innodb-buffer-pool-size 根据具体内存大小修改，此脚本为16G内存

---------------------------------------------------------------------------------------------------------------------------------

#!/bin/bash

yum -y install wget   libaiolibaio-devel yum-utils numactl

wgethttp://repo.mysql.com/mysql57-community-release-el6-11.noarch.rpm  

rpm -Uvhmysql57-community-release-el6-11.noarch.rpm

yum-config-manager --disable mysql57-community

yum-config-manager --disable mysql56-community

yum-config-manager --disable mysql55-community

yum-config-manager --enable mysql80-community

yum -yinstall mysql-community-client-8.0.11*

mkdir /home/data/mysql -p

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

cd /usr/local

tar zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

ln -smysql-8.0.11-linux-glibc2.12-x86_64mysql

cd mysql

mkdir mysql-files

chmod 750 mysql-files

chownmysql:mysql  mysql-files

bin/mysqld --initialize --user=mysql --datadir=/home/data/mysql --basedir=/usr/local/mysql --pid-file=/home/data/mysql/mysql.pid --socket=/home/data/mysql/mysql.sock     >/tmp/log  2>&1

if [ $? -eq 0 ];then

 grep 'root@localhost' /tmp/log | cut -d ':' -f4 > /tmp/tempwd

if [ $? -eq 0 ];then

rm -rf /tmp/log

fi

fi  

bin/mysql_ssl_rsa_setup  --datadir=/home/data/mysql        

chown -R root .

chown -R mysql:mysql      /home/data/mysql

chown -R mysql   mysql-files

 

cat <<EOF >/etc/my.cnf

[mysql]

# CLIENT #

port                           = 3306

socket                         = /home/data/mysql/mysql.sock

default-character-set          = utf8

 

[mysqld]

# GENERAL #

user                       = mysql

default-storage-engine         = InnoDB

socket                         = /home/data/mysql/mysql.sock

pid-file                       = /home/data/mysql/mysql.pid

port                           = 3306

character-set-server = utf8

sql_mode                  = ''

skip_name_resolve = 1

event_scheduler                = 1

log_bin_trust_function_creators = 1

secure_file_priv               = '/tmp'

#skip-grant-tables=1

default_authentication_plugin=mysql_native_password

#skip-grant-tables

collation-server=utf8_unicode_ci

 

# MyISAM #

key-buffer-size                = 32M

log_timestamps                 = SYSTEM

# SAFETY #

max-allowed-packet             = 64M

max-connect-errors             = 1000000

 

# DATA STORAGE #

datadir                        = /home/data/mysql/

 

# BINARY LOGGING #

log-bin                        = /home/data/mysql/mysql-bin

expire-logs-days               = 7

sync-binlog                    = 1

binlog_format                  = row

 

# REPLICATION #

server-id                      = 1

skip-slave-start               = 1

log-slave-updates              = 1

#relay-log                     = /home/data/mysql/relay-bin

slave-net-timeout              = 60

sync-master-info               = 1

sync-relay-log                 = 1

sync-relay-log-info            = 1

 

# CACHES AND LIMITS #

tmp-table-size                 = 64M

max-heap-table-size            = 64M

max-connections                = 500

thread-cache-size              = 200

open-files-limit               = 65535

table-definition-cache         = 1024

table-open-cache               = 200

 

# INNODB #

innodb-log-files-in-group      = 2

innodb-log-file-size           = 64M

innodb_log_buffer_size         = 8M

innodb_lock_wait_timeout       = 50

innodb-flush-log-at-trx-commit = 1

innodb-file-per-table          = 1

innodb-buffer-pool-size        = 8G

innodb_read_io_threads         = 8

innodb_write_io_threads        = 8

innodb_data_home_dir           = /home/data/mysql/

innodb_data_file_path          = ibdata1:10M:autoextend

innodb_log_group_home_dir      = /home/data/mysql/

 

#other

bulk_insert_buffer_size        = 64M

sort_buffer_size               = 16M

read_buffer_size               = 512K

read_rnd_buffer_size           = 1M

myisam_sort_buffer_size        = 8M

 

# LOGGING #

log-error                      = /home/data/mysql/mysql-error.log

log-queries-not-using-indexes  = 1

slow-query-log                 = 1

slow-query-log-file            = /home/data/mysql/mysql-slow.log

EOF

bin/mysqld_safe --user=mysql &

cp support-files/mysql.server /etc/init.d/mysql.server

sed -i 's/^basedir=/basedir=\/usr\/local\/mysql/'/etc/init.d/mysql.server

sed -i 's/^datadir=/datadir=\/home\/data\/mysql/'/etc/init.d/mysql.server

/etc/init.d/mysql.server restart

if [ $? -ne 0 ];then

/etc/init.d/mysql.server restart

fi

mysql -uroot -p`head -n1 /tmp/tempwd`  --connect-expired-password  -e "alter user 'root'@'localhost' identified by 'xxxx'"

if [ $? -eq 0 ];then

rm -rf /tmp/tempwd

/etc/init.d/mysql.server restart

/etc/init.d/mysql.server restart

exit 0

fi

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2、安装完成后此刻不知道密码，my.cnf添加skip-grant-tables  跳过密码

刷新权限，修改密码

flush privileges;

alter user 'root'@'localhost' identified by 'some34QA';

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxx';

flush privileges;

重启数据库

/etc/init.d/mysql.server restart

 

3、导入结构和数据,导入数据之前修改备份脚本，将函数和存储过程的地址改了，也可恢复后再修改

mysql -uroot -p <mysqldump_icaipiao_xxxxxxx.sql




4、日志轮转：

配置/etc/logrotate.d/mysql_log文件：

vim /etc/logrotate.d/mysql_log

 

/home/data/mysql/mysql-slow.log

/home/data/mysql/mysql-error.log

{

create0640 mysql mysql

weekly

notifempty

rotate 6

missingok

dateext

}

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



备份脚本：

vim /opt/backup-mysql.sh   添加下面内容

 

#!/bin/bash

 

Ym="mysqldump_ _`date +%Y%m%d`"

basepath='/home/data/backup'

file=`date +%Y-%m-%d_%H:%M:%S`

start=`date +%Y-%m-%d_%H:%M:%S`

echo -e "开始执行备份：$start" >> $basepath/auto_backup.log

 

if [ "$UID" -ne "0" ];then

echo "must to be use root for exec shell"

exit

fi

 

 

if [ ! -d "$basepath" ]; then

         mkdir -p "$basepath"

         echo -e "the $basepath create successfully!"

else

         echo -e "this $basepath is exists..."

fi

if [ -f $basepath/$Ym.sql ];then

         rm -rf $basepath/$Ym.sql

fi

if [ -f $basepath/$Ym.sql.tar.gz ];then

         rm -rf $basepath/$Ym.sql.tar.gz

fi

 

a=1

while(( $a )) 

do

/usr/local/mysql/bin/mysqldump-uroot -pxxxx -S /home/data/mysql/mysql.sock --single-transaction  --routines=1 --triggers --events=true  --flush-logs --opt -A >  $basepath/$Ym.sql

         if [ $? -eq 0 ]; then

                     cd $basepath && /bin/tar -zcvf $Ym.sql.tar.gz $Ym.sql > /dev/null

              if [ -f  $Ym.sql.tar.gz ]; then

                            find /home/data/backup/ -iname 'mysqldump*' -mtime +15 -exec rm -rf {} \;

                     rm -rf $Ym.sql

                     echo  "The mysql backup successed!!!!"

                            break

            fi

         else

                   let a++

     if [ $a -ge 3 ];then

                  echo -e "the mysql backup $basepath failed,please check!!!"

         exit

         fi

          fi

done