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

This article mainly introduces "the method of compressing MySQL data file". In the daily operation, I believe that many people have doubts about the method of compressing MySQL data file. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "the method of compressing MySQL data file". Next, please follow the editor to study!

If the MySQL data file is too large, can it be compressed? The answer is yes. Here is a way to compress MySQL data files, which I hope will be helpful to you.

After using MySQL for a period of time, the MySQL data file ibdata will grow greatly (mainly for the storage mode of innodb, there are a large number of insert and delete operations), is there a good way to shrink MySQL data files? Checked on the Internet, there is no very good way, only mentioned to delete and rebuild MySQL data files, actually is the backup-delete-restore method, I tried it, basically feasible, but there are still some points for attention:

1. Use dump and other tools to export data my database uses the latin1 character set, must be specified in the parameters (otherwise the default UTF8 export Chinese will change garbled code): mysqldump-- add-drop-table-- user=root-- flush-logs-- default-character-set=latin1-- password=mysql mydbname > c:\ bak_all.sql Note SQLyog and other tools also provide export similar export functions, but their export may not be easy to use, there may be garbled or can not be executed.

two。 Stop mysqld (i.e. mysql's service)

3. To delete the ibdata*, ib_logfile* file, you'd better delete it with your user database directory, otherwise an error may be reported when you import it.

4. Restart mysqld (then mysqld will automatically create the idbdata*, ib_logfile* file)

If you have previously deleted the database directory, you need to rebuild your user database (it is very easy to build a new database with tools such as SQLyog)

5. When the exported data is imported back, the volume will decrease.

You can use the command: mysql-user=root-password=mysql mydbname

< c：\bak_all.sql 　　当然，也可以在SQLyog的查询工具里执行sql文件来恢复。 　　另外，提供一个小技巧，如果你的帐号密码带有空格，在命令行怎么输入呢？ 　　很简单，在你的帐号密码两边带上英文双引号 " （注意不是单引号）。比如：mysqldump ——add-drop-table ——user=root ——flush-logs ——default-character-set=latin1 ——password=" " mydbname >

C:\ bak_all.sql

At this point, the study of "the method of compressing MySQL data files" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.