Mysql - copy table from one database to other

Hi friends

This article explains how to move the particular table from the database to other database of same machine or different machine.Obviously, the user should have sufficient access for the database.

Copy the Table

Syntax:
# mysqldump --opt databasename table1 table2 tablen > tableexport.sql

For eg.,
# mysqldump --opt projectdbname tablename > anybackupname.sql

Moving to Remote machine using SCP:

# scp anybackupname.sql root@remotename_or_ip:/tmp

Restore

# mysql resultantdbname < anybackupname.sql

Thanks
Logu
logu_microsoft@hotmail.com
91-98414-99143

MySQL - Basic administration cmds

Hi friends
This articles provide you the basic administrative commands for MySQL. I have given the syntax for basic commands like installing,backup and restore, repairing and some others targeting the basic administrative purpose. Please refer the Mysql home site for full database commands.Lets go

MySQL installation:
Check MySQL existence
#rpm –qa | grep mysql

Installing MySQL:
#rpm –ivh mysql*
#yum install mysql

Connecting to the MySQL shell:
#mysql
#mysql –u root –p
#mysql –u root –p dbname
Will end up with prompt as “mysql>” is called mysql shell

Adding Database:
mysql>create database dbname; or
#mysqladmin create dbname –u root –p
#mysqladmin reload –u root -p

Dropping Database:
mysql>drop database dbname; or
#mysqladmin drop dbname –u root -p
#mysqladmin reload –u root –p

Creating MySQL User:
mysql>Grant all on dbname.* to dbusername@localhost identified by ‘enterpassword’;
Or
mysql>Grant select,insert,update,delete,create,drop,alter on dbname.* to dbusername@localhost identified by ‘enterpassword’;
Or
mysql>Insert into user (Host,User,Password)
values('localhost','dbuser',' enterpassword ');
flush privileges;

Dropping MySQL User
mysql>drop user 'username'@'location'

Resetting Root MySQL password:
#/etc/rc.d/init.d/mysqld stop
#/usr/bin/mysqld_safe --skip-grant-tables &
#killall mysqld
#/usr/bin/mysqld_safe --skip-grant-tables &
#mysqladmin -u root flush-privileges password "enterpassword"
#/etc/rc.d/init.d/mysqld restart

Changing MySQL User Password:
# set password for username@localhost = PASSWORD('enterpassword');

Backing up a MySQL Database:
#mysqldump databasename > /some/valid/directory/backup-file.sql
#mysqlhotcopy databasename /path/to/some/dir

Restoring a MySQL Database:
# mysql databasename < style="font-weight: bold;">Repairing a MySQL Database:
Database and tables can be corrupted because of the following reasons like killing mysqld in the middle of write, power failure, disk failure, Mysql program bug and MyISAM code bug.
Error syntax for corrupted Database is “Incorrect key file for table: '...'. Try to repair it”

# myisamchk -r -q tablename [continue the below if this doesn’t work]
Backup the database
# myisamchk -r tablename
# myisamchk --safe-recover tablename [use only the precedence cmd fails]

Hope this give you the basic admin commands.

Please do post/comment if you have any questions.

Thanks
Logu
logu_microsoft@hotmail.com | 91-98414-99143