🖥️mysql

#                ███╗   ███╗██╗   ██╗███████╗ ██████╗ ██╗     
#                ████╗ ████║╚██╗ ██╔╝██╔════╝██╔═══██╗██║     
#                ██╔████╔██║ ╚████╔╝ ███████╗██║   ██║██║     
#                ██║╚██╔╝██║  ╚██╔╝  ╚════██║██║▄▄ ██║██║     
#                ██║ ╚═╝ ██║   ██║   ███████║╚██████╔╝███████╗
#                ╚═╝     ╚═╝   ╚═╝   ╚══════╝ ╚══▀▀═╝ ╚══════╝
                                                             

# To connect to a database
mysql -h localhost -u root -p

# To backup all databases
mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql

# To restore all databases
mysql -u root -p  < ~/fulldump.sql

# To create a database in utf8 charset
CREATE DATABASE owa CHARACTER SET utf8 COLLATE utf8_general_ci;

# To add a user and give rights on the given database
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;

# To list the privileges granted to the account that you are using to connect to the server. Any of the 3 statements will work. 
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

# Basic SELECT Statement
SELECT * FROM tbl_name;

# Basic INSERT Statement
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

# Basic UPDATE Statement
UPDATE tbl_name SET col1 = "example";

# Basic DELETE Statement
DELETE FROM tbl_name WHERE user = 'jcole';

#==============================##==============================#
# CMD MYSQL						       #
#==============================##==============================#

[root@ipam ~]# mysql -u root -p
Enter password:
mysql> create database phpipam;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL on phpipam.* to phpipam@localhost identified by ‘phpipamadmin’;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

# Backup IP address table, remove backups older than 10 days
@daily /usr/bin/mysqldump -u ipv6 -pipv6admin phpipam > <ipam_dir>/db/bkp/phpipam_bkp_$(date +"\%y\%m\%d").db
@daily /usr/bin/find <ipam_dir>/db/bkp/ -ctime +10 -exec rm {} \;

# Import SQL Dumps:
mysql -uroot -pPasswort < sql.file

# How to remove a MySQL

mysql> drop database `my-database`;

# Create User in MySQL/MariaDB 
	CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

# then to grant all access to the database (e.g. my_db), use GRANT Syntax, e.g.
	GRANT ALL ON my_db.* TO 'new_user'@'localhost';

# Where ALL (priv_type) can be replaced with specific privilege such as SELECT, INSERT, UPDATE, ALTER, etc.
# Then to reload newly assigned permissions run:
	FLUSH PRIVILEGES;

Shell integration
For people not familiar with MySQL syntax, here are handy shell functions which are easy to remember and use (to use them, you need to load the shell functions included further down).

Here is example:

$ mysql-create-user admin mypass
| CREATE USER 'admin'@'localhost' IDENTIFIED BY 'mypass'

$ mysql-create-db foo
| CREATE DATABASE IF NOT EXISTS foo

$ mysql-grant-db admin foo
| GRANT ALL ON foo.* TO 'admin'@'localhost'
| FLUSH PRIVILEGES

$ mysql-show-grants admin
| SHOW GRANTS FOR 'admin'@'localhost'
| Grants for admin@localhost                                                                                   
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
| GRANT ALL PRIVILEGES ON `foo`.* TO 'admin'@'localhost'

$ mysql-drop-user admin
| DROP USER 'admin'@'localhost'

$ mysql-drop-db foo
| DROP DATABASE IF EXISTS foo
To use above commands, you need to copy&paste the following functions into your rc file (e.g. .bash_profile) and reload your shell or source the file. In this case just type source .bash_profile:

# Create user in MySQL/MariaDB.
mysql-create-user() {
  [ -z "$2" ] && { echo "Usage: mysql-create-user (user) (password)"; return; }
  mysql -ve "CREATE USER '$1'@'localhost' IDENTIFIED BY '$2'"
}

# Delete user from MySQL/MariaDB
mysql-drop-user() {
  [ -z "$1" ] && { echo "Usage: mysql-drop-user (user)"; return; }
  mysql -ve "DROP USER '$1'@'localhost';"
}

# Create new database in MySQL/MariaDB.
mysql-create-db() {
  [ -z "$1" ] && { echo "Usage: mysql-create-db (db_name)"; return; }
  mysql -ve "CREATE DATABASE IF NOT EXISTS $1"
}

# Drop database in MySQL/MariaDB.
mysql-drop-db() {
  [ -z "$1" ] && { echo "Usage: mysql-drop-db (db_name)"; return; }
  mysql -ve "DROP DATABASE IF EXISTS $1"
}

# Grant all permissions for user for given database.
mysql-grant-db() {
  [ -z "$2" ] && { echo "Usage: mysql-grand-db (user) (database)"; return; }
  mysql -ve "GRANT ALL ON $2.* TO '$1'@'localhost'"
  mysql -ve "FLUSH PRIVILEGES"
}

# Show current user permissions.
mysql-show-grants() {
  [ -z "$1" ] && { echo "Usage: mysql-show-grants (user)"; return; }
  mysql -ve "SHOW GRANTS FOR '$1'@'localhost'"
}

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

# Example:
[root@phpipam ~] mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use `phpipam`;

Database changed
mysql> update `users`
       set `password` = '$6$rounds=3000$JQEE6dL9NpvjeFs4$RK5X3oa28.Uzt/h5VAfdrsvlVe.7HgQUYKMXTJUsud8dmWfPzZQPbRbk8xJn1Kyyt4.dWm4nJIYhAV2mbOZ3g.'
       where `username` = "admin";

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>quit
Bye
#############################################################################

# Delete all tables of a mysql database
mysql --defaults-file=my.cnf -e 'show tables' | while read t; do mysql --defaults-file=my.cnt  -e 'drop table '$t; done
# Explanation: If you have a root access to the database, a drop database + create database is easiest. This script is useful in situations where you do not have root access to the database.

# First prepare a file my.cnf to store database credentials so you do not have to enter on the command line:
    # [client]
    # database=dbname
    # user=dbuser
    # password=dbpass
    # host=dbhost
# Make sure to protect this file with chmod go-rwx.
# The one-liner will execute show tables on the database to list all tables. Then the while loop reads each table name line by line and executes a drop table command.
# Limitations: The above solution is lazy, because not all lines in the output of show tables are table names, so you will see errors when you run it. But hey, shell scripts are meant to be lazy!

░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

  █║▌│║█║▌★ KALI ★ PARROT ★ DEBIAN 🔴 PENTESTING ★ HACKING ★ █║▌│║█║▌

              ██╗ ██╗ ██████╗  ██████╗ ██╗  ██╗███████╗██████╗
             ████████╗██╔══██╗██╔═══██╗╚██╗██╔╝██╔════╝██╔══██╗
             ╚██╔═██╔╝██║  ██║██║   ██║ ╚███╔╝ █████╗  ██║  ██║
             ████████╗██║  ██║██║   ██║ ██╔██╗ ██╔══╝  ██║  ██║
             ╚██╔═██╔╝██████╔╝╚██████╔╝██╔╝ ██╗███████╗██████╔╝
              ╚═╝ ╚═╝ ╚═════╝  ╚═════╝ ╚═╝  ╚═╝╚══════╝╚═════╝

               █║▌│║█║▌ WITH COMMANDLINE-KUNGFU POWER █║▌│║█║▌

░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░