DirectAdmin is a robust control panel for web hosting that allows you to create and manage databases, users and permissions, and other tasks in MySQL Server with ease. Whenever you create a MySQL database using the user account, the control panel prefixes the name of database with the username. In case you move or rename the DirectAdmin user, it might happen that the database prefix mismatches with the username. Due to this, you may encounter an issue where MySQL doesn’t work or receive errors, like Access denied for user 'da_admin'@'localhost, MySQL service fails to start.
You may also fail to access the MySQL Server if there is corruption in MySQL database table files (especially .idb or .frm files), incorrect my.cnf configuration files, or incorrect da_admin MySQL user credentials. In this article, we'll discuss the solutions to resolve the MySQL service not working issue after moving the DirectAdmin user.
Causes of the MySQL Service not Working after Moving the DirectAdmin User Issue
You may encounter the MySQL service not working issue due to one of the following reasons:
- Corrupted InnoDB table files (idb or .frm).
- User database files are still owned by the old users after migrating/moving the DirectAdmin user.
- Lack of required permissions. For example, user privileges are not updated after move.
- MySQL socket is not starting.
- Ibdata1 file is corrupted.
Troubleshooting Methods to Resolve MySQL not working after Moving DirectAdmin User
Follow the below troubleshooting methods to resolve the MySQL service isn’t starting issue.
Method 1 - Delete Socket Lock Files
The socket file allows client-server communication used for data transmission. The socket lock file ensures that only one MySQL process is using the socket at a time. If the socket file is corrupt, the lock file creates conflict that it is in use, preventing the MySQL server from starting. To check this, locate the socket file defined in the configuration file (/etc/my.cnf) as shown below:
[mysqld]
socket=/var/run/mysql/mysql.sock
If the socket (mysql.sock) file is damaged or misconfigured, then reset it by deleting its conflicting lock files. The socket lock files are located under the data directory [mysqld] datadir section in configuration file. Locate this section and delete the .sock, .pid, or .lock files.
Method 2 - File Permissions or User Privileges not Updated
The issue “MySQL not working after moving the DirectAdmin user” can occur when user databases are renamed or reassigned during the move, but MySQL user privileges are still configured according to the old username. Ensure that you have appropriate user and file permissions. You can run the SHOW GRANTS statement (see the below example) to know if you (the user) have the required roles and privileges.
SHOW GRANTS
[FOR user_or_role
[USING role [, role] ...]]
user_or_role: {
user (see Section 8.2.4, “Specifying Account Names”)
| role (see Section 8.2.5, “Specifying Role Names”.
}
If you don't have the desired permissions, then use the GRANT statement to assign them.
Method 3 - Repair InnoDB Table and ibdata1 Files
MySQL Server may fail to start if the InnoDB table files and associated metadata or the ibdata1 file get corrupted. You can restore the corrupt ibdata1 file from the transaction log file's redo logs.
MySQL Server crash and fail when it tries to access the corrupt InnoDB tables. In such a case, first restart the MySQL Server using InnoDB force recovery. Then, rebuild the InnoDB tables by dumping and reloading them. Here are the steps to do so:
Step 1: Start MySQL Server by Enabling Innodb_Force_Recovery
First, you need to enable the innodb_force_recovery option to access the database for recovery. The default value of innodb_force_recovery is 0. You can incrementally increase it from 0-6 to enable force recovery mode.
To enable innodb_force_recovery to start the MySQL Server, follow the below steps:
- Search for the configuration file (my.cnf). The my.cnf file’s location varies depending on the operating system. In Windows system, the configuration file is located in ‘/etc’ directory. The default path is /etc/mysql/my.cnf.
- Once you found the my.cnf file, go to the [mysqld] section and then insert the below statements:
[mysqld]
Innodb_force_recovery=1
service mysql restart
Note: Avoid selecting innodb_force_recovery value higher than 4. Larger values don’t support dropping and creating of tables. Also, it can cause data loss.
Step 2: Dump and Reload the Data
Once you have enabled innodb_force_recovery, you can start the server in recovery mode. It allows you to access the corrupt tables. Now, follow the below steps to dump and reload the data:
- Dump the table data by using the mysqldump command as given below:
- Next, export all the databases to the dump.sql file by executing the below command:
mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql
- Now, restart the MySQL Server and then use the DROP DATABASE command to drop the database. To use this command, ensure you have all the database privileges.
- If it fails to drop the database, then run the below commands to delete the database manually:
cd /var/lib/mysql
rm -rf db_name
- Next, comment on the following line in [mysqld] to disable the InnoDB force recovery mode:
- Now, save the applied changes to the configuration (my.cnf) file and then restart the MySQL Server.
This will restore the databases. You can check if the restored databases contain the innodb table files and ibdata1 file.
To repair the corrupt InnoDB tables quickly and with complete integrity, you can use Stellar Repair for MySQL. This advanced MySQL repair tool can repair highly corrupted or damaged InnoDB/MyISAM tables. It can precisely recover indexes, partitioned tables, unique keys, foreign keys, and other objects from corrupted MySQL database with complete integrity. This tool supports all versions of MySQL Server, and Windows and Linux operating systems.
Conclusion
After moving or renaming the DirectAdmin user, the MySQL Server may not work due to several reasons. In this article, we have mentioned some effective solutions to resolve the MySQL not working after moving Direct Admin user issue. If corruption in InnoDB tables is the reason behind this issue, then you can enable InnoDB force recovery to dump and reload the tables. However, it may take a lot of time and result in data loss. To prevent data loss and quickly repair the damaged MySQL database, you can use Stellar Repair for MySQL. It can recover all the data from corrupt InnoDB and MyISAM tables with complete precision.
Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing.
-in-MySQL-to-Restore-Corrupted-Databases-1746.jpg)
5 min read
