mySQL Operation:

[last updated: 2021-05-09]
go to: mySQL home page:
-----

• Contents on this page:
• Find current user name:
• Show all defined users:
• Create a new user:
• Delete (drop) a user:
• Create and delete databases:
• Show databases:
• Show all fields/columns in a table:
• Show Privileges for Current User:
• Show Privileges for other users:

• Available Commands:

#####################

• Find your username:
  mysql> select user(); or
  mysql> select current_user();

-----------------------------------

• show all defined users:
  mysql> select user, host from mysql.user;
  This shows I currently have 5 users defined (all with localhost):
debian-sys-maint
mysql.session
mysql.sys
phpmyadmin
root

-----------------------------------

• Create a new user:
  • [the previously used 'grant' command is being deprecated and you should use 'create user' instead...]
  • mysql> create user [userName];
    this creates: [userName]@'%'
  • mysql> create user tmp01@localhost;
    OR: mysql> create user 'tmp01'@'localhost';
    both seem to create tmp01@localhost
  • Create a new user with password:
    mysql> create user [userName] identified by '[password]' ;

  -----------------------------------

• Delete a user:
  mysql> DROP USER 'jeffrey'@'localhost';

-----------------------------------

• Create & Delete Databases:
• mysql> create database salesdata; ... creates a new database named salesdata
  returns: "Query OK, 1 row affected (0.00 sec)"
• mysql> drop database salesdata; ... deletes the newly created database

-----------------------------------

• mysql> show databases;
  will list all databases, including defaults shipped with the installation plus any created since installation.
My tower installation at this time has, among others:
mysql
phpmyadmin
drupal
• The mysql database includes data on users and their passwords, and the databases they have access to.
• Use the grant command to add users to this database and specify the databases to which they will have access.
• NOT? Use the mysqladmin command to set or change the password for the root user.

-----------------------------------

• show all fields in mysql table:
  mysql> desc mysql-dot-user;
  OR:
  mysql> SHOW columns FROM mysql-dot-user;

-----------------------------------

• show privileges for Current User:
  mysql> show grants; ... shows privs for current user
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

-----------------------------------

• show privileges for other users: (eg. phpmyadmin)
  mysql> show grants for 'phpmyadmin'@'localhost';
  +--------------------------------------------------------------------+
  | Grants for phpmyadmin@localhost |
  +--------------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' |
  | GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost' |
  +--------------------------------------------------------------------+

  -----------------------------------

• Grant privileges to a user:
  mysql> GRANT ALL PRIVILEGES ON [database_name].* TO '[username]'@'localhost';

-----------------------------------

• Available Commands:
  from $ command line:
  • /usr/sbin/mysqld
  • /usr/bin/...
    mysql
    mysqladmin
    mysqlanalyze
    mysqlbinlog
    mysqlcheck
    mysql_config_editor
    mysqld_multi
    mysqld_safe
    mysqldump
    mysqldumpslow
    mysql_embedded
    mysqlimport
    mysql_install_db
    mysqloptimize
    mysql_plugin
    mysqlpump
    mysqlrepair
    mysqlreport
    mysql_secure_installation
    mysqlshow
    mysqlslap
    mysql_ssl_rsa_setup
    mysql_tzinfo_to_sql
    mysql_upgrade
    mysql-workbench

  .
  
  .
  
  .
  

  eof