mysql 自己总结的实用技巧.docx

mysql 自己总结的实用技巧.docx

《mysql 自己总结的实用技巧.docx》由会员分享，可在线阅读，更多相关《mysql 自己总结的实用技巧.docx（21页珍藏版）》请在冰豆网上搜索。

mysql自己总结的实用技巧

1、安装mysql服务

cdmysql/bin（配置过环境变量的可以略过）

mysqld.exe–install（默认服务名称是mysql或者mysqld.exe–install“mysql5″修改服务名称）

删除mysql服务

mysqld.exe–remove（或者mysqld.exe–remove“mysql5″）

2、启动停止服务（必须是安装mysql为服务后）

a）在命令行启动/停止

netstartmysql

netstopmysql

b）或者去控制面板/管理中心/服务，找mysql启动

c）或者开始/运行输入services.msc，确定后，找到mysql启动

引擎

看你的mysql现在已提供什么存储引擎:

SHOWENGINES;

看你的mysql当前默认的存储引擎:

SHOWVARIABLESLIKE'%storage_engine%';

你要看某个表用了什么引擎（在显示结果里参数engine后面的就表示该表当前用的存储引擎）:

SHOWCREATETABLEtablename;

如何修改MySQL数据库引擎

对于MySQL数据库，如果你要使用事务以及行级锁就必须使用INNODB引擎。

如果你要使用全文索引，那必须使用MyISAM。

INNODB的实用性，安全性，稳定性更高但是效率比MyISAM稍差，但是有的功能是MyISAM没有的。

修改MySQL的引擎为INNODB，可以使用外键，事务等功能，性能高。

本文主要介绍如何修改MySQL数据库引擎为INNODB，接下来我们开始介绍。

对于MySQL数据库，如果你要使用事务以及行级锁就必须使用INNODB引擎。

如果你要使用全文索引，那必须使用MyISAM。

INNODB的实用性，安全性，稳定性更高但是效率比MyISAM稍差，但是有的功能是MyISAM没有的。

修改MySQL的引擎为INNODB，可以使用外键，事务等功能，性能高。

本文主要介绍如何修改MySQL数据库引擎为INNODB，接下来我们开始介绍。

首先修改my.ini，在[mysqld]下加上：

default-storage-engine=INNODB

其中的蓝色字体是要指定的数据库引擎名称。

用sql语句修改已经建成表的引擎：

alter table tableName type=InnoDB   --type语法在4.X版本下存在

alter table tableName ENGINE=InnoDB   --5.X下都改成engine=innodb

忘记mysqlroot密码处理办法：

1．关闭mysqld.exe进程

2．Win+R打开cmd.exe

3．运行mysql的bin目录下的mysqld-nt加参数--skip-grant-tables如下

mysqld-nt--skip-grant-tables

4．再打开个cmd.exemysql-uroot进入

5．修改root密码

切换数据库usemysql

修改user表updateusersetpassword=password（‘新密码’）whereuser=’root’;

刷新权限flushprivileges;

退出quit;

6.进入任务管理器关闭mysqld-nt进程

7.从新打开mysqld.exe（可以在任务管理器中新建mysqld.exe）

8.Mysql-uroot-p回车输入密码进入

MySqlHostisblockedbecauseofmanyconnectionerrors;unblockwith'mysqladminflush-hosts'解决方法

环境：

linux，mysql5.5.21

错误：

Hostisblockedbecauseofmanyconnectionerrors;unblockwith'mysqladminflush-hosts'

原因：

　　同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞；

解决方法：

1、提高允许的max_connection_errors数量（治标不治本）：

　　①进入Mysql数据库查看max_connection_errors：

 showvariableslike'%max_connection_errors%';

　  ②修改max_connection_errors的数量为1000：

 setglobalmax_connect_errors=1000;

　　③查看是否修改成功：

showvariableslike'%max_connection_errors%';

2、使用mysqladminflush-hosts 命令清理一下hosts文件（不知道mysqladmin在哪个目录下可以使用命令查找：

whereismysqladmin）；

　　①在查找到的目录下使用命令修改：

/usr/bin/mysqladminflush-hosts-h192.168.1.1 -P3308 -uroot -prootpwd;

　　备注：

　　　　其中端口号，用户名，密码都可以根据需要来添加和修改；

　　　　配置有master/slave主从数据库的要把主库和从库都修改一遍的（我就吃了这个亏明明很容易的几条命令结果折腾了大半天）；

　　　　第二步也可以在数据库中进行，命令如下：

flushhosts;

编码

MySQL的默认编码是Latin1，不支持中文，要支持中午需要把数据库的默认编码修改为gbk或者utf8。

1、需要以root用户身份登陆才可以查看数据库编码方式（以root用户身份登陆的命令为：

