Shulou(Shulou.com)05/31 Report--

This article shows you how to change the table space storage mode of mysql from a shared table space to a single table, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Students who have used MySQL are first exposed to the MyISAM table engine, whose database creates three files: table structure, table index, and table data space. We can migrate a database directory directly to another database or it will work. However, when you use InnoDB, everything changes. By default, InnoDB stores all the table data of the database InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. When adding or deleting a database, the ibdata1 file will not automatically shrink, and the backup of a single database will become a problem. Usually the data can only be exported using mysqldump and then imported to solve this problem.

In the [mysqld] section of the configuration file of MySQL, add the innodb_file_per_table parameter to change the InnoDB to an independent tablespace schema, and each table in each database generates a data space.

Independent tablespace

Advantages:

1. Each table has its own independent table space.

two。 The data and indexes of each table are stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be reclaimed (tablespaces can not be automatically recycled in drop/truncate table mode)

5. For tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of the tablespace will not seriously affect performance, and there is still a chance to deal with it.

Disadvantages:

The increase of a single table is greater than that of shared space.

Conclusion:

Shared tablespaces have some advantages in Insert operations, but nothing else performs as well as independent tablespaces.

When independent tablespaces are enabled, adjust the innodb_open_files parameters reasonably.

Here is a practical solution to the excessive history history of the MySQL database of online Zabbix, which leads to the excessive size of the ibdata1 file.

1. View file size

$sudo cd / var/lib/mysql

$ls-lh

Total 14G

-rw-r--r-- 1 root root 0 Dec 1 14:31 debian-5.1.flag

-rw-rw---- 1 mysql mysql 5.0M Jan 17 21:31 ib_logfile0

-rw-rw---- 1 mysql mysql 5.0M Jan 17 21:29 ib_logfile1

-rw-rw---- 1 mysql mysql 14G Jan 17 21:31 ibdata1

Drwx- 2 mysql root 4.0K Dec 1 14:31 mysql

-rw-rw---- 1 root root 6 Dec 1 14:31 mysql_upgrade_info

Drwx- 2 mysql mysql 4.0K Jan 17 21:29 zabbix

The size of the shared table data space file ibdata1 has reached 14G.

Log in to MySQL to see which tables take up space

$mysql-uroot-p

Mysql > select table_name, (data_length+index_length) / 1024 plus 1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix'

+-- +

| | table_name | total_mb | table_rows | |

+-- +

| | acknowledges | 0.06250000 | 0 | |

....

| | help_items | 0.04687500 | 103 |

| | history | 9678.00000000 | 123981681 | |

| | history_log | 0.04687500 | 0 | |

...

| | history_text | 0.04687500 | 0 | |

| | history_uint | 5386.98437500 | 57990562 | |

| | history_uint_sync | 0.04687500 | 0 | |

...

| | timeperiods | 0.01562500 | 0 | |

| | trends | 54.54687500 | 537680 | |

| | trends_uint | 100.53125000 | 1035592 | |

...

103 rows in set (1.46 sec)

As you can see, the number of records in the history table has reached 123981681, or 120 million, while the history_unit is relatively large, reaching 5G, about 60 million.

In addition, there is also some data in trends,trends_uint.

Because of the large amount of data, it is basically impossible to delete data in the normal way.

Because we automatically send data reports every day, we decided to use truncate table to quickly empty the data of these tables, then use mysqldump to export the data, delete the shared tablespace data file, and re-import the data.

two。 Stop related services and avoid writing data

$sudo / etc/init.d/zabbix-server stop

$sudo / etc/init.d/apache2 stop

3. Emptying historical data

$mysql-uroot-p

Mysql > use zabbix

Database changed

Mysql > truncate table history

Query OK, 123981681 rows affected (0.23 sec)

Mysql > optimize table history

1 row in set (0.02 sec)

Mysql > truncate table history_uint

Query OK, 57990562 rows affected (0.12 sec)

Mysql > optimize table history_uint

1 row in set (0.03 sec)

Mysql > truncate table trends

Query OK, 537680 rows affected (0.04 sec)

Mysql > optimize table trends

1 row in set (0.02 sec)

Mysql > truncate table trends_uint

Query OK, 1035592 rows affected (0.02 sec)

Mysql > optimize table trends_uint

1 row in set (0.01 sec)

4. Backup data

$mysqldump-uroot-p zabbix > ~ / zabbix.sql

5. Stop the database

$sudo stop mysql

6. Delete shared tablespace data files

$cd / var/lib/mysql

$rm ib*

7. Add innodb_file_per_table parameters

$sudo vim / etc/mysql/my.cnf

Set under [mysqld]

1 innodb_file_per_table=1

8. Start MySQL

$sudo start mysql

9. Check whether the parameter is in effect

$mysql-uroot-p

Mysql > show variables like'% per_table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_per_table | ON |

+-+ +

1 row in set (0.00 sec)

10. Re-import data

$mysql-uroot-p zabbix < ~ / zabbix.sql

11. Write a script to automatically clean up the data every day and keep the data for 30 days.

$sudo vim / etc/cron.daily/clean_zabbix_olddata.sh

View source

Print?

#! / bin/bash

DATE= `date-d "30 days ago" `

CLOCK= `date +% s-d "${DATE}" `

MYSQL= "mysql-uroot-p zabbix"

For TABLE in history trends

Do

$MYSQL-e "DELETE FROM ${TABLE} WHERE clock < ${CLOCK};"

$MYSQL-e "OPTIMIZE TABLE ${TABLE};"

$MYSQL-e "DELETE FROM ${TABLE} _ uint WHERE clock < ${CLOCK};"

$MYSQL-e "OPTIMIZE TABLE ${TABLE} _ uint;"

Done

twelve。 Finally, resume the related service process

$sudo / etc/init.d/zabbix-server start

$sudo / etc/init.d/apache2 start

The above is how mysql changes from a shared table space to a single table. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.