MySQL

ProxySQL 2.0.6, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool. ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like ... [More] Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues. This release includes ProxySQL 2.0.6 which introduces many new features and enhancements and also fixes a number of bugs. The proxysql-admin tool has not changed since the previous release. The ProxySQL 2.0.6 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.6 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format. ProxySQL is available under Open Source license GPLv3. [Less]

One of the perks of working on MySQL is that the code can compile (and has been compiled) on pretty much all of the modern (and no so modern) C/C++ compilers and all the modern OSes. This enables each individual developer to choose their “weapon”: pick the OS and the compiler they feel best about.… Facebook Twitter Google+ LinkedIn

One of the perks of working on MySQL is that the code can compile (and has been compiled) on pretty much all of the modern (and no so modern) C/C++ compilers and all the modern OSes. This enables each individual developer to choose their “weapon”: pick the OS and the compiler they feel best about.… Facebook Twitter LinkedIn

While building my student image on Fedora 30, I installed the MySQL PHP Connector (php-mysqlndrp) but neglected to install the Python Connector. This adds the installation and basic test of the Python Connector to the original blog post. You use the ... [More] following command with a wildcard as a privileged user. The wildcard is necessary because you need to load two libraries to support Python 2.7 and 3.7, which are installed on Fedora 30. You also need to be the root user or a user that is found in the sudoer’s list: yum install -y mysql-connector-python* Display detailed console log → Last metadata expiration check: 0:35:46 ago on Tue 20 Aug 2019 05:36:29 PM MDT. Dependencies resolved. ===================================================================================================================================== Package Architecture Version Repository Size ===================================================================================================================================== Installing: mysql-connector-python x86_64 8.0.17-1.fc30 mysql-connectors-community 435 k mysql-connector-python-cext x86_64 8.0.17-1.fc30 mysql-connectors-community 7.7 M mysql-connector-python3 x86_64 8.0.17-1.fc30 mysql-connectors-community 429 k mysql-connector-python3-cext x86_64 8.0.17-1.fc30 mysql-connectors-community 7.7 M Installing dependencies: python2-protobuf noarch 3.6.1-3.fc30 fedora 563 k python3-protobuf noarch 3.6.1-3.fc30 fedora 568 k Transaction Summary ===================================================================================================================================== Install 6 Packages Total download size: 17 M Installed size: 89 M Downloading Packages: (1/6): python3-protobuf-3.6.1-3.fc30.noarch.rpm 1.0 MB/s | 568 kB 00:00 (2/6): python2-protobuf-3.6.1-3.fc30.noarch.rpm 994 kB/s | 563 kB 00:00 (3/6): mysql-connector-python-8.0.17-1.fc30.x86_64.rpm 481 kB/s | 435 kB 00:00 (4/6): mysql-connector-python3-8.0.17-1.fc30.x86_64.rpm 612 kB/s | 429 kB 00:00 (5/6): mysql-connector-python-cext-8.0.17-1.fc30.x86_64.rpm 3.8 MB/s | 7.7 MB 00:02 (6/6): mysql-connector-python3-cext-8.0.17-1.fc30.x86_64.rpm 4.2 MB/s | 7.7 MB 00:01 ------------------------------------------------------------------------------------------------------------------------------------- Total 5.4 MB/s | 17 MB 00:03 warning: /var/cache/dnf/mysql-connectors-community-8bcc2bd350b53f70/packages/mysql-connector-python-8.0.17-1.fc30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY MySQL Connectors Community 7.0 MB/s | 27 kB 00:00 Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering " Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : python3-protobuf-3.6.1-3.fc30.noarch 1/6 Installing : python2-protobuf-3.6.1-3.fc30.noarch 2/6 Installing : mysql-connector-python-8.0.17-1.fc30.x86_64 3/6 Installing : mysql-connector-python3-8.0.17-1.fc30.x86_64 4/6 Installing : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 5/6 Installing : mysql-connector-python-cext-8.0.17-1.fc30.x86_64 6/6 Running scriptlet: mysql-connector-python-cext-8.0.17-1.fc30.x86_64 6/6 Verifying : python2-protobuf-3.6.1-3.fc30.noarch 1/6 Verifying : python3-protobuf-3.6.1-3.fc30.noarch 2/6 Verifying : mysql-connector-python-8.0.17-1.fc30.x86_64 3/6 Verifying : mysql-connector-python-cext-8.0.17-1.fc30.x86_64 4/6 Verifying : mysql-connector-python3-8.0.17-1.fc30.x86_64 5/6 Verifying : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 6/6 Installed: mysql-connector-python-8.0.17-1.fc30.x86_64 mysql-connector-python-cext-8.0.17-1.fc30.x86_64 mysql-connector-python3-8.0.17-1.fc30.x86_64 mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 python2-protobuf-3.6.1-3.fc30.noarch python3-protobuf-3.6.1-3.fc30.noarch Complete! Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.1 Connecting to MySQL Using Connector/Python here’s a test of the connection to MySQL 8. # Import the library. import mysql.connector from mysql.connector import errorcode try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Print the value. print("Database connection resolved.") # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(e) # Close the connection when the try block completes. else: cnx.close() Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.4 Querying Data Using Connector/Python here’s a test of the connection to MySQL 8. # Import the library. import datetime import mysql.connector from mysql.connector import errorcode try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("SELECT " "CASE " " WHEN item_subtitle IS NULL THEN item_title " " ELSE CONCAT(item_title,': ',item_subtitle) " "END AS title, " "release_date " "FROM item " "WHERE release_date BETWEEN %s AND %s " "ORDER BY item_title") # Set the start and end date. start_date = datetime.date(1991, 1, 1) end_date = datetime.date(2004, 12, 31) # Execute cursor. cursor.execute(query, (start_date, end_date)) # Display the rows returned by the query. for (item_name, release_date) in cursor: print("{}, {:%d %b %Y}".format(item_name, release_date)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print "Error code:", e.errno # error number print "SQLSTATE value:", e.sqlstate # SQLSTATE value print "Error message:", e.msg # error message # Close the connection when the try block completes. else: cnx.close() If you run the above in Python 2.7 it works fine. It fails to parse successfully in Python 3.x because the print() function requires the parentheses all the time. You would need to re-write the except block, like this with the parentheses: # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message While it works without the parentheses in Python 2.7, it also works with the parentheses. That means the best practice is to write cross compatible code by always using the parentheses with the print() function. As always, I hope this helps somebody.j [Less]

