mysql 工作笔记记录:

创建数据库


创建数据库
mysql> create DATABASE web_data;
Query OK, 1 row affected (0.00 sec)


mysql> use web_data;

mysql> create table web_count(
    -> id int auto_increment primary key,
    -> app_pv int,
    -> app_uv int,
    -> ip_list VARCHAR(15))ENGINE=InnoDB  DEFAULT charset=utf8;

mysql> desc web_count;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| app_pv      | int(11)     | YES  |     | NULL    |                |
| app_uv      | int(11)     | YES  |     | NULL    |                |
| app_ip_list | varchar(15) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> 


添加字段
alter  table  表名 add 字段名 字段类型
mysql> alter table web_count  add code404 int, add code408 int, add code403 int, add code500 int, add code501 int, add code502 int, add code503 innt, add code413 int;


查看表结构
mysql> desc web_count;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| app_pv      | int(11)     | YES  |     | NULL    |                |
| app_uv      | int(11)     | YES  |     | NULL    |                |
| app_ip_list | varchar(15) | YES  |     | NULL    |                |
| code400     | int(11)     | YES  |     | NULL    |                |
| code404     | int(11)     | YES  |     | NULL    |                |
| code408     | int(11)     | YES  |     | NULL    |                |
| code403     | int(11)     | YES  |     | NULL    |                |
| code500     | int(11)     | YES  |     | NULL    |                |
| code501     | int(11)     | YES  |     | NULL    |                |
| code502     | int(11)     | YES  |     | NULL    |                |
| code503     | int(11)     | YES  |     | NULL    |                |
| code413     | int(11)     | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

mysql> 

创建分级url表
mysql> create table appstatic_count(
    -> id int auto_increment primary key,
    -> appstatic_pv int,
    -> appstatic_uv int,
    -> app_ip_list varchar(15),
    -> code400 int,
    -> code404 int,
    -> code408 int,
    -> code403 int,
    -> code501 int,
    -> code503 int,
    -> code502 int,
    -> code413 int)ENGINE=InnoDB  DEFAULT charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> desc appstatic_count;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| appstatic_pv | int(11)     | YES  |     | NULL    |                |
| appstatic_uv | int(11)     | YES  |     | NULL    |                |
| app_ip_list  | varchar(15) | YES  |     | NULL    |                |
| code400      | int(11)     | YES  |     | NULL    |                |
| code404      | int(11)     | YES  |     | NULL    |                |
| code408      | int(11)     | YES  |     | NULL    |                |
| code403      | int(11)     | YES  |     | NULL    |                |
| code501      | int(11)     | YES  |     | NULL    |                |
| code503      | int(11)     | YES  |     | NULL    |                |
| code502      | int(11)     | YES  |     | NULL    |                |
| code413      | int(11)     | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

修改表字段及类型
mysql> alter table appstatic_count change app_ip_list appstatic_list varchar(15);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0



创建数据库
mysql> CREATE USER 'pvuser'@'10.46.77.105' IDENTIFIED BY 'bdyw201805';
Query OK, 0 rows affected (0.00 sec)


授权pvuser用户 对web_data 数据库访问权限
mysql> GRANT SELECT,insert,update ON web_data.* TO 'pvuser'@'10.46.77.105';
Query OK, 0 rows affected (0.00 sec)

mysql 5.7授权
授权pvmysql 远程访问权限
mysql> grant all privileges on web_data to pvuser@60.191.113.74 identified by 'pawssword';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> grant all privileges on https_data to pvuser@60.191.113.74 identified by 'pawssword';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)



刷新权限
mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

修改时间类型为自动插入当前时间
alter table appcs_count change mdate `CURRENT_TIME` timestamp not null default current_timestamp;

shell mysql插入数据

yesterday_time=$(date -d '-1 day' +%Y_%m_%d)
SQLserver_ip="ip"
server_port="3306"
userlogin="pvuser"
password="xxxxxxxxxxx"
DBname="web_data"


app404=`cat "$app$yesterday_time"_access.log|awk '{print $9}'|grep -w "404"|wc -l`
app_wc=`cat "$app$yesterday_time"_access.log |awk '{print $1}'|wc -l`
app_list=`cat "$app$yesterday_time"_access.log |awk '{print $1}'`


sql_data="insert into app_count(app_pv,code404) value($app_wc,$app404)"
mysql -h${SQLserver_ip} -P${server_port} -u${userlogin} -p${password} ${DBname} -e "${sql_data}"


打赏作者

Leave a Reply

Your email address will not be published.