Archive for the MYSQL Category

Encrypting MySQLDump

If  you are looking to Encrypt your MySQL database dump for security reasons than you can  use the ccrypt tool  to encrypt your dump.sql file.

Follow the below simple steps to  achieve the database dump encryption and decryption :

  1. Create a  encryption key file as /root/.backup_keyfile and add the password/key in  that file
  2. This keyfile  will be used to  encrypt and decrypt the data [dump.sql file]

Now,  that we have created the encryption  file we just need to  execute the below commands :

      mysqldump --opt  opsview |  ccrypt  -k /root/.encryptkeyfile > opsview.sql


This will encrypt the opsview.sql file and to  decrypt the opsview.sql file use :

[[email protected] ~]# cat opsview.sql | ccrypt -d -k /root/.backup_keyfile > opsview1.sql


This will  decrypt the opsview.sql file, easy  isn’t it 😀



List all cPanel users having Wildcard Remote Mysql Access

Remote MySQL option in cPanel allows other webservers to access the MySQL database on your server remotely.

Remote MySQL option can be very handy, if  you want  to allow applications like shopping carts or guestbooks on your other servers to access the databases.

But on the contrary, if the Remote MySQL option is not handled with  care  then  it can lead to database hacks.

So, If you want to find out all cPanel users having Wildcard Access enabled then  you can  use the below simple command ::


[email protected][#]mysql -N  mysql -e "Select User from user where Host='%'" | awk  -F _ '{print $1}' | uniq

That’s all easy isn’t it.

List all databases using Innodb storage engine

While working on a server migration,  I need  to found all databases running on Innodb engine to migrate  them over to  a new server.

So, thought of  sharing  the command to get the list of all innodb databases, if in case anyone requires it. 🙂


Below is the command ::


[email protected][#] mysql -N mysql -e "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" | awk '{print $1}' | sort | uniq

That’s all, easy isn’t it.