domingo, 12 de agosto de 2012

MySQL 5.5.x - faq, technical information, command sintax, tips, etc

1. Introduction

This post should answer Top 80 % answers, frequently forgotten question, technical information, commands sintax and tips about MySQL. Here are index.


1. Introduction
2. MySQL 5.5.x FFQ ( Frequently Forgotten Questions)
2.1. What are MySQL possibles Table Engine Types? What are advantages and disadvantages of each one?
2.2. What are majors differences between MySQL editions?
2.3. What are majors MySQL table columns datatype? What are differences between MySQL and Oracle datatypes?
2.4. Complete create sample tables ( primary key, foreign key, auto_increment, character set, colate, indexes, inserts, etc)
2.5. Select samples ( simple joins, inner, left join, right join and differences for who are used with Oracle)
2.5.1. Join for people that are used with Oracle and Inner Join Sintax
2.5.2. Left Join and Right Join ( sintax for who are used with Oracle (+) )
2.6. Table Column DEFAULT qualifier
2.7. Create MySQL database stored USER FUNCTION
2.8. Create and call execution of MySQL stored PROCEDURE
2.9. Create MySQL table TRIGGER
2.10. How to get sequence recently generated LAST_INSERT_ID( )
2.11. Aggregation functions AVG( ), COUNT( ), GROUP_CONCAT( ), MAX( ), MIN( ) and SUM( )
2.12. Some built-in FUNCTIONS on MySQL
2.13. Using differents database, pseudo databases "information_schema" and "mysql"
2.14. User and Privileges administrations
2.15. MySql configuration file 



2. MySQL 5.5.x FFQ ( Frequently Forgotten Questions)

2.1. What are MySQL possibles Table Engine Types? What are advantages and disadvantages of each one?

  • MyISAM
    • advantages
      • faster
      • simple backup
      • search as a text on datafiles
    • disadvantages
      • no Referential Integrity
      • no Transaction oriented
      • lock mode: tables
      • fragmentation
  • InnoDB
    • advantages
      • Referential Integrity
      • Transaction oriented
      • lock mode: records
    • disadvantages
      • slower for much data (than MyIsan)
      • data is not available for search as text
  • Memory
    • advantages
      • fastest
    • disadvantages
      • no Referential Integrity
      • cleanned up on boot
      • no Transaction oriented
      • lock mode: tables
  • Federated
    • advantages
      • remote tables without replication process
      • distributed data
    • disadvantages
      • complex backup
  • Archive
    • advantages
      • archive method like "log"
      • compressed data
    • disadvantages
      • update/delete not allowed
  • CSV
    • advantages
      • easy data exchange
    • disadvantages
      • "null" information is not interpreted
      • no Referential Integrity
  • BlackHole
    • advantages
      • easy to model and prototype
    • disadvantages
      • data is not saved(like/dev/null)

2.2. What are majors differences between MySQL editions?

  • Community:
    • Free, without  administrative tools
  • Cluster:
    • Free without administrative tools
  • Standard:
    • +/- US$ 2k, Support 24x7, knowledge base
  • Enterprise:
    • +/- US$ 5k, Support 24x7, knowledge base, administrative tools (monitor, backup, partition)
  • Cluster CGE:
    • +/- US$ 10k, Support 24x7, knowledge base, administrative tools (cluster manager, GEO replication, monitor, backup, partition)

