Difference between revisions of "SRA840 Lab7"

From CDOT Wiki
Jump to: navigation, search
(Milton)
Line 57: Line 57:
 
10. Which MySQL log file, in your opinion, is important for you to keep track of and why?
 
10. Which MySQL log file, in your opinion, is important for you to keep track of and why?
 
  --log, --log-error and --log-warnings
 
  --log, --log-error and --log-warnings
 +
 +
=Nestor=
 +
==Show SQL commands that work on MySQL for:==
 +
 +
===give and remove user rights===
 +
 +
====give/grant user right====
 +
mysql> grant all on wordpress.* to wordpress@localhost identified by 'dumpling';
 +
 +
====remove user right====
 +
mysql> flush privileges;
 +
 +
=== create a table===
 +
mysql> create table ''table_foo'' (rec1 char(9));
 +
 +
===insert a few records into the table you created===
 +
mysql> insert into ''table_foo'' values('record1');
 +
mysql> insert into ''table_foo'' values('record2');
 +
mysql> insert into ''table_foo'' values('record3');
 +
 +
===select some of the records in the table you created===
 +
mysql> select rec1 from ''table_foo'';
 +
 +
===change some of the records into the table you created===
 +
mysql> update ''table_foo'' set name='record0' where rec1='record2';
 +
 +
===delete records from the table you created===
 +
mysql> delete from ''table_foo'' where rec1='record3';
 +
 +
===How will you read records from in a plain text file into MySQL. Give an example.===
 +
 +
=====Exporting records=====
 +
mysql> select * into outfile '/tmp/mysql_test/table_foo.txt' fields terminated by '\t' lines terminated by '\n' from ''table_fo'';
 +
 +
=====Importing records =====
 +
mysql> load data infile '/tmp/mysql_test/table_foo.txt' into table ''table_foo2'' fields terminated by '\t' lines terminated by '\n';
 +
 +
 +
===What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL. Assume you have a default installation of Apache and a default installation of MySQL.===
 +
It's just like how we do it for wordpress. First, make sure apache and mysql are all installed and running properbly. Then, create a database and table, also create the fileds that we'll need, e.g: hour, minutes, status, etc. Then it's the tricky part, we have to extra the apache log file's lines into different filed, so that mysql can read it. You can acheive it with 'sed' or some other command or even script. We actually have a python script from Danny's assignment that has simular function. We could adjust the script to acheive the task. then, we can import the text file with mysql command 'load data infile'.
 +
 +
===Name two security measures that MySQL recommmends===
 +
1.always have set a password
 +
2.use a long password with combination of characters and numbers
 +
 +
===Which MySQL log file, in your opinion, is important for you to keep track of and why?===
 +
In my opintion, i think The General Query Log is the most important. Assuming the database has already installed and running properly. The most critical things is data leaking.
 +
It is really important, because you do not want any random people to the record. The database may contain some sensitive records, e.g: VISA numbers, address.
 +
 +
(ref: http://dev.mysql.com/doc/refman/5.0/en/server-logs.html)
 +
 +
===As you observe from the tutorials, many users have added their own comments in addition to the MySQL material. Which two comments (one from the using tutorial and one from the administration tutorial) you think were useful to you.===
 +
 +
====Tut====
 +
It fill in a lot of gap that the tutorial didn't cover.
 +
http://dev.mysql.com/doc/refman/4.1/en/database-use.html
 +
 +
====Admin====
 +
A lot of experience sharing
 +
http://dev.mysql.com/doc/refman/5.0/en/query-log.html.

Revision as of 18:55, 11 April 2009

Milton

1. give and remove user rights

1.1 Give privileges:

mysql> grant all on wordpress.* to wordpress@localhost identified by 'password';

1.2 Remove privileges:

mysql> flush privileges;

2. create a table

mysql> create table table_name (name char(20));

3. insert a few records into the table you created

mysql> insert into table_name values('milton');

4. select some of the records in the table you created

mysql> select name from table_name;

5. change some of the records into the table you created

mysql> update table_name set name='johndoe' where name='milton';

6. delete records from the table you created

mysql> delete from table_name where name='johndoe';

7. How will you read records from in a plain text file into MySQL. Give an example.

Copy records to a plain text file:

mysql> select * into outfile '/tmp/database.txt' fields terminated by '\t' lines terminated by '\n' from table_name;

Import records from a plain text file:

mysql> load data infile '/tmp/database.txt' into table table_name fields terminated by '\t' lines terminated by '\n';

8. What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL. Assume you have a default installation of Apache and a default installation of MySQL.

8.1 Create a database

8.2 Create a table with the fields I will need

8.3 Split the apache log file to a temporary file then mysql can recognize this file

8.4 Import this plain text file into the database

8.5 Create a shell script to keep updating the text file

9. Name two security measures that MySQL recommmends

  • Encrypt the passwords
  • Add an password for the default system users like root and mysql.

10. Which MySQL log file, in your opinion, is important for you to keep track of and why?

--log, --log-error and --log-warnings

Nestor

Show SQL commands that work on MySQL for:

give and remove user rights

give/grant user right

mysql> grant all on wordpress.* to wordpress@localhost identified by 'dumpling';

remove user right

mysql> flush privileges;

create a table

mysql> create table table_foo (rec1 char(9));

insert a few records into the table you created

mysql> insert into table_foo values('record1');
mysql> insert into table_foo values('record2');
mysql> insert into table_foo values('record3');

select some of the records in the table you created

mysql> select rec1 from table_foo;

change some of the records into the table you created

mysql> update table_foo set name='record0' where rec1='record2';

delete records from the table you created

mysql> delete from table_foo where rec1='record3';

How will you read records from in a plain text file into MySQL. Give an example.

Exporting records
mysql> select * into outfile '/tmp/mysql_test/table_foo.txt' fields terminated by '\t' lines terminated by '\n' from table_fo;
Importing records
mysql> load data infile '/tmp/mysql_test/table_foo.txt' into table table_foo2 fields terminated by '\t' lines terminated by '\n';


What are the exact sequence of steps that you need to take so that Apache logs can be read in from MySQL. Assume you have a default installation of Apache and a default installation of MySQL.

It's just like how we do it for wordpress. First, make sure apache and mysql are all installed and running properbly. Then, create a database and table, also create the fileds that we'll need, e.g: hour, minutes, status, etc. Then it's the tricky part, we have to extra the apache log file's lines into different filed, so that mysql can read it. You can acheive it with 'sed' or some other command or even script. We actually have a python script from Danny's assignment that has simular function. We could adjust the script to acheive the task. then, we can import the text file with mysql command 'load data infile'.

Name two security measures that MySQL recommmends

1.always have set a password
2.use a long password with combination of characters and numbers

Which MySQL log file, in your opinion, is important for you to keep track of and why?

In my opintion, i think The General Query Log is the most important. Assuming the database has already installed and running properly. The most critical things is data leaking. 
It is really important, because you do not want any random people to the record. The database may contain some sensitive records, e.g: VISA numbers, address. 

(ref: http://dev.mysql.com/doc/refman/5.0/en/server-logs.html)

As you observe from the tutorials, many users have added their own comments in addition to the MySQL material. Which two comments (one from the using tutorial and one from the administration tutorial) you think were useful to you.

Tut

It fill in a lot of gap that the tutorial didn't cover.
http://dev.mysql.com/doc/refman/4.1/en/database-use.html

Admin

A lot of experience sharing
http://dev.mysql.com/doc/refman/5.0/en/query-log.html.