OurSQL Episode 200: Information Security and Privacy
For our 200th episode, we interview security expert Bruce Schneier. We talk about plenty of topics including airport security and the TSA, PRISM and the NSA, wholesale surveillance, surveillance backwards in time, finding people who have disposable cellphones, about searches and co-travelers, why Facebook does not offer the ability to pay for your account, a bit about Firefox and its propensity to act in the user's interest, and the future of our public information.
innodb_flush_log_at_timeout in 5.6
A setting that isn't used much but could be used more is innodb_flush_log_at_timeout, which controls how often InnoDB does an fsync from the OS buffer to disk. Here's how it interacts with innodb_flush_log_at_trx_commit.
Deciphering Galera Version Numbers
Mon, 2014-09-15 11:29guillaumelefrancIt's important to understand which Galera version you are running, for proper compatibility of MariaDB with the Galera replication library, and also to check which featureset you have access to. However, Galera version numbering can be difficult to decipher because of its complex scheme.
Version numbering in Galera consists of three different components:
Galera wsrep (write-set replication) Patch for MariaDB or MySQL
Galera Replication Shared Library (libgalera_smm.so)
Galera wsrep API
The patch component version number appears usually together with the API version number in the server version string of MariaDB. Below is a typical message displayed immediately after logging into MariaDB Galera Cluster with the mariadb client:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 80475
Server version: 5.5.38-MariaDB-wsrep-log MariaDB Server, wsrep_25.10.r3997
In the last line here, the last string (i.e., wsrep_25.10.r3997) shows the Galera version. The first number after wsrep is the Galera wsrep API version number, which is 25 here. In this situation, you will need to use the Galera shared library with the same API version.
The second number above is the patch release for the MySQL or MariaDB branch to which it relates. In this example, 10 corresponds to the tenth release of the wsrep patch for MariaDB 5.5.
The last numbering component above, r3997 refers to the last bazaar commit in the Galera wsrep mysql patch source tree. In this case, all of the fixes up to revision 3997 have been included. If you want, you can check the Launch Pad site for a complete list of revisions (http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/changes).
As mentioned before, the Galera replication shared library should be the same version as the Galera wsrep API version. If you're installing the library for the first time, check the package version number before installing it. However, if the API is already installed, you can check which version the server is already running by executing the following within the mysql or mariadb client:
SHOW GLOBAL STATUS LIKE 'wsrep_provider_version';
| Variable_name | Value |
| wsrep_provider_version | 25.3.5(rXXXX) |
The version numbering of a Galera replication library always follows the form of xx.y.z. The first number is the Galera wsrep API version. As mentioned earlier, you will need to use the Galera library and API version which has the same version as the MariaDB or MySQL patch release that the server is using. In this example, the API version is 25. This is the same as the version of Galera shown in the previous example. This is consistent.
The second number is the Galera major release version number. In this example, it's part of the 3.x branch of Galera replication library. We could also use the 2.x branch, which happens to be a stable version, as long as it also supports the API version 25. The version string would then be something like, 25.2.x.
Finally, the last number of the results above is the Galera minor release version number (i.e., 5). In this example, it's the fifth release in the 3.x branch.
So, the results of the SHOW statement above indicate that the server is using the Galera API version 25, major release 3, minor release 5. That's consistent with the MariaDB Galera server, which is using version 25.
Within parentheses in the results of the SHOW statement, there is one more value. This is the bazaar revision number. This isn't important to our purposes. In fact, it's now obscured because the developers at Codership has moved to github for version control of the Galera library (https://github.com/codership/galera). The git doesn't provide revision numbers; it's based on tagging instead.
Now that we've pulled apart and identified the components of the Galera version numbers, you can see that the numbering method is logical. You need only to ensure that the MariaDB Galera version installed on the server agrees with the version of the Galera API and library installed on the server. If they're not, you will probably need to use a different version of one or the other.
About the Author
Guillaume Lefranc is a Senior Consultant and Remote DBA Services Engineer, delivering performance tuning and high availability services worldwide.
Speaking about libAttachSQL at Percona Live London
As many of you know I'm actively developing libAttachSQL and am rapidly heading towards the first beta release. For those who don't, libAttachSQL is a lightweight C connector for MySQL servers with a non-blocking API. I am developing it as part of my day job for HP's Advanced Technology Group. It was in-part born out of my frustration when dealing with MySQL and eventlet in Python back when I was working on various Openstack projects. But there are many reasons why this is a good thing for C/C++ applications as well.What you may not know is I will be giving a talk about libAttachSQL, the technology behind it and the decisions we made to get here at Percona Live London. The event is on the 3rd and 4th of November at the Millennium Gloucester Conference Centre. I highly recommend attending if you wish to find out more about libAttachSQL or any of the new things going on in the MySQL world.As for the project itself, I'm currently working on the prepared statement code which I hope to have ready in the next few days. 0.4.0 will certainly be a big release in terms of changes. There has been feedback from some big companies which is awesome to hear and I have fixed a few problems they have found for 0.4.0. Hopefully you will be hearing more about that in the future.For anyone there I'll be in London from the 2nd to the 5th of November and am happy to meet with anyone and chat about the work we are doing.
MEB copies binary logs and relay logs to support PITR and cloning of master/slave
With MySQL Enterprise Backup(MEB) 3.9.0 we had introduced full instance backup feature for cloning the MySQL server. Now with MEB 3.11.0 we have enhanced the feature by copying all the master-slave setup files like MySQL server binary logs(will be referred as 'binlogs'), binary log index files, relay logs of slave, relay log index files, master info of slave, slave info files. As part of full instance backup, copying of binlog files is default behavior MEB-3.11.0 onwards. DBA should be aware of the fact that current full instance backup is bigger than the backups with old MEB's.As every event on MySQL production database goes as a entry to binlog files in particular format, binlog files could be huge. Backing of huge binlog and/or relaylog files should not impact the performance of MySQL server. Hence, all the binlog files, except the current binlog used by server, are copied just like the innodb .ibd files without locking tables. Binlog files currently being used by server and added after backup started, are copied during read the lock which is acquired by MEB for copying meta files and redo logs.DBA gets the following benefits:---------------------------------------------1) Direct cloning of master and slave possible from backupEarlier DBA had to copy binlog files manually in order to setup master/slave. Now, MEB 3.11 by default copies all the files including the global variables needed for setting up master-slave. Hence DBA can clone master or slave with the same state of backed-up server.Now, DBA need not to use --slave-info option to copy the binlog info for setting up the slave after restore. By copying master and slave info files, DBA can fetch the information of up to which master binlog position, slave SQL thread has executed and IO threads has read etc. With this information along with relay logs, binlogs, DBA can easily setup slave from backed-up slave content2) Backup of binary logs helps in Point In Time Recovery (PITR)
First let us understand what is PITR by above example. Consider DBA has taken full backup on Sunday(assume date as 14-09-2014), and incremental backups on Tuesday(date as 16-09-2014), Thursday(date as 18-09-2014). It means DBA can only restore database up to full backup or incremental backups in other words database can be restored either up to Sunday or up to Tuesday, Thursday, but not in between let say Monday or Wednesday. Because backup is just a snapshot of data when it was taken. Hence backup taken once can't be restored in between without change log. That's where binlog helps in restoring to a certain point of time, which is called Point-In-Time-Recovery(PITR). As binlogs captures all the events of a server with timestamps. Therefore to restore in between DBA need to have base data i.e. full backup and incremental binlogs.
Let's look at our example, below are the points to recover server to Wednesday 12 PM(assume date as 17-09-2014)a) Restore the backup up to latest backup before PITR time(Here, restore Tuesday's incremental)b) Get the SQL statements using below mysqlbinlog command up to PITR from the immediate next incremental binlogs(Here get SQL statements up to Wednesday from Thursday's incremental binlogs of binlog.000005, binlog.000006, binlog.000007)mysqlbinlog --start-datetime=<latest backup time before PITR time> \ --stop-datetime=<PITR point> \ <incremental binlogs from immediate next backup> > <SQL file>For our above example, the command ismysqlbinlog --start-datetime="2014-09-16 12:00:00" \ --stop-datetime="2014-09-17 12:00:00" \ binlog.000005 binlog.000006 binlog.000007 > mysql_PITR_restore.sqlRead Point-in-Time (Incremental) Recovery Using the Binary Log for more details about PITR using Binary logs.c) Execute the SQL statements obtained on the restored server, server is restored to PITR point3) Backing up relay-logs from slave server helps avoiding unnecessary pull of logs from master once it is restoredLet us understand this by an exampleSlave has 1 relay log with master binlog positions from 1 to 100SQL thread at slave reads from relaylog and apply events on slave. Now assume SQL thread currently executed statements 1 to 20 and 21 to 100 are yet to be executed.If DBA takes backup without copying relay log, when he/she restores the backup as slave, it asks master from the binlog position 21. So restored slave need to pull the logs of binlog position 21 to 100 from master. More network I/O needed as usually slave is on different machine.As MEB takes backup of relay log, slave can avoid pulling the logs for binlog positions 21 to 100. Now restored slave asks master from binlog positions 101 onwards. This way slave don't pull logs from master which are present in slave backup, there by reducing network I/O which is costly than disk I/O.Unlike binary logs, relaylogs are mostly deleted automatically once applied by SQL thread, as a result few relay logs exist at any point of time. So all the relay logs are copied for all the backup types full, incremental, partial without major impact on backup size and time.4) Copied binary logs remains consistent with the backup dataEarlier DBA had to copy binlog files manually in order to setup master/slave. Data files are copied by MEB and binlogs are copied by DBA at two different times, so there is a possibility of binlog files not consistent with the backed-up data.Lets consider following example:1. MEB takes backup of the server without binlogs at 1 PM2. DBA has copied binlogs from the server at 1:30 PMFrom 1 PM to 1:30, lets say 100 events logged in binlogsNow to use these binlog files, DBA has to either execute 100 events on server or have to remove 100 events from binlog files.Consider another example:1. DBA has copied binlogs from the server at 1:30 PM2. MEB takes backup of the server without binlogs at 2 PMFrom 1:30 PM to 2 PM, lets say 100 events went into backup dataNow DBA has to copy the missing binlog files again from the running server.With MEB 3.11.0 onwards, binlogs and the data are copied at the same time, so they are consistent with each other.Options to avoid binlogs/relay logs:--------------------------------------------------If DBA is not concerned about backing up binlog files then he/she can use --skip-binlog and --skip-relaylog to skip relay log files in backup. It is advisable to use these options if he/she don't plan to clone server or want PITR.For Master, to skip only binlogs:./mysqlbackup --skip-binlog --backup-dir=back_dir --socket=server_sock backupFor Slave, to skip relay-logs./mysqlbackup --skip-relaylog --backup_dir=back_dir --socket=server_sock backupFor Slave which is also a master, to skip both binlogs and relay logs./mysqlbackup --skip-binlog --skip-relaylog backup_dir=back_dir --socket=server_sock backupOptions for offline backup:------------------------------------MEB also supports offline backup. In order to copy binlog and/or relaylog, MEB searches for default values of log-bin-index(default: host_name-bin.index), relay-log-index(default: host_name-relay-bin.index), relaylog-info-file(default: relay-log.info), master-info-file(default: master.info) at default location that is in server's 'datadir'. And if MEB finds those files then it successfully backs up those files. In case those files are configured with different values, DBA need to provide --log-bin-index = PATH, --relay-log-index = PATH, --relaylog-info-file = PATH, --master-info-file=PATH options to MEB in order to copy them.Conclusion:-----------------To enrich the full instance backups that MySQL Enterprise Backup has been performing since release 3.9.0, all the replication setup files are included as part of all the backups in 3.11.0. With these files as well as all the global variables, plugin details, MEB now takes the responsibility of giving all the details to DBA for cloning any server. Read MEB 3.11.0 documentation for more details and many other great features.