Common MySQL deployment issues - Elastic Compute Service

Forgotten MySQL user passwords

If you forget the password for a non-root user, you can use the root user to change it directly. If you forget the root user password, you must stop the MySQL service and restart it in a special mode that bypasses permission checks. This allows you to reset the password.

Forgotten password for a non-root user

Query the Host value of the target user.

# Replace with the actual username
mysql -uroot -p -e "SELECT Host FROM mysql.user WHERE User = 'target_username';"

Change the password based on the Host value. If the user has multiple Host values, you must run the command for each one.

# Replace with the actual username, password, and Host
mysql -uroot -p -e "ALTER USER 'target_username'@'target_host' IDENTIFIED BY 'new_password'; FLUSH PRIVILEGES;"

Forgotten root user password

Important

Resetting the root user password requires stopping the MySQL service.

To stop the MySQL service, run the following command.
Alibaba Cloud Linux and CentOS
```
sudo systemctl stop mysqld
```
Ubuntu and Debian
```
sudo systemctl stop mysql
```
Start MySQL in a temporary mode and note the process ID. You will need this ID to terminate the process later.
Note
- --skip-grant-tables: Bypasses permission validation and allows you to log in without a password.
- &: Runs the MySQL process in the background. The terminal returns a process ID (PID). For example, in the output [1] 1234, the PID is 1234.
```
sudo -u mysql mysqld --skip-grant-tables &
```
Reload the authorization tables and change the root password. Replace <password> with your new password. The password must comply with the current password policy: at least 8 characters long, containing at least one uppercase letter, one lowercase letter, one digit, and one special character.
```
mysql -uroot -e"FLUSH PRIVILEGES;" -e"ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';"
```
If you receive the error Your password does not satisfy the current policy requirements, your password is not compliant. You must terminate the MySQL process, restart it in the permission-bypass mode, and rerun the command with a compliant password.
Terminate the MySQL process.
```
kill <process ID>
```
To start the MySQL service, run the following command.
Alibaba Cloud Linux and CentOS
```
sudo systemctl start mysqld
```
Ubuntu and Debian
```
sudo systemctl start mysql
```
Log in with the new password. If you can access the MySQL command line, the password reset was successful.
```
mysql -u root -p
```

Changing the MySQL data directory

To stop the MySQL service, run the following command.
Alibaba Cloud Linux and CentOS
```
sudo systemctl stop mysqld
```
Ubuntu and Debian
```
sudo systemctl stop mysql
```
In the my.cnf configuration file (typically located at /etc/my.cnf), change the datadir value to the path of the new data directory.
```
datadir=/new/data/directory
```
Copy all files and directories from the original data directory to the new data directory.
```
sudo cp -aR /old/data/directory/* /new/data/directory/
```
To start the MySQL service, run the following command.
Alibaba Cloud Linux and CentOS
```
sudo systemctl start mysqld
```
Ubuntu and Debian
```
sudo systemctl start mysql
```

MySQL startup error: Permission denied

Check the MySQL error.log file to locate the specific error message.

Note
The default location for this file is typically /var/log/mysqld.log or /var/log/mysql/error.log.
```
[ERROR] [MY-010092] [Server] Can't start server: can't create PID file: Permission denied
```

Find and terminate any lingering mysqld processes.

sudo ps aux | grep mysqld
sudo kill -9 <process ID>

To start the MySQL service, run the following command.
Alibaba Cloud Linux and CentOS
```
sudo systemctl start mysqld
```
Ubuntu and Debian
```
sudo systemctl start mysql
```

MySQL startup error: File './binlog.index' not found

Check the MySQL error.log file to locate the specific error message.

Note
The default location for this file is typically /var/log/mysqld.log or /var/log/mysql/error.log.
```
mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
```

This error is often caused by incorrect file ownership in the MySQL data directory. Run the following command to check the ownership.

Note

The default data directory is typically /var/lib/mysql. If you use a custom data directory, modify the command accordingly.

sudo ls -l /var/lib/mysql

-rw-r-----  1  root root        56 Mar 19 15:51   auto.cnf
-rw-r-----  1  root root      1551 Mar 19 15:51   binlog.000001
-rw-r-----  1  root root        16 Mar 19 15:51   binlog.index
-rw-------  1  root root      1680 Mar 19 15:51   ca-key.pem
-rw-r--r--  1  root root      1108 Mar 19 15:51   ca.pem
-rw-r--r--  1  root root      1108 Mar 19 15:51   client-cert.pem

Run the following command to assign the correct ownership to the mysql user and group.
```
sudo chown -R mysql:mysql <data_directory>
```
To start the MySQL service, run the following command.
Alibaba Cloud Linux and CentOS
```
sudo systemctl start mysqld
```
Ubuntu and Debian
```
sudo systemctl start mysql
```

Forgotten MySQL user passwords

Forgotten password for a non-root user

Forgotten root user password

Alibaba Cloud Linux and CentOS

Ubuntu and Debian

Alibaba Cloud Linux and CentOS

Ubuntu and Debian

Changing the MySQL data directory

Alibaba Cloud Linux and CentOS

Ubuntu and Debian

Alibaba Cloud Linux and CentOS

Ubuntu and Debian

MySQL startup error: Permission denied

Alibaba Cloud Linux and CentOS

Ubuntu and Debian

MySQL startup error: File './binlog.index' not found

Alibaba Cloud Linux and CentOS

Ubuntu and Debian