rebuild the database indexes

itj205

Member
May 16, 2022
8
0
1
AU
cPanel Access Level
Root Administrator
Hi,

Recently, MYSQL / MariaDB database dropped from the CPanel User account.

Restore by Moving the MYSQL Database folder into the user's account and now showing up on the user's Cpanel account.

But get 1932 error on all tables and only show 0kb. it contains about 50MB.

1932 Table issue comes from session issue which all table shows in use.


How to fix this issue of reindexing raw data and clear "in use"?
 

Attachments

Last edited by a moderator:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
11,011
1,738
363
cPanel Access Level
Root Administrator
Hey there! Can you get me more details on this portion of the statement?

"Restore by Moving the MYSQL Database folder into the user's account and now showing up on the user's Cpanel account."

I wouldn't expect any MySQL data that is inside the /home/username area to work properly. Any data would need to be located in the main MySQL area of the system, which is typically /var/lib/mysql.

The easiest way to resolve the issue may be to restore the account from a backup.
 

itj205

Member
May 16, 2022
8
0
1
AU
cPanel Access Level
Root Administrator
Hi, thank you for your reply.

account backup wasn't done and only entries SQL. which I could find that directory of database and try to restore.




  • Stop MySQL
  • Change the 'datadir' variable in /etc/my.cnf to /disk1/2022-05-10/system/dirs/_var_lib_mysql_
  • Start MySQL
  • Perform the mysqldump command on the desired database
  • Stop MySQL
  • Change the 'datadir' variable in /etc/my.cnf back to /var/lib/mysql
  • Start MySQL
  • Import the database dump

[[email protected] ventuno]# mysqldump ventuno_wp685 > dump.sql
mysqldump: Got error: 2002: "Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2)" when trying to connect
 

itj205

Member
May 16, 2022
8
0
1
AU
cPanel Access Level
Root Administrator
if using live

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

gets following error. try to dump to make back up of dumpsql


[[email protected] ventuno]# mysqldump ventuno_wp685 > dump.sql
mysqldump: Got error: 1932: "Table 'ventuno_wp685.wptb_ac_segments' doesn't exist in engine" when using LOCK TABLES
 

itj205

Member
May 16, 2022
8
0
1
AU
cPanel Access Level
Root Administrator
okay root password was able to update the MYSQL from cpanel...

but even reconfigure /etc/my.conf to back for dir and socket but gets this error when I tried to run MySQL using backup data before I can mysqldump..




[[email protected] _var_lib_mysql_]# systemctl status mariadb.service
● mariadb.service - MariaDB 10.6.7 database server
Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: failed (Result: exit-code) since Wed 2022-05-18 00:59:59 AWST; 37min ago
Docs: man:mariadbd(8)
systemd
Main PID: 1447 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"

May 18 00:59:59 au5.itj.network systemd[1]: Starting MariaDB 10.6.7 database server...
May 18 00:59:59 au5.itj.network mariadbd[1447]: 2022-05-18 0:59:59 0 [Note] /usr/sbin/mariad.....
May 18 00:59:59 au5.itj.network mariadbd[1447]: 2022-05-18 0:59:59 0 [Warning] Can't create ...st
May 18 00:59:59 au5.itj.network mariadbd[1447]: [123B blob data]
May 18 00:59:59 au5.itj.network mariadbd[1447]: 2022-05-18 0:59:59 0 [ERROR] Aborting
May 18 00:59:59 au5.itj.network systemd[1]: mariadb.service: main process exited, code=exite...URE
May 18 00:59:59 au5.itj.network systemd[1]: Failed to start MariaDB 10.6.7 database server.
May 18 00:59:59 au5.itj.network systemd[1]: Unit mariadb.service entered failed state.
May 18 00:59:59 au5.itj.network systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
[[email protected] _var_lib_mysql_]# systemctl start mariadb.service
 

itj205

Member
May 16, 2022
8
0
1
AU
cPanel Access Level
Root Administrator
I see you also have a ticket open about MariaDB being offline. I'm following along on my end now as well.
Seems to be backup directory /disk1/2022-05-10/system/dirs/_var_lib_mysql_/

Need to whole MySQL folder needs to restore.

1. backup current /var/lib/mysql/
2. restore /disk1/2022-05-10/system/dirs/_var_lib_mysql/
3. Then make back up of ventuno_wp685 to .SQL from folder.
4, restore current database
5. restore ventuno_wp685

I need step by step instructions if this is the correct way to resolve the database of 1932 error
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
11,011
1,738
363
cPanel Access Level
Root Administrator
If the database has been damaged, which it appears to be according to the data found in that ticket, you may need to work with a professional MySQL administrator to avoid data loss. While we don't recommend anyone specifically, we have admins that advertise their familiarity with cPanel here: System Administration Services