Hands on MySQL Administration

O yeah it is an integrated part of open systems these days( read GNU/Linux system). So if somebody sit on it or asked to manage a box which runs that fellow(GNU/Linux) must have to have the ability to deal with this database.

Yes it is an database glue with GNU/Linux( O yeah you can preach PostGreSQL with same breadth!!).So in this article I am gonna touch few basic thing to get your hands dirty with it.Rather get you up start with it.Surely managing production database system is specialized job and I have seen it very closely …as I was working with few people they do all that matters with production databases. Although lot of thing I failed to discover because might be I was not great deal involved in managing databases or somebody else was doing the job for me.

Ok..lets start with installation.For this example I am using Fedora version 12 as my preferred distribution.

First thing first: Install the database server and client

bhaskar@bhaskar-laptop_05:25:15_Fri Jul 30:~> sudo yum install mysql
[sudo] password for bhaskar:
Loaded plugins: presto, refresh-packagekit
google-chrome | 951 B 00:00
google-chrome/primary | 3.1 kB 00:00
google-chrome 5/5
openvz-kernel-rhel5 | 951 B 00:00
openvz-utils | 951 B 00:00
openvz-utils/primary | 6.0 kB 00:00
openvz-utils 24/24
rpmfusion-free-updates | 2.8 kB 00:00
rpmfusion-free-updates/primary_db | 541 kB 00:38
updates/metalink | 8.9 kB 00:00
updates | 4.5 kB 00:00
updates/primary_db | 4.7 MB 06:00
updates-source/metalink | 7.4 kB 00:00
updates-source | 3.3 kB 00:00
updates-source/primary_db | 1.1 MB 01:07
Setting up Install Process
Package mysql-5.1.47-1.fc12.i686 already installed and latest version
Nothing to do
bhaskar@bhaskar-laptop_06:41:16_Fri Jul 30:~> sudo yum install mysql-server
[sudo] password for bhaskar:
Loaded plugins: presto, refresh-packagekit
Setting up Install Process
Package mysql-server-5.1.47-1.fc12.i686 already installed and latest version
Nothing to do

So As you can see from the above output that I have already installed those software in my system.Right!!

Start the service

bhaskar@bhaskar-laptop_07:40:36_Fri Jul 30:~> sudo service mysqld start
Initializing MySQL database: Installing MySQL system tables…
OK
Filling help tables…
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h bhaskar-laptop password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[ OK ]
Starting MySQL: [ OK ]

Cool right!! so it spit out lot of messages..phew! So if go through the spit out messages that it says lot thing s to follow.

So the next step is set the mysql admin user password as the previous messages said.

Set the mysql admin password

So I fire in the terminal:

bhaskar@bhaskar-laptop_07:41:26_Fri Jul 30:~> sudo /usr/bin/mysqladmin -u root -h bhaskar-laptop password “anythingYouWant”

Next..in the above spited message it also said to run a particular script to make the installation secure.It is wrapper script with lot of hand driven thing automated into it. So lets fire it from the terminal like this:

bhaskar@bhaskar-laptop_07:42:23_Fri Jul 30:~> sudo /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we’ll need the current
password for the root user. If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer ‘n’.

Change the root password? [Y/n] n
… skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
… Success!

Normally, root should only be allowed to connect from ‘localhost’. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
… Success!

By default, MySQL comes with a database named ‘test’ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
… Success!

Cleaning up…

All done! If you’ve completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

So please go through the question it asked and answer properly.

Next thing to do is check the login credential of mysql admin user like this

bhaskar@bhaskar-laptop_08:27:36_Fri Jul 30:~> sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.47 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

O yeah it puts me into a mysql shell and the user is validated!!

Now few administrative stuff.Here we go…

Check the processlist

bhaskar@bhaskar-laptop_08:29:43_Fri Jul 30:~> sudo mysqladmin -p processlist
Enter password:
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 14 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

Check the status

bhaskar@bhaskar-laptop_08:30:39_Fri Jul 30:~> sudo mysqladmin -p status
Enter password:
Uptime: 3000 Threads: 1 Questions: 29 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 9 Queries per second avg: 0.9

Check the variable of MySQL database

bhaskar@bhaskar-laptop_08:30:44_Fri Jul 30:~> sudo mysqladmin -p variables
Enter password:
+—————————————–+——————————————————————————————-+
| Variable_name | Value |
+—————————————–+——————————————————————————————-+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0 |
| event_scheduler | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:””&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
| group_concat_max_len | 1024 |
| have_community_features | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| have_partitioning | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
| hostname | bhaskar-laptop |
| identity | 0 |
| ignore_builtin_innodb | OFF |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
| insert_id | 0 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 8384512 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2146435072 |
| myisam_mmap_size | 4294967295 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| ndb_autoincrement_prefetch_sz | 1 |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| ndb_extra_logging | 0 |
| ndb_force_send | ON |
| ndb_index_stat_cache_entries | 32 |
| ndb_index_stat_enable | OFF |
| ndb_index_stat_update_freq | 20 |
| ndb_report_thresh_binlog_epoch_slip | 3 |
| ndb_report_thresh_binlog_mem_usage | 10 |
| ndb_use_copying_alter_table | OFF |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | /usr/lib/mysql/plugin |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| pseudo_thread_id | 0 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| rand_seed1 | |
| rand_seed2 | |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_auto_is_null | ON |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 18446744073709551615 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | IST |
| table_definition_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1280458953 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.1.47 |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+—————————————–+——————————————————————————————-+

Yeah I know it’s not greatly formatted here the table but you will get it nice on the terminal.Just paste for the sake of your curiosity.

Get the extended status about MySQL

bhaskar@bhaskar-laptop_08:36:18_Fri Jul 30:~> sudo mysqladmin -p extended-status
Enter password:
+———————————–+———-+
| Variable_name | Value |
+———————————–+———-+
| Aborted_clients | 0 |
| Aborted_connects | 4 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2122 |
| Bytes_sent | 11428 |
| Com_admin_commands | 1 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 7 |
| Com_set_option | 2 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 2 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 2 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 20 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 3 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 6 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 5 |
| Handler_read_key | 2 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 325 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1 |
| Handler_write | 288 |
| Innodb_buffer_pool_pages_data | 178 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 189 |
| Innodb_buffer_pool_pages_free | 333 |
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 1403 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1174 |
| Innodb_data_fsyncs | 16 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 38 |
| Innodb_data_written | 3400192 |
| Innodb_dblwr_pages_written | 16 |
| Innodb_dblwr_writes | 1 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 77 |
| Innodb_log_writes | 4 |
| Innodb_os_log_fsyncs | 10 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 37888 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 178 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 189 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 7241 |
| Key_blocks_used | 4 |
| Key_read_requests | 13 |
| Key_reads | 4 |
| Key_write_requests | 19 |
| Key_writes | 5 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 1 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 18 |
| Open_streams | 0 |
| Open_table_definitions | 16 |
| Open_tables | 9 |
| Opened_files | 59 |
| Opened_table_definitions | 16 |
| Opened_tables | 16 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 33 |
| Questions | 33 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 3 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 31 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 19 |
| Threads_running | 1 |
| Uptime | 3345 |
| Uptime_since_flush_status | 3345 |
+———————————–+———-+

Ping MySQL server to check weather it alive or not

bhaskar@bhaskar-laptop_08:40:11_Fri Jul 30:~> sudo mysqladmin -p ping
Enter password:
mysqld is alive

Anyway you can put all those into one single line to get the value of it.But not recommended.For the sake of impatience the command is :

bhaskar@bhaskar-laptop_08:40:24_Fri Jul 30:~> sudo mysqladmin -p status proc variables extended-status ping

Now some tricky stuff.

How to reset MySQL root password?

Why you need to reset?? Plenty of reason for that…I will touch few

a) The person who was in charge of database recently left the organization..so he take away the password with him/her(by remembering..or other method)..so changing it closing one of the door to him.

b) Need more strong and meaningful password then before!!!

c) Lot of people in the database department and outside know the password( it was leaked by accident)..so the change is necessary right?

Above stated reasons are to name a few.So the procedure would be:

Stop the MySQL service

bhaskar@bhaskar-laptop_09:04:27_Fri Jul 30:~> sudo service mysqld stop
[sudo] password for bhaskar:
Sorry, try again.
[sudo] password for bhaskar:
Stopping MySQL: [ OK ]

