<h2 style="text-align: justify;">SQL Tutorial Overview</h2>
SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language.
SQL tutorial gives unique learning on Structured Query Language and it helps to make practice on SQL commands which provides immediate results. SQL is a language of database, it includes database creation, deletion, fetching rows and modifying rows etc.
SQL is an ANSI (American National Standards Institute) standard but there are many different versions of the SQL language.
<h3>Index</h3>
<ul>
<li><a href="loading-sql-db-hyperion-essbase/">LOADING FROM SQL DB in Hyperion EssBase</a></li>
<li><a href="setup-sql-server-data-base-informatica/">SQL Server Database Setup in Informatica</a></li>
<li><a href="sql-override-joins-aggregation-informatica/">SQL &ndash;override &ndash;joins and aggregation in Informatica</a></li>
<li><a href="sql-aggregations-datasetrow-colummn-operations-sas/">SQL Aggregations &amp; DataSet(Row &amp; colummn operations) in SAS</a></li>
<li><a href="sql-operators-joins-sas/">SQL Operators and Joins in SAS</a></li>
<li><a href="sql-statements-sas/">SQL Statements in SAS</a></li>
<li><a href="sql-expressions/">SQL Expressions</a></li>
<li><a href="sql-clone-tables/">SQL Clone Tables</a></li>
<li><a href="sql-handling-duplicates/">SQL Handling Duplicates</a></li>
<li><a href="teradata-sql-server-2008/">TeraData in sql server -2008</a></li>
<li><a href="essential-tips-sql-query-performance-remote-dba-oracle/">Essential Tips For SQL Query Performance In Remote DBA Oracle</a></li>
</ul>
<h2 style="text-align: justify;">What is SQL?</h2>
SQL is structured Query Language which is a computer language for storing, manipulating and retrieving data stored in relational database.
SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server uses SQL as standard database language.
Also they are using different dialects, Such as:
MS SQL Server using T-SQL Oracle using PL/SQL MS Access version of SQL is called JET SQL (native format ) etc.
&nbsp;
<h2 style="text-align: justify;">Why SQL?</h2>
Allow users to access data in relational database management systems. Allow users to describe the data. Allow users to define the data in database and manipulate that data. Allow to embed within other languages using SQL modules, libraries &amp; pre-compilers. Allow users to create and drop databases and table. Allow users to create view, stored procedure, functions in a database. Allow users to set permissions on tables, procedures, and views
&nbsp;
<h3 style="text-align: justify;">SQL History</h3>
1970 -- Dr. E.F. "Ted" of IBM is known as the father of relational databases. He described a relational model for databases. 1974 -- Structured Query Language appeared. 1978 -- IBM worked to develop Codd's ideas and released a product named System/R. 1986 -- IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software and its later becoming Oracle.
&nbsp;
<h3 style="text-align: justify;">SQL Process</h3>
When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. There are various components included in the process. These components are Query Dispatcher, Optimization engines, Classic Query Engine and SQL query engine etc. Classic query engine handles all non-SQL queries but SQL query engine won't handle logical files.
Following is a simple diagram showing SQL Architecture:
<h3 style="text-align: justify;">SQL Commands</h3>
<img class="aligncenter wp-image-1393" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide129-460x345.png" alt="Slide1" width="551" height="413" />
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be classified into groups based on their nature:
&nbsp;
<h3 style="text-align: justify;">DDL - Data Definition Language</h3>
<table>
<tbody>
<tr>
<td>Command</td>
<td>Description</td>
</tr>
<tr>
<td>CREATE</td>
<td>Creates a new table, a view of a table, or other object in database</td>
</tr>
<tr>
<td>ALTER</td>
<td>Modifies an existing database object, such as a table.</td>
</tr>
<tr>
<td>DROP</td>
<td>Deletes an entire table, a view of a table or other object in the database.</td>
</tr>
</tbody>
</table>
&nbsp;
<h3 style="text-align: justify;">DML - Data Manipulation Language</h3>
<table>
<tbody>
<tr>
<td>Command</td>
<td>Description</td>
</tr>
<tr>
<td>INSERT</td>
<td>Creates a record</td>
</tr>
<tr>
<td>UPDATE</td>
<td>Modifies records</td>
</tr>
<tr>
<td>DELETE</td>
<td>Deletes records</td>
</tr>
</tbody>
</table>
&nbsp;
<h3 style="text-align: justify;">DCL - Data Control Language</h3>
<table>
<tbody>
<tr>
<td>Command</td>
<td>Description</td>
</tr>
<tr>
<td>GRANT</td>
<td>Gives a privilege to user</td>
</tr>
<tr>
<td>REVOKE</td>
<td>Takes back privileges granted from user</td>
</tr>
</tbody>
</table>
&nbsp;
<h3 style="text-align: justify;">DQL - Data Query Language</h3>
<table>
<tbody>
<tr>
<td>Command</td>
<td>Description</td>
</tr>
<tr>
<td>SELECT</td>
<td>Retrieves certain records from one or more tables</td>
</tr>
</tbody>
</table>
<h3 style="text-align: justify;">&nbsp;</h3>
<h3 style="text-align: justify;">SQL References</h3>
<a href="https://tekslate.com/sql-joins/">SQL Joins</a>
&nbsp;

SQL Tutorial |  A Beginner's Guide | Learn SQL

SQL Tutorial

sql-tutorials

<h2 style="text-align: justify;">MySQL Tutorial Overview</h2>
Welcome to the MySQL Tutorial. The objective of these tutorials is to provide you a in depth understanding of MySQL Server. In these tutorials, we will cover topics such as Installation, Administration, User Management, Security and Optimization of MySQL Server.
<h3>Index</h3>
<ul>
<li><a href="https://tekslate.com/mysql-interview-questions-answers">MySQL Interview Questions &amp; Answers</a></li>
<li><a href="https://tekslate.com/upgrading-mysql/">Upgrading MySQL</a></li>
<li><a href="https://tekslate.com/running-multiple-mysql-instances-one-machine/">Running Multiple MySQL Instances on One Machine</a></li>
<li><a href="https://tekslate.com/mysql-server-logs/">MySQL Server Logs</a></li>
<li><a href="https://tekslate.com/mysql-server-administration/">MySQL Server Administration</a></li>
<li><a href="https://tekslate.com/mysql-administrative-utility-programs/">MySQL Administrative and Utility</a></li>
<li><a href="https://tekslate.com/mysql-client-programs/">MySQL Client Programs</a></li>
<li><a href="https://tekslate.com/mysql-server-server-startup-programs/">MySQL Server and Server-Startup Programs</a></li>
<li><a href="https://tekslate.com/mysql-server-server-startup-programs/">MySQL Server and Server-Startup Programs</a></li>
<li><a href="https://tekslate.com/mysql-installation-related-programs/">MySQL Installation related Programs</a></li>
<li><a href="https://tekslate.com/mysql-installation/">MySQL Installation</a></li>
<li><a href="https://tekslate.com/mysql-planning-introduction/">MySQL Introduction</a></li>
</ul>
<h2 style="text-align: justify;">Overview of MySQL Database Management System</h2>
MySQL is a database management system
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.
MySQL is a relational database management system
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist. In this manual, &ldquo;SQL-92&rdquo; refers to the standard released in 1992, &ldquo;SQL:1999&rdquo; refers to the standard released in 1999, and &ldquo;SQL:2003&rdquo; refers to the current version of the standard. We use the phrase &ldquo;the SQL standard&rdquo; to mean the current version of the SQL Standard at any time.
MySQL Software is Open Source
Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs.
&nbsp;
<h2 style="text-align: justify;">&nbsp;History of MySQL</h2>
They started out with the intention of using the mSQL database system to connect to our tables using our own fast low-level (ISAM) routines. However, after some testing, we came to the conclusion that mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL.
MySQL is named after co-founder Monty Widenius's daughter, My.
The name of the MySQL Dolphin (our logo) is &ldquo;Sakila,&rdquo; which was chosen from a huge list of names suggested by users in our &ldquo;Name the Dolphin&rdquo; contest. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Swaziland, Africa. According to Ambrose, the feminine name Sakila has its roots in SiSwati, the local language of Swaziland. Sakila is also the name of a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.
&nbsp;
<h2 style="text-align: justify;">The Main Features of MySQL</h2>
<ul style="text-align: justify;">
<li>Written in C and C++.</li>
<li>Tested with a broad range of different compilers.</li>
<li>Works on many different platforms.</li>
<li>Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available</li>
<li>Provides transactional and nontransactional storage engines.</li>
<li>Implements in-memory hash tables, which are used as temporary tables.</li>
<li>Works in cross platform.</li>
</ul>
&nbsp;
Why do companies prefer MySQL?
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Before MySQL doesn't supported stored procedures, functions, triggers, views, subqueries and partitioning. But now they are supported. Small and middle level companies are relied on MySQL because it is opensource. Supports replication and clustering for High Availability.
<h2 style="text-align: justify;">&nbsp;</h2>
<h2 style="text-align: justify;">MySQL Architecture</h2>
Client/Server Overview
The MySQL database system operates using a client/server architecture. The server is a central program that manages database contents, and client programs connect to the server &nbsp;&nbsp;&nbsp;&nbsp; to retrieve or modify the data. MySQL also includes non-client utility programs and scripts.
MySQL Server: This is the mysqld program that manages database and tables. Most users choose binary MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source.
<img class="alignnone wp-image-2731" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/140-460x250.png" alt="1" width="464" height="252" />
<img class="alignnone wp-image-2733" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/228-460x378.png" alt="2" width="470" height="385" />
Client Programs: These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example you can use the mysql client to send queries to the server, and the server returns the query results. A client program can connect locally to a server running on the same machine or remotely to a server running on a different machine.
Non-client utility programs: These are programs that generally used for special purposes and do not acts as clients of the server. They do not connect to the server, for example mysqld_safe is a script for starting and stopping the server. Myisamchk is a standalone utility for table check and repair.
&nbsp;
Application layer
This is where the client interact with the RDBMS. This layer is used by three users:
<ul>
<li style="text-align: justify;">Administrator.</li>
<li style="text-align: justify;">client.</li>
<li style="text-align: justify;">User.</li>
</ul>
i) Administrative Interface and Utilities&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; This layer is used by Administrator. This includes utilities such as mysqladmin, myisamchk, mysqldump etc.
ii) Client Interface and Utilities
This layer is used by the clients to communicate with RBDMS. The client interface uses MySQL APIs for various different programming languages such as the&nbsp; PHP API, Java API, etc.
iii) Query Interface
<ul style="text-align: justify;">
<li>This use the mysql as a &ldquo;interactive tool&rdquo; that issue sql statements to the server and displays results to the screen.</li>
</ul>
&nbsp;
Logical layer
All ths DML statements that are sent from the application layer are parsed and optimized in the query processor.
It is again sub-divided into following layers:
i) embedded DML precompiler
When a request is received from a client in the application layer, it is the responsibility of the embedded DML (Data Manipulation Language) precompiler to extract the relevant SQL statements embedded in the client API commands, or to translate the client commands into the corresponding SQL statements.
ii) DDL Compiler
<ul style="text-align: justify;">
<li>Request received from the administrator are processed by the DDL Compiler.</li>
<li>The administrative utility does not expose any interface and hence executed directly by the server.</li>
</ul>
iii) Query Preprocessor&nbsp;
<ul style="text-align: justify;">
<li>The query preprocessor parses the query. The parsing is of 2 types</li>
</ul>
<ol>
<li>syntactical</li>
<li>semantical</li>
</ol>
If the query syntax is right then it is sent to the next pipe . Else it inform to the client with an a error message.
iv)Query Optimizer
<ul style="text-align: justify;">
<li>Query optimizer is the brain of the MySQL.</li>
<li>Once client has permission to execute query, the query is optimized by the query optimizer. The task of the query optimizer is analyze the processed query to see if it can take advantage of any optimizations that will allow it to process the query more quickly.MySQL query optimizer uses indexes.</li>
</ul>
v) Execution Engine
<ul style="text-align: justify;">
<li>Once the MySQL query optimizer has optimized the MySQL query, the query can then be executed against the database. This is performed by the query execution engine, which then proceeds to execute the SQL statements and access the physical layer. As well the database administrator can execute commands on the database to perform specific tasks such as repair, recovery, copying and backup, which it receives from the DDL compiler.</li>
</ul>
&nbsp;
MySQL Transaction Management
i) Transaction Manager
A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the&nbsp; transaction is logged and executed atomically.&nbsp; And also prevents from deadlocks. Furthermore, the transaction manager is responsible for issuing the COMMIT and the ROLLBACK SQL commands. The COMMIT command commits to performing a transaction. Thus, a transaction is incomplete until it is committed to. The ROLLBACK command is used when a crash occurs during the execution of a transaction. If&nbsp; a transaction were left incomplete, the ROLLBACK command would undo all changes made by that transaction. The result of executing this command is restoring the database to its last stable state.
<ul style="text-align: justify;">
<li>Ensures Atomicity</li>
<li>Avoids Deadlocks</li>
<li>Responsible for Commit &amp; Rollback</li>
</ul>
ii) Concurrency- Control Manager
The concurrency-control manager is responsible for making sure that transactions are executed separately and&nbsp; independently. It does so by acquiring locks. Once the lock is acquired, only the operations in one transaction can manipulate the data. If a different transaction tries to manipulate the same locked data,&nbsp; the concurrency-control manager rejects the request until the first transaction is complete .
Recovery Management
iii) Log Manager
The log manager is responsible for logging every operation executed in the database. It does so by storing the log on disk through the buffer manager. The operations in the log are stored as MySQL commands. Thus, in the case of a system crash, executing every command in the log will bring back the database to its last stable state.
&nbsp;iv) Recovery Manager
The recovery manager is responsible for restoring the database to its last stable state. It does so by using the log for the database, which is acquired from the buffer manager, and executing each operation in the log. Since the log manager logs all operations performed on the database, executing each command in the log file would recover the database to its last stable state.
Storage Management
Storage is physically done on some type of secondary storage, however dynamic access of this medium is not&nbsp; practical. Thus, all work is done through a number of buffers. The buffers reside in main and virtual memory&nbsp; and are managed by a Buffer Manager.
i) Resource Manager
The purpose of the Resource Manager is to accept requests from the execution engine, put them into table requests, and request the tables from the Buffer Manager. The Resource Manager receives references to data within memory from the Buffer Manager and returns this data to the upper layers.
ii) Buffer Manager
The role of the Buffer Manager is to allocate memory resources for the use of viewing and manipulating data. The Buffer Manager takes in formatted requests and decides how much memory to allocate per buffer and how many buffers to allocate per request. All requests are made from the Resource Manager.
(key_buffer, sort_buffer, myisam_sort_buffer)
iii) Storage Manager
The storage manager acts as a mediator to send request between buffer manager and secondary storage.

MySQL Tutorial | A Comprehensive Tutorial for Beginners and Beyond

MySql Tutorial

tutorialsmysql

Welcome to Oracle PL SQL Tutorials. The objective of these tutorials is to provide an in-depth understanding of Oracle PL SQL.
In addition to free Oracle Oracle PL SQL&nbsp; Tutorials, we will cover common <a href="plsql-interview-questions-and-answers-beginner-advanced-experienced" target="_blank" rel="noopener">interview questions</a>, issues, and how to&rsquo;s of Oracle PL SQL .
<h2 style="text-align: justify;">Introduction of&nbsp;Oracle PL SQL Tutorials</h2>
PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). <a href="sql-tutorials" target="_blank" rel="noopener">SQL</a> is a powerful language for both querying and updating data in relational databases.
Oracle created PL/SQL that extends some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on the <a href="tutorialsoracle-application-framework-oaf-tutorials" target="_blank" rel="noopener">Oracle </a>database.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by <a href="tutorialsoracle-hyperion-essbase" target="_blank" rel="noopener">Oracle </a>Corporation in the early 90's to enhance the capabilities of SQL.
PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and <a href="core-java-tutorials" target="_blank" rel="noopener">Java.</a>
This Topic will give you a great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts.
The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as a procedural extension language for SQL and the Oracle relational database. The following are notable facts about PL/SQL:
-PL/SQL is a completely portable, high-performance transaction-processing language.
-PL/SQL provides a built-in interpreted and OS independent programming environment.
-PL/SQL can also directly be called from the command-line SQL*Plus interface.
-Direct call can also be made from external programming language calls to database.
-PL/SQL's general syntax is based on that of ADA and Pascal programming language.
-Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.
<pre style="text-align: center;">Learn Oracle PL SQL by Tekslate - Fastest growing sector in the industry. Explore Online <a href="https://tekslate.com/oracle-pl-sql-training/" target="_blank" rel="noopener">Oracle PL SQL Training</a> and course is aligned with industry needs &amp; developed by industry veterans. Tekslate will turn you into Oracle PL SQL Expert.</pre>
<h2 style="text-align: justify;">Features of PL/SQL</h2>
PL/SQL has the following features:
-PL/SQL is tightly integrated with SQL.
-It offers extensive error checking.
-It offers numerous data types.
-It offers a variety of programming structures.
-It supports structured programming through functions and procedures.
-It supports object-oriented programming.
-It supports developing web applications and server pages.
<h3 style="text-align: justify;">PL/SQL Block consists of three sections:</h3>
-The Declaration section (optional).
-The Execution section (mandatory).
-The Exception Handling (or Error) section (optional).
Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.
Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement,s and SQL statements form the part of the execution section.
Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.
<h3 style="text-align: justify;">SQL Command Categories</h3>
SQL commands are grouped into four major categories depending on their functionality. They are as follows:
<h4 style="text-align: justify;">Data Definition Language (DDL)</h4>
These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
<h4 style="text-align: justify;">Data Manipulation Language (DML)</h4>
These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
<h4 style="text-align: justify;">Transaction Control Language (TCL)</h4>
These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
<h4 style="text-align: justify;">Data Control Language (DCL)</h4>
These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.
<h3 style="text-align: justify;">Advantages of PL/SQL</h3>
PL/SQL has the following advantages:
-SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from the PL/SQL block. Dynamic SQL is SQL that allows embedding DDL statements in PL/SQL blocks.
-PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.
-PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.
-PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.
Applications written in PL/SQL are fully portable.
-PL/SQL provides a high-security level.
-PL/SQL provides access to predefined SQL packages.
-PL/SQL provides support for Object-Oriented Programming.
-PL/SQL provides support for Developing Web Applications and Server Pages.
&nbsp;

Oracle PL SQL Tutorial | PL SQL Tutorial | Learn PL SQL

Oracle PL SQL Tutorial

oracle-pl-sql-tutorials

