Mysql

Installing on Linux :

RPM installation:
rpm -i MySQL-server-VERSION.i386.rpm MySQL-client- VERSION.i386.rpm

Source code installation:

• Untar the file
  
• Configure
  
• Make
  
• Make install

Configuring the System:

• The configuration options can be changed in the global options file, /etc/my.cnf
  
• Checking if the System Works: •Should be able to connect to mysql using the command mysql -u root
  

Setting the Root Password:
set password for root@localhost=password(‘'); •If password is set, connect to mysql using command mysql -u root –p

Deleting Anonymous Accounts:
delete from user where User='';
delete from db where User='';
flush privileges;

Creating an Account for Basic Use:
grant create, create temporary tables, delete, execute, index, insert, lock tables, select, show databases, update on *.* to identified by ‘';

MySQL Directory Structure:
The most important directories in your MySQL installation are

• bin : contains the MySQL server and client programs and several other useful compiled programs.
• data : contains actual database data.
• docs (Linux) : contains MySQL documentation in HTML format

Overview of Executables:

• mysqld: the MySQL server
• mysql: the command-line client.
• mysqladmin: Used to perform many administrative functions.
• myisamchk: Used to check and repair damaged MyISAM tables.
• mysqldump: Used to back up databases.
• mysqlbinlog: Used to read the contents of the binary log, essential for disaster recovery.
• mysqlshow: Used to get information about databases and tables.

Overview of User Interfaces:
MySQL Monitor
Command line interface §MySQL Control Center (MySQLCC)
graphical user interface. Can be downloaded from www.mysql.com/downloads/mysqlcc.html phpMyAdmin Web-based interface. Can be downloaded from www.phpmyadmin.net/ •To install phpMyAdmin, a working Web server and PHP installation is needed.

Introduction to the MySQL Monitor

• §mysql -u username -p (Connect to MySQL)
• mysql -h hostname -u username -p (Connect to MySQL on a different machine )
• show databases; (list of databases that exist on the system)
• use databasename; (select a database)
• show tables; (list of tables in a database)
• describe tablename; (information about a table)
• \q (log out of the monitor) §\h (help)
• source filename (run a file of commands)

Case Sensitivity and Identifiers

§

• Database names have the same case sensitivity as directories in the operating system. Table names follow the same rules as filenames. Everything else is case insensitive.
• All identifiers except aliases can be up to 64 characters long. Aliases can be up to 255 characters long.
• Identifiers can contain most characters, but database names may not contain /, \, or . and table names cannot contain . or /.
• Reserved words can be used for identifiers as long as they are within quotes.

Creating & Selecting Database

§

• create database dbname; creates a database.
• use database dbname; selects a database for use.

Creating Tables

§

• create [temporary] table [if not exists] tbl_name [(create_definition,...)] [table_options] [select_statement] (or) create [temporary] table [if not exists] tbl_name like old_table_name; create_definition: col_name type [not null | null] [default default_value] [auto_increment] [primary key] [reference_definition] or primary key (index_col_name,...) or key [index_name] (index_col_name,...) or index [index_name] (index_col_name,...) or unique [index] [index_name] (index_col_name,...) or fulltext [index] [index_name] (index_col_name,...) or [constraint symbol] foreign key [index_name] (index_col_name,...) [reference_definition] or check (expr)

Column Types in MySQL

§

• Exact numeric types are TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, NUMERIC, and DECIMAL.
  
• Approximate numeric types are FLOAT and DOUBLE.
  
• String types are CHAR, VARCHAR, TEXT, and BLOB.
  
• Date and time types are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

Creating indexes

§

• An index is a separate file that is sorted and contains only the fields for sorting. If an index is created on a column, MySQL can find the corresponding record very quickly
  
• Indexing columns improves performance
  
• create [unique|fulltext|spatial] index index_name [index_type] on tbl_name (index_col_name,...) index_col_name: col_name [(length)] [asc | desc] index_type: using {btree | hash}

Deleting Databases, Tables, and Indexes

§

• drop database dbname;
  
• drop table tablename;
  
• index indexname on tablename;
  

Altering Existing Table Structures

§

• alter [ignore] table tbl_name alter_spec [, alter_spec ...] alter_spec: add [column] create_definition [first | after col_name ] or add [column] (create_definition, create_definition,...) or add index [index_name] (index_col_name,...) or add primary key (index_col_name,...) or add unique [index_name] (index_col_name,...) or add fulltext [index_name] (index_col_name,...) or add [constraint symbol] foreign key [index_name] (index_col_name,...) [referenc_e_definition] or alter [column] col_name {set default literal | drop default} or change [column] old_col_name create_definition [first | after col_name] or modify [column] create_definition [first | after col_name] or drop [column] col_name or drop primary key or drop index index_name or disable keys or enable keys or rename [to] new_tbl_name or order by col_name or table_options

Simple SELECTs

§

• The SELECT statement has the following general form: SELECT columns FROM tables [WHERE conditions] [GROUP BY group [HAVING group_conditions]] [ORDER BY sort_columns] [LIMIT limits];
  
• The clause select * retrieves all columns;
  
• The clause select columnname retrieves a particular column.
  
• Tables can be specified as database.table and columns as table.column or database.table.column to avoid ambiguity.
  
• Aliases are alternative names for tables and columns. They can be specified this way: select column as column_alias from table as table_alias;

§

• The WHERE clause is used to select rows matching search criteria.
  
• The keyword DISTINCT removes duplicates from the result set.
  
• The GROUP BY clause treats the rows retrieved group by group. Its chief use is in conjunction with group functions like count().
  
• The HAVING clause is like a WHERE clause for groups.
  
• The ORDER BY clause sorts result rows according to the columns specified.
  
• The LIMIT clause is used to control which rows are returned from the total possible result set. The maximum rows returned and an offset from which to start need to be specified

Inserting Data

§

• String values should be in quotes. Single quotes or backslashes within a string need to be escaped with a backslash.
  
• Add data to tables with the INSERT statement: insert [low_priority | delayed] [ignore] [into] tbl_name [(col_name,...)] values ((expression | default),...),(...),... [ on duplicate key update col_name=expression, ... ] or insert [low_priority | delayed] [ignore] [into] tbl_name [(col_name,...)] select ... or insert [low_priority | delayed] [ignore] [into] tbl_name set col_name=(expression | default), ... [ on duplicate key update col_name=expression, ... ]
  
• the REPLACE statement overwrites rows where a key clash occurs. insert fails or triggers the on duplicate key update clause when a key clash occurs.

Deleting Data:

§

• Delete data from tables with the DELETE statement:
  
• DELETE [LOW_PRIORITY] [QUICK] FROM table_name [WHERE where_definition] [ORDER BY ...] [LIMIT rows] or DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...] FROM table-references [WHERE where_definition] or DELETE [LOW_PRIORITY] [QUICK] FROM table_name[.*] [, table_name[.*] ...] USING table-references [WHERE where_definition]
  
