mysql                                             

# apt-get install mysql-server mysql-client php5-mysql


Durante a instalação será pedido uma senha, digite: 123x


---------------------------------------------------------------------------------------------------------

# mysql -u root -p
Enter password: 123x

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> create database banco_dados;
Query OK, 1 row affected (0.00 sec)

mysql> use banco_dados;
Database changed


Digite as linhas abaixo para cirar a tabela amigos:
Type the below lines to create the table ( tabela_amigos )... or copy and paste it ;)


mysql> create table amigos(
     codigo integer not null,
     nome varchar(50) not null,
     endereco varchar(60),
     cidade varchar(40),
     datanasc date,
     primary key(codigo));


Após digitado as linhas acima, veja abaixo como ficou:

mysql> create table amigos(
    -> codigo integer not null,
    -> nome varchar(50) not null,
    -> endereco varchar(60),
    -> cidade varchar(40),
    -> datanasc date,
    -> primary key(codigo));
Query OK, 0 rows affected (0.01 sec)



Vamos inserir alguns registros:
Let's insert a couple of records:

insert into amigos values(1,'Jurandir','Rua Londres 13','Timbo','3382-3932');
insert into amigos values(2,'Mike','Rua USA 131','Timbo','3382-0000');
insert into amigos values(3,'Eric','Rua Italia 131','Blumenau','3382-0000');
insert into amigos values(4,'Isabela','Rua Italia 131','Blumenau','3382-0000');
insert into amigos values(5,'Rubia','Rua Italia 131','Blumenau','3382-0000');


Após digitado as linhas acima, veja abaixo como ficou:


