标签：MySQL

分类
Mysql

Mysql-Innodb和Myisam概念与数据恢复

Innodb和Myisam是Mysql常见的两种数据存储引擎。没有研究过Oracle、SQL Server等数据库，所以下面只针对Mysql。

一、两种方式的数据存储结构：

在Myisam下，数据库的每个数据表都有*.frm、*.YMI和*.YMD三个文件，其中*.frm存储数据表的表结构，*.MYI存储数据表的索引，*.MYD存数数据表的记录数据；

在Innodb下，每个数据库下的每个数据表只有一个*.frm存储数据表的表结构，而所有数据库的所有表数据索引、数据记录都全部存储在ibdata1文件中，而ib_logfile0和ib_logfile1是日志文件。

 

二、数据导入和恢复：

Case1 Myisam =》 Myisam：

由于Myisam的数据表结构、索引、记录数据等信息分别存储在*.frm、*.MYI和*.MYD文件中，所以只需要将源数据库数据表的相应三个文件复制到对应目的数据库文件夹下就可以了。

但是如果只有*.frm（假设test.frm）了，那么可以将test.frm复制到对应的数据库目录（假设tmp数据库）之后，在tmp目录下，新建test.MYI和test.MYD文件。此时通过“show tables;”可以看到有test表，但是查看表的数据，如“desc test;”，此时报错无法查看，因为只有test.frm有数据，而test.MYI和test.MYD是新建的无效文件。然后可以通过Mysql自带修复操作“REPAIRTABLE test USE_FRM”修复数据表，然后就可以查看表的数据，但是为空（因为test.frm中不包含数据）。

Case2 Innodb =》 Innodb：

由于Innodb下，表结构存在*.frm文件，但是表的数据存储在ibdata1文件中，所以导入时，除了复制*.frm，还要复制源Mysql的data目录下的ibdata1，替换掉目的数据库的ibdata，如果目的数据库中有已存在的其他数据库，此时需要先备份目的数据库的原先data数据，然后将新导入的表格通过其他方式导出，然后在还原原先的data数据，将新导出的数据导入。

如果只有*.frm（假设test.frm），没有有效的ibdata1，那么也只能恢复表的结构。先将test.frm复制到另一个数据库中（假设tmp），然后在tmp下新建一个Innodb型的数据表test（有哪些字段不重要），不要添加任何记录。然后将要恢复的test.frm复制到test目录下替代新建test表产生的test.frm，重启mysql之后，新建的test表的结构就和要恢复的test表结构相同

Case3 Myisam =》 Innodb 和 Innodb =》 Myisam：

如果有*.frm、*.MYI和*.MYD三个文件，可以通过Case1的方法导入之后，重新导出sql或者其他文件，也可以导入后修改为Innodb，之后通过Case2方式。同样Innodb =》 Myisam的操作类似。

 

对于数据库的数据，除了从.frm文件恢复之外，如果有日志文件，从日志文件恢复也是一个一个选择。但是对于数据库，进行移植或这更换系统时，最好还是通过导入导出工具到处相应的sql语句文件。

 

三、Innodb和Myisam其他区别：

　　1、Myisam多个数据库和数据表的记录数据是分文件存储的，而Innodb所有表的记录数据都存储在ibdata1文件中，所以当数据表记录比较少，单个表的数据文件比较少时，此时使用Myisam性能会略好于Innodb。但是如果表的数据记录非常多，此时使用Innodb的性能会远远高于Myisam，通过测试可以发现，随着表的记录增加，Innodb的性能降低很少，而Myisam的性能则迅速降低。

2、Innodb支持事务操作，而Myisam不支持事务操作。

事务操作：就是一组多条sql指令作为一个整体，可以并发操作，但是关键点在于事务内的多个操作必须同时执行完才会提交结果。所以事务内的SQL要么都不做，要么都做。

事务实现一般有下面两种方式：

Way1 begin、rollback和commit：

begin表示开始事务，rollback表示事务回滚，commit表示事务提交。

Way2 通过set来改变Mysql默认的自动提交模式：

