HIGO

English

中文

English

中文

Appearance

Pigai MySQL ES ​

1. Software Overview ​

MySQL version: 5.7.36-0ubuntu0.18.04.1-log

shell
docker run -itd -p 3331:3306 --name mysql5.7.36 \
--restart always --privileged=true \
-v $PWD/conf:/etc/mysql/conf.d -v $PWD/mysql_data:/var/lib/mysql   \
-e MYSQL_ROOT_PASSWORD=mypassword \
-e TZ=Asia/Shanghai mysql:5.7.36
docker run -itd -p 3331:3306 --name mysql5.7.36 \
--restart always --privileged=true \
-v $PWD/conf:/etc/mysql/conf.d -v $PWD/mysql_data:/var/lib/mysql   \
-e MYSQL_ROOT_PASSWORD=mypassword \
-e TZ=Asia/Shanghai mysql:5.7.36

Mirror Installation & Configuration ​

shell
# Fix /etc/resolv.conf reset on reboot (Ubuntu Kylin)
vim /etc/systemd/resolved.conf
# Change to: DNS=<your-dns-server>
service systemd-resolved restart
# Fix /etc/resolv.conf reset on reboot (Ubuntu Kylin)
vim /etc/systemd/resolved.conf
# Change to: DNS=<your-dns-server>
service systemd-resolved restart

Full SSL Configuration ​

server {
    listen        80;
    listen     443 ssl;
    server_name  mj.penly.cn;
    root   /data/app/vue.pigai.org/other/;
    ssl_certificate       /data/ssl/certs/live/penly.cn/fullchain.pem;
    ssl_certificate_key  /data/ssl/certs/live/penly.cn/privkey.pem;

    location / {
        index index.html;
        autoindex  off;
        proxy_read_timeout 300;
        proxy_connect_timeout 300;
        proxy_buffering off;
        proxy_pass  http://127.0.0.1:3001;
        break;
    }
}
server {
    listen        80;
    listen     443 ssl;
    server_name  mj.penly.cn;
    root   /data/app/vue.pigai.org/other/;
    ssl_certificate       /data/ssl/certs/live/penly.cn/fullchain.pem;
    ssl_certificate_key  /data/ssl/certs/live/penly.cn/privkey.pem;

    location / {
        index index.html;
        autoindex  off;
        proxy_read_timeout 300;
        proxy_connect_timeout 300;
        proxy_buffering off;
        proxy_pass  http://127.0.0.1:3001;
        break;
    }
}

Database Migration — InnoDB Copy-Based Restore ​

Reference: https://www.cnblogs.com/gjc592/p/9257613.html

sh
[mysqld]
init_connect='SET NAMES utf8'
character_set_server=utf8
 
[client]
default-character-set=utf8 