• TRUNCATE TABLE statement deletes all rows from a table.

Updating Data:

§

• Update data in tables with the UPDATE TABLE statement:
  
• update [low_priority] [ignore] tbl_name set col_name1=expr1 [, col_name2=expr2 ...] [where where_definition] [order by ...] [limit rows] or update [low_priority] [ignore] tbl_name [, tbl_name ...] set col_name1=expr1 [, col_name2=expr2 ...] [where where_definition]
  
• Load data infile:
  
• Use LOAD DATA INFILE to load the contents of a text file into a table:
  
• load data [low_priority | concurrent] [local] infile 'file_name.txt' [replace | ignore] into table tbl_name [fields [terminated by '\t'] [[optionally] enclosed by ''] [escaped by '\\' ] ] [lines terminated by '\n'] [ignore number lines] [(col_name,...)]

MySQL Built-In Functions with SELECT

Operators:

• Arithmetic Operators • addition (+), subtraction (-), multiplication (*), and division (/)
  
• Comparison Operators •= Equality •!= or <> Inequality
  
• Logical operators •AND or && Logical AND. §Control flow functions – IF and CASE •CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
  
• String Functions: •concat(s1, s2, ...) Concatenate the strings in s1, s2, .... •length(s) Returns the length in characters of the string s. •load_file(filename) Returns the contents of the file stored at filename as a string.
  