<h2 dir="ltr">MySQL Interview Questions And Answers</h2>
<h3 dir="ltr">1. What is MySql?</h3>
Ans: MySQL is one of the excellent Relational Database Management System(RDBMS) which is utilised to build numerous Web-Based applications or software.
<h3 dir="ltr">2. Explain about Database?</h3>
Ans: The database is one individual application in which we can store the data. Each and every database contains one or more APIs for accessing, managing, creating, replicating and also searching the data.
<h3 dir="ltr">3. List the RDBMS terminologies?</h3>
Ans: 
<ul>
<li dir="ltr" role="presentation">Database&nbsp;</li>
<li dir="ltr" role="presentation">Table&nbsp;</li>
<li dir="ltr" role="presentation">Column</li>
<li dir="ltr" role="presentation">Row&nbsp;</li>
<li dir="ltr" role="presentation">Redundancy&nbsp;</li>
<li dir="ltr" role="presentation">Primary Key</li>
<li dir="ltr" role="presentation">Foreign Key</li>
<li dir="ltr" role="presentation">Compound Key</li>
<li dir="ltr" role="presentation">Index&nbsp;</li>
<li dir="ltr" role="presentation">Referential Integrity&nbsp;&nbsp;</li>
</ul>
<h3 dir="ltr">4. Explain why MySql is very popular?</h3>
Ans: MySql is one of the fast and easy to use RDBMS that is being used by many small and big businesses there are quite a few reasons for MySql being popular they are&nbsp;
<ul>
<li dir="ltr" role="presentation">There is no need to pay anything to use MySQL as it is issued beneath an open-source license&nbsp;</li>
<li dir="ltr" role="presentation">The standard that is used in MySql is the very well-known SQL data language</li>
<li dir="ltr" role="presentation">&nbsp;MySql works well even with the massive data sets</li>
<li dir="ltr" role="presentation">MySql is very fast at working</li>
</ul>
<h3 dir="ltr">5. Explain the working of MySql on MySql server?</h3>
Ans: There are three basic steps to describe the working of MySql on the MySql server
<ol>
<li dir="ltr" role="presentation">MySql build a database that lets us create tables that store the data and manipulate it and also describe the relationship among the different tables</li>
<li dir="ltr" role="presentation">Utilising few particular SQL expressions on MySql users can make requests through the command prompt or GUI screen</li>
<li dir="ltr" role="presentation">And Finally, the server will produce the results by responding with the requested expressions upon the client-side</li>
</ol>
<h3 dir="ltr">6. What are the characteristics of MySql?</h3>
Ans:&nbsp;
<ul>
<li dir="ltr" role="presentation">MySql is very easy to use&nbsp;</li>
<li dir="ltr" role="presentation">When it comes to security MySql is the best&nbsp;</li>
<li dir="ltr" role="presentation">This is a standard client/server architecture</li>
<li dir="ltr" role="presentation">We can download MySql for free</li>
<li dir="ltr" role="presentation">It is feasible</li>
<li dir="ltr" role="presentation">It is scalable</li>
</ul>
<h3 dir="ltr">7. List the data types of MySql?</h3>
Ans: 
<ul>
<li dir="ltr" role="presentation">Numeric Data Type</li>
<li dir="ltr" role="presentation">Date and Time Data Type</li>
<li dir="ltr" role="presentation">String Data Types</li>
<li dir="ltr" role="presentation">Large Object Data Types</li>
</ul>
<h3 dir="ltr">8. What are the types of variables in MySql?</h3>
Ans: There are three basic types of variables in MySql they are
<ul>
<li dir="ltr">
User-Defined variables
</li>
<li dir="ltr">
Local variables
</li>
<li dir="ltr">
System variables
</li>
</ul>
<h3 dir="ltr">9. Explain about user-defined variables?</h3>
Ans: User-defined variables are not case sensitive and they are not visible to any other person except the one who declared them, the maximum length of the user-defined variables can be of 64-characters
<h3 dir="ltr">10. Write syntax for the SET statement?</h3>
Ans: SET @variable_name= value;
<h3 dir="ltr">11. How can we display variables from the table?</h3>
Ans: We can display the variables by using Select statement
<h3 dir="ltr">12. Write Syntax for creating a table?</h3>
Ans: CREATE TABLE nameofthe_table(coloumn_name, coloumn_type&hellip;.);
<h3 dir="ltr">13. What is a View?</h3>
Ans: Views are designed for providing security by encapsulating the tables name. Data will be present in the virtual table and it is not stored permanently. Views always display the selected data
<h3 dir="ltr">14. Do you know the default port number of MySQL server?</h3>
Ans: 3306 is the default port of MySQL server
<h3 dir="ltr">15. Which command is used to insert data into a MySQL table?</h3>
Ans: INSERT INTO is the command that is used to insert the data into the table
<h3 dir="ltr">16. What are the operators that can be used in where clause?</h3>
Ans:&nbsp;
<ul>
<li dir="ltr" role="presentation">=(equal to)</li>
<li dir="ltr" role="presentation">!=(not equal to)</li>
<li dir="ltr" role="presentation">&gt;(greater than)</li>
<li dir="ltr" role="presentation">&lt;(less than)</li>
<li dir="ltr" role="presentation">&gt;=(greater than or equal to)</li>
<li dir="ltr" role="presentation">&lt;=(less than or equal to)&nbsp;</li>
</ul>
<h3 dir="ltr">17. What is ISNULL()?</h3>
Ans: This function is used to check whether there is a NULL value or not, if there is a NULL value then this function returns true
<h3 dir="ltr">18. List the DDL commands?</h3>
Ans: 
<ul>
<li dir="ltr" role="presentation">Create</li>
<li dir="ltr" role="presentation">Drop</li>
<li dir="ltr" role="presentation">Alter</li>
<li dir="ltr" role="presentation">Truncate</li>
<li dir="ltr" role="presentation">Rename&nbsp;</li>
</ul>
<h3 dir="ltr">19. What are DML commands?</h3>
Ans: 
<ul>
<li dir="ltr" role="presentation">Select</li>
<li dir="ltr" role="presentation">Insert</li>
<li dir="ltr" role="presentation">Update</li>
<li dir="ltr" role="presentation">Delete&nbsp;</li>
</ul>
<h3 dir="ltr">20. Why do we use the TRUNCATE command?</h3>
Ans: We can remove the records from the table by using this truncate command
<h3 dir="ltr">21. Why do we use ALTER command?</h3>
Ans: We can alter the current database by using ALTER command
<h3 dir="ltr">22. What is the use of GRANT command?</h3>
Ans: GRANT command will allow the users to obtain the database
<h3 dir="ltr">23. List different string types?</h3>
Ans: 
<ul>
<li dir="ltr" role="presentation">SET</li>
<li dir="ltr" role="presentation">ENUM</li>
<li dir="ltr" role="presentation">TEXT</li>
<li dir="ltr" role="presentation">VARCHAR</li>
<li dir="ltr" role="presentation">CHAR</li>
<li dir="ltr" role="presentation">BLOB&nbsp;&nbsp;</li>
</ul>
<h3 dir="ltr">24. What do you know about triggers?</h3>
Ans: A trigger means a collection of codes which executes in acknowledgement to few events.
<h3 dir="ltr">25. Which language is used to develop MYSQL?</h3>
Ans: C and C++ are the languages that are used to develop MYSQL
<h3 dir="ltr">26. Why do we use SELECT CURRENT_DATE()?</h3>
Ans: This method is used to know the current date&nbsp;
<h3 dir="ltr">27. How to access the tables that are in another database?</h3>
Ans: Federated tables allot access over the tables that are present on different databases on the different servers.&nbsp;
<h3 dir="ltr">28. Explain about ISAM?</h3>
Ans: It is a method to file management generated by IBM which gives records to obtain randomly or even sequentially.
<h3 dir="ltr">29. List the drivers in MySQL?</h3>
Ans: 
<ul>
<li dir="ltr" role="presentation">PHP Driver</li>
<li dir="ltr" role="presentation">ODBC Driver</li>
<li dir="ltr" role="presentation">JDBC Driver</li>
<li dir="ltr" role="presentation">RUBY Driver</li>
<li dir="ltr" role="presentation">PERL Driver</li>
</ul>
<h3 dir="ltr">30. How many types of TEXT are there and what are they?</h3>
Ans: There are four types of TEXT they are
<ul>
<li>TINY TEXT</li>
<li>TEXT</li>
<li>MEDIUM TEXT&nbsp;</li>
<li>LONG TEXT</li>
</ul>

MySQL Interview Questions 

mysql-interview-questions-answers

TOP 35+ SQL Server DBA Interview Questions And Answers

SQL Server DBA Interview Questions and Answers

sql-server-dba-interview-questions-and-answers

Top 20+ SQL Server Interview Questions (2023)

<ul>
<li style="text-align: justify;"><a style="color: #000000;" href="#freshers">SQL Server Interview Questions for Freshers</a></li>
<li style="text-align: justify;"><a style="color: #000000;" href="#experienced">SQL Server Interview Questions for Experienced</a></li>
</ul>

SQL Server Interview Questions

interview-questions-on-sql-server

SQL Overview - Tekslate Tutorials

SQL Overview

sql-overview-3

SQL RDBMS Concepts - Tekslate Tutorials

SQL RDBMS Concepts

sql-rdbms-concepts

SQL Data Types - Tekslate

SQL Data Types

sql-data-types

SQL Operators with Examples by SQL Experts

SQL Operators

sql-operators

SQL Joins and SQL Unions Clause - Tekslate

SQL Joins and SQL Unions Clause

sql-joins

SQL Indexes - Tekslate Tutorials

SQL Indexes

sql-indexes

<h2>SQL Temporary Tables Overview</h2>
There are RDBMS which support temporary tables. Temporary Tables are a great feature that &nbsp;lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The SQL temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates. Temporary tables are available in MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23 you can't use temporary tables, but you can use heap tables. As stated earlier temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
<h3>Creating Temporary Tables in SQL with Example</h3>
Here is an example showing you usage of temporary table: mysql&gt; CREATE TEMPORARY TABLE SALESSUMMARY ( -&gt; product_name VARCHAR(50) NOT NULL -&gt; , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -&gt; , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -&gt; , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql&gt; INSERT INTO SALESSUMMARY -&gt; (product_name, total_sales, avg_unit_price, total_units_sold) -&gt; VALUES -&gt; ('cucumber', 100.25, 90, 2); mysql&gt; SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.25 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90.00 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) When you issue a SHOW TABLES command then your temporary table would not be listed out in the list. Now if you will log out of the MySQL session and then you will issue a SELECT command then you will find no data available in the database. Even your temporary table would also not exist.
<h3>Dropping Temporary Tables</h3>
By default all the temporary tables are deleted by MySQL when your database connection gets terminated. Still you want to delete them in between then you do so by issuing DROP TABLE command. Following is the example on dropping a temproary table. mysql&gt; CREATE TEMPORARY TABLE SALESSUMMARY ( -&gt; product_name VARCHAR(50) NOT NULL -&gt; , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -&gt; , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -&gt; , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql&gt; INSERT INTO SALESSUMMARY -&gt; (product_name, total_sales, avg_unit_price, total_units_sold) -&gt; VALUES -&gt; ('cucumber', 100.25, 90, 2); mysql&gt; SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.25 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90.00 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql&gt; DROP TABLE SALESSUMMARY; mysql&gt;&nbsp; SELECT * FROM SALESSUMMARY; ERROR 1146: Table 'TUTORIALS.SALESSUMMARY' doesn't exist &nbsp;

SQL Temporary Tables Tutorial by SQL Experts

SQL Temporary Tables

sql-temporary-tablessql-clone-tables-sql-sub-queries

<h2>SQL&nbsp;Sequences Overview</h2>
A sequence is a set of integers 1, 2, 3, ... that are generated in &nbsp;order on demand. &nbsp;Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them. Using AUTO_INCREMENT column The simplest way in MySQL to use Sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care. Example Try out following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because its auto incremented by MySQL. mysql&gt; CREATE TABLE INSECT -&gt; ( -&gt; id INT UNSIGNED NOT NULL AUTO_INCREMENT, -&gt; PRIMARY KEY (id), -&gt; name VARCHAR(30) NOT NULL, # type of insect -&gt; date DATE NOT NULL, # date collected -&gt; origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02 sec) mysql&gt; INSERT INTO INSECT (id,name,date,origin) VALUES -&gt; (NULL,'housefly','2001-09-10','kitchen'), -&gt; (NULL,'millipede','2001-09-10','driveway'), -&gt; (NULL,'grasshopper','2001-09-10','front yard'); Query OK, 3 rows affected (0.02 sec) Records: 3&nbsp; Duplicates: 0&nbsp; Warnings: 0 mysql&gt; SELECT * FROM INSECT ORDER BY id; +----+-------------+------------+------------+ | id | name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | origin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | +----+-------------+------------+------------+ |&nbsp; 1 | housefly&nbsp;&nbsp;&nbsp; | 2001-09-10 | kitchen&nbsp;&nbsp;&nbsp;&nbsp; | |&nbsp; 2 | millipede&nbsp;&nbsp; | 2001-09-10 | driveway&nbsp;&nbsp;&nbsp; | |&nbsp; 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 sec) Obtain AUTO_INCREMENT Values LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. otherwise PERL and PHH scripts provide exclusive functions to retrieve auto incremented value of last record.
<h3>Using SQL Sequences in PERL - Example</h3>
Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle: $dbh-&gt;do ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-09-14','windowsill')"); my $seq = $dbh-&gt;{mysql_insertid};
<h3>Using SQL Sequences in PHP - Example</h3>
After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ): mysql_query ("INSERT INTO INSECT (name,date,origin) VALUES('moth','2001-09-14','windowsill')", $conn_id); $seq = mysql_insert_id ($conn_id); Renumbering an Existing Sequence There may be a case when you have deleted many records from a table and you want to &nbsp;resequence all the records. This can be done by using a simple trick but you should be very careful to do so if your table is having join with other table. If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique: mysql&gt; ALTER TABLE INSECT DROP id; mysql&gt; ALTER TABLE insect -&gt; ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -&gt; ADD PRIMARY KEY (id); Starting a Sequence at a Particular Value By default MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation. Following is the example where MySQL will start sequence from 100. mysql&gt; CREATE TABLE INSECT -&gt; ( -&gt; id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, -&gt; PRIMARY KEY (id), -&gt; name VARCHAR(30) NOT NULL, # type of insect -&gt; date DATE NOT NULL, # date collected -&gt; origin VARCHAR(30) NOT NULL # where collected ); Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE. mysql&gt; ALTER TABLE t AUTO_INCREMENT = 100; &nbsp; +----------+ Now let us use DISTINCT keywork with the above SELECT query and see the result: SQL&gt; SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY; This would produce following result where we do not have any duplicate entry: +----------+ | SALARY&nbsp;&nbsp; | +----------+ |&nbsp; 1500.00 | |&nbsp; 2000.00 | |&nbsp; 4500.00 | |&nbsp; 6500.00 | |&nbsp; 8500.00 | | 10000.00 | +----------+

SQL Sequences with examples by SQL Experts

SQL Sequences

sql-using-sequencessql-handling-duplicates-sql-injection

<h2 style="text-align: justify;">Overview of MySQL Database Management System</h2>
MySQL is a database management system
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.
MySQL is a relational database management system
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist. In this manual, &ldquo;SQL-92&rdquo; refers to the standard released in 1992, &ldquo;SQL:1999&rdquo; refers to the standard released in 1999, and &ldquo;SQL:2003&rdquo; refers to the current version of the standard. We use the phrase &ldquo;the SQL standard&rdquo; to mean the current version of the SQL Standard at any time.
MySQL Software is Open Source
Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs.
<h2 style="text-align: justify;">&nbsp;History of MySQL</h2>
They started out with the intention of using the mSQL database system to connect to our tables using our own fast low-level (ISAM) routines. However, after some testing, we came to the conclusion that mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL.
MySQL is named after co-founder Monty Widenius's daughter, My.
The name of the MySQL Dolphin (our logo) is &ldquo;Sakila,&rdquo; which was chosen from a huge list of names suggested by users in our &ldquo;Name the Dolphin&rdquo; contest. The winning name was submitted by Ambrose Twebaze, an Open Source software developer from Swaziland, Africa. According to Ambrose, the feminine name Sakila has its roots in SiSwati, the local language of Swaziland. Sakila is also the name of a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.
<h2 style="text-align: justify;">The Main Features of MySQL</h2>
<ul style="text-align: justify;">
<li>Written in C and C++.</li>
<li>Tested with a broad range of different compilers.</li>
<li>Works on many different platforms.</li>
<li>Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available</li>
<li>Provides transactional and nontransactional storage engines.</li>
<li>Implements in-memory hash tables, which are used as temporary tables.</li>
<li>Works in cross platform.</li>
</ul>
Why companies are preferring MySQL ?
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Before MySQL doesn't supported stored procedures, functions, triggers, views, subqueries and partitioning. But now they are supported. Small and middle level companies are relied on MySQL because it is opensource. Supports replication and clustering for High Availability.
<h2 style="text-align: justify;">MySQL Architecture</h2>
Client/Server Overview
The MySQL database system operates using a client/server architecture. The server is a central program that manages database contents, and client programs connect to the server &nbsp;&nbsp;&nbsp;&nbsp; to retrieve or modify the data. MySQL also includes non-client utility programs and scripts.
MySQL Server: This is the mysqld program that manages database and tables. Most users choose binary MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source.
<img class="alignnone wp-image-2731" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/140-460x250.png" alt="1" width="464" height="252" />
<img class="alignnone wp-image-2733" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/228-460x378.png" alt="2" width="470" height="385" />
Client Programs: These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example you can use the mysql client to send queries to the server, and the server returns the query results. A client program can connect locally to a server running on the same machine or remotely to a server running on a different machine.
Non-client utility programs: These are programs that generally used for special purposes and do not acts as clients of the server. They do not connect to the server, for example mysqld_safe is a script for starting and stopping the server. Myisamchk is a standalone utility for table check and repair.
Application layer
This is where the client interact with the RDBMS. This layer is used by three users:
Administrator.
client.
User.
i) Administrative Interface and Utilities&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; This layer is used by Administrator. This includes utilities such as mysqladmin, myisamchk, mysqldump etc.
ii) Client Interface and Utilities
This layer is used by the clients to communicate with RBDMS. The client interface uses MySQL APIs for various different programming languages such as the&nbsp; PHP API, Java API, etc.
iii) Query Interface
<ul style="text-align: justify;">
<li>This use the mysql as a &ldquo;interactive tool&rdquo; that issue sql statements to the server and displays results to the screen.</li>
</ul>
Logical layer
All ths DML statements that are sent from the application layer are parsed and optimized in the query processor.
It is again sub-divided into following layers:
i) embedded DML precompiler
When a request is received from a client in the application layer, it is the responsibility of the embedded DML (Data Manipulation Language) precompiler to extract the relevant SQL statements embedded in the client API commands, or to translate the client commands into the corresponding SQL statements.
ii) DDL Compiler
<ul style="text-align: justify;">
<li>Request received from the administrator are processed by the DDL Compiler.</li>
<li>The administrative utility does not expose any interface and hence executed directly by the server.</li>
</ul>
iii) Query Preprocessor&nbsp;
<ul style="text-align: justify;">
<li>The query preprocessor parses the query. The parsing is of 2 types</li>
</ul>
1) syntactical
2) semantical
If the query syntax is right then it is sent to the next pipe . Else it inform to the client with an a error message.
iv)Query Optimizer
<ul style="text-align: justify;">
<li>Query optimizer is the brain of the MySQL.</li>
<li>Once client has permission to execute query, the query is optimized by the query optimizer. The task of the query optimizer is analyze the processed query to see if it can take advantage of any optimizations that will allow it to process the query more quickly.MySQL query optimizer uses indexes.</li>
</ul>
v) Execution Engine
<ul style="text-align: justify;">
<li>Once the MySQL query optimizer has optimized the MySQL query, the query can then be executed against the database. This is performed by the query execution engine, which then proceeds to execute the SQL statements and access the physical layer. As well the database administrator can execute commands on the database to perform specific tasks such as repair, recovery, copying and backup, which it receives from the DDL compiler.</li>
</ul>
MySQL Transaction Management
i) Transaction Manager
A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the&nbsp; transaction is logged and executed atomically.&nbsp; And also prevents from deadlocks. Furthermore, the transaction manager is responsible for issuing the COMMIT and the ROLLBACK SQL commands. The COMMIT command commits to performing a transaction. Thus, a transaction is incomplete until it is committed to. The ROLLBACK command is used when a crash occurs during the execution of a transaction. If&nbsp; a transaction were left incomplete, the ROLLBACK command would undo all changes made by that transaction. The result of executing this command is restoring the database to its last stable state.
<ul style="text-align: justify;">
<li>Ensures Atomicity</li>
<li>Avoids Deadlocks</li>
<li>Responsible for Commit &amp; Rollback</li>
</ul>
ii) Concurrency- Control Manager
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The concurrency-control manager is responsible for making sure that transactions are executed separately and&nbsp; independently. It does so by acquiring locks. Once the lock is acquired, only the operations in one transaction can manipulate the data. If a different transaction tries to manipulate the same locked data,&nbsp; the concurrency-control manager rejects the request until the first transaction is complete .
Recovery Management
iii) Log Manager
The log manager is responsible for logging every operation executed in the database. It does so by storing the log on disk through the buffer manager. The operations in the log are stored as MySQL commands. Thus, in the case of a system crash, executing every command in the log will bring back the database to its last stable state.
&nbsp;iv) Recovery Manager
The recovery manager is responsible for restoring the database to its last stable state. It does so by using the log for the database, which is acquired from the buffer manager, and executing each operation in the log. Since the log manager logs all operations performed on the database, executing each command in the log file would recover the database to its last stable state.
Storage Management
Storage is physically done on some type of secondary storage, however dynamic access of this medium is not&nbsp; practical. Thus, all work is done through a number of buffers. The buffers reside in main and virtual memory&nbsp; and are managed by a Buffer Manager.
i) Resource Manager
The purpose of the Resource Manager is to accept requests from the execution engine, put them into table requests, and request the tables from the Buffer Manager. The Resource Manager receives references to data within memory from the Buffer Manager and returns this data to the upper layers.
ii) Buffer Manager
The role of the Buffer Manager is to allocate memory resources for the use of viewing and manipulating data. The Buffer Manager takes in formatted requests and decides how much memory to allocate per buffer and how many buffers to allocate per request. All requests are made from the Resource Manager.
(key_buffer, sort_buffer, myisam_sort_buffer)
iii) Storage Manager
The storage manager acts as a mediator to send request between buffer manager and secondary storage.
Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In Hyderabad&nbsp;</a>

MySQL Introduction

mysql-planning-introduction

