+7Hello,
We are doing POC for one of the customer. They are using MySQL 8.0.25-15 on Ubuntu 20.04. We installed Commvault v11.26.8. We enabled xtrabackup and we are trying to full backup. Mysql connections of users drop while backup is in progress. They are restarting services and connections are coming back. Any idea why backup dropping users connection? I’m sharing log files with you.
Regards,
İbrahim
Best answer by Mike Struening RETIRED
Sharing case solution (removed anything identifiable):
Finding Details:
MYSQL backup failed with error, message is: STDERR: [mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect ]
Solution:
We reviewed the Mysql system logs -
2022-03-19T14:21:01.309942Z 355128 [Warning] Open files 3006 exceeds the limit 3000
2022-03-19T14:22:48.124888Z 0 [Warning] [MY-012152] [InnoDB] Open files 3007 exceeds the limit 3000
2022-03-21T12:19:24.056857Z 0 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
2022-03-21T12:20:52.935081Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.25-15).
2022-03-21T12:20:58.300995Z 0 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
2022-03-21T12:21:07.196052Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.25-15) Percona Server (GPL), Release '15', Revision 'a558ec2'.
""""2022-03-21T12:21:20.435843Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 10000 (request: 100000)
2022-03-21T12:21:20.435855Z 0 [Warning] [MY-010141] [Server] Changed limits: max_connections: 9190 (requested 20000)
2022-03-21T12:21:20.435862Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 400 (requested 4096)
2022-03-21T12:21:20.735640Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-03-21T12:21:20.738821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-15) starting as process 899
2022-03-21T12:21:20.803633Z 1 [System] [MY-013576] [] initialization has started.
2022-03-21T12:21:29.656096Z 1 [System] [MY-013577] [] initialization has ended.
2022-03-21T12:21:33.311401Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '0.0.0.0' port: 33060, socket: /var/run/mysqld/mysqlx.sock
mysqld: File '/var/log/mysql/TCMX-SQL-TEMPLA-slow.log' not found (OS errno 13 - Permission denied)
2022-03-21T12:21:33.370809Z 0 [ERROR] [MY-011263] [Server] Could not use /var/log/mysql/TCMX-SQL-TEMPLA-slow.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL SLOW_QUERY_LOG=ON" or restart the MySQL server.
2022-03-21T12:21:36.924943Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-03-21T12:21:36.925652Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-03-21T12:21:37.199949Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.25-15' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release '15', Revision 'a558ec2'.
2022-03-21T12:25:44.409491Z 300 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
2022-03-21T12:28:23.553040Z 0 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
-----------------------------------------
Please review this internally regarding the same as why the backups causes DB connectivity issues.
Good day
MySQL backups with xtrabackup (Percona) are failing due to following error -
MySQLBackupChild.log
198650 307fa 02/18 17:08:32 33 MySqlChildProcess::Wait() - Child died after receiving signal 2
198650 307fa 02/18 17:08:32 33 MySqlBackupDb::writeDataToPipe() - Wait returned failure
198650 307fa 02/18 17:08:32 33 MySqlBackupDb::writeDataToPipe() - last buffer=[]
198650 307fa 02/18 17:08:32 33 MySqlBackupDb::writeDataToPipe() - STDERR: [mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect
]
198650 307fa 02/18 17:08:32 33 MySqlBackupDb::DoBackupDbUsingPipe() - writeDataToPipe failed
198650 307fa 02/18 17:08:32 33 ::MySqlClBackupBase::DoBackupMySql() - MySqlClBackupBase::DoBackupDb returned error
198650 307fa 02/18 17:08:32 33 ::MySqlClBackupBase::DoBackupMySql() - Performance counter enabled from Registry
198650 307fa 02/18 17:08:32 33 ::MySqlClBackupBase::DoBackupMySql() - Analysing Database = [mysql]
198650 307fa 02/18 17:08:32 33 GetMySqlInfoExt::CheckDBPresence() - Checking DB Presence Failed...Probably connection to Server not present or Querying Server failed
198650 307fa 02/18 17:08:32 33 ::CheckDbExistence() - database found in server... will continue to dump...
198650 307fa 02/18 17:08:32 33 MySqlBackupDb::DoBackupDbUsingPipe() - reg key bUseSingleTransaction is either not set or fetch not succeeded.
198650 307fa 02/18 17:08:32 33 GetMySqlInfoExt::QueryVariable() - cannot query variable [2002:Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)]
198650 307fa 02/18 17:08:32 33 GetMySqlInfoExt::GetVersionFromServer() - Server version : 8.0.25-15
198650 307fa 02/18 17:08:32 33 GetMySqlInfoExt::QueryVariable() - cannot query variable [2002:Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)]
198650 307fa 02/18 17:08:32 33 GetMySqlInfo::isMariaDb() - cannot get 'version_comment' value from server.
198650 307fa 02/18 17:08:32 33 GetMySqlInfoExt::isMySQL8orMore() - MariaDB check failed.
Could you try the following
Set the value for the max_allowed_packet variable as 1GB in the MySQL server.
However, you can set a different value for the client by configuring the value for sAdvancedBackupOptions.
The value set on the client is given priority over the one set on MySQL server.
Increase the value for the connect_timeout variable on the MySQL server to 100.
3#
The backup operation fails if the memory size required to dump the BLOB (Binary Large Object) is less. The memory size depends on the RAM usage of the server.
Increase the system memory size.
For more information check the troubleshooting section https://documentation.commvault.com/11.24/expert/19483_backup_troubleshooting_mysql_agent.html
+7Hi
Thank you for the information. I sent this to our customer. I’ll get back to you.
Regards,
İbrahim
+7Hi,
We opened a case. Case number is: 220225-223.
+23Sharing case solution (removed anything identifiable):
Finding Details:
MYSQL backup failed with error, message is: STDERR: [mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect ]
Solution:
We reviewed the Mysql system logs -
2022-03-19T14:21:01.309942Z 355128 [Warning] Open files 3006 exceeds the limit 3000
2022-03-19T14:22:48.124888Z 0 [Warning] [MY-012152] [InnoDB] Open files 3007 exceeds the limit 3000
2022-03-21T12:19:24.056857Z 0 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
2022-03-21T12:20:52.935081Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.25-15).
2022-03-21T12:20:58.300995Z 0 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
2022-03-21T12:21:07.196052Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.25-15) Percona Server (GPL), Release '15', Revision 'a558ec2'.
""""2022-03-21T12:21:20.435843Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 10000 (request: 100000)
2022-03-21T12:21:20.435855Z 0 [Warning] [MY-010141] [Server] Changed limits: max_connections: 9190 (requested 20000)
2022-03-21T12:21:20.435862Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 400 (requested 4096)
2022-03-21T12:21:20.735640Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-03-21T12:21:20.738821Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25-15) starting as process 899
2022-03-21T12:21:20.803633Z 1 [System] [MY-013576] [] initialization has started.
2022-03-21T12:21:29.656096Z 1 [System] [MY-013577] [] initialization has ended.
2022-03-21T12:21:33.311401Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '0.0.0.0' port: 33060, socket: /var/run/mysqld/mysqlx.sock
mysqld: File '/var/log/mysql/TCMX-SQL-TEMPLA-slow.log' not found (OS errno 13 - Permission denied)
2022-03-21T12:21:33.370809Z 0 [ERROR] [MY-011263] [Server] Could not use /var/log/mysql/TCMX-SQL-TEMPLA-slow.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL SLOW_QUERY_LOG=ON" or restart the MySQL server.
2022-03-21T12:21:36.924943Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-03-21T12:21:36.925652Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-03-21T12:21:37.199949Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.25-15' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release '15', Revision 'a558ec2'.
2022-03-21T12:25:44.409491Z 300 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
2022-03-21T12:28:23.553040Z 0 [Warning] [MY-012152] [] Open files 3006 exceeds the limit 3000
-----------------------------------------
Please review this internally regarding the same as why the backups causes DB connectivity issues.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
