MySQL Data Retrieval

MySQL Data Retrieval using Select Statements

How to retrieve data in MySQl database,retrieving data is carried out by Select statements.Igive you example of the basic retrieval syntax.

SELECT   <attribute list>

FROM   <table list>

WHERE  <condition>

Example Simple Select queries

Table” Student”

 

To select the content of the columns named "LastName" and "Age" from the table above.

We use the following SELECT statement:

 SELECT <column> FROM <table>

SELECT Last_name,Age FROMStudent

    

The result goes like this:

The asterisk (*) is a quick way of selecting all columns!

 SELECT * FROM Student

 the result goes like this:
 

Example of  Select Distinct Syntax

SELECT DISTINCT column_name(s)
FROM table_name

Select P_id From Student

 

Example of MySQL WHERE Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

SELECT * FROM Student

WHERE Age='18'

 The result is this:

I  hope this will help you.

Learn MySQL Access Privileges and Account Management

Mysql Access Privileges

creating user in mysql  

>CREATE USER 'mcmc'@'localhost' IDENTIFIED BY 'mcdocute';

Accessing Mysql using  the newly created user

                 >mysql  -mcmc -p

 Syntax how to change password in MySQL

-->SET PASSWORD FOR 'juan'@'%.loc.gov' = PASSWORD('newpass');

GRANT ALL ON db1.* TO 'mcmc'@'localhost';= allowing mcmc to access batabase name db1.

GRANT SELECT ON db2.invoice TO 'mcdo'@'localhost';=allowing mcmc to only performed select to database name db2.

GRANT USAGE ON *.* TO 'mcmc'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;= allowing user mcmc to database with only limited of 90 queries in an our

REVOKE INSERT ON *.* FROM 'mcmc'@'localhost';= revoke means user mcmc is not allowed to insert in the database.




Account Management

Permission  Description

Select_priv  =Permission to run SELECT queries

Insert_priv = Permission to run INSERT statements

Update_priv = Permission to run UPDATE statements

Delete_priv = Permission to run DELETE statements

Create_priv = Permission to CREATE tables and databases

Drop_priv = Permission to DROP tables and databases

Reload_priv = Permission to RELOAD the database (a FLUSH statement for example)

Shutdown_priv = Permission to SHUTDOWN the database server

Process_priv = Permission to view or kill PROCESSes.

File_priv = Permission to read and write FILEs (for example LOAD DATA INFILE)

Grant_priv = Permission to GRANT available permissions to other users

References_priv = Permissions to create, modify or drop INDEXes

  Alter_priv= Permission to ALTER table structures.

Examples: 

Before you can make a query in a database make sure that you use or you access the database  by using this code:

USE mysql;


    

DESCRIBE user;  this code is use to describe the user

DESC host; this code is use to describe host

 DESC tables_priv;

DESC columns_priv;
 

DESC db;
this code show the database user and the privileges.
 

 

 SELECT host,db,select_priv,insert_priv FROM host;

SELECT host,user,select_priv,insert_priv FROM user;
 

SELECT host,db,user,select_priv,insert_priv FROM db;
 this code shows the  

SELECT host, user FROM user;
this code show the different host and users

Security Guidelines

       Security is a major concern for the modern age systems/network/database administrators. It is natural for an administrator to worry about hackers and external attacks while implementing security. But there is more to it. It is essential to first implement security within the organization, to make sure right people have access to the right data. Without these security measures in place, you might find someone destroying your valuable data, or selling your company's secrets to your competitors or someone invading the privacy of others. Primarily a security plan must identify which users in the organization can see which data and perform which activities in the database.
BY: Narayana Vyas Kondreddi


Practicing Security Guidelines and tips gives you security to your database and data.

1.      Only gives to MySQL root account the permission to access the user tables.

2.      You should learn to manipulate MySQL System privilege.

3.      Don’t ever use plain-text password.

4.      Remember your password.

5.      Use fire wall.

6.      Do not transmit plain (unencrypted) data over the Internet.
7.  Install anti-virus software on the SQL Server computer, but exclude your database folders from regular scans. Keep your anti-virus signature files up to date.
8.  Restrict physical access to the SQL Server computer. Always lock the server while not in use.  

MySQL no-install

MySQL no-install

                How to use MySQL without installing it to your computer.  I will show to how you can do it.

First download MySQL no-install, download the latest version of it.

Second extract the install archive to the chosen installation location using your preferred Zip archive tool. Some tools may extract the archive to a folder within your chosen installation location. If this occurs, you can move the contents of the subfolder into the chosen installation location.

Third you need do create an option file If your PC uses a boot loader where C: is not the boot drive, your only option is to use the my.ini file. Whichever option file you use, it must be a plain text file example notepad.An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is in E:\mydata\data, you can create an option file containing a [mysqld] section to specify values for the basedir and datadir options:

[mysqld]

basedir="E:\mysql"

datadir="E:\mydata\data"

Place this option file in C:\Windows

If you would like to use a data directory in a different location, you should copy the entire contents of the data directory to the new location. For example, if MySQL is installed in C:\Program Files\MySQL\MySQL Server 5.0, the data directory is by default in C:\Program Files\MySQL\MySQL Server 5.0\data. If you want to use E:\mydata as the data directory instead.

