Mysql

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

Login as root mysqladmin -u root -p

= Playing 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

= 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';

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


 * References