Start the MySQL server without the password!!

bhaskar@bhaskar-laptop_09:07:02_Fri Jul 30:~> sudo mysqld_safe –skip-grant-tables &
[1] 9730
bhaskar@bhaskar-laptop_09:07:09_Fri Jul 30:~> 100730 09:07:09 mysqld_safe Logging to ‘/var/log/mysqld.log’.
100730 09:07:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Get into the database as root

bhaskar@bhaskar-laptop_08:54:50_Fri Jul 30:~> sudo mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.47 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Then use this statement at the mysql prompt…

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set password=PASSWORD(“anythignYouWant”) where User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Once you type “quit” at the mysql prompt it will exit.

Now start the MySQL server normally like this:

bhaskar@bhaskar-laptop_09:15:11_Fri Jul 30:~> sudo service mysqld start
Starting MySQL: [ OK ]

Now test whether the password has recovered or not by logging as root to the mysql shell.

bhaskar@bhaskar-laptop_09:17:33_Fri Jul 30:~> sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.47 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Yup! I am in.

Backing up MySQL database

It is an important step to remember.So here we go:

Method one :
mysqldump client is a backup program used to dump a database or a collection of databases for backup or transfer to another SQL server. The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

The most common use of mysqldump is probably for making a backup of an entire database:

bhaskar@bhaskar-laptop_09:51:21_Fri Jul 30:~> sudo mysqldump –all-databases -p > all_mysqldata.sql

Here I am dumping all the databases I have..so the options signifies that!right.

Method two:
find out where ur mysql sotre’s the database generally default path will be:
:var/lib/mysql this is datadir=var/lib/mysql which is mention in mysql configuration file.

All the DB’s you will find on mysql dir, so u can copy the mysql dir.This kind of backup used when u want to uninstall and then again install with different version.Simply move to other location mysql dir, and then copy that.

Hope this will help and enjoy! By the way tuning the database is all together a different ball game. And I will write about it some other time.

Cheers!
Bhaskar

Investigate GNU/Linux box for io latency and network clogs

Yup as the title said I am gonna show you some measurement with some handly tools available for those actions.

I shall be running all those on a distribution called Arch Linux .

First I need to get few softwares which I need to manipulate. For that just open up a terminal and fire:

NB: In this distribution a binary is distributed to manage the packages in it..called “pacman”. Like we use in others distros i.e yum in Fedora; zypper in openSUSE,aptitude in Debian and emerge in Gentoo ūüôā .

So here we go…

bhaskar@bhaskar-laptop_10:45:02_Wed Jul 28:~> sudo pacman -S iotop htop iptraf
warning: iotop-0.4.1-1 is up to date — reinstalling
warning: htop-0.8.3-1 is up to date — reinstalling
warning: iptraf-3.0.0-2 is up to date — reinstalling
resolving dependencies…
looking for inter-conflicts…

Targets (3): iotop-0.4.1-1 htop-0.8.3-1 iptraf-3.0.0-2

Total Download Size: 0.00 MB
Total Installed Size: 0.56 MB

Proceed with installation? [Y/n]

Here I select “N” ;because as you can see I have already loaded those software into my system.So lets start exploring those.

To get the IO bottleneck you need to use the software called iotop.Like this:

bhaskar@bhaskar-laptop_10:55:50_Wed Jul 28:~> sudo iotop -o -t
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 27.11 K/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
bhaskar@bhaskar-laptop_10:55:50_Wed Jul 28:~> sudo iotop -o -t
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND

Total DISK READ: 0.00 B/s | Total DISK WRITE: 42.65 K/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
10:55:56 993 be/4 root 0.00 B/s 38.77 K/s 0.00 % 0.04 % [kjournald]
10:55:56 4682 be/4 bhaskar 0.00 B/s 3.88 K/s 0.00 % 0.00 % firefox
10:55:56 4714 be/4 bhaskar 0.00 B/s 0.00 B/s 0.00 % 0.00 % plugin-container /usr/lib/mozilla/plugins/libflashplayer.so 3496 plugin
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
10:55:57 2857 be/4 http 0.00 B/s 3.87 K/s 0.00 % 0.00 % httpd -k start
10:55:57 4682 be/4 bhaskar 0.00 B/s 0.00 B/s 0.00 % 0.00 % firefox
Total DISK READ: 0.00 B/s | Total DISK WRITE: 15.49 K/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 46.47 K/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
10:56:01 993 be/4 root 0.00 B/s 11.62 K/s 0.00 % 0.04 % [kjournald]
10:56:01 4714 be/4 bhaskar 0.00 B/s 0.00 B/s 0.00 % 0.00 % plugin-container /usr/lib/mozilla/plugins/libflashplayer.so 3496 plugin
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
10:56:04 3373 be/4 bhaskar 0.00 B/s 7.76 K/s 0.00 % 0.00 % gnome-terminal
Total DISK READ: 0.00 B/s | Total DISK WRITE: 0.00 B/s
TIME TID PRIO USER DISK READ DISK WRITE SWAPIN IO COMMAND
10:56:05 3373 be/4 bhaskar 0.00 B/s 3.88 K/s 0.00 % 0.00 % gnome-terminal

Ok.. I have used two options with that command.Now a bit more explanation of those.Option “-t” basically attached the time with the process. And “-o” only showing the actual IO happing there.For more option please visit the man page of it.I thought those two options are good enough to show the strength of it.

Now the second software we choose is htop..which is variant of top command ..but has more colorful thing in it.

lets fire it in a terminal:

htop

Click the picture to enlagre it.

Now if you want to make changes to it’s appearance and setup just press “F2” and it will put you in a mc like editor where you can modified the options you need.Look at the bottom of the window to press the correct option and use arrow key to move arround.If you run screen(as I always do) you can use mouse piinter too.

So next tool is called iptraf ..it is an ncurses based application to trac the network information about the connection.

iptraf:

Click the picture to enlarge.

So once again look at the bottom of window for the option.You can configure you network devices as you like and get the live stat flowing from it.So you get an clear idea what is going on.In that picture I have only select the ppp0(dialup) connection to show you the flow.

SS: the tcp/udp socket statistics dumper

Now to curious one most of the GNU/Linux distro come with a TCP/UDP stat dumper called ss.Please try it like this;

bhaskar@bhaskar-laptop_11:39:33_Wed Jul 28:~> sudo ss -a

for the security reason I am not posting the output of this command becuase it reveals lot of information.

Hope this will help .

Cheers!
Bhaskar

Intrusion detection and file integrity check with Tripwire

In this article I am gonna show you how you can monitor your files on the installed system with a software called tripwire.You can get this software here .

As I said before that am running four different distros in my lappi and as well two different distros within virtualization technique.So the installation of that particular software will be handled different by their package manager.And if some choose to go the source compile option still they have to adjust the location of the files.

For the demonstration of this particular software I am going to stick with Fedora(precisely version 12). So my first job is to get the software from the repository ..here we go:

Step 1: Get the software

bhaskar@bhaskar-laptop_08:17:58_Sun Jul 25:~> sudo yum install tripwire
[sudo] password for bhaskar:
Loaded plugins: presto, refresh-packagekit
google-chrome                                                                                                                         |  951 B     00:00
openvz-kernel-rhel5                                                                                                                   |  951 B     00:00
openvz-utils                                                                                                                          |  951 B     00:00
rpmfusion-free-updates                                                                                                                | 2.8 kB     00:03
updates/metalink                                                                                                                      | 8.4 kB     00:02
updates-source/metalink                                                                                                               | 7.9 kB     00:00
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package tripwire.i686 0:2.4.1.2-11.fc12 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
Package                              Arch                             Version                                      Repository                          Size
=============================================================================================================================================================
Installing:
tripwire                             i686                             2.4.1.2-11.fc12                              fedora                             758 k

Transaction Summary
=============================================================================================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total download size: 758 k
Installed size: 4.0 M
Is this ok [y/N]: y

And yes I said “Y” to the prompt to get the software installed in my system.Now see it installed:Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 758 k
tripwire-2.4.1.2-11.fc12.i686.rpm                                                                                                     | 758 kB     01:42
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing     : tripwire-2.4.1.2-11.fc12.i686                                                                                                         1/1

Installed:
tripwire.i686 0:2.4.1.2-11.fc12

