UPDATE: Almost solved! See at the end.
A clean installation of a database server is one where everything
goes according to the expectations. It used to be easy: you only
had to do what the manual says, and, presto! you would see your
database server installed and ready to use. If something went
wrong, you got one or more error messages that informed you of
what needs to be fixed.
Sometimes, rarely, it happened that you got also a warning
message, telling you that while the installation was successful,
you could improve it by fine tuning this and that. No big
deal.
Gone are those times. A clean installation nowadays is a much
harder exercise, if not impossible. Let’s give it a try using
MySQL 5.7.7.
Attempt #1 using mysql_install_db
The first error you could do when using a new version of MySQL is
assuming that basic operations are …
Here’s a way to detect the sql query causing a lock or a session to fail, and also to identify the o.s.pid if need be (btw, no rocket science). “a” way.. I’m sure there are many others, so feel free to suggest, please.
So, we’re using MCM, and have created a MySQL Cluster like mentioned in the cluster intro session (in Spanish I’m afraid), using 7.4.6, which comes with 5.6.24.
With the env up and running, set up a schema, some data and run a few queries:
mysql> create database world; mysql> use world; Database changed mysql> source world_ndb.sql
(world_ndb.sql, as you might guess, is the world_innodb tables script, with a little adjustment as to which storage engine to be used.)
Once created, let’s lock things up in Cluster:
mysql -uroot -h127.0.0.1 -P3306 mysql> use test; …[Read more]
When a new version of MySQL appears, the first source of information for the brave experimenter is a page in the manual named What is new in MySQL X.X, also known as MySQL in a nutshell. For MySQL 5.7, the in-a-nutshell page lists quite a lot of changes. In that page, the list of removed features is enough to send a chill down the spine of most any DBA. Some of the items in the deprecation section are removals in disguise, as they require immediate, rather than delayed, action to use the new version with existing application (SET …
[Read more]
I’ve had a look at a preview release of MySQL 5.7.8, some time
before it became available to the general public (perks and
duties of an Oracle ACE) and I found a few interesting
things among the release notes and the tarball itself:
- There is a new tool named mysqlpump, which is intended as a replacement for mysqldump, with parallel processing, compression, progress watch, the long awaited ability of excluding databases or tables, and more.
- The json functionality has been fished out from the labs and added to the main distribution.
Replication is the process that transfers data from an active
master to a slave server, which reproduces the data stream to
achieve, as best as possible, a faithful copy of the data in the
master.
To check replication health, you may start with
sampling the service, i.e. committing some
Sentinel value in the master and retrieving it
from the slave.
Sentinel data: Tap tap… Is this thing on?
If you want to make sure that replication is working, the easiest
test is using replication itself to see if data is being copied
across from the master to the slaves. The method is easy:
- Make sure that the data you want to see is NOT in the master or in the slave. If you skip this step, you may think that replication is working, while in fact it may not.
- Either create a table …
Maintaining a project like MySQL::Sandbox is sometimes tiring, but it has its
advantages. One of them is that everything related to the server
setup comes to my attention rather earlier than if I were an
average DBA or developer.
I try to keep MySQL Sandbox up to date with every release of
MySQL and (to a lesser extent) MariaDB [1].
For this reason, I am used to trying a new release with MySQL
Sandbox, and … seeing it fail.
Of the latest changes in MySQL, probably the most disruptive was
what happened in MySQL 5.7.6, where the mysql.user table
lost the password column.
Yep. No ‘password’ column anymore. And just to make the setup
procedure harder, the syntax of SET PASSWORD …
Both MySQL and MariaDB have been busy, each introducing new
features, sometimes creating the same feature, often with
different syntax.
This is sometimes good for users, who have a wide choice. And
sometimes it is bad, as once you are used to the deployment and
syntax of one flavor, it is hard to switch to a different one.
This problem is enhanced if you are dealing with an application,
MySQL
Sandbox, that needs to work well with all flavors.
The latest releases of MySQL Sandbox (3.0.51 to 3.0.55) have been necessary to solve minor and major troublesome points with MySQL 5.7.8 and MariaDB 10.1.
The current version (3.0.55) can install all the newest releases, including replication with MySQL 5.7.8 which suffers from a compatibility bug (better explored in a separate article). …
[Read more]One of the cool new features in 5.7 Release Candidate is Multi Source Replication, as I previously looked into in 5.7.5 DMR.
I’ve had more and more people like the idea of this, so here’s a quick set-up as to what’s needed and how it could work.
1. Prepare master environments.
2. Prepare 40 masters for replication.
3. Create slave.
4. Insert data into the Masters.
* I originally tried running 50 mysql’s but I just ran out of resources on my old pc, so it’s at 40.
* The real key behind this scenario is that each of the masters has a unique centre_code that is implicitly inserted via the app & users at that site. i.e. no other site will want to modify any data that was entered from that site, e.g. call centre reclaims dept for specific areas / regions, CNC …
[Read more]
Today Oracle released MySQL 5.7.6 milestone 16. With this, MySQL 5.7
has been in development for over 2 years.
Compared to MySQL 5.6, the changes are quite extensive. The main
effort of the team has been focused on speed, with performance
reportedly improved from 2 to 3 times compared to previous
releases.
A full list of what is new would take too much
space here, but I would like to mention some key points:
- Oracle has spent a considerable amount of energy in the improvement of MySQL security and safety. You will see many new features, but even more old features that were deprecated and more that were removed after deprecation in 5.6.
- The installation process has been changing in every …
default_tmp_storage_engine
variable was introduced
in 5.6.3, allowing the configuration of the default engine for
temporary tables. This seems to be in the direction, as I
commented before, of making MyISAM an optional engine. In 5.7,
a separate tablespace is being created to hold
those tables in order to reduce its performance penalty
(those tables do not need to be redone if the server crashes, so
extra writes are avoided).
However, I have seen many people assuming that because
default_tmp_storage_engine
has the value “InnoDB”,
all temporary tables are created in InnoDB format in
5.6. This is not true: first, because implicit
temporary tables are still being created in memory using …