In this post, we will discuss the minimum requirements to have no single point of failure for all services in a MySQL Cluster setup. To remove all single points of failure there are the following requirements to the Cluster configuration: There must be two copies of the data To allow API and data nodes to come online, there must be at least two management nodes There must be at least two API nodes If one of the data nodes goes offline, an arbitrator must be present Note: The above … [Read more...] about MySQL Cluster requirements to avoid Single Point of Failure
mysql
What is an Arbitrator in MySQL Cluster
If one or more nodes in a cluster fail, it is possible that not all cluster nodes will not be able to "see" one another. In fact, it is possible that two sets of nodes might become isolated from one another in a network partitioning, also known as a "split brain" scenario. This type of situation is undesirable because each set of nodes tries to behave as though it is the entire cluster. When cluster nodes go down, there are two possibilities. If more than 50% of the remaining nodes can … [Read more...] about What is an Arbitrator in MySQL Cluster
How To Install MySQL RPM packages in a different location to allow multiple versions (versions < 5.6.10)
Question: How to install the MySQL RPM packages in a different location to allow multiple versions and/or avoid compromising your existing installation? Note that it is generally not recommended to install the RPM package of MySQL in a different location. Install the RPM package(s) You can use the --relocate option for the rpm command to specify the location where you want to install MySQL. Important also, is the --noscripts option, which makes sure the post-installation scripts are not … [Read more...] about How To Install MySQL RPM packages in a different location to allow multiple versions (versions < 5.6.10)
What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table
While the column mention "tables", it does not literally mean table locks, but rather the number of tables in which the transaction holds one or more InnoDB row locks. From the MySQL Reference Manual: TRX_TABLES_LOCKED: Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) That it is not actually table locks … [Read more...] about What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table
What are the various types of locking used in the MySQL Server
MySQL uses locking at several levels of which table level locks and row level locks are the two most often encountered lock types. For each lock type, the lock held can in general be either a shared lock or an exclusive lock. Shared allows multiple processes to read the same data, but writes will block. An exclusive lock will block both reads and writes. If a query is waiting for a lock, it can in some cases be seen from the "State" column of the 'SHOW PROCESSLIST output. The following goes … [Read more...] about What are the various types of locking used in the MySQL Server
How MySQL Enterprise Backup (MEB) uses locking while making a backup
MySQL Enterprise Backup performs the backup in several steps: First the InnoDB data is copied. FLUSH TABLES WITH READ LOCK is executed. All other files are copied and other tasks requiring the lock are performed. See also below. The lock is released. If you only need to backup InnoDB tables, you can use one of the following options to skip steps 2. through 4. and thus also avoid the locking steps: --only-innodb - Back up only InnoDB data and log files. All .frm files and files … [Read more...] about How MySQL Enterprise Backup (MEB) uses locking while making a backup
MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
MySQL 8.0.11 supports lock instance for backup but MySQL Enterprise Backup (MEB) not support that still uses FTWRL (FLUSH TABLES WITH READ LOCK). Support for avoiding FLUSH TABLES WITH READ LOCK for InnoDB tables was added in MySQL Enterprise Backup 8.0.16. Before 8.0.16, MySQL Enterprise Backup performs the backup in several steps: First the InnoDB data is copied. FLUSH TABLES WITH READ LOCK is executed. All other files are copied and other tasks requiring the lock are performed. See … [Read more...] about MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
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
MySQL: how to figure out which session holds which table level or global read locks
Question: How to Retrieve Table Level or Global Read Lock Status from Running MySQL Server? Prior to MySQL 5.7, it is not possible to figure out table level or global read locks held by each sessions. As of MySQL 5.7, a new performance schema table, metadata_locks is added. This performance schema can achieve the goal. Please look at the example below. mysql> use performance_schema ... snip ... mysql> update performance_schema.setup_instruments set enabled='yes', timed='yes' where name = … [Read more...] about MySQL: how to figure out which session holds which table level or global read locks
Recommended Configuration of the MySQL Performance Schema
In this post, we will see how to configure the Performance Schema and avoid it becomes a performance bottleneck. The default settings are a good starting point. Those have been extensively tested to ensure that in the vast majority of cases, they will not cause a severe overhead. It can be worth considering to enable some additional consumers and instruments. While the below suggestions usually only causes a small or no overhead, it is worth testing whether the overhead of additional … [Read more...] about Recommended Configuration of the MySQL Performance Schema