2.3. What are majors MySQL table columns datatype? What are differences between MySQL and Oracle datatypes?

  • Integers: Positive and negative values integers numbers ( max value )
    • bit (64)
    • tinyint (128)
    • smallint (32768)
    • mediumint (8388608)
    • integer (2147483648)
    • bigint (9223372036854775808)
  • Fraction: Positive and negative fraction values
    • double ( ~15 decimal places )
    • fload ( ~ 7 decimal places )
  • Strings: Text and binary values
    • char / binary (256)
    • varchar / varbinary (256)
    • tinytext / tinyblob (2^8 bytes)
    • text / blob (2^16 bytes)
    • mediumtext / mediumblob (2^24 bytes)
    • longtext / logblob (2^32 bytes)
    • enum (65kb)
    • set (64 itens)
  • Time: Date, time, date/time
    • date (YYYY-MM-DD)
    • datetime (YYY-MM-DD HH:MM:SS)
    • time (HH:MM:SS)
    • timestamp (seconds after 1970)
    • year (YYYY or YY)
  • Others: Boolean
    • boolean true/false values ( 1 or 0 )
  1. MySQL varchar(n) datatype is different from Oracle varchar2(n) and MySQL char(n) datatype is different from Oracle char(n) . MySQL does not reserve a pré-alocated space for varchar(n), so if you update your data with a longer string, this can cause internal fragmentation. So, rule of thumb is use MySQL char(n) as substitute of Oracle varchar2(n).

2.4. Complete create sample tables ( primary key, foreign key, auto_increment, character set, colate, indexes, inserts, etc)

