MySQL DBA Trabalho em casa - Brasil

 Por favor, envie seu currículo/CV em inglês. Esta função é totalmente remota no Brasil.

O TRABALHO:

Junte-se à nossa equipe de banco de dados e torne-se um membro essencial de uma equipe responsável pelo maior

sistema de bilhetagem do mundo. Atualmente estamos trabalhando na criação de um conjunto de próxima geração

ferramentas de banco de dados para criar, gerenciar e operar vários bancos de dados da AWS e, em seguida, migrar nossos bancos de dados em nuvem para a AWS. Utilizamos diversas tecnologias de banco de dados RDBMS e NoSQL. Oferecemos suporte a diversas plataformas de banco de dados, executadas em infraestrutura de nuvem privada e pública, ao mesmo tempo em que impulsionamos o maior sistema de emissão de bilhetes do mundo e atendemos 170 milhões de clientes.

Por favor, siga este URL para enviar currículo 

Criptografia MariaDB (dados em repouso)

Abaixo está um exemplo simples que mostra algumas opções de criptografia MariaDB. 

Você deve considerar o que deseja criptografar. A comunicação de dados (dados em trânsito) ou dados na instância (dados em repouso). 

Esta postagem se concentrará na opção de dados em repouso usando um nó de nível gratuito da AWS em execução no Amazon Linux. Usarei o banco de dados global em 2 instâncias diferentes para mostrar a atualização das tabelas atuais com criptografia, bem como novas tabelas de carregamento que serão criptografadas automaticamente. 

Primeiro, começaremos com as instalações... rápidas e fáceis apenas para esta demonstração. 

 # yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id 
server_id=100

# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using  EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+

Carregaremos o banco de dados global na instância server_id 100. 

# mysql < world.sql
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)  

Agora podemos ver que ambas as instâncias não estão usando criptografia. 

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

Agora, em ambos os sistemas, configurarei chaves aleatórias e as criptografarei. 

#  mkdir /etc/mysql/

#  mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
#  (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1    -pass file:/etc/mysql/encryption/keyfile.key    -in /etc/mysql/encryption/keyfile    -out /etc/mysql/encryption/keyfile.enc

 ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc

Ahora podemos configurar el archivo cnf para habilitar el complemento, como opciones de criptografía. 

# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

Reinicie MariaDB para que podamos verificar el estado actual. 

systemctl restart mariadb.service

mysql

MariaDB [(none)]> select @@server_id;

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

| @@server_id |

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

|         100 |

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

1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

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

| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

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

| innodb_system              |                 1 |              1 |

| mysql/innodb_table_stats   |                 1 |              1 |

| mysql/innodb_index_stats   |                 1 |              1 |

| mysql/transaction_registry |                 1 |              1 |

| mysql/gtid_slave_pos       |                 1 |              1 |

| world/city                 |                 1 |              1 |

| world/country              |                 1 |              1 |

| world/countrylanguage      |                 1 |              1 |

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

8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;

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

| @@server_id |

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

|         200 |

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

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

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

| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

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

| innodb_system              |                 1 |              1 |

| mysql/innodb_index_stats   |                 1 |              1 |

| mysql/gtid_slave_pos       |                 1 |              1 |

| mysql/innodb_table_stats   |                 1 |              1 |

| mysql/transaction_registry |                 1 |              1 |

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

5 rows in set (0.000 sec)

Faça upload também dos dados mundiais para a instância server_id 200. 

 

# mysql < world.sql
# mysql 
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
| world/city                 |                 1 |              1 |
| world/country              |                 1 |              1 |
| world/countrylanguage      |                 1 |              1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

De acordo com information_schema.INNODB_TABLESPACES_ENCRYPTION agora estamos criptografados. No entanto, eles não mostram isso no nível do esquema. Embora digam que está criptografado se aparecer na  tabela INNODB_TABLESPACES_ENCRYPTION, prefiro ter certeza e vê-lo na tabela e no esquema. 

 

MariaDB [(none)]> show create table world.city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)

Até este ponto, você pode ver que ambas as instâncias foram contabilizadas no  esquema INNODB_TABLESPACES_ENCRYPTION após a reinicialização ou carregamento do esquema e dos dados. 

Então... algumas modificações na tabela vão ajudar aqui... 

MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage  ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city\G

*************************** 1. row ***************************

       Table: city