set autocommit=0 禁止自动提交（使用事务，此时每条SQL都会当作事务，必须显示使用commit体提交结果或者rollback撤销结果）
set autocommit=1 开启自动提交（禁用事务）

分类
常见问题

Mysql的row_format

在mysql中， 若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。

所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。

row_format还有其他一些值：

DEFAULT
FIXED
DYNAMIC
COMPRESSED
REDUNDANT
COMPACT

修改行格式

ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改过程导致：

fixed—>dynamic: 这会导致CHAR变成VARCHAR

dynamic—>fixed: 这会导致VARCHAR变成CHAR

分类
常见问题

mysql随机数方法

随机出来2-8的数字，

select rand() * (8-2) + 2 | 0;

哦也，好像网上还没有这样的方法呢。

 

分类
常见问题

客户端提示 Cannot proceed,system tables Event Scheduler

在打开数据库时客户端提示“Cannot proceed because system tables used by Event Scheduler were found damaged at server start” 造成此原因是MySQL服务器的当前版本不兼容所有数据库的表

解决方法

1、linux、windows下解决方法类似
$PATH/mysql/bin/mysql_upgrade – u root -p
例如：/usr/local/mysql/bin/mysql_upgrade – u root -p     [回车输入密码即可]

$PATH mysql安装路径

执行完后停止mysqld后再重新启动mysqld一次，问题即可解决。

具体说明参考：http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

分类
常见问题

利用sql语句复制一条或多条记录

sql 复制记录
insert into article (id,class,title,content) select id,’2′,title,content from article where class=’1′
如果id为自动编号,就把改为:
insert into article (class,title,content) select ‘2’,title,content from article where class=’1′
如果class为数字类型,则去掉单引号。
分类
常见问题

MYSQL Error 2006HY000：MySQL server has gone away的解决方案

MySQL server has gone away有几种情况。

1、应用程序（比如PHP）长时间的执行批量的MYSQL语句。
最常见的就是采集或者新旧数据转化。
解决方案：

在my.cnf文件中添加或者修改以下两个变量：
wait_timeout=2880000
interactive_timeout = 2880000

关于两个变量的具体说明可以google或者看官方手册。

如果不能修改my.cnf，则可以在连接数据库的时候设置CLIENT_INTERACTIVE，比如：

sql = “set interactive_timeout=24*3600”;
mysql_real_query(…)

2、执行一个SQL，但SQL语句过大或者语句中含有BLOB或者longblob字段。

比如，图片数据的处理

解决方案

在my.cnf文件中添加或者修改以下变量：
max_allowed_packet = 10M(也可以设置自己需要的大小)

max_allowed_packet 参数的作用是，用来控制其通信缓冲区的最大长度。

分类
常见问题

解决mysql远程连接速度慢的问题

发生上述情况的原因在于
mysql服务器在接收到一个远程ip访问的时候，默认会去查该ip的反向解析
这个反查的过程会比较慢，如果该ip没有反解，mysql也有可能会卡死在这个连接上。
从而导致mysql中出现大量状态为Connect的连接，影响mysql使用。

禁止MySQL对外部连接进行DNS解析，从而导致mysql中出现大量状态为Connect的连接，影响mysql使用使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

为了杜绝这个现象的发生，可以有两种解决方法
1、在my.cnf的[mysqld]段中添加一行skip-name-resolve，表示跳过反向解析
[mysqld]
skip-name-resolve
2、将远程的ip地址写在/etc/hosts文件中，这样在反向解析时候能快速找到反解。

分类
常见问题

我的MySQL事务处理（可以支持事务处理及数据库路径自己定义）

#这是我的MySql数据的配置
#本文中我的用计算机的mysql数据库安装目录是“d:\webserver\mysql”
#我的数据库目录是“f:\sun data\mysql data”
#我的计算机的IP地址为192.168.0.2如果是公网IP也是一样
#[mysqld]部分为mysql的环境配置部分
#[WinMySQLadmin]为数据库启动部分
[code]到[/code]之间的为配置环境变量及事务处理数据库的部分