<h3>2.1. Installing MySQL from RPM Packages on Linux</h3>
For non-RPM Linux distributions, you can install MySQL using a .tar.gz package. MySQL-server-VERSION.glibc23.i386.rpm The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine. MySQL-client-VERSION.glibc23.i386.rpm The standard MySQL client programs. You probably always want to install this package to connect to the servers. MySQL-devel-VERSION.glibc23.i386.rpm The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules. MySQL-shared-VERSION.glibc23.i386.rpm This package contains the shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL. It contains single-threaded and thread-safe libraries. If you install this package, do not install the MySQL-shared-compat package. MySQL-shared-compat-VERSION.glibc23.i386.rpm This package includes the shared libraries for MySQL 3.23, 4.0, and so on, up to the current release. It contains single-threaded and thread-safe libraries. Install this package instead of MySQL-shared if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.
<h3>2.2.&nbsp; Installing MySQL from a Standard Source Distribution</h3>
&nbsp;
<table style="width: 500px;" border="1" cellspacing="1" cellpadding="1">
<tbody>
<tr>
<td>Directory</td>
<td>Contents of Directory</td>
</tr>
<tr>
<td>bin</td>
<td>Client programs and scripts</td>
</tr>
<tr>
<td>Var</td>
<td>Log files, databases</td>
</tr>
<tr>
<td>docs</td>
<td>Manual in Info format</td>
</tr>
<tr>
<td>man</td>
<td>Unix manual pages</td>
</tr>
<tr>
<td>Include/mysql</td>
<td>Include (header) files</td>
</tr>
<tr>
<td>Lib/mysql</td>
<td>Libraries</td>
</tr>
<tr>
<td>Libexec</td>
<td>The mysqld server</td>
</tr>
<tr>
<td>share/mysql</td>
<td>Miscellaneous support files, including error messages, sample configuration files, SQL for database installation</td>
</tr>
<tr>
<td>sql-bench</td>
<td>Benchmarks</td>
</tr>
</tbody>
</table>
&nbsp; &nbsp; # Preconfiguration setup shell&gt; groupadd mysql shell&gt; useradd -g mysql mysql # Beginning of source-build specific instructions shell&gt; tar zxvf mysql-VERSION.tar.gz shell&gt; cd mysql-VERSION shell&gt; ./configure --prefix=/usr/local/mysql shell&gt; make shell&gt; make install # End of source-build specific instructions # Postinstallation setup shell&gt; cd /usr/local/mysql shell&gt; chown -R mysql . shell&gt; chgrp -R mysql . shell&gt; bin/mysql_install_db --user=mysql shell&gt; chown -R root . shell&gt; chown -R mysql var # Next command is optional shell&gt; cp support-files/my-medium.cnf /etc/my.cnf shell&gt; bin/mysqld_safe --user=mysql &amp; # Next command is optional shell&gt; cp support-files/mysql.server /etc/init.d/mysql.server
<h2>&nbsp;</h2>
<h2>2.3&nbsp; Installing MySQL from Generic Binaries on Unix/Linux</h2>
&nbsp;
<table style="width: 500px;" border="1" cellspacing="1" cellpadding="1">
<tbody>
<tr>
<td>Directory</td>
<td>Contents of Directory</td>
</tr>
<tr>
<td>bin</td>
<td>Client programs and scripts</td>
</tr>
<tr>
<td>Var</td>
<td>Log files, databases</td>
</tr>
<tr>
<td>docs</td>
<td>Manual in Info format</td>
</tr>
<tr>
<td>man</td>
<td>Unix manual pages</td>
</tr>
<tr>
<td>Include/mysql</td>
<td>Include (header) files</td>
</tr>
<tr>
<td>Lib/mysql</td>
<td>Libraries</td>
</tr>
<tr>
<td>Libexec</td>
<td>The mysqld server</td>
</tr>
<tr>
<td>share/mysql</td>
<td>Miscellaneous support files, including error messages, sample configuration files, SQL for database installation</td>
</tr>
<tr>
<td>sql-bench</td>
<td>Benchmarks</td>
</tr>
</tbody>
</table>
&nbsp; shell&gt; groupadd mysql shell&gt; useradd -r -g mysql mysql shell&gt; cd /usr/local shell&gt; tar zxvf /path/to/mysql-VERSION-OS.tar.gz shell&gt; ln -s full-path-to-mysql-VERSION-OS mysql shell&gt; cd mysql shell&gt; chown -R mysql . shell&gt; chgrp -R mysql . shell&gt; scripts/mysql_install_db --user=mysql shell&gt; chown -R root . shell&gt; chown -R mysql data # Next command is optional shell&gt; cp support-files/my-medium.cnf /etc/my.cnf shell&gt; bin/mysqld_safe --user=mysql &amp; # Next command is optional shell&gt; cp support-files/mysql.server /etc/init.d/mysql.server 2.4&nbsp; Setting &nbsp;Environment &nbsp;Variables &nbsp; # vi .bash_profile export MySQL_HOME=/usr/local/mysql #PATH=$PATH:$HOME/bin PATH=$PATH:$HOME/bin:$MySQL_HOME export MYSQL_HOME='localhost' export USER='localhost'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Only on Win and Netware export MYSQL_PWD='localhost' Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In Hyderabad&nbsp;</a> &nbsp;

MySQL Installation Tutorial

MySQL Installation

mysql-installation

3.1. &nbsp;mysql_fix_privilege_tables is an older script that previously was used to uprade the system tables in the mysql database after a MySQL upgrade. As of MySQL 5.0.19, mysql_fix_privilege_tables is superseded by mysql_upgrade, which should be used instead. Before running mysql_fix_privilege_tables, make a backup of your mysql database. On Unix or Unix-like systems, update the system tables by running the mysql_fix_privilege_tables script: shell&gt; mysql_fix_privilege_tables --password=XXX &nbsp; 3.2. mysql_install_db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist. shell&gt; bin/mysql_install_db --user=mysql --basedir=/opt/mysql/mysql &ndash;datadir=/opt/mysql/mysql/data bootstrap This option is used by the mysql_install_db script to create the MySQL privilege tables without having to start a full MySQL server &nbsp; 3.3. mysql_secure_installation This program enables you to improve the security of your MySQL installation in the following ways:
<ul>
<li>You can set a password for root</li>
<li>You can remove root accounts that are accessible from outside the local host.</li>
<li>You can remove anonymous-user accounts.</li>
<li>You can remove the test database (which by default can be accessed by all users, even anonymous users)</li>
</ul>
shell&gt; mysql_secure_installation &nbsp; 3.4.&nbsp; mysql_tzinfo_to_sql &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The mysql_tzinfo_to_sql program loads the time zone tables in the mysql database. It is used on systems that have a zoneinfo database (the set of files describing time zones). Examples of such systems are Linux, FreeBSD, Solaris, and Mac OS X. One likely location for these files is the &ldquo;/usr/share/zoneinfo&rdquo; directory shell&gt; mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In Bangalore</a>

MySQL Installation Related Program

MySQL Installation related Programs

mysql-installation-related-programs

<h3>4.1. &nbsp;mysqld &mdash; The MySQL Server</h3>
mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files. When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients. The mysqld program has many options that can be specified at startup. For a complete list of options, run this command: shell&gt; mysqld --verbose &ndash;help Command to start mysql shell&gt; mysqld &ndash;user=mysql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (--console) MySQL Server also has a set of system variables that affect its operation as it runs. System variables can be set at server startup, and many of them can be changed at runtime to effect dynamic server reconfiguration. MySQL Server also has a set of status variables that provide information about its operation. You can monitor these status variables to access runtime performance characteristics.
<h3>&nbsp;</h3>
<h3>4.2.&nbsp; mysqld_safe &mdash; MySQL Server Startup Script</h3>
mysqld_safe is the recommended way to start a mysqld server on Unix and NetWare. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log file. mysqld_safe reads all options from the [mysqld], [server], and [mysqld_safe] sections in option files. The&nbsp;mysqld_safe&nbsp;script is written so that it normally can start a server that was installed from either a source or a binary distribution of MySQL, even though these types of distributions typically install the server in slightly different locations. (See&nbsp;Section&nbsp;2.7, &ldquo;Installation Layouts&rdquo;.)&nbsp;mysqld_safe&nbsp;expects one of the following conditions to be true: The server and databases can be found relative to the working directory (the directory from which&nbsp;mysqld_safe&nbsp;is invoked). For binary distributions,&nbsp;mysqld_safe&nbsp;looks under its working directory for&nbsp;bin&nbsp;and&nbsp;data&nbsp;directories. For source distributions, it looks for&nbsp;libexec&nbsp;and&nbsp;var&nbsp;directories. This condition should be met if you execute mysqld_safe&nbsp;from your MySQL installation directory (for example,&nbsp;/usr/local/mysql&nbsp;for a binary distribution). If the server and databases cannot be found relative to the working directory,&nbsp;mysqld_safe&nbsp;attempts to locate them by absolute path names. Typical locations are&nbsp;/usr/local/libexec&nbsp;and&nbsp;/usr/local/var. The actual locations are determined from the values configured into the distribution at the time it was built. They should be correct if MySQL is installed in the location specified at configuration time. --basedir=path The path to the MySQL installation directory. --help Display a help message and exit --defaults-extra-file=path The name of an option file to be read in addition to the usual option files. --log-error=file_name Write the error log to the given file --ledir=path If&nbsp;mysqld_safe&nbsp;cannot find the server, use this option to indicate the path name to the &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; directory where the server is located --no-defaults Do not read any option files. This must be the first option on the command line if it is used. --defaults-file=file_name The name of an option file to be read instead of the usual option files. This must be the first &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; option on the command line if it is used. --mysqld-version=suffix This option is similar to the&nbsp;--mysqld&nbsp;option, but you specify only the suffix for the server &nbsp;&nbsp; program name. The basename is assumed to be&nbsp;mysqld. For example, if you use&nbsp;--mysqld-&nbsp;&nbsp;&nbsp;&nbsp; version=debug,&nbsp;mysqld_safe&nbsp;starts the&nbsp;mysqld-debug&nbsp;program in the&nbsp;ledir&nbsp;directory. If the &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; argument to&nbsp;--mysqld-version&nbsp;is empty,mysqld_safe&nbsp;uses&nbsp;mysqld&nbsp;in the&nbsp;ledir&nbsp;directory. --open-files-limit=count The number of files that&nbsp;mysqld&nbsp;should be able to open. The option value is passed to&nbsp;ulimit &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -n. Note that you need to start&nbsp;mysqld_safe&nbsp;as&nbsp;root&nbsp;for this to work properly! --pid-file=file_name The path name of the process ID file. --port=port_num The port number that the server should use when listening for TCP/IP connections. The port &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; number must be 1024 or higher unless the server is started by the&nbsp;root&nbsp;system user. --socket=path The Unix socket file that the server should use when listening for local connections. &nbsp;
4.3.&nbsp; mysql.server &mdash; MySQL Server Startup Script MySQL distributions on Unix include a script named mysql.server . It can be used on systems such as Linux and Solaris that use System V-style run directories to start and stop system services. It is also used by the Mac OS X Startup Item for MySQL. mysql.server can be found in the support-files directory under your MySQL installation directory or in a MySQL source distribution. --basedir=path The path to the MySQL installation directory. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --datadir=path The path to the MySQL data directory. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --pid-file=file_name The path name of the file in which the server should write its process ID. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --service-startup-timeout=file_name How long in seconds to wait for confirmation of server startup. If the server does not start &nbsp; within this time, mysql.server exits with an error. The default value is 900. A value of 0 means &nbsp; not to wait at all for startup. Negative values mean to wait forever (no timeout). This option was &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; added in MySQL 5.1.17. Before that, a value of 900 is always used. --use-mysqld_safe Use mysqld_safe to start the server. This is the default. --user=user_name The login user name to use for running mysqld. &nbsp;
4.4.&nbsp; mysqld_multi ― Manage Multiple MySQL Servers
Check Out Our Related Courses
&nbsp;<a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a>
<a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In Bangalore</a>

MySQL Server Tutorial

MySQL Server and Server-Startup Programs

mysql-server-server-startup-programs

We can create User Accounts in two ways.
<ol>
<li>By using CREATE USER or GRANT statements. (Recommended Method)</li>
<li>By manipulating the mysql grant tables with statements like INSERT and DELETE.</li>
</ol>
&nbsp; Using CREATE USER and GRANT statements: &nbsp; Creating a User: By using CREATE USER statement we can just create users and we cannot grant privileges to the user. To use this statement you should have CREATE USER privilege. &nbsp; For Single user: Syntax:&nbsp; CREATE USER &lt;username&gt; IDENTIFIED BY &lt;password&gt; ; &nbsp; For Multiple user: Syntax:&nbsp; CREATE USER &lt;username&gt; IDENTIFIED BY &lt;password&gt;, &lt;username&gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IDENTIFIED BY &lt;password&gt;, &lt;username&gt; IDENTIFIED BY &lt;password&gt;........... ; Using GRANT statement we can create users and as well as we can grant privileges to the users. For using this statement you should have GRANT privilege and you should have privileges that you are granting for others. This statement can be used in many ways. Some examples of them are:
<ol>
<li>mysql&gt;&nbsp; GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY &lt;password&gt; WITH GRANT OPTION;</li>
</ol>
The above statement grants all privileges on all databases to specified user name and host name. If the use name doesn't exists it create a user and grants privileges and if the user exists the password is also changed. If you do not want to change the password use the below query mysql&gt;&nbsp; GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' ;
<ol start="2">
<li>mysql&gt;&nbsp; GRANT SELECT, INSERT, UPDATE ON *.* TO 'username'@'hostname' IDENTIFIED BY &lt;password&gt;;</li>
</ol>
The above statement grants SELECT, INSERT, UPDATE privileges on all databases to specified user name and host name.
<ol start="3">
<li>mysql&gt;&nbsp; GRANT SELECT, INSERT, UPDATE ON DB1.* TO 'username'@'hostname';</li>
</ol>
The above statement grants SELECT, INSERT, UPDATE privileges on DB1 database to specified user name and host name.
<ol start="4">
<li>mysql&gt;&nbsp; GRANT ALL PRIVILEGES ON DB1.TBL1 TO 'username'@'hostname';</li>
</ol>
The above statement grants SELECT, INSERT, UPDATE privileges on DB1.TBL1 table to specified user name and host name.
<ol start="5">
<li>mysql&gt;&nbsp; GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' WITH</li>
</ol>
MAX QUERIES PER HOUR 50 MAX UPDATES PER HOUR 15 MAX CONNECTIONS PER HOUR 3 MAX USER CONNECTIONS 2; The above statement grants all privileges on all databases to specified user name and host name and restricts user resources. By the above query the user can only execute 50 queries per hour, 15 update queries per hour, and can connect max 3 times in an hour and the user can have only 2 simultaneous connections. &nbsp; Renaming a User: To rename a user you must have global CREATE USER privilege. Renaming a user does not automatically migrate all the objects that the user has created. &nbsp; For Single user: Syntax:&nbsp; RENAME USER &lt;old_username&gt; TO &lt;new_username&gt; ; &nbsp; For Multiple user: Syntax:&nbsp; RENAME USER &lt;old_username&gt; TO &lt;new_username&gt;, &lt;old_username&gt; TO &lt;new_username&gt;, &lt;old_username&gt; TO &lt;new_username&gt;.......; &nbsp; Revoking Privileges: mysql&gt;&nbsp; REVOKE ALL PRIVILEGES FROM &lt;username&gt;; The above statement revokes all privileges for the specified user. mysql&gt;&nbsp; REVOKE ALL PRIVILEGES ON DB1.* FROM &lt;username&gt;; The above statement revokes all privileges on DB1 database for the specified user. mysql&gt;&nbsp; REVOKE ALL PRIVILEGES ON DB1.TBL1 FROM &lt;username&gt;; The above statement revokes all privileges on DB1.TBL1 table database for the specified user. &nbsp; Setting Password: The below statement is used to set/update user password. In case if the user forgets his password we can use this statement to reset the password. &nbsp; Syntax:&nbsp; SET PASSWORD FOR '&lt;username&gt;'@'&lt;host name&gt;' = PASSWORD('&lt;password&gt;'); &nbsp; Removing User: To drop a user you should have global CREATE USER privilege. Dropping a user does not automatically close any opened sessions and it does not automatically delete the objects created by the user. &nbsp; Syntax: DROP USER username; &nbsp; IMP:&nbsp;&nbsp; After every DML Command on grant tables we need to issue FLUSH PRIVILEGES Command. Otherwise the permission will not effect until the MySQL Server is restarted. To execute FLUSH PRIVILEGES you need to have the RELOAD privilege. Once the MySQL Server is started it will store all the grant tables in the memory. After every GRANT,&nbsp; REVOKE statements it will reload the grant tables. But by using the DML commands it doesn't reload the tables. This may be the reason DML commands on grant tables are not recommended. &nbsp; Creating a User: If you want just to create a user, insert a row in mysql.user table providing values for host and user. For this you need to have a INSERT privilege on mysql.user table. Syntax: INSERT INTO user (host, user) VALUES ('&lt;host name&gt;', '&lt;user name&gt;'); &nbsp; Granting Global Privileges: To grant all privileges on all the databases insert a row into the mysql.user table. Syntax: INSERT INTO user (host, user, password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv) VALUES ('&lt;hostname&gt;', '&lt;username&gt;', password('&lt;password&gt;', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',&nbsp; 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')); In the above statement if you doesn't want to give any privileges we can set the flag as 'N'. Also we can set below values in the same table. max_questions&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --&gt; &nbsp;&nbsp;Defines max queries for the user per hour. max_updates&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --&gt;&nbsp;&nbsp; Defines max updates for the user per hour. max_connections&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --&gt;&nbsp;&nbsp; Defines max connections for the user per hour. max_user_connections&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; --&gt;&nbsp;&nbsp; Defines max simultaneous connections by the user . &nbsp; Granting Database Level Privileges: If you want to grant privileges on database level we need to insert a row in mysql.db table. For this the user need to have INSERT privilege on mysql.db table.&nbsp;&nbsp; Before Inserting a row in this table a row in mysql.user table need to be inserted for the respective user with all the privileges as &ldquo;N&rdquo;. &nbsp; Granting Table Level Privileges: If you want to grant privileges on table level we need to insert a row in mysql.tables_priv table. For this the user need to have INSERT privilege on mysql.tables_priv table. Before Inserting a row in this table a row in mysql.user table need to be inserted for the respective user with all the privileges as &ldquo;N&rdquo;. &nbsp; Revoking Privileges: If you want to revoke global privileges for user just set value as 'N' in mysql.user table for respective privilege. &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If you want to revoke some database level privileges for a user just set value as 'N' in mysql.db table for respective privilege or if you want to revoke entire privileges on a particular database just delete the row from mysql.db table for specified user and db. If you want to revoke some table level privileges for a user update table_priv field on mysql.tables_priv table or if you want to revoke entire privileges on a particular table just delete the row from mysql.tables_priv table for specified user, db and table. &nbsp; Setting Password: If you want set/update a password execute the below statement. Syntax:&nbsp; mysql&gt; UPDATE mysql.user SET password = PASSWORD('&lt;password&gt;'); &nbsp; Removing User: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Deleting a user need to remove rows from three tables.
<ol>
<li>Delete a record from mysql.user table.</li>
<li>Delete records from mysql.db table.</li>
<li>Delete records from mysql.tables_priv table.</li>
</ol>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Syntax:&nbsp; mysql&gt; DELETE FROM mysql.user WHERE user = '&lt;username&gt;'; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; mysql&gt; DELETE FROM mysql.db WHERE user = '&lt;username&gt;'; mysql&gt; DELETE FROM mysql.tables_priv WHERE user = '&lt;username&gt;'; &nbsp; &nbsp; Resetting Administrator Password: If the administrator forgets the password restart the MySQL Server with --skip-grant-tables option. By this the MySQL stops loading the grant tables and we can login to the MySQL without any password. There you can update password in mysql.user table and start&nbsp; MySQL Server as usual. &nbsp; Follow below steps: [shell]# /etc/init.d/mysqld stop [shell]# /etc/init.d/mysqld start --skip-grant-tables [shell]# mysql mysql&gt; UPDATE mysql.user SET password = PASSWORD('new password') WHERE user = '&lt;username&gt;' AND host = '&lt;host name&gt;'; mysql&gt; \q [shell]# /etc/init.d/mysqld restart &nbsp; <img class="alignnone wp-image-2749" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/327-460x354.png" alt="3" width="423" height="324" /> &nbsp; Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In Houston</a>

MySQL Account Management

User Account Management

user-account-management-mysql