• Date and Time Functions: •curdate(), curtime(), now() These return the current date, the current time, and the current date and time, respectively.
  
• Numeric Functions: •mod(n,m) and div These two functions divide n by m. div returns the integral quotient, and mod() returns the integral remainder. •power(n,m) Returns n to the power of m.
  
• Functions for Use with GROUP BY Clauses •avg(column) Returns the average value in column. •count(column) Returns the number of values in column

Managing User Privileges:

§

• Creating User Accounts with GRANT and REVOKE
  
• Privilege Levels
  
• Evaluating Privileges
  
• Using the REVOKE Statement
  
• Understanding the Privilege Tables

GRANT and REVOKE :

• The GRANT statement is used to grant privileges to a user or to create a user account.
  
• It has the following format: GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} TO user_name [IDENTIFIED BY [PASSWORD] 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [REQUIRE NONE | [{SSL| X509}] [CIPHER cipher [AND]] [ISSUER issuer [AND]] [SUBJECT subject]] [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # | MAX_UPDATES_PER_HOUR # | MAX_CONNECTIONS_PER_HOUR #]]

Privilege Levels :

2 basic categories:

• User-level privileges – Privileges granted to a user
  
• Administrator-level privileges - Privileges that should be granted only to administrators. Some of these privileges can be granted to users with caution at the administrator’s discretion, but they should not be granted to users by default.

User-level privileges:

Privilege	Meaning
CREATE	User can create tables.
CREATE TEMPORARY TABLES	User can create temporary tables.
DELETE	User can delete rows.
EXECUTE	User can execute procedures.
INDEX	User can create indexes.
INSERT	User can insert rows.
LOCK TABLES	User can lock tables.
SELECT	User can select rows.
SHOW DATABASES	User can execute a SHOW DATABASES command to retrieve the list of available databases.
UPDATE	User can update rows.
USAGE	User can log in, but cannot do anything else.

Administrator-level privileges:

Privilege	Meaning
ALL	User has all the privileges except WITH GRANT OPTION.
ALTER	User can alter tables. You may give this to some power users, but proceed with caution because it may be used to change the privilege tables.
DROP	User can drop tables. You may give this to trusted users.
FILE	User can load data from a file. Again, you may give this to trusted users. Beware of users trying to load arbitrary files, such as /etc/passwd or similar files!
PROCESS	User can show full process list—that is, see all the processes that MySQL is executing.
RELOAD	User can use the FLUSH statement. This has various purposes.

REPLICATION CLIENT	User can check where the masters and slaves are.
REPLICATION SLAVE	Special privilege designed for the special replication user on the slave.
SHUTDOWN	User can run mysqladmin shutdown.
SUPER	User can connect even if MySQL has its maximum number of connections and can execute the commands CHANGE MASTER, KILL (thread), mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL.
WITH GRANT OPTION	User can pass on any privileges he has.

Evaluating Privileges:

§

• Four sets of privileges are granted with the GRANT statement:
• Global privileges apply across all databases. These are specified with *.* in a GRANT statement. –Eg. grant all on *.* to fred;
  
• Database privileges apply to one particular database. These are granted with database.* in a GRANT statement. –Eg. grant all on employee.* to fred;
  
• Table privileges apply to a single table. These are granted by naming a specific table in the GRANT statement –Eg. grant select on department to fred;
  
• Column privileges apply to a single column. These are specified in the GRANT clause of the GRANT statement. –Eg. grant select (employeeID) on employee to fred;
  
• To evaluate a user’s privilege to do a particular task, MySQL will look at the combination of that user's global privileges plus his database privileges plus his table privileges plus his column privileges ORed together.
  

Using the REVOKE Statement:

§

• The REVOKE statement is used to take privileges away from a user. It has the following format: REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} FROM user_name [, user_name ...]

Starting Up and Shutting Down the MySQL Server:

• §

• Command to start MySQL: /etc/init.d/mysqld start
  
• Command to stop MySQL: /etc/init.d/mysqld stop (or) mysqladmin –u root –p shutdown

Getting Information about the Server and Databases:

• To get information about database – mysqlshow script (or) show databases
  
• To see the status of MySQL server: SHOW STATUS (inside MySQL)
  
• mysqladmin –u username –p –extended-status (from command line)
  
• To see the values of server variables - show variables; (inside MySQL) or §mysqladmin –u username –p variables (command line)
  
• To see the processes currently running on the server: show processlist; (inside MySQL) §mysqladmin –u username –p showprocesslist (from command line)

Setting Variables, Killing Threads and Clearing Caches:

• Setting Variables: Set variables with set variable=value;
• Killing Threads: See threads with show processlist and kill them with kill processid Clearing Caches:
  
• Clear caches with FLUSH and RESET •flush privileges •Flush query cache •Reset query cache

Log Files:

• Error log: Tracks all the errors that have occurred. This one is logged by default and will appear in data directory. The file is called hostname.err. The location can be set with the option log-error=filename in my.cnf file.
  
• Query log: Logs all the queries run on the system. The location should be specified with the option log=filename in my.cnf file.
  
• Binary log: Logs all the queries that change data. The location should be specified with the option log-bin=filename in my.cnf file.
  
• Slow query log: Logs all queries that took longer to execute than the value stored in the variable long_query_time. The location should be specified with the option log-slow-queries=filename in my.cnf file.

mysqladmin Option:

• mysqladmin create databasename (create database)
  
• mysqladmin drop databasename (drop database)
  
• mysqladmin ping (check if server is up)
  
• mysqladmin version (version of MySQL server software)
  
• mysqladmin status (short status message from server)
  
• mysqladmin extended-status ( long status)
  
• mysqladmin processlist (list of current active threads within server)
  
• mysqladmin kill id1,id2,id3... (kill processes)
  
• mysqladmin variables (print the value of MySQL variables)

Back Up:

• mysqldump creates a dump file of SQL statements. •mysqldump --opt –u username –p password employee > backup.sql
  
• mysqlhotcopy copies the data files to a backup location. •mysqlhotcopy -u username -p database_name backup_location
  
• BACKUP TABLE copies the data file for a table to a backup location. This is only for MyISAM tables •backup table t1 to 'path/to/backup';
  
• Manual back up can be done by locking and flushing the tables and then copying the files.

Restoring Backup:

§

• Reload dump files from mysqldump.
  
• Copy back data files from mysqlhotcopy or a manual backup.
  
• Restore from BACKUP TABLE with RESTORE TABLE. •restore table t1 from 'path/to/backup'; Re-execute operations since the backup from the binary log. §Restore from binary log •mysqlbinlog logfile > updates.sql

Checking and Repairing Tables:

• Check tables with CHECK TABLE •check table
  
• Repair tables with REPAIR TABLE •Repair table
  
• Check tables with myisamchk ( myisamchk should not be used while the server is being used) •myisamchk table §Check tables with mysqlcheck •mysqlcheck -u username -p employee

Replication:

• MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves.
  
• In single-master replication, the master server writes updates to its binary log files and maintains an index of those files to keep track of log rotation. The binary log files serve as a record of updates to be sent to any slave servers.
  
• When a slave connects to its master, it informs the master of the position up to which the slave read the logs at its last successful update. The slave receives any updates that have taken place since that time, and then blocks and waits for the master to notify it of new updates.

Optimizing MySQL Server Configuration:

§

• Tune server parameters for a performance boost, particularly those relating to memory usage. These are some particularly important parameters:
  
• key_buffer_size: Amount of memory used for storing MyISAM indexes.
  
• table_cache: Number of tables that can be open at once.
  
• read_buffer_size: Amount of memory used to store data from full table scans.
• sort_buffer: Amount of memory used to store table data to be sorted for ORDER BY.
  
• Tune hardware with more memory and a fast network between clients and servers and masters and slaves.

Aqua Data Studio for MySQL:

§

• Aqua Data Studio is a database query tool and administration tool for the MySQL relational database.
  
• The visual administration features provide users the ability to browse and modify database structures, including schema objects, database storage and maintain database security.
  
• An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts.
  
• This allows the database administrator or developer to tackle multiple tasks simultaneously from one application.