Use MySQL and MariaDB from the Command Line

Install MySQL or MariaDB

Windows

1. Download MySQL Community Server from the official website.
2. Run the installer and follow the steps.
3. Set a root password during the installation.
4. MySQL then runs as a Windows service.

macOS

brew install mysql
brew services start mysql
mysql_secure_installation

Ubuntu

sudo apt update
sudo apt install mysql-server

(or mariadb-server for MariaDB). The service starts by itself.

CentOS, Rocky and AlmaLinux (MariaDB)

sudo yum update
sudo yum install mariadb-server
sudo systemctl start mariadb
sudo systemctl enable mariadb

Then run the security script and set the root password:

sudo mysql_secure_installation

Log in

mysql -u root -p

Enter the password when asked. To connect to a remote server:

mysql -h server_ip -u username -p

The prompt changes to mysql> or MariaDB [(none)]>. Every command ends with a semicolon.

Common commands

Databases

SHOW DATABASES;
CREATE DATABASE mydb;
USE mydb;
DROP DATABASE mydb;

Tables

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);
SHOW TABLES;
DESCRIBE users;
DROP TABLE users;

Data

INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');
SELECT * FROM users;
UPDATE users SET name = 'Jane Doe' WHERE id = 1;
DELETE FROM users WHERE id = 1;

Users and rights

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword!';
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'appuser'@'localhost';

Leave the client

EXIT;

Run a command or a file without the prompt

mysql -u root -p -e "SHOW DATABASES;"
mysql -u root -p mydb < script.sql

Back up and restore

mysqldump -u root -p mydb > mydb.sql
mysql -u root -p mydb < mydb.sql

Quick recap

• Log in with mysql -u root -p.
• End every statement with ;.
• Use mysqldump for backups.