7.1&nbsp; mysql ― The MySQL Command-Line Tool mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options. If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: shell&gt; mysql db_name Or: shell&gt; mysql --user=user_name --password=your_password db_name &nbsp; mysql Options: mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] option file groups. &nbsp; --compress, -C Compress all information sent between the client and the server if both support compression. --database=db_name, -D db_name The database to use. This is useful primarily in an option file. --delimiter=str Set the statement delimiter. The default is the semicolon character (&ldquo;;&rdquo;). --execute=statement, -e statement Execute the statement and quit. --force, -f Continue even if an SQL error occurs. --host=host_name, -h host_name Connect to the MySQL server on the given host. --html, -H Produce HTML output. --password[=password], -p[password] The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one. --port=port_num, -P port_num The TCP/IP port number to use for the connection. --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. --skip-column-names, -N Do not write column names in results. --socket=path, -S path For connections to localhost, the Unix socket file to use. --tee=file_name Append a copy of output to the given file. This option works only in interactive mode. --user=user_name, -u user_name The MySQL user name to use when connecting to the server. --xml, -X Produce XML output. --connect_timeout The number of seconds before connection timeout. &nbsp; 7.2&nbsp; mysqladmin ― Client for Administering a MySQL Server mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more. &nbsp; create db_name Create a new database named db_name. drop db_name Delete the database named db_name and all its tables. flush-logs Flush all logs. flush-privileges&nbsp; (reload) Reload the grant tables (same as reload). flush-status Clear status variables. flush-tables Flush all tables. kill id,id,... Kill server threads. If multiple thread ID values are given, there must be no spaces in the list. ping Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running. processlist Show a list of active server threads. This is like the output of the SHOW PROCESSLIST statement. If the --verbose option is given, the output is like that of SHOW FULL PROCESSLIST. status Display a short server status message. Uptime The number of seconds the MySQL server has been running. Threads The number of active threads (clients). Questions The number of questions (queries) from clients since the server was started. Slow queries The number of queries that have taken more than long_query_time seconds. Opens The number of tables the server has opened. Flush tables The number of flush-*, refresh, and reload commands the server has executed. Open tables The number of tables that currently are open. Queries per second avg Average Queries executed in the server per second. variables Display the server system variables and their values. version Display version information from the server. &nbsp; 7.3&nbsp; mysqlcheck ― A Table Maintenance Program The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables. Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock only. Table maintenance operations can be time-consuming, particularly for large tables. If you use the --databases or --all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time. mysqlcheck is similar in function to myisamchk, but works differently. The main operational difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to perform table maintenance. mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. For details about which storage engines each statement works with, see the descriptions for those statements The MyISAM storage engine supports all four maintenance operations, so mysqlcheck can be used to perform any of them on MyISAM tables. Other storage engines do not necessarily support all operations. In such cases, an error message is displayed. For example, if test.t is a MEMORY table, an attempt to check it produces this result: shell&gt; mysqlcheck test t test.t note&nbsp;&nbsp;&nbsp;&nbsp; : The storage engine for the table doesn't support check &nbsp; Note : It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors. There are three general ways to invoke mysqlcheck: shell&gt; mysqlcheck [options] db_name [tbl_name ...] shell&gt; mysqlcheck [options] --databases db_name ... shell&gt; mysqlcheck [options] &ndash;all-databases If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are checked. &nbsp; Options: --all-databases, -A Check all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line. --analyze, -a Analyze the tables and stores the key distribution for a table. --auto-repair If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked. --check, -c Check the tables for errors. This is the default operation. --check-only-changed, -C Check only tables that have changed since the last check or that have not been closed properly. --databases, -B Process all tables in the named databases. Normally, mysqlcheck treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. --extended, -e If you are using this option to check tables, it ensures that they are 100% consistent but takes a long time. --fast, -F Check only tables that have not been closed properly. --force, -f Continue even if an SQL error occurs. --host=host_name, -h host_name Connect to the MySQL server on the given host. --medium-check, -m Do a check that is faster than an --extended operation. This finds only 99.99% of all errors, which should be good enough in most cases. --optimize, -o Optimize the tables. --password[=password], -p[password] The password to use when connecting to the server. --port=port_num, -P port_num The TCP/IP port number to use for the connection. --quick, -q &nbsp; If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method. &nbsp; --repair, -r Perform a repair that can fix almost anything except unique keys that are not unique. --socket=path, -S path For connections to localhost, the Unix socket file to use --tables Override the --databases or -B option. All name arguments following the option are regarded as table names. --user=user_name, -u user_name The MySQL user name to use when connecting to the server. &nbsp; &nbsp; 7.4&nbsp; mysqldump ― A Database Backup Program &nbsp; Refer 11.&nbsp; Backup and Recovery &nbsp; &nbsp; 7.5&nbsp; mysqlimport ― A Data Import Program &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Refer 11.3.3&nbsp; Dumping Data in Delimited-Text Format &nbsp; 7.6&nbsp; mysqlshow ― Display Database, Table, and Column Information &nbsp; The mysqlshow client can be used to quickly see which databases exist, their tables, or a table's columns or indexes. mysqlshow provides a command-line interface to several SQL SHOW statements. The same information can be obtained by using those statements directly. For example, you can issue them from the mysql client program. &nbsp; Invoke mysqlshow like this: shell&gt; mysqlshow [options] [db_name [tbl_name [col_name]]] &nbsp;
<ul>
<li>If no database is given, a list of database names is shown</li>
<li>If no table is given, all matching tables in the database are shown.</li>
<li>If no column is given, all matching columns and column types in the table are shown.</li>
</ul>
&nbsp; The output displays only the names of those databases, tables, or columns for which you have some privileges. &nbsp; If the last argument contains shell or SQL wildcard characters (&ldquo;*&rdquo;, &ldquo;?&rdquo;, &ldquo;%&rdquo;, or &ldquo;_&rdquo;), only those names that are matched by the wildcard are shown. If a database name contains any underscores, those should be escaped with a backslash to get a list of the proper tables or columns. &ldquo;*&rdquo; and &ldquo;?&rdquo; characters are converted into SQL &ldquo;%&rdquo; and &ldquo;_&rdquo; wildcard characters. This might cause some confusion when you try to display the columns for a table with a &ldquo;_&rdquo; in the name, because in this case, mysqlshow shows you only the table names that match the pattern. This is easily fixed by adding an extra &ldquo;%&rdquo; last on the command line as a separate argument. &nbsp; Options: &nbsp; --count Show the number of rows per table. This can be slow for non-MyISAM tables. --host=host_name --password[=password] --port=port_num --socket=path &nbsp; --keys Show table indexes &nbsp; --status Display extra information about each table (SHOW TABLE STATUS) &nbsp; &nbsp; 7.7&nbsp; mysqlslap ― Load Emulation Client mysqlslap is a program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server. mysqlslap is available as of MySQL 5.1.4. &nbsp; Invoke mysqlslap like this: [shell] #&nbsp; mysqlslap [options] &nbsp; Some options such as --create or --query enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the --delimiter option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file; mysqlslap does not understand them. mysqlslap runs in three stages: &nbsp;
<ol>
<li>Create schema, table, and optionally any stored programs or data you want to using for the test. This stage uses a single client connection.</li>
<li>Run the load test. This stage can use many client connections.</li>
<li>Clean up (disconnect, drop table if specified). This stage uses a single client connection.</li>
</ol>
&nbsp; Examples: &nbsp; Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each: mysqlslap --delimiter=";" \ --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \ --query="SELECT * FROM a" --concurrency=50 &ndash;iterations=200 &nbsp; Tell the program to load the create, insert, and query SQL statements from the specified files, where the create.sql file has multiple table creation statements delimited by ';' and multiple insert statements delimited by ';'. The --query file will have multiple queries delimited by ';'. Run all the load statements, then run all the queries in the query file with five clients. &nbsp; mysqlslap --concurrency=5 --iterations=5 &ndash;query=query.sql --create=create.sql&nbsp;&nbsp; --delimiter=";" mysqlslap supports the following options, which can be specified on the command line or in the [mysqlslap]: &nbsp; --auto-generate-sql, -a Generate SQL statements automatically when they are not supplied in files or using command options. &nbsp; --auto-generate-sql-add-autoincrement Add an AUTO_INCREMENT column to automatically generated tables. This option was added in MySQL 5.1.18. &nbsp; --auto-generate-sql-execute-number=N Specify how many queries to generate automatically. This option was added in MySQL 5.1.18. &nbsp; --auto-generate-sql-secondary-indexes=N Specify how many secondary indexes to add to automatically generated tables. By default, none are added. This option was added in MySQL 5.1.18. &nbsp; --auto-generate-sql-unique-query-number=N How many different queries to generate for automatic tests. For example, if you run a key test that performs 1000 selects, you can use this option with a value of 1000 to run 1000 unique queries, or with a value of 50 to perform 50 different selects. The default is 10. This option was added in MySQL 5.1.18. &nbsp; --create=value The file or string containing the statement to use for creating the table. --delimiter=str, -F str The delimiter to use in SQL statements supplied in files or using command options. &nbsp; --detach=N Detach (close and reopen) each connection after each N statements. The default is 0 (connections are not detached). This option was added in MySQL 5.1.21. &nbsp; --engine=engine_name, -e engine_name The storage engine to use for creating tables. &nbsp; --host=host_name, -h host_name Connect to the MySQL server on the given host. &nbsp; --iterations=N, -i N The number of times to run the tests. &nbsp; --number-char-cols=N, -x N The number of VARCHAR columns to use if --auto-generate-sql is specified. &nbsp; --number-int-cols=N, -y N The number of INT columns to use if --auto-generate-sql is specified. &nbsp; --only-print Do not connect to databases. mysqlslap only prints what it would have done. This option was added in MySQL 5.1.5. &nbsp; --password[=password], -p[password] The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. &nbsp; --port=port_num, -P port_num The TCP/IP port number to use for the connection. &nbsp; --query=value, -q value The file or string containing the SELECT statement to use for retrieving data. &nbsp; --socket=path, -S path For connections to localhost, the Unix socket file to used. &nbsp; --user=user_name, -u user_name The MySQL user name to use when connecting to the server. Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In Chicago</a>

MYSQL Client Program

MySQL Client Programs

mysql-client-programs

8.1&nbsp; myisamchk ― MyISAM Table-Maintenance Utility The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables. You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. Note: It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Invoke myisamchk like this: shell&gt; myisamchk [options] tbl_name ... With no options, myisamchk simply checks your table as the default operation. tbl_name is the database table you want to check or repair. If you run myisamchk somewhere other than in the database directory, you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, myisamchk does not actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there. You can name several tables on the myisamchk command line if you wish. You can also specify a table by naming its index file (the file with the .MYI suffix). This enables you to specify all tables in a directory by using the pattern *.MYI. For example, if you are in a database directory, you can check all the MyISAM tables in that directory like this: shell&gt; myisamchk *.MYI If you are not in the database directory, you can check all the tables there by specifying the path to the directory: shell&gt; myisamchk /path/to/database_dir/*.MYI You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory: shell&gt; myisamchk /path/to/datadir/*/*.MYI Note:&nbsp; You must ensure that no other program is using the tables while you are running myisamchk. The most effective means of doing so is to shut down the MySQL server while running myisamchk, or to lock all tables that myisamchk is being used on. Otherwise, when you run myisamchk, it may display the following error message: warning: clients are using or haven't closed the table properly This means that you are trying to check a table that has been updated by another program (such as the mysqld server) that hasn't yet closed the file or that has died without closing the file properly, which can sometimes lead to the corruption of one or more MyISAM tables. &nbsp; Options: --check, -c Check the table for errors. This is the default operation if you specify no option that selects an operation type explicitly. --fast, -F Check only tables that haven't been closed properly. --analyze, -a Analyze the distribution of key values. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which indexes it should use. To obtain information about the key distribution. --backup, -B Make a backup of the .MYD file as file_name-time.BAK --extend-check, -e Do a repair that tries to recover every possible row from the data file. Normally, this also finds a lot of garbage rows. Do not use this option unless you are desperate. --recover, -r Do a repair that can fix almost any problem except unique keys that are not unique. If you want to recover a table, this is the option to try first. You should try --safe-recover only if myisamchk reports that the table cannot be recovered using --recover. --safe-recover, -o Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found. This is an order of magnitude slower than --recover, but can handle a couple of very unlikely cases that --recover cannot. --unpack, -u Unpack a table that was packed with myisampack. --set-auto-increment[=value], -A[value] Force AUTO_INCREMENT numbering for new records to start at the given value (or higher, if there are existing records with AUTO_INCREMENT values this large). If value is not specified, AUTO_INCREMENT numbers for new records begin with the largest value currently in the table, plus one. &nbsp; 8.2. myisampack &mdash; Generate Compressed, Read-Only MyISAM Tables The myisampack utility compresses MyISAM tables. myisampack works by compressing each column in the table separately. Usually, myisampack packs the data file 40% to 70%. When the table is used later, the server reads into memory the information needed to decompress columns. This results in much better performance when accessing individual rows, because you only have to uncompress exactly one row.
<ul>
<li>If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process. It is safest to compress tables with the server stopped</li>
<li>After packing a table, it becomes read only.</li>
</ul>
Invoke myisampack like this: shell&gt; myisampack [options] file_name ... After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes. Options: --backup, -b Make a backup of each table's data file using the name tbl_name.OLD. --tmpdir=path, -T path Use the named directory as the location where myisampack creates temporary files. &nbsp; Compressed Table Characteristics: Compressed storage format is a read-only format that is generated with the myisampack tool. Compressed tables can be uncompressed with myisamchk.
<ul>
<li>Compressed tables take very little disk space. This minimizes disk usage, which is helpful when using slow disks (such as CDROMs).</li>
<li>Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row in the table. Each column is compressed differently.</li>
<li>Numbers with a value of zero are stored using one bit.</li>
<li>If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (eight bytes) can be stored as a TINYINT column (one byte) if all its values are in the range from -128 to 127.</li>
<li>If a column has only a small set of possible values, the data type is converted to ENUM.</li>
</ul>
<h3>&nbsp;</h3>
<h3>mysql_convert_table_format &mdash; Convert Tables to Use a Given Storage Engine</h3>
mysql_convert_table_format converts the tables in a database to use a particular storage engine (MyISAM by default). mysql_convert_table_format is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see Section&nbsp;2.22, &ldquo;Perl Installation Notes&rdquo;). Invoke mysql_convert_table_format like this: shell&gt; mysql_convert_table_format [options]db_name &nbsp; Options: --host=host_name Connect to the MySQL server on the given host. --password=password The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. --port=port_num The TCP/IP port number to use for the connection. --socket=path For connections to localhost, the Unix socket file to use. --type=engine_name Specify the storage engine that the tables should be converted to use. The default is MyISAM if this option is not given. --user=user_name The MySQL user name to use when connecting to the server. &nbsp; mysql_config &mdash; Get Compile Options for Compiling Clients &nbsp; mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL. About port, socket, include files, libraries, version etc. Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In New york</a>

MySQL Administrative and Utility

mysql-administrative-utility-programs

9.1&nbsp; Server System Variables &nbsp; [client] [mysqld] [mysqldump] [myisamchk] [mysqlhotcopy] &nbsp; auto_increment_increment Controls the interval between successive column values. &nbsp; auto_increment_offset determines the starting point for the&nbsp;AUTO_INCREMENT&nbsp;column value. Autocommit log-error skip-networking &nbsp; init_connect A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled. &nbsp; init_file The name of the file specified with the&nbsp;--init-file&nbsp;option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts. Each statement must be on a single line and should not include comments. No statement terminator such as&nbsp;;,&nbsp;\g, or&nbsp;\G&nbsp;should be given at the end of each statement. &nbsp; join_buffer_size The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. &nbsp;Increase the value of&nbsp;join_buffer_size&nbsp;to get a faster full join when adding indexes is not possible. The maximum permissible setting for&nbsp;join_buffer_size&nbsp;is 4GB. &nbsp; max_allowed_packet The maximum size of one packet or any generated/intermediate string.You must increase this value if you are using large&nbsp;BLOB&nbsp;columns or long strings. &nbsp; max_connect_errors If there are more than this number of interrupted connections from a host, that host is blocked from further connections. You can unblock blocked hosts with the&nbsp;FLUSH HOSTS&nbsp;statement. If a connection is established successfully within fewer than&nbsp;max_connect_errors&nbsp;attempts after a previous connection was interrupted, the error count for the host is cleared to zero. &nbsp; max_connections The maximum permitted number of simultaneous client connections. By default, this is 151, beginning with MySQL 5.1.15. &nbsp; --skip-external-locking Do not use external locking (system locking). This affects only&nbsp;MyISAM&nbsp;table access. &nbsp; wait_timeout(default 28800) The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory. &nbsp; 9.2&nbsp; Server Status Variables The server maintains many status variables that provide information about its operation. You can view these variables and their values by using the&nbsp;SHOW [GLOBAL | SESSION] STATUS&nbsp;statement&nbsp; The optional&nbsp;GLOBAL&nbsp;keyword aggregates the values over all connections, and&nbsp;SESSIONshows the values for the current connection. &nbsp; Aborted_clients The number of connections that were aborted because the client died without closing the connection properly. &nbsp; Aborted_connects The number of failed attempts to connect to the MySQL server. &nbsp; Bytes_received Bytes_sent Uptime Com_commit Com_delete Com_insert Com_select Com_update &nbsp; Max_used_connections The maximum number of connections that have been in use simultaneously since the server started. &nbsp; Queries The number of statements executed by the server. This variable includes statements executed within stored programs. &nbsp; Questions The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs. Select_scan The number of joins that did a full scan. &nbsp; Table_locks_immediate The number of times that a request for a table lock could be granted immediately. &nbsp; Table_locks_waited The number of times that a request for a table lock could not be granted immediately and a wait was needed. If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. &nbsp; Threads_connected The number of currently open connections. &nbsp; Threads_created The number of threads created to handle connections. If&nbsp;Threads_created&nbsp;is big, you may want to increase thethread_cache_size&nbsp;value. The cache miss rate can be calculated as &nbsp;Threads_created/Connections. &nbsp; 9.3&nbsp; Server SQL Modes The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. You can set the default SQL mode by starting&nbsp;mysqld&nbsp;with the&nbsp;--sql-mode="modes"&nbsp;option, or by using&nbsp;sql-mode="modes"&nbsp;in&nbsp;my.cnf. The default value is empty. You can change the SQL mode at runtime by using a&nbsp;SET [GLOBAL|SESSION]&nbsp; sql_mode='modes'&nbsp;statement to set the&nbsp;sql_mode&nbsp;system value.&nbsp; Setting the&nbsp;SESSION&nbsp;variable affects only the current client. Any client can change its own session&nbsp;sql_mode&nbsp;value at any time. You can retrieve the current global or session&nbsp;sql_mode&nbsp;value with the following statements: mysql&gt; SELECT @@GLOBAL.sql_mode; mysql&gt; SELECT @@SESSION.sql_mode; &nbsp; The most important&nbsp;sql_mode&nbsp;values are probably these: &nbsp; ALLOW_INVALID_DATES Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. &nbsp; ANSI_QUOTES Treat &ldquo;"&rdquo; as an identifier quote character (like the &ldquo;`&rdquo; quote character) and not as a string quote character. You can still use &ldquo;`&rdquo; to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier. &nbsp; IGNORE_SPACE Permit spaces between a function name and the &ldquo;(&rdquo; character. &nbsp; NO_AUTO_CREATE_USER Prevent the GRANT statement from automatically creating new users&nbsp; unless nonempty password is specified. &nbsp; NO_DIR_IN_CREATE When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers. &nbsp; NO_ENGINE_SUBSTITUTION Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in. &nbsp; NO_ZERO_IN_DATE In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If used with the IGNORE option, MySQL inserts a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated. &nbsp; PIPES_AS_CONCAT Treat || as a string concatenation operator. &nbsp; REAL_AS_FLOAT Treat REAL as a synonym for FLOAT. &nbsp; STRICT_TRANS_TABLES &amp; STRICT_ALL_TABLES Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back. For non-transactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled: &nbsp;
<ul>
<li>For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it is best to use single-row statements because these can be aborted without changing the table.</li>
<li>For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement.</li>
</ul>
&nbsp; ANSI This mode changes syntax and behavior to confirm more closely to standard SQL REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI &nbsp; TRADITIONAL Make MySQL behave like a &ldquo;traditional&rdquo; SQL database system. A simple description of this mode is &ldquo;give an error instead of a warning&rdquo; when inserting an incorrect value into a column. &nbsp; Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a &ldquo;partially done&rdquo; update. &nbsp; Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER. Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-dba-training/">SQL Server DBA Training In San Jose</a> &nbsp;

MYSQL Server Administration

MySQL Server Administration

mysql-server-administration

10.1&nbsp; The Error Log The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log. You can specify where mysqld writes the error log with the --log-error[=file_name] option. If the option is given with no file_name value, mysqld uses the name host_name.err by default. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell&gt; mv host_name.err host_name.err-old shell&gt; mysqladmin flush-logs shell&gt; mv host_name.err-old backup-directory The --log-warnings option or log_warnings system variable can be used to control warning logging to the error log. The default value is enabled (1). Warning logging can be disabled using a value of 0. If the value is greater than 1, aborted connections are written to the error log. &nbsp; 10.2&nbsp; The General Query Log The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld. Control the general query log at server startup as follows:
<ul>
<li>Before 5.1.6, the general query log destination is always a file. To enable the log, start mysqld with the &ndash;log[=file_name].</li>
<li>As of MySQL 5.1.6, the destination can be a file or a table, or both. Start mysqld with the &ndash;log[=file_name] and optionally use --log-output to specify the log destination.</li>
</ul>
&nbsp; --log-output This option determines the destination for general query and slow query log output. The option value can be given as one or more of the words TABLE, FILE, or NONE. If the option is given without a value, the default is FILE. TABLE select logging to the general_log and slow_log tables in the mysql database as a destination. FILE selects logging to log files as a destination. NONE disables logging. This option selects log output destinations, but does not enable log output.
<ul>
<li>As of MySQL 5.1.12, as an alternative to --log or -l, use --general_log[={0|1}] to specify the initial general query log state.</li>
<li>As of MySQL 5.1.12, use --general_log[={0|1}] to enable or disable the general query log, and optionally --general_log_file=file_name to specify a log file name. The --log option is deprecated.</li>
</ul>
If you specify no name for the general query log file, the default name is host_name.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. From&nbsp; MySQL 5.1.12 we can control the general query log at runtime. Use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell&gt; mv host_name.log host_name-old.log shell&gt; mysqladmin flush-logs shell&gt; mv host_name-old.log backup-directory The session sql_log_off variable can be set to ON or OFF to disable or enable general query logging for the current connection. &nbsp; 10.3&nbsp; The Binary Log The binary log contains &ldquo;events&rdquo; that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:
<ul>
<li>For replication purpose.</li>
<li>Data recovery operations.</li>
</ul>
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement. The binary log is not used for statements such as SELECT or SHOW that do not modify data. The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported.
<ol>
<li>statement-based logging (causes logging to be statement-based)</li>
<li>row-based logging (causes logging to be row-based) (5.1.5)</li>
<li>mixed-base logging (causes logging to use mixed format) (5.1.8)</li>
</ol>
Statement-Based Logging: Propogates all the SQL statements which are executed in the server. Row-Based Logging: writes events to the binary log that indicate how individual table rows are affected. Mixed-Logging: With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases.
<h4>&nbsp;</h4>
<h4>Setting The Binary Log Format</h4>
The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable: mysql&gt; SET GLOBAL binlog_format = 'STATEMENT'; mysql&gt; SET GLOBAL binlog_format = 'ROW'; mysql&gt; SET GLOBAL binlog_format = 'MIXED'; An individual client can control the logging format for its own statements by setting the session value of binlog_format: mysql&gt; SET SESSION binlog_format = 'STATEMENT'; mysql&gt; SET SESSION binlog_format = 'ROW'; mysql&gt; SET SESSION binlog_format = 'MIXED'; &nbsp; To change the global binlog_format value, you must have the SUPER privilege.
<h4>&nbsp;</h4>
<h4>Binary Log Options and Variables</h4>
--log-bin[=base_name] Enable binary logging. The server logs all statements that change data to the binary log. &nbsp; binlog_format (5.1.5) This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. &nbsp; --log-bin-index[=file_name] The index file for binary log file names. If you omit the file name, and if you did not specify one with --log-bin, MySQL uses host_name-bin.index as the file name. &nbsp; --binlog-do-db=db_name Logs only the specific database changes. &nbsp; Statement-based logging: Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such as UPDATE some_db.some_table SET foo='bar' to be logged while a different database (or no database) is selected. &nbsp; Row-based logging:&nbsp; Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the default database has no effect on this. Suppose that the server is started with --binlog-do-db=sales and row-based logging is in effect, and then the following statements are executed: USE prices; UPDATE sales.february SET amount=amount+100; The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. &nbsp; --binlog-ignore-db=db_name &nbsp; Statement-based logging:&nbsp; Tells the server to not log any statement where the default database (that is, the one selected by USE) is db_name. &nbsp; Row-based format:&nbsp; Tells the server not to log updates to any tables in the database db_name. The current database has no effect. &nbsp; --binlog_cache_size The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled. &nbsp; --max_binlog_size If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs. The minimum value is 4096 bytes. The maximum and default value is 1GB. &nbsp; --sync_binlog If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk&mdash;in this case, the server relies on the operating system to flush the binary log's contents from time to time. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, But it is also the slowest choice. mysqld appends a numeric extension to the binary log basename to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size. A binary log file may become larger than max_binlog_size if you are using large transactions because a transaction is written to the file in one piece, never split between files. To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same basename as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option. You should not manually edit this file while mysqld is running; doing so would confuse mysqld. &nbsp; Deleting Binary Logs: You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. mysql&gt; RESET MASTER; mysql&gt; PURGE BINARY|MASTER LOGS TO &lsquo;&lt;filename&gt;&rsquo;; A client that has the SUPER privilege can disable binary logging of its own statements by using a SET sql_log_bin=0 statement. You can display the contents of binary log files with the mysqlbinlog utility. mysql&gt; mysqlbinlog &lt;filename&gt; &gt; log.txt; &nbsp; Options: &nbsp; --database=db_name, -d db_name This option causes mysqlbinlog to output entries from the binary log (local log only) that occur while db_name is been selected as the default database by USE. &nbsp; --host=host_name, -h host_name --password[=password], -p[password] --port=port_num, -P port_num --socket=path, -S path &nbsp; --result-file=name, -r name Direct output to the given file. &nbsp; --start-datetime=datetime Start reading the binary log at the first event having a timestamp equal to or later than the datetime argument. The datetime value is relative to the local time zone on the machine where you run mysqlbinlog. Shell &gt; mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003 --stop-datetime=datetime Stop reading the binary log at the first event having a timestamp equal to or later than the datetime argument. This option is useful for point-in-time recovery. &nbsp; --start-position=N, -j N Start reading the binary log at the first event having a position equal to or greater than N. This option applies to the first log file named on the command line. This option is useful for point-in-time recovery. &nbsp; --stop-position=N Stop reading the binary log at the first event having a position equal to or greater than N. This option applies to the last log file named on the command line. This option is useful for point-in-time recovery. &nbsp; 10.4&nbsp; The Slow Query Log The slow query log consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might be different from execution order. To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option. &nbsp; --long_query_time (min=1, default=10) If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If you are using the --log-slow-queries option, the query is logged to the slow query log file.
<ul>
<li>Before 5.1.6, the slow query log destination is always a file.</li>
<li>As of MySQL 5.1.6, the destination can be a file or a table.</li>
<li>As of MySQL 5.1.12, as an alternative to --log-slow-queries, use --slow_query_log[={0|1}] to specify the initial slow query log state. In this case, the default slow query log file name is used.</li>
<li>As of MySQL 5.1.29, use --slow_query_log[={0|1}] to enable or disable the slow query log, and optionally --slow_query_log_file=file_name to specify a log file name. The --log-slow-queries option is deprecated.</li>
</ul>
If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory. Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In Hyderabad</a>

