Reset root password

Stop the MySQL service

(Ubuntu and Debian) Run the following command:

sudo /etc/init.d/mysql stop

(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:

sudo /etc/init.d/mysqld stop

Start MySQL without a password

Run the following command. The ampersand (&) at the end of the command is required.

sudo mysqld_safe --skip-grant-tables &

Connect to MySQL

Run the following command:

mysql -uroot

Set a new MySQL root password

Run the following command:

use mysql;

update user set password=PASSWORD("mynewpassword") where User='root';

for mariaDB:
UPDATE mysql.user SET authentication_string = PASSWORD('mynewpassword') WHERE User = 'root' AND Host = 'localhost'; flush privileges;
quit

MySQL шпаргалки

На всё локально

GRANT ALL PRIVILEGES ON * . * TO 'novyi_polzovatel'@'localhost';

или на чтение локально
GRANT SELECT ON *.* TO 'novyi_polzovatel'@'localhost';
или для пользователя для создания бэкапов
GRANT SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES, PROCESS, EXECUTE ON *.* TO 'novyi_polzovatel'@'localhost';

на базу локально

GRANT ALL PRIVILEGES ON database.table TO 'novyi_polzovatel'@'localhost';

на всё глобально

GRANT ALL PRIVILEGES ON *.* TO 'outuser'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION

FLUSH PRIVILEGES;

Восстановление таблиц "...is marked as crashed"

myisamchk -r $TABLE_NAME
или
myisamchk -r -v -f $TABLE_NAME

sed -i 's/`MYDATABASE`/`MYNEWDATABASE`/g' mydump.sql

РАЗМЕРЫ

Для того чтоб посмотреть размер всех баз данных выполните команду:

Для того чтоб узнать размер таблиц конкретной базы данных:

SET NAMES utf8;
DROP DATABASE `mydb`;
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;
USE `mydb`;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
SOURCE /path/to/db_dump.sql;
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

SELECT * FROM information_schema.PROCESSLIST AS p WHERE p.COMMAND = 'Binlog Dump';

SNMP Trap receiver (former EventDB) on RedHat

Install snmp package:

apt-get -y install snmptrapd

now we have to decide if Variant 1 or 2 (2 is preferred because of translated traps)

VARIANT 1:

Creating just a normal trap database

Now, we need to create the database the traps will be stored in, as well as the database user we will use when logging traps.

mysql -u root -p

Create the database we will log traps to:

MariaDB [(none)]> create database net_snmp;

MariaDB [(none)]> create user 'netsnmp'@'localhost' identified by 'sekritpass';
MariaDB [(none)]> grant all on net_snmp.* to 'netsnmp'@'localhost' identified by 'sekritpass';

Create the schema we will enter data into.

A database schema is the actual structure we write data to, including the tables. Consider it the 'shape' and 'function' of the database. The file describing the database schema is, as far as I can tell, not installed with snmpd. But it is present in the net-snmp source code distribution.

I reproduced it below.

Copy and paste this at the MariaDB prompt:

USE net_snmp;
DROP TABLE IF EXISTS notifications;
CREATE TABLE IF NOT EXISTS `notifications` (
  `trap_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL,
  `host` varchar(255) NOT NULL,
  `auth` varchar(255) NOT NULL,
  `type` ENUM('get','getnext','response','set','trap','getbulk','inform','trap2','report') NOT NULL,
  `version` ENUM('v1','v2c', 'unsupported(v2u)','v3') NOT NULL,
  `request_id` int(11) unsigned NOT NULL,
  `snmpTrapOID` varchar(1024) NOT NULL,
  `transport` varchar(255) NOT NULL,
  `security_model` ENUM('snmpV1','snmpV2c','USM') NOT NULL,
  `v3msgid` int(11) unsigned,
  `v3security_level` ENUM('noAuthNoPriv','authNoPriv','authPriv'),
  `v3context_name` varchar(32),
  `v3context_engine` varchar(64),
  `v3security_name` varchar(32),
  `v3security_engine` varchar(64),
  PRIMARY KEY  (`trap_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS varbinds;
CREATE TABLE IF NOT EXISTS `varbinds` (
  `trap_id` int(11) unsigned NOT NULL default '0',
  `oid` varchar(1024) NOT NULL,
  `type` ENUM('boolean','integer','bit','octet','null','oid','ipaddress','counter','unsigned','timeticks','opaque','unused1','counter64','unused2') NOT NULL,
  `value` blob NOT NULL,
  KEY `trap_id` (`trap_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Konfigurationen:

/etc/snmp/snmp.conf

mibdirs  +/etc/snmp/translatedMIBS
# mibwarninglevel: Should warnings about mibs be displayed when the mibs are loaded
#   arguments: 1|2
mib 1warninglevel

/etc/snmp/snmptrapd.conf

# Example configuration file for snmptrapd
#
# No traps are handled by default, you must edit this file!
#
# authCommunity   log,execute,net public
# traphandle SNMPv2-MIB::coldStart    /usr/bin/bin/my_great_script cold

#authCommunity log YOURCOMMUNITYSTRING
disableAuthorization yes
sqlMaxQueue 1
sqlSaveInterval 9

/etc/my.cnf.d/snmptrapd.cnf

[snmptrapd]
user=USER
password=PASSSWORD
host=localhost

So you can send traps to the database using:

snmptrap -v 2c -c public IPofTRAPRECEIVER "TEXT" 1.2.3.4.0

[tags]mysql,sql,linux,debian,ubuntu,скрипты,сервер,настройка,backup[/tags]

MariaDB 

Установка:
sudo apt-get install libapache2-mod-php mysql-server mysql-client
sudo mysql_install_db
sudo mysql_secure_installation

Добавление пользователя (незайти в phpmyadmin по root:
CREATE USER 'root1'@'localhost' IDENTIFIED BY 'mypassword123';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Удаление:
sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean



 Lisolog (C) / 2020