Archive for gennaio, 2014

SettingUpSSHPrivateKeyPairsAndKeyRings

1. Firstly, generate your public/private keys using ssh-keygen

% ssh-keygen -t rsa

You must use the -t option to specify that you are producing keys for SSHv2 using RSA. This will generate your id_rsa and id_rsa.pub in the .ssh directory in your home directory. I strongly suggest using a passphrase.

2. Now copy the id_rsa.pub to the .ssh directory of the remote host you want to logon to as authorized_keys2 .

[Note: If you have more than one host from which you want to connect to the remote host, you need to add the local host’s id_rsa.pub as one line in the authorized_keys2 file of the remote host, i.e., you can have more than one entry. Thanks to Jinn Koriech for pointing this out. Also, you need to ‘chmod 644 authorized_keys2’ to make it unwritable to everybody apart from the user. Thanks to Matthew Lohbihler for this info.]

You are basically telling the sshd daemon on the remote machine to encrypt the connection with this public key and that this key is authorized for version 2 of the ssh protocol. Try using something secure like scp for this copying.

% scp ~foo/.ssh/id_rsa.pub [email protected]:~foo/.ssh/authorized_keys2

 

 

 

Convert OpenSSH? to IETF SECSH key file

 

Usage: ssh-keygen [options]
Options:
  -b bits     Number of bits in the key to create.
  -c          Change comment in private and public key files.
  -e          Convert OpenSSH to IETF SECSH key file.
  -f filename Filename of the key file.
  -g          Use generic DNS resource record format.
  -i          Convert IETF SECSH to OpenSSH key file.
  -l          Show fingerprint of key file.
  -p          Change passphrase of private key file.
  -q          Quiet.
  -y          Read private key file and print public key.
  -t type     Specify type of key to create.
  -B          Show bubblebabble digest of key file.
  -C comment  Provide new comment.
  -N phrase   Provide new passphrase.
  -P phrase   Provide old passphrase.
  -r hostname Print DNS resource record.
  -G file     Generate candidates for DH-GEX moduli
  -T file     Screen candidates for DH-GEX moduli

 

ssh-keygen -e -f id_dsa.pub >id_dsa.pub.ssh

 

MySQL

rename a table

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

 

re-index massive tables that are taking too long to re-index and are highly volatile

 

  • rename the table to something and create a table to accept incoming (temporary data)

 

    rename table status_log to status_log_maint; rename table status_log_temp to status_log;

 

  • optimise the renamed table

 

    optimize table status_log_maint

 

  • move big table back into place

 

    rename table status_log to status_log_temp; rename table status_log_maint to status_log;

 

 

  • move data back again:

 

    insert into status_log 
              (status_log_datetime, status_log_task_id, status_log_type, status_log_description, 
               status_log_status, status_log_booked_to_id, time, run_at_server) 
    select status_log_datetime, status_log_task_id, status_log_type, status_log_description, 
    status_log_status, status_log_booked_to_id, time, run_at_server 
    from status_log_temp;

 

  • remove temp table

 

    drop table status_log_temp

 

MySQLRebuildAndOptimiseIndexCron

MySQL command mysqlcheck

this can be run whilst mysql is running, unlike myasmchk which ideally shouldn’t be run on a live mySQL server

 

  • mysql – rebuild indexes by database in crontab

 

	* 00 11 * * * /usr/local/mysql/bin/mysqlcheck -u<user> -p<password>  
  --repair --quick --databases <db name(s)> --silent

 

  • mysql – rebuild indexes by table(s) in crontab

 

	* 00 11 * * * /usr/local/mysql/bin/mysqlcheck -u<user> -p<password>  
  --repair --quick <database> <table name(s)(,comma separated)> --silent

 

MySQL command myisamchk

 

    myisamchk --force --fast --update-state -O key_buffer=128M -O sort_buffer=128M -O  
    read_buffer=1M -O write_buffer=2M /usr/local/mysql/data/s_mon/*.MYI 2>&1 > myisamchk.log

 

  myisamchk: warning: Table is marked as crashed and last repair failed 
  myisamchk: warning: 1 clients is using or hasn't closed the table properly 
  myisamchk: warning: Size of indexfile is: 12240194560 Should be: 4986249216 
  myisamchk: warning: Size of datafile is: 7019888251 Should be: 6931236906

output in log is:

 

  [email protected]# cat myisamchk.log 
  Checking MyISAM? file: /usr/local/mysql/data/s_mon/component_data.MYI 
  Data records: 407719818 Deleted blocks: 0

key_buffer_size

key_buffer_size – seems to equate to size of in memory buffer to hold indexes? anyway, the books seem to favour this one to be set to as much as you can give it

can ‘tune’ what would be otherwise startup parameters specified as mysqld params, or else values as set in ‘my.cnf’ on the fly :

 

  mysql> show variables like "key_buffer_size";
  +-----------------+-----------+
  | Variable_name   | Value     |
  +-----------------+-----------+
  | key_buffer_size | 393216000 |
  +-----------------+-----------+
  1 row in set (0.01 sec)

  mysql>set global key_buffer_size=<val>

where val is the required byte value

you can set the amount of memory used by mysql to be easily 50% of over all system but be carefull if exceeding limit as heavy swap use may be incurred