Complete!

Step 2: Verify the installation

bhaskar@bhaskar-laptop_08:54:38_Sun Jul 25:~> sudo rpm -qi tripwire
[sudo] password for bhaskar:
Name        : tripwire                     Relocations: (not relocatable)
Version     : 2.4.1.2                           Vendor: Fedora Project
Release     : 11.fc12                       Build Date: Fri 21 Aug 2009 10:24:49 PM IST
Install Date: Sun 25 Jul 2010 08:54:04 AM IST      Build Host: x86-2.fedora.phx.redhat.com
Group       : Applications/System           Source RPM: tripwire-2.4.1.2-11.fc12.src.rpm
Size        : 4192723                          License: GPLv2+
Signature   : RSA/8, Wed 30 Sep 2009 11:06:18 AM IST, Key ID 9d1cc34857bbccba
Packager    : Fedora Project
URL         : http://www.tripwire.org/
Summary     : IDS (Intrusion Detection System)
Description :
Tripwire is a very valuable security tool for Linux systems, if  it  is
installed to a clean system. Tripwire should be installed  right  after
the OS installation, and before you have connected  your  system  to  a
network (i.e., before any possibility exists that someone  could  alter
files on your system).

When Tripwire is initially set up, it creates a database  that  records
certain file information. Then when it is run, it compares a designated
set of files and directories to the information stored in the database.
Added or deleted files are flagged and reported, as are any files  that
have changed from their previously recorded state in the database. When
Tripwire is run against system files  on  a  regular  basis,  any  file
changes will be spotted when Tripwire is run. Tripwire will report  the
changes, which will give system administrators a clue that they need to
enact damage control measures immediately if certain  files  have  been
altered.

Right! so next to configure the software properly to scan out filesystem properly.

Step 3: Find out the files installed by tripwire

lets get the file installed in the system in different places a.k.a location in the filesystem

bhaskar@bhaskar-laptop_08:58:46_Sun Jul 25:~> sudo rpm -ql tripwire
/etc/cron.daily/tripwire-check
/etc/tripwire
/etc/tripwire/twcfg.txt
/etc/tripwire/twpol.txt
/usr/sbin/siggen
/usr/sbin/tripwire
/usr/sbin/tripwire-setup-keyfiles
/usr/sbin/twadmin
/usr/sbin/twprint
/usr/share/doc/tripwire-2.4.1.2
/usr/share/doc/tripwire-2.4.1.2/COMMERCIAL
/usr/share/doc/tripwire-2.4.1.2/COPYING
/usr/share/doc/tripwire-2.4.1.2/ChangeLog
/usr/share/doc/tripwire-2.4.1.2/License-Issues
/usr/share/doc/tripwire-2.4.1.2/README.Fedora
/usr/share/doc/tripwire-2.4.1.2/TRADEMARK
/usr/share/doc/tripwire-2.4.1.2/policyguide.txt
/usr/share/doc/tripwire-2.4.1.2/tripwire.gif
/usr/share/man/man4/twconfig.4.gz
/usr/share/man/man4/twpolicy.4.gz
/usr/share/man/man5/twfiles.5.gz
/usr/share/man/man8/siggen.8.gz
/usr/share/man/man8/tripwire.8.gz
/usr/share/man/man8/twadmin.8.gz
/usr/share/man/man8/twintro.8.gz
/usr/share/man/man8/twprint.8.gz
/var/lib/tripwire
/var/lib/tripwire/report

So from this information that it has distributed it’s file throughout the system.

Step 4: Configure and customize tripwire

For the sake of clarity I keep the default file as it is :

bhaskar@bhaskar-laptop_09:05:02_Sun Jul 25:~> sudo vim /etc/tripwire/twcfg.txt

ROOT                   =/usr/sbin
POLFILE                =/etc/tripwire/tw.pol
DBFILE                 =/var/lib/tripwire/$(HOSTNAME).twd
REPORTFILE             =/var/lib/tripwire/report/$(HOSTNAME)-$(DATE).twr
SITEKEYFILE            =/etc/tripwire/site.key
LOCALKEYFILE           =/etc/tripwire/$(HOSTNAME)-local.key
EDITOR                 =/bin/vi
LATEPROMPTING          =false
LOOSEDIRECTORYCHECKING =false
MAILNOVIOLATIONS       =true
EMAILREPORTLEVEL       =3
REPORTLEVEL            =3
MAILMETHOD             =SENDMAIL
SYSLOGREPORTING        =false
MAILPROGRAM            =/usr/sbin/sendmail -oi -t

So the next file look upon is the policy file of it:

bhaskar@bhaskar-laptop_09:08:41_Sun Jul 25:~> sudo vim /etc/tripwire/twpol.txt

once again for the sake of clarity I keep the policy file as default.But one should change it according to his/her requirement.It’s a very simple file with just key=value pair in it.

Step 5: Generate the key pair to protect tripwire files

You need to run this script to get things done:

root@bhaskar-laptop_09:32:59_Sun Jul 25:/etc/tripwire # sudo /usr/sbin/tripwire-setup-keyfiles

The Tripwire site and local passphrases are used to sign a  variety  of
files, such as the configuration, policy, and database files.

Passphrases should be at least 8 characters in length and contain  both
letters and numbers.

See the Tripwire manual for more information.

———————————————-
Creating key files…

(When selecting a passphrase, keep in mind that good passphrases typically
have upper and lower case letters, digits and punctuation marks, and are
at least 8 characters in length.)

Enter the site keyfile passphrase:

Verify the site keyfile passphrase:
Generating key (this may take several minutes)…Key generation complete.

(When selecting a passphrase, keep in mind that good passphrases typically
have upper and lower case letters, digits and punctuation marks, and are
at least 8 characters in length.)

Enter the local keyfile passphrase:
Verify the local keyfile passphrase:
Generating key (this may take several minutes)…Key generation complete.

———————————————-
Signing configuration file…
Please enter your site passphrase:
Wrote configuration file: /etc/tripwire/tw.cfg

A clear-text version of the Tripwire configuration file:
/etc/tripwire/twcfg.txt
has been preserved for your inspection.  It  is  recommended  that  you
move this file to a secure location and/or encrypt it in place (using a
tool such as GPG, for example) after you have examined it.

———————————————-
Signing policy file…
Please enter your site passphrase:
Wrote policy file: /etc/tripwire/tw.pol

A clear-text version of the Tripwire policy file:
/etc/tripwire/twpol.txt
has been preserved for  your  inspection.  This  implements  a  minimal
policy, intended only to test  essential  Tripwire  functionality.  You
should edit the policy file to  describe  your  system,  and  then  use
twadmin to generate a new signed copy of the Tripwire policy.

Once you have a satisfactory Tripwire policy file, you should move  the
clear-text version to a secure location  and/or  encrypt  it  in  place
(using a tool such as GPG, for example).

Now run “tripwire –init” to enter Database Initialization¬† Mode.¬† This
reads the policy file, generates a database based on its contents,  and
then cryptographically signs the resulting  database.  Options  can  be
entered on the command line to specify which policy, configuration, and
key files are used  to  create  the  database.  The  filename  for  the
database can be specified as well. If no  options  are  specified,  the
default values from the current configuration file are used.

So it’s done with key files..now every time we need run tripwire you have to supply the correct site passphrase.

Step 6: Initialize the database

root@bhaskar-laptop_09:43:11_Sun Jul 25:/etc/tripwire # tripwire –init

Please enter your local passphrase:
Incorrect local passphrase.
Please enter your local passphrase:
Parsing policy file: /etc/tripwire/tw.pol
Generating the database…
*** Processing Unix File System ***
……………………

………………………
Wrote database file: /var/lib/tripwire/bhaskar-laptop.twd
The database was successfully generated.

So the first time I have entered the wrong passphrase so I was asked to enter it again. Now the database of the existing filesystem has been generated . Next thing to check the integrity.

Step 7: Checking integrity

root@bhaskar-laptop_09:46:55_Sun Jul 25:/etc/tripwire # tripwire –check

Parsing policy file: /etc/tripwire/tw.pol
*** Processing Unix File System ***
Performing integrity check…

……………………….

Wrote report file: /var/lib/tripwire/report/bhaskar-laptop-20100725-095014.twr

Open Source Tripwire(R) 2.4.1 Integrity Check Report

