Mysql 问题集锦

1. mysql字符乱码

如要进行数据库定制，可到’/usr/local/mysql/support-files/‘文件夹底下，把里面的任一个.cnf配置文件复制到/etc/目录底下并修改文件名称为my.cnf。

sudo cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

sudo vi /etc/my.cnf

[mysqld]部分加入：

character-set-server=utf8

2. mysql无法删除数据库

错误代码

DROP DATABASE db_name;
ERROR 1010 (HY000): Error dropping database (can't rmdir './db_name', errno: 66)

解决办法

1) Find the database directory: mysql -e "select @@datadir" -> /usr/local/mysql/data/

2) Go to DB folder: cd /usr/local/mysql/data/

3) Delete DB folder of DB with issue (in this case: sudo rm -rf DB_NAME)

参考链接http://stackoverflow.com/questions/16527054/drop-database-return-error-dropping-database-errno-66-in-mysql

3. Can’t connect to MySQL server on ‘127.0.0.1’ (61)

原因 数据库未启动

4. java.sql.SQLException: Access denied for user ‘root’@‘localhost’ (using password: YES)

解决办法 把数据库连接的localhost改为127.0.0.1

4. 开启远程访问数据库

首先访问下3306的状态

maizk@x02:~$ netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

3306只对本机开放

修改/etc/mysql/my.cnf

//注销下面这句话
bind-address = 127.0.0.1

重启mysql

//linux
sudo /etc/init.d/mysql restart

再查看下3306的状态

maizk@x02:/etc/mysql$ netstat -an | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:53766         127.0.0.1:3306          TIME_WAIT  
tcp        0      0 127.0.0.1:53765         127.0.0.1:3306          TIME_WAIT  
tcp        0      0 127.0.0.1:53767         127.0.0.1:3306          TIME_WAIT

ok 3306对外开放了

远程访问下试试

K:~ maizhikun$ /usr/local/mysql/bin/mysql -u root -h 192.168.0.11 -p 
Enter password: 
ERROR 1130 (HY000): Host '192.168.0.129' is not allowed to connect to this MySQL server

给予IP用户权限

mysql> grant all on *.* to user_name@"%" identified by "user_password"; 　

*.*代表任意数据库的任意表,%代表任何IP,

但是发现phpmyadmin无法登陆了提示2002错误,phpmyadmin应该默认用localhost登陆的,但是我们要改成127.0.0.1

vim /usr/share/phpmyadmin/libraries/config.default.php

$cfg['Servers'][$i]['host'] = 'localhost';

//修改为

$cfg['Servers'][$i]['host'] = '127.0.0.1';

参考链接http://www.cnblogs.com/sunzhenchao/archive/2013/01/16/2863160.html

ok,完事

主要参考这个,还有数据库拒绝外界访问设置参考链接http://amon.org/how-to-manage-port-3306-for-mysql-connection.html

sql

Comments