Mysql

Other articles

Mysql/CheatSheet

List Databases

mysql -u root -p
show databases;
 
mysql -u root -p -e 'show databases;'

echo "show databases;" | mysql

Create Database

CREATE DATABASE database_name;

Dump Database

mysqldump -u root -p databasename > databasename.sql

mysqldump -u root -p --quick --add-drop-table --complete-insert --allow-keywords databasename > databasename.sql

Dump Database from Docker

docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql

Import Database

mysql -u root -p databasename < databasename.sql

Import Database in Docker

docker exec -i mysql-container mysql -uuser -ppassword name_db < data.sql

docker-compose:

    volumes:
        - ./dump.sql:/docker-entrypoint-initdb.d/dump.sql

User Management

List all Users

SELECT user, host FROM mysql.user;

Create User

CREATE USER 'username'@'localhost' INDENTIFIED BY 'password'
CREATE USER 'username'@'%' IDENTIFIED BY 'password'

Change User Password

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';

Grant Access

GRANT ALL PRIVILIGES ON *.* to 'username'@'localhost' WITH GRANT OPTION;

Show Access Rights

SHOW GRANTS FOR 'username'@'localhost';