MYSQL Server Log

MySQL Server Logs

mysql-server-logs

MySQL Backup Recovery

Backup and Recovery

mysql-backup-recovery

In some cases, you might want to run multiple instances of MySQL on a single machine. You might want to test a new MySQL release while leaving an existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves. It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.0 and one from MySQL 5.1, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases. Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances. &nbsp; 12.1.&nbsp; Setting Up Multiple Data Directories Each MySQL Instance on a machine should have its own data directory. The location is specified using the --datadir=path option. There are different methods of setting up a data directory for a new instance:
<ul>
<li>Create a new data directory</li>
<li>Copy an existing data directory</li>
</ul>
&nbsp; Note: Normally, you should never have two servers that update data in the same databases. This may lead to unpleasant surprises if your operating system does not support fault-free system locking. If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers try to log to the same files. &nbsp; Create a New Data Directory: With this method, the data directory will be in the same state as when you first install MySQL. It will have the default set of MySQL accounts and no user data.On Unix, initialize the data directory by running mysql_install_db. &nbsp; Copy an Existing Data Directory: With this method, any MySQL accounts or user data present in the data directory are carried over to the new data directory.
<ol>
<li>Stop the existing MySQL instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes to disk.</li>
<li>Copy the data directory to the location where the new data directory should be.</li>
<li>Copy the my.cnf or my.ini option file used by the existing instance. This serves as a basis for the new instance.</li>
<li>Modify the new option file so that any pathnames referring to the original data directory refer to the new data directory. Also, modify any other options that must be unique per instance, such as the TCP/IP port number and the log files.</li>
<li>Start the new instance, telling it to use the new option file.</li>
</ol>
&nbsp; 12.2.&nbsp; Running Multiple MySQL Instances on Unix: One way is to run multiple MySQL instances on Unix is to compile different servers with different default TCP/IP ports and Unix socket files so that each one listens on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server. Assume that an existing 5.0 server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new 5.1.57 server to have different operating parameters, use a configure command something like this: shell&gt; ./configure --with-tcp-port=port_number \ --with-unix-socket-path=file_name \ --prefix=/usr/local/mysql-5.1.57 Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file path name, and the --prefix value should specify an installation directory different from the one under which the existing MySQL installation is located. If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket file name: shell&gt; mysqladmin --host=host_name --port=port_number variables You need not compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to use the same server binary and start each invocation of it with different parameter values at runtime. One way to do so is by using command-line options: shell&gt; mysqld_safe --socket=file_name --port=port_number To start a second server, provide different --socket and --port option values, and pass a &ndash;datadir=path option to mysqld_safe so that the server uses a different data directory. Alternatively, put the options for each server in a different option file, then start each server using a --defaults-file option that specifies the path to the appropriate option file. For example, if the option files for two server instances are named /usr/local/mysql/my.cnf and /usr/local/mysql/my.cnf2, start the servers like this: command: shell&gt; mysqld_safe --defaults-file=/usr/local/mysql/my.cnf shell&gt; mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2 &nbsp; Using Client Programs in a Multiple-Server Environment: To connect with a client program to a MySQL server that is listening to different network interfaces from those compiled into your client, you can use one of the following methods:
<ul>
<li>Start the client with --host=host_name --port=port_number to connect using TCP/IP to a remote server, with --host=127.0.0.1 --port=port_number to connect using TCP/IP to a local server, or with --host=localhost --socket=file_name to connect to a local server using a Unix socket file.</li>
</ul>
<h4>&nbsp;</h4>
<h4>MySQL Instance Manager</h4>
MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 5.5. &nbsp; 12.3&nbsp; Using mysqld_multi for Managing Multiple MySQL Servers mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status. mysqld_multi searches for groups named [mysqldN] in my.cnf (or in the file named by the --config-file option). N can be any positive integer. This number is referred to in the following discussion as the option group number, or GNR.
<ol>
<li>Initialize the new datadir.</li>
<li>Change the option file.</li>
</ol>
Sample Option file is as below: [mysqld_multi] mysqld&nbsp;&nbsp;&nbsp;&nbsp; = /usr/local/bin/mysqld_safe mysqladmin = /usr/local/bin/mysqladmin user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = multi_admin password&nbsp;&nbsp; = multipass &nbsp; [mysqld2] socket&nbsp;&nbsp;&nbsp;&nbsp; = /tmp/mysql.sock2 port&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 3307 pid-file&nbsp;&nbsp; = /usr/local/mysql/var2/hostname.pid2 datadir&nbsp;&nbsp;&nbsp; = /usr/local/mysql/var2 language&nbsp;&nbsp; = /usr/local/share/mysql/english user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = john [mysqld3] socket&nbsp;&nbsp;&nbsp;&nbsp; = /tmp/mysql.sock3 port&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = 3308 pid-file&nbsp;&nbsp; = /usr/local/mysql/var3/hostname.pid3 datadir&nbsp;&nbsp;&nbsp; = /usr/local/mysql/var3 language&nbsp;&nbsp; = /usr/local/share/mysql/swedish user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = monty &nbsp; To invoke mysqld_multi, use the following syntax: shell&gt; mysqld_multi [options] {start|stop|report} [GNR[,GNR] ...] start, stop, and report indicate which operation to perform. You can perform the designated operation for a single server or multiple servers, depending on the GNR list that follows the option name. If there is no list, mysqld_multi performs the operation for all servers in the option file. Each GNR value represents an option group number or range of group numbers. The value should be the number at the end of the group name in the option file. For example, the GNR for a group named [mysqld17] is 17. To specify a range of numbers, separate the first and last numbers by a dash. The GNR value 10-13 represents groups [mysqld10] through [mysqld13]. Multiple groups or group ranges can be specified on the command line, separated by commas. This command starts a single server using option group [mysqld17]: shell&gt; mysqld_multi start 17 This command stops several servers, using option groups [mysqld8] and [mysqld10] through [mysqld13]: shell&gt; mysqld_multi stop 8,10-13 For an example of how you might set up an option file, use this command: shell&gt; mysqld_multi --example &nbsp; Options: &nbsp; --example &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Display a sample option file. --mysqladmin=prog_name &nbsp;&nbsp;&nbsp; The mysqladmin binary to be used to stop servers. --mysqld=prog_name&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The mysqld binary to be used. --password=password &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The password of the MySQL account to use when invoking mysqladmin. --user=user_name&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The user name of the MySQL account to use when invoking mysqladmin. Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In Bangalore</a>

Running Multiple MySQL Instances on One Machine

running-multiple-mysql-instances-one-machine

13.1.&nbsp; General Security Guidelines
<ul>
<li>Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! This is critical.</li>
<li>Learn the MySQL access privilege system. The GRANT and REVOKE statements are used for controlling access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts.</li>
<li>Do not store any plaintext passwords in your database. Instead, use MD5(), SHA1(), or some other one-way hashing function and store the hash value.</li>
<li>Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone.</li>
<li>See what are the privileges provided by the MySQL.</li>
</ul>
Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In Bangalore</a>

General Security Issues

security-issues-mysql

Optimization is a complex task because ultimately it requires understanding of the entire system to be optimized. Although it may be possible to perform some local optimizations with little knowledge of your system or application, the more optimal you want your system to become, the more you must know about it. &nbsp; 14.1&nbsp; Optimization Overview The most important factor in making a system fast is its basic design. You must also know what kinds of processing your system is doing, and what its bottlenecks are. In most cases, system bottlenecks arise from these sources: &nbsp; Disk seeks: It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk. &nbsp; Disk reading and writing: When the disk is at the correct position, we need to read the data. With modern disks, one disk delivers at least 10&ndash;20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks. &nbsp; CPU cycles: When we have the data in main memory, we need to process it to get our result. Having small tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem. &nbsp; &nbsp;14.2&nbsp; Obtaining Query Execution Plan Information &nbsp; 14.2.1&nbsp; Optimizing Queries with EXPLAIN The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a SELECT statement or as a synonym for DESCRIBE.
<ul>
<li>When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order.</li>
<li>EXPLAIN PARTITIONS is available beginning with MySQL 5.1.5. It is useful only when examining queries involving partitioned tables.</li>
<li>EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name.</li>
</ul>
If you have a problem with indexes not being used when you believe that they should be, you should run ANALYZE TABLE to update table statistics such as cardinality of keys, that can affect the choices the optimizer makes. &nbsp; EXPLAIN Output Format: EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table. &nbsp; Example: mysql&gt; EXPLAIN SELECT * FROM tbl_a a, tbl_b b where a.id = b.id; &nbsp; +----+---------------+-------+-----------+-------------------+--------------+------------+--------+------+-------+ | id | select_type | table | type&nbsp;&nbsp;&nbsp;&nbsp; | possible_keys | key&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | key_len&nbsp; | ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | rows | Extra | +----+---------------+-------+-----------+-------------------+--------------+------------+--------+------+-------+ |&nbsp; 1 | SIMPLE&nbsp;&nbsp;&nbsp;&nbsp; | b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | system | PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | NULL&nbsp;&nbsp;&nbsp;&nbsp; | NULL&nbsp; |&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | |&nbsp; 1 | SIMPLE&nbsp;&nbsp;&nbsp;&nbsp; | a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | const&nbsp;&nbsp;&nbsp; | PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | PRIMARY | 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | const&nbsp; |&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | +----+---------------+-------+-----------+-------------------+--------------+------------+---------+------+-------+ &nbsp; 14.2.2&nbsp; Optimizing SELECT Statements First, one factor affects all statements: The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you do not grant any table-level or column-level privileges, the server need not ever check the contents of the tables_priv and columns_priv tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high statement-processing load, it may be worth the time to use a simplified grant structure to reduce permission-checking overhead. If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is &nbsp; Syntax: mysql &gt; BENCHMARK(loop_count,expression); &nbsp; The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute. For example: &nbsp; mysql&gt; SELECT BENCHMARK(1000000,1+1); +---------------------------------------+ | BENCHMARK(1000000,1+1) | +---------------------------------------+ | 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | +---------------------------------------+ 1 row in set (0.32 sec) &nbsp; It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system. &nbsp; Speed of SELECT Statements: In general, when you want to make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. You can use the EXPLAIN statement to determine which indexes are used for a SELECT. Some general tips for speeding up queries on MyISAM tables:
<ul>
<li>To help MySQL better optimize queries, use ANALYZE TABLE or run myisamchk --analyze on a table after it has been loaded with data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1.) MySQL uses this to decide which index to choose when you join two tables based on a nonconstant expression. You can check the result from the table analysis by using SHOW INDEX FROM tbl_name and examining the Cardinality value.</li>
<li>To sort an index and data according to an index, use myisamchk --sort-index (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.</li>
</ul>
&nbsp; WHERE Clause Optimization: This section discusses optimizations that can be made for processing WHERE clauses. The examples use SELECT statements, but the same optimizations apply for WHERE clauses in DELETE and UPDATE statements.
<ul>
<li>Removal of unnecessary parentheses:</li>
</ul>
((a AND b) AND c OR (((a AND b) AND (c AND d)))) =&gt; (a AND b AND c) OR (a AND b AND c AND d)
<ul>
<li>Constant folding:</li>
</ul>
(a&lt;b AND b=c) AND a=5 =&gt; b&gt;5 AND b=c AND a=5 &nbsp; In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
<ul>
<li>Remove non key columns in WHERE clause.</li>
<li>Should not use IN, LIKE operators.</li>
<li>Avoid sub queries and queries in FROM clause.</li>
<li>Use functions in the left side of the condition.</li>
<li>Should not use DISTINCT clause, instead use GROUP BY clause.</li>
<li>Do not create duplicate indexes.</li>
</ul>
&nbsp; 14.3.&nbsp; Tuning Server Parameters For a mysqld server that is currently running, you can see the current values of its system variables by connecting to it and issuing this statement: mysql&gt; SHOW VARIABLES; You can also see some statistical and status indicators for a running server by issuing this statement: mysql&gt; SHOW STATUS; System variable and status information also can be obtained using mysqladmin: shell&gt; mysqladmin variables shell&gt; mysqladmin extended-status &nbsp; When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_ open_cache. You should first feel confident that you have these set appropriately before trying to change any other variables. If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, you should increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations. &nbsp; Temp variables: You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. &nbsp; Server variable : tmp_table_size (Need to be increased depending upon the below values) Status variables : Created_tmp_disk_tables, Created_tmp_tables. &nbsp; Query Cache: The cache is not used for queries of the following types:
<ol>
<li>Queries that are a subquery of an outer query</li>
<li>Queries executed within the body of a stored function, trigger, or event</li>
</ol>
A query cannot be cached if it contains any of the functions like CURDATE(), NOW(), CONVERT_TZ(), CURTIME(), DATABASE(), RAND(), UUID() etc. A query also is not cached under these conditions:
<ol>
<li>It refers to tables in the mysql or INFORMATION_SCHEMA system database.</li>
<li>SELECT ... INTO OUTFILE.</li>
<li>It uses TEMPORARY tables.</li>
<li>It does not use any tables.</li>
<li>It generates warnings.</li>
</ol>
Two query cache-related options may be specified in SELECT statements: &nbsp; SQL_CACHE The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND. &nbsp; SQL_NO_CACHE The query result is not cached. &nbsp; Examples: SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;
<h4>Query Cache Configuration:</h4>
The have_query_cache server system variable indicates whether the query cache is available: mysql&gt; SHOW VARIABLES LIKE 'have_query_cache'; +--------------------------+--------+ | Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value | +--------------------------+--------+ | have_query_cache | YES&nbsp;&nbsp; | +--------------------------+--------+ &nbsp; To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default. If the query cache size is greater than 0, the query_cache_type variable influences how it works. This variable can be set to the following values:
<ol>
<li>A value of 0 or OFF prevents caching or retrieval of cached results.</li>
<li>A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.</li>
<li>A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.</li>
</ol>
Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this: mysql&gt; SET SESSION query_cache_type = 0; When a query is to be cached, its result (the data sent to the client) is stored in the query cache during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable. When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:
<ul>
<li>The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.</li>
<li>If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large.</li>
</ul>
The RESET QUERY CACHE statement, to removes all query results from the query cache execute the FLUSH TABLES statement. &nbsp; Status variables: Qcache_free_blocks The number of free memory blocks in the query cache. &nbsp; qcache_hits Whenever MySQL performs a SELECT operation, it either increments com_select or the qcache_hits status variables.&nbsp; com_selects thus show us the cache misses. So get the hit ratio by this formula:&nbsp; qcache_hits / (qcache_hits + com_select) which gives us .9999 or 99.99%. &nbsp; Qcache_inserts The number of queries added to the query cache. &nbsp; Qcache_lowmem_prunes The number of queries that were deleted from the query cache because of low memory. &nbsp; Qcache_not_cached The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting). The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. &nbsp; Other Optimizations: sort_buffer_size If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. &nbsp; myisam_sort_buffer_size The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. &nbsp; thread_cache_size How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. &nbsp; wait_timeout The number of seconds the server waits for activity on a noninteractive connection before closing it.

MySQL Optimization Tutorial

Optimization

mysql-optimization-tutorial

