Mysql

=Installation= For Ubuntu: sudo apt-get install mysql-server

Login as root mysqladmin -u root -p

= 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 tutorial_author = 'Aman'; SELECT * from book_tbl WHERE tutorial_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

= 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] host=10.0.1.100 user=root password=pwd@123 [mysql] pager=/usr/bin/less
 * 1) for local server use localhost
 * 2) host=localhost

mysql -u root -ppwd@123 -e 'select * from MasterDB where sr='71111121';' codered
 * Running query from Shell directly:

https://github.com/amanjosan2008/ImageDB
 * Inserting Image to DB, source code can be found at my GIT Hub:


 * References