Linux/MySQL: Find all database using innodb engine

mysql

 

If you are the Linux administrator that using full backup of your account, you will find out that the MySQL database full backup couldn’t help to restore your database.

Hence, you might want to schedule a full mysqldump backup for those databases that using the innodb engine.

To show all of your databases that using innodb engine, you may use the following command,

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

MySQL: Duplicate MySQL database

One of the client are requesting to duplicate their database to development database in case their production database have change mistakenly.

Scenario:
– MySQL Database server
– Root password: [email protected]
– Production Database: db1
– Development Database: db2
– Development User: db_user
– Development User Password: [email protected]

To do this, I choose to use mysqldump to done it.

1. First of all, you must create the development database named db2.

mysql -u root -p'[email protected]' -e "create database db2"

2. Next, you may duplicate the database data from db1 to db2.

mysqldump -u root -p'[email protected]' db1 | mysql -u root -p'[email protected]' db2

3. Now, you need to assign the MySQL user permission to the db2(development) database.
a) Log into the database server.

mysql -u root -p'[email protected]'

b) Grant the database privilege,

mysql> GRANT ALL PRIVILEGES ON db2.* TO [email protected] ;

Host X is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

For some reason, you might get these error when you execute the MySQL query,

Host 'mickgenie.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' in /home/mickgenie/public_html/wow.php on line 25
Unable to connect to mysql server

The quick fix but not permanent solution will be running the command as below,

mysqladmin flush-hosts

To fix the issue permanently, edit the file /etc/my.cnf.

Add the following line to the /etc/my.cnf below [mysqld]

set-variable = max_connect_errors=5000

Then restart the MySQL services.

cPanel: Database size is Zero

Wonder why cPanel database size is not calculated and it is always showing zero (0) in the database section?

You may fix this by 2 options,
Option 1:
1. Log into WHM(Web Host Manager).
2. Go to Server Configuration and look for Tweak Settings.
3. Look for “Include databases in disk usage calculations” and enable it.
4. Save it.

Option 2:
1. Log into SSH.
2. Open the file named /var/cpanel/cpanel.config.
vi /var/cpanel/cpanel.config
3. Search “disk_usage_include_sqldbs” and change the value to 1.
4. Lastly, run the command as below,
/scripts/update_db_cache

Commonly used permission set in MySQL

Here is some MySQL permission set that I am normally used, I am now sharing them to you.

1. Standard step to create database and grant all permission to mickgenie_wordpress.

mysql>create database mickgenie_wordpress;
Query OK,1 row affected (0.00 sec)
mysql>grant all on mickgenie_wordpress.* to [email protected] identified by 'password';
Query OK,0 rows affected (0.00 sec)

2. Grant create view on mickgenie_user to database mickgenie_wordpress.

mysql>grant select,insert,update,delete,create,drop,index,alter,create temporary tables,lock tables ,CREATE VIEW,CREATE ROUTINE,REFERENCES,EXECUTE,ALTER ROUTINE  on mickgenie_wordpress.* to [email protected] identified by 'password';
Query OK,0 rows affected (0.00 sec)

3. Grant remote access to mickgenie_wordpress.

mysql>grant all on mickgenie_wordpress.* to [email protected] identified by 'password';
Query OK,0 rows affected (0.00 sec)

4. Grant read only user to mickgenie_wordpress.

mysql>grant select on mickgenie_wordpress.* to [email protected] identified by 'password';
Query OK,0 rows affected (0.00 sec)

Clean space from mysql-bin logs

Today, I was found out one of customer server filled up with mysql-bin log and the server var space fully utilized.

From their MySQL path, file as below have filled up,
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
….

As mysql-bin used to record the event of the MySQL databases and it is not necessary to keep the log from the first day of the databases create until the present date, hence I would suggest for maximum 30 days of logs to be kept.

To disable the mysql-bin, simply remove log-bin from the MySQL configuration file, /etc/.my.cnf or my.ini.
log-bin = mysql-bin

Otherwise, keep only 30 days of log,
expire_logs_days = 30

Display database result vertically

By default, database will showing the result according to the select entry in tables,

mysql> select * from wp_terms WHERE term_id='1';
+---------+----------+----------+------------+
| term_id | name     | slug     | term_group |
+---------+----------+----------+------------+
|       1 | Blogging | blogging |          0 |
+---------+----------+----------+------------+
1 row in set (0.00 sec)

Somehow, you could make them display in vertically.

mysql> select * from wp_terms WHERE term_id='1'\G;
*************************** 1. row ***************************
term_id: 1
name: Blogging
slug: blogging
term_group: 0
1 row in set (0.00 sec)

This could be done by just use the “\G” (without quotation) from the query.

Install pdo_MySQL manually from CentOS

As you know, MGe is missing for sometimes for iOS and Android. 🙂

Today, MGe wish to share on how to install pdo_mysql from CentOS manually.

1. Basically, you will need to compile pear and mysql from your Apache. Run the command as below to get it updated.

yum install php-devel php-pear mysql-devel httpd-devel
pecl install pdo
PHP_PDO_SHARED=1 pecl install pdo_mysql

2. Open your php.ini file and insert the line as below.

extension=pdo.so
extension=pdo_mysql.so

3. Restart the Apache services.