数据库创建用户和备份

mysql 创建用户

   insert into mysql.user(Host,User,Password) values("localhost","账号",password("密码"));

//localhost 代表在本地访问 %则代表可以在任意主机访问不过这个好像有的版本用不了

用这个也可以创建用户

  GRANT USAGE ON *.* TO '用户名'@'localhost' IDENTIFIED BY '密码' WITH GRANT OPTION;

修改用户权限

GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON  `name`.* TO 'zjke'@'127.0.0.1' WITH GRANT OPTION;

设置用户 name 只能在本地访问并且只有zjke 表的操作权限

数据库备份

#!/bin/bash
# Database info
DB_USER="user"
DB_PASS="pwd"
DB_HOST="localhost"
DB_NAME="yshkj"

# Others vars
BIN_DIR="/usr/local"            #the mysql bin path
BCK_DIR="/sql"    #the backup file directory
DATE=`date +%F`

#echo $DATE
# TODO
# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql

##备份数据库
mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/db_$DB_NAME.$DATE.sql
#打包备份
zip -r -q -o $BCK_DIR/db_$DB_NAME.$DATE.zip  $BCK_DIR/db_$DB_NAME.$DATE.sql
rm -rf $BCK_DIR/db_$DB_NAME.$DATE.sql
#echo $BCK_DIR/db_$DB_NAME.`date +%F -d '-10 days'`.zip
#删除10天前的数据库备份
rm -rf $BCK_DIR/db_$DB_NAME.`date +%F -d '-10 days'`.zip
#还原数据库
#用mysql-front导入前一天的 *.sql 文件即可恢复数据

设置定时任务每天凌晨4点50 执行备份

50 04 * * * /sql.sh