How to Install Redmine 1.4 with cPanel 11.32

The reason I repost this article is because my previous article is old enough and I still can see the demand on this task.

Requirement:
– Ruby On Rails 1.8.x enabled, please downgrade to 1.8.7, 1.9.2 or 1.9.3 if your are running higher than that. To check the Ruby version, type “ruby -v” from shell.
– Shell Access(SSH) enabled
– MySQL Enabled
– Rubygems <= 1.8

Pre-Step:
1. Install gem rails version 2.3.14(exact version), from SSH shell, run

gem install rails -v=2.3.14

2. Install gem rack version 1.1.3, from SSH shell, run

gem install rack -v=1.1.3

3. Install gem rubygems 1.8.23(or higher version, not >= 1.8), from SSH shell, run

rubygems-update

If it is not working, refer to http://www.mickgenie.com/how-to-update-rubygems/.
4. Create MySQL database from your cPanel and I assume that you are using the database as detail below.
Database Name: mickgenie_redmine
Username: mickgenie_redmine
Password: StR0NgPa$$w0Rd

Installation Step:
1. Log into the cPanel, click on Ruby On Rails.
2. In the App Name, enter Redmine, environment session select Production and create the App.
3. Click on Create Rewrite button and enter the subdomain or path that needed, then save it.
4. Download Redmine from RubyForce.
5. Upload through File Manager according to the path that you created from the Ruby On Rails.
6. Extract the file to the RoR Apps.
7. Install bundler gem by enter,

gem install bundler

8. Now, run the following command to install all the required gem from Redmine,

bundle install --without development test postgresql

9. Rename the in config/database.yml.example to config/database.yml.
10. Open the database.yml file and look for the production section.
11. Modify the production section as detail below.
production:
adapter: mysql
database: mickgenie_redmine
host: localhost
username: mickgenie_redmine
password: StR0NgPa$$w0Rd

(These information is create as above).
12. Create session store secret.

rake generate_session_store

13. Create the database structure.

RAILS_ENV=production rake db:migrate

14. Create default data.

RAILS_ENV=production rake redmine:load_default_data

15. You have done the installation, just go to cPanel and start the mongrel.

Final Step:
You may now log into Redmine with detail below,
Username: admin
Password: admin

Linux Shell Script to backup MySQL database to remote server

Database is the most important file other than web file, hence the backup is very important for the web developer and web owner.

By referring to the current post, you may backup your database automatically from your Linux box.

###START###

#!/bin/bash

# Specify the temporary backup directory
BKUPDIR=”/home/mickgenie/tmp”

# Database Name
dbname=”dbname_here”
dbuser=”user_here”
dbpasswd=”password_here”

# store the current date
date=`date ‘+%Y-%m-%d’`

# Specify Ftp details
ftpserver=”FtpServerIP”
ftpuser=”username”
ftppass=”password”

# Dump the mysql database with the current date and compress it.
/usr/bin/mysqldump -u$dbuser -p$dbpasswd $dbname | gzip > $BKUPDIR/$date.$dbname.sql.gz

# Change directory to the backup directory
cd $BKUPDIR

# Upload the backup
ftp -n $ftpserver <<!EOF!
user $ftpuser $ftppass
binary
prompt
mput *.sql.gz
quit
!EOF!

# Remove the local backup file
/bin/rm -f /$BKUPDIR/$date.$dbname.sql.gz

###END###

With the above script, you may save it as file.sh and just schedule a cronjob to run it as your needed time.

Restore Innodb MySQL data

Today I am trying to get the data from MySQL Innodb type but it failed to get the data when I directly dump it from the server as it is crashed.

Somehow, I am trying to get the data from the backup available from the offline server which come with physical file only and this is the reason I need to setup another fresh MySQL database to get the data.

To done it, you may follow the step as below.
1. Restore the database table data with the database name and ibdata[number].
eg. database folder and ibdata1 file.
2. Copy these folder and file to the database server.
3. Dump the database with the mysqldump command.
mysqldump [database_name] > database.sql