CREATE TABLE `member` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `password` char(32) NOT NULL,
  `email` char(100) NOT NULL,
  `user_name` char(100) NOT NULL,
  `email_check` tinyint(1) NOT NULL DEFAULT '0',
  `ctime` int(10) NOT NULL,
  `teacher_or_student` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=undefined, 1=teacher, 2=student',
  `userinfo` text NOT NULL COMMENT 'serialized teacher/student info',
  `img` varchar(50) NOT NULL,
  `lastlogin` int(11) NOT NULL COMMENT 'last login time',
  `score` int(11) NOT NULL COMMENT 'points',
  `ifprice` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=unpaid, 1=paid',
  `end_time` int(11) DEFAULT NULL COMMENT 'paid subscription expiry',
  `ip` varchar(20) NOT NULL,
  `lg_cnt` int(11) NOT NULL,
  `is_nju` int(1) NOT NULL DEFAULT '0',
  `nju_user_id` int(11) NOT NULL,
  `renzheng` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'verified',
  `pay` float(8,2) NOT NULL COMMENT 'payment amount',
  `tj_name` varchar(60) NOT NULL COMMENT 'referrer',
  `gt_time` int(10) NOT NULL,
  `tz_type` int(2) NOT NULL COMMENT 'notification method',
  `is_del` tinyint(1) NOT NULL COMMENT '1=deleted, 0=active',
  `uc_user_id` int(11) NOT NULL COMMENT 'uc_center user id',
  `school_type` tinyint(1) NOT NULL DEFAULT '0',
  `school_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `email` (`email`),
  KEY `is_del` (`is_del`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `eng_essay` (
  `essay_id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0' COMMENT 'FK, essay prompt ID',
  `author_id` int(11) NOT NULL DEFAULT '0' COMMENT 'FK, essay author',
  `internal_idx` int(11) DEFAULT NULL COMMENT 'internal question number',
  `title` varchar(255) NOT NULL COMMENT 'title',
  `essay` text COMMENT 'student answer',
  `essay_html` text NOT NULL COMMENT 'user html content',
  `tm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `sent_cnt` int(6) NOT NULL DEFAULT '0' COMMENT 'sentence count',
  `token_cnt` int(6) NOT NULL DEFAULT '0' COMMENT 'token count',
  `len` int(11) NOT NULL,
  `ctime` int(10) NOT NULL,
  `stu_number` varchar(32) NOT NULL COMMENT 'student number',
  `stu_name` varchar(60) NOT NULL COMMENT 'name',
  `stu_class` varchar(60) NOT NULL COMMENT 'class',
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '-2=self-deleted, -1=teacher-deleted, 0=saved, 1=submitted, 2=teacher-graded, 3=system-graded',
  `score` float unsigned NOT NULL COMMENT 'teacher score',
  `qw_score` float NOT NULL DEFAULT '0' COMMENT 'expected score',
  `sy_score` float NOT NULL COMMENT 'system score at time of manual grading',
  `pigai` varchar(255) NOT NULL COMMENT 'teacher brief comment',
  `pigai_time` int(10) NOT NULL COMMENT 'grading time',
  `gram_score` double NOT NULL COMMENT 'authenticity score',
  `is_pigai` tinyint(1) NOT NULL DEFAULT '0',
  `version` int(4) NOT NULL DEFAULT '1',
  `cate` tinyint(4) NOT NULL DEFAULT '0',
  `src` varchar(20) NOT NULL DEFAULT '',
  `tid` int(11) NOT NULL DEFAULT '0',
  `fid` int(11) NOT NULL DEFAULT '0',
  `tag` varchar(255) NOT NULL DEFAULT '',
  `is_chang` tinyint(1) NOT NULL,
  `jianyi` text NOT NULL,
  `anly_cnt` int(6) NOT NULL,
  `mp3` varchar(10) NOT NULL,
  PRIMARY KEY (`essay_id`),
  KEY `uid` (`user_id`),
  KEY `request_id` (`request_id`),
  KEY `type` (`type`),
  KEY `request_id_2` (`request_id`,`score`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE `eng_essay_request` (
  `request_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'prompt ID',
  `internal_idx` smallint(6) DEFAULT '0',
  `essay_title` varchar(255) DEFAULT NULL,
  `essay_topic` text,
  `essay_level` varchar(20) DEFAULT NULL,
  `essay_type` varchar(10) DEFAULT NULL,
  `src` varchar(50) DEFAULT NULL,
  `tags` varchar(100) DEFAULT NULL,
  `grade` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `valid_begin_time` int(11) DEFAULT NULL,
  `valid_end_time` int(11) DEFAULT NULL,
  `tm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `teacher_name` varchar(30) NOT NULL,
  `essay_cnt` int(6) NOT NULL DEFAULT '0',
  `type` tinyint(1) NOT NULL DEFAULT '0',
  `gram_score` double NOT NULL,
  `is_zpigai` tinyint(1) NOT NULL DEFAULT '0',
  `did` int(11) NOT NULL,
  `qw_avg` int(4) NOT NULL,
  `qw_hight` int(4) NOT NULL,
  `qw_low` int(4) NOT NULL,
  `is_qw` int(1) NOT NULL,
  `cat_id` int(4) NOT NULL,
  `version` tinyint(1) NOT NULL,
  `img` varchar(200) NOT NULL,
  `is_chang` tinyint(1) NOT NULL,
  `manfen` float(5,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`request_id`),
  KEY `user_id` (`user_id`),
  KEY `create_time` (`create_time`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE `eng_rq_ying` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_request_id` int(10) NOT NULL COMMENT 'old request_id',
  `request_id` int(10) NOT NULL COMMENT 'new request_id',
  `ctime` int(10) NOT NULL,
  `type` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_id` (`request_id`),
  KEY `f_request_id` (`f_request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


