MySQL Cheat Sheet

Para executar após a instalação do serviço MySQL e antes de disponibilizá-lo para uso regular:

# mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql --auth-root-authentication-method=normal
# systemctl start mysqld
# mysql_secure_install

Alterar a senha do usuário root quando a senha é conhecida:

# mysqladmin -u root -p password newpass

Purge binary logs from /var/lib/mysql - change to appropriate mysql-bin.{number} and to desired number of days to be kept (eg 3 days)

> PURGE BINARY LOGS TO `mysql-bin.000223`;
> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

Check authentication method used by each user

> SELECT user,authentication_string,plugin,host FROM mysql.user;

If root uses auth_socket, change it to native password

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'CHANGEME';
> FLUSH PRIVILEGES;

phpMyAdmin

Os comandos abaixo criarão um usuário para lidar com a database do phpmyadmin e executar tarefas de backup Usa-se /usr/share/webapps/phpMyAdmin/sql/create_tables.sql se necessário para criar as tabelas As tabelas abaixo devem estar listadas em /etc/webapps/phpmyadmin/config.inc.php

# mysql -u root -p < /usr/share/webapps/phpMyAdmin/sql/create_tables.sql
# mysql -u root -p
> CREATE USER 'pma'@'localhost' IDENTIFIED BY 'CHANGE-ME!';
> GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'CHANGE-ME!';
> GRANT SELECT (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
> GRANT SELECT ON mysql.db TO 'pma'@'localhost';
> GRANT SELECT ON mysql.host TO 'pma'@'localhost';
> GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';
> GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
> GRANT SELECT, LOCK TABLES ON *.* TO 'pma'@'localhost';

Falta agora configurar a chave para blowfish_secret e o diretório tempórario (requer permissão de escrita para o usuário http)

# vi /usr/share/webapps/phpMyAdmin/config.inc.php
> $cfg['blowfish_secret'] = 'UNIQUE-32-CHARS-RANDOM-STRING';
> $cfg['TempDir'] = '/tmp/phpmyadmin';

Wordpress operations

Criar um administrador via banco de dados:

> INSERT INTO `wp_replace_me`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('3', 'new-user', MD5('new-password'), 'New User', 'newuser@nowhere.com', '', '2020-01-01 00:00:00', '', '0', 'New User');
> INSERT INTO `wp_replace_me`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '3', 'wp_capabilities', 'a:1:{s:13: "administrator";s:1:"1";}');
> INSERT INTO `wp_replace_me`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '3', 'wp_user_level', '10');

Search&Replace URLS - substituir http por https e tal:

> USE wp_database;
> UPDATE wp_options SET option_value = replace(option_value, 'http://OLDSITE', 'https://NEWSITE') WHERE option_name = 'home' OR option_name = 'siteurl';
> UPDATE wp_posts SET guid = replace(guid, 'http://OLDSITE','https://NEWSITE');
> UPDATE wp_posts SET post_content = replace(post_content, 'http://OLDSITE', 'https://NEWSITE');
> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://OLDSITE','https://NEWSITE');