15.1&nbsp; Overview of Partitioning in MySQL A partition is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance reasons. The partitioning can be done by either building separate smaller databases, or by splitting selected elements, for example just one table. Partitioning enables you to distribute portions of individual tables across a file system according to rules which you can set largely as needed.In effect, different portions of a table are stored as separate tables in different locations. The user-selected rule by which the division of data is accomplished is known as a partitioning function, which in MySQL can be the modulus, simple matching against a set of ranges or value lists, an internal hashing function, or a linear hashing function. The function is selected according to the partitioning type specified by the user. In MySQL 5.1, all partitions of the same partitioned table must use the same storage engine; for example,&nbsp;you cannot use MyISAM for one partition and InnoDB for another. However, there is nothing preventing you from using different storage engines for different partitioned tables. &nbsp; 15.1&nbsp; Partitioning Types This section discusses the types of partitioning which are available in MySQL 5.1.
<ol>
<li>RANGE Partitioning</li>
<li>LIST Partitioning</li>
<li>HASH Partitioning</li>
<li>KEY Partitioning</li>
</ol>
&nbsp; RANGE Partitioning: RANGE partitioning contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. Suppose if you are creating a table to hold the employee details as below: &nbsp; CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ); &nbsp; This table can be partitioned by range in a number of ways, depending on your needs. One way would be to use the dept_id column. For instance, you might decide to partition the table 4 ways by adding a PARTITION BY RANGE clause as shown here: &nbsp; CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ) engine = myisam PARTITION BY RANGE (dept_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) ); &nbsp; In this partitioning scheme, all rows corresponding to employees working at dept 1 through 5 are stored in partition p0, to those employed at dept 6 through 10 are stored in partition p1, and so on. Note that each partition is defined in order, from lowest to highest. This is a requirement of the PARTITION BY RANGE syntax; you can think of it as being analogous to a series of if ... elseif ... statements in C or Java in this regard. It is easy to determine that a new row containing the data (72, 'Michael', 'Widenius', '1998-06-25' 13) is inserted into partition p2, but what happens when your chain adds a 21st dept_id? Under this scheme, there is no rule that covers a row whose dept_id is greater than 20, so an error results because the server does not know where to place it. You can keep this from occurring by using a &ldquo;catchall&rdquo; VALUES LESS THAN clause in the CREATE TABLE statement that provides for all values greater than the highest value explicitly named. CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ) engine = myisam PARTITION BY RANGE (dept_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE ); &nbsp; MAXVALUE represents an integer value that is always greater than the largest possible integer value. Now, any rows whose dept_id column value is greater than or equal to 16 are stored in partition p3. At some point in the future when the number of depts has increased to 25, 30, or more you can use an ALTER TABLE statement to add new partitions for stores 21-25, 26-30, and so on. Rather than splitting up the table data according to dept number, you can use an expression based DATE column instead. For example, let us suppose that you wish to partition based on the year that each employee joined the company. An example of a CREATE TABLE statement that implements such a partitioning scheme is shown here: &nbsp; CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ) engine = myisam PARTITION BY RANGE ( YEAR(hired) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE ); &nbsp; In this scheme, for all employees who joined before 1991, the rows are stored in partition p0; for those who joined in the years 1991 through 1995, in p1; for those who joined in the years 1996 through 2000, in p2; and for any workers who joined after the year 2000, in p3. Range partitioning is particularly useful when you want or need to delete &ldquo;old&rdquo; data. If you are using the partitioning scheme shown immediately above, you can simply use ALTER TABLE employees DROP PARTITION p0; to delete all rows relating to employees who stopped working for the firm prior to 1991. For a table with a great many rows, this can be much more efficient than running a DELETE query such as DELETE FROM employees WHERE YEAR(separated) &lt;= 1990;. &nbsp; LIST Partitioning: List partitioning in MySQL is similar to range partitioning in many ways. As in partitioning by RANGE, each partition must be explicitly defined. The chief difference between the two types of partitioning is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. Unlike the case with partitions defined by range, list partitions do not need to be declared in any particular order. Suppose if you are creating a table to hold the employee details as below: &nbsp; CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ); &nbsp; Suppose that there are 20 departments distributed among 4 franchises as shown in the following table.
<table>
<tbody>
<tr>
<td width="66">Branch</td>
<td width="180">Dept ID Numbers</td>
</tr>
<tr>
<td width="66">B1</td>
<td width="180">3, 5, 6, 9, 17</td>
</tr>
<tr>
<td width="66">B2</td>
<td width="180">1, 2, 10, 11, 19, 20</td>
</tr>
<tr>
<td width="66">B3</td>
<td width="180">4, 12, 13, 14, 18</td>
</tr>
<tr>
<td width="66">B4</td>
<td width="180">7, 8, 15, 16</td>
</tr>
</tbody>
</table>
&nbsp; To partition this table in such a way that rows for departments belonging to the same branch are stored in the same partition, you could use the CREATE TABLE statement shown here: CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ) PARTITION BY LIST(dept_id) ( PARTITION pB1 VALUES IN (3,5,6,9,17), PARTITION pB2 VALUES IN (1,2,10,11,19,20), PARTITION pB3 VALUES IN (4,12,13,14,18), PARTITION pB4 VALUES IN (7,8,15,16) ); &nbsp; This makes it easy to add or drop employee records relating to specific branch from the table. For instance, suppose that all departments in the B3 branch are removed. All rows relating to employees working at particular departments in that B3&nbsp; branch can be deleted with an ALTER TABLE employees DROP PARTITION pB3 statement, which can be executed much more efficiently than the equivalent DELETE statement (DELETE FROM employees WHERE dept_id IN (4,12,13,14,18)). However, the ALTER TABLE ... DROP PARTITION statement also removes the partition itself from the definition for the table, and you must execute an ALTER TABLE ... ADD PARTITION statement to restore the table's original partitioning scheme. (This problem is resolved in MySQL 5.5, which adds the ALTER TABLE ... TRUNCATE PARTITION statement for removing all rows from a given partition without affecting the table definition.) Unlike the case with RANGE partitioning, there is no &ldquo;catch-all&rdquo; such as MAXVALUE; all expected values for the partitioning expression should be covered in PARTITION ... VALUES IN (...) clauses. An INSERT statement containing an unmatched partitioning column value fails with an error, as shown in this example: mysql&gt; INSERT INTO employees VALUES (101, &lsquo;abc&rsquo;, &lsquo;xyz&rsquo;, NOW(), 41); ERROR 1525 (HY000): Table has no partition for value 41 When inserting multiple rows using a single INSERT statement, any rows coming before the row containing the unmatched value are inserted, but any coming after it are not. &nbsp; HASH Partitioning: Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided. To partition a table using HASH partitioning, just append to the CREATE TABLE statement a PARTITION BY HASH (expr) clause, where expr is an expression that returns an integer. This can simply be the name of a column whose type is one of MySQL's integer types. In addition, you will most likely want to follow this with a PARTITIONS num clause, where num is a positive integer representing the number of partitions into which the table is to be divided. For example, the following statement creates a table that uses hashing on the dept_id column and is divided into 4 partitions: CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ) PARTITION BY HASH(dept_id) PARTITIONS 4; &nbsp; If you do not include a PARTITIONS clause, the number of partitions defaults to 1. Using the PARTITIONS keyword without a number following it results in a syntax error. You can also use an SQL expression that returns an integer for expr. For instance, you might want to partition based on the year in which an employee was hired. This can be done as shown here: CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', dept_id INT NOT NULL ) PARTITION BY HASH(YEAR(hired)) PARTITIONS 4; &nbsp; expr must return a nonconstant, nonrandom integer value (in other words. You should also keep in mind that this expression is evaluated each time a row is inserted or updated, this means that very complex expressions may give rise to performance issues, particularly when performing operations (such as batch inserts) that affect a great many rows at one time. When PARTITION BY HASH is used, MySQL determines which partition of num partitions to use based on the modulus of the result of the user function. In other words, for an expression expr, the partition in which the record is stored is partition number N, where N = MOD(expr, num). Suppose that table t1 is defined as follows, so that it has 4 partitions: &nbsp; CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; If you insert a record into t1 whose col3 value is '2005-09-15', then the partition in which it is stored is determined as follows: MOD(YEAR('2005-09-01'),4) =&nbsp; MOD(2005,4) =&nbsp; 1 &nbsp; KEY Partitioning: Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. MySQL Cluster uses MD5() for this purpose; for tables using other storage engines, the server employs its own internal hashing function which is based on the same algorithm as PASSWORD(). The syntax rules for CREATE TABLE ... PARTITION BY KEY are similar to those for creating a table that is partitioned by hash. The major differences are listed here: - KEY is used rather than HASH. - KEY takes only a list of one or more column names. Beginning with MySQL 5.1.5, the column or columns used as the partitioning key must comprise part or all of the table's primary key, if the table has one. Beginning with MySQL 5.1.6, KEY takes a list of zero or more column names. Where no column name is specified as the partitioning key, the table's primary key is used, if there is one. For example, the following CREATE TABLE statement is valid in MySQL 5.1.6 or later: CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2; &nbsp; If there is no primary key but there is a unique key, then the unique key is used for the partitioning key: CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2; In both of these cases, the partitioning key is the id column, even though it is not shown in the output of SHOW CREATE TABLE or in the PARTITION_EXPRESSION column of the INFORMATION_SCHEMA.PARTITIONS table. Unlike the case with other partitioning types, columns used for partitioning by KEY are not restricted to integer or NULL values. For example, the following CREATE TABLE statement is valid: &nbsp; CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY ) PARTITION BY KEY(s1) PARTITIONS 10; &nbsp; Managing partitions: Range and list partitions are very similar with regard to how the adding and dropping of partitions are handled. Tables which are partitioned by hash or by key are very similar to one another with regard to making changes in a partitioning setup. &nbsp; Dropping a Partition: Dropping a partition from a table that is partitioned by either RANGE or by LIST can be accomplished using the ALTER TABLE statement with a DROP PARTITION clause. mysql&gt; ALTER TABLE &lt;table_name&gt; DROP PARTITION p2; It is very important to remember that, when you drop a partition, you also delete all the data that was stored in that partition. You can see that this is the case by re-running the previous SELECT query. If you wish to drop all data from all partitions while preserving the table definition and its partitioning scheme, use the TRUNCATE TABLE statement. If you intend to change the partitioning of a table without losing data, use ALTER TABLE ... REORGANIZE PARTITION instead. You cannot drop partitions from tables that are partitioned by HASH or KEY in the same way that you can from tables that are partitioned by RANGE or LIST. However, you can merge HASH or KEY partitions using the ALTER TABLE ... COALESCE PARTITION statement. &nbsp; Adding a Partition: mysql&gt; ALTER TABLE &lt;table_name&gt; ADD PARTITION (PARTITION p0 VALUES LESS THAN (50)); VALUES LESS THAN value must be strictly increasing for each partition. &nbsp; Merge partitions: mysql&gt; ALTER TABLE &lt;table_name&gt; REORGANIZE PARTITION p0, p1 INTO (PARTITION p01 VALUES LESS THAN (10)); VALUES LESS THAN value must be strictly 2nd partion MAX VALUE REORGANIZE PARTITION can be used by any partitioning type but for hash partitions the new number of partitions must be the same as those reorganised. mysql&gt; ALTER TABLE t1 COALESCE PARTITION 2; This command can be used to merge partitions in a hash partition. No data is lost. &nbsp; Split partitions: mysql&gt; ALTER TABLE &lt;table_name&gt; REORGANIZE PARTITION p01 INTO&nbsp; (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (16)); &nbsp; Rebuild partitions: mysql&gt; ALTER TABLE &lt;table_name&gt; REBUILD PARTITION p0, p1 Rebuild the partitions, can be a method to remove fragmentation as an example. Optimise, analyse, repair and check partitions mysql&gt; ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1); mysql&gt; ALTER TABLE t1 CHECK PARTITION (p0); mysql&gt; ALTER TABLE t1 ANALYZE PARTITION (p0); mysql&gt; ALTER TABLE t1 REPAIR PARTITION (p0);
<h2>&nbsp;</h2>
<h2>Restrictions and Limitations on Partitioning:</h2>
<ol>
<li>User-defined partitioning and the MERGE storage engine are not compatible. Tables using the MERGE storage engine cannot be partitioned.</li>
<li>FEDERATED storage engine.&nbsp; Partitioning of FEDERATED tables is not supported.</li>
<li>CSV storage engine.&nbsp; Partitioned tables using the CSV storage engine are not supported.</li>
<li>Prior to MySQL 5.1.6, tables using the BLACKHOLE storage engine also could not be partitioned.</li>
<li>Partitioning by KEY is the only type of partitioning supported for the NDBCLUSTER storage engine.</li>
</ol>

MySQL Partitioning Tutorial

Partitioning

mysql-db-partition

INFORMATION_SCHEMA provides access to database metadata. Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog. INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them. Example of a statement that retrieves information from INFORMATION_SCHEMA: mysql&gt; SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'db5' ORDER BY table_name DESC; &nbsp; The statement requests a list of all the tables in database db5, in reverse alphabetic order, showing just three pieces of information: the name of the table, its type, and its storage engine.
<h2>&nbsp;</h2>
<h2>SCHEMATA Table:</h2>
This table provides information about databases.
<h2>&nbsp;</h2>
<h2>TABLES Table:</h2>
This table provides information about tables in databases.
<h2>&nbsp;</h2>
<h2>STATISTICS Table:</h2>
This table provides information about table indexes.
<h2>&nbsp;</h2>
<h2>USER_PRIVILEGES Table:</h2>
This table provides information about global privileges. This information comes from the mysql.user grant table.
<h2>&nbsp;</h2>
<h2>SCHEMA_PRIVILEGES Table:</h2>
This table provides information about schema (database) privileges. This information comes from the mysql.db grant table.
<h2>&nbsp;</h2>
<h2>TABLE_PRIVILEGES Table:</h2>
This table provides information about table privileges. This information comes from the mysql.tables_priv grant table.
<h2>&nbsp;</h2>
<h2>COLUMN_PRIVILEGES Table:</h2>
This table provides information about column privileges. This information comes from the mysql.columns_priv grant table.
<h2>&nbsp;</h2>
<h2>TABLE_CONSTRAINTS Table:</h2>
This table describes which tables have constraints.
<h2>&nbsp;</h2>
<h2>REFERENTIAL_CONSTRAINTS Table:</h2>
This table provides information about foreign keys. This table was added in MySQL 5.1.10.
<h2>&nbsp;</h2>
<h2>ROUTINES Table:</h2>
This table provides information about stored routines (both procedures and functions). his information comes from the mysql.proc table.
<h2>&nbsp;</h2>
<h2>VIEWS Table:</h2>
This table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.
<h2>&nbsp;</h2>
<h2>TRIGGERS Table:</h2>
This table provides information about triggers. You must have the SUPER privilege to access this table.
<h2>&nbsp;</h2>
<h2>PROFILING Table:</h2>
This table provides statement profiling information. Its contents correspond to the information produced by the SHOW PROFILES and SHOW PROFILE statements.&nbsp; The table is empty unless the profiling session variable is set to 1.
<h2>&nbsp;</h2>
<h2>ENGINES Table:</h2>
This table provides information about storage engines.
<h2>&nbsp;</h2>
<h2>PARTITIONS Table:</h2>
This table provides information about table partitions.
<h2>&nbsp;</h2>
<h2>GLOBAL_STATUS and SESSION_STATUS Tables:</h2>
This tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL STATUS and SHOW SESSION STATUS statements. This tables were added in MySQL 5.1.12.
<h2>&nbsp;</h2>
<h2>GLOBAL_VARIABLES and SESSION_VARIABLES Tables:</h2>
This tables provide information about server status variables. Their contents correspond to the information produced by the SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES statements. This tables were added in MySQL 5.1.12.
<h2>&nbsp;</h2>
<h2>PROCESSLIST Table:</h2>
The PROCESSLIST table provides information about which threads are running.
<h2>&nbsp;</h2>
<h2>EVENTS Table:</h2>
The EVENTS table provides information about scheduled events. MySQL Profiler: The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command: mysql&gt; set profiling=1; Two things happen once you issue this command. First, any query you issue from this point on will be traced by the server with various performance diagnostics being created and attached to each distinct query. Second, a memory table named profiling is created in the INFORMATION_SCHEMA database for your particular session (not viewable by any other MySQL session) that stores all the SQL diagnostic results. This table remains persistent until you disconnect from MySQL at which point it is destroyed. Now, simply execute a SQL query: mysql&gt; SELECT COUNT(*) FROM t1 WHERE broker_id = 2; +----------+ | count(*) | +----------+ |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp; | +----------+ &nbsp; Once the query completes, you can issue the following command to view the SQL profiles that have currently been stored for you: mysql&gt; show profiles; +----------+-----------------+-----------------------------------------------------------------------+ | Query_ID | Duration&nbsp;&nbsp; | Query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | +----------+-----------------+------------------------------------------------------------------------+ |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 | 0.00007300 | set profiling=1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | 0.00044700 | SELECT COUNT(*) FROM t1 WHERE broker_id = 2&nbsp;&nbsp; | +----------+-----------------+------------------------------------------------------------------------+ &nbsp; You get a quick summary of all your captured SQL plus the total duration that the query took to complete. To get the same diagnostic info, you can also query the memory table that holds your statistical information: mysql&gt; SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1; +-------------------+ | sum(duration) | +-------------------+ |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.000447&nbsp;&nbsp; | +-------------------+ &nbsp; You can view more detailed diagnostic info about one or more queries that you've profiled. The most basic command is one that lists the steps a profiled query went through to satisfy your SQL request, along with each step's time: mysql&gt; show profile for query 1; +------------------------+----------------+ | Status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Duration&nbsp;&nbsp;&nbsp;&nbsp; | +------------------------+----------------+ | (initialization)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00006300 | | Opening tables&nbsp;&nbsp;&nbsp;&nbsp; | 0.00001400 | | System lock&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00000600 | | Table lock&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00001000 | | init&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00002200 | | optimizing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00001100 | | statistics&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00009300 | | preparing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00001700 | | executing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00000700 | | Sending data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00016800 | | end&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00000700 | | query end&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00000500 | | freeing items&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00001200 | | closing tables&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0.00000800 | | logging slow query | 0.00000400 | +-------------------------+-----------------+
<h2>&nbsp;</h2>
<h2>Event Scheduler:</h2>
The MySQL Event Scheduler manages the scheduling and execution of events: Tasks that run according to schedule. Event support was added in MySQL 5.1.6. MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a &ldquo;cron job&rdquo;) or the Windows Task Scheduler. MySQL Events have the following major features and properties:
<ul>
<li>In MySQL 5.1.12 and later, an event is uniquely identified by its name and the schema to which it is assigned.</li>
<li>An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in a BEGIN ... END block if desired</li>
<li>An event's timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval.</li>
</ul>
<h3>Event Scheduler Configuration:</h3>
Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the PROCESS privilege in the output of SHOW PROCESSLIST. The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. Beginning with MySQL 5.1.12, it has one of these 3 values, which affect event scheduling as described here:
<ul>
<li>OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.</li>
<li>ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events. When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process.</li>
<li>DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.</li>
</ul>
It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime. mysql &gt; SET GLOBAL event_scheduler = ON; To disable the event scheduler, use one of the following two methods:
<ul>
<li>As a command-line option when starting the server:</li>
</ul>
--event-scheduler=DISABLED
<ul>
<li>In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server</li>
</ul>
event_scheduler=DISABLED &nbsp; Note: You can issue event-manipulation statements when event_scheduler is set to DISABLED. No warnings or errors are generated in such cases (provided that the statements are themselves valid). However, scheduled events cannot execute until this variable is set to ON (or 1). Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied. Beginning with MySQL 5.1.17, starting the MySQL server with the --skip-grant-tables option causes event_scheduler to be set to DISABLED, overriding any other value set either on the command line or in the my.cnf or my.ini file (Bug#26807). MySQL 5.1.6 and later provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to obtain information about scheduled events which have been defined on the server. Creating Events: CREATE EVENT &lt;event_name&gt; ON SCHEDULE [AT] &lt;schedule&gt; [EVERY] &lt;interval&gt; DO &lt;event_body&gt;; &nbsp; Example: CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1; &nbsp; The previous statement creates an event named myevent. This event executes once&mdash;one hour following its creation&mdash;by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1. The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:
<ul>
<li>AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value.</li>
<li>To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means &ldquo;every six weeks&rdquo;.</li>
</ul>
CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM site_activity.sessions; &nbsp; More complex compound statements, such as those used in stored routines, are possible in an event. delimiter | &nbsp; CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; &nbsp; SET v = 0; &nbsp; WHILE v &lt; 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END | &nbsp; delimiter ; &nbsp; There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event: CREATE EVENT e_call_myproc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO CALL myproc(5, 27); &nbsp; Alter Event: Examples: ALTER EVENT myevent ON SCHEDULE EVERY 12 HOUR; &nbsp; ALTER TABLE myevent AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO TRUNCATE TABLE myschema.mytable; &nbsp; To disable myevent, use this ALTER EVENT statement: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALTER EVENT myevent DISABLE; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALTER EVENT myevent RENAME TO yourevent; &nbsp; You can also move an event to a different database using ALTER EVENT ... RENAME TO ... and db_name.event_name notation, as shown here: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
<h3>Drop Event:</h3>
DROP EVENT [IF EXISTS] event_name; &nbsp; Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In Houston</a>

MySQL Information_Schema

INFORMATION_SCHEMA Tables

mysql-information_schema-tables

MySQL uses several strategies that cache information in memory buffers to increase performance. Depending on your database architecture, you balance the size and layout of these areas, to provide the most performance benefit without wasting memory or exceeding available memory. &nbsp;
17.1&nbsp; The MyISAM Key Cache To minimize disk I/O, the MyISAM storage engine exploits a strategy that is used by many database management systems. It employs a cache mechanism to keep the most frequently accessed table blocks in memory: - For index blocks, a special structure called the key cache (or key buffer) is maintained. The structure contains a number of block buffers where the most-used index blocks are placed. - For data blocks, MySQL uses no special cache. Instead it relies on the native operating system file system cache. - Multiple sessions can access the cache concurrently. - You can set up multiple key caches and assign table indexes to specific caches. To control the size of the key cache, use the key_buffer_size system variable. If this variable is set equal to zero, no key cache is used. The key cache also is not used if the key_buffer_size value is too small to allocate the minimal number of block buffers (8). When the key cache is not operational, index files are accessed using only the native file system buffering provided by the operating system. (In other words, table index blocks are accessed using the same strategy as that employed for table data blocks.) When data from any table index block must be accessed, the server first checks whether it is available in some block buffer of the key cache. If it is, the server accesses data in the key cache rather than on disk. Usually the server follows an LRU (Least Recently Used) strategy: When choosing a block for replacement, it selects the least recently used index block. To make this choice easier, the key cache module maintains all used blocks in a special list (LRU chain) ordered by time of use. When a block is accessed, it is the most recently used and is placed at the end of the list. When blocks need to be replaced, blocks at the beginning of the list are the least recently used and become the first candidates for eviction. &nbsp;
Shared Key Cache Access: Threads can access key cache buffers simultaneously, subject to the following conditions: - A buffer that is not being updated can be accessed by multiple sessions. - A buffer that is being updated causes sessions that need to use it to wait until the update is complete. - Multiple sessions can initiate requests that result in cache block replacements, as long as they do not interfere with each other (that is, as long as they need different index blocks, and thus cause different cache blocks to be replaced). Shared access to the key cache enables the server to improve throughput significantly &nbsp;
Multiple Key Caches: Shared access to the key cache improves performance but does not eliminate contention among sessions entirely. They still compete for control structures that manage access to the key cache buffers. To reduce key cache access contention further, MySQL also provides multiple key caches. This feature enables you to assign different table indexes to different key caches. The separate key cache can be created by setting its size with a SET GLOBAL parameter setting statement or by using server startup options. For example: mysql&gt; SET GLOBAL keycache1.key_buffer_size=128*1024; The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named keycache1: mysql&gt; CACHE INDEX t1, t2, t3 IN keycache1; The key cache referred to in a CACHE INDEX statement can be created by setting its size with a SET GLOBAL parameter setting statement or by using server startup options. For example: mysql&gt; SET GLOBAL keycache1.key_buffer_size=128*1024; For a busy server, you can use a strategy that involves three key caches: - A &ldquo;hot&rdquo; key cache that takes up 20% of the space allocated for all key caches. Use this for tables that are heavily used for searches but that are not updated. - A &ldquo;cold&rdquo; key cache that takes up 20% of the space allocated for all key caches. Use this cache for medium-sized, intensively modified tables, such as temporary tables. - A &ldquo;warm&rdquo; key cache that takes up 60% of the key cache space. Employ this as the default key cache, to be used by default for all other tables. &nbsp; 17.2&nbsp; The InnoDB Buffer Pool InnoDB maintains a buffer pool for caching data and indexes in memory. InnoDB manages the pool as a list, using a least recently used (LRU) algorithm incorporating a midpoint insertion strategy. When room is needed to add a new block to the pool, InnoDB evicts the least recently used block and adds the new block to the middle of the list. The midpoint insertion strategy in effect causes the list to be treated as two sublists:
<ul>
<li>At the head, a sublist of &ldquo;new&rdquo; (or &ldquo;young&rdquo;) blocks that have been recently used.</li>
<li>At the tail, a sublist of &ldquo;old&rdquo; blocks that are less recently used.</li>
</ul>
As a result of the algorithm, the new sublist contains blocks that are heavily used by queries. The old sublist contains less-used blocks, and candidates for eviction are taken from this sublist. &nbsp;
innodb_buffer_pool_size Specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables. &nbsp; innodb_old_blocks_pct Specifies the approximate percentage of the buffer pool that InnoDB uses for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). &nbsp; innodb_old_blocks_time Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist. innodb_old_blocks_pct and innodb_old_blocks_time are available as of MySQL 5.1.41, but only for InnoDB Plugin, not the built-in version of InnoDB. &nbsp; Locking Issues MySQL manages contention for table contents using locking:
<ul>
<li>Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely by the server and involves no other programs.</li>
<li>External locking occurs when the server and other programs lock MyISAM table files to coordinate among themselves which program can access the tables at which time.</li>
</ul>
Check Out Our Related Courses
<a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a>
<a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In Dallas</a>