Create Table: CREATE TABLE `city` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `Name` char(35) NOT NULL DEFAULT '',

  `CountryCode` char(3) NOT NULL DEFAULT '',

  `District` char(20) NOT NULL DEFAULT '',

  `Population` int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (`ID`),

  KEY `CountryCode` (`CountryCode`),

  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1

1 row in set (0.000 sec)

Isso é simples etc. até agora... agora precisamos habilitar binlogs e verificar mais. 

vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)

Verificando os binlogs .... 

mariadb-binlog--base64-output=DECODE-ROWS --verbose  demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741  Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

É bom ver que agora está sendo criptografado. 

MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
       Table: city2
Create Table: CREATE TABLE `city2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)

Quero ver essas transações no log binário.. como? Puedes usar mariadb_binlog junto con --read-from-remote-server para poder ver los datos en los registros...

mariadb-binlog  --base64-output=DECODE-ROWS --verbose --read-from-remote-server   demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741  Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225  0:06:06 server id 100  end_log_pos 296 CRC32 0x0c89f3bb  Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225  0:06:06 server id 100  end_log_pos 325 CRC32 0x535776a2  Gtid list []
# at 325
#240225  0:06:06 server id 100  end_log_pos 363 CRC32 0x2ac4a61b  Binlog checkpoint demo.000001
# at 363
#240225  0:09:40 server id 100  end_log_pos 405 CRC32 0x93e10dc4  GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225  0:09:40 server id 100  end_log_pos 501 CRC32 0x39269040  Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225  0:09:49 server id 100  end_log_pos 543 CRC32 0xde82b753  GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225  0:09:49 server id 100  end_log_pos 602 CRC32 0x05bbb9e6  Annotate_rows:
#Q> insert into city2 select * from city
#240225  0:09:49 server id 100  end_log_pos 661 CRC32 0x9e0b4e0d  Table_map: `world`.`city2` mapped to number 21
# at 661

Espero que isso possa pelo menos começar ... 

Recursos :

https://mariadb.com/kb/en/securing-mariadb-encryption/  

Usando seu arquivo FRM para obter Schema e, em seguida, importar arquivos idb ..

Este é um tópico que em geral você nunca deveria ter que fazer ... Por quê? Porque você criou backups certos ... Você testou e sabe que os backups funcionam, então você pode simplesmente restaurar esses backups e obter seu esquema perdido e dados relacionados ... 

No entanto, aquela instância no escritório de canto ... você nunca teve o tempo de configurar ... não é tão importante ... apenas travou e agora você percebe como realmente a usa ... 

Nem tudo está perdido ..  

O MySQL lanç seus utilitários MySQL há algum tempo e, desde então, foi substituído mais pelo MySQL Shell.  

• https://downloads.mysql.com/archives/utilities 
• https://dev.mysql.com/downloads/shell/

O mysqlfrm ainda é muito útil quando precisa retirar o esquema de um arquivo FRM em um comando rápido e simples e é uma instalação simples. 

mysqlfrm --diagnostic city.frm
# WARNING: Cannot generate character set or collation names without the --server option. # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for city.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(160) DEFAULT NULL,
  `CountryCode` char(12) NOT NULL,
  `District` char(80) NOT NULL,
  `Population` int(11) NOT NULL,
PRIMARY KEY `PRIMARY` (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `popkey` (`Population`)
) ENGINE=InnoDB;

#...done.


Agora que você tem o esquema que perdeu ... reconstrua o banco de dados ou uma tabela. Por causa do exemplo, direi que acabamos de perder os dados da cidade do banco de dados mundial. 

$ cp  city.ibd  / tmp /  

$ cp city.ibd /tmp/
mysql> LOCK TABLES city WRITE;
mysql> ALTER TABLE city DISCARD TABLESPACE;

cp city.ibd /edb/local/mysql/data/rundeck/
chown tmdba:dba /edb/local/mysql/data/rundeck/city.ibd

mysql> ALTER TABLE city IMPORT TABLESPACE;
mysql> UNLOCK TABLES;
mysql> SELECT COUNT(*) FROM city;



MySQL mysql_config_editor & expect

 Esta é apenas uma nota para ajudar qualquer pessoa que queira usar o comando mysql_config_editor em suas ferramentas de automação. 

o mysql_config_editor não aceita um argumento de senha, portanto, como ferramentas de automação que podem ter definido sua senha no arquivo .my.cnf ao tentar usar mysql_config_editor falham. 

É possível e bastante simples com uma ferramenta expect. 

 yum -y install expect  

ele também funciona para o apt-get. 

Portanto, neste exemplo, mostrarei uma versão de script bash simples. 

1º .. meu caminho de login não funciona ... 

mysql --login-path=local

ERROR 1045 (28000): Access denied for user

Defina isso com o esperado 

Você executaria isso por meio de seu script bash.  

expect <<EOD

spawn mysql_config_editor set --login-path=local --host=localhost --user=root --password 

expect "password"

send  -- "<PASSWORD>\r"

interact

EOD

Agora funciona ...

mysql --login-path=local

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1002

MySQL e Dockers ... uma configuração simples

MySQL e Dockers ... não são novos conceitos, as pessoas estão se mudando para o Dockers há algum tempo. Para alguém que está apenas se movendo para isso em desenvolvimento, isso pode ter alguns obstáculos.

Enquanto o MySQL funciona bem em execução local, se você estiver testando código em diferentes versões do MySQL, é bom ter várias versões facilmente disponíveis.

Uma opção há anos, é claro, é https://mysqlsandbox.net/ de Giuseppe Maxia. Esta é uma solução muito válida para poder obter várias instâncias e testar a replicação, etc.

Dockers agora também são outro cenário frequentemente usado quando se trata de testar versões diferentes do MySQL. A seguir, serão apresentados alguns passos para obter várias versões instaladas facilmente. Eu uso o OSX, então esses exemplos são para o OSX.

Você precisa do Docker para iniciar e, claro, e o Docker Desktop é uma ferramenta útil para você poder acessar facilmente.

• https://docs.docker.com/docker-for-mac/install/
• https://docs.docker.com/docker-for-windows/

Depois de configurar o Docker, posso preparar meu ambiente para o MySQL.

Aqui, criei uma pasta Docker que contém os diretórios de dados do MySQL, os arquivos de configuração e o diretório de arquivos mysql, se necessário.

mkdir ~/Docker ;

mkdir ~/Docker/mysql_data;

mkdir ~/Docker/mysql-files;

mkdir ~ / Docker / cnf;

Agora dentro do mysql_data

cd ~/Docker/mysql_data;

mkdir 8.0;

mkdir 5.7;

mkdir 5.6;

mkdir 5.5;

Agora eu configurei arquivos cnf simples para este exemplo. A principal coisa a observar é o endereço de ligação. Isso está definido para garantir que seja aberto para alcançarmos o MySQL fora da janela de encaixe. Você também pode observar que esses arquivos podem ser usados ​​para configurar informações adicionais de configuração, conforme você entender por instância do estivador do MySQL.

cd ~/Docker/cnf;




cat my.8.0.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

secure-file-priv= /var/lib/mysql-files

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

bind-address = 0.0.0.0

port=3306

server-id=80







# Custom config should go here

!includedir /etc/mysql/conf.d/




cat my.5.7.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=57

max_allowed_packet=32M




$ cat my.5.6.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=56




$ cat my.5.5.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=55

OK. Agora que temos os arquivos de configuração configurados, precisamos construir as janelas de encaixe. Algumas coisas a serem observadas nos comandos de construção.

--name Definimos uma referência nomeada para a janela de encaixe.

Aqui estamos mapeando os arquivos de configuração, o diretório de dados e o diretório mysql-files para a janela de encaixe. Isso nos permite ajustar o arquivo my.cnf e etc facilmente.

-v ~ / Docker / cnf / my.8.0.cnf: /etc/mysql/my.cnf

-v ~ / Docker / mysql_data / 8.0: / var / lib / mysql

-v ~ / Docker / arquivos mysql: / var / lib / arquivos mysql

Queremos alcançar essas instâncias do MySQL fora da janela de encaixe, portanto, precisamos publicar e mapear a porta de acordo.

-p 3306: 3306 Isso significa 3306 local para 3306 dentro da janela de encaixe

-p 3307: 3306 Isso significa 3307 local para 3306 dentro da janela de encaixe

-p 3308: 3306 Isso significa 3308 local para 3306 dentro da janela de encaixe

-p 3309: 3306 Isso significa 3309 local para 3306 dentro da janela de encaixe

Também passamos algumas variáveis ​​de ambiente.

-e MYSQL_ROOT_HOST =% -e MYSQL_ROOT_PASSWORD = <defina uma senha aqui>

Então, juntando tudo ...

docker run --restart always --name mysql8.0 -v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/8.0:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3306:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:8.0




docker run --restart always --name mysql5.7 -v ~/Docker/cnf/my.5.7.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.7:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3307:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.7




docker run --restart always --name mysql5.6 -v ~/Docker/cnf/my.5.6.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.6:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3308:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.6




docker run --restart always --name mysql5.5 -v ~/Docker/cnf/my.5.5.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.5:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3309:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.5

Após cada execução dos comandos acima, você deve obter um ID retornado.

exemplo: 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316

Você pode iniciar / reiniciar e acessar cada terminal docker facilmente através do Docker Desktop ou apenas anotar os IDs relacionados e executá-los através do terminal.

O Docker Desktop também mostra todas as variáveis ​​que você passou para que você possa validar.

Obviamente, você também pode acessar a CLI aqui, parar e iniciar ou destruí-la facilmente.

$ docker exec -it 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316 /bin/sh; exit

# mysql -p

Se o contêiner do Docker já estiver em execução, você poderá acessar o MySQL através do seu terminal localhost.

$ mysql --host=localhost --protocol=tcp --port=3306 -p -u root

Agora, se você estiver tendo algum problema de acesso, lembre-se de garantir que as contas do MySQL estejam corretas e que suas portas e mapeamento estejam corretos.

• Conexão perdida com o servidor MySQL ao 'ler o pacote de comunicação inicial'
• ERRO 1045 (28000): acesso negado para o usuário 'root'@'192.168.0.5' (usando a senha: YES)

Agora você pode ver que todos estão ativos e disponíveis e os IDs do servidor correspondem ao que definimos por eariler de arquivo cnf.

$ mysql --host=localhost --protocol=tcp --port=3306 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| 58e9663afe8d | 8.0.19 | 80 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3307 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| b240917f051a | 5.7.29 | 57 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3308 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| b4653850cfe9 | 5.6.47 | 56 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3309 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| 22e169004583 | 5.5.62 | 55 |
+--------------+-----------+-------------+

MySQL Como você restaura o tablespace

MySQL Como você restaura o tablespace?

Esta não é uma informação nova, mas eu não a abordei tanto, então dirijo-a agora para aqueles que precisam dela.

Se você perder seus arquivos ibd ... você perderá seus dados. Portanto, se você tiver uma cópia de uma disponível ... ou mesmo se estiver sincronizando a partir de outro banco de dados, ainda poderá importá-la. O que / como você perde o tablespace?

Aqui está um exemplo simples para recuperar o espaço de tabelas.



mysql> Create database demo;

mysql> use demo;

mysql> CREATE TABLE `demotable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;

