Question: How to use mysqlsh to execute addInstance in silent mode (from a script) and avoid answering such a question? cl.addInstance(‘[USER]@[HOST]:[PORT]’) Please pick an option out of [C]lone/[I]ncremental recover/[A]bort (default Clone): Adding an instance to InnoDB Cluster requires the specification of what kind of recovery is desired. This can be of two types (incremental or […]
mysql
How to take Logical Backups on a MySQL Replication Slave using shell script
The sample script in this post uses the bash shell. It is also assumed that MySQL has been installed and the slave has been initialized. For MySQL 5.7 and later, the script only works for single-source replication. The server to be backed up should be configured as a slave. If you have one or more […]
MySQL Cluster requirements to avoid Single Point of Failure
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]