阿里云备份后恢复至本地 数据库无法启动、登录异常等问题
阿里云物理备份文件恢复到自建数据库
使用backup-my.cnf 无法启动
修改my.cnf
启动失败， log-error文件目录不存在，创建目录并授权后，启动正常
[root@GZYXRDS log]# grep err /etc/my.cnf
log-error = /var/log/mysql/error.log
[root@GZYXRDS log]#
密码问题处理：
修改my.cnf 添加skip-grant-tables 后修改密码问题：
mysql> create user dbadmin@'%' identified by 'password';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决办法：mysql> flush privileges;
mysql> alter user root@'127.0.0.1' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
修改root密码后登录失败
[root@GZYXRDS mysql]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql> select user,host,authentication_string,plugin from user where user='root';
+------+-----------+-----------------------+-------------------------------------------+
| user | host | authentication_string | plugin |
+------+-----------+-----------------------+-------------------------------------------+
| root | 127.0.0.1 | N | *9C4A0C37D2BC94317DA16D4489C54CDB3E7A48D7 |
| root | ::1 | | |
+------+-----------+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
解决办法：
mysql> update user set plugin='mysql_native_password' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set authentication_string=password('password') where user in ('root','dbadmin');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 1
去掉跳过密码参数，重启mysql后数据库登录正常。
