Archive for the MYSQL Category

How to check if InnoDB engine is enabled in MySQL

If you want to  check ,  if InnoDB is enabled on  the server ,  you can  check  this via two methods :

1>

 [email protected][#] mysqladmin variables | grep  have_innodb

If the above command show the output as below :

| have_innodb                             | YES

then it means InnoDB is enabled.

2 > [email protected][#] mysql
mysql> show engines;
If the above command shows the below output : 

+————+———+—————————————————————-+
| Engine     | Support | Comment                                                        |
+————+———+—————————————————————-+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | YES     | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | YES     | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES     | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+————+———+—————————————————————-+

then it means InnoDB is enabled.

That’s all,  easy  isn’t it.

The database prefix option has been disabled.

If you have disabled the option :"The database prefix option has been 
disabled". It cannot be enabled through the WHM and should not be 
re-enabled if any databases or database users have been created 
with database prefixing disabled." from WHM and want's to 
re-enabled it but does not find any option as to how to 
re-enable that option don't panic.
Follow the below steps to re-enable that option :
1> Login to Server via SSH as a root user
2> open this file : /var/cpanel/cpanel.config in you favorite editor
[email protected][#] vi /var/cpanel/cpanel.config
and then search for the line : database_prefix=0
and replace it with database_prefix=1
save and quit.
restart the cpanel service
[email protected][#] /etc/init.d/cpanel restart

That’s all you are done.

How to enable Mysql 3306 port for a particular IP and deny for all ?

If you want to disable the Mysql Access for all IPs except your ip then follow the below steps :

[email protected][#] vi /etc/csf/csf.conf

Then search for the line :
# Allow incoming TCP ports
and the remove the port : 3306

and also search for the line :

# Allow outgoing TCP ports
and remove the port : 3306

Save and Quit.

then open the csf.allow file

[email protected][#] vi /etc/csf/csf.allow
and add the entry as :

tcp:in:d=3306:s=10.10.10.10

And then restart the CSF service

[email protected][#] csf -r

Note : Replace 10.10.10.10 with the IP for which you want to Allow access.

That’s all you are done.