ByteRef
← Home
🗄️

MySQL banco de dados relacional

sqldatabasemysql
🚀 Conexão & Setup
Conectar ao MySQLmysql -u root -p
Conectar a banco específicomysql -u user -p -h localhost mydb
Criar banco de dadosCREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Listar bancosSHOW DATABASES;
Criar usuárioCREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';
Dar permissõesGRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
Ver versãoSELECT VERSION();
Iniciar serviçosudo systemctl start mysql
📋 DDL - Estrutura
Criar tabelaCREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Adicionar colunaALTER TABLE users ADD COLUMN age INT;
Criar índiceCREATE INDEX idx_users_email ON users(email);
Foreign keyALTER TABLE posts ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;
Criar viewCREATE VIEW active_users AS
SELECT * FROM users WHERE active = 1;
Auto increment customALTER TABLE users AUTO_INCREMENT = 1000;
Enum typeALTER TABLE users ADD COLUMN status ENUM('active', 'inactive', 'banned') DEFAULT 'active';
🔍 Consultas & Joins
JOIN com agregaçãoSELECT u.name, COUNT(p.id) as posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 0;
SubquerySELECT * FROM users
WHERE id IN (
  SELECT user_id FROM posts
  WHERE created_at > '2023-01-01'
);
Window functionsSELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;
Full-text searchALTER TABLE articles ADD FULLTEXT(content);
SELECT * FROM articles
WHERE MATCH(content) AGAINST('database query' IN NATURAL LANGUAGE MODE);
JSON operationsSELECT JSON_EXTRACT(data, '$.name') as name,
  JSON_EXTRACT(data, '$.address.city') as city
FROM users WHERE JSON_CONTAINS(data, '{"active": true}');
PaginationSELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;
⚡ Performance & Otimização
EXPLAIN queryEXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
Ver índicesSHOW INDEX FROM users;
Analyze tableANALYZE TABLE users;
Ver status do InnoDBSHOW ENGINE INNODB STATUS;
Ver conexões ativasSHOW PROCESSLIST;
Cache hit ratioSHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
Tamanho das tabelasSELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;
🔄 Backup & Restore
Backup completomysqldump -u root -p mydb > backup.sql
Backup comprimidomysqldump -u root -p mydb | gzip > backup.sql.gz
Backup de estrutura apenasmysqldump -u root -p --no-data mydb > schema.sql
Restore de backupmysql -u root -p mydb < backup.sql
Backup remotomysqldump -h remote.host -u user -p mydb > backup.sql
Backup incrementalmysqlbinlog --start-datetime="2023-01-01 00:00:00" /var/log/mysql/mysql-bin.000001 > incremental.sql
🔧 Administração
Ver variáveis globaisSHOW GLOBAL VARIABLES;
Alterar variávelSET GLOBAL innodb_buffer_pool_size = 268435456;
Ver statusSHOW GLOBAL STATUS;
Ver logs de errotail -f /var/log/mysql/error.log
Ver tamanho do bancoSELECT table_schema "Database",
  SUM(data_length + index_length) "Size"
FROM information_schema.tables GROUP BY table_schema;
Flush privilegesFLUSH PRIVILEGES;
Reset root passwordALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
BR ByteRef DevOps & Backend Reference · 2026 — comandos