>mysql -uroot–p,之后两次输入root用户的密码），查看数据库的编码方式命令为:

 >showvariableslike'character%';

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

|Variable_name|Value|

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

|character_set_client|latin1|

|character_set_connection|latin1|

|character_set_database|latin1|

|character_set_filesystem|binary|

|character_set_results|latin1|

|character_set_server|latin1|

|character_set_system|utf8|

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

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

从以上信息可知数据库的编码为latin1，需要修改为gbk或者是utf8；

其中，character_set_client为客户端编码方式；character_set_connection为建立连接使用的编码；character_set_database数据库的编码；

character_set_results结果集的编码；

character_set_server数据库服务器的编码；

只要保证以上四个采用的编码方式一样，就不会出现乱码问题。

另一个查看数据库编码的命令：

>showvariableslike‘collation%’;

2、linux系统下，修改MySQL数据库默认编码的步骤为：

ü        停止MySQL的运行

/etc/init.d/mysqlstart（stop）为启动和停止服务器

ü        MySQL主配置文件为f，一般目录为/etc/mysql

var/lib/mysql/放置的是数据库表文件夹，这里的mysql相当于windows下mysql的date文件夹

ü        当我们需要修改MySQL数据库的默认编码时，需要编辑f文件进行编码修改,在linux下修改mysql的配置文件f，文件位置默认/etc/f文件

找到客户端配置[client]在下面添加

default-character-set=utf8默认字符集为utf8

在找到[mysqld]添加

default-character-set=utf8默认字符集为utf8

init_connect='SETNAMESutf8'（设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行）

修改好后，重新启动mysql即可，重新查询数据库编码可发现编码方式的改变：

>showvariableslike'character%';

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

|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/|

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

此方法用于标准mysql版本同样有效，对于/etc/f文件，需要从mysql/support-files的文件夹cpmy-f一份到/etc/f

3、windows系统下可以删除MySQL数据库，并重新安装，在安装过程中可以直接用ＭysqlServerInstanceConfigWizard进行设置

4、当MySQL数据库服务器已经有数据不适合删除重装时，可以个别指定数据库的编码方式。

MySQL指定编码的方式是非常灵活并多样化的，可以指定表级别的编码，行级别编码，甚至可以指定字段级别的编码。

以下示例给出创建数据库时指定编码的两种方式：

1）CREATE DATABASE ms_db CHARACTERSET utf8 COLLATE utf8_general_ci；

2）create database if not exists netctoss defaultcharactersetutf8;

5、如果你采用的是外部接入的方式，可以在连接中确定请求的编码格式如：

jdbc:

mysql:

//localhost:

3306/mysql?

useUnicode=true&characterEncoding=utf-8（注意：

不要出现任何空格，否则出错）

6、执行脚本：

指定编码格式setnamesgbk（注意，不是UTF-8）可以修改

执行前：

执行后：

从执行命令前后可知，setnamesgbk只可以修改character_set_client、character_set_connection、character_set_results的编码方式，并且这种修改是窗口级别的，只针对本窗口有效，打开另外一个窗口修改无效。

也可发现数据库底层的编码方式没有改变，插入数据后还是以utf8编码方式保持。

基本操作

1、连接Mysql

格式：

mysql-h主机地址-u用户名－p用户密码

1、连接到本机上的MYSQL。

首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql-uroot-p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。

如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是：

mysql>

2、连接到远程主机上的MYSQL。

假设远程主机的IP为：

110.110.110.110，用户名为root,密码为abcd123。

则键入以下命令：

mysql-h110.110.110.110-uroot-p123;（注:

u与root之间可以不用加空格，其它也一样）

3、退出MYSQL命令：

exit/quit（回车）

2、修改密码

格式：

mysqladmin-u用户名-p旧密码password新密码

1、给root加个密码ab12。

首先在DOS下进入目录mysql\bin，然后键入以下命令

mysqladmin-uroot-p-passwordab12

或mysql>SETPASSWORDFOR'root'@'localhost'=PASSWORD（'newpass'）;

方法3：

用UPDATE直接编辑user表

　　mysql-uroot

　　mysql>usemysql;

　　mysql>UPDATEuserSETPassword=PASSWORD（'newpass'）WHEREuser='root';

　　mysql>FLUSHPRIVILEGES;

注：

因为开始时root没有密码，所以-p旧密码一项就可以省略了。

2、再将root的密码改为djg345。

mysqladmin-uroot-pab12passworddjg345

3、增加新用户

注意：

和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符

格式：

grantselecton数据库.*to用户名@登录主机identifiedby“密码”

（@%）对所有的机器，

1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。

首先用root用户连入MYSQL，然后键入以下命令：

grantselect,insert,update,deleteon*.*to[email=test1@”%]test1@”%[/email]”Identifiedby“abc”;

但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。