While building my student image on Fedora 30, I installed the MySQL PHP Connector (php-mysqlndrp) but neglected to install the Python Connector. This adds the installation and basic test of the Python Connector to the original blog post. You use the ... [More] following command with a wildcard as a privileged user. The wildcard is necessary because you need to load two libraries to support Python 2.7 and 3.7, which are installed on Fedora 30. You also need to be the root user or a user that is found in the sudoer’s list: yum install -y mysql-connector-python* Display detailed console log → Last metadata expiration check: 0:35:46 ago on Tue 20 Aug 2019 05:36:29 PM MDT. Dependencies resolved. ===================================================================================================================================== Package Architecture Version Repository Size ===================================================================================================================================== Installing: mysql-connector-python x86_64 8.0.17-1.fc30 mysql-connectors-community 435 k mysql-connector-python-cext x86_64 8.0.17-1.fc30 mysql-connectors-community 7.7 M mysql-connector-python3 x86_64 8.0.17-1.fc30 mysql-connectors-community 429 k mysql-connector-python3-cext x86_64 8.0.17-1.fc30 mysql-connectors-community 7.7 M Installing dependencies: python2-protobuf noarch 3.6.1-3.fc30 fedora 563 k python3-protobuf noarch 3.6.1-3.fc30 fedora 568 k Transaction Summary ===================================================================================================================================== Install 6 Packages Total download size: 17 M Installed size: 89 M Downloading Packages: (1/6): python3-protobuf-3.6.1-3.fc30.noarch.rpm 1.0 MB/s | 568 kB 00:00 (2/6): python2-protobuf-3.6.1-3.fc30.noarch.rpm 994 kB/s | 563 kB 00:00 (3/6): mysql-connector-python-8.0.17-1.fc30.x86_64.rpm 481 kB/s | 435 kB 00:00 (4/6): mysql-connector-python3-8.0.17-1.fc30.x86_64.rpm 612 kB/s | 429 kB 00:00 (5/6): mysql-connector-python-cext-8.0.17-1.fc30.x86_64.rpm 3.8 MB/s | 7.7 MB 00:02 (6/6): mysql-connector-python3-cext-8.0.17-1.fc30.x86_64.rpm 4.2 MB/s | 7.7 MB 00:01 ------------------------------------------------------------------------------------------------------------------------------------- Total 5.4 MB/s | 17 MB 00:03 warning: /var/cache/dnf/mysql-connectors-community-8bcc2bd350b53f70/packages/mysql-connector-python-8.0.17-1.fc30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY MySQL Connectors Community 7.0 MB/s | 27 kB 00:00 Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering " Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : python3-protobuf-3.6.1-3.fc30.noarch 1/6 Installing : python2-protobuf-3.6.1-3.fc30.noarch 2/6 Installing : mysql-connector-python-8.0.17-1.fc30.x86_64 3/6 Installing : mysql-connector-python3-8.0.17-1.fc30.x86_64 4/6 Installing : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 5/6 Installing : mysql-connector-python-cext-8.0.17-1.fc30.x86_64 6/6 Running scriptlet: mysql-connector-python-cext-8.0.17-1.fc30.x86_64 6/6 Verifying : python2-protobuf-3.6.1-3.fc30.noarch 1/6 Verifying : python3-protobuf-3.6.1-3.fc30.noarch 2/6 Verifying : mysql-connector-python-8.0.17-1.fc30.x86_64 3/6 Verifying : mysql-connector-python-cext-8.0.17-1.fc30.x86_64 4/6 Verifying : mysql-connector-python3-8.0.17-1.fc30.x86_64 5/6 Verifying : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 6/6 Installed: mysql-connector-python-8.0.17-1.fc30.x86_64 mysql-connector-python-cext-8.0.17-1.fc30.x86_64 mysql-connector-python3-8.0.17-1.fc30.x86_64 mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 python2-protobuf-3.6.1-3.fc30.noarch python3-protobuf-3.6.1-3.fc30.noarch Complete! Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.1 Connecting to MySQL Using Connector/Python here’s a test of the connection to MySQL 8. # Import the library. import mysql.connector try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Print the value. print("Database connection resolved.") # Handle exception and close connection. except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) # Close the connection when the try block completes. else: cnx.close() Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.4 Querying Data Using Connector/Python here’s a test of the connection to MySQL 8. # Import the library. import datetime import mysql.connector try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Print the value. print("Database connection resolved.") # Set the query statement. query = ("SELECT item_title, release_date FROM item " "WHERE release_date BETWEEN %s AND %s") # Set the start and end date. start_date = datetime.date(1991, 1, 1) end_date = datetime.date(2004, 12, 31) # Execute cursor. cursor.execute(query, (start_date, end_date)) # Display the rows returned by the query. for (item_name, release_date) in cursor: print("{}, {:%d %b %Y}".format(item_name, release_date)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(err) # Close the connection when the try block completes. else: cnx.close() As always, I hope this helps somebody. [Less]

