MySQL Forums
Forum List  »  General

Re: databases are lost
Posted by: Peter Brawley
Date: March 15, 2021 11:34AM

Your MySQL server is running on the parent machine, its data is on a VM installed in this machine?
Do I have this right? Let's see my.cnf please

Based on this 27 Feb 2021 log, first order of biz would be to bring down the server, make the changes indicated below in my.cnf, move ib*.log from the mysql data directory to a new location, start the server, make sure replication is disabled, run mysql_upgrade, then restart the server without replication to see what errors remain.


Details ...

Quote

2021-02-27T00:19:17.366314Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 100000)
2021-02-27T00:19:17.366814Z 0 [Warning] Changed limits: max_connections: 4190 (requested 20000)
2021-02-27T00:19:17.366845Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 8000)
2021-02-27T00:19:17.485976Z 0 [Warning] The syntax '--metadata_locks_hash_instances' is deprecated and will be removed in a future release
2021-02-27T00:19:17.486127Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

...

2021-02-27T00:19:17.517942Z 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed.
2021-02-27T00:19:17.518043Z 0 [Warning] InnoDB: Setting innodb_checksums to OFF is DEPRECATED. This option may be removed in future releases. You should set innodb_checksum_algorithm=NONE instead.
2021-02-27T00:19:17.518052Z 0 [Warning] InnoDB: innodb_open_files should not be greater than the open_files_limit.

You need to make the indicated changes in my.cnf, see the manual for more info on each of these.

Quote

2021-02-27T00:19:31.694711Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=database-M-relay-bin' to avoid this problem.
2021-02-27T00:19:31.723242Z 1 [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.
2021-02-27T00:19:31.725450Z 2 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2021-02-27T00:19:31.733107Z 1 [Note] Slave I/O thread for channel '': connected to master 'admin@10.210.65.12:3306',replication started in log 'master6512.000170' at position 1007314
2021-02-27T00:19:31.755947Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'master6512.000170' at position 1007314, relay log './database-M-relay-bin.000393' position: 369

Replication is utterly broken. Again, what problem were you trying to solve by putting a VM layer between the MySQL server and its data files?

Quote

2021-02-27T00:19:31.857531Z 0 [ERROR] Native table 'performance_schema'.'status_by_thread' has the wrong structure
2021-02-27T00:19:31.857559Z 2 [Note] Failed to delete file './database-M-relay-bin.000393'
2021-02-27T00:19:31.857589Z 0 [ERROR] Native table 'performance_schema'.'status_by_user' has the wrong structure
2021-02-27T00:19:31.857615Z 0 [ERROR] Native table 'performance_schema'.'global_status' has the wrong structure
2021-02-27T00:19:31.857639Z 0 [ERROR] Native table 'performance_schema'.'session_status' has the wrong structure
2021-02-27T00:19:31.857661Z 0 [ERROR] Native table 'performance_schema'.'variables_by_thread' has the wrong structure
2021-02-27T00:19:31.857686Z 0 [ERROR] Native table 'performance_schema'.'global_variables' has the wrong structure
2021-02-27T00:19:31.857705Z 0 [ERROR] Native table 'performance_schema'.'session_variables' has the wrong structure

These msgs indicate that an upgrade was done incorrectly. For starters, you would have to stop all replication then run mysql_upgrade to see if that mysql utility can fix part of this mess.

Quote

2021-02-27T00:19:31.862613Z 2 [ERROR] Failed to open log (file './database-M-relay-bin.000394', errno 2)
2021-02-27T00:19:31.862635Z 2 [ERROR] Error reading relay log event for channel '': Could not open log file
2021-02-27T00:19:31.862650Z 2 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
2021-02-27T00:19:31.862654Z 2 [ERROR] Slave (additional info): File './database-M-relay-bin.000394' not found (Errcode: 2 - No such file or directory) Error_code: 29
2021-02-27T00:19:31.862658Z 2 [Warning] Slave: Being purged log ./database-M-relay-bin.000393 was not found Error_code: 1612
2021-02-27T00:19:31.862662Z 2 [Warning] Slave: File './database-M-relay-bin.000394' not found (Errcode: 2 - No such file or directory) Error_code: 29
2021-02-27T00:19:31.862666Z 2 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master6512.000170' position 1007314.

The server instance is broken so replication is also.

Quote

2021-02-27T00:19:31.870453Z 0 [ERROR] Cannot open mysql.db
2021-02-27T00:19:31.878314Z 0 [ERROR] Cannot open mysql.user
2021-02-27T00:19:31.878379Z 0 [ERROR] Cannot open mysql.event
2021-02-27T00:19:31.878385Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

Symptoms of the broken upgrade process. Let's see what the log says after you run mysql_upgrade.

Quote

2021-02-27T00:19:34.753901Z 0 [Warning] InnoDB: Cannot open './account_platform/tb_log.ibd'. Have you deleted .ibd files under a running mysqld server?
2021-02-27T00:19:34.753949Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-02-27T00:19:34.753954Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-02-27T00:19:34.753958Z 0 [Warning] InnoDB: Cannot open './account_platform/tb_log.ibd'. Have you deleted .ibd files under a running mysqld server?

Well, have you?



Edited 1 time(s). Last edit at 03/15/2021 11:37AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
March 02, 2021 03:33AM
March 02, 2021 11:25AM
March 09, 2021 08:31PM
March 09, 2021 09:17PM
March 09, 2021 09:25PM
March 10, 2021 10:08AM
March 10, 2021 07:53PM
March 10, 2021 09:23PM
March 11, 2021 12:11AM
March 11, 2021 05:36AM
March 11, 2021 06:39AM
March 11, 2021 09:37AM
March 11, 2021 08:04PM
March 12, 2021 09:11AM
March 12, 2021 07:36PM
March 12, 2021 08:17PM
March 12, 2021 08:41PM
March 13, 2021 09:41AM
March 15, 2021 10:03AM
Re: databases are lost
March 15, 2021 11:34AM
March 15, 2021 08:45PM
March 15, 2021 09:10PM
March 15, 2021 09:26PM
March 15, 2021 09:38PM
March 15, 2021 09:47PM
March 15, 2021 10:30PM
March 16, 2021 09:24PM
March 16, 2021 10:31PM
March 16, 2021 11:09PM
March 17, 2021 07:08AM
March 17, 2021 07:17AM
March 15, 2021 09:56PM
March 17, 2021 11:16AM
March 19, 2021 12:02AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.