MySQL para DBAs Oracle

Ricardo Portilho Proniricardo@nervinformatica.com.br

Esta obra está licenciada sob a licençaCreative Commons Atribuição-SemDerivados 3.0 Brasil.

Para ver uma cópia desta licença, visite http://creativecommons.org/licenses/by-nd/3.0/br/.

1

2

Por que MySQL?

2

3

Evolução MySQL

3

Fonte: Apresentação “Novidades do Universo MySQL”, do GUOB Tech Day 2014

4

Evolução MySQL

4

Fonte: Apresentação “Novidades do Universo MySQL”, do GUOB Tech Day 2014

5

Evolução MySQL

5

Fonte: Apresentação “Novidades do Universo MySQL”, do GUOB Tech Day 2014

6

Edições

6

7

Edições

7

8

Instalação

[root@Melquior ~]# yum -y install mysql-server...[root@Melquior ~]# rpm -qa | grep mysqlmysql-server-5.1.73-3.el6_5.x86_64mysql-libs-5.1.73-3.el6_5.x86_64mysql-5.1.73-3.el6_5.x86_64[root@Melquior ~]#

8

9

Instalação

9

10

Instalação

10

11

Lab 1: Instalação

Instale o MySQL.# yum -y install wget# wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm# rpm -ivh mysql-community-release-el6-5.noarch.rpm# yum -y install mysql-server# chkconfig mysqld on# ls -lh /var/lib/mysql# service mysqld start# ls -lh /var/lib/mysql# /usr/bin/mysqladmin -u root password 'Nerv2015'

Acesse o MySQL, e verifique a versão instalada.# mysql -u root -pNerv2015mysql> exit;

Verifique o Log do MySQL.# cat /var/log/mysqld.log

Verifique o arquivo de parâmetros do MySQL.# cat /etc/my.cnf

11

12

Lab 2: Programas Cliente

# mysql -u root -pNerv2015mysql> SHOW STATUS;mysql> EXIT;

# mysql -u root -pNerv2015 -e “SHOW STATUS”

# mysql -u root -pNerv2015 -e “SHOW STATUS” > status.txt# cat status.txt

# echo “SHOW STATUS” >> script.sql# cat script.sql# mysql -u root -pNerv2015 < script.sql# rm -f status.txt# mysql -u root -pNerv2015 < script.sql > status.txt# cat status.txt

# mysqladmin# mysqladmin -u root -pNerv2015 status

12

13

Instância x Banco de Dados

13

14

Engines

● MyISAM● InnoDB● Memory● CSV● Archive● Blackhole● Merge● Federated● Example

14

15

Lab 3: Processos e Threads

Execute um teste de carga no MySQL.# yum -y install mysql-test# mysqlslap --user=root --password=Nerv2015 --auto-generate-sql --concurrency=10 --iterations=10 --number-char-cols=10 --number-int-cols=5 --engine=innodb

Durante a execução do teste, acompanhe no Linux, via top.

Durante a execução do teste, acompanhe no MySQL, via mysqladmin.# mysqladmin -u root -pNerv2015 status

15

16

Lab 4.1: Parâmetros de otimizaçãoAltere todos os parâmetros abaixo no arquivo de parâmetros, e reinicie o MySQL.max_connections = 800table_open_cache = 100thread_cache_size = 16

innodb_buffer_pool_size = 128Minnodb_thread_concurrency = 32

key_buffer = 128M

read_buffer_size = 2Mread_rnd_buffer_size = 2Msort_buffer = 2Mjoin_buffer_size = 2M

query_cache_type = 1query_cache_size = 16Mquery_cache_limit = 2Mquery_cache_min_res_unit = 4096

16

17

Lab 4.2: Parâmetros de recuperação

Altere todos os parâmetros abaixo no arquivo de parâmetros, e reinicie o MySQL.tmp_table_size = 8Mtmpdir = /tmp/

slow_query_log_file = /var/log/mysql-slow.loglong_query_time = 5

innodb_log_file_size = 32Minnodb_log_files_in_group = 7innodb_log_buffer_size = 64Minnodb_flush_log_at_trx_commit = 1

mysql> show variables like '%slow%';mysql> show variables;set = ALTER SESSIONset global = ALTER SYSTEM

17

18

Lab 5: Bancos de Dados

Crie um novo banco de dados.# mysql -u root -pNerv2015mysql> show databases;mysql> create database nerv;mysql> show databases;mysql> use nerv;mysql> show tables;

Banco mysql = Schema SYSTEM

18

19

Usuários e Permissõesmysql> CREATE USER 'portilho'@'localhost' IDENTIFIED BY 'Nerv2015';mysql> CREATE USER 'portilho'@'192.168.0.5' IDENTIFIED BY 'Nerv2015';mysql> CREATE USER 'portilho'@'%' IDENTIFIED BY 'Nerv2015';

mysql> GRANT SELECT ON nerv.teste TO 'portilho'@'%';mysql> GRANT SELECT ON nerv.* TO 'portilho'@'localhost';mysql> GRANT ALL PRIVILEGES ON *.* TO 'portilho'@'localhost';

Conexão remota$ mysql -u root -pNerv2015 -h 192.168.0.2

Tabela mysql.user = Tabela DBA_USERS

19

20

Estruturas físicas