drop table if exists person;
create table person
(
  id integer    not null auto_increment, 
  name char(50) not null,
  PRIMARY KEY (id)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

drop table if exists product;
create table product
(
  id integer    not null auto_increment, 
  name char(50) not null,
  PRIMARY KEY (id)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

drop table if exists sale;
create table sale
(
  id integer   auto_increment, 
  dt_sale      date    not null,
  dt_last_updt datetime,
  person_id    integer not null,
  product_id   integer not null,
  quantity     integer not null,
  unit_value   double  not null,
  total_value  double  not null,
  is_canceled  boolean not null,
  obs          text,
  PRIMARY KEY (id)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


drop table if exists audit;
CREATE TABLE audit (
  id         mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  sale_id    integer NOT NULL,
  changetype enum('NEW','EDIT','DELETE') NOT NULL,
  changetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  audit_txt  char(255),
  PRIMARY KEY (id),
  KEY idx_sale_id (sale_id),
  KEY idx_changetype (changetype),
  KEY idx_changetime (changetime)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



alter table sale add foreign key(person_id)  references person(id);
alter table sale add foreign key(product_id) references product(id);

create index fk_sale_person  on sale(person_id);
create index fk_sale_product on sale(product_id);

insert into person ( name ) values ( 'josemar' );
insert into person ( name ) values ( 'danilo' );
insert into person ( name ) values ( 'igor' );
insert into person ( name ) values ( 'andre' );
insert into person ( name ) values ( 'alex' );
insert into person ( name ) values ( 'wellington' );

insert into product ( name ) values ( 'coca-cola' );
insert into product ( name ) values ( 'guarana' );
insert into product ( name ) values ( 'suco laranja' );
insert into product ( name ) values ( 'água' );
insert into product ( name ) values ( 'água com gas' );
insert into product ( name ) values ( 'cachaca' );
insert into product ( name ) values ( 'itubaina' );
commit;
insert into sale 
( 
  person_id,
  product_id,
  quantity,
  unit_value,
  total_value,
  dt_sale,
  is_canceled
)
values 
(
  1           /* person     */, 
  1           /* product    */,
  1           /* quantity        */,
  1           /* unit_value   */,
  1           /* total_value  */,
  '2012-08-04'/* dt_sale  */,
  0           /* is_canceled*/
);

insert into sale 
( 
  person_id,
  product_id,
  quantity,
  unit_value,
  total_value,
  dt_sale,
  is_canceled
)
values 
(
  2           /*person*/, 
  2           /*product*/,
  2           /*quantity*/,
  2           /*unit_value*/,
  4           /*total_value*/,
  '2012-08-04'/* dt_sale  */,
  0           /* is_canceled*/
);

insert into sale 
( 
  person_id,
  product_id,
  quantity,
  unit_value,
  total_value,
  dt_sale,
  is_canceled
)
values 
(
  3           /*person*/, 
  3           /*product*/,
  3           /*quantity*/,
  3           /*unit_value*/,
  9           /*total_value*/,
  '2012-08-04'/* dt_sale  */,
  0           /* is_canceled*/
);

insert into sale 
( 
  person_id,
  product_id,
  quantity,
  unit_value,
  total_value,
  dt_sale,
  is_canceled
)
values 
(
  4           /*person*/, 
  4           /*product*/,
  4           /*quantity*/,
  4           /*unit_value*/,
 16           /*total_value*/,
  '2012-08-04'/* dt_sale  */,
  0           /* is_canceled*/
);

insert into sale 
( 
  person_id,
  product_id,
  quantity,
  unit_value,
  total_value,
  dt_sale,
  is_canceled
)
values 
(
  1           /*person*/, 
  5           /*product*/,
  1           /*quantity*/,
  5           /*unit_value*/,
  5           /*total_value*/,
  '2012-08-04'/* dt_sale  */,
  0           /* is_canceled*/
);

commit;

2.5. Select samples ( simple joins, inner, left join, right join and differences for who are used with Oracle)

2.5.1. Join for people that are used with Oracle and Inner Join Sintax

select a.id sale_id,
       a.person_id,
       b.name name_person,
       a.quantity,
       a.unit_value,
       a.total_value,
       a.product_id,
       c.name       name_product
from   sale  a,
       person  b,
       product c
where  a.person_id  = b.id
and    a.product_id = c.id;

+---------+-----------+-------------+----------+------------+-------------+------------+---------------+
| sale_id | person_id | name_person | quantity | unit_value | total_value | product_id | name_product  |
+---------+-----------+-------------+----------+------------+-------------+------------+---------------+
|       1 |         1 | josemar     |        1 |          1 |           1 |          1 | coca-cola     |
|       2 |         2 | danilo      |        2 |          2 |           4 |          2 | guarana       |
|       3 |         3 | igor        |        3 |          3 |           9 |          3 | suco laranja  |
|       4 |         4 | andre       |        4 |          4 |          16 |          4 | água          |
|       5 |         1 | josemar     |        1 |          5 |           5 |          5 | água com gas  |
+---------+-----------+-------------+----------+------------+-------------+------------+---------------+
5 rows in set (0.08 sec)

/* In MySQL you should write SQL also like this */
select a.id         sale_id,
       a.person_id,
       b.name       name_person,
       a.quantity,
       a.unit_value,
       a.total_value,
       a.product_id,
       c.name       name_product
from   sale as a
INNER JOIN person as b
ON    b.id = a.person_id
INNER JOIN product as c
ON    c.id = a.product_id;

2.5.2. Left Join and Right Join ( sintax for who are used with Oracle (+) )

MySQL use sintax "Left" and "Right" to force use of all rows of table on the left side or on the right side. For Oracle users, you must use sintax "left" or "right" to force the use of all rows of the side.

SELECT  a.id person_id,
        a.name,
        b.id  sale_id,
        b.product_id,
        b.quantity,
        b.unit_value
FROM    person as a
LEFT    JOIN sale as b
ON      b.person_id = a.id
ORDER   BY a.name;


+-----------+------------+---------+------------+----------+------------+
| person_id | name       | sale_id | product_id | quantity | unit_value |
+-----------+------------+---------+------------+----------+------------+
|         5 | alex       |    NULL |       NULL |     NULL |       NULL |
|         4 | andre      |       4 |          4 |        4 |          4 |
|         2 | danilo     |       2 |          2 |        2 |          2 |
|         3 | igor       |       3 |          3 |        3 |          3 |
|         1 | josemar    |       1 |          1 |        1 |          1 |
|         1 | josemar    |       5 |          5 |        1 |          5 |
|         6 | wellington |    NULL |       NULL |     NULL |       NULL |
+-----------+------------+---------+------------+----------+------------+
7 rows in set (0.00 sec)



2.6. Table Column DEFAULT qualifier

drop table if exists test_default;
create table test_default 
(
  id integer not null auto_increment, 
  a  integer default 1, 
  b  varchar(255) default 'qualquer coisa',
  PRIMARY KEY (id)
);
insert into test_default ( id ) values ( null );
select * from test_default;

 +----+------+----------------+
| id | a    | b              |
+----+------+----------------+
|  1 |    1 | qualquer coisa |
+----+------+----------------+
1 row in set (0.00 sec)


2.7. Create MySQL database stored USER FUNCTION

The most important thing you must know for who are used with Oracle is: you must change de "delimiter", because MySQL launch interpretation when a ";" is find inside function command.

drop function pi;
delimiter |
create function pi () returns integer
begin
  return 3.1415;
end
|
delimiter ;

delimiter |
create function hello () returns varchar(255)
begin
  return concat('My','Sql');
end
|
delimiter ;

select pi() "pi()", hello() "hello()"
from   dual;

+----------+---------+
| pi()     | hello() |
+----------+---------+
| 3.141593 | MySql   |
+----------+---------+
1 row in set (0.03 sec)


2.8. Create and call execution of MySQL stored PROCEDURE

The most important thing you must know for who are used with Oracle is: you must change de "delimiter", because MySQL launch interpretation when a ";" is find inside function command.

drop procedure upd_total_value_sale;
delimiter |
create procedure upd_total_value_sale( p integer ) 
begin
  update sale
  set    total_value = quantity * unit_value;
end;
|
delimiter ;

call upd_total_value_sale( 0 );

2.9. Create MySQL table TRIGGER

The most important thing you must know for who are used with Oracle is: you must change de "delimiter", because MySQL launch interpretation when a ";" is find inside function command. You also must know that:
  • NEW reference can't be used with AFTER INSERT
  • Use prefix "SET " to set new a value
  • Use @ to create ad-hoc variable used in trigger body
  • Use command "show triggers;" to show all current database triggers;
drop trigger tr_a_er_i_sale;
delimiter |
CREATE
  TRIGGER tr_a_er_i_sale BEFORE INSERT
  ON sale
  FOR EACH ROW BEGIN
    SET @audit_txt = 'trigger tr_a_er_i_sale launched';
    set new.total_value = new.quantity * new.unit_value;
    INSERT INTO audit (sale_id, changetype, audit_txt) VALUES (NEW.id, 'NEW', @audit_txt);
  END;
|
delimiter ;
show triggers;
+----------------+--------+-------+------------
| Trigger        | Event  | Table | Statement  
+----------------+--------+-------+------------
| tr_a_er_i_sale | INSERT | sale  | BEGIN
    SET @audit_txt = 'trigger tr_a_er_i_sale la
    set new.total_value = new.quantity * new.un
    INSERT INTO audit (sale_id, changetype, aud
  END | BEFORE | NULL    |          | root@loca
+----------------+--------+-------+------------
1 row in set (0.01 sec)

insert into sale ( person_id, product_id, quantity, unit_value, total_value, dt_sale, is_canceled)
values 
(
  1           /* person     */, 
  1           /* product    */,
  6           /* quantity        */,
  2           /* unit_value   */,
  0           /* total_value  */,
  '2012-08-04'/* dt_sale  */,
  0           /* is_canceled*/
);

select * from audit;
+----+---------+------------+---------------------+---------------------------------+
| id | sale_id | changetype | changetime          | audit_txt                       |
+----+---------+------------+---------------------+---------------------------------+
|  1 |       0 | NEW        | 2012-08-12 23:11:21 | trigger tr_a_er_i_sale launched |
+----+---------+------------+---------------------+---------------------------------+
1 row in set (0.00 sec)


2.10. How to get sequence recently generated LAST_INSERT_ID( )

If you need to insert in a master table a autoincrement PK value and immediatly you need to insert detail table referencing master table PK. You should know built-in function LAST_INSERT_ID( ).
  • LAST_INSERT_ID( ) must be used immediatly to autoincrement generation;
/*
 * Insert into master table journal - sequence autoincremented
 */
INSERT INTO journals( id,  journalized_id,  journalized_type,  user_id,  notes,  created_on)
values( null /* autoinc */, 100, 'Issue', 1, 'hello', now() );
/*
 * Get last sequence incremented from master table journal
 */
SELECT last_insert_id() into @journal_id;
/*
 * Use sequence last_inserted to new insert into detail table journal_details
 */
INSERT INTO journal_details(  id, journal_id, property, prop_key, old_value, value)
values
(  null,@journal_id, 'attr', 'assigned_to_id', new_assigned_to_id, 'null');


2.11. Aggregation functions AVG( ), COUNT( ), GROUP_CONCAT( ), MAX( ), MIN( ) and SUM( )

MySQL supports standards aggregation functions. For those that are used to Oracle, the most important thing you *must known* is that MySQL permit you group by for some columns and select for others columns. If you don't know exactaly what you are doing the result set will be impredictable :-). But, the function GROUP_CONCAT( ) is cool!



SELECT  a.name,
        COUNT(b.id)  sale_id_COUNT,
        b.product_id,
        b.quantity,
        b.unit_value
FROM    person as a
LEFT    JOIN sale as b
ON      b.person_id = a.id
GROUP   BY a.name
ORDER   by a.name;
+------------+---------------+------------+----------+------------+
| name       | sale_id_COUNT | product_id | quantity | unit_value |
+------------+---------------+------------+----------+------------+
| alex       |             0 |       NULL |     NULL |       NULL |
| andre      |             1 |          4 |        4 |          4 |
| danilo     |             1 |          2 |        2 |          2 |
| igor       |             1 |          3 |        3 |          3 |
| josemar    |             3 |          1 |        1 |          1 |
| wellington |             0 |       NULL |     NULL |       NULL |
+------------+---------------+------------+----------+------------+

SELECT  a.name,
        COUNT(b.id)  sale_id_COUNT,
        SUM( b.quantity * b.unit_value) total_value_SUM
FROM    person as a
LEFT    JOIN sale as b
ON      b.person_id = a.id
GROUP   BY a.name
ORDER   by a.name;
+------------+---------------+-----------------+
| name       | sale_id_COUNT | total_value_SUM |
+------------+---------------+-----------------+
| alex       |             0 |            NULL |
| andre      |             1 |              16 |
| danilo     |             1 |               4 |
| igor       |             1 |               9 |
| josemar    |             3 |              18 |
| wellington |             0 |            NULL |
+------------+---------------+-----------------+
6 rows in set (0.00 sec)

SELECT  a.name,
        COUNT(b.id)  sale_id_COUNT,
        SUM( b.quantity * b.unit_value) total_value_SUM,
        AVG( b.quantity ) quantity_AVG,
        MAX( b.quantity) quantity_MAX,
        MIN( b.quantity) quantity_MIN
FROM    person as a
LEFT    JOIN sale as b
ON      b.person_id = a.id
GROUP   BY a.name
ORDER   by a.name;
+------------+---------------+-----------------+--------------+--------------+--------------+
| name       | sale_id_COUNT | total_value_SUM | quantity_AVG | quantity_MAX | quantity_MIN |
+------------+---------------+-----------------+--------------+--------------+--------------+
| alex       |             0 |            NULL |         NULL |         NULL |         NULL |
| andre      |             1 |              16 |       4.0000 |            4 |            4 |
| danilo     |             1 |               4 |       2.0000 |            2 |            2 |
| igor       |             1 |               9 |       3.0000 |            3 |            3 |
| josemar    |             3 |              18 |       2.6667 |            6 |            1 |
| wellington |             0 |            NULL |         NULL |         NULL |         NULL |
+------------+---------------+-----------------+--------------+--------------+--------------+
6 rows in set (0.03 sec)

SELECT  a.name,
        GROUP_CONCAT(b.id)  sale_id_GRUP_CONCAT
FROM    person as a
LEFT    JOIN sale as b
ON      b.person_id = a.id
GROUP   BY a.name
ORDER   by a.name;
+------------+---------------------+
| name       | sale_id_GRUP_CONCAT |
+------------+---------------------+
| alex       | NULL                |
| andre      | 4                   |
| danilo     | 2                   |
| igor       | 3                   |
| josemar    | 1,5,6               |
| wellington | NULL                |
+------------+---------------------+
6 rows in set (0.00 sec)



2.12. Some built-in FUNCTIONS on MySQL

MySQL supports some built-in functions.

    • Numeric MySQL built-in Functions: CEIL( ), ROUND( ), TRUNCATE( ), RAND( )
select ceil(123.4), round(123.4), truncate(123.45678,2), rand() from dual;
+-------------+--------------+-----------------------+------------------+
| ceil(123.4) | round(123.4) | truncate(123.45678,2) | rand()           |
+-------------+--------------+-----------------------+------------------+
|         124 |          123 |                123.45 | 0.13640881994405 |
+-------------+--------------+-----------------------+------------------+
1 row in set (0.01 sec)

  • String MySQL built-in Functions: CHAR_LENGTH( ), CONCAT( ), LEFT( ), RIGHT( ), LOCATE( ), LOWER( ), UPPER( ), LOWER( ), REPEAT( ), SUBSTRING( ), TRIM( ), REPLACE( )
SELECT CHAR_LENGTH( 'Entendo que nossas rugas aumentam para que nossas rusgas diminuam') as char_length_sample,
       CONCAT( 'Maria', 'Do', 'Carmo', 'Furegatti' )                                     as concat_sample,
  LEFT( 'Josemar Furegatti de Abreu Silva', 7 )                                 as left_sample, 
       RIGHT( 'Josemar Silva', 5 )                                                       as right_sample,
       LOCATE( 'crime', 'Nunca vi tão nitidamente caracterizado o crime de formação de quadrilha') as locate_sample
FROM   dual;
+--------------------+-----------------------+-------------+--------------+---------------+
| char_length_sample | concat_sample         | left_sample | right_sample | locate_sample |
+--------------------+-----------------------+-------------+--------------+---------------+
|                 65 | MariaDoCarmoFuregatti | Josemar     | Silva        |            42 |
+--------------------+-----------------------+-------------+--------------+---------------+
1 row in set (0.00 sec)

SELECT LOWER( 'MENSALAO' ) as lower_sample, 
       UPPER( 'cAiXa 2' )  as upper_sample, 
       REPEAT( 'ha', 3 )   as repeat_sample, 
       SUBSTRING( 'Oh! que saudades que tenho da aurora da minha vida', 8, 9) as substring_sample, 
       TRIM( '   Estou usando      muito bem o vernáculo    ' ) as trim_sample
FROM   dual;
+--------------+--------------+---------------+------------------+-----------------------------------------+
| lower_sample | upper_sample | repeat_sample | substring_sample | trim_sample                             |
+--------------+--------------+---------------+------------------+-----------------------------------------+
| mensalao     | CAIXA 2      | hahaha        |  saudades        | Estou usando      muito bem o vernáculo |
+--------------+--------------+---------------+------------------+-----------------------------------------+
1 row in set (0.00 sec)


SELECT REPLACE(value, ' ', '')  FROM settings WHERE name = 'issue_transition_projects_list';
+-------------------------+
| replace(value, ' ', '') |
+-------------------------+
| 0,1000,11,1,9           |
+-------------------------+
1 row in set (0.00 sec)


  • Date/Time MySQL built-in Functions: CURRENT_DATE( ), CURRENT_TIME( ), NOW( ), CURRENT_TIMESTAMP( ), DATE_ADD( ), DATE_SUB( ), DATE_FORMAT( ), DATE( ), TIME( ), DATEDIFF( ), TIMEDIFF( ), DAY( ), MONTH( ), YEAR( ), HOUR( )


SELECT CURRENT_DATE() AS "CURRENT_DATE",
       CURRENT_TIME() AS "CURRENT_TIME",
       NOW()          AS "NOW",
       DATE_ADD( '2004-10-25', INTERVAL 182 DAY ) AS "DATE_ADD",
       DATE_SUB( '2004-10-25', INTERVAL 1 DAY )   AS "DATE_SUB",
   CURRENT_DATE() + 1 AS "DATE_SUM_NUMBER"
FROM   dual;


+--------------+--------------+---------------------+------------+------------+-----------------+
| CURRENT_DATE | CURRENT_TIME | NOW                 | DATE_ADD   | DATE_SUB   | DATE_SUM_NUMBER |
+--------------+--------------+---------------------+------------+------------+-----------------+
| 2012-11-14   | 23:08:16     | 2012-11-14 23:08:16 | 2005-04-25 | 2004-10-24 |        20121115 |
+--------------+--------------+---------------------+------------+------------+-----------------+
1 row in set (0.00 sec)



SELECT DATE_FORMAT( '1969-04-25', '%d/%m/%Y' ), 
       DATE( '2004-10-25 11:45:00' ),
       TIME( '2004-10-25 11:45:00' )
FROM   dual;
+-----------------------------------------+-------------------------------+-------------------------------+
| DATE_FORMAT( '1969-04-25', '%d/%m/%Y' ) | DATE( '2004-10-25 11:45:00' ) | TIME( '2004-10-25 11:45:00' ) |
+-----------------------------------------+-------------------------------+-------------------------------+
| 25/04/1969                              | 2004-10-25                    | 11:45:00                      |
+-----------------------------------------+-------------------------------+-------------------------------+
1 row in set (0.00 sec)



SELECT NOW()                             as "NOW",
       DATEDIFF( NOW(), '2012-10-25' )   as "DATEDIFF", 
       TIMEDIFF( '2012-10-25 18:00', '2012-10-25 09:00' ) as "TIMEDIFF", 
       DAY( NOW() )                      as "DAY",
       MONTH( NOW() )                    as "MONTH",
       YEAR( NOW() )                     as "YEAR",
       HOUR( NOW() )                     as "HOUR"
FROM   dual;
+---------------------+----------+----------+------+-------+------+------+
| NOW                 | DATEDIFF | TIMEDIFF | DAY  | MONTH | YEAR | HOUR |
+---------------------+----------+----------+------+-------+------+------+
| 2012-11-14 23:18:05 |       20 | 09:00:00 |   14 |    11 | 2012 |   23 |
+---------------------+----------+----------+------+-------+------+------+
1 row in set (0.00 sec)



2.13. Using differents database, pseudo databases "information_schema" and "mysql"

MySQL has two pseudo databases to archive data-dictionary and privileges.


show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)


use information_schema;
Database changed

show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+

use mysql;
Database changed
show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
| user_info                 |
+---------------------------+
18 rows in set (0.00 sec)



2.14. User and Privileges administrations

  • The most important difference between MySQL and Oracle is about user administration. In MySQL you should create one user for each IP or SUBNET connection.
create user 'josemar'@'127.0.0.1' identified by 'josemarlocalhost';
create user 'josemar'@'%' identified by 'josemarfromanywhere';
rename user 'josemar'@'127.0.0.1' to 'josemar'@'localhost';
set password for 'josemar'@'locahost' = password('senhacriptografada');
select host, user from mysql.user order by host, user;

+--------------------------------+-----------------+

| host                           | user            |

+--------------------------------+-----------------+
| localhost                      | josemar         |
| %                              | josemar         |
+--------------------------------+-----------------+
drop user 'josemar'@'%';

  • GRANT, REVOKE and SHOW privileges
GRANT SELECT ON information_schema.* TO 'josemar'@'localhost';
GRANT ALL ON test TO 'josemar'@'localhost';
GRANT CREATE ROUTINE ON test TO 'josemar'@'localhost';
REVOKE CRATE ROUTINE ON test FROM 'josemar'@'localhost';
REVOKE ALL PRIVILEGES ON test.* FROM 'demitido'@'%';
SHOW GRANTS FOR 'josemar'@'locahost';



2.15. MySql configuration file 

  • /etc/my.conf show current configuration file for MySQL
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

  • There are others standards suggested files configuration for each propouse:
[root@SRV0010 etc]# find /usr/share/mysql -name my-*.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-large.cnf

  • But the really relevant configurations keys are
port=3306
default-character-et=utf8
datadir=/var/lib/mysql
basedir=/var/lib/mysql
default-storage-engine=INNODB
max_connections=50
query_cache_size=32M
table_cache=256
tmp_table_size=34M



  3. References



Postado por às Nenhum comentário:
Assinar: Postagens (Atom)