Mysql实战常见问题汇总

1、Mysql批量导入sql

(1)选择数据库

mysql>use abc;

(2)设置数据库编码

mysql>set names utf8;

(3)导入数据(注意sql文件的路径)

mysql>source /home/abc/abc.sql;


2、Mysql导出为sql(导出数据和表结构)

mysqldump -u用户名 -p 数据库名 > 数据库名.sql


#/usr/local/mysql/bin/mysqldump -uroot -p abc > abc.sql

1

敲回车后会提示输入密码


或者通过navicat工具导出。


3、导出结果数据

select * from ly_info where enckeyword = "0"  into outfile '/tmp/ly_insert.txt';

1

将结果转为sql语句,参考:

http://blog.csdn.net/laoyang360/article/details/53236018


4、Mysql ut8格式修改,目的:防止出现乱码。

最简单的完美修改方法,修改mysql的my.cnf文件中的字符集键值(注意配置的字段细节):


1)、在[client]字段里加入default-character-set=utf8,

如下:


[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

default-character-set=utf8

1

2

3

4

2)、在[mysqld]字段里加入character-set-server=utf8,

如下:


[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

character-set-server=utf8

1

2

3

4

3)、在[mysql]字段里加入default-character-set=utf8,

如下:


[mysql]

no-auto-rehash

default-character-set=utf8

1

2

3

验证方式如下:

使用SHOW VARIABLES LIKE ‘character%’;查看,发现数据库编码全已改成utf8。


+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

1

2

3

4

5

6

7

8

9

10

11

12

4)如果上面的都修改了还乱码,那剩下问题就一定在connection连接层上。

解决方法是在发送查询前执行一下下面这句(直接写在SQL文件的最前面):

SET NAMES ‘utf8’;


它相当于下面的三句指令:


SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

1

2

3

5、mysql远程windows授权命令

错误:Access denied for user ‘root@IP’ (using password: YES)

Mysql实战常见问题汇总

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password@!' WITH GRANT OPTION;

1

6、查询、更新操作

SELECT count(1) AS num, 'qd_last' AS info FROM `qd_info` WHERE enckeyword = "0";

update ya_info t1 , ls_info t2 set t1.id_new = t2.id_new  where t1._id = t2._id;

alter table qd_info add `word` varchar(256) NOT NULL DEFAULT '0', add `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

1

2

3

7、查看死锁表死锁

show processlist ;

mysql> show processlist;

+--------+------+--------------------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------+--------------------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+

| 219733 | root | 10.200.1.137:38012 | test | Sleep | 1659 | | NULL |

| 219749 | root | 10.200.1.137:38071 | test | Query | 85 | Locked | SELECT count(1) AS num, 't_last' AS info FROM `com_info` WHERE enckeyword = "0"

1

2

3

4

5

6

7

解决方法: 关闭用不到的连接


mysql> kill 219733 ;

1

未完待续

上一篇:大数据时代的云计算:迅雷云加速悄然布局


下一篇:Spring项目开发过程的几个坑