Mysql

Sometimes, you may need to terminate multiple SQL queries simultaneously. This can be time-consuming, as the MySQL KILL command accepts only one argument at a time. A straightforward solution is to create a script to automate this process. First, begin by listing the processes you wish to terminate. For more information on listing processes, refer to: https://support.qualityunit.com/245419-Identifying-and-Filtering-Problematic-SQL-Queries-in-MySQL You may wish to further refine this script to...

When your MySQL server encounters issues and you need to identify SQL queries with the worst performance, the first action every server administrator typically takes is to open the MySQL console and start investigating using the SHOW PROCESSLIST or SHOW FULL PROCESSLIST commands. On servers with many concurrent connections to multiple databases, you will often see numerous rows with the command text: Sleep. When I am troubleshooting slow SQL queries, I do not want to be distracted by these sle...

MySQL and MariaDB replication is a powerful feature for database redundancy and scaling. However, unexpected crashes can disrupt replication and lead to errors that require careful diagnosis and resolution. This article guides you through identifying common replication errors after a crash and provides steps to restore replication functionality. Common Error Messages After a server crash, you may encounter error messages such as: Last_IO_Error: Got fatal error 1236 from master when reading da...

Here is the SQL command you should use when you need to calculate the size of databases on your MySQL server directly from the MySQL console: SELECT table_schema AS "DB Name", SUM(data_length + index_length) / 1024 / 1024 AS "DB Size in MB", SUM(data_free)/ 1024 / 1024 AS "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema; You can run this SQL command after logging into your MySQL console. It will provide a summary showing each database name, its total size...

Migrating MySQL databases that use MyISAM tables to a MariaDB Galera Cluster requires careful planning, as Galera supports only InnoDB tables for synchronous replication. This guide outlines the recommended steps for a smooth migration and provides a bash script to automate parts of the process. Prerequisites - Access to the source MySQL database with MyISAM tables - A running MariaDB Galera Cluster - Sufficient storage and network capacity for the data transfer - Familiarity with command-line...

Follow these steps to upgrade your existing MariaDB 10.1.x installation to the latest available 10.1.x release. 0. Prepare for the Upgrade Important: Ensure the following before proceeding: - Ensure that no traffic is being sent to the database server. - Back up all configuration files (e.g., /etc/my.cnf*). - Back up all databases on the server in case any issues occur during the upgrade process. Always perform a full backup before starting the upgrade process to avoid data loss. 1. Update ...

To reduce used space by Mysql InnoDb tables (we use file per table setting), you need to optimize table. Otherwise, the InnoDB data file just grows. Even if you delete some rows from table, free space for deleted rows is correctly reused later by new rows, but data file on disk will never reduce the size alone. IMPORTANT: - small tables NO NEED to optimize - there is no big win to do that - huge tables SHOULD NOT be optimized on live server - it will lock your table for minutes/hours Replicat...

Resolving MySQL Replication Failures Due to Corrupted Relay Binlogs Sometimes MySQL replication crashes due to corrupted relay binlogs (error 1594), and it is not possible to restart it with a simple START SLAVE command. Here is a step-by-step guide on how to resolve this issue. Step 1: Check the Current Slave Status To check the current slave status, execute the following command: show slave status\G You should see a result similar to this: ************************** 1. row **************...