Mysql

From Network Security Wiki



Installation

For Ubuntu: 

sudo apt-get install mysql-server

Change new password: 

mysql_secure_installation

If default password not working, check credentials in below file: 

sudo cat /etc/mysql/debian.cnf

Login as root: 

mysql -u root -p

If above fails, try: 

sudo mysql -u root -p

And then updating the password using: 

$ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

Once this is done stop and start the mysql server. 

sudo service mysql stop
sudo service mysql start

Working with Databases

Creating Databases

Create a Database: 

mysqladmin -u root -p create testdb

Or 

create database testdb;

Check Databases: 

mysql -u root -p
show databases;

Select a Database and check its tables: 

use testdb;
show tables;

Checking Table contents: 

show columns from column_name;

Creating Tables

Generic SQL syntax to create a MySQL table 

CREATE TABLE table_name (column_name column_type);

 CREATE TABLE book_tbl(
 book_id INT NOT NULL AUTO_INCREMENT,
 book_title VARCHAR(100) NOT NULL,
 book_author VARCHAR(40) NOT NULL,
 submission_date DATE,
 PRIMARY KEY ( book_id )
 );

Inserting Data

 INSERT INTO book_tbl
 (book_title, book_author, submission_date)
 VALUES
 ("Learn MySQL", "Aman", NOW());

Fetching Data

SELECT * from book_tbl;
SELECT * from book_tbl WHERE book_author = 'Aman';
SELECT * from book_tbl WHERE book_author LIKE '%man';

Updating Data

 UPDATE book_tbl 
   SET book_title = 'Learning JAVA' 
   WHERE book_id = 3;

Sorting Data

SELECT * from book_tbl ORDER BY book_author ASC
SELECT * from book_tbl ORDER BY book_author DESC

Joins

Inner Join

        This section is under construction.

Left Join

        This section is under construction.

User Administration

Creating a user: 

CREATE USER test IDENTIFIED BY 'test123';

Grant access to a DB to a user created on fly: 

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON testdb.* TO 'guest'@'localhost' IDENTIFIED BY 'guest123';

Connecting to a database directly: 

mysql testdb -u guest -pguest123

Access Control

Allows the user guest to connect to the database. 

grant usage on . to guest@localhost identified by 'guest123';

Allows the user guest from the localhost full access to testdb: 

grant all privileges on testdb.* to mguest@localhost;

Dumping Databases

Export DB 

mysqldump testdb > testdb.sql -p

Import DB 

mysql testdb < /var/www/html/test.com/testdb.sql -p

External Connections

The default port of the database server is 3306 If you are unable to connect to the database, try disabling the IPTables: 

iptables -F

If still unable to get access from Network:

Check if mysqld is listening on 0.0.0.0 or 127.0.0.1: 

netstat -lntp

If it is listening on 127.0.0.1, Edit binding in below file: 

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
bind-address            = 127.0.0.1
to
bind-address            = 0.0.0.0

Grant network access to user: 

CREATE USER 'aman'@'%' IDENTIFIED BY 'test123';
GRANT ALL PRIVILEGES ON *.* TO 'aman'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

PHPMySQL

Installation 

sudo apt-get install phpmyadmin php-mbstring php-gettext

Enable the PHP extensions: 

sudo phpenmod mcrypt
sudo phpenmod mbstring

Restart apache service: 

sudo systemctl restart apache2

Access the application: 

https://10.1.1.1/phpmyadmin

Run below command if you get Not Found error: 

sudo ln -s /usr/share/phpmyadmin /var/www/html/

Query Log

TO figure out the query being sent to mysql server: 

mysql -u root -p
mysql> SET GLOBAL log_output = "FILE";
mysql> SET GLOBAL general_log_file = "/var/log/mysql/query.log";
mysql> SET GLOBAL general_log = 'ON';

Misc

  • To avoid password prompt create ~/.my.cnf file as follows:
nano ~/.my.cnf

[client]
# for local server use localhost
#host=localhost
host=10.0.1.100
user=root
password=pwd@123
[mysql]
pager=/usr/bin/less
  • Running query from Shell directly:
mysql -u root -ppwd@123 -e 'select * from MasterDB where sr='71111121';' codered
  • Inserting Image to DB, source code can be found at my GIT Hub:
https://github.com/amanjosan2008/ImageDB



References





{{#widget:DISQUS |id=networkm |uniqid=Mysql |url=https://aman.awiki.org/wiki/Mysql }}

Retrieved from "https://aman.awiki.org/wiki/Mysql?oldid=4054"