alter table member_info  discard tablespace;
alter table member_info import tablespace;

alter table member  discard tablespace;
alter table member import tablespace;

alter table eng_essay  discard tablespace;
alter table eng_essay import tablespace;

alter table eng_essay_request  discard tablespace;
alter table eng_essay_request import tablespace;

alter table eng_rq_ying  discard tablespace;
alter table eng_rq_ying import tablespace;

alter table eng_essay_attr  discard tablespace;
alter table eng_essay_attr import tablespace;
[mysqld]
init_connect='SET NAMES utf8'
character_set_server=utf8
 
[client]
default-character-set=utf8 


CREATE TABLE `member` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `password` char(32) NOT NULL,
  `email` char(100) NOT NULL,
  `user_name` char(100) NOT NULL,
  `email_check` tinyint(1) NOT NULL DEFAULT '0',
  `ctime` int(10) NOT NULL,
  `teacher_or_student` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=undefined, 1=teacher, 2=student',
  `userinfo` text NOT NULL COMMENT 'serialized teacher/student info',
  `img` varchar(50) NOT NULL,
  `lastlogin` int(11) NOT NULL COMMENT 'last login time',
  `score` int(11) NOT NULL COMMENT 'points',
  `ifprice` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=unpaid, 1=paid',
  `end_time` int(11) DEFAULT NULL COMMENT 'paid subscription expiry',
  `ip` varchar(20) NOT NULL,
  `lg_cnt` int(11) NOT NULL,
  `is_nju` int(1) NOT NULL DEFAULT '0',
  `nju_user_id` int(11) NOT NULL,
  `renzheng` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'verified',
  `pay` float(8,2) NOT NULL COMMENT 'payment amount',
  `tj_name` varchar(60) NOT NULL COMMENT 'referrer',
  `gt_time` int(10) NOT NULL,
  `tz_type` int(2) NOT NULL COMMENT 'notification method',
  `is_del` tinyint(1) NOT NULL COMMENT '1=deleted, 0=active',
  `uc_user_id` int(11) NOT NULL COMMENT 'uc_center user id',
  `school_type` tinyint(1) NOT NULL DEFAULT '0',
  `school_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `email` (`email`),
  KEY `is_del` (`is_del`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `eng_essay` (
  `essay_id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0' COMMENT 'FK, essay prompt ID',
  `author_id` int(11) NOT NULL DEFAULT '0' COMMENT 'FK, essay author',
  `internal_idx` int(11) DEFAULT NULL COMMENT 'internal question number',
  `title` varchar(255) NOT NULL COMMENT 'title',
  `essay` text COMMENT 'student answer',
  `essay_html` text NOT NULL COMMENT 'user html content',
  `tm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `sent_cnt` int(6) NOT NULL DEFAULT '0' COMMENT 'sentence count',
  `token_cnt` int(6) NOT NULL DEFAULT '0' COMMENT 'token count',
  `len` int(11) NOT NULL,
  `ctime` int(10) NOT NULL,
  `stu_number` varchar(32) NOT NULL COMMENT 'student number',
  `stu_name` varchar(60) NOT NULL COMMENT 'name',
  `stu_class` varchar(60) NOT NULL COMMENT 'class',
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '-2=self-deleted, -1=teacher-deleted, 0=saved, 1=submitted, 2=teacher-graded, 3=system-graded',
  `score` float unsigned NOT NULL COMMENT 'teacher score',
  `qw_score` float NOT NULL DEFAULT '0' COMMENT 'expected score',
  `sy_score` float NOT NULL COMMENT 'system score at time of manual grading',
  `pigai` varchar(255) NOT NULL COMMENT 'teacher brief comment',
  `pigai_time` int(10) NOT NULL COMMENT 'grading time',
  `gram_score` double NOT NULL COMMENT 'authenticity score',
  `is_pigai` tinyint(1) NOT NULL DEFAULT '0',
  `version` int(4) NOT NULL DEFAULT '1',
  `cate` tinyint(4) NOT NULL DEFAULT '0',
  `src` varchar(20) NOT NULL DEFAULT '',
  `tid` int(11) NOT NULL DEFAULT '0',
  `fid` int(11) NOT NULL DEFAULT '0',
  `tag` varchar(255) NOT NULL DEFAULT '',
  `is_chang` tinyint(1) NOT NULL,
  `jianyi` text NOT NULL,
  `anly_cnt` int(6) NOT NULL,
  `mp3` varchar(10) NOT NULL,
  PRIMARY KEY (`essay_id`),
  KEY `uid` (`user_id`),
  KEY `request_id` (`request_id`),
  KEY `type` (`type`),
  KEY `request_id_2` (`request_id`,`score`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE `eng_essay_request` (
  `request_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'prompt ID',
  `internal_idx` smallint(6) DEFAULT '0',
  `essay_title` varchar(255) DEFAULT NULL,
  `essay_topic` text,
  `essay_level` varchar(20) DEFAULT NULL,
  `essay_type` varchar(10) DEFAULT NULL,
  `src` varchar(50) DEFAULT NULL,
  `tags` varchar(100) DEFAULT NULL,
  `grade` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `valid_begin_time` int(11) DEFAULT NULL,
  `valid_end_time` int(11) DEFAULT NULL,
  `tm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `teacher_name` varchar(30) NOT NULL,
  `essay_cnt` int(6) NOT NULL DEFAULT '0',
  `type` tinyint(1) NOT NULL DEFAULT '0',
  `gram_score` double NOT NULL,
  `is_zpigai` tinyint(1) NOT NULL DEFAULT '0',
  `did` int(11) NOT NULL,
  `qw_avg` int(4) NOT NULL,
  `qw_hight` int(4) NOT NULL,
  `qw_low` int(4) NOT NULL,
  `is_qw` int(1) NOT NULL,
  `cat_id` int(4) NOT NULL,
  `version` tinyint(1) NOT NULL,
  `img` varchar(200) NOT NULL,
  `is_chang` tinyint(1) NOT NULL,
  `manfen` float(5,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`request_id`),
  KEY `user_id` (`user_id`),
  KEY `create_time` (`create_time`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE `eng_rq_ying` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_request_id` int(10) NOT NULL COMMENT 'old request_id',
  `request_id` int(10) NOT NULL COMMENT 'new request_id',
  `ctime` int(10) NOT NULL,
  `type` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_id` (`request_id`),
  KEY `f_request_id` (`f_request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


alter table member_info  discard tablespace;
alter table member_info import tablespace;

alter table member  discard tablespace;
alter table member import tablespace;

alter table eng_essay  discard tablespace;
alter table eng_essay import tablespace;

alter table eng_essay_request  discard tablespace;
alter table eng_essay_request import tablespace;

alter table eng_rq_ying  discard tablespace;
alter table eng_rq_ying import tablespace;

alter table eng_essay_attr  discard tablespace;
alter table eng_essay_attr import tablespace;

Tables in pigai_org:

+---------------------+
| Tables_in_pigai_org |
+---------------------+
| eng_essay           |
| eng_essay_attr      |
| eng_essay_request   |
| eng_rq_cat          |
| eng_rq_ying         |
| member              |
| member_info         |
| school              |
| user_invite         |
+---------------------+
9 rows in set (0.00 sec)
+---------------------+
| Tables_in_pigai_org |
+---------------------+
| eng_essay           |
| eng_essay_attr      |
| eng_essay_request   |
| eng_rq_cat          |
| eng_rq_ying         |
| member              |
| member_info         |
| school              |
| user_invite         |
+---------------------+
9 rows in set (0.00 sec)