MySql - Command-line MySQL setup

Command-line MySQL Setup

The MySQL command-line interface (CLI) is a powerful tool that allows database administrators and developers to manage databases, execute queries, and perform administrative tasks using only text-based commands. This guide provides an in-depth walkthrough of setting up and working with MySQL from the command line.

1. Introduction to MySQL CLI

MySQL CLI is the default command-line tool provided with the MySQL installation. It allows you to interact with the MySQL server directly without needing a graphical interface like MySQL Workbench.

Benefits of using the MySQL CLI:

• Lightweight and fast
• Ideal for scripting and automation
• Remote server management over SSH
• Compatible across Windows, macOS, and Linux

2. Accessing the MySQL CLI

2.1 Logging into MySQL

To access the MySQL CLI, open your terminal or command prompt and use the following command:

mysql -u root -p

This command does the following:

• -u root: Connects as the root user
• -p: Prompts for a password

After entering your password, you'll enter the MySQL prompt, typically denoted by mysql>.

2.2 Connecting to a Remote MySQL Server

mysql -h host_address -u username -p

Example:

mysql -h 192.168.1.100 -u admin -p

3. MySQL CLI Structure and Navigation

3.1 CLI Prompt

Once logged in, the prompt looks like:

mysql>

3.2 Command Syntax

All SQL commands end with a semicolon ;. Pressing enter without it will keep the statement open.

3.3 Exiting MySQL CLI

exit;
quit;

4. Database Operations

4.1 Creating a Database

CREATE DATABASE mydatabase;

4.2 Listing Databases

SHOW DATABASES;

4.3 Using a Database

USE mydatabase;

4.4 Deleting a Database

DROP DATABASE mydatabase;

5. Table Management

5.1 Creating Tables

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5.2 Viewing Tables

SHOW TABLES;

5.3 Table Structure

DESCRIBE users;

5.4 Altering Tables

ALTER TABLE users ADD age INT;

5.5 Dropping Tables

DROP TABLE users;

6. User Management

6.1 Creating a User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'securepassword';

6.2 Granting Permissions

GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';

6.3 Viewing Users

SELECT user, host FROM mysql.user;

6.4 Revoking Permissions

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'newuser'@'localhost';

6.5 Deleting a User

DROP USER 'newuser'@'localhost';

7. CRUD Operations (Create, Read, Update, Delete)

7.1 Insert Data

INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

7.2 Read Data

SELECT * FROM users;

7.3 Update Data

UPDATE users 
SET email = 'newemail@example.com' 
WHERE username = 'john_doe';

7.4 Delete Data

DELETE FROM users WHERE username = 'john_doe';

8. Importing and Exporting Data

8.1 Exporting a Database

mysqldump -u root -p mydatabase > mydatabase.sql

8.2 Importing a Database

mysql -u root -p mydatabase < mydatabase.sql

9. Viewing and Analyzing Data

9.1 Filtering with WHERE

SELECT * FROM users WHERE age > 25;

9.2 Sorting with ORDER BY

SELECT * FROM users ORDER BY created_at DESC;

9.3 Limiting Results

SELECT * FROM users LIMIT 5;

9.4 Grouping with GROUP BY

SELECT age, COUNT(*) FROM users GROUP BY age;

10. Advanced Configuration

10.1 Customizing Prompt

mysql --prompt="\\u@\\h [\\d]> "

10.2 Using Configuration Files

Set login credentials and default databases in ~/.my.cnf:

[client]
user=root
password=yourpassword

10.3 Scripting

MySQL scripts can be run using the source or \. command:

source /path/to/script.sql;

11. Securing MySQL

11.1 Run mysql_secure_installation

On Unix/Linux, run:

sudo mysql_secure_installation

This script helps you:

• Set root password
• Remove anonymous users
• Disallow root login remotely
• Remove test database
• Reload privilege tables

11.2 Change Root Password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';

11.3 Disable Remote Root Login

Edit the my.cnf file and bind to localhost:

[mysqld]
bind-address = 127.0.0.1

12. Performance Monitoring

12.1 Show Processlist

SHOW PROCESSLIST;

12.2 Status Information

SHOW STATUS;

12.3 Query Performance

EXPLAIN SELECT * FROM users WHERE age > 25;

12.4 Slow Query Log

Enable and check slow queries to identify performance issues.

13. Backup and Restore

13.1 Full Backup

mysqldump -u root -p --all-databases > all_databases.sql

13.2 Restore

mysql -u root -p < all_databases.sql

Using the MySQL CLI allows for powerful and flexible database management. From setting up databases and managing users to querying and securing your data, the command line provides full control over MySQL. Although it may seem intimidating at first, mastering the CLI leads to a deeper understanding of database systems and enables automation, efficiency, and precision.

Continue to explore MySQL documentation, experiment with queries, and develop good practices to become proficient in MySQL command-line operations.