MySQL5.7下载地址 https://dev.mysql.com/downloads/mysql/5.7.html#downloads
下面是本文介绍的MySQL安装涉及到的目录结构
/data
|-- mysql
|-- mysql_bak # mysql 备份的路径
|-- mysql_data # mysql数据文件的路径
如果目录不存在,就需要创建出来。
# mkdir -p /data/mysql/mysql_bak
# mkdir -p /data/mysql/mysql_data
安装MySQL5.7
# yum -y install cmake make gcc gcc-c++ bison ncurses ncurses-devel perl numactl
# rpm -qa|grep -i mysql 查一下是否已安装mysql, 如果有就删掉
# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
# rpm -qa|grep mariadb 查一下是否已安装mariadb, 如果有就删掉
# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
先确认centos的版本,如果是centos6则用mysql-5.7.13-1.el6.x86_64.rpm-bundle.tar包,如果是centos7则用mysql-5.7.13-1.el7.x86_64.rpm-bundle.tar,请注意两个安装包的区别
# cat /etc/redhat-release
CentOS release 6.8 (Final)
下面以centos6为例说明安装过程,(centos7的安装步骤完全一致)
如果服务器上没有mysql-5.7.13-1.el6.x86_64.rpm-bundle.tar,则下载后手工上传mysql-5.7.13-1.el6.x86_64.rpm-bundle.tar 到/home/coolgua/soft
# cd /home/coolgua/soft
# mkdir mysql
下面以mysql-5.7.13-1.el6.x86_64.rpm-bundle.tar为例,介绍MySQL安装步骤
# tar -xvf mysql-5.7.13-1.el6.x86_64.rpm-bundle.tar -C mysql
# cd mysql
# rpm -ivh mysql-community-common-5.7.13-1.el6.x86_64.rpm
# rpm -ivh mysql-community-libs-5.7.13-1.el6.x86_64.rpm
# rpm -ivh mysql-community-client-5.7.13-1.el6.x86_64.rpm
# rpm -ivh mysql-community-server-5.7.13-1.el6.x86_64.rpm
# service mysqld start
mysql启动后,会自动初始化一个密码,在日志中
# vi /var/log/mysqld.log 查找password,能获取临时生成的密码 ,如
A temporary password is generated for root@localhost: cek0kD.eGhIF
# mysql -uroot -p
Enter password: 此处输入刚才日志文件中的随机密码
修改密码:
mysql> SET PASSWORD = PASSWORD('heWif@123456Nom'); 此处的密码需要一定的复杂度,否则不能通过,因为mysql5.7的密码校验插件比较复杂,当然也可以卸掉该插件
mysql> quit
Bye
更改MySQL的datadir目录
# service mysqld stop
# mkdir -p /data/mysql/mysql_data
# cp -R -p /var/lib/mysql/* /data/mysql/mysql_data
# chown -R mysql:mysql /data/mysql/mysql_data/
编辑mysql的配置文件 /etc/my.cnf
# vi /etc/my.cnf (可以将下面示例内容复制过来再调整,或者参考线上其他服务器的内容)
[client]
port=3306
default-character-set=utf8
socket=/data/mysql/mysql_data/mysql.sock
[mysql]
default-character-set=utf8
[mysqld]
datadir=/data/mysql/mysql_data/
tmpdir=/data/mysql/mysql_data/
# basedir=/data/mysql/mysql_data/
socket=/data/mysql/mysql_data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# sql_mode=''
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8
default-storage-engine=INNODB
key_buffer_size=16M
max_allowed_packet=20M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
max_connections=500
thread_cache_size=16
query_cache_size=16M
# innodb_buffer_pool_size 配置为系统内存的大概50%
innodb_buffer_pool_size=3G
innodb_flush_log_at_trx_commit=1
tmp_table_size=4M
server-id=1
log-bin=mysql-bin
expire_logs_days=7
[mysqldump]
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
# ---------------------/etc/my.cnf结束---------------------------
# vi /etc/init.d/mysqld (推荐复制其他服务器的该文件,免得自己编辑)
找到/var/lib/mysql,将其替换为新路径/data/mysql/mysql_data
如果是从其他服务器复制过来的mysqld,则需要添加执行权限 chmod +x /etc/init.d/mysqld
# chcon -R -t mysqld_db_t /data/mysql/mysql_data
# service mysqld start
数据库自动备份脚本及配置
# cd /data/mysql/
# vi db_backup.sh
#!/bin/bash
USER=test #数据库用户名
PASSWORD=xxxxxxx #数据库用户密码
DATABASE=test #数据库名称
BACKUP_DIR=/data/mysql/mysql_bak #备份文件存储路径
LOGFILE=$BACKUP_DIR/data_backup.log #日记文件路径
DATE=`date '+%Y%m%d'` #日期格式(作为文件名)
DUMPFILE=$BACKUP_DIR/$DATABASE-$DATE.sql.gz #压缩文件名
OLDDATE=`date -d "-10 day" +%Y%m%d`
OLDFILE=$BACKUP_DIR/$DATABASE-$OLDDATE.sql.gz
#判断备份文件存储目录是否存在,否则创建该目录
if [ ! -d $BACKUP_DIR ];then
mkdir -p "$BACKUP_DIR"
fi
#开始备份之前,将备份信息头写入日记文件
echo " ">> $LOGFILE
echo "----------------------">> $LOGFILE
echo "BACKUP DATE:" $(date +"%Y-%m-%d %H:%M:%S") >> $LOGFILE
echo "----------------------">> $LOGFILE
#删除五天前的备份文件
rm -f $OLDFILE
#使用mysqldump 命令备份制定数据库,并以格式化的时间戳命名备份文件
mysqldump -u$USER -p$PASSWORD $DATABASE | gzip > $DUMPFILE
#判断数据库备份是否成功
if [[ $? == 0 ]]; then
#输入备份成功的消息到日记文件
echo "[$DUMPFILE] Backup Successful!" >> $LOGFILE
else
echo "Database Backup Fail!" >> $LOGFILE
#备份失败后向网站管理者发送邮件提醒,需要mailutils或者类似终端下发送邮件工具的支持
#mail -s “Database:$DATABASE Daily Backup Fail” $WEBMASTER
fi
#输出备份过程结束的提醒消息
echo "Backup Process Done"
#-----------------------自动备份脚本结束--------------------
# chmod +x db_backup.sh
# sh db_backup.sh
然后查看/data/mysql/mysql_bak目录下是否成功生成了备份文件。
# crontab -e #插入下面一行,具体编辑命令与vi一致
10 0 * * * sh /data/mysql/db_backup.sh
# crontab -l #查询命令
10 0 * * * sh /data/mysql/db_backup.sh
这样每天凌晨0点10分就会自动备份MySQL数据库