During the restoration, I’m actually getting error as below.
======================
[ERROR] Plugin ‘InnoDB’ init function returned error.
[ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported table type: INNODB
[ERROR] Aborting
======================

With the above error, it is because I have restored the ib_logfile[number] file too. To fix it, simply remove all file named ib_logfile[number].

How to install Redmine with cPanel

Although Redmine do provide the installation guide as URL as below, however it might not work if you are using cPanel.
http://www.redmine.org/wiki/redmine/RedmineInstall

With this article, you may get a complete guide to install Redmine to cPanel server with Ruby on Rails running.

Requirement:
– Ruby On Rails 1.8.x enabled, please downgrade to 1.8.x if your are running higher than that. To check the Ruby version, type “ruby -v” from shell.
– Shell Access(SSH) enabled
– MySQL Enabled

Pre-Step:
1. Install gem rails version 2.3.5(exact version), from SSH shell, run “gem install rails -v=2.3.5”.
2. Install gem rack version 1.0.1(or higher version), from SSH shell, run “gem install rack”.
3. Install gem rubygems 1.3.1(or higher version), from SSH shell, run “rubygems-update”. If it is not working, refer to http://www.mickgenie.com/how-to-update-rubygems/.
4. Create MySQL database from your cPanel and I assume that you are using the database as detail below.
Database Name: mickgenie_redmine
Username: mickgenie_redmine
Password: StR0NgPa$$w0Rd

Installation Step:
1. Log into the cPanel, click on Ruby On Rails.
2. In the App Name, enter Redmine, environment session select Production and create the App.
3. Click on Create Rewrite button and enter the subdomain or path that needed, then save it.
4. Download Redmine from RubyForce.
5. Upload through File Manager according to the path that you created from the Ruby On Rails.
6. Extract the file to the RoR Apps.
7. Log into the SSH, run “rake gems:install” from the Ruby Apps path, for more information, please refer to URL at here.
8. Rename the in config/database.yml.example to config/database.yml.
9. Open the database.yml file and look for the production section.
10. Modify the production section as detail below.
production:
adapter: mysql
database: mickgenie_redmine
host: localhost
username: mickgenie_redmine
password: StR0NgPa$$w0Rd

(These information is create as above).
11. Run “rake generate_session_store” to create session store secret.
12. Next, run “RAILS_ENV=production rake db:migrate” to get the database structure.
13. Then you may run “RAILS_ENV=production rake redmine:load_default_data” to get the default data.
14. You have done the installation, just go to cPanel and start the mongrel.

Final Step:
You may now log into Redmine with detail below,
Username: admin
Password: admin

For Redmine 1.4, please refer to the URL as below,
http://www.mickgenie.com/how-to-install-redmine-1-4-with-cpanel-11-32/

SQL: Select

Assume that we have a table named MyTable and it contain 4 column named ID, Name, Date and City.

Today, I will show some SQL Select command that might useful to SQL Administrator daily task.

1) SELECT * FROM MyTable – Returns all columns/Rows from MyTable
2) SELECT * FROM MyTable WHERE ID=2 – Returns the row(s) which ID has value 2
3) SELECT * FROM MyTable WHERE ID BETWEEN 10 and 20 – Return all row(s) between 10 and 20, this result includes 10 and 20
4) SELECT * FROM MyTable WHERE City IN (‘KL’,’Penang’,’Johore’) – Returns all row(s) which city is KL, Penang, Johore
5) SELECT * FROM MyTable WHERE Name LIKE ‘A%’ – Return all row(s) where the name starts letter A
6) SELECT * FROM MyTable WHERE Name LIKE ‘%A’ – Return all row(s) where the name ends letter A
7) SELECT * FROM MyTable WHERE Name LIKE ‘[ABC]%’ – Return all row(s) of name start with A / B / C
8) SELECT * FROM MyTable WHERE Name LIKE ‘[^ABC]%’ – Return all row(s) of name not start with A and B and C
9) SELECT (Name+space(1)+City) AS Address FROM MyTable – Returns single column address, name and city added together with a space
10) SELECT * FROM MyTable WHERE Name IS NULL – Return all row(s) which Name has null values
11) SELECT * FROM MyTable WHERE Name IS NOT NULL – Return all row(s) which Name has not null values
12) SELECT * FROM MyTable Order By ID Desc – Sort the result set descending order, Asc or not using any sort Ascending order
13) SELECT ‘Visual Studio’ as IDE, ‘2010’ as Version – Creating memory resident result set with two columns[IDE and Version]
14) SELECT Distinct ID FROM MyTable – Returns unique rows based on ID
15) SELECT TOP 10 * FROM MyTable – Return 10 customers randomly
16) SELECT getdate() – Shows the current date
17) SELECT db_name() – shows the database name which you are working on
18) SELECT @@Servername – Shows name of the server
19) SELECT serverproperty (‘Edition’) – You can pass following ServerName, Edition, EngineEdition, ProductLevel to get current information about the server
20) SELECT user_name() – Get current user
21)  SELECT *  into #test FROM MyTable  – Create temporary table #test and insert all records FROM MyTable
22)  SELECT Max(ID) FROM MyTable – Returns Maximum ID FROM MyTable
23)  SELECT * FROM MyTable Compute Max(ID) – Returns two result sets – getting all rows and maximum value of ID

