sudo service mysql start
mysql -u root
CREATE DATABASE shiyanlou;
use mysql;
create user shiyanlou IDENTIFIED by 'shiyanlou';
#select host,user,password from user;
grant ALL privileges on shiyanlou.* to shiyanlou identified by 'shiyanlou';
flush privileges;
#show grants for shiyanlou;
中文csv导入数据库
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
use shiyanlou;
#show character set;
/*********table course********/
create table course (
id integer not null,
name varchar(64) not null,
primary key (id)
) default character set utf8mb4;
load data infile '/home/shiyanlou/loudatabase/shiyanlou_course.csv'
into table course character set utf8mb4
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n';
#select * from course;
/*********table user********/
create table user (
id integer not null,
name varchar(64) not null,
primary key (id)
) default character set utf8mb4;
load data infile '/home/shiyanlou/loudatabase/shiyanlou_user.csv'
into table user character set utf8mb4
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n';
/********table usercourse*********/
create table usercourse (
user_id integer not null,
course_id integer not null,
study_time integer not null,
foreign key(user_id) references user(id),
foreign key(course_id) references course(id)
) default character set utf8mb4;
load data infile '/home/shiyanlou/loudatabase/shiyanlou_usercourse.csv'
into table usercourse character set utf8mb4
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n';
# 在第一列增加一列自动增长的id列
alter table usercourse add COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY first;
学习时间最长
#select user.name,course.name,study_time from usercourse,course,user where usercourse.course_id = course.id and user.id = usercourse.user_id group by user_id group by user.name,course.name;
create table favorite select user.name as user_name ,course.name as course_name,max(study_time) as study_time from usercourse,course,user where usercourse.course_id = course.id and user.id = usercourse.user_id group by user.name;
alter table favorite add COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY first;
查询慢
alter table usercourse add index user_Index(user_id);
alter table usercourse add index studytime_Index(study_time);
备份和恢复
/*************************/
crontab -u shiyanlou –e
#每个星期一凌晨3:00完全备份
0 3 * * 1 /usr/bin/mysqldump --opt --force -uroot shiyanlou> /usr/lib/mysql/"mysql-`date +'%Y%m%d-%H:%M:%S'`".sql
#周二到周天凌晨3:00增量备份
0 3 * * 2-7 mysqladmin -uroot flush-logs
mysqlbackup.sh
# /bin/bash
DB_NAME="****"
DB_USER="****"
DB_PASS="****"
BIN_DIR="/usr/bin"
BACK_DIR="/data/backdata"
DATE="mysql-`date +'%Y%m%d-%H:%M:%S'`"
LogFile="$BACK_DIR"/dbbakup.log #日志记录保存的目录
BackNewFile=$DATE.sql
$BIN_DIR/mysqldump --opt --force -u$DB_USER -p$DB_PASS $DB_NAME > $BACK_DIR/$DATE.sql
echo ----------"$(date +"%y-%m-%d %H:%M:%S")"------------ >> $LogFile
echo createFile:"$BackNewFile" >> $LogFile
#find "/data/backdata/" -cmin +1 -type f -name "*.sql" -print > deleted.txt
find "/data/backdata/" -ctime +7 -type f -name "*.sql" -print > deleted.txt
echo -e "delete files:\n" >> $LogFile
#循环删除匹配到的文件
cat deleted.txt | while read LINE
do
rm -rf $LINE
echo $LINE>> $LogFile
done
echo "---------------------------------------------------------------" >> $LogFile
