Error connecting to MySQL: Access denied for user: 'da_admin@localhost' (Using password: YES)
When connecting to the MySQL interfaces in DirectAdmin, if this error appears, that would indicated that the "da_admin" user has not been set up correctly.
The very first thing you should check is for the existence of conflicting MySQL configuration files, including:
Some systems have a
/root/.my.cnf file with a preset password. This will override the value set in DA and cause these errors, especially during backups/restores.
If either of these conflicting MySQL configuration files exist, figure out if they or the
/usr/local/directadmin/conf/mysql.conf file contains the correct MySQL da_admin credentials, ensure that the correct credentials are in use in the
/usr/local/directadmin/conf/mysql.conf file, and then rename/remove the conflicting MySQL files.
Use the following command to test the credentials (enter the password when prompted):
mysql -uda_admin -p
If the issue is not related to the existence of these files, proceed with the following to resolve this:
Desired root password:
Desired da_admin password:
1. Make sure the root MySQL password works.
If you know it, skip to #2.
The MySQL root password can be found in the
/usr/local/directadmin/scripts/setup.txt if the file has not been deleted and/or the password hasn't been changed since initial setup. It's under the header "mysql=". If it cannot be found, then mysqld will have to be restarted with the skip-grant-tables option.
For MySQL 5.7 and higher on CentOS/RHEL and Debian/Ubuntu
systemctl stop mysqld systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" systemctl start mysqld
Now reset password for root:
mysql mysql -e "UPDATE mysql.user SET authentication_string=PASSWORD('rootpass') WHERE user='root'; FLUSH PRIVILEGES;"
And restart mysql without skip-grant-tables :
systemctl unset-environment MYSQLD_OPTS systemctl stop mysqld systemctl start mysqld mysql -uroot -p'rootpass' -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootpass';"
For all the other versions of MySQL/MariaDB:
service mysqld stop mysqld_safe --skip-grant-tables &
Reset password with:
mysql mysql -e "UPDATE mysql.user SET password=PASSWORD('rootpass') WHERE user='root'; FLUSH PRIVILEGES;"
And restart normally:
killall -9 mysqld_safe killall -9 mysqld service mysqld start
2. Reset the da_admin MySQL user password.
mysql -uroot -p
Then press enter. You'll be asked for the password. Once in MySQL, type:
ALTER USER 'da_admin'@'localhost' IDENTIFIED BY 'daadminpass'; GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost WITH GRANT OPTION; FLUSH PRIVILEGES; quit
That should set the password for da_admin in MySQL.
3. Make sure it's setup correctly for DA to use.
/usr/local/directadmin/conf/mysql.conf and set
4. Test it out in DirectAdmin.
Check how it goes from DirectAdmin user panel > MySQL Management.
mysql_connect(): Too many connections. Unable to connect to sql server
Mysqld has a limit to the number of connections that it will take at one time.
You can tell mysqld to increase the limit by creating an
my.cnf file, adding an option, then restarting mysqld.
The option is:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
If you get an error that you cannot connect to your mysql.sock file (can also be
/tmp/mysql.sock on Debian), then do the following:
- Check to see if the mysql.sock exists:
ls -la /var/lib/mysql/mysql.sock
- Check to see if it's some other MySQL instance that has moved it somewhere else (possibly from a wrong my.cnf setting):
netstat -lnp | grep mysql
- Check to see if it's mysqld is running:
ps ax | grep mysql
- Restart mysqld to recreate the sock file:
service mysqld stop service mysqld start
- If needed, use a brute force method (as a last resort) by repeating the following commands:
killall -9 mysqld mysqld_safe
Repeat until no mysqld process is running, it will throw an error.
service mysqld start
- Check the following log for clues:
On Debian, it's:
Use the following command to confirm the location of your configured error log:
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "show variables like 'log_error';"
mysqldump 1449: The user specified as a definer ('da_admin'@'%') does not exist when using LOCK TABLES
If you get a backup error similar to:
Error while backing up database user_dbname mysqldump error output: mysqldump: Got error: 1449: The user specified as a definer ('da_admin'@'%') does not exist when using LOCK TABLES
then add the following to your directadmin.conf and restart DA:
Note that this is now an internal default for mysqldump
String 'username_dbuser' is too long for user name (should be no longer than 16)
When restoring your MySQL databases, if you run into this error:
Unable to add user username_dbuser. String 'username_dbuser' is too long for user name (should be no longer than 16) Can't add user username_dbuser to username_dbname: Unable to add user username_dbuser. String 'username_dbuser' is too long for user name (should be no longer than 16)
it means that the username_dbuser values are too long for MySQL.
Although there are settings in the mysql.user and mysql.db tables which control then max length of the username, MySQL clearly states that they cannot be changed: http://dev.mysql.com/doc/refman/5.5/en/user-names.html
"The limit on MySQL user name length is hard-coded in the MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the MySQL database does not work."
The solution (with regards to DA) is to shorten the DA username, so that the total length of the db user is shorter.
There are 3 ways of doing this:
- Change the existing username and restore again from that User's backups folder:
where this path is needed, as it's a username independent restore area.
- Or delete the existing User, rename the backup to a shorter name, and restore again, e.g.,
cd /home/admin/admin_backups mv user.admin.**username**.tar.gz user.admin.**usern**.tar.gz
- Or this last option is more difficult, but will shorten the database name and db usernames, rather than changing the DA username.
- Using this guide, you can extract the current tar.gz backup file.
- In the "backup" directory, each MySQL database will have 3 files, e.g.,
**username_dbname**.conf **username_dbname**.sql **username_dbname**.dump.log
Rename those 3 files to have a shorter _dbname value, so username_dbname is 16 characters, or less.
- Also edit the username_dbname.conf and change any username_dbuser= entries which are more than 16 characters.
- Repack the tar.gz.
If you're seeing the word "collation" in the error, ensure you're restoring onto DA version 1.43.0 or newer, with regards to this change: http://www.directadmin.com/features.php?id=821
Error: 'Out of resources when opening file (Errcode: 24 - Too many open files)'
If you get the following error while creating a backup:
Error while backing up database USER_NAME mysqldump error output: mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces mysqldump: Couldn't execute 'show fields from `NAME_accesslog`': Out of resources when opening file '/tmp/#sql_390d_0.MYD' (Errcode: 24 - Too many open files) (23)
150707 0:15:49 [ERROR] Error in accept: Too many open files
check this forum thread for solutions:
Some solutions below:
/etc/init.d/mysqldand add this to the top, after
ulimit -HSn 1024 ulimit -HSn 32768 ulimit -HSn 1024000
which should force it to try and set it as high as it can go. If one of the value is too high, delete that line, or lower the value.
For Operating Systems with systemd (CentOS7+), edit the service file, (for example, MariaDB's service file would be located here:
/usr/lib/systemd/system/mariadb.service) and set the number of files limit to a higher value like so:
/etc/my.cnf. Under the [mysqld] section, if you have a open_files_limit variable, increase it, e.g.,
then restart mysqld:
systemctl daemon-reload service mysqld restart
- Confirm in MySQL itself by checking the open_files_limit variable to ensure it's increased, by running the SQL query:
show global variables like 'open%';
InnoDB: Warning: io_setup() failed with EAGAIN.
If mysqld won't start up, and you check your MySQL error log, e.g.,
cd /var/lib/mysql #"cd /home/mysql" for Debian tail -n 200 `hostname`.err
and you find this error somewhere in it:
131201 19:22:27 InnoDB: Using Linux native AIO 131201 19:22:27 InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up. InnoDB: Warning: io_setup() attempt 1 failed. InnoDB: Warning: io_setup() attempt 2 failed. InnoDB: Warning: io_setup() attempt 3 failed. InnoDB: Warning: io_setup() attempt 4 failed. InnoDB: Warning: io_setup() attempt 5 failed. 131201 19:22:30 InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts. InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf 131201 19:22:30 InnoDB: Fatal error: cannot initialize AIO sub-system
then the solution would be to add:
innodb_use_native_aio = 0
to the [mysqld] section of your
If this is** a fresh install **(no data yet), you might need to re-install MySQL with the my.cnf setting in place.
cd /usr/local/directadmin/scripts ./mysql.sh pass1 da_admin pass2
Where pass1 and pass2 can be set to any passwords you want. pass1 will be the root mysql password, and pass2 will be the da_admin MySQL password.
Another reported solution is to increase your kernel setting:
I have a corrupted ibdata1 and related innodb files, I need to rebuild them.
This is never really a good position to be in, but there are ways of rebuilding these files into a clean slate. Note, that we recommend using the innodb_file_per_table in the [mysqld] section of your
my.cnf from install time. Changing it after databases already exist could break things and force you to use this guide. On most operating systems innodb_file_per_table is set to “on” by default.
If you want to change to use
innodb_file_per_table, then you'd need to use this guide.
It will essentially dump all databases to sql files, drop all databases, stop mysqld, delete the ib* data files, and restart mysqld and restore the sql files.
- Before anything else, ensure you have full .sql backups of all of your data. It's also recommended to have a copy of the MySQL data directory, so you can copy it back if things go south.
CustomBuild can make the plain-text sql files for you. Run the following, but only if your data itself is still valid. Do not run this if you need to restore your backups to get non-corrupted data:
cd /usr/local/directadmin/custombuild ./build update ./build set mysql_backup yes ./build mysql_backup
Then confirm that the sql files have been created here:
ls -la /usr/local/directadmin/custombuild/mysql_backups
If you do not have any valid plain .sql file backups, do not proceed. This guide only works if if you have something to restore from.
Note: The innodb_force_recovery option may let you get mysqld running, and possibly even repair it for you in a quick/easy manner.
- Next, we'll shut off mysqld to make a data directory backup, which you'll want to do either way (corrupted or not).
perl -pi -e 's/mysqld=ON/mysqld=OFF/' /usr/local/directadmin/data/admin/services.status service mysqld stop
then copy the directory.
cd /var/lib cp -Rp mysql mysql.innodb.backup
- Try to surgically fix or remove the offending database:
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf database_name < /usr/local/directadmin/custombuild/mysql_backups/database_name.sql
Make sure to replace
database_name with the name of the database in question.
Hopefully this will work for you, and you can stop without a full rebuild. If this fixes the problem, stop here, do not continue.
At this point, assuming you have safe backups of all of your data, you can move to the point of no return.
Start mysqld again, and drop all databases, except these three: mysql, performance_schema, information_schema.
To do this, you can use /phpMyAdmin via Apache to make things easier and quicker.
Use the da_admin user/pass from:
Drop all, but those 3 databases.
- Now that they're gone, we can clean up the ib* data. Stop mysqld again, delete the ib* files, and start mysqld. If you are changing to use the
innodb_file_per_tableoption, make sure it's in your
/etc/my.cnfat this point.
cd /var/lib/mysql /etc/init.d/mysqld stop rm -f ib* /etc/init.d/mysqld start perl -pi -e 's/mysqld=OFF/mysqld=ON/' /usr/local/directadmin/data/admin/services.status
This will rebuild the ibdata1 and innodb log files, goodbye corruption.
- Last step is to restore your sql data files. Because the MySQL database was left, the users should all still be there (its tables all use MyISAM, thus not affected by innodb corruption).
Assuming your sql files are in the path above, then you should be able to run the following script:
cd /usr/local/directadmin/custombuild/mysql_backups wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh chmod 755 restore_sql_files.sh ./restore_sql_files.sh
This will automatically skip the MySQL and schema databases, and restore everything else.
Error inserting host: Field 'authentication_string' doesn't have a default value
Update January 25, 2018:
An actual fix has been added to check for, and explicitly set the blank authentication_string value when adding an access host:
mysqldump error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLES
Some types of database corruption can cause the data to be lost, but will leave behind enough information to rebuild the empty table structure.
Only use this guide if you've given up trying to recover the table contents, and just want to fix the stable structure.
Let's say that you get the above error for User database , and table throwing the backup error:
mysqldump error output: mysqldump: Got error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLES
- Check to see if there are any other data files, or if it's just the .frm file:
cd "/var/lib/mysql/user_db" ls -la "table.*"
If it's just the table.frm file, then the rest of the data is likely lost, but you may be able to rebuild the table.
- For that, we need to read the .frm file. The mysqlfrm tool is required for that, e.g.,
yum install mysql-utilities
mysqlfrm --diagnostic "table.frm"
which might output the full CREATE TABLE syntax. Save this somewhere safe, until the end of of the last ; character. Ignore any # lines, as they're just comments.
Note, if you see "CHARACTER SET ", you can either delete those 3 words, or change to the correct charset, if you happen to know what it should be set to.
- Now, we need to remove the broken table. Login to /phpMyAdmin and run the query:
DROP TABLE user_db.table
- Lastly, run the CREATE TABLE query from above, to rebuild the table.
Although** this doesn't rebuild the data**, it will at least get the table back, which can be fine in some cases (if you're lucky, like a temp cache table).
MariaDB: Aria engine is not enabled or did not start.
If MariaDB (mysqld) isn't starting, and you're getting this error:
Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables
Make a full backup of your
Try renaming the
aria_log_controlto something else, e.g.,
cd /var/lib/mysql mv aria_log_control aria_log_control.moved
- Restart mysqld
service mysqld restart
It is safe to delete this file, but only if MariaDB is not running:
MySQL: Number of Users shows -1, or one less than it should
For a User, the "MySQL Management" page listing the databases has a column called "Number of Users" which represents the number of MySQL accounts set up to use it. When you click the DB, you can see these accounts.
However, DA does not show the fact that the DA system User account (that User's DA login name) is also able to access this database (as it's highly discouraged to use it in scripts, etc.)
So if there is 1 username_dbuser on username_db, there is also username on username_db. So the true count is 2. When DA counts the Users in mysql, it subtracts 1 from this count to show the total number in the "Number of Users" column.
DA User Login: DB Name: username_
If you're seeing a total count of -1 on a given database, this would mean that:
There is no usernamedbuser on usernamedb
There is no username on username_db
The solution is to simply add the username account onto username_db.
To do this, we need to login to /phpMyAdmin.
Login to /phpMyAdmin with the da_admin user/pass found in
Click the "SQL" tab
Type in the following query:
GRANT ALL PRIVILEGES ON `username_db`.* TO 'username'@localhost
Still in the SQL tab, run the query:
Note: if you have multiple access hosts on this database, you would need to either repeat the above with that value instead of localhost, or just delete and re-add those access hosts through DirectAdmin.
Running the mysql.sh is not creating data in /var/lib/mysql
If you're trying to re-install MySQL/MariaDB, and are starting from scratch with the
/usr/local/directadmin/scripts/mysql.sh, but it's not creating the mysql tables + data, this guide may be able to help. If your solution is not listed here, please create a ticket for DirectAdmin Support, so we can investigate and add it here.
Check your output, and see if any of it matches up with the scenarios below:
- Within the output:
Data needs to be created in /var/lib/mysql/mysql ... Neither host 'server.host.com' nor 'localhost' could be looked up with '/usr/sbin/resolveip' Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option
We've encountered this error before, even though the
/etc/hosts did work correctly. Solution to this one is to edit the
mysql.sh and add --force to the end of the mysql_install_db line, making it look like this:
where the --force option simply skips those DNS checks.
DirectAdmin already makes sure that localhost exists in the
/etc/hosts, so we're not concerned about these checks.
Note: DirectAdmin 1.56.0 and up will use --force by default, so you'd only need to make this edit for older versions.
MariaDb 10.4: Deleting Databases is slow / causes timeout
As of version 10.4, MariaDB has replaced the
mysql.user table with
mysql.globa_priv table, and
mysql.user becomes a view:
For the most part, this really shouldn't matter, as DA would be using the mysql_use_new_user_methods=1 (for ALTER USER, etc, instead of direct
We had a report of slow response time for deleting database, where DA does some fancy queries to figure out if a User is on mysql databases or not, so it would only delete the User if it's only on the one being deleted.
The short of it is that DA was looking at a few rows that were not specifically indexes, thus the lookup would have been done in a linear fashion (I believe), hence very slow response time, as this server had 45,000 database accounts (yes this is a lot, but there is no limit).
To optimize things, one can added 3 new index files on the related tables:
Where, I believe the biggest gain was mysql.db:db.
To add a simple index to a database, login to /phpMyAdmin with your da_admin account (
/usr/local/directadmin/conf/mysql.conf), and go to the "mysql" database. Click the table you'd like (e.g.,
global_priv), and go to the "Structure" tab.
About 1/2 way down there is a line that says:
Create an index on  columns [GO]
Click that GO button with 1 in the form.
For "Index Choice" change "UNIQUE" to be "INDEX", select the column of your choice (e.g., 'user'), and click "Go".
An Index is a lookup table, so MariaDB can find a specific value in that row more quickly.
If there is no index for a row that is heavily used, performance can be affected.
Note that these tables already do have "PRIMARY" indexes combining sometimes 3 different columns, but if DA is only referencing just 1 column, then you should see performance gains by giving that one column it's own index.
The before and after results were fairly clear it worked:
MariaDB [mysql]> select count(*) from user; +----------+ | count(*) | +----------+ | 45196 | +----------+ 1 row in set (4 min 48.356 sec)
MariaDB [mysql]> select count(*) from user; +----------+ | count(*) | +----------+ | 45201 | +----------+ 1 row in set (0.001 sec)
Perhaps this is something the MariaDB team could look at in the future, but for now this seems to work.
Similar report on MariaDB 10.3 which does not use a view, solution was to index:
The server requested authentication method unknown to the client [caching_sha2_password]
MySQL 8.0 supports a new authentication plugin called caching_sha2_password.
For the most part, this may cause confusion in several areas, including the one-click phpMyAdmin login / SSO.
If you login to phpMyAdmin as da_admin and check your
mysql.user table, if you have any Users created by DA, including the SSO logins that start with "da_sso_" which have the "plugin" column set to
caching_sha2_password, then the work-around is** to force the default back to
mysql_native_password ** .
my.cnf file and in the [mysqld] section, add code
and restart mysqld.
We'll continue to monitor the prevalence of this issue, and if it's widespread, then we'll include:
IDENTIFIED WITH mysql_native_password BY 'password'
in the CREATE USER section, when applicable.
MySQL queries cause high-load
First thing you should do when suspecting MySQL is the reason of high load is to check MySQL queries, you may do it by executing the following line from the terminal:
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "show full processlist;" -sss
In case DirectAdmin triggers a high-load notice to the Admin message system, SHOW FULL PROCESSLIST; output will be included in the message. The output is only generated if:
- MySQL/MariaDB is running.
- MySQL/MariaDB is running localy (as the queries on a remote mysqld server are not likely relevant in the output). You may verify it via the
/usr/local/directadmin/conf/mysql.conffile. It should have
host=localhostor no host at all since the default setting is 'localhost'.