#本文中使用”#”的部分为注释行正文部分带有“#”的为可选的，可以打开

#本文参考了部分mysql的阅读文件。

[mysqld]
basedir=d:/webserver/mysql
#bind-address=192.168.0.2
datadir=F:/Sun Date/mysql data/
#language=d:/webserver/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306

[WinMySQLadmin]
Server=d:/webserver/mysql/bin/mysqld-max-nt.exe
user=启动数据库的用户名
password=启动数据库的密码
[code]
innodb_data_file_path = ibdata1:1000M;ibdata2:1000M
innodb_data_home_dir = d:webservermysqlibdata
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = d:webservermysqliblogs
innodb_flush_log_at_trx_commit=1
innodb_log_arch_dir = d:webservermysqliblogs
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=80M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=30M
set-variable = innodb_log_buffer_size=8M
#set-variable = key_buffer=16M
[/code]

分类
常见问题

Mysql-front 5.1注册码

bhaaAThangemMkaz2tQhq3/f7dZ7
Vj29WeGHjuupj/AhYqymjAuokhYi
X1T/fG+q1yR22PdcEP39dxU3ovEo
lLLzwIZlQr9oJYwUf5eG4x5e1bMP
nfIaIl8reszQPHFNbrxBjCVrBEXL
TyBLehxzUpVI672t3LjX8q4ytCRC
ezcoZQfMAc0DulJzNREbwlcf0Rco
N0zW3ojBUxGsplBBZZPrD7/bPhkM
rjtS8Bxym+HeV1TJDTXuiL8QLg==

======================================

fSbC9xxy7ga57DOrLz9mMqYSFx8S
OIWiOBI61uJ562GINVTVNBDaGBIl
Cuni0a1iUAF88I6zrHzH9Wzcv4h+
h3wTGgOX8mYgN0jEEOmzI1DLx0DJ
8t94hUJ5O0I99Ts6iqvHhw24jYfI
h++JG/BzVLnaSDbNdqH9iYbXZ2Pc
eFZAZe6hzPQ9DREtkEvg7rAul/Fw
ZptCP1WFfKkfgviwY8i67NucJe2L
UTZFKoX5MGSuP9GGJnouQg/P1SII
3PCKXGoUv0hBoTiMLXBlJWpaNg==

=============================================

0W01VaFQPp/XRYgXjOFSUY7hZ5Cc
W9f35KH4bym2jmhYygsHyNsr47Hs
7OBjd7QH+ALAKqZdDAh/AfG3V/8Q
his64uZEhglY8B8yu5PvN49tOO05
XG7Qyz3skOK3An9Zoc/xSA1fLGeq
PioqBj/khHWF1LK0lBi00LOLFXzs
oQtoMi1Ol+Ehxz8URH3kQqN09raw
iM7DrTxZe1oVoOjTPDerwPOLULI1
6EnbdBu92YSk0tYQw0BmVss2G6ak
GjaDrPWrDWP3uip0OOX4UXNggQ==

===============================================

H4nLHG3xsjy5l4QMEsSpaYAyggPp
9OdFDMG0Y1XM7VqTyg+GAAm8V/eP
9zuAwQ5/9tA8GQeOlavXxIDkMsY7
I4UAy4LmhUU2vLtEkgjtMhSd3nFT
OGe2bOmGvoMCPts/1eQx5YFMet9K
AjkZPI1BR8/fm7Mb0bQOPeJQyilC
T1/4k4MfqnQkub06+ADZ7pg3WdkC
jneqAVvKPaM2Kf55cGDsLmo3Xxck
ZHpzPKJDISjEQ+qFadS8Z9Ao0HHK
31JWyvw0Qr/9d0Ku10+W6DrhZw==

=================================================

DZaNXFw2rfRrF3xlukPGGiilkurQ
r7vKuSPCUEdAoRWRJmjnADY0Ink3
Nr9qD3CM5+v9PYJ4uf/b9jShtlYH
KV16XPA+On7CjH8zqxywbz2ci85i
f0va/I0DOvGhpsww1jAJCJWC4A+z
ZjTMzA0+SEQcKyHzOUe89eA+c/Gq
FZBmitn5u602wlMj2KtvMSN8EgSc
wDVxI+FSi0U/b89fRaUMJRbUadm0
uZHKMPdlaykMQPwZFN8G561OFCU/
rbFQlatd0JFPnLKxjNsjva04LQ==

