Table of contents of the article:
One of the under-explored issues in the SEO world, often unknown to professionals without Linux system expertise, concerns the difficulty of maintaining website availability during MySQL database backup operations. This challenge is equally unfamiliar to many system administrators who, despite excelling in technical system management, have limited SEO knowledge and fail to fully grasp the impact of such activities on a site's search engine rankings.
Typically, maintenance and backup tasks are scheduled for nighttime hours, when traffic and server load are expected to be lower. However, a significant problem can arise if Google's crawlers, busy crawling and indexing new content, happen to be operating during this time frame.
In an ideal context, where the backup has been carefully designed and created with advanced tools, considering the size of the dataset, the process should proceed smoothly, allowing Google to carry out its operations undisturbed. Instead, a poorly designed backup can lead to significant consequences, such as the inability of crawlers to access the site's contents, due to 500-type errors that indicate server malfunctions.
If this occurs, the so-called "crawling budget", i.e. the resources that Google dedicates to crawling a site, could be quickly exhausted, preventing the retrieval and indexing of new content. This may not only prevent site information from being updated in search engines in a timely manner, but also damage the site's long-term ranking and reputation. A high frequency of errors, or a high "error ratio", can in fact negatively influence the visibility of the site in search results.
Therefore, it is crucial that backup operations are carried out with the utmost care, considering all technical and logistical aspects, to avoid inconveniences and ensure constant and correct indexing of the site by Google. This is a challenge that requires effective collaboration between SEO experts and Linux systems engineers, so that both sides can understand and effectively address the full spectrum of implications of these critical operations.
But why does this happen?
To understand the causes of these problems – not excessively frequent, but not rare either – it is necessary to carefully examine the standard operating practices of a hosting provider, with particular attention to the management of server-level backups. This analysis must include an in-depth analysis of the specific dynamics that are activated during the backup of a website database.
Let's imagine a situation where, just as the hosting provider is backing up data, our site receives an external request. This request, processed through the PHP scripting language, involves the execution of multiple SQL queries directed at the database. In this context, what happens?
If backup management is ineffective, the result would be a “dirty” backup, compromised by simultaneous interactions with the database. For example, if during the backup we update a record in a personal data table without simultaneously updating the corresponding tax code table, we would end up with a backup that does not faithfully reflect the current state of the data, missing a fundamental element.
In a well-organized environment and according to consolidated operating standards, the task of performing the backup would be entrusted to the system administrator or to a control panel that uses "mysqldump" with locking parameters. These parameters allow you to lock tables for reading and writing during backup, thus ensuring data integrity and consistency. However, this methodology can also have limitations, such as generating locking issues that can block resources during the process. As a result, queries may not be processed within the time set by the web server, leading to a timeout or, even worse, a prolonged “hang” of the crawler, which would also result in timeouts on the crawler side.
To prevent such problems, it is essential to take targeted precautions. A thorough analysis of the scenario, which considers factors such as the technology used, the size of the dataset, the type of tables involved and the client's budget, is essential. This allows you to calmly plan the most appropriate and economical solution, satisfying the customer's needs without compromising the quality and effectiveness of the backup.
MySQL backup via mysqldump
Using mysqldump to back up MySQL databases is an extremely common and easy-to-use method, although it is often criticized for some of its limitations. Its widespread use stems from the fact that mysqldump is included with MySQL client programs, thus being pre-installed in most environments. This tool is capable of performing logical backups, offering the flexibility to back up entire databases, a specific database, all tables, or individual tables within a database. The resulting dump is sent to the standard output, which allows for easy redirection of the output to other programs for increased interoperability. A classic example is linking mysqldump output with gzip to compress the data, since mysqldump does not have a built-in compression function.
Despite these useful features, mysqldump has some significant drawbacks, particularly when managing large databases. Slow backup performance in such contexts is one of the biggest challenges . This inefficiency is attributable not only to the nature of logical backups, which take longer to complete than physical backups, but also to the fact that both backup and restore rely on single-threaded processes. Furthermore, the generated dump includes a complete set of SQL statements, which involve either table creation, data insertion, or both. This approach, while beneficial for backup portability and flexibility, can significantly increase the time and resources required to complete the operation, especially in environments with large data volumes.
Using mysqldump to back up MySQL, Percona Server, or MariaDB databases is often considered obsolete, especially in the current technological environment of 2023, which has evolved significantly since 2005. This consideration should prompt both you and your hosting provider to reconsider using this utility, especially for large databases, whose size is measured in gigabytes. In an era of constant technological evolution, adherence to outdated methodologies could represent a significant limitation, especially for ambitious and mission-critical projects. If your provider is unwilling to update, it may be worth considering a change of provider, especially if the success of your project is a priority.
Mysqldump works by extracting the contents of tables row by row, or by copying the entire contents of the table into a memory buffer before proceeding with the backup. The latter method can be problematic for large tables, causing significant slowdowns and inefficiencies.
The main goal of mysqldump is to generate a .sql file, known as a dump, that includes all the instructions needed to recreate the database. To ensure consistent backup, mysqldump uses specific table locking parameters: for example, use the –single-transaction option for InnoDB tables and –lock-tables for MyISAM tables.
For small databases, ranging from a few to ten megabytes, mysqldump can be a reasonable choice, taking only a few seconds or so to generate a .sql dump. However, with larger databases, measured in gigabytes, the backup process can become extremely long, lasting from tens of minutes to several hours.
During this period, the database remains in a locked state, blocking all read and write operations. This situation can not only cause slowdowns in daily operations, but also problems with search engine crawlers, such as Google, which may encounter server errors while crawling the site. These issues can negatively impact the site's ranking and visibility in search results, making it crucial to choose a more efficient backup method that minimizes the impact on normal database and website operations.
Percona XtraBackup. How to make a MySQL backup like a pro.
While the method listed above performs logical backups, which are excruciatingly slow, a similar approach becomes unusable once a database grows by a few gigabytes. Xtrabackup is the most popular physical MySQL backup software.
Percona is a leading database company, specializing in MySQL, MariaDB, MongoDB, and PostgreSQL solutions. It is renowned for its consulting, technical support and software services dedicated to database performance optimization and reliability. Among the products developed by Percona, one of the most important is Percona XtraBackup.
Percona XtraBackup stands out as one of the most advanced and reliable physical backup tools for MySQL and MariaDB. This software offers an efficient and secure way to perform full backups, without interrupting database service. XtraBackup is known for its ability to perform “hot” backups, that is, while the database is running, eliminating the need to stop or slow down operations during the backup process.
Percona XtraBackup represents an advanced solution for performing professional MySQL database backups, going beyond the limitations of the traditional mysqldump approach . In today's environment, with ever-growing databases, often measured in gigabytes, the slow and logical mysqldump approach becomes impractical. XtraBackup, developed by the renowned Percona company, known for its innovative database solutions, offers a much more efficient physical backup method.
This software allows you to perform "hot" backups, meaning you don't have to interrupt the database server. It's significantly faster than mysqldump and supports completely non-blocking backups, as long as all tables are InnoDB or XtraDB. XtraBackup can both create local backup files and stream standard output, making it an extremely versatile tool. For example, you can use tools like gof3r to stream backups to Amazon S3, allowing you to upload your backup directly to the cloud without having to store it locally. Although the backup size is nearly the same as the entire database, XtraBackup supports backup compression using qpress, significantly reducing the final size. Another notable feature is the ability to encrypt backups or streaming with standards like AES128, AES192, and AES256.
One of the main advantages of XtraBackup is its ability to perform backups without blocking the database, thus allowing it to continue responding to queries, even from nightly visitors, spiders and crawlers. This aspect is essential to avoid service interruptions and SEO problems due to downtime.
To understand the contrast between mysqldump and XtraBackup, it is helpful to analyze how mysqldump works. This tool locks the tables, proceeds to take the backup, and finally unlocks the tables. This procedure, while effective for small databases, becomes burdensome and time-consuming with larger databases, causing periods of downtime and possible database access problems.
Percona XtraBackup, on the other hand, leverages a native feature of MySQL and derived DBMS software, such as MariaDB or Percona Server, called binary log (binlog). The binlog records all changes to the database data or data structures, including SQL statements and schema-level changes. Percona XtraBackup uses this information to perform "hot" backups, without interrupting or crashing the database. This reduces downtime and increases database availability, maintaining uninterrupted operation.
In summary, while mysqldump requires a “cold” backup with potential downtime, Percona XtraBackup uses binlog for a “hot” backup that minimizes the impact on database operations while ensuring effective and timely recoverability. This advanced technology offers a dramatic improvement in both efficiency and reliability, making XtraBackup a prime choice for professionals and businesses managing large databases.
Performance MySQLdump VS Percona XtraBackup
If we want to touch real numbers or measure to decide, we can briefly see a benchmark between the two tools to understand what we are talking about and why mysqldump must be considered as an inappropriate toy for systems in production, going to create slow mysql backups.
From our analysis, it's clear that, even working on the same 73GB data set, a backup performed with mysqldump is up to 50 times slower than one performed with Percona XtraBackup. This means that a backup that might take hours with mysqldump could be completed in minutes with Percona XtraBackup. This can make a significant difference for businesses that need to perform frequent database backups and aim to minimize downtime.
It's also important to note that Percona XtraBackup is completely free software, with no limitations. It's currently considered the best backup solution available for systems based on MySQL and its derivatives. The value this tool offers is so great that it even surpasses MySQL Enterprise Backup, a product developed by the MySQL development team itself.
In a decidedly sarcastic tone, it's worth noting that MySQL Enterprise Backup is offered at the not-so-modest price of $5000 per year per server. This makes Percona XtraBackup's offering even more impressive, considering it not only offers a high-quality backup solution, but does so at no cost. This demonstrates how Percona XtraBackup represents an excellent choice in terms of both performance and value for money for businesses using MySQL or its derivative as their database management system.
In short, there is no reason not to use Percona XtraBackup other than superficiality and overt ignorance.
Do you use panels like cPanel or Plesk? Attention, they also use mysqldump
Unfortunately, amateurism and carelessness does not only concern Sunday systems engineers and improvised handymen but also companies of a certain depth that produce commercial control panels such as cPanel or Plesk.
In fact, reading on their website we can read in January 2019 regarding the use of Percona:
Furthermore, even compared to other types of backups, here is a simple summary that shows why you should use this way of working instead of improvising with antiquated and highly non-performing utilities.
Percona XtraBackup is the only solution able to satisfy all the requirements, it can be used in an absolutely profitable way even together with a MASTER / SLAVE replica but this is another matter.
Conclusion
In conclusion, if you find yourself frequently managing backups of individual databases and multiple .sql files in your daily or weekly backup space, it might be time to seriously consider some substantial changes. Accepting the slowness and inefficiency of MySQL backups as the norm is not only inappropriate, but it's a wake-up call that should prompt you to reconsider your current backup practices.
It is essential to carefully observe the behavior of your website. If you start seeing frequent errors from the Google spider, this is a clear sign that something in the backup process is not working properly. The situation becomes even more worrying if you find that your hosting provider still relies on outdated tools like mysqldump for backups. In such a scenario, you should seriously consider switching providers, especially if your website is a primary source of income and your current provider does not use advanced solutions like Percona XtraBackup.
Our intention is not to denigrate specific individuals or companies, but rather to highlight how an unprofessional approach to backups can pose a real risk not only to the financial health of your business, but also to your personal peace of mind. An inefficient or unreliable backup system can cause serious and sometimes irreparable data loss.
Your business deserves the best solutions available on the market. Switching to a provider that uses Percona XtraBackup can significantly improve speed, efficiency, and security. Take the time to carefully evaluate your options. Don't be afraid to take the next step and upgrade when you realize it's necessary.
The decision to adopt a more efficient backup system could have a crucial impact on the future of your business. Remember, a secure digital business is one that has the potential to grow and thrive sustainably. It is important to invest in the protection and security of your data. Don't wait until it's too late to act; take the initiative now and ensure the security and stability of your online business.
