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] ;

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)

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/

Connect ASP.Net script with MySQL

It is very easy if you have ASP.Net script and want to connect to MySQL databases.

Today, Mick Genie will share how to connect your MySQL database with your ASP.Net script.

Sample Code:
==========
<%@ Page Language=”VB” debug=”true” %>
<%@ Import Namespace = “System.Data” %>
<%@ Import Namespace = “MySql.Data.MySqlClient” %>
<script language=”VB” runat=”server”>

Sub Page_Load(sender As Object, e As EventArgs)

Dim myConnection  As MySqlConnection
Dim myDataAdapter As MySqlDataAdapter
Dim myDataSet     As DataSet

Dim strSQL        As String
Dim iRecordCount  As Integer

myConnection = New MySqlConnection(“server=localhost; user id=mickgenie; password=password; database=database;”)

strSQL = “SELECT * FROM mytable;”

myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
myDataSet = New Dataset()
myDataAdapter.Fill(myDataSet, “mytable”)

MySQLDataGrid.DataSource = myDataSet
MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

<form runat=”server”>

<asp:DataGrid id=”MySQLDataGrid” runat=”server” />

</form>

</body>
</html>
==========

From the above code, you may change the setting from myConnection and you shall be able to connect without any problem.

Reset MySQL 5.1 Root password

Sometimes you might want to reset your used password since it is used for long time.

To reset MySQL database password, you may need to consider different Operating System and it’s version will have minor differences during the root password reset.

Today, Mick Genie will guide you how to reset MySQL database v5.1  through Windows Operating System and Linux Operating System.

For Windows Operating System,
1. Firstly, create a text file and name it as resetpassword.txt and you may save it to C drive.
2. Enter the following word to your text file.

UPDATE mysql.user SET Password=PASSWORD('[email protected]') WHERE User='root';
FLUSH PRIVILEGES;

Continue reading Reset MySQL 5.1 Root password

Scheduled MySQL database backup

Assume that you are using crontab to schedule your task which you are using Unix system.

To schedule a database backup, it will be very easy as step below.

1. Log into your SSH.
2. Type “crontab -e” without quote.
3. Enter the command below.

0 0 * * * date=`date -I` ; mysqldump -a -u[user] -p[password]  [dbname] | gzip > /path/folder/[dbname]_$date.sql.gz

where you shall replace the user, password and dbname to your database information.

With the command as above it will actually backup your database by 12.00AM daily and place to the desired path and name with date.

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