A few weeks ago I had the pleasure of talking over the internet with Will Vincent and Carlton Gibson about lots of Django-related topics. They somewhat informed me it was being recorded for a podcast. Now the day I anticipated and feared is here. ... [More] I discovered through this tweet that my voice is online: Episode 27 - MySQL & Security with Adam Johnson (@AdamChainz) is now live! Adam is a Django core developer responsible for the popular django-mysql package. We discuss why MySQL still makes sense with Django, security, hosting on AWS, and more. https://djangochat.com We talked about all kinds of things including: How I got started with Django Why MySQL and MariaDB are still a competitive databases for Django apps—despite their relative unpopularity in the community and ecosystem fragmentation. Ways to host Django My security talk at DjangoCon Europe this year Testing Django applications, coverage and speeding up tests If you’re interested in Django, head on over to the website and listen to Episode 027: MySQL & Security. Enjoy! —Adam (P.S. Don’t get mixed up and go to the wrong Twitter account, @DjangoChat, unless you want to see an adorable French cat.) [Less]

Since version 2.1.0 the FromDual Recovery Manager (rman) for MariaDB and MySQL has also a progress indicator for the restore of logical backups made with mysqldump. This feature was implemented because of the numerous requests of FromDual rman ... [More] users who were not happy with the default mysql behavior. You can check your current rman version as follows: # ./bin/rman --version 2.2.1 As with all FromDual tools you get a command overview with examples with the --help option: # ./bin/rman --help | less ... progress Print progress information to STDOUT. ... A backup for example is done as follows: # ./bin/bman --target=brman:[email protected]:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 The Recovery Manager progress indicator logs to STDOUT: # ./bin/rman --target=brman:[email protected]:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Reading configuration from /etc/mysql/my.cnf No rman configuration file. Command line: /home/mysql/product/brman-2.2.1/bin/rman.php --target=brman:******@127.0.0.1:3308 --type=full --mode=logical --policy=daily --instance-name=qamariadb102 --progress --backup-name=bck_qamariadb102_full_2019-08-20_21:15:23.sql Options from command line target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 Resulting options target = brman:******@127.0.0.1:3308 type = full mode = logical progress = backup-name = bck_qamariadb102_full_2019-08-20_21:15:23.sql policy = daily instance-name = qamariadb102 log = ./rman.log datadir = /var/lib/mysql owner = mysql backupdir = /home/mysql/bck binlog-policy = binlog Logging to ./rman.log Backupdir is /home/mysql/bck Version is 2.2.1 Start restore at 2019-08-20 21:18:46 mysql --user=brman --password=****** --host=127.0.0.1 --port=3308 From backup file: /home/mysql/bck/daily/bck_qamariadb102_full_2019-08-20_21:15:23.sql.gz Restore progress: . schema brman_catalog . . table backup_details 0 statements, 0 rows, 0 bytes . . table backups 0 statements, 0 rows, 0 bytes . . table binary_logs 0 statements, 0 rows, 0 bytes . . table files 0 statements, 0 rows, 0 bytes . . table metadata 1 statements, 2 rows, 78 bytes . schema foodmart . schema fromdual_a . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_b . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table employees 0 statements, 0 rows, 0 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema fromdual_c . . table audit 1 statements, 3 rows, 171 bytes . . table c1 1 statements, 3 rows, 42 bytes . . table c2 1 statements, 3 rows, 42 bytes . . table child 1 statements, 3 rows, 177 bytes . . table parent 1 statements, 3 rows, 175 bytes . schema mysql . . table column_stats 0 statements, 0 rows, 0 bytes . . table columns_priv 0 statements, 0 rows, 0 bytes . . table db 1 statements, 2 rows, 267 bytes . . table event 0 statements, 0 rows, 0 bytes . . table func 0 statements, 0 rows, 0 bytes . . table gtid_slave_pos 0 statements, 0 rows, 0 bytes . . table help_category 1 statements, 39 rows, 1202 bytes . . table help_keyword 1 statements, 464 rows, 7649 bytes . . table help_relation 1 statements, 1028 rows, 9861 bytes . . table help_topic 1 statements, 527 rows, 419915 bytes . . table host 0 statements, 0 rows, 0 bytes . . table index_stats 0 statements, 0 rows, 0 bytes . . table innodb_index_stats 1 statements, 207 rows, 20611 bytes . . table innodb_table_stats 1 statements, 29 rows, 1622 bytes . . table plugin 0 statements, 0 rows, 0 bytes . . table proc 1 statements, 2 rows, 2220 bytes . . table procs_priv 0 statements, 0 rows, 0 bytes . . table proxies_priv 1 statements, 2 rows, 140 bytes . . table roles_mapping 0 statements, 0 rows, 0 bytes . . table servers 0 statements, 0 rows, 0 bytes . . table table_stats 0 statements, 0 rows, 0 bytes . . table tables_priv 0 statements, 0 rows, 0 bytes . . table time_zone 0 statements, 0 rows, 0 bytes . . table time_zone_leap_second 0 statements, 0 rows, 0 bytes . . table time_zone_name 0 statements, 0 rows, 0 bytes . . table time_zone_transition 0 statements, 0 rows, 0 bytes . . table time_zone_transition_type 0 statements, 0 rows, 0 bytes . . table user 1 statements, 5 rows, 1042 bytes . . table general_log 0 statements, 0 rows, 0 bytes . . table slow_log 0 statements, 0 rows, 0 bytes . schema test . . table test 347 statements, 4621 rows, 286528 bytes . schema test_catalog . schema world . . table City 1 statements, 4079 rows, 177139 bytes . . table Country 1 statements, 239 rows, 36481 bytes . . table CountryLanguage 1 statements, 984 rows, 26160 bytes . schema brman_catalog . schema foodmart . schema fromdual_a . schema fromdual_b . schema fromdual_c . schema mysql . schema test . schema test_catalog . schema world Schemas: 9, Tables: 55, Statements: 376, Rows: 12275, Bytes: 992736 WARNING: Progress numbers for Total Byte Counter may be different of dump file size. Restore time was: 0d 0h 1' 28" End restore at 2019-08-20 21:20:14 (rc=0) The overhead of FromDual Recovery Manager progress indicator for MariaDB and MySQL is not significant. We measured less than 1% longer recovery times with the progress indicator compared to pure mysql restoration. Taxonomy upgrade extras:  Restore Recovery pitr brman rman FromDual Backup and Recovery Manager [Less]

