Category: CentOS

MySQL InnoDB, one file problem

I installed new server about 6 months ago and now found problem with MySQL InnoDB. I add and delete a few big InnoDB tables, but space of InnoDB still grow. I found a lot ideas how to fix it (dump all DB and restore back), but the best way is to add to MySQL config file “innodb_file_per_table”.

TODO:

  • Stop the MySQL server (usually “/etc/init.d/mysql stop” on Linux).
  • Edit the my.cnf file (located at somewhere like /etc/my.cnf or /etc/mysql/my.cnf)
  • Add “innodb_file_per_table” on a single line by itself into the my.cnf file.
  • Start the MySQLserver (“/etc/init.d/mysql start”).

Any new InnoDB tables that are created will now be created in the database’s directory with a single file per database table. Doing this does not affect any existing InnoDB tables they will still use the common data file.

How to edit 5.3 GB file over SSH

I have problem with InnoDB on mine server so I dumped all files and want to change in SQL file table type from InnoDB to MyISAM, but can’t because file size is 5.3 GB. No one editor can’t edit so big file over SSH, so I found amazing solution for that:

1

split -b 10000k mysqldump.sql

You split one file by pieces, then edit piece what you want and when can make one file, by this command:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

cat xa* > mysqldump.sql
 
SYNOPSIS
       split [OPTION]... [INPUT [PREFIX]]
 
       -a, --suffix-length=N
              use suffixes of length N (default 2)
 
       -b, --bytes=SIZE
              put SIZE bytes per output file
 
       -C, --line-bytes=SIZE
              put at most SIZE bytes of lines per output file
 
       -d, --numeric-suffixes
              use numeric suffixes instead of alphabetic
 
       -l, --lines=NUMBER
              put NUMBER lines per output file

Hope that idea helps you too!

CentOS 6 + ISPConfig 3!

I change old server from: 1 core x 2 Ghz Xeon, 10GB HDD, 1GB RAM (CentOS 6 + webmin) to new one: 2 cores x 2Ghz Xeon, 50GB HDD, 3 GB RAM (CentOS 6 + ISPConfig) and everything works incredible fine.

CentOS 6 + ISPConfig 3 intallation is soo easy, like 2+2: http://www.howtoforge.com/perfect-server-centos-6.0-x86_64-ispconfig-3

Some system configuration:
* “/usr/local/ispconfig/server/conf” – in php-fcgi-starter.master dropped PHP_FCGI_MAX_REQUESTS or modifications in: /etc/httpd/conf.d/fcgid.conf add: FcgidMaxRequestLen 59931072or more.

* vi /etc/named.conf – allow-query     { any; }; change to: allow-query     { localhost; };

Some interesting bugs:
* “Warning: include(): open_basedir restriction in effect. File() is not within the allowed path(s)? File()” – is empty value, wtf? You can turn off only in default php-fcg-starter.master file or in:/var/www/php-fcgi-scripts/web/.php-fcgi-starter. Apache AllowOveride All and .htaccess with php_admin_value open_basedir none not helps!

* “Fail2Ban Logs” (need some modification in /etc/fail2ban/fail2ban.conf edit log target param to: logtarget = /var/log/fail2ban.log);

Problems later:
* [ERROR] Can’t find messagefile ‘/usr/share/mysql/english/errmsg.sys’ – after one month, helps: yum –enablerepo=remi,remi-test reinstall mysql-libs

All other services now works fine about ~one month. Client friendly interface, easy usage.

Highly recommend for small business or home users!