MySQL partition is 100% full, MySQL won?t operate

Document ID : KB000044691
Last Modified Date : 14/02/2018
Show Technical Document Details

 

Symptoms:

 

Mysql partition is 100% full, mysql won’t operate

MySQL partition can fill up for few reasons. Two of them are:

1.        Audits filling up DB, DB grows and MySQL partition get 100% full

2.       Due to replication being broken, bin logs will grow faster, and fill up the space.

 

Now,  for both of these we have a solutions:  manage bin log and manage audit script to keep it under control.

If you don’t monitor the system, or those scripts fail due to cron-tab username password expired .. things can go wrong and you can run into a issue like below

 

/dev/mapper/vg00-lv_db

 21G 20G 0 100% /var/lib/mysql

 

Environment:

RED HAT 5/6

VM appliance or hardware appliance

 

Cause:

1.       Audits filling up DB, DB grows and MySQL partition

2.       Due to replication being broken, bin logs will grow faster, and fill up the space.

 

 

Resolution/Workaround:

If you catch it on time you can run through the steps on stopping replication than replication rebuild. If you are too late, where MySQL won’t start,  than you will not be able to stop replication so you are going to manually remove bin and relay logs and then reset index files.

 

On both nodes in the cluster

1.       Stop the MySQL on primary DB node and secondary DB node  service mysql stop

2.       Remove the binary and relay log files from primary and secondary:

 

find /var/lib/mysql -type f -regextype posix-extended -regex ".*[0-9]{6}" -exec rm  {} \;

>just to make it clear .. Logs you are removing are located here: /var/lib/mysql/ and they look like this<

-rw-rw---- 1 mysql mysql 524288827 Jul  1 02:46 ssgbin-log.000001

-rw-rw---- 1 mysql mysql  56779582 Jul  1 09:04 ssgbin-log.000002

-rw-rw---- 1 mysql mysql       197 Jun 30 05:02 ssgrelay-bin.000003

-rw-rw---- 1 mysql mysql       254 Jun 30 05:02 ssgrelay-bin.000004

 

NOW

3.       Reset the binary log index: cat /dev/null > /var/lib/mysql/ssgbin-log.index

4.       Reset the relay log index:   cat /dev/null > /var/lib/mysql/ssgrelay-log.index

                                                        cat /dev/null > /var/lib/mysql/ssgrelay-bin.index

                                                        cat /dev/null > /var/lib/mysql/ssgrelay-bin.info

5.       Start the MySQL on primary DB node and secondary DB node  service MySQL start

6.       Now you can proceed to stop replication and rebuild.

 

STOP REPLICATION

1)             Backup the primary database. For safety, I would back up the secondary database as well. In other words, run this command on both nodes:

[root@host ~]# mysqldump --all-databases | gzip > ~/all.sql.gz

2) Stop slave replication on both nodes.

[root@host ~]# mysqladmin stop-slave

3) Reset the master/slave configuration on both nodes.

[root@host ~]# mysql -e "reset master"

mysql -e "reset slave all"

 

REBUILD REPLICATION

Service SSG STOP

4) Execute the create_slave.sh script on the SECONDARY node.

[root@host2 ~]# /opt/SecureSpan/Appliance/bin/create_slave.sh

5) When prompted, provide the fully qualified domain name of the PRIMARY node.

6) Accept the request to drop the local database and copy the database from the primary node.

7) Execute the create_slave.sh script on the PRIMARY node.

[root@host1 ~]# /opt/SecureSpan/Appliance/bin/create_slave.sh

8) When prompted, provide the FQDN of the SECONDARY node.

9) Decline the request to drop the local database.

Service SSG START

10) Query the status of replication.

[root@host ~]# mysql -e "show slave status\G"

11) Verify both nodes return the following lines:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0