1. create a mydata folder or directory to E: and copy the entire data folder inside it from .

And that it you can now use MySQL;

Ho to use MySQL

1.       Open your command prompt and open a server using  >mysqld  -u root

2.       Open one more command prompt and open client using >mysql –u root

And that's it

Enjoy!!!!

Check, Repair, Optimize, Analyze MySQL tables

Checking and Repairing MySQL Tables

                Every Database Administrator has a worst nightmare and that is when their data or database has been corrupted and this is a very big problem. This will affect the whole operation of a company.

                But if your database is MySQL luckily there’s still hope to retrieve or recover your database, by the use if the built-in MySQL Recovery tool to check and try to recover your corrupted database.

                Two MySQL Built-in commands you can use to recover corrupted database.

1.       The MySQL distribution ships with a tool called "myisamchk," designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line.

2.       In addition to the tool above, MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE, REPAIR TABLE, and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client.

I will show you how to use MySQL commands. The CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE commands.

1.       Open  your MS Command Prompt and open your MySQL  server using

>mysqld

                2. Then open new command prompt and open mysql using

                      >mysql –u root

                3. Then choose the database you want to repair

                >use [database name]

4. And then you can now use the MySQL Administrator commands for checking repairing and optimize tables.

                >check table [table name] = If you're having trouble accessing a table you can use this command to check errors

>repair table [table name] =  If your check reveals errors, or if you see cryptic error messages about table                      handlers or corrupt indexes, then you need to try repairing the table.

>optimize table [table name]= OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

>analyze table [table name] = ANALYZE TABLE analyzes and stores the key distribution for a table.

               

LOGGING

LOGGING

                MySQL has many different types of Log Files. This log files is activated when you use and configure my.ini  as follows:

log-error=[name].log

log=[name].log

log-bin=[name].log

log-slow-queries=[name].log

1. The error log this is the place to look for problems with starting, running, or stopping MySQL.

2. The query logs all connections and executed queries are logged here.

3. The binary update log All SQL statements that change data are stored.

4. The slow query log all queries that took more than long_query_time to execute, or that didn't make use of any indexes, are logged here.

The Error Log

                MySQL error log is called mysql.err on windows and it is located in the data directory where your MySQL where found, usually in (C:\mysql\bin).

It contains startup and shutdown information and any critical errors that occur while running. It will log if the server died and was automatically restarted or if MySQL notices that a table needs to be automatically checked or repaired. The log may also contain a stack trace when MySQL dies. A sample error log follows:

120709 14:51:14 [Note] Plugin 'FEDERATED' is disabled.

120709 14:51:14  InnoDB: Initializing buffer pool, size = 8.0M

120709 14:51:14  InnoDB: Completed initialization of buffer pool

120709 14:51:14  InnoDB: Started; log sequence number 0 44233

120709 14:51:14 [Note] Event Scheduler: Loaded 0 events

120709 14:51:14 [Note] mysqld: ready for connections.

Version: '5.1.63-community-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

120709 15:02:44 [Note] mysqld: Normal shutdown

The Binary Log

                The binary update logs contain all the SQL statements that update the database, as well as how long the query took to execute and a timestamp of when the query was processed. Statements are logged in the same order as they are executed (after the query is complete but before transactions are completed or locks removed). Updates that have not yet been committed are placed in a cache first.

The binary update log is also useful for restoring backups and for when you are replicating a slave database from a master.

Binary update logs start with an extension 001. The binary update index file contains a list of all the binary logs used to date. A sample could be as follows:

.\mcbin.000001

.\mcbin.000002

.\mcbin.000003

You can delete all the unused binary update logs with RESET MASTER:

mysql> RESET MASTER;

Query OK, 0 rows affected (0.00 sec)

View using a text  editor:

þbinbúO   f   j    

5.1.63-community-log                              búO8

   S ­€úO   S   ½           @          std

mcdo create database mcdoé€úO   Q             @          std

mcdo drop database mcdoIúO   S   a         

@          std

mcdo create database mcdo‚úOt   

But you can see this using mysqlbinlog:

C:\Program Files\MySQL\data>..\bin\mysqlbinlog mcbin.000003

The Slow Query Log

                All SQL statements that take longer to execute than long_query_time are logged.

A sample slow query log follows:

mysqld, Version: 5.1.63-community-log (MySQL Community Server (GPL)). started with:

TCP Port: 0, Named Pipe: (null)

Time                 Id Command    Argument

mysqld, Version: 5.1.63-community-log (MySQL Community Server (GPL)). started with:

TCP Port: 0, Named Pipe: (null)

Time                 Id Command    Argument

mysqld,

The General Query Log

General query log established a connection between clients.

                --log[=file_name] or -l [file_name]

A sample general query log follows:

Query   create database pogi

120709 15:12:14                    1 Query            create database pogi

120709 15:12:33                    1 Query            show databases

120709 15:12:49                    1 Query            create database pogi

120709 15:12:56                    1 Quit