Since April 19th, when MySQL 8.0 became Generally Available (GA), the MySQL community has been abuzz with excitement over all of the new features and improvements. Many of new features were improvements to performance or monitoring, while others were specifically related to replication. We reviewed Replication Performance Enhancements in MySQL 8 recently. Today’s blog will describe how to set up a basic master-slave configuration with MySQL, using two servers on a single machine.

Replication Defined

MySQL replication is a process in which data from one MySQL database server (the master) is copied automatically to one or more MySQL database servers (the slaves). In the case of multiple slaves, these are usually referred to as a slave cluster. Replication should not to be confused with backup operations. Whereas the aim of backups is to protect the data and/or data structure, the role of replication is typically to spread …

We build highly available and fault tolerant MySQL database infrastructure operations for some of the largest internet properties in this planet,  Our consulting team spend several hours daily researching on MySQL documentation and MySQL blogs to understand what are the best possible ways we can build optimal, scalable, highly available and reliable database infrastructure operations for planet-scale web properties. The most common approach towards building a fault-tolerant system is to make all the components in the ecosystem redundant, To make it even simple, component can be removed and system should continue to operate as expected.  MySQL replication is an proven method to build redundant database infrastructure operations, operationally these systems are highly complex, requiring maintenance and administration of several servers instead of just one, You need Sr. DBAs to manage such systems.

MySQL Group Replication can operate …

Sequences are used to requesting unique values on demand, The best use case of sequences is to have a unique ID. , that can be used across multiple tables. In some cases sequences are really helpful to have an identifier before an actual row is inserted. With the normal way of having an automatically incrementing identifier, the identifier value will only be available after insert of the row and the identifier will only be unique inside its own table. MariaDB Server 10.3 follows the standard and includes compatibility with the way Oracle does sequences introduced in Oracle Database Server on top of the standard.

Simple steps to create a sequence in MariaDB 10.3 onwards, a create statement is used:

MariaDB [MDB101]> CREATE SEQUENCE Seq1_100
    -> START WITH 100
    -> INCREMENT BY 1;
Query OK, 0 rows affected (0.015 sec)

This creates a sequence that starts at 100 and is incremented with 1 every time a …

ProxySQL 1.4.2 introduced native clustering, allowing several ProxySQL instances to communicate with and share configuration updates with each other. In this blog post, I’ll review this new feature and how we can start working with 3 nodes.

Before I continue, let’s review two common methods to installing ProxySQL.

ProxySQL as a centralized server

This is the most common installation, where ProxySQL is between application servers and the database. It is simple, but without any high availability. If ProxySQL goes down you lose all connectivity to the database.

ProxySQL on app instances

Another common setup is to install ProxySQL onto each application server. This is good because the loss of one ProxySQL/App server will not bring down the entire application.

For more information about the previous installation, please visit this link …

We are all familiar with “.frm” files since the earliest days of MySQL, The community has been continuously requesting for replacement of file-system based metadata for several good reasons, So with MySQL 8.0 “.frm” files are gone for ever, Going forward MySQL stores table metadata in the data dictionary tables which uses InnoDB storage engine. This blog is about MySQL 8.0 data dictionary and how it creates value for MySQL going forward:

How file based metadata management used to work in the past (before MySQL 8.0) ? 

• Every table in MySQL will have corresponding .frm file, This .frm file stores information like column names and data-types in the binary format, In addition to the .frm file, there are .trn, .trg and .par files to support triggers, trigger namespace and partitioning .

What are major bottlenecks faced due to the usage of file based metadata management …

The MySQL error log is an essential part of database server performance monitoring. Whenever something goes wrong or performance degrades, the Error Logs are usually the first place we look to start troubleshooting.

The MySQL Error Log is one of three related log types:

• The Error Log: It contains information about errors that occur while the server is running (as well as server start and stop events).
• The General Query Log: This is a general record of what mysqld is doing (connect, disconnect, queries).
• The Slow Query Log: It consists of “slow” SQL statements as defined in the long_query_time global variable.

You can enable error log monitoring to allow Monyog to keep an eye on your MySQL Error Log, and notify you when something goes awry. Moreover, Monyog combines the General Query, Slow Query and Error logs in a single view for both network and cloud servers. For example, in the …

Since summer 2017, Amazon RDS supports encryption at rest using AWS Key Management Service (KMS) for db.t2.small and db.t2.medium database instances, making the feature now available to virtually every instance class and type.

Unless you are running Previous Generation DB Instances or you can only afford to run a db.t2.micro, every other instance class now supports native encryption at rest using KMS. As for the Amazon documentation:

Encryption on smaller T2 database instances is useful for development and test use cases, where you want the environment to have identical security …

MySQL 8.0 General Availability was announced in April and it comes with a host of new features. The overview about the new features and improvements made in MySQL 8.0 can be found in the following blog.

The server can be upgraded by performing either an INPLACE upgrade or LOGICAL upgrade. …

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Some big news out from Microsoft about their acquisition of GitHub for $7.5 billion. GitHub hosts many projects, including from the MySQL ecosystem, but maybe more interesting is that their DBA team is awesome, give great talks, and are generally prolific writers. Some of the cool tools the MySQL world has gotten thanks to the excellent team include (but are not limited to): ccql, gh-ost for triggerless online schema migrations, and Orchestrator which is a GUI-based High Availability and …

We will show you how to list all databases in MySQL, i.e how to have a list of all MySQL ...

Read More

The post How To Show A List Of All DataBases In MySQL appeared first on RoseHosting.