Tips for compressing MySQL Data Files

If the MySQL data file is too large, can it be compressed? The answer is yes. The following describes how to compress MySQL data files.
After using MySQL for a period of time, the MySQL Data File ibdata will grow significantly (mainly for innodb Storage, there are a large number of databases for insertion and deletion operations ), is there a good way to contract MySQL Data Files? I checked on the Internet and found that there is no good way. I only mentioned the method of deleting and recreating MySQL data files. The actual method is backup-delete-Restore. I tried it and it is basically feasible, but there are still some precautions:
1. using tools such as mysqldump to export data my database uses the latin1 character set and must be specified in the parameter (otherwise, the Chinese language exported by UTF8 will become garbled by default ): mysqldump -- add-drop-table -- user = root -- flush-logs -- default-character-set = latin1 -- password = mysql mydbname> c: \ bak_all. SQL note that SQLyog and other tools also provide export-like functions, but their export is not necessarily easy to use, and may be garbled or cannot be executed after being exported.
2. Stop mysqld (mysql Service)
3. Delete the ibdata * And ib_logfile * files. You 'd better delete them together with your user database directory. Otherwise, an error may be returned during import.
4. Restart mysqld (then mysqld will automatically create the idbdata * And ib_logfile * files)
If you have deleted the database directory, You need to recreate a user database (it is very easy to create a database using SQLyog and other tools)
5. Import the exported data back to reduce the volume.
Run mysql -- user = root -- password = mysql mydbname <c: \ bak_all. SQL
Of course, you can also execute the SQL file in the SQLyog query tool to restore.
In addition, we provide a tip: If your account password contains spaces, how can I enter it in the command line?
It is easy to put double quotation marks on both sides of your account and password "(note that it is not a single quotation mark ). For example, mysqldump -- add-drop-table -- user = root -- flush-logs -- default-character-set = latin1 -- password = "" mydbname> c: \ bak_all. SQL