Difference between revisions of "MySQL/MariaDB Reference"

From The Linux Source
Jump to: navigation, search
m
Line 1: Line 1:
*List Databases*
+
*List Databases
 
   mysql> show databases;
 
   mysql> show databases;
  
*List Tables*
+
*List Tables
 
   mysql> show tables;
 
   mysql> show tables;
  
*Change Passwords*
+
*Change Passwords
 
   # mysqladmin -u root password 'new-password'
 
   # mysqladmin -u root password 'new-password'
 
Note: the mysqladmin command should be run from a shell script, so as to not have this critical password exposed in the command history - make sure to delete the shell script after running
 
Note: the mysqladmin command should be run from a shell script, so as to not have this critical password exposed in the command history - make sure to delete the shell script after running
  
*Delete Database*
+
*Delete Database
 
   mysql> drop database test;
 
   mysql> drop database test;
  
*Creating a new DB and assigning permissions*
+
*Creating a new DB and assigning permissions
  
 
Command line/scripted
 
Command line/scripted
Line 24: Line 24:
 
   mysql> grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
 
   mysql> grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
  
*Table Structure*
+
*Table Structure
 
  describe SystemEvents;
 
  describe SystemEvents;
  
*View Data (Some Query Examples)*
+
*View Data (Some Query Examples)
 
  select * from user;
 
  select * from user;
 
  select Host,User,Grant_priv from user;
 
  select Host,User,Grant_priv from user;
Line 34: Line 34:
 
  select * from SystemEvents where SysLogTag like '%[%' limit 5;  
 
  select * from SystemEvents where SysLogTag like '%[%' limit 5;  
  
*Modify Data*
+
*Modify Data
 
  update SystemEvents set SysLogTag='CROND:' where SysLogTag='CROND';
 
  update SystemEvents set SysLogTag='CROND:' where SysLogTag='CROND';
 
  update SystemEvents set SysLogTag='sshd:' where SysLogTag like 'sshd[%';
 
  update SystemEvents set SysLogTag='sshd:' where SysLogTag like 'sshd[%';
  
*Delete From Table*
+
*Delete From Table

Revision as of 14:39, 13 June 2017

  • List Databases
 mysql> show databases;
  • List Tables
 mysql> show tables;
  • Change Passwords
 # mysqladmin -u root password 'new-password'

Note: the mysqladmin command should be run from a shell script, so as to not have this critical password exposed in the command history - make sure to delete the shell script after running

  • Delete Database
 mysql> drop database test;
  • Creating a new DB and assigning permissions

Command line/scripted

 # mysqladmin -p create wikidb
 # echo "grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';" | mysql -p

Note: the echo command should be run from a shell script, so as to not have a critical password exposed in the command history - make sure to delete the shell script after running

mySQL command line - note: this is added to the mysql command history (not a good idea to leave the password exposed):

 # mysql -p
 mysql> create database wikidb;
 mysql> grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
  • Table Structure
describe SystemEvents;
  • View Data (Some Query Examples)
select * from user;
select Host,User,Grant_priv from user;
select * from SystemEvents where SysLogTag='CROND';
select * from SystemEvents limit 5;
select * from SystemEvents where SysLogTag like '%[%' limit 5; 
  • Modify Data
update SystemEvents set SysLogTag='CROND:' where SysLogTag='CROND';
update SystemEvents set SysLogTag='sshd:' where SysLogTag like 'sshd[%';
  • Delete From Table