MySQL5.7开启独立表空间参数innodb_file_per_table【原创】

今天在线上某个系统发现MySQL数据库使用的是共享表空间，想修改为独立表空间，操作如下：

#因为是主从结构，在从库修改测试，先关闭binlog
SET SQL_LOG_BIN=0;
show variables like "SQL_LOG_BIN";
show global variables like "innodb_file_per_table";
set global innodb_file_per_table=1;
show global variables like "innodb_file_per_table";
SET SQL_LOG_BIN=1;

#编辑配置文件
vi /etc/my.cnf
innodb_file_per_table=1

 

改为参数后进入datadir发现还是共享表空间。

这是因为动态修改后仅对后续操作生效，如原来为共享表空间，动态修改为独立表空间后仅新建的表、或修改旧表结构时才会生效为独立表空间

经过生产系统的实践，如果要想改成每个表数据都是独立表空间。

1、先对数据库进行全备包括触发器、存储过程等，以免影响业务使用。

2、最好生产库有从库，或者其他高可用架构，这样方便切换不影响业务。我负责的数据库是keepalived+MySQL主主复制

试验环境

A：192.168.26.63（以下简称63）

B：192.168.26.64（以下简称64）

1、63、64上的停止复制，先将vip飘到63上，做64数据库的操作

stop slave;
reset slave all;

2、停止数据库服务

service mysqld stop
修改配置文件
vi /etc/my.cnf
innodb_file_per_table = 1

3、启动数据库

service mysqld start
验证参数
show global variables like '%per_table%';
show variables like '%per_table%';

# 按照网上查的删除ibdata1文件和日志，这步有问题的，删除数据文件是mysql是无法启动的。所以下面我的操作是重新初始化数据库重建。
rm -rf ibdata1
rm -rf ib_logfile*


4、直接初始化数据库

mysqld --defaults-file=/etc/my.cnf --initialize

5、还原数据库

source all_mysql.sql
查看数据库文件是否分开

mysql表导入完成后，要刷新一下才能生效
flush privileges;

6、64上重新配置主从复制

CHANGE MASTER TO MASTER_HOST='192.168.26.63',
MASTER_USER='backup',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=588785830 FOR CHANNEL '633306';

 

7、确认数据主从同步后，同样的方法在切换keepalived vip至64，做63的数据库。

注意：切换keepalived时可能会影响业务数据读写，请提前和业务沟通好。本次变更，有得java应用定时任务执行失败，重启java应用后恢复。

此次完成 每个表数据都是独立表空间操作后，发现用pt-table-sync校验主从同步时也正常了，之前共享表空间时运行pt-table-sync时会造成数据库无法使用，即使加了no-lock参数也影响数据库，看来和独立表空间也有关系。