I have a personal story that goes very well with this tweet:
A blog post over at Percona discusses better replication for MySQL and compares Galera and MySQL Group Replication.
Galera builds their own initial state transfer mechanism and their own transaction distribution mechanism, independently of MySQL replication (write set replication wsrep). wsrep is synchronous – on commit, the write set is shipped, applied and acknowledged (or not).
MySQL Group Replication strives to achive the same thing, but uses their own, “MySQL native” set of technologies to do this.
This is a replay of a much older blog post, which was available in German language in the old blog. It’s from 2012, and neither GTID nor Galera cluster or Group Replication existed back then.
Wonka> The http://www.toppoint.de probably will never have meaningful load, but I would like to know how one would make this highly available. Some kind of Redundant Array of Inexpensive Databases.
Lalufu> MySQL with replication? Or DRBD?
Isotopp> With DRBD. Not with replication.
Since 5.0, MySQL does allow natively encrypted connections to the database, and supposedly also does support client certs for user authentication. Supposedly, because I never tried.
MySQL as a database performs well with transient connections as they are prevalent in two-tier deployments (mod_php, mod_perl, mod_python to database), in which a database connection is made upon web request, and the connection is torn down at the end of the request. This model does not scale so well with encryption in the mix, as on connection a full TLS/SSL exchange must be made.
In this article, JF Gagne explains what happens when you ignore or silence warnings in MySQL instead of dealing with the root cause properly, and is having fun with INSERT IGNORE and other things.
Simon Mudd writes about Setting up MySQL Orchestrator in a production environment at Booking.com. He covers basics, failover, HA and Monitoring.
Following a great idea from their friends at GitLab, Soup.io loses all postings since 2015 because of malfunctioning backups. They write:
We had a big database crash, and the backups we had were corrupted.
The only working backup was from 2015.
Improving Recovery Procedures
9. Automated testing of recovering PostgreSQL database backups (#1102)
Does your database backup successfully restore? Are you sure? Are you testing this?
Remember these words of wisdom:
Nobody wants backup.
Everybody wants restore.
— Martin Seeger
The Percona Blog has an Overview Of Different MySQL Replication Solutions. On top of the regular traditional Async Replication shown above, they also cover SemiSync, Group Replication, and Galera Cluster. Statement and Row Based Replication are contrasted.
Finally, “some misconceptions about replication” are being addressed, with “Replication is not a cluster”, “Replication is a HA solution”, “Replication replaces backups” and “Replication replaces Load Balancing”.
In mysql() nach PHP 7 retten, Charly Kühnast explains how you can get the deprecated and disabled mysql extension back in PHP 7. You shouldn’t.
There are many reasons for this. One of them being that none of the newer features in MySQL can be used with the old mysql extensions. There is an overview in the PHP documentation that explains exactly what you are missing.
One of the things that you are missing is support for prepared statements. Prepared statements are a mechanism in which you write SQL statements with placeholders for variables, and then later bind values to the placeholders using a “bind” call or as part of the “execute” call which is actually running the statement. In any case, the variables are being escaped properly automatically, making SQL injection a lot harder.
This is not just a problem limited to PHP – a search for bind and execute other sources can be very instructive. For example, the sources of Opennebula or in older versions of Owncloud (up to and including version 7) are rich treasure troves of potential exploits.
So currently the situation is as follows:
There are three extensions at the PHP level, one of which is deprecated and disabled in PHP 7:
The wire protocol of all of these extensions is implemented by a C-level library, against which the extension can be linked. A manual page explains the choices.
What you should be using: These days, your code should not be using the mysql extension. So you will be using mysqli or PDO_mysql, depending on your needs, with the underlying implementation of the native driver doing the heavy lifting.
Do not attempt to port mysql-Extension based code to PHP 7 without refactoring it for prepared statements, please.