Percona announces the release of Percona Server for MySQL 5.6.45-86.1 on August 20, 2019. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker ... [More] Hub repository. Based on MySQL 5.6.45, and including all the bug fixes in it, Percona Server for MySQL 5.6.45-86.1 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Bugs Fixed: The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850. The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748. Other bugs fixed: #5531, #5146 #5638, #5645, #5669, #5749 #5752, #5780, #5833, #5725 #5742, #5743, and #5746. Release notes are available in the online documentation. Please report any bugs on the JIRA bug tracker. [Less]

Please join Percona Principal Support Engineer Marcos Albe as he presents his talk “Flame Graphs 201″ on Thursday, August 22th, 2019 at 11:00 AM PDT (UTC-7). Register Now Visualizing profiling information can be a very powerful tool for ... [More] performance diagnostics, especially if we zoom right into the problem. Flame graphs were developed for this purpose and we use them on a daily basis at Percona to successfully solve complex performance issues. In this presentation, attendees will learn advanced techniques for profiling with performance and visualizing those profiles with flame graphs, as well as other assorted tricks that the flame graph scripts allow us to do. If you can’t attend, sign up anyways we’ll send you the slides and recording afterward. [Less]

Overview The Skinny Clustering provides high availability and disaster recovery, along with the ability to read-scale both locally and globally. Some clusters even provide active/active capabilities, which others have a single master. Real time ... [More] database replication is a must for clustering and other key business purposes, like reporting. There are a number of replication technologies available for MySQL, and some are even bundled into various solutions. When choosing a replication methodology, it is paramount to understand just how the data moves from source to target. In this blog post, we will examine how asynchronous, synchronous, and “semi-synchronous” replication behave when used for clustering. Also, we will explore how replication affects database performance and data availability. Asynchronous Replication Fast and Flexible Asynchronous replication (async for short) is used by MySQL natively as well as by our own Tungsten Replicator. With async, all replication is decoupled from the database. Specifically, replication is a background process that reads events from the binary log on disk and makes those events available to the slaves upon request. There is no change to how the application works, and thus is quick and easy to implement. Also, async does not slow down the response from MySQL to the application. With asynchronous replication, you have: The least impact to application performance of any replication type, because replication is handling database events separately in the background by reading the binary logs. The best (and really the only) choice for WAN replication because the application does not need to wait for confirmation from each slave before proceeding – this delay is prohibitive over long distances due to the simple physics of the speed of light creating delays in transit (Round-Trip-Time or RTT). The ability to deploy databases at geo-scale, serving applications that need high performance globally. Replication is de-coupled from your application, so slow WAN links do not imapct application performance. A chance of data lag on the replication slaves, which means that the slaves are not completely up-to-date with the master. In the event of a failure, there is a chance the newly-promoted slave would not have the same data as the master. The risk of data loss when promoting a slave to master per the above point. There are a number techniques to mitigate data loss which will be discussed in another blog post. Synchronous Replication Slow and Steady Used by Galera and its variants, synchronous replication (sync for short) addresses the above data loss issues by guaranteeing that all transactions are committed on all nodes at database commit. Synchronous replication will wait until all nodes have the transaction committed before providing a response to the application, as opposed to asynchronous replication which occurs in the background after a commit to the master. With the sync method, you can be sure that if you commit a transaction, that transaction will be committed on every node. With synchronous replication, you have: The most significant application lag of any type of replication, because your application must wait for all nodes in the cluster to commit the transaction too. Per above, it is complicated to implement over wide-area or slow networks, due to the almost prohibitive application lag, as it waits for transactions to be committed on remote databases. Transaction commit on all nodes, guaranteed. No slave data lag, as slaves are always up to date by definition. No chance of a data loss window in the event of a failover. Possibility to have multiple masters in a local cluster using a process called “certification”; certification will process transactions in order or, in the event of a conflict, raise an error. Semi-Synchronous Replication The New Guy One example of semi-synchronous replication is MySQL group replication. MySQL group replication introduces “semi-synchronous” in an attempt to merge the advantages of both asynchronous and semi-synchronous replication. With semi-synchronous replication (semi-sync for short), transactions are committed on the master, transferred to at least one slave node but NOT NECESSARILY committed. At this point, control is handed back to the application and commits to the slaves are handled in the background. Compared to synchronous replication, applications can potentially be more responsive since they receive control back sooner (though it is not as fast as async). Also, compared to async, there is less chance of data loss and potentially less replication lag. With semi-synchronous replication, you have: Application response that is faster than synchronous replication, but slower than asynchronous replication The potential for a data loss window that is smaller than with asynchronous replication, but still larger than with synchronous replication. Configuration is more complex than with asynchronous replication, since it is not decoupled from the master database. A relatively new technology as compared to other replication methodologies. It remains to be seen if semi-sync will be a viable solution for production workloads. Why does Tungsten Clustering choose Asynchronous Replication? The Wrap-Up Tungsten Clustering uses the Tungsten Replicator, leveraging asynchronous replication, so that complex clusters can be deployed at geo scale without modifying or impacting applications or database servers. When deploying over wide area networks, asynchronous replication is the best and usually only option to protect application performance. Also, even over fast LAN’s, for write-intensive workloads, asynchronous replication is the best choice because the bottom-line impact to the application is minimized. Look out for Part 2 of this blog, that will dive into how different replication technologies impact cluster behavior in day-to-day operations (such as failover, local and global replication breaks, zero downtime, maintenance updates, etc.). For more information about Tungsten clusters, please visit https://docs.continuent.com If you would like to speak with one of our team, please feel free to reach out here: https://www.continuent.com/contact [Less]