`

(转)MySQL 不停服务来启用 innodb_file_per_table

 
阅读更多
转载自:扶凯[http://www.php-oa.com]

本文链接: http://www.php-oa.com/2012/04/20/mysql-innodb_file_per_table.html

InnoDB  是一个非常不错的 MySQL 的存储引擎,目前使用非常广泛基本所有的网站和项目,我想都会优先选择这个,这个也有很好的诊断和微调的工具.我发现其中一个缺点,就是磁盘空间管理时设计非常低效.这个设计成给所有数据都存到 ibdata1 文件,所以这个文件的存储空间会不断的扩展.InnoDB 并不会收缩这些空间,就算你删除表和数据库.

所以我们需要注意我们的配置.最好一开始就使用 innodb_file_per_table 的选项.这样可以使得更加灵活性的给数据存到每个单表的数据库. 我非常不幸地, 最开始我的数据并没有考虑这个所以没有打开这个参数.后来我测试过,在之后打开这个参数根本不能生效.所以只能 dump 整个数据库,然后在启动这个参数后重新恢复数据库才行.这时我们在想有没有法子,不用关掉数据库的服务时就能完成这个工作.

目前看来只能使用 mysqldump 输出,然后才能恢复.所以我们只能使用 MySQL 的主从模式来切换,才能最大限度地减少停机时间.

所以,我使用的基本步骤是:

配置为当前的原始数据库为 Master 数据库.
使用 Xtrabackup 来备份你的原始数据库.
恢复你的备份和到第二个 MySQL 的实例上.
恢复,但不同步,然后运行 mysqldump 在第二个做为 Slave 的数据库上然后导出.
停止第二个数据库实例,并删除数据库.
创建一个新的数据库,使用前面导出的数据来恢复 MySQL 的实例,记得先要打开 innodb_file_per_table 的选项恢复.
配置这个数据库为 slave 然后运行复制.
当复制完成时,给这个 slave 切换成主,然后重新配置你的客户端使用这个实例
现在可以停止主数据库和删除它.
下面是详细的步骤

准备,配置数据库主从
这个看以前的 Blog 有讲怎么在线做 MySQL 的主从.接下来在 Slave 上做转换的操作

Dump 出 Slave 上的数据

假设你根据我以前的文章都做好了.现在配置好 slave 了.记得使用 innobackupex-1.5.1 –apply-log 恢复后,然后复制到数据库的目录.并修改权限.

这时记得先不要设置同步.直接启动数据后.直接使用 MySQL 的 mysqldump 来导出整个数据库.

?123 service mysqld start mysqldump -uroot -p --quick  --default-character-set=utf8 bbsee >dump_data.sql service mysqld stop

创建新的数据库

在新的机器上.安装新的数据库文件.只保留 dump_data.sql.然后使用 mysql_install_db 来建权限数据,MySQL 的数据库

?1 mysql_install_db --user=mysql

创建新的配置文件

vim /etc/mysql/my.cnf
添加以下行到新的文件:

innodb_file_per_table = 1
server-id = 2
bin-log = 1
我在次声明,这是成功的关键,innodb_file_per_table 会让过一会导入的数据按表来存, binlog 是为了以后切主的时候使用.



启动 MySQL的并配置 Slave 的同步
先要启动数据库

?1 server mysqld start

恢复导出的数据库

?1 mysql -uroot -p  --default-character-set=utf8  < dump_data.sql

配置复制
因为刚导入数据库,所以要从现在开始复制,需要在主数据库上根据我以前的文章,给这个设置好权限,然后我们在 Slave 配置一下 slave 的设置

?1234567 change master to master_host='127.0.0.1', master_user='repl', master_password='passwd', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=3874; start slave;

这个中的 master_log_file 和 master_log_pos 是根据我以前文章刚开始使用 innobackupex 做主数据备份时 xtrabackup_binlog_info 中的信息

?1 mysql -uroot -p -e "show slave status\G"|grep Seconds

当MySQL显示 Seconds_Behind_Master: 0 时,这就 Slave 就同步的和主数据库一样了.



主从切换
现在备份完 Salve 也同步成和主一样,这时因为使用了  innodb_file_per_table 的参数,所以可以切成主了.直接在主数据库中锁一下数据库

?1 SET global read_only=1;

然后查看主数据库的状态,写 binlog 到那个文件,什么位置了.

?1 SHOW master STATUS;

接着到 Slavle 的数据库上看看是不同步到一样的状态了.

?1 SHOW slave STATUS \G;

确保主从同步的一样后,先到 Slave 上停止 Slave 的复制变成新的 master,这时才方便切成主,所以要在新的 master上执行

?1234 stop slave change master to master_host=” reset slave reset maters

之所以要在新的master上执行change master to master_host=”及reset slave,主要是为了断开与老的master之间连接信息.

现在切完了,可以根据以前设置 msater 的经验来给原来的主数据库设置成这个的 slave .也可以给这个重新备份一个出来,在恢复了.

分享到:
评论

相关推荐

    Mysql InnoDB删除数据后释放磁盘空间的方法

    为了解决这个问题,一种有效的方法是在创建数据库时启用`innodb_file_per_table`选项。这个参数使得每个InnoDB表拥有独立的数据文件(.ibd),而不是存储在系统表空间(ibdata1)中。当数据被删除后,通过执行`...

    14.8 InnoDB File-Format Management

    在具体操作上,可以通过innodb_file_format配置参数来指定InnoDB创建file_per_table表空间时的文件格式。默认情况下,使用的是Antelope格式。当需要启用新特性的文件格式,如Barracuda,或者需要管理不同MySQL版本...

    MySql innodb 引擎表存储分析

    - 当启用 `innodb_file_per_table` 参数后,每个表的数据和索引将存储在单独的 `.ibd` 文件中。而其他一些元数据,如撤销 (undo) 信息和系统事务信息,则仍然存储在共享表空间内。 - 数据段 (Data Segment) 和索引...

    mysql 5.5 配置文档

    7. `table_cache` (在 MySQL 5.6 后改为 `table_open_cache`): 缓存打开的表的数量,提高查询效率。 8. `back_log`: 表示在系统拒绝新的连接之前,等待连接队列的最大长度。 9. `max_connections`: 允许的最大并发...

    InnoDB 1.1 for MySQL 5.5 User’s Guide

    - **启用压缩**:用户可以通过设置`innodb_file_per_table`参数,并指定`ROW_FORMAT=COMPRESSED`来启用表压缩。 - **配置参数**:涉及压缩的相关配置选项包括`innodb_compression_level`等,用于调整压缩程度。 - **...

    mysql_best_configuration-master--姜承尧配置5.6-5.7

    10. **innodb_file_per_table**:将每个InnoDB表的数据存储在单独的文件中,有利于空间管理和备份恢复。通常推荐开启。 以上仅是MySQL配置中的冰山一角,实际应用中还需要考虑服务器硬件、工作负载特性以及业务需求...

    mysql数据库配置模板

    7. **innodb_file_per_table**: 推荐启用此选项,可以使每个InnoDB表使用独立的数据文件,便于管理和备份。 综上所述,通过对`my.cnf`配置文件中各项参数的合理设置和优化,可以显著提升MySQL服务器的性能表现和...

    MySQL PXC_百度大神cnf配置

    - `innodb_file_per_table`: 为每个表创建单独的.ibd文件,利于空间管理和备份。 - `innodb_log_file_size`: 写入日志文件的大小,影响事务恢复速度。 配置`my.cnf`时,应考虑服务器硬件、预期负载、业务需求和...

    MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析

    3. **未启用`innodb_file_per_table`**:所有表的数据和索引都会存储在`ibdata1`,而不是单独的表空间文件。 #### 瘦身策略 1. **清理无用事务**:定期检查并结束长时间运行的事务。 2. **启用`innodb_file_per_...

    优化InnoDB表BLOB,TEXT列的存储效率

    启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效。 2、InnoDB的data page...

    my.cnf-调优

    - **innodb_file_per_table**: 每张表使用单独的.ibd文件。 #### 四、其他组件配置 - **mysqldump**: 数据库备份工具配置。 - **quick**: 使用快速模式备份。 - **max_allowed_packet**: 与服务端相同。 - **...

    MySQL各版本my.cnf配置文件.rar

    例如,MySQL 5.6到5.7再到8.0,对InnoDB存储引擎进行了许多改进,如InnoDB默认事务隔离级别由READ-UNCOMMITTED变为READ-COMMITTED,以及默认启用`innodb_file_per_table`等。 在优化配置文件时,还需要考虑服务器的...

    mysql数据库my.cnf配置文件

    # MySQL每打开一个表,都会读入一些数据到table_open_cache缓存中,当MySQL在这个缓存中找不到相应信息时,才会去磁盘上读取。默认值64 # 假定系统有200个并发连接,则需将此参数设置为200*N(N为每个连接所需的文件...

    MySQL和IO 上1

    1. innodb_file_per_table:决定每个表的数据是否独立存储为单个文件,启用此选项可以提高管理效率并减少数据迁移风险。 2. innodb_flush_log_at_trx_commit:控制redo log的刷盘策略,不同的设置平衡了性能与数据...

Global site tag (gtag.js) - Google Analytics