mysql> insert into amigos values(1,'Jurandir','Rua Londres 13','Timbo','3382-3932');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into amigos values(2,'Mike','Rua USA 131','Timbo','3382-3132');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into amigos values(3,'Eric','Rua Italia 131','Blumenau','3382-3232 ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into amigos values(4,'Isabela','Rua Italia 131','Blumenau','3382-3232');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into amigos values(5,'Rubia','Rua Italia 131','Blumenau','3382-3232');
Query OK, 1 row affected, 1 warning (0.00 sec)



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


mysql> use banco_dados;
Database changed

mysql> describe amigos;
+----------+--------------+-------+-----+---------+--------+
| Field    | Type         | Null  | Key | Default | Extra  |
+----------+--------------+-------+-----+---------+--------+
| codigo   | int(11)      | NO    | PRI | NULL    |        |
| nome     | varchar(50)  | NO    |     | NULL    |        |
| endereco | varchar(60)  | YES   |     | NULL    |        |
| cidade   | varchar(40)  | YES   |     | NULL    |        |
| datanasc | date         | YES   |     | NULL    |        |
+----------+--------------+-------+-----+---------+--------+
5 rows in set (0.00 sec)


mysql> SELECT * FROM amigos;
+--------+----------+----------------+----------+------------+
| codigo | nome     | endereco       | cidade   | datanasc   | 
+--------+----------+----------------+----------+------------+
|   1    | Jurandir | Rua Londres 13 | Timbo    | 0000-00-00 |
|   2    | Mike     | Rua USA 131    | Timbo    | 0000-00-00 |
|   3    | Eric     | Rua Italia 131 | Blumenau | 0000-00-00 |
|   4    | Isabela  | Rua Italia 131 | Blumenau | 0000-00-00 |
|   5    | Rubia    | Rua Italia 131 | Blumenau | 0000-00-00 |
+--------+----------+----------------+----------+------------+
5 rows in set (0.00 sec)3 rows in set (0.00 sec)


mysql> SELECT * FROM amigos WHERE nome LIKE "mi%";
+--------+---------+---------------+---------+---------------+
| codigo | nome    | endereco      | cidade  | datanasc      |
+--------+---------+---------------+---------+---------------+
|     2  | Mike    | Rua USA 131   | Timbo   | 0000-00-00    |
+--------+---------+---------------+---------+---------------+
1 row in set (0.00 sec)


mysql> SELECT * FROM amigos WHERE cidade =  "Blumenau";
+--------+--------+----------------+-----------+-------------+
| código | nome   | endereco       | cidade    | datanasc    |
+--------+--------+----------------+-----------+-------------+
|    3   | Eric   | Rua Italia 131 | Blumenau  | 0000-00-00  |
|    4   | Isabela| Rua Italia 131 | Blumenau  | 0000-00-00  |
+--------+--------+----------------+-----------+-------------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM amigos WHERE codigo BETWEEN 1 and 2;
+--------+----------+----------------+---------+-------------+
| codigo | nome     | endereco       | cidade  | datanasc    |
+--------+----------+----------------+---------+-------------+
|    1   | Jurandir | Rua Londres 13 | Timbo   | 0000-00-00  |
|    2   | Mike     | Rua USA 131    | Timbo   | 0000-00-00  |
+--------+----------+----------------+---------+-------------+
2 rows in set (0.00 sec)


mysql> UPDATE amigos SET datanasc='20101225' WHERE codigo=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT * FROM amigos;
+--------+----------+---------------+-----------+------------+
| codigo | nome     | endereco      | cidade    | datanasc   |
+--------+----------+---------------+-----------+------------+
|   1    | Jurandir | Rua Londres 13| Timbo     | 2010-12-25 |
|   2    | Mike     | Rua USA 131   | Timbo     | 0000-00-00 |
|   3    | Eric     | Rua Italia 131| Blumenau  | 0000-00-00 |
|   4    | Isabela  | Rua Italia 131| Blumenau  | 0000-00-00 |
+--------+----------+---------------+-----------+------------+
mysql>
mysql> quit   ( para sair do mysql )

---------------------------------------------------------------------------------------------------------

# drop database banco_teste; ( removendo banco de dados )

# drop database amigos;         ( removendo tabela )



No prompt do mysql (mysql> ) use ctrl l para limpar a tela.


quit ou ctrl c ( sai do mysql )



# /etc/init.d/mysql stop

# /etc/init.d/mysql start

# /etc/init.d/mysql restart


---------------------------------------------------------------------------------------------------------

# netstat -tap | grep mysql
tcp        0      0 localhost:mysql   *:*   LISTEN    6135/mysqld


---------------------------------------------------------------------------------------------------------

# ps ax | grep mysql

 6024 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
 6135 ?        Sl     0:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --
file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
 6136 ?        S      0:00 logger -t mysqld -p daemon.error
 6255 pts/0    T      0:00 mysql -u root -p

---------------------------------------------------------------------------------------------------------

Fazendo backup das tabelas do mysql

Backing up mysql tables


# mkdir mysql_bk

# mysqldump --opt --all-databases -u root -p1234= | tar -zcf all_mysql_database-`date +%d-%m-%y-%a-%h`.tar.gz  mysql_bk/



# ls mysql_bk/


Visualizando conteúdo do arquivo.

# tar -tvf todos.tar.gz


# du -sch all_mysql_databases-2012-04-29.sql.gz
176K    all_mysql_databases-2012-04-29.sql.gz
176K    total

---------------------------------------------------------------------------------------------------------

# tail -f /var/log/mysql.log
                 2105 Query     PREPARE mdb2_statement_mysql_38bb0284b3d7ae8 FROM 'UPDATE session SET changed=\'2013-01-24 00:26:50\' WHERE sess_id=?'
                 2105 Prepare   UPDATE session SET changed='2013-01-24 00:26:50' WHERE sess_id=?
                 2105 Query     SET @0 = '0s446tfhiv2rthvj452kc3vnr4'
                 2105 Query     EXECUTE mdb2_statement_mysql_38bb02845a508b4663afbc2a9bef5d6225f3d7ae8 USING @0
                 2105 Execute   UPDATE session SET changed='2013-01-24 00:26:50' WHERE sess_id='0s446tfhiv2rthvj452kc3vnr4'
                 2105 Query     DEALLOCATE PREPARE mdb2_statement_mysql_38bb02845a508b4663afbc2a9bef5d6225f3d7ae8
                 2105 Quit
130124  0:27:01  2106 Connect   root@localhost on
                 2106 Query     select version()
                 2106 Quit
130124  0:27:50  2107 Connect   webmail_user@localhost on
                 2107 Init DB   webmail
                 2107 Query     SET NAMES utf8
                 2107 Query     PREPARE mdb2_statement_mysql_1289b0d2c2aaf31ba20d17360dcc446db5c30b58d FROM 'SELECT vars, ip, changed FROM session WHERE sess_id = ?'
                 2107 Prepare   SELECT vars, ip, changed FROM session WHERE sess_id = ?
                 2107 Query     SET @0 = '0s446tfhiv2rthvj452kc3vnr4'
                 2107 Query     EXECUTE mdb2_statement_mysql_1289b0d2c2aaf31ba20d17360dcc446db5c30b58d USING @0
                 2107 Execute   SELECT vars, ip, changed FROM session WHERE sess_id = '0s446tfhiv2rthvj452kc3vnr4'
                 2107 Query     DEALLOCATE PREPARE mdb2_statement_mysql_1289b0d2c2aaf31ba20d17360dcc446db5c30b58d
                 2107 Query     PREPARE mdb2_statement_mysql_28f847f7be0e4f33d0a508d1170eb12bf21d945bd FROM 'SELECT * FROM users WHERE user_id = ?'
                 2107 Prepare   SELECT * FROM users WHERE user_id = ?
                 2107 Query     SET @0 = '2'
                 2107 Query     EXECUTE mdb2_statement_mysql_28f847f7be0e4f33d0a508d1170eb12bf21d945bd USING @0
                 2107 Query     DEALLOCATE PREPARE mdb2_statement_mysql_28f847f7be0e4f33d0a508d1170eb12bf21d945bd
                 1125 Query     SELECT username, password FROM mailbox WHERE username='jurandir@juralinux.com.br' AND active=1
                 1125 Query     SELECT maildir FROM mailbox WHERE username = 'jurandir@juralinux.com.br' AND active = 1
                 2107 Quit
130124  0:28:02  2108 Connect   root@localhost on
                 2108 Query     select version()
                 2108 Quit

---------------------------------------------------------------------------------------------------------

Monitorar mysql

#apt-get install mysql 

Crie o arquivo abaixo.

# cat > ~/.mytop

 user=root

 pass=12345=

 host=localhost

 db=my_tables

 delay=5

 port=3306

 socket=

 batchmode=0

 header=1

 color=1

 idle=1

ctrl + d ( para salvar e sair do arquivo )

# mysql

MySQL on localhost (5.1.66-0+squeeze1-log)              up 1+05:22:19 [00:19:33]

 Queries: 103.0   qps:    0 Slow:     0.0         Se/In/Up/De(%):    291/00/00/00

              qps now:    0 Slow qps: 0.0  Threads:    2 (   1/   2) 00/00/00/00

 Key Efficiency: 80.0%  Bps in/out:   0.0/  3.7   Now in/out:   8.4/ 1.5k

        Id      User         Host/IP         DB               Time    Cmd Query or State

        --      ----         -------          --                  ----    --- --------------

      2072    root       localhost        my_tables          0     Query show full processlist

      1125    root localhost:49507    postfixadm        43    Sleep

----------------------------------------------------------------------------------------------------------------------------------------

===[ Sharing Knowledge ]===   -  Obrigado - Thank you - Danke - Merci - Grazie - Gracias - arigatou gozaimasu
“We make a living by what we get, but we make a life by what we give.”  - Give and you will receive - just share :)
-----------------------------------------------------------------------------------------------------------------------------------------