Inherent flavors of MySQL
Posted On January 13, 2016 by Sheetal Singh filed under
This article explores the inherent properties of MySQL database transactions. The MySQL database management system was developed by a consulting firm in Sweden called TcX. MySQL is based on another database management system called mSQL. It is fast, reliable, and extremely flexible. It is used in many with many applications throughout the world. Universities, Internet service providers and nonprofit organizations are the main users of MySQL, mainly because of its price (it is mostly free).The reason for the growth of MySQL's popularity is the advent of the Open Source Movement in the computer industry. The popularity of Linux, has given rise the use of open source products in the business world. Linux's popularity has given the new direction to MySQL .
The relational database model was developed by E.F. Codd back in the early 1970s. He proposed that a database should consist of data stored in columns and tables that could be related to each other. This kind of thinking was very different from the hierarchical file system that was used at the time. His thinking truly revolutionized the way databases are created and used. Most modern databases use a relational model to accomplish their tasks. MySQL is not different. It meets the relational model.
MySQL is a true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular and standardized database language in the world. MySQL is a client/server implementation that consists of a server daemon mysqld and many client programs and libraries.
MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server. For Unix and OS/2 platforms, MySQL is basically free; for Microsoft platforms you must get a MySQL license .
Strengths of MySQL
MySQL offers one of the most powerful transactional database engines on the market today. MySQL database dominates in:
- Reliability : Little or no intervention required for constant uptime to be achieved.
- Performance : High performance and fast response times for all database interactions.
- Ease-of-Use : Installation and configuration of MySQL take no more than 15 minutes (most times is much less). Very little management necessary for ongoing maintenance or traditional administration tasks.
- ACID Transactions : MySQL supports ACID (Atomic, Consistent, Isolated, Durable) transaction model that ensures the integrity and validity of every entered transaction. Full COMMIT and ROLLBACK, with redo logs and undo (rollback) segment areas to ensure data consistency during crash recovery operations.
- Server-Based Data Integrity : Invalid data (bad dates, invalid numbers, etc.) can be automatically rejected at the server, with column-level rules enforcement being possible. In addition, full foreign-key support is provided so that complete data referential integrity is guaranteed.
- Low Contention Issues : Unlimited row-level locking is provided within InnoDB as is automatic deadlock detection, custom isolation levels (READ COMMITTED, etc.), to virtually eliminate locking issues. In addition, InnoDB supports multi-version concurrency control so that those reading data see a snapshot of the requested information as it existed at the moment their query was issued. This ability ensures that readers don’t block writers and vice-versa, and leads to extremely high levels of data availability.
- Automatic Storage Management : MySQL’s no-hassle storage management system automatically expands database storage as needed to accommodate incoming data loads so out-of-space errors become a thing of the past. In addition, the InnoDB storage engine automatically compacts database objects that have experienced heavy amounts of data.
MySQL Database Tools
MySQL has many built-in tools to help transfer data from one database to another. MySQL is also very accepting. Even though it may not use certain features other databases have, such as MySQL will not generate an error if it comes across these while importing data. This is because MySQL developers wanted it to be extremely flexible when migrating. Because MySQL is from the realm of open source, many people have contributed programs or other useful tools to help work with MySQL. Some of these are GUI interfaces, import wizards, and API wrappers. These tools, along with the ones you can create, are valuable assets to any MySQL DBA.
Secure Distributed Transaction Processing
Using MySQL’s transactional storage engine – InnoDB – a DBA can now allow for distributed transactions that utilize a two-phase commit protocol to ensure transactional integrity. MySQL provides a number of new features that assist in securing and validating critical information stored in MySQL databases.
Views, as their name implies, provide a window into a single table, or a join of multiple tables. Views have been widely used by DBAs and developers alike for two main reasons:
- Security Administration : Views help protect sensitive data through the hiding of one or more sensitive columns in a table, through limiting the actual rows returned in a table or table join, or through decrypting data that has been encrypted on disk.
- Performance Tuning : Views can be used by DBAs to help the performance of a database by pre-defining a tuned join condition of multiple tables or through limiting the amount of data returned in large tables
MySQL provides support for views, with updates being possible through views in certain situations. An example of a view that decrypts sensitive data, such as a customer’s social security number, that has been encrypted to disk would be:
mysql> select * from customer;
| customer_id | customer_first_name | customer_last_name | customer_ssn |
| 1 | SK | Pandey | U¶¢ƒ??4? |
+ ------------+--------------------+------------------+ -------------+--------------------
mysql> create view v_customer as t_name,
-> select customer_id, customer_first_name, customer_lastome_last_name
-> aes_decrypt(customer_ssn,`password`) as customer_ssn
-> from customer;
mysql> select * from v_customer;
| customer_id | customer_first_name | customer_last_name | customer_ssn |
| 1 | SK | Pandey | 91234567 |
MySQL Instance Manager
Because MySQL has become so popular in nearly every area of data management, DBAs have to manage and control more MySQL databases than ever before. To more easily administer MySQL servers, latest version of MySQL introduces Instance Manager. Instance Manager provides DBAs with a powerful utility to control and view many MySQL servers from one location.
With Instance Manager, a DBA can:
- View the up/down status of all MySQL instances on a server.
- Remotely start/stop any MySQL instance on a Linux or Unix server.
- Remotely edit any my.cnf file on any Unix/Linux server. You can add, change, and remove any my.cnf file entry and have it saved on the remote server.
- Read remote log files (error, query, etc.) on any MySQL Server. You can choose to view an entire log, only the last 50 lines, or whatever is desired.
With its current capabilities, Instance Manager can greatly ease the management burden of DBAs that are charged with managing an ever growing database farm by minimizing the time and effort involved in handling many of the traditional DBA tasks and troubleshooting exercises.
MyODBC is a 32-bit ODBC driver for connecting a ODBC-aware application to MySQL. MyODBC works on Windows98, NT ,XP and on most Unix platforms. Normally you only need to install MyODBC on Windows machines. You only need MyODBC for Unix if you have a program like ColdFusion that is running on the Unix machine and uses ODBC to connect to the databases. MyODBC is in public domain and you can get the newest version at http://www.mysql.com/.
If you want to install MyODBC on a Unix box, you will also need an ODBC manager. MyODBC is known to work both with most of the Unix ODBC managers. The user and Windows machine must have the access privileges to the MySQL server
the Unix machine. This is set up with the GRANT command. mysqladmin processlist only shows the connection and INSERT DELAYED threads.
MySQL Cluster is a parallel main-memory DBMS. In MySQL Cluster fields on disk are introduced. This introduces a number of challenges on the recovery architecture. MySQL Cluster uses the normal MySQL Server technology paired with a new storage engine NDB Cluster. Data within MySQL Cluster is synchronously replicated among the data nodes in the cluster. MySQL Cluster uses the shared-nothing architecture, data nodes in the cluster handle their own storage and the only means of communication between the nodes is through messages. The main reason for choosing a shared-nothing architecture is to enable a very fast fail-over at node failures. It also doesn’t rely on an advanced storage system as most shared-disk systems do. This means that normal cluster hardware can be used also for building large database clusters. Internally there are a number of protocols designed, to be able to handle single failures most of these protocols have an extra protocol to handle failures of the master and to be able to handle multiple failures there is also an extra protocol to handle failure of the new master taking over for the old master. Applications can use the normal MySQL interface from PHP, Perl, Java, C++, C and so forth, the only difference is that the tables are created with ENGINE=NDBCLUSTER.In MySQL Cluster all data in MySQL Cluster resides in main memory distributed over the data nodes in the cluster.
The MySQL security system is very flexible. With it, you can give potential users various levels of access ranging from the ability to log in from a specific machine as a specific user to full administrator access from anywhere. It is up to you to decide how tight you want your security to be. MySQL holds all permissions and privileges in the mysql database. This database is one of two databases that are created automatically when you install MySQL. (The other one is the test database). The only people who should have access to this table are the database administrators. This database is just like any other MySQL database. The data files are stored in data a directory under the mysql parent directory, where all other data files are stored. The tables in this database are
You can run queries on these tables, just as you can on any other table. These tables are collectively referred to as the Grant tables. Each column in these Grant tables reflect what permissions a person has by either a Y (meaning they can perform the operation) or an N (meaning they cannot). For instance, a person who has DELETE privileges in the user table would have a Y in the Delete_priv column.
The user Table
The user table contains the permission data for all the users that have access to MySQL. You can set all the permissions for users here. This table consists of the following columns:
- Host This is the name of the user’s computer. With MySQL, you can limit a person's access based on the location from which he or she is connecting.
- User This is the user’s name that he or she will use to access MySQL.
- password The user’s password.
- Select_priv Grants the user the ability to perform SQL SELECT queries.
- Insert_priv Allows the user to add data to databases by using the SQL INSERT statement.
- Update_priv Gives the user the ability to edit table data by using the UPDATE statement.
- Delete_priv Grants the user the ability to remove data from the database by using the DELETE statement.
- Create_priv Grants the user the ability to add tables and databases to the MySQL server.
- Drop_priv Gives the user the ability to delete tables and databases from the MySQL server. This ability can be very dangerous in the hands of a user, so take care when granting this privilege.
- Reload_priv Allows the user to refresh the Grant tables by using the FLUSH statement.
- Shutdown_priv Allows the user to shut down the server.
- Process_priv Grants the user the ability to look at the MySQL processes by using the mysqladmin processlist command or with the SHOW PROCESSLIST statement. Also gives the user the ability to kill these processes.
- File_priv Allows the user to read and write files that reside on the MySQL server. You should take special care when granting this permission. If it is not used properly, a malicious person could overwrite system files on the server machine. This is one of the reasons why MySQL should never run under the system’s root user.
- Grant_priv Allows the user to grant privileges to other users. This privilege should be restricted to database administrators for obvious reasons.
- References_priv This is not used for anything right now.
- Index_priv Grants the user the ability to create indexes on tables. It also allows the user to drop indexes. Create_priv and Drop_priv do not affect this privilege in any way. If a user has DROP and CREATE privileges already, he or she must have the Index_priv to create and drop indexes.
- Alter_priv Grants the user the ability to change a table's structure. Granting this privilege does not allow the user to add indexes to tables. The user must have those permissions as well to change the table.By giving a user permissions at this level, you are giving him or her global access to the database. This means that a user who has DELETE privileges granted in the user table can delete records in any database that is in the MySQL server. There are times when you may not want to do this. For example, suppose that you are the administrator for a MySQL server that has two databases: one for Accounting and one for Human Resources. The Accounting database contains all the tables and data that are tracked by the business, such as AR (accounts receivable), AP (accounts payable), and Payroll. The Human Resources database contains all employee information. In a situation like this, you would want to give the users in Accounting the ability to delete their own records, but you wouldn’t want them to have the ability to delete records from the Human Resources database. However, you would want the users in Accounting to have the ability to view records from the HR database. If you were to give the Accounting users the DELETE privilege in the user table, they would have the ability to delete records from the HR database. So how do you prevent this? Read on.
The db Table
The db table contains the permissions for all the databases that are contained in your MySQL server. Permissions granted here are given only for the named database. So, in the previous example, you could give DELETE permissions to the users at the database level instead of the user level. The db table has most of the same columns as the user table with a few exceptions. Because this table governs permissions at the database level, there are no administrator-level privileges, such as Reload_priv, Shutdown_priv, Process_priv, and File_priv. These permissions do not relate to databases operations that can be performed on databases, so they are only found in the user table. The only new column in the db table is Db. This is the database for which to apply these privileges.
The host Table
The host table, along with the db table, controls access by limiting the hosts that can connect to the database. This table has the same columns as the db table.
The columns_priv and tables_priv Tables
The columns_priv and tables_priv tables govern the permissions for a database’s tables and columns. With MySQL, we can limit what a user can do down to the column in a table.
These tables share the following columns:
- Host The host from which the user is connecting.
- Db The database that contains the tables to which you’re applying privileges.
- User The username of the person to whom you are granting permissions.
- Table_name The table name of the database on which you’re setting permissions.This column is case sensitive.
- Column_priv This column of either table controls the access a user has. It can contain the following values: SELECT, INSERT, UPDATE, and REFERENCES. If more than one privilege is granted, the fields must be separated by a comma.
- Timestamp This column contains the timestamp indicating when changes were made.
The Grantor and Table_priv columns are the only ones in the tables_priv table that do not appear in the columns_priv table. The Grantor column holds the name of the person granting the permissions. The Table_priv column maintains the permissions for the given table. The values it can contain are: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, and ALTER. The columns_priv table has only one column that does not appear in both tables; It is the Column_name column. This column contains the name of the column that is affected by the permissions granted in the Column_priv column. You may be wondering how this works—it works very similarly to the user and db tables. If you want to grant a user SELECT privileges for all the columns in a table, you can grant those privileges in the tables_priv table. However, if do not want a user to have certain rights, you have to limit his or her privileges in the column level.
Stages of Control
There is a hierarchy of security in the MySQL database system. When a user connects to the MySQL database, MySQL first looks in the user table to see if it can find a match for the hostname, username, and password. If it can, the user is given access to the system. When he or she issues a query against the database, MySQL first looks at the user table to see what privileges the user has. If the user has no privileges in that table, it looks to the db table. Again, it searches the table for a match with the hostname, username, and database. If it finds a match, it will look at the privileges the person has. If that person doesn’t have the privileges needed to issue the query, MySQL will then search the tables_priv table and then the columns_priv table for the permissions necessary to execute the query. If it cannot find any permissions, an error will be generated. This all happens every time a query is performed in MySQL As you can see, there are two control points. One point is the connection verification, and the second control point is the request verification. These control points offer a more secure environment for your database. A person who can connect to the database may not be able to do anything once he or she is in the database. This provides a very secure wall against would-be crackers disrupting your business and protects the database from users who may do harm unintentionally.
Managing a Database with mysqladmin
The mysqladmin utility is used to perform a wide range of administrative operations on a MySQL database. It is usually run from a command line prompt.When invoked, you should pass mysqladmin a number of options and commands that tell it how to runand what to do.
The following is the basic syntax for mysqladmin:
mysqladmin [options] command1 [cmd1_opt] command2 [cmd2_opt]
Remember that you may need to prefix mysqladmin with the path to the mysql/bin directory where it resides. Also, after you assign usernames and privileges to a database, you will need to use the -p and/or –u options when invoking mysqladmin if your MySQL username is different from your UNIX username. This tells MySQL that you are offering to provide it a username and password; if you do not and MySQL requires them, you will receive an error message.
The following commands can be used with mysqladmin:
- create databasename Creates a new database with the specified name
- drop databasename Drops (deletes) the database with the specified name
- status Gives a brief status message from the server
- version Displays server version information
- extended-status Creates an extended status message from the server
- variables Displays available variables
- processlist Displays a list of active threads in the server, useful to see how busy the server is
- flush-hosts Flushes all cached hosts
- flush-logs Flushes all logs
- flush-tables Flushes all tables
- flush-privileges Reloads the grant tables
- kill id1,id2 Kills mysql threads
- password new_password Changes the old password to a new password
- ping Sends a "ping" signal to mysqld to check that it is functioning
- reload Makes mysql reload its grant tables
- refresh Flushes all grant tables and closes and opens logfiles
- shutdown Shuts down the MySQL server
Repairing Damaged Tables with isamchk
The general syntax for using isamchk is as follows:
isamchk [options] table_name
While isamchk works on .ISM/.ISD table types, myisamchk does the same thing on the newer MyISAM table types with .MYI and .MYD extensions, respectively.
This article covers few of the features of MySQL .For more details you can refer the following references or e-mail at : firstname.lastname@example.org
- “Transaction Processing: Concepts and Techniques” By Gray, A. Reuter., Morgan Kaufmann Pub.
- “Mysql “By Michael Kofler, Apress Pub.
- “Mysql: Administrator's Guide” By MySQL AB, MySQL AB., Paul DuBois, MySQL Press.
- “Learning MySQL” By Seyed M. M. Tahaghoghi, Hugh E. Williams
- “MySQL: The Definitive Guide to Using, Programming, and Administering MySQL “ By Paul DuBois, Sams Pub.
- “MySQL “By Larry Edward Ullman, Peachpit Pub.
- MySQL: Essential Skills By Michael Grey, John W. Horn,McGraw Hill Pub.
- “MySQL Clustering “By Alex Davies, Harrison Fisk, Sams Pub.
- “MySQL: Building User Interfaces” By Matthew Stucky, Sams Pub.
- “Mysql Phrasebook: Essential Code and Commands “By Zak Greant, Chris Newman, Sams Pub.
- “Expert Mysql “ By Charles A. Bell , Apress Pub.
About the Author