===============================================

hFeWdc8oboH/QwzaaLhrH6CgRo6g
nBXqa0bjHJdEYTIzMdqnkEBvygf+
xJeO2zRqajhnH+S1wZWeNUwXLwZm
KvS8LCvwCf1WBqzsRMuDOTQnac5d
RGf80G986szBG8kzkhKjC4Bx3uLy
NomiYSHrGBoDacd5wPCqFMoTsaMC
CjVMabK/JyQyAeuULOf8lW0WiXTo
TKH7k1yGW6plaQKWGIqBZuYlYk5H
tFm8hdKXaxbeiiREFl7ni1G9lP7j
1Gdf/cmb+fq2sKrSgZyLEjQLFA==
===============================================

Y2CjWsyI23Fa0G1pban8ep83zQAI
7mERztK7a3ZufX0LWeAe/y50DURN
kOOlakZ0dgkoipf/6KsNEEm2aptm
L3/dXG1jKphojzSwNsS4PHY4qGKA
9vHBEAsDLlwGrLFfmdtKOCrgNG6V
ab7clt+yRFuxctwefegNu02YdlGC
wEKI2MP9QcMXl/aFLkFsz3mBFn1t
YZtjYFswD+7zJ6KETy1cRK0TG/UK
/6GoBQtjLIeCjNBbrEWvOm6XunsO
jCaOhBeccOe3lIUCbJEwSFKAGA==

=============================================

3AFJhTfHVl0MBUFC1NEgO34cyEqR
saJhnLE8X1K0OU5wU630QIlLyAiu
pMGYcSoTEanqEaBvKiXFfMvqbTM+
X87dmWn6bicol0G9APDw1GKO4A4g
Zyc8z47uRSNgqD9moJGFIdZZ+l1b
+/PCg5MKBBhDhc6VDBKJ3hdVTu6L
sW3+fY+9awZvyMMrXNu3STU0z0TX
kHVOuMI+XqKeywPGEClGO/U5vgN7
DbL2xQm0Czm89V3fPnY+03gVOkP2
dvBKP4EqwgCXuxJVDEbC8T0bPQ==

=============================================

SoydMqMl+/Gpf41SeCYSamtq3urK
wqREsSLloXTdR1DoEd6sFEtxWH+0
w/nC12MfI+TzlhOuvit3bqOzUSrq
XTPipT1EA1YfelWllo3inkHWZ8Bd
IG4b/GCULr4ufAKEtZnyXVHeUPoo
/HCkWPViDUwQUbQqZhcu7baPDnAo
fGQlW5++dTXq3yIZTxNs8tpffgEk
5oemtUyJhXaAEas6KQKw/vEFLPho
48B3tuL31SNvluTSp3O3y8qZvSEV
h3GE09Cp7E4SWKkv6rB9VG8Vcg==

===============================================

Xzlv+2HZXpuIUhAUGVYmjhw6ncp9
b7qlOIwQa4BiPlhJl61ma2fUW1s4
BvNSzx0WgiHGB86X6VZ1ix3bXZop
eq4MxQFHKtaLj60Y6jduOX8Id9Is
oBIKLLkuRijSbZJpBxIqHLrz+2C/
nym1n8XbWYagYJ1z4OqNxjkrCGfW
LoSPC9jAtivGSk3T5lDsUzh6MEqF
bSd6Bjce5tqDgX9AV0DM3icFg3dF
/u9XXp+72DOCT9FStV01UwID6L/O
zzokhH37OQL8irSbjE1FdGW+TQ==

==========================================