Report generated by:          root
Report created on:            Sun 25 Jul 2010 09:50:14 AM IST
Database last updated on:     Never

===============================================================================
Report Summary:
===============================================================================

Host name:                    bhaskar-laptop
Host IP address:              127.0.0.1
Host ID:                      None
Policy file used:             /etc/tripwire/tw.pol
Configuration file used:      /etc/tripwire/tw.cfg
Database file used:           /var/lib/tripwire/bhaskar-laptop.twd
Command line used:¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† tripwire –check

===============================================================================
Rule Summary:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

Rule Name                       Severity Level    Added    Removed  Modified
———¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† ————–¬†¬†¬† —–¬†¬†¬† ——-¬† ——–
Invariant Directories           66                0        0        0
Temporary directories           33                0        0        0
* Tripwire Data Files             100               1        0        0
Critical devices                100               0        0        0
User binaries                   66                0        0        0
Tripwire Binaries               100               0        0        0
Critical configuration files    100               0        0        0
Libraries                       66                0        0        0
Operating System Utilities      100               0        0        0
Critical system boot files      100               0        0        0
File System and Disk Administraton Programs
100               0        0        0
Kernel Administration Programs  100               0        0        0
Networking Programs             100               0        0        0
System Administration Programs  100               0        0        0
Hardware and Device Control Programs
100               0        0        0
System Information Programs     100               0        0        0
Application Information Programs
100               0        0        0
Shell Related Programs          100               0        0        0
Critical Utility Sym-Links      100               0        0        0
Shell Binaries                  100               0        0        0
System boot changes             100               0        0        0
OS executables and libraries    100               0        0        0
Security Control                100               0        0        0
Login Scripts                   100               0        0        0
Root config files               100               0        0        0

Total objects scanned:  36197
Total violations found:  1

===============================================================================
Object Summary:
===============================================================================

——————————————————————————-
# Section: Unix File System
——————————————————————————-

——————————————————————————-
Rule Name: Tripwire Data Files (/var/lib/tripwire)
Severity Level: 100
——————————————————————————-

Added:
“/var/lib/tripwire/bhaskar-laptop.twd”

===============================================================================
Error Report:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

1.   File system error.
Filename: /dev/kmem
No such file or directory
……………………
…………………..
Filename: /root/.Xauthority
No such file or directory

——————————————————————————-
*** End of report ***

Open Source Tripwire 2.4 Portions copyright 2000 Tripwire, Inc. Tripwire is a registered
trademark of Tripwire, Inc. This software comes with ABSOLUTELY NO WARRANTY;
for details use –version. This is free software which may be redistributed
or modified only under certain conditions; see COPYING for details.
All rights reserved.
Integrity check complete.

Now you can see lot of working because I haven’t made any change to the default files.So the binaries assumed lot of things and spit out the warning.Those warning can be chopped off if one modified(and should) the policy file according to the need.I prefer to go way with the default one to show the impact of it.

Now what that check does? it simply compare the database of our filesystem we created earlier with present state of the filesystem and spit out the result.And more importantly it generate a report of it and keep that file in the filesystem. Next we are going to see the report that the check prepared for us.

Step 8: Checking report

Now it stores the report in /var/lib/tripwire/report

Here is the report file just created:

root@bhaskar-laptop_09:52:53_Sun Jul 25:/etc/tripwire # ls -al /var/lib/tripwire/report/
total 20
drwx——. 2 root root¬† 4096 2010-07-25 09:52 .
drwx——. 3 root root¬† 4096 2010-07-25 09:46 ..
-rw-r–r–. 1 root root 11878 2010-07-25 09:52 bhaskar-laptop-20100725-095014.twr

So we going to examine that .twr file .And we will use a binary shipped with this software called “twprint”

root@bhaskar-laptop_10:04:20_Sun Jul 25:/etc/tripwire #twprint -m r –twrfile /var/lib/tripwire/report/bhaskar-laptop-20100725-095014.twr

Note: Report is not encrypted.
Open Source Tripwire(R) 2.4.1 Integrity Check Report

Report generated by:          root
Report created on:            Sun 25 Jul 2010 09:50:14 AM IST
Database last updated on:     Never

===============================================================================
Report Summary:
===============================================================================

Host name:                    bhaskar-laptop
Host IP address:              127.0.0.1
Host ID:                      None
Policy file used:             /etc/tripwire/tw.pol
Configuration file used:      /etc/tripwire/tw.cfg
Database file used:           /var/lib/tripwire/bhaskar-laptop.twd
Command line used:¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† tripwire –check

===============================================================================
Rule Summary:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

Rule Name                       Severity Level    Added    Removed  Modified
———¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† ————–¬†¬†¬† —–¬†¬†¬† ——-¬† ——–
Invariant Directories           66                0        0        0
Temporary directories           33                0        0        0
* Tripwire Data Files             100               1        0        0
Critical devices                100               0        0        0
User binaries                   66                0        0        0
Tripwire Binaries               100               0        0        0
Critical configuration files    100               0        0        0
Libraries                       66                0        0        0
Operating System Utilities      100               0        0        0
Critical system boot files      100               0        0        0
File System and Disk Administraton Programs
100               0        0        0
Kernel Administration Programs  100               0        0        0
Networking Programs             100               0        0        0
System Administration Programs  100               0        0        0
Hardware and Device Control Programs
100               0        0        0
System Information Programs     100               0        0        0
Application Information Programs
100               0        0        0
Shell Related Programs          100               0        0        0
Critical Utility Sym-Links      100               0        0        0
Shell Binaries                  100               0        0        0
System boot changes             100               0        0        0
OS executables and libraries    100               0        0        0
Security Control                100               0        0        0
Login Scripts                   100               0        0        0
Root config files               100               0        0        0

Total objects scanned:  36197
Total violations found:  1

===============================================================================
Object Detail:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

——————————————————————————-
Rule Name: Tripwire Data Files (/var/lib/tripwire)
Severity Level: 100
——————————————————————————-
—————————————-
Added Objects: 1
—————————————-

Added object name:  /var/lib/tripwire/bhaskar-laptop.twd

===============================================================================
Error Report:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

1.   File system error.
Filename: /dev/kmem
No such file or directory

…………………
……………

——————————————————————————-
*** End of report ***

Open Source Tripwire 2.4 Portions copyright 2000 Tripwire, Inc. Tripwire is a registered
trademark of Tripwire, Inc. This software comes with ABSOLUTELY NO WARRANTY;
for details use –version. This is free software which may be redistributed
or modified only under certain conditions; see COPYING for details.
All rights reserved.

Because it’s freshly installed so lot of information is missing from the reports.And of course lot of warnings can be eliminated from this report if I had modified the policy file..which I didn’t for sake of clarity.

Step 9: Track the tripwire database

Just to check how much information tripwire keep track for us.

root@bhaskar-laptop_10:12:34_Sun Jul 25:/etc/tripwire # twprint -m d –print-dbfile

Open Source Tripwire(R) 2.4.1 Database

Database generated by:        root
Database generated on:        Sun 25 Jul 2010 09:43:55 AM IST
Database last updated on:     Never

===============================================================================
Database Summary:
===============================================================================

Host name:                    bhaskar-laptop
Host IP address:              127.0.0.1
Host ID:                      None
Policy file used:             /etc/tripwire/tw.pol
Configuration file used:      /etc/tripwire/tw.cfg
Database file used:           /var/lib/tripwire/bhaskar-laptop.twd
Command line used:¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† tripwire –init

Object Summary:
===============================================================================

——————————————————————————-
# Section: Unix File System
——————————————————————————-

