MySQL InnoDB 备份与恢复

MySQL InnoDB 备份与恢复

MysqlDump

这种方式不仅适用于InnoDB,还适用于其它类型的存储引擎,如MyISAM。备份的时候将数据库备份成SQL(包含drop,create,insert等语句),恢复的时候直接导入即可。属于逻辑备份。

Copy Files拷贝文件

我们知道InnoDB底层存储的时候会将数据和元信息存在下列文件中:
ibdata*, *.ibd, *.frm, .ib_logfile,所以备份这些文件即可备份InnoDB的数据,另外别忘了将MySQL的配置文件my.cnf也一并备份起来。
如果my.cnf中开启了innodb_file_per_table那么InnoDB在存储的时候也会像MyISAM那样每一个表都会有相应的文件,你也可以只备份其中的某些文件(也就是表)。
注意copy这些files的时候,最好能确保InnoDB的所有事务都commit了。
比如你可以将MySQL暂时关闭掉,如果不能忍受一些down time的话,先不要考虑这种方式。
另外还可以先获取Table的read lock, 比如Lock Tables Customers READ.
你可以用show innodb status看看还有没有活动的事务没有commit。属于物理备份。

下面讲一下这种备份方式怎么恢复:

a. 停止任何关于这个表的写,lock tables customers write;

b. 删除这个表的空间,alter table customers discard tablespace;

c. 将你备份的.ibd文件拷贝到相应的目录下。

d. 重新建立表空间,alter table customers import tablespace;

e. 释放写锁,unlock tables customers;

ibbackup

这是一个商业化的工具,将你线上的my.cnf配置文件复制一份出来到比如说/etc/my.backup.cnf,更改里边的datadir等比如说到/data/backup,然后运行 ibbackup /etc/my.cnf /etc/my.backup.cnf, ibbackup就会讲my.cnf所指向的的数据内容备份到my.backup.cnf指向的数据目录。恢复的时候也很奇葩,因为你现在等于说是拥有了两个数据目录,所以你重新运行mysql,safe_mysqld --defaults-file=/etc/my.backup.cnf. 这种方式仅局限于你的数据库全部使用InnoDB存储引擎。

innobackup

如果你还有其它数据表用的是MyISAM,那么你可以使用innobackup,它不但会将InnoDB的相关文件备份起来,还会将MyISAM的比如MYI, MYD文件也备份起来。innobackup使用了ibbackup作为InnoDB的备份子工具。

MySQL Administrator

binary logs

需要开启log-bin, 所有的更新操作都会被写到binary file里。恢复的时候mysqlbinlog binlog_file | mysql,这种方式基本上可以用在在线备份上。属于逻辑备份。

XtraBackup

对InnoDB做数据备份的开源工具,支持在线热备,备份时不影响数据读写。属于物理备份。
项目地址https://github.com/percona/percona-xtrabackup,可以结合使用Innobackupex进行全备,xtrabackup进行增量备份。

个人觉得第一种,第二种和第六种和第七种都非常不错,各有自己的特点。第一种方式比较适合小型的数据库,由于是SQL,所以不仅可以跨存储引擎,还能跨不同的数据库,比如PostgreSQL。数据量稍大一些就可以用第二种方式了,由于是文件传输,所以效率就是文件传输的效率了。第六种方式将数据库本身与备份方式利用binlog进行解耦,既不会对在线数据库造成太大的overhead,而且因为所有的更新操作都在binlog里了,所以可以利用此数据做很多的事情。第七种方式比较特殊,结合了第三种和第四种,所以功能上比较强大,商业上可以选用。

Continue reading...

MySQL中用GKB来让UTF-8字段中的中文按照拼音排序

UTF-8 中的中文不是按照拼音排序的,因此对于 使用 UTF-8 编码集的字段就无法按照拼音进行排序,最简单的解决方法就是转成 GBK 编码。
实例代码:

SELECT *
FROM `test`
ORDER BY CONVERT( `test`.`name`
USING GBK )
LIMIT 0 , 30

数据库表结构:

CREATE TABLE IF NOT EXISTS `test` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

测试环境:Windows 2003 Standard Edition,MySQL 5.1.22-rc-community。
对于我正在使用的 Symfony 框架,因为使用了 propel 做 ORM,比较难直接操作SQL,除非使用 RAW SQL,而且使用 RAW SQL会导致很多高级特性无法使用,解决方式:$criteria->addAscendingOrderByColumn('CONVERT(' . TestPeer::NAME . ' USING GBK)');