Agora nós armazenamos alguns dados ...


mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
+----+---------------------+
2 rows in set (0.00 sec)

OK agora vamos quebrar ..


# systemctl stop mysqld
# cd /var/lib/mysql/demo/
# ls -ltr
total 80
-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd
# mv demotable.ibd /tmp/

# systemctl start mysqld
# mysql demo

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demotable |
+----------------+
1 row in set (0.00 sec)

mysql> desc demotable;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.

Espaço de tabelas quebrado e perdido ... Agora podemos recuperá-lo ..


demo]# cp /tmp/demotable.ibd .

mysql> ALTER TABLE demotable DISCARD TABLESPACE;

demo]# cp /tmp/demotable.ibd .
demo]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd
demo]# chown mysql:mysql demotable.ibd
demo]# mysql demo
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it

mysql> REPAIR TABLE demotable;
+----------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+---------------------------------------------------------+
| demo.demotable | repair | note | The storage engine for the table doesn't support repair |
+----------------+--------+----------+---------------------------------------------------------+

Note agora que nós também temos outro erro .. Isto geralmente está ligado ao espaço disponível para o tmpdir, e o reparo não funciona para o .ibd de qualquer maneira.


mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+

# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/

# systemctl restart mysqld
# mysql demo

OK usou o diretório mysql-files apenas por exemplo.
Agora podemos tentar novamente.


