MySQL cheatsheet

tags: mysql, cheatsheet date: 2020-07-24

Reset password for the MariaDB root user

sudo -u mysql mysqld --verbose --skip-grant-tables

Run:

mysql

In MariaDB/MySQL shell run:

use mysql
UPDATE user SET authentication_string=PASSWORD('c00lpassw0rd') WHERE user='root';

You can start the MariaDB daemon with:

/usr/bin/mysqld_safe --datadir='/var/lib/mysql'

Create user

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'str0ngpassw0rd';

Provide access to database

GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';

Add indexes

Add index on field:

ALTER TABLE stat ADD INDEX (hostname);

ALTER TABLE stat ADD INDEX (created);

Add index on two fields:

ALTER TABLE stat ADD INDEX `hostname_created_index`(`hostname`, `created`);

Review indexes

SHOW INDEX FROM `stat`;

Delete indexes

DROP INDEX  hostname ON stat;