Cheat sheet for mariadb

mariadb-cheat-sheet.md

MariaDB Cheat Sheet

From command line

Export database (with drop tables)

mysqldump -u username -p --add-drop-table database_name > data-dump.sql

Import database

mysql -u username -p database_name < data-dump.sql

From MariaDB

Logging into MariaDB if using passwords

mysql -u your-username-here -p NOTE: Don't provide your password until MariaDB asks for it.

Logging into MariaDB if using unix_socket

mysql -u your-username-here

Create user with password authentication (@localhost)

CREATE USER your-username-here@localhost IDENTIFIED VIA mysql_native_password;

Create user with unix_socket authentication (@localhost)

CREATE USER your-username-here@localhost IDENTIFIED VIA unix_socket;

Set password for an existing user

First, while loggedin as the admin user (root)

USE mysql;

then...

UPDATE user SET password=PASSWORD('your-password-here') WHERE User='your-username-here' AND Host = 'localhost';

Use password instead of unix_socket

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;

Create Database

CREATE DATABASE database_name;

Edit database/table

UPDATE db_name.table_name SET column_name='something' WHERE table_name='something_else';

Grant privileges

GRANT ALL privileges ON mydb.* TO your-username-here@localhost;

or

GRANT ALL PRIVILEGES ON mydb.* TO 'your-username-here'@localhost WITH GRANT OPTION;

Flushing privileges

FLUSH PRIVILEGES; Always flush privileges after making changes to the database.