Buffering and Caching in MySQL

Buffering and Caching

mysql-buffering-caching

MySQL manages contention for table contents using locking:
<ul>
<li>Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely by the server and involves no other programs.</li>
<li>External locking occurs when the server and other programs lock MyISAM table files to coordinate among themselves which program can access the tables at which time.</li>
</ul>
&nbsp; 18.1. Internal Locking Methods: This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs. External locking occurs when the server and other programs lock MyISAM table files to coordinate among themselves which program can access the tables at which time. MySQL uses row-level locking for InnoDB tables, and table-level locking for MyISAM, MEMORY, and MERGE tables. Which lock type works better for your application depends on the application and its workload, especially whether the data is modified frequently and how many concurrent sessions need to read or write the same tables. Different parts of an application may require different lock types. &nbsp; Considerations for Table Locking: Table locking in MySQL is deadlock-free for storage engines that use table-level locking. Deadlock avoidance is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. &nbsp; MySQL grants table write locks as follows:
<ol>
<li>If there are no locks on the table, put a write lock on it.</li>
<li>Otherwise, put the lock request in the write lock queue.</li>
</ol>
&nbsp; MySQL grants table read locks as follows:
<ol>
<li>If there are no write locks on the table, put a read lock on it.</li>
<li>Otherwise, put the lock request in the read lock queue.</li>
</ol>
&nbsp; Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not &ldquo;starved&rdquo; even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates. You can analyze the table lock contention on your system by checking the Table_locks_immediate and Table_locks_waited status variables, which indicate the number of times that requests for table locks could be granted immediately and the number that had to wait, respectively. The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. This behavior is altered by the concurrent_insert system variable. InnoDB uses row locks. Deadlocks are possible for InnoDB because it automatically acquires locks during the processing of SQL statements, not at the start of the transaction. &nbsp; Considerations for Row Locking: &nbsp; Advantages of row-level locking:
<ol>
<li>Fewer lock conflicts when different sessions access different rows.</li>
<li>Possible to lock a single row for a long time.</li>
</ol>
&nbsp; Disadvantages of row-level locking:
<ol>
<li>Requires more memory than table-level locks.</li>
<li>Slower than table-level locks when used on a large part of the table because you must acquire many more locks.</li>
<li>Slower if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently.</li>
</ol>
&nbsp; Choosing the Type of Locking: Generally, table locks are superior to row-level locks in the following cases:
<ol>
<li>Most statements for the table are reads.</li>
<li>Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:</li>
</ol>
&nbsp; mysql&gt; UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; mysql&gt; DELETE FROM tbl_name WHERE unique_key_col=key_value; &nbsp;
<ol start="3">
<li>SELECT combined with concurrent INSERT statements, and very few UPDATE or DELETE statements</li>
</ol>
Many scans or GROUP BY operations on the entire table without any writers. &nbsp; 18.2&nbsp; Table Locking Issues: To achieve a very high lock speed, MySQL uses table locking for all storage engines except InnoDB and NDBCLUSTER. For InnoDB tables, MySQL uses table locking only if you explicitly lock the table with LOCK TABLES. For this storage engine, avoid using LOCK TABLES at all, because InnoDB uses automatic row-level locking to ensure transaction isolation. For large tables, table locking is often better than row locking, but there are some disadvantages:
<ul>
<li>Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access. During the update, all other sessions that want to access this particular table must wait until the update is done.</li>
<li>A session issues a SELECT that takes a long time to run. Another session then issues an UPDATE on the same table. This session waits until the SELECT is finished.</li>
</ul>
The following items describe some ways to avoid or reduce contention caused by table locking:
<ul>
<li>Try to get the SELECT statements to run faster so that they lock tables for a shorter time.</li>
<li>To give a specific INSERT, UPDATE, or DELETE statement lower priority, use the LOW_PRIORITY</li>
<li>To give a specific SELECT statement higher priority, use the HIGH_PRIORITY</li>
<li>Start mysqld with --low-priority-updates. For storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE), this gives all statements that update (modify) a table lower priority than SELECT</li>
<li>Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter.</li>
</ul>
&nbsp; 18.3&nbsp; Concurrent Inserts: The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table. If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements. The results of a concurrent INSERT may not be visible immediately. The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1 and concurrent inserts are handled as just described. If concurrent_insert is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are permitted even for tables that have deleted rows. &nbsp; 18.4&nbsp; External Locking: External locking is used in situations where a single process such as the MySQL server cannot be assumed to be the only process that requires access to tables. Here are some examples:
<ul>
<li>If you run multiple servers that use the same database directory (not recommended), each server must have external locking enabled.</li>
<li>If you use myisamchk to perform table maintenance operations on MyISAM tables, you must either ensure that the server is not running, or that the server has external locking enabled so that it locks table files as necessary to coordinate with myisamchk for access to the tables. The same is true for use of myisampack to pack MyISAM If the server is run with external locking enabled, you can use myisamchk at any time for read operations such a checking tables. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.</li>
</ul>
With external locking in effect, each process that requires access to a table acquires a file system lock for the table files before proceeding to access the table. If all necessary locks cannot be acquired, the process is blocked from accessing the table until the locks can be obtained. External locking affects server performance because the server must sometimes wait for other processes before it can access tables. For mysqld, external locking is controlled by the value of the skip_external_locking system variable. When this variable is enabled, external locking is disabled, and vice versa. Use of external locking can be controlled at server startup by using the --external-locking or --skip-external-locking option. Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In Chicago</a>

Locking Issues in MySQL

Locking Issues

mysql-locking-issues

High Availability And Scalability

High Availability and Scalability

high-availability-scalability-mysql

As a general rule, to upgrade from one release series to another, you should go to the next series rather than skipping a series. To upgrade from a release series previous to MySQL 5.0, upgrade to each successive release series in turn until you have reached MySQL 5.0, and then proceed with the upgrade to MySQL 5.1. For example, if you currently are running MySQL 4.1 and wish to upgrade to a newer series, upgrade to MySQL 5.0 first before upgrading to 5.1, and so forth. If you perform a binary upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.1. This occurs due to an incompatible change in the MyISAM table index format in MySQL 5.0. Upgrade from MySQL 4.1 to 5.0 and repair all MyISAM tables. Then upgrade from MySQL 5.0 to 5.1 and check and repair your tables. To upgrade from MySQL 5.0 to 5.1, use the items in the following checklist as a guide:
<ul>
<li>Before any upgrade, back up your databases, including the mysql database that contains the grant tables.</li>
<li>Read all the notes of upgrading document. These notes enable you to identify upgrade issues that apply to your current MySQL installation. Some incompatibilities discussed in that section require your attention before upgrading. Others should be dealt with after upgrading.</li>
<li>Read change history as well, which provides information about features that are new in MySQL 5.1 or differ from those found in MySQL 5.0.</li>
<li>After upgrading to a new version of MySQL, run mysql_upgrade. This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities.</li>
<li>If you upgrade an installation originally produced by installing multiple RPM packages, it is best to upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.</li>
<li>If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name. The same is true if the new version of MySQL implements a built-in function with the same name as an existing stored function.</li>
</ul>
If you are cautious about using new versions, you can always rename your old mysqld before installing a newer one. For example, if you are using MySQL 5.0.13 and want to upgrade to 5.1.10, rename your current server from mysqld to mysqld-5.0.13. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld. If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation. If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a &ldquo;dummy&rdquo; database instance for assessing what conversions might be needed and the work involved to perform them. Make a copy of your MySQL instance that contains a full copy of the mysql database, plus all other databases without data. Run your upgrade procedure on this dummy instance to see what actions might be needed so that you can better evaluate the work involved when performing actual data conversion on your original database instance. It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as PHP mysql extensions and the Python MySQLdb module. &nbsp; Known Issues:
<ol>
<li>mysql_upgrade attempts to to upgrade tables that are incompatible with the current version of MySQL. (It invokes mysqlcheck to check tables and, if necessary, repair them.) However this can fail for storage engines that do not support REPAIR TABLE, such as InnoDB, and leave tables in a nonupgradable state. To work around this problem, use ALTER TABLE tbl_name ENGINE=InnoDB to perform a &ldquo;null&rdquo; alter operation that rebuilds the table.</li>
<li>After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables:</li>
</ol>
Before MySQL 5.1.42, accessing those tables will cause the server to crash, even if you have run &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mysql_upgrade or CHECK TABLE ... FOR UPGRADE. As of MySQL 5.1.42, the server will not open 5.0 ARCHIVE tables at all. &nbsp; In either case, the solution is to use mysqldump to dump all 5.0 ARCHIVE tables before &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; upgrading, and reload them into MySQL 5.1 after upgrading.
<ol>
<li>Dumps performed by using mysqldump to generate a dump file before the upgrade and reloading the file after upgrading are subject to the following problem:</li>
</ol>
Before MySQL 5.0.40, mysqldump displays SPATIAL index definitions using prefix lengths for the indexed columns. These prefix lengths are accepted in MySQL 5.0, but not as of MySQL 5.1. If you use mysqldump from versions of MySQL older than 5.0.40, any table containing SPATIAL indexes will cause an error when the dump file is reloaded into MySQL 5.1 or higher. &nbsp; For example, a table definition might look like this when dumped in MySQL 5.0: CREATE TABLE `t` ( `g` geometry NOT NULL, SPATIAL KEY `g` (`g`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 &nbsp; The SPATIAL index definition will not be accepted in MySQL 5.1. To work around this, edit the dump file to remove the prefix: &nbsp; CREATE TABLE `t` ( `g` geometry NOT NULL, SPATIAL KEY `g` (`g`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 &nbsp; RPM&rsquo;s Upgrade Process
<ol>
<li>Download th RPM packages.</li>
<li>Take complete backup of all the databases including mysql database. This helps you in priventing loss of data in case if anything goes wrong.</li>
<li>Enter into the newly downloaded MySQL directory and use below command to upgrade the MySQL RPM packages.</li>
</ol>
&nbsp; [shell]# rpm -Uvh file.rpm
<ol>
<li>And after upgrading to a new version of MySQL you must run mysql_upgrade program. This program checks all the tables and attempts to repair them if necessary. It also updates the grant tables so that you can take advantage of new privileges or capabilities that might have been added. .</li>
</ol>
&nbsp; [shell]# mysql_upgrade
<ol>
<li>After upgrading the software its recommended to rebuild the tables for compatibility with the new version. But rebuild may not possible in OLTP databases. In this case at least we need to repair the table.</li>
</ol>
<ol>
<li>i) You can rebuild the tables using mysqldump utility.</li>
<li>ii) You can also use the below statement to rebuild the table with the same engine name.</li>
</ol>
mysql &gt; ALTER TABLE tablename ENIGNE = &lt;ENGINE TYPE&gt;; &nbsp; Binary Upgrade Process: However, always check the upgrade notes in the manual for incompatible changes, such as a binary incompatibility that might require some data to be dumped to text first before reloading. Before attempting any upgrade, please back up your databases, including the mysql database that contains the grant tables. To begin with, this process will be much easier and less confusing for the future, if your data directory is located outside of the MySQL installation directory. By default, MySQL uses /usr/local/mysql/data for the data directory. To simplify upgrading, change the data dir to out side of installation dir by adding the following to the [mysqld] section of your my.cnf file: &nbsp; [mysqld] datadir = /usr/local/mysql-data &nbsp; After changing this, shut down the server, move the existing data files to the new directory, and then start up mysqld again. Next, ensure that everything works with the data in the new data directory. Do all of this before attempting to upgrade. This way you are only changing one thing at a time, and thus should make this process easier. Now that the data directory is independent of the installation directory, you can easily switch between different versions of MySQL. And you can simply shut down the old server and start up the new one once it is installed. As for installing the new version, you just need to download the file you are installing, copy it to the directory of your choice, extract the files, and create a symbolic link. For example, if you were going to install this to /usr/local: &nbsp; # cd /usr/local # tar zvxf /tmp/mysql-VERSION.tar.gz # ln -s full-path-to-mysql-VERSION-OS mysql &nbsp; If a symbolic link already exists for your previous version of MySQL, then you can delete the existing symbolic link first and then re-create it, or you can simply use the &ndash;f option to perform this automatically with the ln command. # ln -sf full-path-to-mysql-VERSION-OS mysql This is when you should also change the ownership of program binaries to root and the group attribute to the mysql group. Assuming you are still in (/usr/local), run the following commands: cd mysql chown -R root&nbsp; . chgrp -R mysql . &nbsp; The chown command changes the owner attribute of the files to the root user. The chgrp changes the group attribute to the mysql group. Since this is for an upgrade and we have recommended you move your data directory, this assumes that you have already changed those files appropriately. Now you just need to create the new my.cnf file. Usually, it&rsquo;s sufficient just copy the one currently in use to the new installation. Be sure to have the new data directory specified in this my.cnf file. Now, shut down the current mysqld # mysqladmin -u root -p shutdown Then start mysqld in your normal fashion. Since you have used symbolic links, the normal way in which you invoke mysqld (i.e., mysqld_safe &amp;) will now use the new version of MySQL. To revert to the old version, follow the same steps, but point /usr/local/mysql to the old version instead of the new one. Note:&nbsp; After starting the MySQL server repair all the tables. &nbsp; Source Upgrade Process: Download the latest version of MySQL to upgrade. Extract the file and go to the specific directory and execute the below commands. # ./configure &ndash;prefix=/usr/local/mysql&nbsp;&nbsp; (should be the same location of the existing MySQL installation) # make # make install Check Out Our Related Courses <a href="https://tekslate.com/tutorials/sql-tutorials/">Sql Tutorials</a> <a href="https://tekslate.com/tutorials/oracle-pl-sql/">Oracle Pl SQL Tutorials</a> <a href="https://tekslate.com/interview-questions-on-sql-server/">Top 50 Sql Server Interview Questions</a> <a href="https://tekslate.com/sql-server-t-sql-training/">SQL Server T-SQL Training In San Jose</a> &nbsp;

Upgrading MYSQL

Upgrading MySQL

upgrading-mysql

<h2>SQL Injections Overview</h2>
If you take user input through a webpage and insert it into a SQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts &nbsp;and SQL statements in your server side scripts such as PERL Script. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the example below, the name is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars (Modify these rules as needed). if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) { $result = mysql_query("SELECT * FROM CUSTOMERS WHERE name=$matches[0]"); } else { echo "user name not accepted"; } To demonstrate the problem, consider this excerpt: // supposed input $name = "Qadir'; DELETE FROM CUSTOMERS;"; mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'"); The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster: the injected DELETE query removes all records from CUSTOMERS. Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails. However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform &nbsp;stacked queries, executing all of the queries provided in one string and creating a serious security problem.
<h3>Preventing SQL Injection</h3>
You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL. if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'"); The LIKE Quandary: To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let's you specify a character range to escape. $sub = addcslashes(mysql_real_escape_string("%str"), "%_"); // $sub == \%str\_ mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

SQL Injections Tutorial by SQL Experts

SQL Injections

sql-injections

<h2>SQL SubQueries Overview</h2>
A Subquery or Inner query or Nested query is a query within another SQL query, and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, &lt;, &gt;, &gt;=, &lt;=, IN, BETWEEN etc. There are a few rules that subqueries must follow:
<ul style="list-style-type: circle;">
<li>Subqueries must be enclosed within parentheses.</li>
<li>A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.</li>
<li>An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.</li>
<li>Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.</li>
<li>The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.</li>
<li>A subquery cannot be immediately enclosed in a set function.</li>
<li>The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.</li>
</ul>
<h3>Subqueries with the SELECT Statement</h3>
Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows: SELECT column_name [, column_name ] FROM&nbsp;&nbsp;&nbsp;&nbsp; table1 [, table2 ] WHERE&nbsp; column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) Example: Consider CUSTOMERS table is having following records: +----+----------+-----+-----------+----------+ | ID | NAME&nbsp;&nbsp;&nbsp;&nbsp; | AGE | ADDRESS&nbsp;&nbsp; | SALARY&nbsp;&nbsp; | +----+----------+-----+-----------+----------+ |&nbsp; 1 | Ramesh&nbsp;&nbsp; |&nbsp; 35 | Ahmedabad |&nbsp;&nbsp; 2000.00 | |&nbsp; 2 | Khilan&nbsp;&nbsp; |&nbsp; 25 | Delhi&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1500.00 | |&nbsp; 3 | kaushik&nbsp; |&nbsp; 23 | Kota&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2000.00 | |&nbsp; 4 | Chaitali |&nbsp; 25 | Mumbai&nbsp;&nbsp;&nbsp; |&nbsp; 6500.00 | |&nbsp; 5 | Hardik&nbsp;&nbsp; |&nbsp; 27 | Bhopal&nbsp;&nbsp;&nbsp; |&nbsp; 8500.00 | |&nbsp; 6 | Komal&nbsp;&nbsp;&nbsp; |&nbsp; 22 | MP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 4500.00 | |&nbsp; 7 | Muffy&nbsp;&nbsp;&nbsp; |&nbsp; 24 | Indore&nbsp;&nbsp;&nbsp; | 10000.00 | +----+----------+-----+-----------+----------+ Now let us check following sub-query with SELECT statement: SQL&gt; SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY &gt; 4500) ; This would produce following result: +----+----------+-----+---------+----------+ | ID | NAME&nbsp;&nbsp;&nbsp;&nbsp; | AGE | ADDRESS | SALARY&nbsp;&nbsp;&nbsp; | +----+----------+-----+---------+----------+ |&nbsp; 4 | Chaitali |&nbsp; 25 | Mumbai&nbsp; |&nbsp; 6500.00 | |&nbsp; 5 | Hardik&nbsp;&nbsp; |&nbsp; 27 | Bhopal&nbsp; |&nbsp; 8500.00 | |&nbsp; 7 | Muffy&nbsp;&nbsp;&nbsp; |&nbsp; 24 | Indore&nbsp; | 10000.00 | +----+----------+-----+---------+----------+
<h3>Subqueries with the INSERT Statement</h3>
Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions. The basic syntax is as follows: INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] Example: Consider &nbsp;a &nbsp;table &nbsp;CUSTOMERS_BKP &nbsp;with &nbsp;similar &nbsp;structure &nbsp;as &nbsp;CUSTOMERS &nbsp;table. &nbsp;Now &nbsp;to &nbsp;copy &nbsp;complete CUSTOMERS table into CUSTOMERS_BKP, following is the syntax: SQL&gt; INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS) ;
<h3>Subqueries with the UPDATE Statement</h3>
The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement. The basic syntax is as follows: UPDATE table &nbsp;SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [WHERE) ] Example: Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table. Following example updates SALARY by 0.25 times in CUSTOMERS table for all the customers &nbsp;whose AGE is greater than or equal to 27: SQL&gt; UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE &gt;= 27 ); This would impact two rows and finally CUSTOMERS table would have following records: +----+----------+-----+-----------+----------+ | ID | NAME&nbsp;&nbsp;&nbsp;&nbsp; | AGE | ADDRESS&nbsp;&nbsp; | SALARY&nbsp;&nbsp; | +----+----------+-----+-----------+----------+ |&nbsp; 1 | Ramesh&nbsp;&nbsp; |&nbsp; 35 | Ahmedabad |&nbsp;&nbsp;&nbsp; 125.00 | |&nbsp; 2 | Khilan&nbsp;&nbsp; |&nbsp; 25 | Delhi&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1500.00 | |&nbsp; 3 | kaushik&nbsp; |&nbsp; 23 | Kota&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2000.00 | |&nbsp; 4 | Chaitali |&nbsp; 25 | Mumbai&nbsp;&nbsp;&nbsp; |&nbsp; 6500.00 | |&nbsp; 5 | Hardik&nbsp;&nbsp; |&nbsp; 27 | Bhopal&nbsp;&nbsp;&nbsp; |&nbsp; 2125.00 | |&nbsp; 6 | Komal&nbsp;&nbsp;&nbsp; |&nbsp; 22 | MP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 4500.00 | |&nbsp; 7 | Muffy&nbsp;&nbsp;&nbsp; |&nbsp; 24 | Indore&nbsp;&nbsp;&nbsp; | 10000.00 | +----+----------+-----+-----------+----------+
<h3>Subqueries with the DELETE Statement</h3>
The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above. The basic syntax is as follows: DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] Example: Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table. Following example deletes records from CUSTOMERS table for all the customers whose AGE is greater than or equal to 27: SQL&gt; DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE &gt; 27 ); This would impact two rows and finally CUSTOMERS table would have following records: +----+----------+-----+---------+----------+ | ID | NAME&nbsp;&nbsp;&nbsp;&nbsp; | AGE | ADDRESS | SALARY&nbsp;&nbsp;&nbsp; | +----+----------+-----+---------+----------+ |&nbsp; 2 | Khilan&nbsp;&nbsp; |&nbsp; 25 | Delhi&nbsp;&nbsp; |&nbsp; 1500.00 | |&nbsp; 3 | kaushik&nbsp; |&nbsp; 23 | Kota&nbsp;&nbsp;&nbsp; |&nbsp; 2000.00 | |&nbsp; 4 | Chaitali |&nbsp; 25 | Mumbai&nbsp; |&nbsp; 6500.00 | |&nbsp; 6 | Komal&nbsp;&nbsp;&nbsp; |&nbsp; 22 | MP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 4500.00 | |&nbsp; 7 | Muffy&nbsp;&nbsp;&nbsp; |&nbsp; 24 | Indore&nbsp; | 10000.00 | +----+----------+-----+---------+----------+