Mode        UID                  Size       Modify Time
——¬†¬†¬†¬†¬† ———-¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† ———- ———-
/
dr-xr-xr-x  root (0)             XXX        XXXXXXXXXXXXXXXXX
/bin
dr-xr-xr-x  root (0)             4096       Sun 25 Jul 2010 09:40:32 AM IST
/bin/alsaunmute
-rwxr-xr-x  root (0)             123        Mon 28 Jun 2010 05:21:44 PM IST
/bin/arch
-rwxr-xr-x  root (0)             26828      Wed 28 Apr 2010 09:26:03 PM IST
/bin/awk
lrwxrwxrwx  root (0)             4          Mon 12 Jul 2010 08:44:23 PM IST
/bin/basename
-rwxr-xr-x  root (0)             25032      Wed 28 Apr 2010 09:26:03 PM IST
/bin/bash
-rwxr-xr-x  root (0)             861128     Fri 21 May 2010 10:54:37 PM IST
/bin/cat
-rwxr-xr-x  root (0)             48292      Wed 28 Apr 2010 09:26:03 PM IST
/bin/chgrp
-rwxr-xr-x  root (0)             56708      Wed 28 Apr 2010 09:26:03 PM IST
/bin/chmod
-rwxr-xr-x  root (0)             52304      Wed 28 Apr 2010 09:26:03 PM IST
/bin/chown
-rwxr-xr-x  root (0)             58932      Wed 28 Apr 2010 09:26:03 PM IST
/bin/cp
-rwxr-xr-x  root (0)             118896     Wed 28 Apr 2010 09:26:03 PM IST
/bin/cpio
-rwxr-xr-x  root (0)             133292     Wed 10 Mar 2010 07:55:16 PM IST
/bin/cut
-rwxr-xr-x  root (0)             44076      Wed 28 Apr 2010 09:26:03 PM IST
/bin/dash
-rwxr-xr-x  root (0)             102216     Sat 25 Jul 2009 11:46:41 AM IST
/bin/date
-rwxr-xr-x  root (0)             60140      Wed 28 Apr 2010 09:26:03 PM IST
/bin/dbus-cleanup-sockets
-rwxr-xr-x  root (0)             9628       Fri 18 Dec 2009 09:02:09 PM IST
/bin/dbus-daemon
-rwxr-xr-x  root (0)             362416     Fri 18 Dec 2009 09:02:09 PM IST
/bin/dbus-monitor
-rwxr-xr-x  root (0)             13804      Fri 18 Dec 2009 09:02:09 PM IST
/bin/dbus-send
-rwxr-xr-x  root (0)             17168      Fri 18 Dec 2009 09:02:09 PM IST
/bin/dbus-uuidgen
-rwxr-xr-x  root (0)             7972       Fri 18 Dec 2009 09:02:09 PM IST
/bin/dd
-rwxr-xr-x  root (0)             56188      Wed 28 Apr 2010 09:26:03 PM IST
/bin/df
-rwxr-xr-x  root (0)             69536      Wed 28 Apr 2010 09:26:03 PM IST
/bin/dmesg
-rwxr-xr-x  root (0)             7028       Tue 13 Apr 2010 12:34:31 AM IST
/bin/dnsdomainname
lrwxrwxrwx  root (0)             8          Tue 08 Jun 2010 11:39:42 AM IST
/bin/doexec

