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 .

I do not issue any guarantee that this will work for you.

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

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 do not issue any guarantee that this will work for you.

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