Elastic Compute Service:MySQL deployment FAQ

Password for a regular user

1. Query the Host value of the target user.

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

2. Modify the password based on the Host value. If you have multiple hosts, specify each host individually.

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

Root user password

1. Stop the MySQL service.

   Alibaba Cloud Linux or CentOS

   sudo systemctl stop mysqld

   Ubuntu or Debian

   sudo systemctl stop mysql

2. Start MySQL in temporary mode. Record the process ID (PID) for later termination.

   Note

   • --skip-grant-tables: Skip permission verification, which allows logon without a password.

   • &: Run the MySQL process in the background. The terminal returns a PID For example, when [1] 1234 is returned, 1234 indicates the PID.

   sudo -u mysql mysqld --skip-grant-tables &

3. Reload the authorization tables on the server and modify the root password. Replace <password> with the password you want to use. The password must comply with the following requirements: It must contain at least one uppercase letter, one lowercase letter, one digit, and one special character and be at least 8 characters in length.

   mysql -uroot -e"FLUSH PRIVILEGES;" -e"ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';"

   If the Your password does not satisfy the current policy requirements error message is returned, your password does not meet the policy requirements. You need to close the MySQL process, start MySQL by skipping permission verification, and then run the password modification command.

4. Terminate the MySQL process.

   kill <process_ID>

5. Start the MySQL service based on the operating systems.

   Alibaba Cloud Linux or CentOS

   sudo systemctl start mysqld

   Ubuntu or Debian

   sudo systemctl start mysql

6. Enter the new password. If you can enter the MySQL CLI, the password is changed as expected.

   mysql -u root -p

How do I change the MySQL data directory?

To change the MySQL data directory, perform the following steps:

1. Stop MySQL. The command varies based on the operating system.

   Alibaba Cloud Linux or CentOS

   sudo systemctl stop mysqld

   Ubuntu or Debian

   sudo systemctl stop mysql

2. Open the my.cnf configuration file and change the datadir parameter in the file to the path of the new MySQL data directory. The file is usually stored in the /etc/my.cnf directory.

   datadir=/new/data/directory

3. Copy all files and folders in the original data directory to the new one.

   sudo cp -aR /old/data/directory/* /new/data/directory/

4. Start MySQL. The command varies based on the operating system.

   Alibaba Cloud Linux and CentOS

   sudo systemctl start mysqld

   Ubuntu and Debian

   sudo systemctl start mysql

What do I do if I cannot start MySQL due to the "Permission denied" error?

To resolve the issue, perform the following steps:

1. Check the error log file of MySQL to find the error message.

   Note

   By default, the file is usually stored in the /var/log/mysqld.log or /var/log/mysql/error.log directory.

   [ERROR] [MY-010092] [Server] Can't start server: can't create PID file: Permission denied

2. Run the following commands to check whether residual mysqld processes exist and terminate the residual processes:

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

3. Start MySQL. The command varies based on the operating system.

   Alibaba Cloud Linux or CentOS

   sudo systemctl start mysqld

   Ubuntu or Debian

   sudo systemctl start mysql