-rwxr-xr-x  root (0)             4912       Wed 28 Apr 2010 11:56:37 PM IST
/bin/domainname
lrwxrwxrwx  root (0)             8          Tue 08 Jun 2010 11:39:42 AM IST
/bin/dumpkeys
-rwxr-xr-x  root (0)             59824      Wed 26 Aug 2009 10:23:56 PM IST
/bin/echo
-rwxr-xr-x  root (0)             26224      Wed 28 Apr 2010 09:26:03 PM IST
/bin/ed
-rwxr-xr-x  root (0)             48036      Sat 25 Jul 2009 07:53:30 PM IST
/bin/egrep
-rwxr-xr-x  root (0)             98156      Thu 08 Apr 2010 03:10:19 AM IST
/bin/env
-rwxr-xr-x  root (0)             25084      Wed 28 Apr 2010 09:26:03 PM IST
/bin/ex
lrwxrwxrwx  root (0)             2          Mon 05 Apr 2010 06:03:08 PM IST
/bin/false
-rwxr-xr-x  root (0)             23324      Wed 28 Apr 2010 09:26:03 PM IST
/bin/fgrep
-rwxr-xr-x  root (0)             65168      Thu 08 Apr 2010 03:10:19 AM IST
/bin/find
-rwxr-xr-x  root (0)             172300     Tue 01 Jun 2010 04:21:17 PM IST
/bin/fusermount
-rwsr-xr-x  root (0)             29340      Tue 08 Jun 2010 10:45:17 AM IST
/bin/gawk
-rwxr-xr-x  root (0)             346704     Thu 01 Apr 2010 08:00:09 PM IST
/bin/gettext
-rwxr-xr-x  root (0)             27436      Tue 08 Dec 2009 01:40:34 PM IST
/bin/grep
-rwxr-xr-x  root (0)             102284     Thu 08 Apr 2010 03:10:19 AM IST
/bin/gtar
lrwxrwxrwx  root (0)             3          Sat 22 May 2010 09:30:52 AM IST
/bin/gunzip
-rwxr-xr-x  root (0)             61         Tue 23 Feb 2010 03:37:28 PM IST
/bin/gzip
-rwxr-xr-x  root (0)             67500      Tue 23 Feb 2010 03:37:28 PM IST
/bin/hostname
-rwxr-xr-x  root (0)             12728      Fri 19 Mar 2010 04:59:02 PM IST
/bin/ipcalc
-rwxr-xr-x  root (0)             11588      Wed 28 Apr 2010 11:56:37 PM IST
/bin/iptables-xml
lrwxrwxrwx  root (0)             20         Tue 10 Nov 2009 12:41:37 AM IST
/bin/kbd_mode
-rwxr-xr-x  root (0)             8420       Wed 26 Aug 2009 10:23:56 PM IST
/bin/kill
-rwxr-xr-x  root (0)             11484      Tue 13 Apr 2010 12:34:31 AM IST
/bin/link
-rwxr-xr-x  root (0)             25028      Wed 28 Apr 2010 09:26:03 PM IST
/bin/ln
-rwxr-xr-x  root (0)             46596      Wed 28 Apr 2010 09:26:03 PM IST
/bin/loadkeys
-rwxr-xr-x  root (0)             88720      Wed 26 Aug 2009 10:23:56 PM IST
/bin/login
-rwxr-xr-x  root (0)             26020      Tue 13 Apr 2010 12:34:31 AM IST
/bin/lowntfs-3g
-rwxr-xr-x  root (0)             56596      Tue 18 May 2010 11:05:21 PM IST
/bin/ls
-rwxr-xr-x  root (0)             116688     Wed 28 Apr 2010 09:26:03 PM IST
/bin/mail
lrwxrwxrwx  root (0)             5          Wed 26 May 2010 05:23:13 PM IST
/bin/mailx
-rwxr-xr-x  root (0)             374868     Mon 27 Jul 2009 06:12:34 AM IST
/bin/mkdir
-rwxr-xr-x  root (0)             46328      Wed 28 Apr 2010 09:26:03 PM IST
/bin/mknod
-rwxr-xr-x  root (0)             30916      Wed 28 Apr 2010 09:26:03 PM IST
/bin/mktemp
-rwxr-xr-x  root (0)             35296      Wed 28 Apr 2010 09:26:03 PM IST
/bin/more
-rwxr-xr-x  root (0)             35056      Tue 13 Apr 2010 12:34:31 AM IST
/bin/mount
-rwsr-xr-x  root (0)             70444      Tue 13 Apr 2010 12:34:25 AM IST
/bin/mountpoint
-rwxr-xr-x  root (0)             7212       Mon 26 Apr 2010 03:33:17 PM IST
/bin/mv
-rwxr-xr-x  root (0)             109976     Wed 28 Apr 2010 09:26:03 PM IST
/bin/netstat
-rwxr-xr-x  root (0)             122960     Fri 19 Mar 2010 04:59:02 PM IST
/bin/nice
-rwxr-xr-x  root (0)             27328      Wed 28 Apr 2010 09:26:03 PM IST
/bin/nisdomainname
lrwxrwxrwx  root (0)             8          Tue 08 Jun 2010 11:39:42 AM IST
/bin/ntfs-3g
-rwxr-xr-x  root (0)             51380      Tue 18 May 2010 11:05:21 PM IST
/bin/ntfs-3g.probe
-rwxr-xr-x  root (0)             8196       Tue 18 May 2010 11:05:21 PM IST
/bin/ntfs-3g.secaudit
-rwxr-xr-x  root (0)             60884      Tue 18 May 2010 11:05:21 PM IST
/bin/ntfs-3g.usermap
-rwxr-xr-x  root (0)             14920      Tue 18 May 2010 11:05:21 PM IST
/bin/ntfsmount
lrwxrwxrwx  root (0)             7          Tue 08 Jun 2010 11:39:44 AM IST
/bin/ping
-rwsr-xr-x  root (0)             41976      Tue 11 May 2010 07:33:13 PM IST
/bin/ping6
-rwsr-xr-x  root (0)             37228      Tue 11 May 2010 07:33:13 PM IST
/bin/plymouth
-rwxr-xr-x  root (0)             31048      Tue 26 Jan 2010 11:59:16 AM IST
/bin/ps
-rwxr-xr-x  root (0)             83624      Mon 16 Nov 2009 07:30:24 PM IST
/bin/pwd
-rwxr-xr-x  root (0)             30140      Wed 28 Apr 2010 09:26:03 PM IST
/bin/readlink
-rwxr-xr-x  root (0)             38732      Wed 28 Apr 2010 09:26:03 PM IST
/bin/red
-rwxr-xr-x  root (0)             48036      Sat 25 Jul 2009 07:53:30 PM IST
/bin/redhat_lsb_init
-rwxr-xr-x  root (0)             576        Wed 16 Apr 2008 11:20:14 PM IST
/bin/rm
-rwxr-xr-x  root (0)             56792      Wed 28 Apr 2010 09:26:03 PM IST
/bin/rmdir
-rwxr-xr-x  root (0)             38840      Wed 28 Apr 2010 09:26:03 PM IST
/bin/rpm
-rwxr-xr-x  root (0)             22528      Wed 30 Jun 2010 03:46:32 PM IST
/bin/rvi
lrwxrwxrwx  root (0)             2          Mon 05 Apr 2010 06:03:08 PM IST
/bin/rview
lrwxrwxrwx  root (0)             2          Mon 05 Apr 2010 06:03:08 PM IST
/bin/sed
-rwxr-xr-x  root (0)             66380      Mon 22 Mar 2010 06:41:35 PM IST
/bin/setfont
-rwxr-xr-x  root (0)             39008      Wed 26 Aug 2009 10:23:56 PM IST
/bin/setserial
-rwxr-xr-x  root (0)             20788      Wed 29 Jul 2009 02:03:22 AM IST
/bin/sh
lrwxrwxrwx  root (0)             4          Fri 23 Jul 2010 10:22:22 AM IST
/bin/sleep
-rwxr-xr-x  root (0)             26000      Wed 28 Apr 2010 09:26:03 PM IST
/bin/sort
-rwxr-xr-x  root (0)             98344      Wed 28 Apr 2010 09:26:03 PM IST
/bin/stty
-rwxr-xr-x  root (0)             63472      Wed 28 Apr 2010 09:26:03 PM IST
/bin/su
-rwsr-xr-x  root (0)             32744      Wed 28 Apr 2010 09:26:02 PM IST
/bin/sync
-rwxr-xr-x  root (0)             23844      Wed 28 Apr 2010 09:26:03 PM IST
/bin/tar
-rwxr-xr-x  root (0)             295904     Mon 03 May 2010 03:44:03 PM IST
/bin/taskset
-rwxr-xr-x  root (0)             11904      Tue 13 Apr 2010 12:34:31 AM IST
/bin/touch
-rwxr-xr-x  root (0)             47732      Wed 28 Apr 2010 09:26:03 PM IST
/bin/tracepath
-rwxr-xr-x  root (0)             10040      Tue 11 May 2010 07:33:13 PM IST
/bin/tracepath6
-rwxr-xr-x  root (0)             10732      Tue 11 May 2010 07:33:13 PM IST
/bin/traceroute
-rwxr-xr-x  root (0)             55600      Wed 14 Jul 2010 08:45:46 PM IST
/bin/traceroute6
lrwxrwxrwx  root (0)             10         Fri 23 Jul 2010 10:23:48 AM IST
/bin/true
-rwxr-xr-x  root (0)             23324      Wed 28 Apr 2010 09:26:03 PM IST
/bin/ulockmgr_server
-rwxr-xr-x  root (0)             11400      Tue 08 Jun 2010 10:45:17 AM IST
/bin/umount
-rwsr-xr-x  root (0)             47540      Tue 13 Apr 2010 12:34:26 AM IST
/bin/uname
-rwxr-xr-x  root (0)             26828      Wed 28 Apr 2010 09:26:03 PM IST
/bin/unicode_start
-rwxr-xr-x  root (0)             2555       Wed 26 Aug 2009 10:23:55 PM IST
/bin/unicode_stop
-rwxr-xr-x  root (0)             363        Wed 26 Aug 2009 10:23:53 PM IST
/bin/unlink
-rwxr-xr-x  root (0)             24068      Wed 28 Apr 2010 09:26:03 PM IST
/bin/usleep
-rwxr-xr-x  root (0)             7096       Wed 28 Apr 2010 11:56:37 PM IST
/bin/vi
-rwxr-xr-x  root (0)             722680     Thu 25 Mar 2010 11:43:17 PM IST
/bin/view
lrwxrwxrwx  root (0)             2          Mon 05 Apr 2010 06:03:09 PM IST
/bin/ypdomainname
lrwxrwxrwx  root (0)             8          Tue 08 Jun 2010 11:39:42 AM IST
/bin/zcat
-rwxr-xr-x  root (0)             62         Tue 23 Feb 2010 03:37:28 PM IST
/boot
dr-xr-xr-x  root (0)             1024       Fri 23 Jul 2010 10:25:29 AM IST
/boot/System.map-2.6.32.12-115.fc12.i686
-rw-r–r–¬† root (0)¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 1643223¬†¬†¬† Sat 01 May 2010 02:19:02 AM IST
/boot/System.map-2.6.32.14-127.fc12.i686
-rw-r–r–¬† root (0)¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 1643320¬†¬†¬† Fri 28 May 2010 10:47:55 AM IST
/boot/System.map-2.6.32.16-141.fc12.i686
-rw-r–r–¬† root (0)¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 1624179¬†¬†¬† Wed 07 Jul 2010 10:22:11 AM IST
/boot/config-2.6.32.12-115.fc12.i686
-rw-r–r–¬† root (0)¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 106890¬†¬†¬†¬† Sat 01 May 2010 02:19:02 AM IST
/boot/config-2.6.32.14-127.fc12.i686
-rw-r–r–¬† root (0)¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 106890¬†¬†¬†¬† Fri 28 May 2010 10:47:55 AM IST
/boot/config-2.6.32.16-141.fc12.i686
-rw-r–r–¬† root (0)¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† 106915¬†¬†¬†¬† Wed 07 Jul 2010 10:22:11 AM IST
/boot/efi
drwxr-xr-x  root (0)             1024       Tue 10 Nov 2009 12:43:21 AM IST

…….I have snipped the output …because it is huge and very informative.

Step 10: Update the tripwire database

Sometime it needs to update the database file for get rid of false warning and errors. So for that we need to update the database file for the tripwire.

root@bhaskar-laptop_10:28:35_Sun Jul 25:/home/bhaskar # tripwire –update –twrfile /var/lib/tripwire/report/bhaskar-laptop-20100725-095014.twr

It will put you to a temporary file editor to update the file check.If you really don’t want to informed about anything specific out of this file just remove the “X” beside that option and save the file.

Step 11: Update the policy file

We will use a binary ship with the tripwire software called “twadmin”.

First as the norm that once you finished with configured tripwire for your system should delete the clear text policy file .Now if need to view it once more then you can generate it like this:

root@bhaskar-laptop_10:28:35_Sun Jul 25:/home/bhaskar # twadmin –print-profile > /etc/tripwire/twpol.txt

So the next step is that I am gonna encrypt the file :

root@bhaskar-laptop_10:38:13_Sun Jul 25:/etc/tripwire #/usr/sbin/twadmin –create-polfile -S site.key /etc/tripwire/twpol.txt

Please enter your site passphrase:

Wrote policy file: /etc/tripwire/tw.pol

Now I am going update the database with new created encrypted policy file . For that I need to remove the existing database file:

