MySQL : A little security tip

>In this article I am going to show you a little trick about MySQL ,which might help you to prevent some attack on production database server.Every MySQL server has a root user attached to it and for intruders it’s very easy to guess that and launch attack on it.

Here is how you can change the root user name for that MySQL server :


bhaskar@bhaskar-laptop_08:55:51_Thu Jan 13:~> sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.51-log Gentoo Linux mysql-5.1.51

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>use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where
User='';
mysql> flush privileges;
mysql> quit

That’s all!!

Hope this will help.

Cheers!
Bhaskar

Advertisements

Get alert about LAMP stack

In this article I am going to show you a very very rudimentary script to get over it.I have written it just to show you how it can be done,but having said that this script can be written with more information and much more complex way.Yes ,there is a  lot scope of improvement of this script and I would like to get your feedback on that.

So without much delay here is the mundane script to monitor the LAMP stack and provide alert


1 #!/bin/bash
2 # This program is written for my own help.Needs lot of tweaking.
3 #This script is written on Fedora and RHEL keep in mind,so other distribution
4 #should cross check the binary space of the specified program to their distribution.
5 #Author : Bhaskar Chowdhury
6 #Date : 03-12-2010
7
8
9 date=`date`
10
11 echo "Today is :" $date
12 echo
13 echo
14 host_name=`hostname`
15 echo "This is for the $host_name"
16 echo
17 echo
18 #System binary location information
19 kernel=`uname -r`
20
21 apache=`/usr/sbin/httpd -v`
22
23 mysql=`/usr/bin/mysql --version`
24
25 php=`/usr/bin/php -v`
26
27 webroot='/var/www/html'
28
29 mail='whoeverincahrgeofit@gmail.com' #The person in-charge of lamp stack
30
31 echo "******************************* GNU/Linux ******************************"
32
33 echo " We are running Linux Kernel: " $kernel
34 echo
35 echo
36
37
38
39
40 echo
41 echo
42 echo "Check out kernel stuff...."
43 echo
44
45
46 /sbin/sysctl -p
47 echo
48 echo
49
50
51 echo
52 echo "********************* OS End *************************"
53 echo
54 echo
55
56 sleep 5
57
58
59 echo "************ Apache ******************"
60 echo
61
62 echo " We are running Apache : " $apache
63 echo
64 echo
65
66
67 echo " How Apache has been compiled with other stuff : "
68 echo
69 echo
70
71 /usr/sbin/httpd -V
72
73 echo
74 echo
75
76 echo "Check out the modules loaded with it.."
77
78 /usr/sbin/httpd -M
79
80 echo
81 echo
82 echo
83
84 echo " Check out Apache process ..."
85 echo
86
87 ps -ef | grep httpd
88
89 echo
90 echo
91
92 sleep 5
93
94 echo
95 echo "***************************** Apache End *************************"
96 echo
97 echo
98
99
100 echo " **************** PHP **********************"
101 echo
102
103
104 echo "We are running Php : " $php
105 echo
106 echo
107
108
109 echo " Let's check out php related thing ..checking ini files....."
110 echo
111 echo
112
113 php --ini
114
115 echo
116 echo
117 echo " Get details of php things into the system..like modules and other stuff..."
118 echo
119 echo
120
121 php -im
122
123 echo
124 echo
125 echo
126 echo "****************************** PHP End **********************************"
127 echo
128 echo
129
130 sleep 5
131
132
133 echo "************************** MySql ***********************"
134
135
136 echo "We are running MySql : " $mysql
137 echo
138 echo
139 echo
140
141
142 echo " Check out the process for it....."
143 echo
144 echo
145
146 ps -ef | grep mysql
147
148 echo
149 echo
150
151
152 echo "****************************** MySql End **************************"
153 echo
154 echo
155
156 sleep 5
157
158 echo " Let's check the web tree permission.."
159
160 echo
161
162 ls -d $webroot
163
164 echo
165 echo
166
167 echo " Check out the SELinux thing applied on the webtree ..."
168 echo
169 echo
170
171 ls -Z $webroot
172 echo
173 echo
174
175 #if [[ `pidof httpd` -ne 0 ]]
176 # then
177 # echo "Httpd seems working fine!"
178 #else
179 # echo " problem with httpd daemon"
180 #fi
181
182 #if [[ `pidof mysql` -ne 0 ]]
183 #then
184 # echo " Yup,mysql working"
185 #else
186 #echo " Seems to check the mysql thing"
187 #fi
188
189
190 #if [[`pidof php` -ne 0 ]]
191 #then
192 # echo "Looks good"
193 #else
194 # echo " Problem with php"
195 #fi
196
197 echo " If the stack goes down ..let me know.."
198 echo
199 echo
200
201 if [[`/sbin/pidof httpd mysqld php` -eq 0 ]]
202 then
203 mail -s "problem with lamp stack" $mail
204 echo "Mail sent with information"
205 fi

Now you can trash out lot of echo statement out of that script,but I’ve kept it for readability .The basic motto behind that script to check out the binary position of those LAMP(GNU/Linux,Apache,MySql,Php/Perl/Python) software and call them to verify it.Then I move onto checking how the web server( In this case Apache) was complied with what sort of flags.

Next checking out ini file related to php thing and then mysql server started or not.Then I checked the web root tree permission and SELinux thing with it.Finally if something not working (by checking the pid of it,kindly point out the better way to check)and if that is equal to zero then surely something not came up in the stack and need attention.

This script is just an outline and as I said need your feedback(with reason) to improve it.

Hope this will help.

Cheers!
Bhaskar

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

Shell Tips ! » Fine tuning a Linux Apache MySQL PHP (LAMP) server

This is very good explanation of LAMP stack.Although many more thing can be consider out of this.A very good starting point to ponder over the fact.

Shell Tips ! » Fine tuning a Linux Apache MySQL PHP (LAMP) server

Bhaskar Chowdhury
Chat Google Talk: unixbhaskar Skype: unixbhaskar Y! messenger: unixbhaskar
Contact Me StumbleuponFacebookDiggTwitterBloggerRedditTechnoratiWordpress