Continue reading...

写的一个discuz存储过程数据测试

BEGIN

DECLARE i int;
DECLARE p_uid int;
DECLARE p_username VARCHAR(24);
SET i = 1;
WHILE i <12 DO
SELECT uid,username INTO p_uid, p_username FROM pre_common_member_49 ORDER BY RAND() LIMIT 1;

SELECT p_uid , p_username;

INSERT INTO

`discuz`.`pre_publicity_users`
(`pid`, `uid`, `username`, `fromip`, `fromtime`, `extcredits1`, `extcredits2`, `extcredits3`, `extcredits4`, `extcredits5`, `extcredits6`, `extcredits7`, `extcredits8`, `content`) VALUES

('16435049', p_uid, p_username, '192.168.51.53', CURRENT_TIME(), '1', NULL, NULL, NULL, NULL, '1', NULL, NULL, '威望+1 贡献+1 ');

SET i = i + 1;

END WHILE;

END

Continue reading...

MySQL MyISAM/InnoDB高并发优化经验.

最近做的一个应用,功能要求非常简单,就是 key/value 形式的存储,简单的 INSERT/SELECT,没有任何复杂查询,唯一的问题是量非常大,如果目前投入使用,初期的单表 insert 频率约 20Hz(次/秒,我喜欢这个单位,让我想起国内交流电是 50Hz),但我估计以后会有 500Hz+ 的峰值。目前的工作成果,额定功率 200Hz(CPU 占用 10 – 20,load avg = 2),最大功率 500Hz(这时 load avg > 20,很明显,只能暂时挺挺,应该在出现这种负载前提前拆表了)

INSERT DELAYED INTO

从 数据的插入开始说起。如果可以容忍结果几秒以后再生效的,可以用 INSERT DELAYED INTO,因为在我的这个结构中不需要对同一个 key 频繁的 INSERT/SELECT,因为 SELECT 我是用 Memcached 挡住了,除非 Memcached 挂了,或者数据实在老到过期了,才会去 SELECT。而且要注意,如果 PHP 不需要关心 MySQL 操作的返回结果,应该使用 unbuffered query,简单的说,在你提交 query 后,不用等待 MySQL 有返回信息就继续执行之后的 PHP 指令,具体用法是用 mysql_unbuffered_query 代替 mysql_query,如果用的 MySQLi 类,应该使用 mysqli->query($sQuery, MYSQLI_USE_RESULT);

如果 SHOW PROCESSLIST,可以看到用户名为 DELAYED 的进程,进程数量等于 INSERT DELAYED 的表的数量,因为表级锁的存在,每个表一条以上的 DELAYED 进程是没有意义的

Continue reading...

CentOS编译安装MySQL-5.5.20 cmake参数说明。

  1. cmake . \   
  2. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \   
  3. -DMYSQL_DATADIR=/usr/local/mysql/data \   
  4. -DMYSQL_UNIX_ADDR=/var/tmp/mysql/mysqld.sock \   
  5. -DDEFAULT_CHARSET=utf8 \   
  6. -DDEFAULT_COLLATION=utf8_general_ci \   
  7. -DWITH_EXTRA_CHARSETS=all \   
  8. -DWITH_MYISAM_STORAGE_ENGINE=1 \   
  9. -DWITH_INNOBASE_STORAGE_ENGINE=1 \   
  10. -DWITH_MEMORY_STORAGE_ENGINE=1 \   
  11. -DWITH_READLINE=1 \   
  12. -DENABLED_LOCAL_INFILE=1 \   
  13. -DMYSQL_USER=mysql  

-DCMAKE_INSTALL_PREFIX= 数据库程序安装路径;
-DMYSQL_DATADIR= 数据库文件存放路径
-DMYSQL_UNIX_ADDR= 默认位置是/tmp/mysql.sock
-DDEFAULT_CHARSET= 默认数据库编码
-DDEFAULT_COLLATION= 默认数据库整理编码
-DWITH_EXTRA_CHARSETS= 扩展支持编码(all | utf8,gbk,gb2312 | none)
-DWITH_MYISAM_STORAGE_ENGINE= MYISAM引擎支持(1|0)
-DWITH_INNOBASE_STORAGE_ENGINE= innoDB引擎支持(1|0)
-DWITH_MEMORY_STORAGE_ENGINE= MEMORY引擎支持(1|0)

Continue reading...



about me

  • 陶之11's Blog Name:陶之11 OICQ:14779023 Site:Pao11.com

分类

快捷入口