Recovering InnoDB database from backed up “data” folder in XAMP in Windows 10

I copied the data directory from mysql folder and uninstalled the XAMPP as the MariaDB was not starting. I installed XAMPP same version and backed up the data folder into mysql data directory. At first only copied the required database folder named production inside the mysql data directory. PhpMyAdmin showed the tables correctly though no record/row was there and it showed "Table doesn’t exist in engine". Then I copied all the contents from the DATA folder to the mysql data folder and now it does not start. Now the problem is MariaDB is not starting. I tried setting the innodb_force_recovery but it failed to start the mysql.

The error log is given below:

2021-06-11  0:08:23 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2021-06-11  0:08:23 0 [Note] InnoDB: Uses event mutexes 2021-06-11  0:08:23 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2021-06-11  0:08:23 0 [Note] InnoDB: Number of pools: 1 2021-06-11  0:08:23 0 [Note] InnoDB: Using SSE2 crc32 instructions 2021-06-11  0:08:23 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M 2021-06-11  0:08:23 0 [Note] InnoDB: Completed initialization of buffer pool 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\applied.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`applied``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`applied` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\data.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`data``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`data` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\data_employer.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`data_employer``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`data_employer` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\education.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`education``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`education` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\education_list.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`education_list``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`education_list` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\industry.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`industry``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`industry` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\jobs.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`jobs``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`jobs` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\login.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`login``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`login` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\login_admini.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`login_admini``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`login_admini` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\login_employer.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`login_employer``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`login_employer` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\resume.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`resume``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`resume` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\jobs\selected.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``jobs`.`selected``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `jobs`.`selected` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\projects\entries.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``projects`.`entries``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `projects`.`entries` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\projects\geoname.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``projects`.`geoname``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `projects`.`geoname` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\projects\geonamebackup.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``projects`.`geonamebackup``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `projects`.`geonamebackup` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\projects\pincodes.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``projects`.`pincodes``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `projects`.`pincodes` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\projects\specifier.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``projects`.`specifier``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `projects`.`specifier` because it could not be opened. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation. 2021-06-11  0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2021-06-11  0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2021-06-11  0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\projects\users.ibd' OS error: 203 2021-06-11  0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation. 2021-06-11  0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/ 2021-06-11  0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``projects`.`users``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2021-06-11  0:08:23 0 [Warning] InnoDB: Ignoring tablespace for `projects`.`users` because it could not be opened. 2021-06-11  0:08:23 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2021-06-11  0:08:23 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2021-06-11  0:08:23 0 [Note] InnoDB: Setting file 'D:\Program Files\XAMP\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2021-06-11  0:08:23 0 [Note] InnoDB: File 'D:\Program Files\XAMP\mysql\data\ibtmp1' size is now 12 MB. 2021-06-11  0:08:23 0 [Note] InnoDB: Waiting for purge to start 2021-06-11  0:08:23 0 [Note] InnoDB: 10.4.19 started; log sequence number 1515509790; transaction id 611499 2021-06-11  0:08:23 0 [Note] InnoDB: Loading buffer pool(s) from D:\Program Files\XAMP\mysql\data\ib_buffer_pool 2021-06-11  0:08:23 0 [Note] Plugin 'FEEDBACK' is disabled. 2021-06-11  0:08:23 0 [Note] Server socket created on IP: '::'. 

Whether can mysql router connects to several MySQL InnoDB CLuster?

As suggested by MySQL, we can install MySQL Router service on application server.But how to configure the MySQL Router service when it needs to connect to several MySQL InnoDB Cluster?

For example, we have 2 InnoDB Clusters ClusterA and ClusterB.The applications connecting to the 2 clusters are under same host HostA.How can we configure in the mysql router on HostA, then we can use mysql router to connect 2 clusters.

InnoDB: page_cleaner: settings might not be optimal (how to tune)

I am having similar issues to this question but I’m trying to understand how to tune the database correctly (rather than just lowering settings and hoping 😉

In the error log I have these entries (often days or weeks apart)

2020-12-19T11:54:53.640527Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12204ms. The settings might not be optimal. (flushed=27 and evicted=0, during the time.) 2020-12-19T23:14:04.901370Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6788ms. The settings might not be optimal. (flushed=10 and evicted=0, during the time.) 

And corresponding entries in the mysql-slow log! down to the second. What I find weird is that I thought the cleaner was supposed to be some kind of background process… why does it have any impact on the queries themselves?

mysqltuner is happy, the db has 32 gigs ram and some relevant settings are

Settings:

  1. innodb_page_cleaners is currently 4,
  2. innodb_buffer_pool_instances is 25 !.
  3. innodb_buffer_pool_chunk_size is 134217728
  4. innodb_buffer_pool_size is 26843545600

Questions:

  1. Should I just set the page cleaners to 25 as suggested in the other answer.
  2. Should I try 1) first, see if it makes a difference, then lower the innodb_lru_scan_depth if I don’t see an improvement?
  3. Why doesn’t MYSQL just default the page cleaners to be the same as the number of instances if it can have such a drastic affect? t suggests setting the page cleaners to the same as the pool instances. Why doesn’t MYSQL just default to that if it’s so sensible?
  4. Is it expected to see the cleaner have an impact in the slow-query log?

Thanks!

How to safely change Innodb variables

I’m using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I’ve ran into a "Table handler out of memory" on some searches. The table itself is only 4.4 GB. I’ve read on stackoverflow, that changing some of the InnoDB variables such as:

• innodb_buffer_pool_size

• innodb_ft_result_cache_limit

from their default value to say 4 GB could potential solve to the memory issue. My question is three parts, I suppose.

  1. Are there any other variables I should consider changing.

• innodb_buffer_pool_instances
• innodb_ft_cache_size
• innodb_ft_total_cache_size

  1. Because this DB is fairly critical to run, after running the command lines to change the variables would I need to stop and start the MariaDB service to fetch these changes?

  2. If, restarting MariaDB services is needed, can anyone point me to a guide as far as how to safely change these variables?

Is there performance loss in out of sequence inserted rows (MySQL InnoDB)

I am trying to migrate from a bigger sized MySQL AWS RDS instance to a small one and data migration is the only method. There are four tables in the range of 330GB-450GB and executing mysqldump, in a single thread, while piped directly to the target RDS instance is estimated to take about 24 hours by pv (copying at 5 mbps).

I wrote a bash script that calls multiple mysqldump using ‘ & ‘ at the end and a calculated --where parameter, to simulate multithreading. This works and currently takes less than an hour with 28 threads.

However, I am concerned about any potential loss of performance while querying in the future, since I’ll not be inserting in the sequence of the auto_increment id columns.

Can someone confirm whether this would be the case or whether I am being paranoid for no reasons.

What solution did you use for a single table that is in the 100s of GBs? Due to a particular reason, I want to avoid using AWS DMS and definitely don’t want to use tools that haven’t been maintained in a while.

MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB. The first one has columns “Offset, Size, and ColumnToBeIndexed” with BIGINT “Offset” being the primary key, and the second one has columns “OffsetAndSize, and ColumnToBeIndexed” with BIGINT “OffsetAndSize” being the primary key. And there are both 15 millions rows in each table. I added indexes to both tables on “ColumnToBeIndexed.”My two queries for them are “SELECT Offset, Size FROM someTable WHERE ColumnToBeIndex BETWEEN 20 AND 10000 ORDER BY Offset ASC” and “SELECT OffsetAndSize FROM someTable WHERE ColumnToBeIndex BETWEEN 20 AND 10000 ORDER BY OffsetAndSize ASC.” Because the second query could use the secondary index and does not need to look up the clustered index to find the “size” information, I expected that the second query on the second table performs better than the first query on the first table. However, as the test came out, it turned out that the first query performs better every single time. Does anybody out there know what seems to be the problem?

MySQL innoDB cluster auto rejoin failed

3 node cluster, single primary. heavy read/write was happening on the master node. Restart the Master node. Then node 3 became the master. After the restart, the old master was in recovery state

"recoveryStatusText": "Distributed recovery in progress",                  "role": "HA",                  "status": "RECOVERING"    select * from gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | NO               | YES       |                   0 |                 8401 | +------------------+-----------+---------------------+----------------------+ 

this trx_to_cert never decreased even after 15mins,

then I tried to reboot node2

this also went to recovery mode.

Finally restart the node3, that’s all

It is saying no eligible primary in the cluster. Not able to recover it.

ERROR LOG:

2020-06-03T15:24:19.735261Z 2 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0' 2020-06-03T15:24:19.735271Z 2 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds' 2020-06-03T15:24:19.735285Z 2 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "41add3fb-9abc-11ea-a59d-42010a00040b"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; ' 2020-06-03T15:24:19.748017Z 6 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 2020-06-03T15:24:19.846752Z 9 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './dev-mysql-01-relay-bin-group_replication_applier.000002' position: 4 2020-06-03T15:24:19.846765Z 2 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 2020-06-03T15:24:19.868161Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3' 2020-06-03T15:24:19.868183Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33061' 2020-06-03T15:24:21.722047Z 2 [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address dev-mysql-03:3306.' 2020-06-03T15:24:21.722179Z 0 [ERROR] Plugin group_replication reported: 'Group contains 3 members which is greater than auto_increment_increment value of 1. This can lead to an higher rate of transactional aborts.' 2020-06-03T15:24:21.722427Z 24 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10' 2020-06-03T15:24:21.722550Z 0 [Note] Plugin group_replication reported: 'Group membership changed to dev-mysql-01:3306, dev-mysql-03:3306, dev-mysql-02:3306 on view 15910200188085516:19.' 2020-06-03T15:24:21.803914Z 24 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='dev-mysql-02', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. 2020-06-03T15:24:21.855802Z 24 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor bd472ec4-9abc-11ea-976d-42010a00040c at dev-mysql-02 port: 3306.' 2020-06-03T15:24:21.856155Z 26 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2020-06-03T15:24:21.862169Z 26 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'mysql_innodb_cluster_1@dev-mysql-02:3306',replication started in log 'FIRST' at position 4 2020-06-03T15:24:21.918855Z 27 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './dev-mysql-01-relay-bin-group_replication_recovery.000001' position: 4 2020-06-03T15:24:42.718769Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200603 15:24:42 2020-06-03T15:24:55.206155Z 41 [Note] Got packets out of order 2020-06-03T15:29:29.682585Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: dev-mysql-02:3306' 2020-06-03T15:29:29.682635Z 0 [Note] Plugin group_replication reported: 'The member with address dev-mysql-02:3306 has unexpectedly disappeared, killing the current group replication recovery connection' 2020-06-03T15:29:29.682635Z 0 [Note] Plugin group_replication reported: 'The member with address dev-mysql-02:3306 has unexpectedly disappeared, killing the current group replication recovery connection' 2020-06-03T15:29:29.682729Z 27 [Note] Error reading relay log event for channel 'group_replication_recovery': slave SQL thread was killed 2020-06-03T15:29:29.682759Z 0 [Note] Plugin group_replication reported: 'Group membership changed to dev-mysql-01:3306, dev-mysql-03:3306 on view 15910200188085516:20.' 2020-06-03T15:29:29.683116Z 27 [Note] Slave SQL thread for channel 'group_replication_recovery' exiting, replication stopped in log 'mysql-bin.000009' at position 846668856 2020-06-03T15:29:29.689073Z 26 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery' 2020-06-03T15:29:29.689089Z 26 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000009', position 846668856 2020-06-03T15:29:29.700329Z 24 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 2/10' 

How to improve performance for read only innodb MySQL Database

I have a particular task which is to maximize the concurrent performance. There is only one particular type of query, which is

select * from table where col1 between ? and ? and col2 between ? and ? 

I have created a composite index for (col1, col2). The table is about 20G in size and 100 million rows

However, even in peak concurrent requests, the CPU utilization for MySQL is only 30%. I have tried various techniques like increase max_connections, innodb_buffer_pool_instances but none of them are working.

How to maximize the configuration so that it can perform such read-only query to extreme?

MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to ‘dynamic’ first for all the tables to be on Barracuda then running “alter table engine = InnoDB” for 16 tables. 12 of the 16 tables changed file formats as well without an alter table command. I am at a loss to understand this. I think this may be related to the .frm files, but I’m not sure how. I’ve checked environment variables:

innodb_file_format is showing Barracuda

innodb_file_format_check is ON

A couple of the tables: Articletranslations is showing as compressed, pubmedabstractauthors and pubmedtranslated are showing as compact. The create table statements from tables that I had changed to dynamic file format before changing the storage engine to InnoDB.

Table: articletranslations

Create Table: CREATE TABLE `articletranslations` (   `TranslationID` int(11) NOT NULL AUTO_INCREMENT,   `ArticleID` int(11) NOT NULL,   `language` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,   `TextContent` longtext COLLATE utf8_unicode_ci,   `Name` text COLLATE utf8_unicode_ci,   `Tags` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,   `Detail_Abstract` longtext COLLATE utf8_unicode_ci,   `Disclosures` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,   `Discussion` longtext COLLATE utf8_unicode_ci,   `Acknowledgements` longtext COLLATE utf8_unicode_ci,   `D` longtext COLLATE utf8_unicode_ci,   `Materials` text COLLATE utf8_unicode_ci,   `HTMLTopContent` text COLLATE utf8_unicode_ci,   `Rep_Results` longtext COLLATE utf8_unicode_ci,   `Introduction` text COLLATE utf8_unicode_ci,   `IsMachine` tinyint(1) NOT NULL DEFAULT '1',   `DateTranslated` datetime DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`TranslationID`),   KEY `ArticleTranslations_Language_ArticleID` (`language`,`ArticleID`),   KEY `ArticleTranslations_ArticleID` (`ArticleID`) ) ENGINE=InnoDB AUTO_INCREMENT=177437 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED 

Table: pubmedabstractauthors

Create Table: CREATE TABLE `pubmedabstractauthors` (   `AuthorID` int(11) NOT NULL AUTO_INCREMENT,   `ForeName` varchar(255) NOT NULL,   `LastName` varchar(255) NOT NULL,   `Initials` varchar(255) NOT NULL,   PRIMARY KEY (`AuthorID`),   KEY `names` (`ForeName`,`LastName`,`Initials`) ) ENGINE=InnoDB AUTO_INCREMENT=712515 DEFAULT CHARSET=latin1 

Table: pubmedtranslated

Create Table: CREATE TABLE `pubmedtranslated` (   `PMID` int(11) NOT NULL,   `ArticleTitle` text COLLATE utf8_unicode_ci NOT NULL,   `ArticleAbstract` text COLLATE utf8_unicode_ci NOT NULL,   `LanguageID` smallint(6) NOT NULL,   PRIMARY KEY (`PMID`,`LanguageID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

MySQL import – all rows missing except those in final INSERT of InnoDB tables

This is a very strange bug. I’m using this on a mac:

mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64)

If I import an SQL dump of a WordPress DB (so not very complicated structure, but some long lines) from the command line, the table structure for all tables appears normal, but lots of content is missing from InnoDB tables.

Specifically, where the INSERTS have been ‘chunked’ into groups of records, only the final group is there. If I use --skip-extended-insert to write a statement for every record, only one record is ever in the InnoDB table.

MyISAM data seems fine.

I’ve tried dropping and recreating the database, could there be some corruption elsewhere?