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

WordPress migration with SSH

Scenario:
Old Site: www.domain.com/blog
Path: /home/domain/public_html/blog
New Site: www.domain.com (Assume the site is empty)
Path: /home/domain/public_html

In order to move the WordPress account manually without any plugin, you may done it through SSH without any trouble.

Folder/File
1.  Log into the SSH.
2.  Copy the folder and file from blog folder to main site.

cp -apr /home/domain/public_html/blog /home/domain/public_html

3. If you using Permalink and having mod_rewrite rule, you may edit your .htaccess file,

vi /home/domain/public_html/.htaccess

4. Change the rewrite rule from,

RewriteEngine On
RewriteBase /blog/
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /blog/index.php [L]

to

RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]

Database:
1.  Dump the database. Assume your database named wordpress.

mysqldump wordpress > wordpress.sql

2. Change the entry from www.domain.com/blog to www.domain.com.

sed -i 's/www\.domain.\com\/blog/www\.domain\.com/g' wordpress.sql

3. Restore the database back to the domain.

mysql wordpress < wordpress.sql

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.

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.

Ruby on Rails Handy Guide

Some Useful command
To install ruby Gem,
gem install [gem_name]
eg, gem install rails

To install ruby Gem with certain version,
gem install [gem_name] –version=x.x.x
eg, gem install rails –version=2.3.5
eg, gem install rails -v=2.3.5

To update ruby Gem,
gem update [gem_name]
eg, gem update mysql

To update ruby Gem with latest version(for all)
gem update –system

To check missing gem
rake gems:install (must run from rails apps folder)

To install gem with lesser space
gem install –no-rdoc –no-ri [gem_name]
eg. gem install –no-rdoc –no-ri rails

To check RoR log,
Path: /home/[cpanel_username]/etc/rails_apps/[apps_name]/log

Continue reading Ruby on Rails Handy Guide