MYSQL ibdata1 space full processing, mysqlibdata1

MYSQL ibdata1 space full processing, mysqlibdata1
Disk Space cleaning of MYSQL server in Chengdu
2.7.1 project name
Chengdu Project
2.7.2 Problem Description
90% disk space
2.7.3 impact Scope
Scheduled downtime
2.7.4 Problem Analysis
The disk space of the database is full, which is caused by log files.
2.7.5 Solutions
1. Procedure:
1. Disable the MAP service.
2. Back up the database
Mysqldump-uroot-pXXXX -- quick -- routines -- add-drop-database -- all-databases -- add-drop-table>/bak/mysqldump. SQL


3. Disable the backup server Heartbeat
1) service heartbeat stop
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Secondary/Primary ds: UpToDate/UpToDate C r ----


4. Disable the master server heartbeat
1) service heartbeat stop
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Primary/Secondary ds: UpToDate/UpToDate C r ----


5. Back up mysql database files on the master server
To prevent cleanup failure and facilitate rollback, you must back up the/drbddata/mysql directory and the following files in addition to using mysqldump to export all user databases, you can delete it after the cleaning is confirmed to be successful.


Log on to the primary mysql Server
Ssh root@10.1.1.81


Disable mysql Service
Service mysql stop


Go to the drbd directory
Cd/drbddata/

Back up the mysql directory under the drbddata directory to the backup server
Scp-r mysql root@10.1.1.118:/backup

Compare the number and size of the source and target folders

6. Delete all files and directories except the mysql directory on the drbddata/mysql server.
Rm-f ibdata1
Rm-f ib_logfile *
Rm-f global/* (delete files in the corresponding database directory. If multiple databases exist, delete files in other database Directories)

7. added the innodb_file_per_table option to modify the configuration file of my. cnf on the master server.
Vi/etc/my. cnf
Add innodb_file_per_table after [mysqld]
Innodb_file_per_table = 1
8. added the innodb_file_per_table option to modify the configuration file of my. cnf on the backup server.
Vi/etc/my. cnf
Add innodb_file_per_table after [mysqld]
Innodb_file_per_table = 1

9. Enable the mysql service on the master server
Service mysql start

10. enable the master server heartbeat
1) service heartbeat start
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Primary/Secondary ds: UpToDate/UpToDate C r ----

11. Enable the backup server heartbeat
1) service heartbeat start
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Secondary/Primary ds: UpToDate/UpToDate C r ----

12. Restore the database
Use the dump file backed up by mysqldump for restoration and recovery
A. source/bak/mysqldump. SQL
Or
B. mysql-uroot-p </bak/mysqldump. SQL


13. Check whether the disk usage drops

14. verify whether the data is correct and whether there is an error log in the Database Error Log.

15. Enable the MAP service

Ii. rollback for failed cleaning:
1. Disable the backup server Heartbeat
1) service heartbeat stop
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Secondary/Primary ds: UpToDate/UpToDate C r ----

2. Disable the master server heartbeat
1) service heartbeat stop
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Primary/Secondary ds: UpToDate/UpToDate C r ----

3. restore the original/drbddata/mysql File

4. enable the master server heartbeat
1) service heartbeat start
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Primary/Secondary ds: UpToDate/UpToDate C r ----

5. Enable the backup server heartbeat
1) service heartbeat start
2) cat/proc/drbd. Make sure the status is as follows:
0: cs: Connected ro: Secondary/Primary ds: UpToDate/UpToDate C r ----

6. Enable the MAP service