MySQL 8.0 introduced a new feature that allows you to persist
configuration changes from inside MySQL. Previously you could
execute SET GLOBAL to change the configuration at
runtime, but you needed to update your MySQL configuration file
in order to persist the change. In MySQL 8.0 you can skip the
second step. This blog discuss how this works and how to backup
and restore the configuration.
MySQL Support Blogs
This is an aggregated feed of various blogs of Oracle support staff who work and support MySQL customers and users. There is an RSS feed to which you can subscribe.
When you write programs that uses a database backend, it is necessary to use a connector/API to submit the queries and retrieve the result. If you are writing Python programs that used MySQL, you can use MySQL Connector/Python – the connector developered by Oracle Corporation.
Now there is a new book dedicated to the usage of the connector: MySQL Connector/Python Revealed, which is published by Apress. It is available in a softcover edition as well as an eBook (PDF, ePub, Mobi).
MySQL has since version 5.7 had support for progress information
for some queries. As promised in my previous post, I will here discuss how you
can use that to get progress information for ALTER
TABLE on InnoDB tables.
Background and Setup
Progress information is implemented through the Performance
Schema using the stage events. In version 8.0.12 there are
currently seven stages that can provide this information
for ALTER TABLE statements on InnoDB tables.
In MySQL 8, it is easy to list the stages capable of reporting
progress information by using the …
MySQL NDB Cluster has great support for online (inplace) schema
changes, but it is still sometimes necessary to perform an
offline (copying) ALTER TABLE. These are relatively
expensive to make as the entire table is copied into a new table
which eventually replace the old table.
One example where a copying ALTER TABLE is required
is when upgrading from MySQL NDB Cluster 7.2 or earlier to MySQL
NDB Cluster 7.3 or later. The format used for temporal columns
changed between these version (corresponding to MySQL Server 5.5
to 5.6). In order to take advantage of the new temporal format, a
table rebuild is required.
…
The Performance Schema and sys schema are great for
investigating what is going on in MySQL including investigating
performance issues. In my work in MySQL Support, I have a several
times heard questions whether a peak in the InnoDB Data File
I/O – Latency graph in MySQL Enterprise Monitor (MEM) or
some values from the corresponding tables and view in the
Performance Schema and sys schema are cause for
concern. This blog will discuss what these observations means and
how to use them.
…
With the introduction of MySQL Shell 8.0, the second major
version of the new command-line tool for MySQL, a new and rich
featured prompt was introduced. Unlike the prompt of the
traditional mysql command-line client, it does not just say
mysql> by default. Instead it comes in a colour
coded spectacle.
The default prompt is great, but for one reason or another it may be that you want to change the prompt. Before getting to that, let’s take a look at the default prompt, so the starting point is clear.
The Default Prompt
An example of the default prompt can be seen in the screen shot below. As you can see, there are several parts to the prompt, each carrying its information.
…
In the first delivery of this series of posts,
delivered for whom who are interested to understand the basics of
MySQL Cluster "by examples", I wrote about installing MySQL
Cluster with a python utility called ndb_setup-py, which offers a nice web
graphical interface to define and start our brand new
cluster.
In this post I will share an example to do everything from
scratch and manually, instead. Doing things manually is always
the best recommendation to learn everything about processes life
cycle through their:
- Initialization
- Administration (start/stop/reconfigure)
- Monitoring (logs/counters/status)
- Troubleshooting
The resulting topology I'd like to setup is composed of 2 data nodes, 1 management node and …
There are many nice changes included in the MySQL 8.0.12 release that were published a couple of days ago. One of the most exciting is the ability to make instant schema changes to tables. This blog will look into why I think that that is a stand-out change.
I will not go into details with the implementation other than noting that the new data dictionary in MySQL 8.0 has allowed for a nice implementation of the patch that was contributed by the Tencent Games DBA Team. If you are interested in learning more about the implementation, I will recommend you to read the blog by Bin Su ( …
MySQL supports a long list of character sets, and one of the strong points are that you can use different character sets per column. This is very flexible, but which character set should you use?
If you do not want to read the whole blog the summary is: Use utf8mb4, particularly in MySQL 8.0 with one of the UCA 9.0.0 based collations.
Before I reach this conclusion, let’s start out looking at what a character set is.
What is a Character Set?
Characters are wonderful things that can take all kinds of shapes ranging from the familiar (for English speakers at least) Latin characters as those used to write this blog, over Arabic (مرحبا بالعالم) and Asian characters such as simplified Chinese …
Today I'll share a few slides I prepared last year for a
presentation delivered at Oracle Open World. This is a quick and
easy hands-on lab for fresh MySQL Server DBAs. I chose 5 among
the most relevant topics when tuning and scaling a MySQL Server
using InnoDB tables.
In particular, in this hands-on, I will talk about:
- Scaling connections
- The threads model
- InnoDB REDO log
- InnoDB Buffer Pool
- The Execution plan
MySQL Performance Tuning 101 from
Mirko Ortensi
Rate at which MySQL is delivering new features and improvements
is impressive, in fact MySQL Server 8 boosts performance in many
fields, especially regarding InnoDB REDO logging. Hence while the
rest of …
I have been playing with MySQL Cluster for some years now, and
today I'd like to start writing a bit about it, how to set it up,
configure, backup and also how to use it, as there's plenty of
ways to drive operations towards the Cluster for brutal
speed and concurrency. MySQL Cluster, the open source in
memory database from Oracle MySQL, is available for free from
MySQL Cluster download page (Community version has
GPL license).
But before starting with an overview of installation and setup,
if you're new to MySQL Cluster, I would strongly recommend to
have a look at this video.
What I find more interesting about MySQL Cluster, is that it is
possible to have a setup running on commodity hardware (the bare
laptop), as it can be configured to have a minimum footprint in
terms of memory and storage requirements. About high
availability, MySQL Cluster offers unique …
A common problem when you need to set up a new database instance is to import the initial data. This can particularly be an issue, if you are converting an existing database so there is a large amount of data to import. In MySQL NDB Cluster 7.6 which just was announced as general available (GA) this week, there is a new tool to help with such imports: ndb_import.
The ndb_import utility takes a CSV file and imports the data into a table. (While C stands for comma, the tool is not limited to comma-separated files, in fact tabs is the default …
Today – 31 March – is world backup day, so I thought I would write a little about backups in MySQL NDB Cluster.
Just because NDB Cluster offers built-in redundancy and high availability does not mean backups are not important. They are – as ever and as for everything in software. The redundancy does not protect against user errors (anyone ever executed DROP TABLE or DROP SCHEMA by accident?) neither does it protect against a natural disaster, fire, or another disaster hitting the data center. Similar with high availability.
In short, if the data is in any way remotely important for you, you ensure you have a backup. Furthermore, a backup is not worth any more than your ability to restore it. If a fire rages …
I have had the opportunity to write a blog for Apress with a brief introduction to MySQL NDB Cluster. The blog gives a brief overview of the history and why you should consider it. The architecture is described before some key characteristics are discussed.
If you are interested, the blog can be found at https://www.apress.com/us/blog/all-blog-posts/what-is-mysql-ndb-cluster/15454530.
Happy reading.
Sometimes it can be useful to take a step back and look at the world from a bit larger distance than usual. So in this blog, I will take a look at the high level architecture of MySQL Server.
Info …
It is with great pleasure, I can announce that a new book dedicated to MySQL NDB Cluster has just been released. The book Pro MySQL NDB Cluster is written by my colleague Mikiya Okuno and myself and is a nearly 700 pages deep dive into the world of MySQL NDB Cluster. The book is published by Apress.
Tip: There are several ways to cluster MySQL. This book is about the product MySQL Cluster (often called MySQL NDB Cluster to clarify which cluster it is). There is also MySQL InnoDB Cluster, clustering using replication, and …
There's a GTID_SUBTRACT function, and the manual shows how to write your own cheap GTID_UNION, but not a GTID_INTERSECT. Fortunately it's easy to write your own, as it's just subtracting twice.
CREATE FUNCTION GTID_INTERSECT(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(g1, GTID_SUBTRACT(g1, g2));
What use is it?
SET @slave_executed =
'33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681577,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1267098:1267100-1267416:1267418-1589733';
SET @master_executed =
'33738f8c-c1a5-11e7-8fc3-0a002700000f:1-730294,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';
SET @master_purged =
'33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681582,
421d139e-04b9-11e7-b702-0050569935dc:1-13077260,
…
I have shown how to build a basic system based on a Raspberry to capture a
picture through a PiCamera and share it on a web page. Now I'd
like to share a bit more of my experiments by describing an
end-to-end approach to detect movements in the monitored area
and alert of a possible intrusion. To the purpose, I'll share
what I did setup with basic pieces of code found browsing around
the internet. This minimal system is made of three simple
elements:
- …
After leaving my brand new Raspberry 3 on the shelf for months (I
admit being an impulsive buyer) I decided to give it some use by
setting up a surveillance system with it. Raspberry 3 has more
than enough power for easy image processing, and the camera
developed for its GPU has nice quality at a very accessible
price.
So, in order to start with this project, I got this PiCamera v2. I got that one with InfraRed (IR)
filter, as I don't plan to take nightly shots with added IR
lighting …
1. Watch the parentheses. It's:
ST_GeomFromText('POLYGON((outerRing), (innerRing),
(innerRing), ...)')
The inner rings are optional. If you have just the outer ring, then it's still:
ST_GeomFromText('POLYGON((outerRing))')
and not:
ST_GeomFromText('POLYGON(outerRing)')
2. Polygons have to start and end at the same point.
3. Watch the commas. Rings are comma-delimited sets of of whitespace-delimited coordinate pairs:
x1 y1, x2 y2, x3 y3, x4 y4
not:
x1 y1 x2 y2 x3 y3
x1, y1, x2, y2, x3, y3, x4, y4
(x1, y1), (x2, y2), (x3, y3)
or other variations on that theme.
Content reproduced on this page is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.