Configuring the CLI Prompt

To temporarily modify the MySQL prompt during a session, execute the prompt command:

mysql> prompt \u@mycluster \r:\m:\s-> 

For a persistent configuration, append the prompt directive under the [mysql] section in the my.cnf configuration file:

[mysql]
prompt=\u@mycluster \r:\m:\s-> 

Establishing Database Connections

Connecting to a remote instance via TCP/IP:

mysql -h 192.168.1.50 -u admin -P 3306 -p

Connecting to a local instance using a socket file:

mysql -h localhost -u root -S /var/lib/mysql/mysql.sock

Modifying User Credentials

Updating a password using the ALTER USER statement:

mysql> ALTER USER 'admin'@'localhost' IDENTIFIED BY 'newSecurePass';

Updating a password via the mysqladmin utility:

mysqladmin -u admin password "newSecurePass"

If the user already has a password set:

mysqladmin -u admin -p currentPass password "newSecurePass"

Directly modifying the privilege tables (applicable to older MySQL versions):

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('newSecurePass') WHERE User='admin';
mysql> FLUSH PRIVILEGES;

Recovering a Lost Root Password

Restart the database service in safe mode, bypassing the grant tables:

mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --user=mysql &

Connect without a password and update the credentials:

mysql -u root
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('recoveryPass') WHERE user='root';
mysql> FLUSH PRIVILEGES;

Shut down the instance and restart it normally:

mysqladmin -h127.0.0.1 -uroot -p shutdown

Routine Database Operations

Querying and sorting existing users:

mysql> SELECT User, Host FROM mysql.user ORDER BY User;

Removing an empty or unwanted user:

mysql> DROP USER ''@'localhost';

Creating databases with specific character sets. Using utf8mb4 is highly recommended to prevent character encoding issues:

mysql> CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verifying the database character set configuration:

mysql> SHOW CREATE DATABASE app_db\G

Viewing available databases and switching context:

mysql> SHOW DATABASES;
mysql> USE app_db;
mysql> SELECT DATABASE();

Checking system information:

mysql> SELECT VERSION();
mysql> SELECT CURRENT_USER();
mysql> SELECT NOW();

Removing a database:

mysql> DROP DATABASE app_db;

User Privilege Management

Creating a new user with a password:

mysql> CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'userPass123';

Creating a user and granting specific privileges simultaneously:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost' IDENTIFIED BY 'userPass123';

Adding privileges to an existing user:

mysql> GRANT SELECT ON app_db.* TO 'app_user'@'localhost';

Granting remote access from a specific subnet:

mysql> CREATE USER 'remote_user'@'10.0.0.%' IDENTIFIED BY 'remotePass';

Reviewing assigned privileges:

mysql> SHOW GRANTS FOR 'app_user'@'localhost';

Revoking specific privileges (e.g., removing DELETE access):

mysql> REVOKE DELETE ON app_db.* FROM 'app_user'@'localhost';

Table Management

Defining a new table structure:

mysql> CREATE TABLE inventory (
    item_id INT NOT NULL AUTO_INCREMENT,
    item_name VARCHAR(100) NOT NULL,
    supplier_name VARCHAR(40) NOT NULL,
    release_date DATE,
    PRIMARY KEY (item_id)
);

Inspecting table structures:

mysql> DESC inventory;
mysql> SHOW CREATE TABLE inventory\G