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 Admin Tips

Hi friends,

This article gives you the quick start admin guide for MySQL.

# change root password
$ mysql -u root
set password for root@localhost = password('abcd');
set password for root@localhost = '';
flush privileges;
quit;

# create new database
$ mysql -p -u root
CREATE DATABASE amarok;
USE mysql;
GRANT ALL ON amarok.* TO amarok@localhost IDENTIFIED BY 'PASSWORD_CHANGE_ME';
FLUSH PRIVILEGES;

# get the config variables
mysqladmin -u root variables
for example, to find the datadir
mysqladmin -u root variables | grep 'datadir'

# rename a DB
mysqladmin -u root create boss
mysqldump -u root --opt boss_dev >db.sql
mysql -u root boss mysql> revoke all on boss_dev.* from 'old_db_user'@'localhost';

# give access from remote host
GRANT ALL ON *.* TO 'root'@'192.168.1.%';

# user management
select host, db, user from db;
revoke all on noss2_prd.* from 'boss2'@'localhost';
select host, user, password from user;
delete from user where user like 'dep%';

# procedures
select routine_schema, specific_name from INFORMATION_SCHEMA.ROUTINES;

# views
select table_schema, table_name from INFORMATION_SCHEMA.VIEWS;

# tables
SELECT table_schema, table_name, table_type, engine, data_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'boss' AND table_name like 'audit%';

SELECT table_name, data_length FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'boss' AND table_name like 'audit%';

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'boss' AND table_name = 'audit'\G

DROP TABLE audit_20060427102929;
DROP TABLE boss_2006apr27094201;

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