A MySQL single table query SQL, causing a "story"

I. description

The story goes like this. The MySQL database has a large table of more than 40 gigabytes. The developer executes a single-table query, and as a result, I receive an alarm that the disk space is full. All of a sudden, I didn't calm down. I thought about all kinds of things in my mind who were in trouble, and what I thought most was that ibtmp1 exploded. I looked at it on the first timeline and found the largest file, and the result was the same as I thought. Here we need to see how SQL is written and what the table structure looks like, and then compared with the official website, MySQL may use disk temporary tables when querying, including table joins, sorting, large fields, and so on.

II. Solutions

Temporary solution:

1. Expand the hard drive temporarily

2. Restart directly

Terminating the solution

Procedure:

1. Check the database status

Ps-ef | grep mysql

2. Check the status of the master and backup database

Show slave status\ G

3. Check the database shutdown parameter, which is required to be 1

Show variables like 'innodb_fast_shutdown'

4. Turn off the synchronization process

Stop slave

5. Close the database

Shutdown

6. Set database temporary tablespace parameters

# / etc/my.cnf

Innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

7. Start database service and synchronize threads

Mysqld_safe-- defaults-file=/etc/my.cnf &

Start slave

Show slave status\ G

8. Check database temporary tablespace parameters

Show variables like 'innodb_temp_data_file_path'

9. View the ibtmp1 file size

Du-sh ibtmp1

Third, Xiao Jie

1. Never underestimate a select xxx from tab; if you don't know what you're doing and the risks, don't hit any instructions in the production system.

2. Be in awe.