1) Stop all PBXware services and make sure everything is stopped
/opt/pbxware/sh/stop /opt/httpd/sh/stop
2) Create a backup of current MySQL data before doing anything:
cd /opt/pbxware/pw/var/lib/ cp -a mysql mysql.bak
3) In order to force InnoDB recovery you will have to enter an additional config line in my.cnf file. This line goes into [mysqld] context:
nano /opt/pbxware/pw/etc/mysql/my.cnf Insert -> innodb_force_recovery = 1
4) Start mysqld:
/opt/pbxware/sh/mysqld
5) Check if MySQL daemon started without any issues. To do this check MySQL log file:
tail -n 30 /opt/pbxware/pw/var/log/mysql/mysqld.err (check if started properly)
6) Initiate MySQL data dump:
Note: Before starting a database dump or import, it is strongly recommended to use a persistent terminal session, for example screen. These operations can take a long time, and an SSH timeout or disconnect may terminate the process, forcing you to start over.
cd /opt/pbxware sh/mysqldump --all-databases --force > SqlDump.sql
If the initial dump fails and MySQL crashes, return to step 3) and increase innodb_force_recovery to 2 (this can go up to 6, ).
7) Once the dump is complete (mysqld did not crash and there were no serious errors) stop MySQL:
/opt/pbxware/sh/mysqladmin shutdown
8) Remove everything from pw/var/lib/mysql/ leaving only MySQL folder and files within
cd /opt/pbxware/pw/var/lib/mysql/
9) Edit my.cnf file and remove or comment out innodb_force_recovery conf line
10) Start mysqld and check if it's properly started (see 4. and 5.)
11) Initiate data import:
cd /opt/pbxware/ sh/mysql < SqlDump.sql
NOTE: In case the import is stopped due to duplicate entry error (i.e. - ERROR 1062 (23000) at line 344361: Duplicate entry '53527420' for key 'PRIMARY') the only way to complete the import will be to use --force switch.
Please be aware that this will skip all duplicate entries which will be omitted from the database, however, this is the only way to complete the process.
sh/mysql --force < SqlDump.sql
12) Wait for the import to complete. It may take some time depending on the amount of data and system speed. One way to monitor progress:
cd /opt/pbxware/pw/var/lib/ watch -n 15 "du -sh mysql*"
13) Shutdown mysqld once the import is complete ( see 7.)
14) Start all services
/opt/httpd/sh/start /opt/pbxware/sh/start
--------------------------------------------------------------------
If during dump or reimport you get MySQL complaints for some tables from MySQL folder like:
ERROR 1146 (42S02) at line 1896: Table 'mysql.slave_master_info' doesn't exist
ERROR 1146 (42S02) at line 1897: Table 'mysql.slave_master_info' doesn't exist
ERROR 1146 (42S02) at line 1898: Table 'mysql.slave_worker_info' doesn't exist
ERROR 1146 (42S02) at line 1899: Table 'mysql.slave_relay_log_info' doesn't exist
ERROR 1146 (42S02) at line 1903: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 1907: Table 'mysql.innodb_index_stats' doesn't exist
You need to remove all .ibd files along with corresponding .frm files. .MYD files and their corresponding .frm files should not be touched. The files mentioned are in the MySQL folder.
In this particular case slave_* , innodb_* , ibdata , iblog* were removed along with the performance_schema folder which I believe is not present on v4 systems.
After removing the above, start MySQL and perform mysql_upgrade from chroot like this:
chroot /opt/pbxware/pw mysql_upgrade -s
After this completes with no errors, stop MySQL and start it again to make sure there are no errors or crashes.
If there are no errors in the MySQL log and if the service does not crash start re-importing your data from the dump file (step 11.)
In case you notice errors like below in the MySQL log file,
2021-07-21 23:50:00 731 [Note] InnoDB: Database was not shutdown normally! 2021-07-21 23:50:00 731 [Note] InnoDB: Starting crash recovery. 2021-07-21 23:50:00 731 [Note] InnoDB: Reading tablespace information from the .ibd files... 2021-07-21 23:50:24 731 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_relay_log_info uses space ID: 3 at filepath: ./mysql/slave_relay_log_info.ibd. Cannot open tablespace admin_site/200_pbxware_auto_ranges_numbers which uses space ID: 3 at filepath: ./admin_site/200_pbxware_auto_ranges_numbers.ibd 2021-07-21 23:50:24 7fe618f65f40 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: Error: could not open single-table tablespace file ./admin_site/200_pbxware_auto_ranges_numbers.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here.
You will need to edit MySQL configuration file as per below and start MySQL again,
nano /opt/pbxware/pw/etc/mysql/my.cnf into [mysqld] context if it does not exist add, innodb_force_recovery = 2 If it exists simply put value to 2