日期:2014-05-16  浏览次数:20896 次

mysql密码恢复及远程访问mysql问题
进入mysql安装目录执行下面命令
# /usr/local/mysql/bin/mysqld_safe  --skip-grant-tables

windows 下
my.ini文件加入 skip-grant-tables 重启mysql服务

执行后出现:
100128 21:01:24 mysqld_safe Logging to '/var/lib/mysql/shanggu.com.err'.
100128 21:01:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
此时窗口不能动,需要克隆回话打开新窗口执行mysql
如果不能执行则进入mysql安装目录后再执行如下:
# /usr/local/mysql/bin/mysql
这时就进入到mysql了
mysql> use mysql
mysql> update user set password=password("jasonterry@2006") where user="liujl";
mysql> flush privileges;
mysql> exit;

进入mysql命令:
# /usr/local/mysql/bin/mysql -u root -p
Enter password:输入修改后的密码shanggu@root

========================下面是linux下添加mysql用户==============================

构建web应用时,需要远程访问数据库。

我们不推荐使用root来登录,而是添加新用户。

1)以root用户登录
#mysql -u root -p
insert into mysql.user (host,user,password) values('%','ssouser',PASSWORD('sso'));
mysql> insert into mysql.user (host,user,password,ssl_cipher,x509_issuer,x509_su
bject,authentication_string) values('%','ssouser',PASSWORD('sso'),'','','','');
grant all on ssotest.* to ssouser@'%' identified by 'sso';
grant all on shoptest.* to ssouser@'%' identified by 'sso';
grant all on cetvoss8.* to ssouser@'%' identified by 'sso';
grant all on cetv.* to root@'10.1.8.36' identified by 'founder123';

update mysql.user set password=password('Founder123') where user='root'
2)插入新用户,host:%;user:jason;pssword:jason
mysql>insert into mysql.user (host,user,password) values('%','ljlleo',PASSWORD('111111'));

3)更新密码
mysql>update mysql.user set password=password('liujiafu@root') where user='liujiafu'

4)授予权限
mysql>grant all on cetvoss8.* to cetvoss@'192.168.54.3' identified by 'cetvoss';
其中neuzjs为数据库名;

5)刷新权限
mysql>flush privileges;

insert into mysql.user (host,user,password) values('%','sg123',PASSWORD('shanggu@root'));
grant all on shanggu.* to sg123@'%' identified by 'shanggu@root';

发现MySQL远程连接的时候,报1042-Can't get hostname for your address错误,而授权工作已经于服务器上做好。

解决方法如下:

编辑my.ini
在[mysqld]节点下新增或修改如下两行行
skip-name-resolve #忽略主机名的方式访问
lower_case_table_names=1 #忽略数据库表名大小写

重启mysql服务,问题得到解决。