Table of contents of the article:
One of the little explored issues in the SEO world and often unknown to professionals who do not have skills in Linux systems, concerns the difficulties of availability of a website during MySQL database backup operations. This challenge is equally unknown to many systems engineers who, despite excelling in the technical management of systems, have limited SEO knowledge and are unable to fully grasp the repercussions of these activities on the positioning of the site in search engines.
Typically, maintenance and backup activities are scheduled at night, when reduced traffic and less load on the server are expected. However, a significant problem may emerge if Google's crawlers, engaged in scanning and indexing new content, find themselves operating during this period of time.
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 backup MySQL databases is an extremely common and easy-to-apply method, although it is often criticized for some of its limitations. Its wide diffusion derives from the fact that mysqldump is included in MySQL client programs, therefore being pre-installed in most environments. This tool is capable of performing logical backups, giving you the flexibility to back up entire databases, a specific database, all tables, or individual tables within a database. The produced dump is sent to standard exit, which allows you to easily redirect the output to other programs to increase interoperability. A classic example is linking mysqldump output with gzip to compress data, since mysqldump does not integrate a compression function.
Despite these useful features, mysqldump has some significant issues, particularly when it comes to managing large databases. The slowness of performing backups 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 recovery rely on single-threaded processes. Additionally, the generated dump includes a complete set of SQL statements, covering either table creation or 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 high-volume data environments.
The use of mysqldump to backup MySQL, Percona Server or MariaDB databases is often considered no longer adequate, especially in the current technological context of 2023, which has significantly evolved compared to 2005. This reflection should push both you and your hosting provider to reconsider the use of this utility, especially in the context of large databases, the size of which can be measured in gigabytes. In an era of continuous technological evolution, adherence to outdated methodologies could represent a significant limitation, especially for ambitious and mission-critical projects. If your provider is unwilling to upgrade, it may be worth considering a supplier change, 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 cause not only slowdowns in daily operations, but also problems with search engine crawlers, such as Google, which may encounter server errors when crawling the site. These problems can have negative repercussions on the positioning and visibility of the site in search results, therefore making it crucial to choose a more efficient backup method that has less impact on the normal operations of the database and website.
Percona XtraBackup. How to make a MySQL backup like a pro.
While the method listed above performs logical backups, with grueling slowness, such an approach becomes unusable as a database begins to grow a few gigabytes. Xtrabackup is the most popular MySQL physical 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 backups of MySQL databases, going beyond the limits of the traditional mysqldump approach. In the current environment, with ever-growing databases, often measured in gigabytes, the logical and slow approach of mysqldump becomes impractical. XtraBackup, developed by the renowned company Percona, known for its innovative solutions in the field of databases, offers a much more efficient physical backup method.
This software allows you to create "hot" backups, i.e. without the need to interrupt the operation of the database server. It is significantly faster than mysqldump and supports completely non-blocking backups, as long as all tables are of type 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. Even though the size of backups is almost as large as the entire database, XtraBackup supports compressing backups using qpress, significantly reducing the final size. A standout feature is also the ability to encrypt backups or streaming with standards such as 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, however, takes advantage of 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 database data or data structures, including SQL statements and schema-level changes. Percona XtraBackup uses this information to perform “hot” backups, without interrupting or blocking the database. This helps reduce downtime and increase database availability, maintaining uninterrupted operations.
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 examination, it is clear that, even working on the same data set of 73GB, a backup performed with mysqldump is even 50 times slower than one performed with Percona XtraBackup. This means that a backup that could take hours with mysqldump could be completed in minutes with Percona XtraBackup. This can make a significant difference to businesses that need to perform database backups frequently and aim to minimize downtime.
It is also important to underline that Percona XtraBackup is completely free software, without any limitations. Currently, it is considered the best backup solution available for systems based on MySQL and its derivatives. The value offered by this tool is such that it even surpasses MySQL Enterprise Backup, a product developed by the MySQL developer team itself.
In a distinctly sarcastic tone, it should be noted that MySQL Enterprise Backup is offered at the not exactly modest price of $5000 per server per year. This makes Percona XtraBackup's offer even more impressive, considering that it not only offers a high-quality backup solution, but does it at no cost. This demonstrates that Percona XtraBackup is an excellent choice in terms of both performance and value for money for enterprises using MySQL or a derivative thereof as a 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 various .sql files in your daily or weekly backup space, it may be time to seriously consider some breaking changes. Accepting slow and inefficient MySQL backups as the norm is not only inappropriate, but a wake-up call that should prompt you to question your 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 represent a significant improvement in speed, efficiency and security. Take the time to carefully consider 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.