# ls -lh /var/lib/mysql/total 29M-rw-rw----. 1 mysql mysql 18M Jan 24 14:45 ibdata1-rw-rw----. 1 mysql mysql 5.0M Jan 24 14:45 ib_logfile0-rw-rw----. 1 mysql mysql 5.0M Jan 24 14:45 ib_logfile1drwx--x--x. 2 mysql mysql 4.0K Jan 24 14:43 mysqlsrwxrwxrwx. 1 mysql mysql 0 Jan 24 14:45 mysql.sockdrwx------. 2 mysql mysql 4.0K Jan 24 15:12 nervdrwx------. 2 mysql mysql 4.0K Jan 24 14:43 performance_schema-rw-rw----. 1 mysql mysql 6 Jan 24 14:45 Proni-PC.localdomain.pid-rw-r--r--. 1 root root 112 Jan 24 14:43 RPM_UPGRADE_HISTORY-rw-r--r--. 1 mysql mysql 112 Jan 24 14:43 RPM_UPGRADE_MARKER-LASTdrwxr-xr-x. 2 mysql mysql 4.0K Jan 24 14:43 test

# ls -lh /var/lib/mysql/nerv/total 4.0K-rw-rw----. 1 mysql mysql 65 Jan 24 15:12 db.opt

20

21

Lab 6: Tabelas

mysql> use nerv;mysql> CREATE TABLE teste (coluna1 int, coluna2 char(50), coluna3 varchar(255)) Engine=InnoDB;mysql> show tables;O que a criação desta tabela alterou nas estruturas físicas?

mysql> CREATE TABLE teste2 (coluna1 int, coluna2 char(50), coluna3 varchar(255)) Engine=MyISAM;mysql> show tables;O que a criação desta tabela alterou nas estruturas físicas?

mysql> use mysql;mysql> desc user;mysql> desc nerv.teste;

innodb_file_per_table

21

22

Lab 7: INSERT, UPDATE, DELETE, SELECT

Execute INSERT de 10 registros diferentes nas tabelas que você criou.

22

23

Lab 8: Transações e Isolamento

Execute um UPDATE em todas os registros da teste.

Abra outra sessão, e execute SELECT em todos os dados da tabela.O que aconteceu?

Repita a operação, mas antes do UPDATE, coloque como abaixo.mysql> START TRANSACTION;mysql> UPDATE ...O que aconteceu?

Execute INSERT,UPDATE, DELETE e SELECT nas tabelas que você criou.

23

24

Lab 9: System StatesExecute novamente o teste de carga, e acompamnhe pelos comandos abaixo.mysql> SHOW PROCESSLIST;mysql> SHOW FULL PROCESSLIST;

http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

24

25

Lab 10: Importação e Exportação

ExemplosSELECT * FROM Cars INTO OUTFILE '/tmp/cars.txt';LOAD DATA INFILE '/tmp/cars.txt' INTO TABLE Cars;

SELECT * FROM Cars INTO OUTFILE '/tmp/cars.csv' FIELDS TERMINATED BY ',';LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars FIELDS TERMINATED BY ',';

Execute este procedimento para as tabelas que você criou.

25

26

Lab 11: Bin Logs

Adicione no /etc/my.cnf a linha abaixo, e reinicie o mysql.log-bin=mysql-bin

O que aconteceu no diretório de dados?

expire_logs_days

26

27

Lab 12: Backup MyISAM

Coloque uma tabela MyISAM em LOCK, e a copie para outro diretório.LOCK TABLE teste2 WRITE;FLUSH TABLE teste2;

Execute a cópia pelo sistema operacional.# cp -rf /var/lib/mysql/nerv /root

UNLOCK TABLEs;

27

28

Lab 13: mysqldump

Execute um backup via mysqldump.$ mysqldump -u root -pNerv2015 nerv > nerv.sql$ mysqldump -u root -pNerv2015 --all_databases > nerv01.sql

Edite o arquivo gerado.O que ele contém?Como utilizar este backup?Quais suas desvantagens?

28

29

Lab 14: INFORMATION_SCHEMA

Verifique as tabelas do banco de dados INFORMATION_SCHEMA.Quantas tabelas você possui em todo o MySQL?E em seu banco de dados?

29

30

Lab 15: System Status

Execute no MySQL o comando abaixo.mysql> SHOW STATUS;

Que informações que este comando gera são úteis?

30

31

Alta Disponibilidade

MySQL Replication

31

32

MySQL Replication

32

Mastermysql> CREATE USER 'replicator'@'192.168.0.2' IDENTIFIED BY 'Nerv2015';mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.3';mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;Backup no Master / Restore no Slavemysql> UNLOCK TABLES;

Slavemysql> CHANGE MASTER TO

MASTER_HOST='192.168.0.4',MASTER_USER='replicator',MASTER_PASSWORD='Nerv2015',MASTER_LOG_FILE='mysql-bin.000087',MASTER_LOG_POS=197872700;

mysql> START SLAVE;mysql> SHOW SLAVE STATUS;

33

Alta Disponibilidade

MySQL Proxy

33

34

Alta Disponibilidade

MySQL Cluster

34

35

Alta Disponibilidade

MySQL Fabric

35

36

Documentação / Livros

Documentação oficialhttp://dev.mysql.com/doc/refman/5.0/en/index.htmlhttp://dev.mysql.com/doc/refman/5.1/en/index.htmlhttp://dev.mysql.com/doc/refman/5.5/en/index.htmlhttp://dev.mysql.com/doc/refman/5.6/en/index.htmlhttp://dev.mysql.com/doc/refman/5.7/en/index.html

LivrosMySQL 5.0 Certification Study GuideHigh Performance MySQL (O'Reilly)

36

37

Certificações

● Oracle Certified Professional, MySQL 5.6 Database Administrator● Oracle Certified Professional, MySQL 5.6 Developer

37