2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。

grantselect,insert,update,deleteonmydb.*to[email=test2@localhost]test2@localhost[/email]identifiedby“abc”;

如果你不想test2有密码，可以再打一个命令将密码消掉。

grantselect,insert,update,deleteonmydb.*to[email=test2@localhost]test2@localhost[/email]identifiedby“”;

4.1创建数据库

注意：

创建数据库之前要先连接Mysql服务器

命令：

createdatabase<数据库名>

例1：

建立一个名为xhkdb的数据库

mysql>createdatabasexhkdb;

例2：

创建数据库并分配用户

①CREATEDATABASE数据库名;

②GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTERON数据库名.*TO数据库名@localhostIDENTIFIEDBY'密码';

③SETPASSWORDFOR'数据库名'@'localhost'=OLD_PASSWORD（'密码'）;

依次执行3个命令完成数据库创建。

注意：

中文“密码”和“数据库”是户自己需要设置的。

4.2显示数据库

命令：

showdatabases（注意：

最后有个s）

mysql>showdatabases;

注意：

为了不再显示的时候乱码，要修改数据库默认编码。

以下以GBK编码页面为例进行说明：

1、修改MYSQL的配置文件：

my.ini里面修改default-character-set=gbk

2、代码运行时修改：

①Java代码：

jdbc:

mysql:

//localhost:

3306/test?

useUnicode=true&characterEncoding=gbk

②PHP代码：

header（"Content-Type:

text/html;charset=gb2312"）;

③C语言代码：

intmysql_set_character_set（MYSQL*mysql,char*csname）；

该函数用于为当前连接设置默认的字符集。

字符串csname指定了1个有效的字符集名称。

连接校对成为字符集的默认校对。

该函数的工作方式与SETNAMES语句类似，但它还能设置mysql->charset的值，从而影响了由mysql_real_escape_string（）设置的字符集。

4.3删除数据库

命令：

dropdatabase<数据库名>

例如：

删除名为xhkdb的数据库

mysql>dropdatabasexhkdb;

例子1：

删除一个已经确定存在的数据库

mysql>dropdatabasedrop_database;

QueryOK,0rowsaffected（0.00sec）

例子2：

删除一个不确定存在的数据库

mysql>dropdatabasedrop_database;

ERROR1008（HY000）:

Can'tdropdatabase'drop_database';databasedoesn'texist

//发生错误，不能删除'drop_database'数据库，该数据库不存在。

mysql>dropdatabaseifexistsdrop_database;

QueryOK,0rowsaffected,1warning（0.00sec）//产生一个警告说明此数据库不存在

mysql>createdatabasedrop_database;

QueryOK,1rowaffected（0.00sec）

mysql>dropdatabaseifexistsdrop_database;//ifexists判断数据库是否存在，不存在也不产生错误

QueryOK,0rowsaffected（0.00sec）

4.4连接数据库

命令：

use<数据库名>

例如：

如果xhkdb数据库存在，尝试存取它：

mysql>usexhkdb;

屏幕提示：

Databasechanged

use语句可以通告MySQL把db_name数据库作为默认（当前）数据库使用，用于后续语句。

该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句：

mysql>USEdb1;

mysql>SELECTCOUNT（*）FROMmytable;#selectsfromdb1.mytable

mysql>USEdb2;

mysql>SELECTCOUNT（*）FROMmytable;#selectsfromdb2.mytable

使用USE语句为一个特定的当前的数据库做标记，不会阻碍您访问其它数据库中的表。

下面的例子可以从db1数据库访问作者表，并从db2数据库访问编辑表：

mysql>USEdb1;

mysql>SELECTauthor_name,editor_nameFROMauthor,db2.editor

->WHEREauthor.editor_id=db2.editor.editor_id;

USE语句被设立出来，用于与Sybase相兼容。

有些网友问到，连接以后怎么退出。

其实，不用退出来，use数据库后，使用showdatabases就能查询所有数据库，如果想跳到其他数据库，用

use其他数据库名字

就可以了。

4.5当前选择的数据库

命令：

mysql>selectdatabase（）;

MySQL中SELECT命令类似于其他编程语言里的print或者write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。

如何使用MySQL中SELECT命令的特殊功能？

1.显示MYSQL的版本

mysql>selectversion（）;

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

|version（）|

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

|6.0.4-alpha-community|

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

1rowinset（0.02sec）

2.显示当前时间

mysql>selectnow（）;

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

|now（）|

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

|2009-09-1522:

35:

32|

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

1rowinset（0.04sec）

3.显示年月日

SELECTDAYOFMONTH（CURRENT_DATE）;

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

|DAYOFMONTH（CURRENT_DATE）|

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

|15|

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

1rowinset（0.01sec）

SELECTMONTH（CURRENT_DATE）;

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