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

MSSQL: The server principal is not able to access the database

If you accessing MSSQL 2005 with MSSQL 2008 management studio, you might getting error of The server principal is not able to access the database when log into the database server.

To fix it,
1. Connect to SQL Server Management Studio.
2. Connect to the SQL server.
3. Click “View” and then select on the “Object Explorer Details”. (This step could be done from shortcut F7) Continue reading MSSQL: The server principal is not able to access the database

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

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