0

I have a Percona MySQL 5.7.31 instance fresh from install on CentOS 7. Some of my collegues noticed some performance problems during some activity on it with a Drupal site, so I was checking another MySQL 5.7 instance that was made by another group. Just for convenience I made a clone of the vm where MySQL is running.

This is my basic my.cnf file

pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
ignore-db-dir=lost+found
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=512M
binlog_format=row
server-id=522232

This is the my.cnf I was trying from the other instance:


# GENERAL #
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/run/mysqld/mysqld.pid
symbolic-links                 = 0
ignore-db-dir                  = lost+found

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql

# BINARY LOGGING #
log-bin                        = bin.log
log-bin-index                  = bin-log.index
binlog_format                  = row
innodb-data-file-path          = ibdata1:512M;ibdata2:512M:autoextend:max:8G
expire-logs-days               = 1
sync-binlog                    = 1
server-id                      = 522232

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096
binlog-cache-size              = 16M

# INNODB #
innodb-flush-method            = O_DIRECT_NO_FSYNC

innodb-flush-neighbors         = 2
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1G
innodb-log-buffer-size         = 32M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1G
innodb-buffer-pool-instances   = 1
thread-pool-size               = 16
innodb-page-cleaners           = 2
innodb-io-capacity             = 2000
innodb-io-capacity-max         = 4000
innodb-page-size               = 8k 
innodb-log-write-ahead-size    = 8192
innodb-thread-concurrency      = 0   
innodb-write-io-threads        = 12  
innodb-read-io-threads         = 12  
innodb-read-ahead-threshold    = 56  
innodb-random-read-ahead       = ON  
innodb-lru-scan-depth          = 1024

# LOGGING #
log-error                      = /var/log/mysqld.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 0
slow-query-log-file            = /var/log/mysql/mysql-slow.log

With these parameter I immediately have an error regarding the Innodb system tablespace datafiles.

[ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 9728 pages (rounded down to MB) than the 65536 pages specified in the .cnf file!
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.

So I tried to create a new system tablespace using new datafiles changing the innodb-data-file-path using:

innodb-data-file-path          = ibdata1new:512M;ibdata2new:512M:autoextend:max:8G

...and I got these errors on restart

[ERROR] InnoDB: redo log file './ib_logfile0' exists. Creating system tablespace with existing redo log files is not recommended. Please delete all redo log files before creating new system tablespace.
[ERROR] InnoDB: InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.

I deleted the ibdata1 ib_logfile0 ib_logfile1 files and restarted, new system tablespace datafiles were created but I got several other errors

[Warning] Failed to open optimizer cost constant tables
[Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
[Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
[Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
[Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
[Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.

As I said this is a test environment and I can rollback easily, but I was wondering which is the right procedure to change the system tablespace and how to handle redo logs. Does anyone have suggestions?

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.