MySQL Cluster

In this post, we will see how to change the default character set from the default latin1_swedish_c to utf8_general_ci (or any other) and how to propagate charset changes in a master-master replication environment. 1. First determine the current default values, by issuing the MySQL command: mysql> show variables like '%character_set_%'; This will return something like this: +--------------------------+----------------------------+ | Variable_name | Value … [Read more...] about How to set the default character set in MySQL and how to propagate it in a master-master replication scenario

MyISAM Storage Engine MyISAM was the default MySQL storage engine prior to the MySQL server version 5.5.5. The current default is the InnoDB storage engine. The mysql database contains tables in the MyISAM format. Each MyISAM table is represented by three files: Formatfile: Stores the definition of the table structure (mytable.frm) Data file: Stores the contents of table rows (mytable.MYD) Index file: Stores any indexes on the table (mytable.MYI) The MyISAM storage engine … [Read more...] about Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE

MySQL database keeps the information about old versions of changed rows and supports transactional features such as concurrency and rollback. A rollback segment is an InnoDB storage area that contains the undo log. InnoDB can respond to queries for multiple versions of the same row when those queries are part of transactions that started at different times. It uses part of the undo log, the update undo buffer, to build earlier versions of database rows. Three hidden fields exist on every row … [Read more...] about Multi-Versioning in MySQL Database

Referential Integrity Referential integrity means that relationships between tables are consistent. MySQL enforces referential integrity by using foreign key constraints. When one table (the child table) has a foreign key to another table (the parent table), MySQL prevents you from adding a record to the child table if there is no corresponding record in the parent table. It also facilitates cascading updates and deletes to ensure that changes made to the child table are reflected in the parent … [Read more...] about How to use foreign keys to attain referential integrity in MySQL

Storage Engines and MySQL When you create a table, MySQL uses the InnoDB storage engine to create the storage for that table on the hard disk. You can choose an alternative storage engine to use for each table. Typically, you make this choice according to which storage engine offers features that best fit the needs of your application. Each storage engine has a particular set of operational characteristics. These characteristics include the types of locks that are used to manage query … [Read more...] about Beginners Guide to Storage Engines in MySQL

A database is a structured collection of data. Metadata is "data about data". Using the following methods, MySQL provides access to metadata for databases, tables, and other objects managed by the database server: 1. INFORMATION_SCHEMA: The MySQL server contains a data dictionary implemented as a database (schema) named INFORMATION_SCHEMAthat includes a number of objects that appear to be tables. 2. SHOW statements: Proprietary syntax to obtain data on server statistics, schemas, and … [Read more...] about How to obtain MySQL metadata (metadata access methods)

Question: How can we not allow user creation with a blank password in MySQL? Using a blank password for a user is always a no go. In order to restrict user creation with blank password follow the steps given below: Before MySQL 5.6 In case of MySQL version below 5.6, this can not be achieved completely. But there are settings that can be used to come closer to our objective. Set the below SQL mode on the MySQL instance: SET SQL_MODE = NO_AUTO_CREATE_USER Using NO_AUTO_CREATE_USER … [Read more...] about How to Restrict MySQL User Creation with Blank Password

Data Types: Overview In MySQL, the available data types can be grouped into four major categories: Numeric: Numeric values Binary: Binary data strings Character: Text strings Temporal: Time and date values Within each category, there are numerous specific data types that use varying amounts of memory and disk space, and thus have varying effects on performance. Choosing the best data type for the column has a rather small effect on performance in an individual record, but as the … [Read more...] about Beginners Guide to MySQL Data Types

Question: How to provide a password to mysql_config_editor without exposing it on the command line and without having to be present to type it? Use an expect script. For example: #!/usr/bin/env expect spawn mysql_config_editor set --skip-warn --login-path=client --user=root --password expect "Enter password: " send "$env(my_password)\r" expect eof The password can be stored in whatever location is considered secure enough for the operation that is accessible by the script. … [Read more...] about “expect” script to provide password to mysql_config_editor

What is MySQL Query Cache MySQL server features Query Cache Feature for a long time. When in use, the query cache stores the text of a SELECT query together with the corresponding result that is sent to a client. If another identical query is received, the server can then retrieve the results from the query cache rather than parsing and executing the same query again. It caches the full result set produced from a SELECT query: Queries executed must be identical. Cache is stored in … [Read more...] about Understanding MySQL Query Cache