linux实验（数据库备份）

linux实验（数据库备份）

以下所有操作皆以机房电脑上的虚拟机为基础环境

下载链接：Linux课程机房虚拟机#

1. 切换到root用户

   su - root
   

2. 安装数据库mysql 5.7

   rpm -ivh https://mirrors4.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-common-5.7.29-1.el7.x86_64.rpm https://mirrors4.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-libs-5.7.29-1.el7.x86_64.rpm https://mirrors4.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-client-5.7.29-1.el7.x86_64.rpm https://mirrors4.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/mysql-community-server-5.7.29-1.el7.x86_64.rpm --force --nodeps
   

3. 配置数据库cnf文件

   vim /etc/my.cnf
   

   在文件中添加以下代码：

   kip-grant-tables
   character_set_server=utf8
   init_connect='SET NAMES utf8'
   

   ​​

4. 启动数据库

   systemctl start mysqld.service
   

5. 进入数据库并配置密码

   mysql
   update mysql.user set authentication_string=password('123456') where user='root';
   

6. 使配置生效

   flush privileges;
   

7. 退出数据库

   exit
   

8. 停止数据库

   systemctl stop  mysqld.service
   

9. 编辑my.cnf配置文件将：skip-grant-tables这一行注释掉

   vim /etc/my.cnf
   

   ​​

10. 重启数据库

    systemctl start mysqld.service
    

11. 再次登录数据库

    mysql -uroot -p123456
    

12. 创建数据库和表

    CREATE DATABASE IF NOT EXISTS Student;
    USE Student;
    CREATE TABLE `user` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(255) NULL,
      `sex` VARCHAR(255) NULL,
      `phone` VARCHAR(255) NULL,
      PRIMARY KEY (`id`)
    );
    INSERT INTO `user` VALUES (1, 'zhangsan', '1', '123456');
    INSERT INTO `user` VALUES (2, 'lisi', '1', '123456');
    INSERT INTO `user` VALUES (3, 'wangwu', '1', '123467989');
    INSERT INTO `user` VALUES (4, 'chuanchuan', '2', '123456789');
    

13. 退出数据库

    exit
    

14. 创建数据库备份脚本

    vi cmysql.sh
    

    粘贴以下内容

    #!/bin/bash
    # 要备份的数据库列表
    DBLIST="mysql Student"
    
    # 要备份的目录
    BACKUPDIR=/mydata/backups/data/mysql
    
    # 要保存的时间
    NUMDAYS=30
    
    # 备份要用到Linux命令和MySQL数据库配置
    FINDCMD="find"
    MYSQLCMD="mysql"
    MyUSER="root"		# USERNAME
    MyPASS="123456"			# PASSWORD
    MyHOST="localhost"	# Hostname
    DUMPCMD="mysqldump -u$MyUSER -h $MyHOST -p$MyPASS --lock-tables --databases"
    GZIPCMD="gzip"
    
    # 备份的日期
    BACKUPDATE=`date +%Y%m%d_%H%M`
    
    function USAGE() {
    cat << EOF
    
    usage: $0 options
    
    This script backs up a list of MySQL databases.
    
    OPTIONS:
     -h Show this message
     -a Backup all databases
     -l Databases to backup (space seperated)
     -n Number of days to keep backups
    EOF
    }
    
    while getopts "hal:n:" opt;
    do
      case $opt in
        a)
          DBLIST=""
          ;;
        h)
          USAGE
    	  exit1
    	  ;;
    	l)
    	  DBLIST="$OPTARG"
    	  ;;
    	n)
    	  NUMDAYS="$OPTARG"
    	  ;;
    	\?)
    	  USAGE
    	  exit
    	  ;;
    	:)
    	  echo "Option -$OPTARG requires an argument.">&2
    	  exit 1
    	  ;;
    	esac
    done
    
    function ERROR() {
      echo && echo "[error] $@"
      exit 1
    }
    
    function NOTICE() {
      echo && echo "[notice] $@"
    }
    
    function RUNCMD() {
      echo $@
      eval $@
    }
    
    # 用备份的数据作健壮性判断
    if [ ! -n "$DBLIST" ]; then
      DBLIST= `$MYSQLCMD -N -s -e "show databases" | grep -viE ' (information_schema | performance_schema|mysql|test)'`
    
      if [ ! -n "$DBLIST" ]; then
        ERROR "Invalid database list"
      fi
    fi
    
    if [ ! -n "$BACKUPDIR" ]; then
      ERROR "Invalid backup directory"
    fi
    
    if [[ ! $NUMDAYS =~ ^[0-9]+$ ]]; then
      ERROR "Invalid number of days: $NUMDAYS"
    elif [ "$NUMDAYS" -eq "0" ]; then
      ERROR "Number of days must be greater than zero"
    fi
    
    # 屏蔽字
    umask 077
    
    # $BACKUPDIR是前面定义的备份的目录变量
    RUNCMD mkdir -p -v $BACKUPDIR
    
    if [ ! -d $BACKUPDIR ]; then
      ERROR "Invalid directory: $BACKUPDIR"
    fi
    
    NOTICE "Dumping MySQL databases..."
    RC=0
    
    for database in $DBLIST; do
      NOTICE "Dumping $database..."
      RUNCMD "$DUMPCMD $database | $GZIPCMD > $BACKUPDIR/${database}_$BACKUPDATE.sql.gz"
    
      RC=$?
      if [ $RC -gt 0 ]; then
        continue;
      fi
    done
    
    if [ $RC -gt 0 ]; then
      ERROR "MySQLDump failed!"
    else
      NOTICE "Removing dumps older than $NUMDAYS days..."
      RUNCMD "$FINDCMD $BACKUPDIR -name \" *.sql.gz\" -type f -mtime +$NUMDAYS -print0 | xargs -0 rm -fv"
    
      NOTICE "Listing backup directory contents..."
      RUNCMD ls -la $BACKUPDIR
    
      NOTICE "MySQLDump is complete!"
    fi
    
    # exit 0
    

15. 运行脚本

    bash cmysql.sh
    

至此，恭喜你完成了该实验

‍

参考链接：

centos7安装mysql（完整）

‍