ubuntu mysql 二三事

2019独角兽企业重金招聘Python工程师标准>>>

mysql默认装在?
登录后通过命令
show variables like '%dir%'查看

mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name                           | Value                      |
+-----------------------------------------+----------------------------+
| basedir                                 | /usr                       |
| binlog_direct_non_transactional_updates | OFF                        |
| character_sets_dir                      | /usr/share/mysql/charsets/ |
| datadir                                 | /var/lib/mysql/            |
| innodb_data_home_dir                    |                            |
| innodb_log_group_home_dir               | ./                         |
| innodb_max_dirty_pages_pct              | 75                         |
| lc_messages_dir                         | /usr/share/mysql/          |
| plugin_dir                              | /usr/lib/mysql/plugin/     |
| slave_load_tmpdir                       | /tmp                       |
| tmpdir                                  | /tmp                       |
+-----------------------------------------+----------------------------+
11 rows in set (0.00 sec)

mysql的配置文件在?

/etc/mysql/my.cnf

如何设置让外部可以访问？ (转自：http://blog.chinaunix.net/uid-7934175-id-4289801.html）
mysql 的root账户,我在连接时通常用的是localhost或127.0.0.1,公司的测试服务器上的mysql也是localhost所以我想访问无法访问,测试暂停.

解决方法如下:

1、修改表,登录mysql数据库,切换到mysql数据库,使用sql语句查看
"select host,user from user ;"
mysql>use mysql; 
mysql>update user set host = '%' where user ='root'; 
mysql>select host, user from user; 
mysql>flush privileges; 

注意:最后一句很重要,目的是使修改生效.如果没有写,则还是不能进行远程连接.

2、授权用户,你想root使用密码从任何主机连接到mysql服务器
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql的密码' WITH GRANT OPTION;
mysql>flush privileges;

如果你想允许用户root从ip为192.168.12.16的主机连接到mysql服务器 
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.16'   IDENTIFIED BY '123456' WITH GRANT OPTION;

cyper实战：
之前

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| 127.0.0.1 | root             |
| ::1       | root             |
| localhost | debian-sys-maint |
| localhost | phpmyadmin       |
| localhost | root             |
| zerorun   | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

执行grant命令后查看变化：

mysql> use mysql;
Database changed
mysql> grant all privileges on *.* to 'root'@'192.168.1.100' identified by '***' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+---------------+------------------+
| host          | user             |
+---------------+------------------+
| 127.0.0.1     | root             |
| 192.168.1.100 | root             |
| ::1           | root             |
| localhost     | debian-sys-maint |
| localhost     | phpmyadmin       |
| localhost     | root             |
| zerorun       | root             |
+---------------+------------------+
7 rows in set (0.00 sec)

可见这条命令其实就是在user表中新增了一条记录。

结果外部还是连不上，出现了和这里一样的问题：http://stackoverflow.com/questions/25156018/failed-to-connect-remote-mysql-server
解决方法是将/etc/mysql/my.cnf的
bind-address = 127.0.0.1 注释掉。

设置utf8编码
见这里：http://my.oschina.net/sldk/blog/266268

用php读写mysql开始乱码了（之前不乱），解决办法(参考：http://www.cnblogs.com/rickyNote/archive/2012/04/09/2438704.html）
在conn.php中

$conn = mysql_connect($db_host, $db_user, $db_pwd);
if (!$conn)
{
    die('Could not connect: ' . mysql_error());
}
mysql_query("set character set 'utf8'"); //read
mysql_query("set names 'utf8'"); //write
mysql_select_db("crazy_blog", $conn);

好了，乱码问题得到解决。

第一次安装后root密码为空，修改密码：
mysqladmin -uroot password yourpasshere

转载于:https://my.oschina.net/uniquejava/blog/357380