Configuration of MySQLInnodb independent tablespace

Mysqlinnodb standalone tablespace and shared tablespace. The standalone tablespace puts the data and table files of each table together. The shared tablespace stores the data of all databases in the ibdate1 file.

The standalone and shared tablespaces of mysql innodb are the data and table files of each table. The shared tablespace stores the data of all databases in the ibdate1 file.

No experience is terrible.

The project started to run in July. Now the statistical tables in the database are very large, and sometimes the leaders want you to add fields to these statistical tables. Wow, that's a headache, even if we delete data during each project upgrade every month, the data in those statistical tables in a month also reaches 10 million. It hurts! On Friday, the project was upgraded and stuck on the big data. It was really slow to add those fields, so we simply killed all the data!

Configure the database as an independent tablespace:

1. Check that:

Mysql> show variables like '% per_table % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Innodb_file_per_table | OFF |
+ ----------------------- + ------- +
1 row in set (0.00 sec)

Note: OFF indicates that mysql is a shared tablespace, that is, data in all databases is stored in an ibdate1 file.

Directory structure for installing mysql with rpm

Database Directory:/var/lib/mysql/

Configuration File:/usr/share/mysql (mysql. server command and configuration file)

Related commands:/usr/bin (mysqladmin, mysqldump, and other commands) (* secure mysql Startup Mode:/usr/bin/mysqld_safe -- user = root &)

Startup Script:/etc/rc. d/init. d/

2. Stop the mysql server:

Mysql installed in rpm Mode
[Root @ localhost ~] #/Etc/rc. d/init. d/mysqld stop
[Root @ localhost ~] #/Etc/init. d/mysqld stop

3. Modify the my. cnf file: Add the following sentence to the mysqld file of my. cnf:

I cannot find it because it is rpm installation. Copy the my-*. cnf file from the mysql configuration file directory to the etc directory and change it to my. cnf.

Innodb-file-per-table = 1

4. Start mysql

[Root @ localhost ~] # Service mysql start
Or
[Root @ localhost ~] #/Etc/init. d/mysqld start

5. Check again

Mysql> show variables like '% per_table % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Innodb_file_per_table | ON |
+ ----------------------- + ------- +
1 row in set (0.00 sec)

Summary: The standalone and shared tablespaces of mysql innodb put the data and table files of each table together. The shared tablespace stores the data of all databases in the ibdate1 file. The Internet says that you delete the data and it will not contract. That is to say, if you delete the table data if the file is 40 GB, this file is still 40 GB, which is terrible, so we replace it with an independent tablespace. In addition, although independent, I personally think it is not very thorough. I copied those files to another database, only tables and no data, so it is not thorough!