Hands on MySQL Administration
July 30, 2010 Leave a comment
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
Unixbhaskar's Blog 