Shantanu's Blog

There are number of ways to create / copy tables. It depends upon table type (InnoDB/MyISAM), indexes and records those you want to copy.

// copy paste the output to the new location
SHOW CREATE TABLE db2.oldtable

// create table without data exactly like old table (with indexes)
CREATE TABLE db1.newtable LIKE db2.oldtable

// create only table like old table (without indexes)
CREATE TABLE db1.newtable SELECT * FROM db2.oldtable WHERE 1=2

// create table without data like old table at command prompt (-d for without data)
[root@localhost test]# mysqldump db2 oldtable -d | mysql db1

// create table without indexes but with 100 records from old table
CREATE TABLE db1.newtable SELECT * FROM db2.oldtable LIMIT 100

// create table without indexes with all the records from old table
CREATE TABLE db1.newtable SELECT * FROM db2.oldtable

// The exactly idential tables (including indexes) in 2 statements
CREATE TABLE db1.newtable LIKE db2.oldtable;
INSERT INTO db1.newtable SELECT * FROM db2.oldtable;

// The exactly idential tables (including indexes) in one statement
CREATE TABLE db1.newtable (PRIMARY KEY (nation), KEY fsearchIp (searchip)) ENGINE = MEMORY SELECT * FROM db2.oldtable WHERE 1=2

// create table like old table on same or remote server
[root@localhost test]# mysqldump db2 oldtable | mysql -h 172.29.0.0 -uroot -pPassWd db1

// Copy table testme from test database to remote database 'db' (MyISAM only)
[root@localhost test]# scp -p /var/lib/mysql/test/testme.* root@172.29.0.0:/var/lib/mysql/db/

// Copy table testme to testme1 in the same db (MyISAM only - when no one is using the table)
[root@localhost test]# cp -p testme.MYD testme1.MYD
[root@localhost test]# cp -p testme.MYI testme1.MYI
[root@localhost test]# cp -p testme.frm testme1.frm

// Copy table testme from test database to db database (MyISAM only)
[root@localhost test]# cp -p /var/lib/mysql/test/testme.* /var/lib/mysql/db/

## copy data directory after skipping a few tables
time rsync -av --filter="- /*/data_intermediate*.*" --filter="- /*/oa_session.*" --filter="- /*/ox_audit.*" /mysql/data/ /redis/data/mysql/

// Update the remote table with updates recorded in the bin logs
[root@localhost test]# mysqlbinlog --start-datetime="`date +%Y-%m-%d' '%H:00:00 -d\"1 hour ago\"`" --stop-datetime="`date +%Y-%m-%d' '%H:00:00`" mysql-bin.* | awk '/tbl_company/,/;/' | replace tbl_company mydb.tbl_company | mysql -h 172.29.0.0 -uroot -pPasswd mydb

// Copy dbDRCGW1 Database to a new database shananu. Recreate the database shantanu if not exist
mysqladmin -uroot -pPassWord drop shantanu; mysqladmin -uroot -pPassWord create shantanu

time mysqldump -uroot -pPassWord -R --single-transaction dbDRCGW1 | perl /home/shantanu/dumper.pl --delete | mysql -uroot -pPassWord shantanu
# dumper.pl file is used to remove the "definer" of stored procedures and triggers and can be found here...
# http://forge.mysql.com/tools/tool.php?id=242

_____

Copy data from one server to another.

Sending server:
mysqldump db_name tbl_name | bzip2 -c | nc -l 1234

Receiving server:
nc 10.10.10.14 1234 | bzip2 -cd | mysql -uroot -proot@123 db_name

Running commands on remote host

#!/bib/Bash
#
# script: keyit.dsa

REM_HOST=$1
cat $HOME/.ssh/id_dsa.pub | ssh $REM_HOST "cat >> ~/.ssh/authorized_keys"

#!/bin/Bash
#
# script: keyit.rsa

REM_HOST=$1
cat $HOME/.ssh/id_rsa.pub | ssh $REM_HOST "cat >> ~/.ssh/authorized_keys"

If you want to set up the encryption keys for a new user, first su to that user ID,

and then issue one of the following commands.
To set up DSA keys issue this command:
ssh-keygen -t dsa

TO set up RSA keys issue this one:
ssh-keygen -t rsa

Read the ssh-keygen man page for more details: man ssh-keygen

// copy the rsa credentials to another server
ssh-copy-id -i  ~/.ssh/id_rsa root@10.10.10.10

who, w, last and ps auxw commands give you detailed analysis of the current server state.
_____

There is another way to make your logins passwordless temporarily. You need to add the following lines to the config file.

$ vi ~/.ssh/config
Host *
ControlMaster auto
ControlPath ~/.ssh/master-root@192.168.1.1:22
StrictHostKeychecking=no
UserKnownHostsFile=/dev/null

Change the server IP in the above statement and login. Next time you will not be asked for any password and you can connect quickly.
This passwordless sesssion will continue till master server is logged in. Once you disconnect the first session in which you have typed the password, this facility will end.

If you do not want a prompt that would ask if you want to save the IP to the known hosts list, add the following as an alias.

alias ssh='ssh -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null'

It can also be added to /etc/ssh/sshd_config so that all users can take advantage of this setting.