mysql> ALTER TABLE demotable IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.61 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
| 3 | 2019-07-12 23:56:08 |
+----+---------------------+

OK funcionou.
Agora, tudo isso é legal e simples se você tiver apenas uma tabela. Mas e quanto aos 100s ...

Automatize, é claro, e use seu information_schema para ajudar.

Faça mais algumas cópias para teste.


mysql> create table demotable1 like demotable;
Query OK, 0 rows affected (0.51 sec)

mysql> create table demotable2 like demotable;
Query OK, 0 rows affected (1.04 sec)

mysql> create table demotable3 like demotable;
Query OK, 0 rows affected (0.74 sec)

mysql> create table demotable4 like demotable;
Query OK, 0 rows affected (2.21 sec)

Quebre todos eles ..


demo]# mv *.ibd /tmp/

Agora, usando sua tabela information_schema.tables, você pode construir todos os comandos necessários.


# vi build_discard.sql
# cat build_discard.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';

# vi build_import.sql
# cat build_import.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';


# mysql -N < build_import.sql > import_tablespace.sql
# mysql -N < build_discard.sql | mysql demo

demo]# cp /tmp/*.ibd .
demo]# chown mysql:mysql *.ibd
# systemctl restart mysqld
# mysql demo < import_tablespace.sql
# mysql demo

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO demotable1 (id) VALUES (NULL);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO demotable2 (id) VALUES (NULL);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO demotable3 (id) VALUES (NULL);
^[[AQuery OK, 1 row affected (0.37 sec)

mysql> INSERT INTO demotable4 (id) VALUES (NULL);
Query OK, 1 row affected (0.12 sec)

E funcionou. 

MySQL Binlogs :: Como recuperar

Então percebi que não havia feito um post sobre isso depois dessa situação que surgiu recentemente.

Aqui está o cenário: Um backup foi feito à meia-noite, eles usaram o MySQL Dumps por banco de dados. Então, às dez da manhã do dia seguinte, o banco de dados caiu. Uma série de eventos aconteceu antes de eu ser chamado, mas eles conseguiram uma versão do banco de dados com tabelas MyISAM e os arquivos IBD faltando no tablespace.

Portanto, a opção 1, a restauração do backup, nos levaria à meia-noite e perderíamos horas de dados. Opção 2, nós reimportamos os milhares de arquivos ibd e mantemos tudo. Em seguida, tivemos a opção 3, restaurar a partir do backup e, em seguida, aplicar os logs binários para alterações recentes.

Para torná-lo mais interessante, eles não tinham todos os arquivos ibd que me contaram, e eu vi alguns desaparecidos. Portanto, não tenho certeza de como isso foi possível, mas a opção 2 se tornou uma opção inválida. Eles, claro, queriam a menor perda de dados possível, então fomos com a opção 3.

Para fazer isso com segurança, iniciei outra instância do MySQL na porta 3307. Isso me permitiu um local seguro para trabalhar enquanto o tráfego tinha acesso de leitura aos dados do MyISAM na instância da porta 3306.

Depois que todos os arquivos de backup foram descompactados e importados para a instância 3307, consegui me concentrar nos arquivos do log binário.

A princípio, esse conceito parece muito mais arriscado do que realmente é. Na verdade, é bem direto e simples.

Então, primeiro você tem que encontrar os dados depois. Uma revisão dos arquivos binlogs lhe dá uma vantagem sobre quais arquivos são relevantes. No meu caso, de alguma forma eles conseguiram redefinir o log binário para que o arquivo 117 tivesse 2 intervalos de datas dentro dele.

Primeiro para a revisão do binlog, o comando a seguir exibe os dados em formato legível.

mysqlbinlog --defaults-file=/root/.my.cnf --base64-output=DECODE-ROWS --verbose mysql-bin.000117 > review_mysql-bin.000117.sql

* Nota ... Tenha cuidado ao executar o comando acima. Observe que eu tenho que despejar o arquivo diretamente no mesmo local que o log binário. Então, VALIDAR que seu nome de arquivo é válido. Este mysql-bin.000117.sql é diferente deste mysql-bin.000117 .sql. Você perderá seu log binário com a 2ª opção e um espaço antes de .sql.

Agora, salve os dados para que possam ser aplicados. Desde que eu tinha vários binlogs eu criei um arquivo e queria verificar novamente os intervalos de tempo de qualquer maneira.


mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql

mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"


Agora eu apliquei todos os dados desses binlogs para os intervalos de tempo fornecidos. O cliente checou novamente todos os dados e ficou muito feliz em tê-los de volta.

Várias opções diferentes existiam para esta situação, isso aconteceu para treinar melhor com o cliente.

Uma vez que o validado estava tudo bem na versão restaurada, era simples parar ambos os bancos de dados, mover os diretórios de dados (queria manter os padrões de datadir intactos), chover os diretórios apenas para serem seguros e iniciar o MySQL. Agora a instância restaurada estava na porta 3306.