root@bhaskar-laptop_10:38:13_Sun Jul 25:/etc/tripwire # rm /var/lib/tripwire/bhaskar-laptop.twd
rm: remove regular file `/var/lib/tripwire/bhaskar-laptop.twd’? y

And I said “yes ” to it ..so it removed.

So recreate the database once more:

root@bhaskar-laptop_10:42:14_Sun Jul 25:/etc/tripwire # tripwire –init

Please enter your local passphrase:

………

Wrote database file: /var/lib/tripwire/bhaskar-laptop.twd
The database was successfully generated.

Once it is done we will recheck it once more :

root@bhaskar-laptop_10:47:03_Sun Jul 25:/etc/tripwire # tripwire –check

Wrote report file: /var/lib/tripwire/report/bhaskar-laptop-20100725-104915.twr

Open Source Tripwire(R) 2.4.1 Integrity Check Report

Report generated by:          root
Report created on:            Sun 25 Jul 2010 10:49:15 AM IST
Database last updated on:     Never

===============================================================================
Report Summary:
===============================================================================

Host name:                    bhaskar-laptop
Host IP address:              127.0.0.1
Host ID:                      None
Policy file used:             /etc/tripwire/tw.pol
Configuration file used:      /etc/tripwire/tw.cfg
Database file used:           /var/lib/tripwire/bhaskar-laptop.twd
Command line used:¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† tripwire –check

===============================================================================
Rule Summary:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

Rule Name                       Severity Level    Added    Removed  Modified
———¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬† ————–¬†¬†¬† —–¬†¬†¬† ——-¬† ——–
Invariant Directories           66                0        0        0
Temporary directories           33                0        0        0
* Tripwire Data Files             100               1        0        0
Critical devices                100               0        0        0
User binaries                   66                0        0        0
Tripwire Binaries               100               0        0        0
Critical configuration files    100               0        0        0
Libraries                       66                0        0        0
Operating System Utilities      100               0        0        0
Critical system boot files      100               0        0        0
File System and Disk Administraton Programs
100               0        0        0
Kernel Administration Programs  100               0        0        0
Networking Programs             100               0        0        0
System Administration Programs  100               0        0        0
Hardware and Device Control Programs
100               0        0        0
System Information Programs     100               0        0        0
Application Information Programs
100               0        0        0
Shell Related Programs          100               0        0        0
Critical Utility Sym-Links      100               0        0        0
Shell Binaries                  100               0        0        0
System boot changes             100               0        0        0
OS executables and libraries    100               0        0        0
Security Control                100               0        0        0
Login Scripts                   100               0        0        0
Root config files               100               0        0        0

Total objects scanned:  36199
Total violations found:  1

===============================================================================
Object Summary:
===============================================================================

——————————————————————————-
# Section: Unix File System
——————————————————————————-

——————————————————————————-
Rule Name: Tripwire Data Files (/var/lib/tripwire)
Severity Level: 100
——————————————————————————-

Added:
“/var/lib/tripwire/bhaskar-laptop.twd”

===============================================================================
Error Report:
===============================================================================

——————————————————————————-
Section: Unix File System
——————————————————————————-

Integrity check complete.

Hope this will help.

Cheers!

Bhaskar

Configuring Apache and Nginx | Packt Publishing Technical & IT Book Store

Configuring Apache and Nginx | Packt Publishing Technical & IT Book Store

via Configuring Apache and Nginx | Packt Publishing Technical & IT Book Store.

Measure bandwidth for the dialup internet connection

So running internet with a dialup connection in India is pain on the butt.That is why one needs to measure the bandwidth he/she is getting from their service provider.

I am gonna use age old tool called “Vnstat”; you can find it here.

Now once you got the software( As I have four different version of GNU/Linux running in my laptop..so it’s installation procedure would be different) But for the sake of clarity I am sticking with Gentoo.

Step 1: Install the software

bhaskar@bhaskar-laptop_09:39:03_Tue Jul 20:~> sudo emerge -av vnstat
Password:

These are the packages that would be merged, in order:

Calculating dependencies… done!
[ebuild R ] net-analyzer/vnstat-1.10 USE=”-gd” 0 kB

Total: 1 package (1 reinstall), Size of downloads: 0 kB

Would you like to merge these packages? [Yes/No]

Here I put N, because I have installed it long time back and using it for quite some time.If you put Y then it will fetch the software from the site and install it.I decided not to reinstall it.The ” R” signifies “Reinstall”.

Step 2: Get the file install in the system

bhaskar@bhaskar-laptop_09:50:07_Tue Jul 20:~> sudo qlist vnstat
/var/run/vnstatd/.keep_net-analyzer_vnstat-0
/var/lib/vnstat/.keep_net-analyzer_vnstat-0
/usr/share/doc/vnstat-1.10/FAQ.bz2
/usr/share/doc/vnstat-1.10/README.setup.bz2
/usr/share/doc/vnstat-1.10/README.bz2
/usr/share/doc/vnstat-1.10/CHANGES.bz2
/usr/share/doc/vnstat-1.10/vnstat.cgi.bz2
/usr/share/doc/vnstat-1.10/ip-up.example.bz2
/usr/share/doc/vnstat-1.10/ip-down.example.bz2
/usr/share/doc/vnstat-1.10/UPGRADE.bz2
/usr/share/man/man1/vnstat.1.bz2
/usr/share/man/man1/vnstatd.1.bz2
/usr/bin/vnstatd
/usr/bin/vnstat
/etc/init.d/vnstatd
/etc/vnstat.conf
/etc/conf.d/vnstatd
/etc/cron.hourly/vnstat

Step 3: Main files to interact with

/var/lib/vnstat/
This directory contains all databases the program uses. Files are named according to the monitored interfaces.

/etc/vnstat.conf
Config file that will be used unless $HOME/.vnstatrc exists. See vnstat.conf(5) for more information.

Step 4: Get the particular interface initialised with it and create database for it

bhaskar@bhaskar-laptop_09:53:30_Tue Jul 20:~> sudo /usr/bin/vnstat -u -i ppp0

So it does basically create a database file for the interface of ppp0 and make a mark for update of it.The option “u” for update and “i” to initialize that interface. It could be eth0, wlan0 etc.

Step 5:Get the statistics on your terminal

bhaskar@bhaskar-laptop_09:56:22_Tue Jul 20:~> sudo vnstat
Password:
Database updated: Tue Jul 20 09:55:32 2010

ppp0 since 11/28/09

rx: 6.47 GiB tx: 933.61 MiB total: 7.38 GiB

monthly
rx | tx | total | avg. rate
————————+————-+————-+—————
Jun ’10 1.16 GiB | 167.63 MiB | 1.32 GiB | 4.28 kbit/s
Jul ’10 1.15 GiB | 276.73 MiB | 1.42 GiB | 7.09 kbit/s
————————+————-+————-+—————
estimated 1.83 GiB | 440 MiB | 2.26 GiB |

daily
rx | tx | total | avg. rate
————————+————-+————-+—————
yesterday 120.60 MiB | 22.15 MiB | 142.76 MiB | 13.54 kbit/s
today 4.53 MiB | 1.13 MiB | 5.66 MiB | 1.30 kbit/s
————————+————-+————-+—————
estimated 9 MiB | 2 MiB | 11 MiB |

Now if you want some specific statistics of a month or day or year;you can do so by this:

Step 6: Specific to days

sudo vnstat -d

ppp0 / daily

day rx | tx | total | avg. rate
————————+————-+————-+—————
05/20/10 14.31 MiB | 4.34 MiB | 18.66 MiB | 1.77 kbit/s
05/24/10 85.00 MiB | 9.21 MiB | 94.21 MiB | 8.93 kbit/s
05/25/10 11.93 MiB | 3.19 MiB | 15.12 MiB | 1.43 kbit/s
05/26/10 56.32 MiB | 11.70 MiB | 68.02 MiB | 6.45 kbit/s
05/31/10 224.23 MiB | 22.86 MiB | 247.09 MiB | 23.43 kbit/s
06/01/10 59.62 MiB | 13.77 MiB | 73.40 MiB | 6.96 kbit/s
06/04/10 61.85 MiB | 15.58 MiB | 77.43 MiB | 7.34 kbit/s
06/06/10 12.85 MiB | 2.96 MiB | 15.80 MiB | 1.50 kbit/s
06/10/10 167.88 MiB | 13.60 MiB | 181.48 MiB | 17.21 kbit/s
06/13/10 199.02 MiB | 17.05 MiB | 216.07 MiB | 20.49 kbit/s
06/14/10 18.29 MiB | 1.91 MiB | 20.20 MiB | 1.92 kbit/s
06/16/10 169.26 MiB | 18.28 MiB | 187.54 MiB | 17.78 kbit/s
06/20/10 114.46 MiB | 15.88 MiB | 130.34 MiB | 12.36 kbit/s
06/22/10 39.43 MiB | 8.71 MiB | 48.14 MiB | 4.56 kbit/s
06/27/10 28.28 MiB | 8.23 MiB | 36.51 MiB | 3.46 kbit/s
06/30/10 315.76 MiB | 51.66 MiB | 367.42 MiB | 34.84 kbit/s
07/01/10 171.94 MiB | 45.67 MiB | 217.60 MiB | 20.63 kbit/s
07/02/10 37.74 MiB | 13.36 MiB | 51.10 MiB | 4.84 kbit/s
07/05/10 57.69 MiB | 16.18 MiB | 73.87 MiB | 7.00 kbit/s
07/06/10 119.98 MiB | 24.60 MiB | 144.58 MiB | 13.71 kbit/s
07/08/10 71.45 MiB | 19.33 MiB | 90.78 MiB | 8.61 kbit/s
07/11/10 296.82 MiB | 32.26 MiB | 329.08 MiB | 31.20 kbit/s
07/12/10 121.50 MiB | 26.05 MiB | 147.54 MiB | 13.99 kbit/s
07/13/10 30.79 MiB | 19.54 MiB | 50.33 MiB | 4.77 kbit/s
07/14/10 58.40 MiB | 25.19 MiB | 83.59 MiB | 7.93 kbit/s
07/15/10 39.39 MiB | 15.02 MiB | 54.40 MiB | 5.16 kbit/s
07/17/10 32.30 MiB | 11.46 MiB | 43.76 MiB | 4.15 kbit/s
07/18/10 12.51 MiB | 4.80 MiB | 17.31 MiB | 1.64 kbit/s
07/19/10 120.60 MiB | 22.15 MiB | 142.76 MiB | 13.54 kbit/s
07/20/10 4.53 MiB | 1.13 MiB | 5.66 MiB | 1.30 kbit/s
————————+————-+————-+—————
estimated 9 MiB | 2 MiB | 11 MiB |

Now to get the hourly stat out of this.

Step 7:Hourly stat

sudo vnstat -h
ppp0 09:55
^ r
| r r
| r r
| r r
| r r
| r r
| r r r
| r r r
| r r r r r
| rt r rt r r rt r
-+—————————————————————————>
| 10 11 12 13 14 15 16 17 18 19 20 21 22 23 00 01 02 03 04 05 06 07 08 09

h rx (KiB) tx (KiB) h rx (KiB) tx (KiB) h rx (KiB) tx (KiB)
10 11011 4344 18 0 0 02 0 0
11 9700 3176 19 0 0 03 0 0
12 0 0 20 0 0 04 0 0
13 16154 4001 21 0 0 05 0 0
14 39047 2395 22 0 0 06 0 0
15 37631 3193 23 0 0 07 0 0
16 7794 4202 00 0 0 08 0 0
17 2167 1377 01 0 0 09 4636 1161

Now want to get some statistics related to months,here is how to get it

Step 8:
bhaskar@bhaskar-laptop_10:01:55_Tue Jul 20:~> sudo vnstat -m

ppp0 / monthly

month rx | tx | total | avg. rate
————————+————-+————-+—————
Nov ’09 0 KiB | 0 KiB | 0 KiB | 0.00 kbit/s
Feb ’10 17.67 MiB | 1.46 MiB | 19.12 MiB | 0.06 kbit/s
Mar ’10 1.22 GiB | 99.70 MiB | 1.32 GiB | 4.14 kbit/s
Apr ’10 1.32 GiB | 204.41 MiB | 1.52 GiB | 4.93 kbit/s
May ’10 1.60 GiB | 183.68 MiB | 1.77 GiB | 5.56 kbit/s
Jun ’10 1.16 GiB | 167.63 MiB | 1.32 GiB | 4.28 kbit/s
Jul ’10 1.15 GiB | 276.84 MiB | 1.42 GiB | 7.09 kbit/s
————————+————-+————-+—————
estimated 1.83 GiB | 440 MiB | 2.26 GiB |

Now lets get some stat about the week

Step 9: Weekly stat

bhaskar@bhaskar-laptop_10:06:04_Tue Jul 20:~> sudo vnstat -w

ppp0 / weekly

rx | tx | total | avg. rate
—————————+————-+————-+—————
last 7 days 298.77 MiB | 99.54 MiB | 398.31 MiB | 5.88 kbit/s
last week 294.88 MiB | 102.05 MiB | 396.93 MiB | 5.38 kbit/s
current week 125.38 MiB | 23.54 MiB | 148.92 MiB | 9.94 kbit/s
—————————+————-+————-+—————
estimated 617 MiB | 113 MiB | 730 MiB |

OK.. now some internals …you can actually clear the database and rebuild it at your will.So the steps are:

Step 10 : Clear the Database and rebuild it

sudo vnstat -r # for resetting the tool

then

sudo vnstat –rebuildtotal # Reset the total traffic counters and recount those using recorded months.

You can also specify which interface to monitor or not by passing an flag to it..

sudo vnstat –enable

OR

sudo vnstat –disable

But there is an option to extract out the database info ,which is called “–dumpdb”

lets get it from the man page :

Instead of showing the database with a formated output, this output will dump the whole database in a format that should be easy to parse with
most script languages. Use this for example with PHP, Perl or Python to make a custom webpage. The dump uses ; as field delimeter.

active;1 activity status
interface;eth0 name for the interface
nick;inet nick (if given)
created;1023895272 creation date in Unix time
updated;1065467100 when the database was updated
totalrx;569605 all time total received MiB
totaltx;2023708 all time total transmitted MiB
currx;621673719 latest rx value in /proc
curtx;981730184 latest tx value in /proc
totalrxk;644 total rx KiB counter
totaltxk;494 total tx KiB counter
btime;1059414541 system boot time in Unix time

Then follows 30 lines like the following

d;0;1078696800;559;7433;68;557;1

where d = days, 0 = day number in database (0 is today), 1077314401 date in Unix time, 559 = rx MiB, 7433 = tx MiB, 68 = rx KiB, 557 = tx KiB and
1 tells that vnStat has filled this value and it is in use.

m;0;1078092000;48649;139704;527;252;1 (x12)
t;0;1078351200;5979;47155;362;525;1 (x10)
h;0;1078699800;118265;516545 (x24)

m = months, t = top10 and h = hours, all other fields are in the same order as in days except hours that doesn’t have a separate KiB value. For
hours the forth and fifth fields have values in KiB.

So it clearly stated it purpose.

Step 11: Top 10 traffic list

bhaskar@bhaskar-laptop_10:17:10_Tue Jul 20:~> sudo vnstat -t

ppp0 / top 10

# day rx | tx | total | avg. rate
—————————–+————-+————-+—————
1 03/10/10 627.71 MiB | 33.10 MiB | 660.81 MiB | 62.65 kbit/s
2 04/16/10 344.85 MiB | 28.56 MiB | 373.40 MiB | 35.40 kbit/s
3 06/30/10 315.76 MiB | 51.66 MiB | 367.42 MiB | 34.84 kbit/s
4 05/06/10 312.34 MiB | 26.65 MiB | 338.99 MiB | 32.14 kbit/s
5 07/11/10 296.82 MiB | 32.26 MiB | 329.08 MiB | 31.20 kbit/s
6 04/24/10 300.84 MiB | 24.38 MiB | 325.22 MiB | 30.84 kbit/s
7 05/05/10 271.04 MiB | 23.46 MiB | 294.50 MiB | 27.92 kbit/s
8 05/04/10 265.08 MiB | 23.83 MiB | 288.91 MiB | 27.39 kbit/s
9 05/31/10 224.23 MiB | 22.86 MiB | 247.09 MiB | 23.43 kbit/s
10 04/30/10 213.40 MiB | 25.67 MiB | 239.07 MiB | 22.67 kbit/s
—————————–+————-+————-+—————

Curious to know more? Check out the website of it and of course the man paged.

Hope this will help.

Cheers!
Bhaskar

Guide to porting from Solaris to Linux on POWER

Wonderful guide to porting to different system .

Guide to porting from Solaris to Linux on POWER.

HOW TO: Teach your old Mutt some new tricks

Good points to remember.

HOW TO: Teach your old Mutt some new tricks.