MySQL

As a MySQL system administrator, you might need to perform various administrative tasks to manage and maintain the MySQL server efficiently. Here are some essential MySQL system admin commands:

1.Starting and Stopping MySQL Server :

   # Start MySQL Server

   sudo systemctl start mysql

   # Stop MySQL Server

   sudo systemctl stop mysql

   # Restart MySQL Server

   sudo systemctl restart mysql

   # Check MySQL Server Status

   sudo systemctl status mysql

2.Logging in to MySQL Server :

   # Log in as root user (if password is set)

   mysql -u root -p

   # Log in as root user (without password)

   mysql -u root

   # Log in as a specific user

   mysql -u username -p

3.Creating and Managing Users :

   # Create a new user

   CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

   # Grant privileges to a user on a specific database

   GRANT privileges ON database_name.* TO 'username'@'localhost';

   # Remove privileges from a user

   REVOKE privileges ON database_name.* FROM 'username'@'localhost';

   # Delete a user

   DROP USER 'username'@'localhost';

4.Backup and Restore :

   # Create a backup (mysqldump)

   mysqldump -u username -p database_name > backup.sql

   # Create a backup of all databases (mysqldump)

   mysqldump -u your_username -p --all-databases --events > all_databases.sql

   # Restore a backup

   mysql -u username -p database_name < backup.sql

5.Managing Databases :

   # Create a new database

   CREATE DATABASE database_name;

   # Delete a database

   DROP DATABASE database_name;

   # Show all databases

   SHOW DATABASES;

6.Managing Tables :

   # Create a new table

   CREATE TABLE table_name (

       column1 datatype constraint,

       column2 datatype constraint,

       ...

   );

   # Delete a table

   DROP TABLE table_name;

   # Show the structure of a table

   DESCRIBE table_name;

7.MySQL Configuration File :

   The MySQL configuration file (`my.cnf` or `my.ini` depending on the system) is used to configure MySQL server settings. The location of the file may vary based on your operating system and installation method.

8.MySQL Error Log :

   The MySQL error log contains information about server errors, warnings, and issues. You can find the log in the MySQL data directory or as specified in the MySQL configuration file.

9.MySQL Slow Query Log :

   The slow query log records queries that take longer than a certain threshold to execute. It helps identify performance issues. Enable it in the MySQL configuration file.

10.MySQL User Privileges :

    MySQL has various user privileges to control what operations users can perform on databases and tables. Common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and more.

11.To check the list of MySQL users

SELECT User, Host FROM mysql.user;

These are some of the essential MySQL system admin commands. As a system administrator, you may also need to perform other tasks like managing server resources, monitoring performance, securing the database server, and more, depending on your specific environment and requirements.