SQL SubQueries by SQL Experts

SQL SubQueries

sql-subqueries

How to handle duplicates in SQL

SQL Handling Duplicates

sql-handling-duplicates

<h2>SQL</h2>
<ul>
<li>It is called as structured query language(or)SQL concept.</li>
<li>Using SQL concept, we can handle any database.</li>
<li>Using SQL concept, we can generate result in <a href="http://www.sas.com/en_us/home.html" target="_blank" rel="noopener">SAS environment</a> or outside of the environment.</li>
<li>SQL concept mainly running based on 4 concepts.</li>
<li>DDL (Data Definition Language)</li>
<li>DML(Data Manipulation Language)</li>
<li>DCL(Data Controlling Language)</li>
<li>Query language.</li>
</ul>
DDL: Using this concept, we can create a table with variables without observations(null data set)using procedure and data set block. DML: Using this concept, we can insert the data in existed table, update data values, delete the observation from existed table. DCL: We can control data process. Query Language: Using with query language, we can retrieve the data for reporting and storage.
<ul>
<li>To create a table use with SQL block , SQL block start with proc sql and ends with quit statement.</li>
</ul>
Create statement: It can be used to create the tables with variables and without observations. Insert statement: It can be used to insert the data in existed table. Insert statement is working based on value clause or set clause.
<ul>
<li>Value clause is working based on variable position.</li>
<li>Set clause is working based on variable name.</li>
</ul>
Select statement: It is a query statement, it can be used to retrieve the data for reporting and storage. /* create a table with SQL block*/ Ex: Proc sql; Create table demo(pid num, age num, gender char); Insert into demo values (100, 23,&rsquo;male&rsquo;) values (101, 34, &rsquo;female&rsquo;) values (102, 45, &rsquo;female&rsquo;) select * from demo; quit;
<pre style="text-align: center;">Learn the core features of SAS and become master with our expertise <a href="https://tekslate.com/base-sas-training/" target="_blank" rel="noopener">SAS Training</a>.</pre>
Ex: Proc sql; Create table medi(stno num, drug char, sdate num, stime num, edtime num); Insert into medi values(100,&rsquo;5mg&rsquo;,&rsquo;12jan2003&rsquo;d,&rsquo;12:23:23&rsquo;t,&rsquo;17dec2003:10:23:23&rsquo;dt); Select stno, drug, Sdate format = ddmmyy10., Stime format = timeampm12., Edtime format = date time18. From medi; Quit;
<ul>
<li>If we write format statement in query statements, these statements are temporary.</li>
<li>If we write format in create statement these statements are permanenet.</li>
</ul>
Ex: Proc sql; Create table medi(stno num, drug char, sdate num, stime num, edtime num); Insert into medi values(100,&rsquo;5mg&rsquo;,&rsquo;12jan2003&rsquo;d,&rsquo;12:23:23&rsquo;t,&rsquo;17dec2003:10:23:23&rsquo;dt); Select stno, drug, Sdate format = ddmmyy10., Stime format = timeampm12., Edtime format = date time18.); /* to insert the data with set clause */ Proc sql; Create table lab (pid num, test char, units num); Insert into lab Set pid =100,test =&rsquo;hr&rsquo;, units =78 Set pid =100,test =&rsquo;dbp&rsquo;, units =89 Set pid =100,test =&rsquo;sbp&rsquo;, units =145; Select * from lab; Quit; Order by clause: It can be used to report the data in ascending or descending order. Default descending order, order by clause can be written in select statement. Order by clause requires variables names or variable position. Ex: Proc sql; Select * from <a href="https://tekslate.com/tutorials/sas/" target="_blank" rel="noopener">sas</a>help. class Order by age; Quit; Ex: Proc sql; Select * from sashelp. class Order by age desc; Quit; /* To create a table using query statement */ Ex: Proc sql; Create table class as Select * from sashelp. class Order by age desc; Quit;
<ul>
<li>SQL block is more efficient for reporting comparative to data set block. Sql block is both reporting and storage. data set block is only for storage.</li>
</ul>
Where clause: It can be used to create a subset of data for reporting and storage. /* reporting */ Ex: Proc sql; Select * from sashelp. class Where age &gt;= 14; Quit; /* storage */ Ex: Proc sql; Create table class2 as Select * from sashelp. class Where age &gt;= 14; Quit; /* To report age&gt;=14 sub and to generate report in descending based on age variable*/ Ex: Proc sql; Select * from sashelp. class Where age &gt;= 14 Order by age desc; Quit; /* To manipulate data for reporting*/ Ex: Proc sql; Select eid, salary +1000 as salary, sale from emp; Quit; /* To create new variables for reporting */ Ex: Proc sql; Select * , salary +1000 as nsalary from emp; Quit; To do data manipulation based on condition: If we want to manipulate the data based pn condition, in this case we will use case when then else clause -&gt; closed with end statement. Ex: Proc sql; Select * , salary +case When sale ge 500 then 2000 Else 1000 end As nsalary from emp; Quit; When condition are more, then we will use when clause Ex: Proc sql; Select * , salary +case When sale ge 500 then 2000 When sale ge 400 and sale lt 500 then 1500 Else 1000 end As nsalary from emp; Quit; /* to create a new variable in existed data set*/ Update statement : It can be used to modify the data values in existed variables Syntax: Update &lt;table name&gt; set &lt;variable name&gt; = &lt;expressions&gt;; Alter statement: It can be used to modify the existed table. Modifications:
<ol>
<li>Adding new column</li>
<li>Adding or dropping required column for the existed table.</li>
<li>Assign the constraints</li>
<li>Delete the constraints</li>
</ol>
/* Add&nbsp; new column*/ Ex: Proc sql; Alter table emp Add nsalary num Format =comma 12.; Quit; /* Adding values in new column*/ Ex: Proc sql; Update emp set nsalary= salary+case When sale ge 500 then 2000 When sale ge 400 and sale lt 500 Then 1500 Else 1000 end; Quit; Note: Using alter statement, we can add multiple column (or) variables(or) drop multiple columns or variables at a time. /* drop column */ Ex: Proc sql; Alter table emp Drop salary; Quit; &nbsp; Alter table&lt;table name&gt; add &lt;variable name&gt;&lt;data type&gt;&lt;format&gt;; Syntax: Alter table &lt;table name&gt; drop&lt;variable name&gt;;
<table border="1" width="761" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>sbp</td>
<td>drug</td>
<td>Daily dose</td>
</tr>
<tr>
<td>&gt;= 1!=0</td>
<td>15mg</td>
<td>3</td>
</tr>
<tr>
<td>&gt;=150 and &lt;170</td>
<td>10mg</td>
<td>2</td>
</tr>
<tr>
<td>&lt;=150</td>
<td>5mg</td>
<td>3</td>
</tr>
</tbody>
</table>
Data medi; Input pid sbp; Cards; 100&nbsp; 156 101&nbsp; 176 102&nbsp; 140 103&nbsp; 180 104&nbsp; 145 105&nbsp; 167 ; Proc sql; Select * , case When sbp &gt;= 170 then &lsquo;15mg&rsquo; When sbp &gt;= 150and Sbp &lt;170 then &lsquo;10mg&rsquo; Else &lsquo;5mg&rsquo; end As drug , case Where sbp &gt;= 170 then 3 When sbp &gt;= 150 and Sbp &lt;170 then 2 Else&nbsp; 3 end as Daily dose From medi; Quit; /* new variable creation for storage */ Ex: Proc sql; Alter table medi Add drug char, dailydose num; Quit; /* Loading values in drug*/ Ex: Proc sql; Update medi set Drug =case When sbp &gt;= 170 then &lsquo;15mg&rsquo; When&nbsp; sbp &gt;=150 and Sbp&lt;170 then &lsquo;10mg&rsquo; Else &lsquo;5mg&rsquo; end; Quit; /* loading valies in daily dose*/ Ex; Proc sql; Update medi set Daily dose =case When sbp &gt;= 170 then 3 When&nbsp; sbp &gt;=150 and Sbp&lt;170 then 2 Else 3 end; Quit;
For indepth knowledge on SAS, click on below
<ul>
<li><a href="https://tekslate.com/normalization-sas/" target="_blank" rel="noopener">Normalization in SAS</a></li>
<li><a href="https://tekslate.com/sql-aggregations-datasetrow-colummn-operations-sas/" target="_blank" rel="noopener">SQL Aggregations &amp; DataSet(Row &amp; colummn operations) in SAS</a></li>
<li><a href="https://tekslate.com/working-macro-variable-sas/" target="_blank" rel="noopener">Working with Macro Variable in SAS</a></li>
<li><a href="https://tekslate.com/7917" target="_blank" rel="noopener">SAS Interact With Oracle</a></li>
<li><a href="https://tekslate.com/sas-interview-questions-answers" target="_blank" rel="noopener">SAS Interview Questions &amp; Answers</a></li>
</ul>

SQL Statements in SAS - Tekslate SQL Tutorials

SQL Statements in SAS

sql-statements-sas

&nbsp;
NoSQL Databases is a relatively new genre of databases. NoSQL does not imply noSQL at all. In fact, most NoSQL databases support an SQL-like query language. NoSQL is for Not Only SQL. NoSQL databases differ from relational databases (RDBMS) in that they are based on a flexible schema (or schema-free) data model. A relational database such as Oracle database or MySQL database has a fixed table structure with a fixed number of columns and pre-specified column types. Data added to an RDBMS table must conform to the table definition. In contrast, a NoSQL data store could store data of variable structure. One row or document could have a different data structure from another. Different NoSQL data stores support different data models. The main data models are Document store, Key-value store, and Wide column store. Document stores are document-oriented database systems, most of which are based on the JSON document model. Key-value stores are based on a data model in which data is stored as key/value pairs. Wide column stores are somewhat similar to the table structure of a relational database in that data is stored in rows and columns, but the columns (column names and column types) are not fixed. We shall compare three commonly used NoSQL databases: <a title="MongoDB" href="https://tekslate.com/mongodb-training/" target="_blank" rel="noopener">MongoDB</a>, Apache Cassandra, and Couchbase.
NoSQL provides the new data management technologies designed to meet the increasing volume, velocity, and variety of data. It can store and retrieve data that is modeled in means other than the tabular relations used in relational databases. NoSQL systems are also called &ldquo;Not Only SQL&rdquo; to emphasize that they may also support SQL-like query languages.
The Relational Databases have the following challenges:
<ul>
<li style="text-align: justify;">Not good for large volume (Petabytes) of data with a variety of data types (eg. images, videos, text)</li>
<li style="text-align: justify;">Cannot scale for large data volume</li>
<li style="text-align: justify;">Cannot scale-up, limited by memory and CPU capabilities</li>
<li style="text-align: justify;">Cannot scale-out, limited by cache dependent Read and Write operations</li>
<li style="text-align: justify;">Sharding (break database into pieces and store in different nodes) causes operational problems (e.g. managing a shared failure)</li>
<li style="text-align: justify;">Complex <a class="zem_slink" title="Relational database management system" href="http://en.wikipedia.org/wiki/Relational_database_management_system" target="_blank" rel="noopener wikipedia">RDBMS</a> model</li>
<li style="text-align: justify;">Consistency limits the scalability in RDBMS</li>
</ul>
<blockquote>
Inclined to build a profession as Cassandra Training? Then here is the blog post on, explore <a title="Cassandra Training" href="cassandra-training" target="_blank" rel="noopener">Cassandra Training</a>
</blockquote>
Compared to relational databases, NoSQL databases are more scalable and provide superior performance. NoSQL databases address the challenges that the relational model does not by providing the following solution:
<ul>
<li style="text-align: justify;">A scale-out, shared-nothing architecture, capable of running on a large number of nodes</li>
<li style="text-align: justify;">A non-locking concurrency control mechanism so that real-time reads will not conflict writes</li>
<li style="text-align: justify;">Scalable replication and distribution &ndash; thousands of machines with distributed data</li>
<li style="text-align: justify;">An architecture providing higher performance per node than RDBMS</li>
<li style="text-align: justify;">Schema-less data model</li>
</ul>
<h2 style="text-align: justify;">Data Collection</h2>
Just as a table is stored in a database in an RDBMS, NoSQL data stores provide a top-level namespace or container for storing data. In MongoDB, the equivalent of a relational database table is a "collection," which contains one or more documents. A MongoDB "database," which is the top-level container, consists of one or more collections. In Couchbase, the equivalent of an RDBMS database is called a "bucket." A bucket could have one or more documents. In Apache Cassandra, a "keyspace" defines a top-level namespace for tables.
<h2 style="text-align: justify;"><a class="zem_slink" style="color: #ba372a;" title="Data model" href="http://en.wikipedia.org/wiki/Data_model" target="_blank" rel="noopener wikipedia">Data Model</a></h2>
The equivalent of an RDBMS table is a MongoDB collection and the equivalent of an RDBMS table row is a MongoDB document. MongoDB is based on the document store data model in which a document is stored as BSON format. BSON format is binary JSON format. A MongoDB document consists of fields and values and each document could have different or the same fields as another. For example, the following could be a MongoDB document stored in a collection.
<pre>{
 journal: 'Oracle Magazine',
 publisher: 'Oracle Publishing',
 edition: 'January February 2010,
}</pre>
A different document in the same collection could have different fields. For example, the following could be another document stored in the same collection as the first.
<pre>{
 journal: 'Oracle Magazine',
 edition: 'January February 2010,
 section: 'Oracle JDeveloper',
 title: 'Installing JDeveloper'
}

</pre>
<ul>
<li style="text-align: justify;">Although a document in the same collection could have completely dissimilar fields, similar documents are usually grouped together. A MongoDB document field value could be any of the BSON data types such as Double, String, Object, Array, and Binary data.</li>
<li style="text-align: justify;">The Couchbase data model is based on the JSON document store. Couchbase data is stored as JSON documents in data buckets. As for MongoDB's BSON format, Couchbase does not have a fixed schema. One JSON document could have different fields from another. Couchbase document data types are the JSON data types such as strings, boolean, and arrays. What makes the JSON and BSON data models flexible is the nested hierarchical structures, including nested arrays and objects supported by JSON.</li>
<li style="text-align: justify;">Apache Cassandra's data model is a wide column model in which&nbsp;columns are grouped into a column family. Cassandra is not totally schema-free in that metadata for columns in a column family could be pre-specified. Two types of column families are feasible: static column family and dynamic column family. In a static column family, the column metadata, column names, and types are specified when a column family is created. In a dynamic column family, the column metadata is not pre-specified and an application may define any columns. In a static or dynamic column family, each row could have different columns. The only schema requirement is that each row has a row key, which is the equivalent of a primary key in an RDBMS table, and its type. In fact, a Cassandra table could consist only of row keys and no columns in any row. An Apache Cassandra column family may also be called a table; both <tt>CREATE TABLE</tt> and <tt>CREATE COLUMN FAMILY</tt> commands are available. The following is a comparison of an RDBMS table and an Apache Cassandra table (or column family).</li>
</ul>
<img class=" wp-image-21627 aligncenter" style="display: block; margin-left: auto; margin-right: auto;" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1497.png" alt="Data Model" width="679" height="146" />
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" href="cassandra-interview-questions-answers" target="_blank" rel="noopener">Cassandra Interview Questions</a>
NoSQL platform includes:
Workload diversity &ndash; Big Data comes in all shapes, colors, and sizes. Rigid schemas have no place here; instead, you need a more flexible design. You want your technology to fit your data, not the other way around. And you want to be able to do more with all of that data &ndash; perform transactions in real-time, run analytics just as fast, and find anything you want in an instant from oceans of data, no matter what form that data may take.
Scalability &ndash; With big&nbsp;data you want to be able to scale very rapidly and elastically,&nbsp;whenever and wherever you want. This applies to all situations, whether scaling across multiple data centers and even to the cloud if needed.
Performance &ndash; As has already been discussed, in an online world where nanosecond delays can cost you sales, Big Data must move at extremely high velocities no matter how much you scale or what workloads your database must perform. The performance of your environment, namely your applications, should be high on the list of requirements for deploying a NoSQL platform.
Continuous Availability&nbsp;&ndash; Building off of the performance consideration, when you rely on big&nbsp;data to feed your essential, revenue-generating 24/7 business&nbsp;applications, even high availability is not high enough. Your data can&nbsp;never go down, therefore there should be no single point of failure in&nbsp;your NoSQL environment, thus ensuring applications are always available.
Manageability &ndash;&nbsp;Operational complexity of a NoSQL platform should be kept at a minimum.&nbsp;Make sure that the administration and development required to both&nbsp;maintain and maximize the benefits of moving to a NoSQL environment are&nbsp;achievable.
Cost &ndash; This is certainly&nbsp;a glaring reason for making the move to a NoSQL platform as meeting&nbsp;even one of the considerations presented here with relational database&nbsp;technology can cost become prohibitively expensive. Deploying NoSQL&nbsp;properly allows for all of the benefits above while also lowering&nbsp;operational costs.
Strong Community&nbsp;&ndash; This is perhaps one of the more important factors to keep in mind as&nbsp;you move to a NoSQL platform. Make sure there is a solid and capable&nbsp;community around the technology, as this will provide an invaluable&nbsp;resource for the individuals and teams that will be managing the&nbsp;environment. Involvement on the part of the vendor should not only&nbsp;include strong support and technical resource availability, but also&nbsp;consistent outreach to the user base. Good local user groups and&nbsp;meetups will provide many opportunities for communicating with other&nbsp;individuals and teams that will provide great insight into how to work&nbsp;best with the platform of choice.

NoSQL Cassandra | Tekslate

Cassandra NoSQL

cassandra-nosql

<h2>SQL Server Sample Resume</h2>
123 Main Street, Sydney, Australia. (126)582 369. adleyrock@gmail.com.
<h3>Professional Summary :</h3>
<ul>
<li>Having 4 years experience in SQL server data base developer and Buisness Intelligence Technologies.</li>
<li>Expertise in installing, upgrading and Configuring Microsoft SQL server.</li>
<li>Excellent in writting complex SQL queries and involving in tables like inner and outer joins.</li>
<li>Expertise in creating triggers, tables, stored procedures, user defined functions and T-SQL statements for various applications.</li>
<li>Having knowledge on software development life cycle, database design and data modelling concepts.</li>
<li>Ability to complete tasks in time as well as good in team work skills.</li>
<li>Involved in Data Extraction, Transforming and Loading using SSIS.</li>
</ul>
<h3>Technical Skills :</h3>
<ul>
<li>C ,C++, SQL server 2005, 2008, ASP.net, VB.net, Microsoft Excel, Crystal Reports, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS).</li>
</ul>
<h3>Academic Qualifications :</h3>
<ul>
<li>Bachelor of Engineering in Information Technology, Imperial College, London.</li>
</ul>
<h3>Professional Experience :</h3>
<ul>
<li>4 years Experience with ABB Group as Senior Data Base developer.</li>
</ul>
<h3>Key Areas :</h3>
<ul>
<li>Worked&nbsp; with DBA's, team leads and Technical managers to understand the requirements of the system.</li>
<li>Designed and Developed stored procedures and SQL SSIS packages to extract data from various data sources.</li>
<li>Developed SQL Server OLAP Services for building the data cubes.</li>
<li>Assigning functional specification document to the team.</li>
<li>Assist the project manager to develop both high-level and detailed application architecture user requests and business needs.</li>
<li>Involved in developing scripts to migrate data from multiple sources.</li>
<li>Creating various reports using MS Reporting services and loading data from various data sources using MS Data transformation Services.</li>
</ul>
<h3>Accomplishments :</h3>
<ul>
<li>Implemented third -party solutions.</li>
<li>Establish a team to support and maintain regional operations.</li>
</ul>
<h3>SQL Server Sample Resume Secrets You Need To Know :</h3>
<ul>
<li>Your Resume must showcase the developer competency.</li>
<li>Invest your time in highlighting the most relevant skills.</li>
<li>Bold the most recent jobs you have held.</li>
<li>Highlight your Roles&nbsp; and Responsibilities.</li>
<li>Showcase transparency in employment Gaps (if any).</li>
<li>Make it clear in the 'Objectives' that you are qualified for the type of jobs you are applying.</li>
</ul>
<h3 style="text-align: justify;">Download SQL Server Sample Resume (PDF) here :</h3>
SQL Server Sample Resume &nbsp;