解决在MySQL5.5.62下ibdata1文件过大问题

问题记录：

在安装MySQL 5.5.62不了解其特性，未在my.cnf配置文件中指明innodb_file_per_table=1开启独立表空间，导致ibdata1文件在长期运行下体积过大！

单实例MySQL

生产环境的数据库操作，请先停止项目访问，如果大型应用则应发布停机公告。建议凌晨节点执行对用户影响最小！

需要备份的数据库：back_db_test

需要备份的表:products

CREATE TABLE `products` (
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `stock` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

创建储存过程

DELIMITER //
CREATE PROCEDURE generate_test_data(IN num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_rows DO
        INSERT INTO products (name, price, stock) 
        VALUES (CONCAT('Product ', i), RAND() * 100, FLOOR(RAND() * 1000));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

执行储存过程,插入测试数据到表中

#调用存储过程生成100条数据
CALL generate_test_data(100);

停止Nginx中项目的访问


location /your_project/{
    return 403;
}

修改my.cnf配置文件，设置只读模式，防止由于其他业务系统的写入导致后续备份SQL恢复过程中报错

[mysqld]
...
innodb_force_recovery = 4
...

导出需要备份的mysql文件

执行后，当前目录会生成all-dbs.sql的文件

./mysql_dump_all_db.sh

删除infomation_schema、performance_schema、mysql之外的其他数据库

停止mysql数据库

service mysqld stop;

修改my.cnf配置文件

#[mysqld]下增加下面配置 
innodb_file_per_table=1

删除ibdata1和ib_logfile*日志文件

mysql数据目录:/tgdata/mysql-5.5.62/data

rm -rf ibdata1
rm -rf ib_logfile*

启动mysql数据库

service mysqld start;

还原数据库备份文件

mysql -uroot -p'123456' < all-dbs.sql

主从MySQL

附件

shell脚本工具：mysql_dump_all_db.sh

默认排除mysql、information_schema和performance_schema这3个数据库

--socket 按my.cnf实际配置修改路径

#!/bin/sh

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

#
# Collect all database names except for
# mysql, information_schema, performance_schema and sys
#

SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema','sys','temp')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --lock-all-tables"
mysqldump --socket=/var/lib/mysql/mysql.sock ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql

exit