Relational databases, such as MySQL, give you the ability to organize data into separate tables, but link the tables together to form relationships when necessary. MySQL joins give you the ability to link data together in a MySQL database. A join is a way to get columns from more than one table into a single set of results. This is usually much more efficient than trying to perform multiple queries and combining them later. This article looks at the different types of joins that can be performed in MySQL and goes over the different options you have to combine data from multiple tables: inner joins, left and right joins, and full outer joins. A base example of MySQL joins To further our understanding of joins, we’ll create a simple database of grocery items, each item having a category. Categories are stored in the categories table and items are stored in a separate items table.CREATE TABLE categories ( id int PRIMARY KEY AUTO_INCREMENT, name …
[Read more]Learn how and when to use inner joins outer joins left joins and right joins.
Learn how and when to use inner joins outer joins left joins and right joins.
Let’s continue our journey of deploying the MySQL Database System
on OCI with Terraform.
This time we will see how we can use a backup (see [1] and [2]) as a source (initial data) for a new
instance.
Within the oci_mysql_mysql_db_system it’s possible
to define a source detailing how to provision the initial
data of the db system.
Let’s deploy a new MySQL Database Instance using a manual backup we made earlier:
We can also find the backup’s ocid using the oci cli
command:
$ oci mysql backup list …Today I would share a nice book related to MySQL 8.0 for our Chinese users.
The original title of this book by Scott Yao, is MySQL 8.0 运维与优化
I had the chance to read parts of the book before it was published to have my comment on the back cover.
This book is very detailed with many examples on how to
administer a MySQL server.
It covers the new features of MySQL 8.0 and Scott illustrates
perfectly how to take advantage of them.
Reading the book, it is obvious that the author is an experienced MySQL DBA and is sharing his experience that many junior DBAs can certainly benefit from.
This is a very technical book, as I like, I really recommend it to all Chinese people and I hope to see it translated into English too.
And finally I would thanks Scott for having sent to me a hard signed copy of the …
[Read more]
Recently I wrote an article on how to define a backup policy for
MySQL Database Service in OCI using Terraform. We saw that it
was possible to define tags (defined_tags and
freeform_tags) in the backup_policy
section of a oci_mysql_mysql_db_system resource.
However, it seems that these tags are never used for manual or automated backups in the MySQL Database Service backup process. In the strategy implemented on OCI, the backups inherit the tags from the MySQL DB system itself.
This means that if you want to have some custom tags on your backups, you need to specify them in the MySQL Database resource like this (on line 12):
resource "oci_mysql_mysql_db_system" "MDSinstance" {
admin_password = var.admin_password
admin_username = …A few weeks back, we faced an issue in a replication environment for a Managed Services client:
LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”
After some initial investigation, we noticed that the user in the replica didn’t exist! Was MySQL going crazy? But then the customer mentioned they had the following error in the primary before being able to execute the query successfully:
root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY …[Read more]
In the previous blog, we have gone through the Basic operation of the Skeema tool. In this blog, we will see the production use case of the Skeema tool.
[Read more]If you watched Finding Poorly Designed Schemas and How to Fix Them you witnessed Marcos Albe use some very interesting queries. These queries let you find tables without primary keys, tables with non-integer primary keys, tables that do not use InnoDB, tables and indexes with the most latency, indexes that are 50% larger than the table, find duplicate indexes, and find unused indexes. As promised, they are below.
— Find tables without PK
SELECT t.table_schema,t.table_name,t.engine
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema=c.table_schema
AND t.table_name=c.table_name
WHERE t.table_schema NOT IN (‘mysql’, ‘information_schema’,
‘sys’, ‘performance_schema’)
AND t.table_type = ‘BASE TABLE’
GROUP BY t.table_schema,t.table_name, t.engine
HAVING …
MySQL gave us the JSON data type back in mid-2015 with the release of MySQL 5.7.8. Since then, it has been used as a way to escape rigid column definitions and store JSON documents of all shapes and sizes: audit logs, configuration settings, 3rd party payloads, user-defined fields, and more. Although MySQL gives us functions for reading and writing JSON data, you’ll quickly discover something that is conspicuously missing: the ability to directly index your JSON columns. In other databases, the best way to directly index a JSON column is usually through a type of index known as a Generalized Inverted Index, or GIN for short. Since MySQL doesn’t offer GIN indexes, we’re unable to directly index an entire stored JSON document. All is not lost though, because MySQL does give us a way to indirectly index parts of our stored JSON documents. Depending on the version of MySQL that you're using, you have two options for indexing JSON. In MySQL 5.7 you …
[Read more]