MySQL Useful Command

If you are the database administrator, today post will help you to mange your server easily.

mysqladmin ping
-check whether the server is alive

mysqladmin status
-display a short server status message.

mysqladmin variable
-display the server system variables and their value.

mysqladmin proc stat
-all command shortened

mysqladmin processlist
-Shows active mysql connections and queries

mysqladmin drop database_name
-Dropsdeletes the selected database

mysqladmin create database_name
-Creates a mysql database

mysql -u username -p password databasename < databasefile.sql
-Restores a MySQL database from databasefile.sql

mysqldump -u username -p password databasename > databasefile.sql
-Backup MySQL database to databasefile.sql

Restore MySQL database with MySQLdump

MySQL database is one of the famous database engine used from the IT world.

To you might have a question such as how to move the current hosted database to another server? Actually it is very easy through the command line.

You may refer to the step as below to restore your database file.

Restore database with password.

mysql -h [remote_IP] -u [username] -p [password] [database_name] < [database_name].sql

Restore database without password

mysql -h [remote_IP] -u [username] [database_name] < [database_name].sql

Restore database from the remote server(you’ve logged into the server that wish to restore database)

mysql -u [username] [database_name] < [database_name].sql

How to repair your MySQL databases

If you are using MySQL databases as your database engine, you surely ever get the error message as below if your database grown day by day.

Table “table” is marked as crashed and should be repaired.

To understand MySQL database table with MyISAM type, you will have .frm, .MYI and .MYD type, so the data stored from MyISAM type is storing to .MYI file extension.

To repair the database, open your SSH Console, type the following command.

myisamchk -o /path_to_mysql/database_name/table_name.MYI

where the table_name is the table name.

If you got tmp drive too low, you may add the following command from the my.ini or my.cnf.

[mysqldump]
tmpdir = /home/mysql/tmp

assume you assign the path to /home/mysql/tmp/.

Your website shall working without any issue now.

Schedule backup for MSSQL to remote computer

If you familiar with MSSQL maintenance plan and ever configured backup through this tool and you should know that to backup the MSSQL database to remote computer, you have to configure Log On setting from services.

Okay, today we assume that we couldn’t allow to change the setting from Log On setting and wish to set the schedule backup, we could use maintenance tool and batch command to schedule it.

Firstly, create a maintenance plan from MSSQL, then select “Back Up Database Task”. From Database(s) option, select your database name, you may specify the directory that wish to backup. From this article we make it to C:\mssql\backup\.

Next, create a batch file named mssql_backup.bat and insert command as below.

xcopy C:\mssql\backup "\\remote_server\c$\backup" /e /i /h /y

Where remote_server is your remote server name and you would like to place it to C:\backup.

Lastly, create Windows Task Scheduler and configure the mssql_backup.bat to run daily or weekly as your wish.