BJNZHBPeVX4GKH3AbulBcezga945
qNEKaHM8PpixioQLO0rZ57tUUQ7l
jkWF/5Vh6pWVSJTmNhM54CkFDq3a
M6EVzfPJT00+YS66mmQe4FI85zaj
cvyNyiaKqBh8XjwxXhFs6oHjYJJ4
MAWRTTvOVXDH29E7J30UQkz4qFWD
0445Aj0clJCfu4WAmGq7TmAWF8H2
g+k5VUTx8aBnRS/WVMG3iyryv+3h
W2S4LjfsnF1Zsaf4D4N9p5Uqr2rI
Lvhaj80tbxjTja+MLVhNr4GURQ==

=================================================

Lv/+h44wqgNXFicAJPjLZFqwpCoF
mnn2JvfPTtQ9kEX51XdLNImcH6Pe
bKp1KUMMGaUWSA5/NRMqqmoVaJQm
Fh3B9G2jCyVfYeag+EFRCI/ByCpx
Jg+yvnE4i9NUS1mKuBXD/QR5uEBk
OsiAzv8DgQORN/sbIkjUKX7vwvzG
6QEaDIPaWzKqPV7RIctjkUt4+5Av
r5oBre/6Mg1Q1GIhBftZkD1UtPm3
/3oDrT3o+2SlPNwElOcmypfkMHkk
jAVK5+vfb8lOPKou3lmzUPZPKw==

==============================================

zPH5/yKwB7akS0BMDoMsIRoIA0dN
rE5EB8qSLhyCiwp+oZVZOYm7fH/o
z7odFfCIGuTPHwUOB7r2u6AT1v0a
i0vSNJXevY/dv/EY2hCClIhxTohs
GUE0VLLV3vZJ0+AalJPuXfP3v513
PQgPG0jLLMHoYm2gC0eZU4TCQx0B
r4JJo5Ne0CH9wGfwiPdkIul0NhHU
uQGP78v0FIsTBbQvDuing6q3msxX
GQ+GeVUmshiefNvZ8MzB86ug0PaL
hgtayc9N3lwGEf23C2S/MZW6bQ==

==================================================

pF+PyM1Vz5eTwApjWBBOC8UtZmOV
0xsmgjgvdTLVMXc8pLbhdsDkvMyI
sgBv8WZuftr4FdKRxQVXVr+j5MAi
ai3I9jJ4msA80i5ITzj+UykFuXeJ
M8AB68iTiN80FjoKA1o2aJcjzc1J
ffxHJCavDvlFG7KnfbuM9VjLHIbQ
9lkXTIQX20o6qYOPmPpOG7JqeVLe
H4XZLbTjzJp81h5YY9dVISCKbf01
1Wl6vlh3L1Q1IFSnIfaQpTZwixHw
bJZE8tuy2STl7G17uiqjuvcmdg==

分类
常见问题

关于MySql备份恢复时错误的处理

　　今天在对MySql数据库进行备份、恢复时，出现一个问题，从源数据库用Bascup as SQL dump方式备份出的SQL文件，执行恢复Restore from SQL dump时出现错误，在打开错误日志时，记事本停止响应，无法打开，因此看不到错误日志，无法确定错误原因。后来一想记事本停止响应应该是日志文件过大造成的，而记事本对大的文本文件处理能力较弱，于是上网下载UltraEdit文本编辑器，手动果然可以打开错误日志（winxp系统错误日志路径：C:\Documents and Settings\zqd\Application Data\SQLyog\sqlyog.err），错误信息为：“Got a packet bigger than ‘max_allowed_packet’ bytes”，一看备份的SQL文件含数据体积达到60多兆，几经折腾找到了解决的办法：

　　1、在MySql安装目录下找到配置文件my.ini(Linux下为my.cnf)，如果找不到则说明系统没有这个配置文件（默认好像没有这个文件），可以手动新建一个txt文件，将名称改为My.ini(Linux下未测试)。

　　2、在其中添加：max_allowed_packet=500M（可以自定义）

　　3、如果My.ini存在，则还是按步骤2操作，完毕后，还须查找是否有这样一行：old_passwords=1。如果有，则删除，如果没有则OK。

　　4、此时即可用恢复功能恢复超大的备份文件。一切正常。