Knowledge base

Lost Database Server Password

If you lose your password, you can create a new one using the following commands:

Stop the MySQL using the command:

# service mysql stop - ОС Centos 6/Debian/Ubuntu

# systemctl stop mariadb - ОС Centos 7

or

# systemctl stop mysqld

Run MySQL without access privilege using the command:

# mysqld_safe --skip-grant-tables &

Name the user as root using the command:

# mysql -uroot

Change the password:

# use mysql;

# UPDATE user SET Password=PASSWORD("mypassword") WHERE User='root';

# FLUSH PRIVILEGES;

Restart the database server with access privilege using the command:

# service mysqld restart - ОС Centos 6/Debian/Ubuntu

# systemctl restart mariadb - ОС Centos 7

or

# systemctl restart mysqld

Log onto the MySQL server with the new password:

# mysql -uroot -p mypassword

How Do You View the List of MySQL Server Errors?

For a list of database server errors, check the server’s log files. Each operating system and file system has the log files located in a different directory. To determine the location of the MySQL log files on your server, connect to the server via the console (for example, Putty) and run the following command to locate the my.cnf file:

find / -name ‘my.cnf’

The result of this command will be the pathway that will help you locate the target file.

Open the located file with any editor, e.g vi, and locate the lines starting with "log" or "log-error"

vi /etc/my.cnf

Using the editor, open the log file via the path and check the errors.

If the my.cnf file does not contain lines pointing to the log files, this means that the error check (logging) is excluded. Enable this feature by adding the line in my.cnf:

[mysqld]

log-error=/var/log/mysql.log

where mysql.log is a new file for recording errors.

Create the file and assign privileges using the commands:

touch /var/log/mysql.log

chown mysql:mysql /var/log/mysql*

chmod 640 /var/log/mysql*

If necessary, the log file can be viewed in the background :

tail –f /var/log/mysql.log &

Potential Errors in the  Log File and their Solutions

Log file or browser shows error:

This message indicates that the damaged table appeared in one of the databases that can be recovered. To accomplish this, connect to the server via the console and run the following command to check the tables’ integrity:

mysqlcheck --repair --analyze --optimize --all-databases -u<USER> –p<PASSWORD>

In case of an error, run multiple commands:

mysqlcheck --repair --all-databases -u<USER> –p<PASSWORD>

mysqlcheck --analyze --all-databases -u<USER> –p<PASSWORD>

mysqlcheck --optimize --all-databases -u<USER> –p<PASSWORD>

where <USER> is the database user, <PASSWORD> is the password.

If you know the specific database that is corrupted, run the following command:

mysqlcheck --repair --analyze --optimize <DB> -u<USER> -p<PASSWORD>

where

<USER> is the database user, <PASSWORD> is the password, <DB> is the corrupted database name.

Type error appears:

This messages states that for user_xxx, access to the database or any of its tables is denied.

Enter the ISPmanager, go to "Databases" - "Database Servers” button, and double-click on the name of the database server to open its settings.

Verify that the values in the “Username” and “Password” fields match those found in the website settings for connection to the database in question.

"Failed to connect to the database" type error appears on the website

In the case of a failure to connect to the database, multiple error message types can be displayed. They are as follows:

Make sure that the MySQL database server is running. Enter the ISPmanager and go to "Settings" -> "Software configuration" and check that the User Options list shows a green lightbulb on the "DBMS MySQL Server" line. If the lightbulb is “off”, select the line and click "Install" on the toolbar.

If the problem still persists, connect to the server via the console and restart the MySQL using the following command:

/etc/init.d/mysqld restart    - Centos 6, Debian

systemctl restart mysqld -  Centos 7

Check that the server has started correctly by running the command to display the MySQL task list:

 ps axuw | grep mysql

If the result shows no tasks, MySQL failed to run.

Unable to Start MySQL

Try running MySQL via the ISPmanager control panel. If this fails, connect to the server via SSH and attempt running MySQL using the console command:

/etc/init.d/mysqld restart     -  Centos 6, Debian

systemctl restart mariadb       -  Centos 7

If MySQL will not run via the console, you will receive a message about the error type:

Check the disk space using command

df -h

Command

du –hs /*

displays the space occupied by each directory.

If disk space is low, open up more space by clearing the log files from MySQL and other services.

Restart MySQL via the console using the commands above.

If the problem persists, look closely at the MySQL log file entries that start with [ERROR]. For example, the “Error while setting value '--read_buffer_size=256K' to 'sort_buffer_size'” entry means that the MySQL configuration file has the misspelled sort_buffer_size directive.