Retrive_mysql_root_login
Recover mysql root login:
1.
# /etc/init.d/mysql stop
or service mysqld stop
2.
# mysqld_safe --skip-grant-tables &
3.
# mysql -u root
4.
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
5.
# /etc/init.d/mysql stop
or service mysqld stop
6.
# /etc/init.d/mysql start or service mysqld start
# mysql -u root -p
<enter the new password>
*) How to change mysql root password:
$ mysqladmin -u root password NEWPASSWORD
to update the password: $ mysqladmin -u root -p'oldpassword' password newpass
example: $ mysqladmin -u root -p'abc' password '123456'
changing the password of the other user:
$ mysqladmin -u <username> -p oldpassword password newpass
Changing MySQL root user password using MySQL sql command
$ mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='user_name';
mysql> flush privileges;
mysql> quit
*) original link from:
http://www.cyberciti.biz/faq/mysql-change-root-password/
http://www.cyberciti.biz/tips/recover-mysql-root-password.html
perfomance_tunning
Do check things on key_buffer_size query_check_size and query_cache_type.
For a 8GB RAM system we can set the following to get few improvements:
to check the current values (at) mysql prompt:
show variables like 'key_buffer_size';
show variables like 'query_cache_size';
show variables like 'query_cache_type';
to update the values:
set global key_buffer_size=3221225472; [ 3 GB ]
set global query_cache_size=268435456; [256 MB ]
set global query_cache_type=ON;
Do monitor the memory information such as swap and physical memory after changes and you might like to check the slow query log file to find still the same query is taking long time or not.
Mysql_Notes
Note: On a System we can have multiple DB [ database ] and the default one is mysql. Let's say along with mysql we have one more database known as "MYDB" and if you need to grant some permission to someone on the "MYDB" then all those credentials stays as "mysql" database.
The username and password stay at "user" table where as the database information stays at "db" tables.
1. mysql -uroot -p -h localhost
2. show databases;
3. create database world;
4. use world;
5. source /home/amit/world.sql [ download the same database from http://dev.mysql.com/doc/index-other.html -> MyISAM or Innodb]
6. show tables;
NOTE: "$" -> unix console and ">" -> mysql console [ default is mysql console ]
7. Communication protocols: [ protocol - Types of connections - OS ]
7a. TCP/IP - local,remote - All
7b. Unix socket file - local only - Unix only
7b. Shared memory - local only - windows only
7d. Named pipes - local only - windows only
8. Table names are case sensitivity.
9. Default installation location: /usr/local/mysql [ currently in my system its at: /var/lib/mysql] and the files I can see with in /var/lib/mysql/world:
a. City.frm
b. City.MYD
c. City.MYI
d. Country.frm
e. Country.MYD
f. Country.MYI
g. CountryLanguage.frm
h. CountryLanguage.MYD
i. CountryLanguage.MYI
j. db.opt
10. Basic commands that must be executed to install a MySQL source distribution:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> cd /usr/local/mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql var
shell> bin/mysqld_safe --user=mysql &
11. mysql> select @@global.time_zone, @@session.time_zone; [ to know the current values of the global and client-specific time zones ]
12. mysql -uroot -p
13. use mysql
14. show tables like 'time_zone%' [ show the system tables that have somethings to do with time zones ]
mysql> show tables like 'time_zone%';
+------------------------------+
| Tables_in_mysql (time_zone%) |
+------------------------------+
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+------------------------------+
5 rows in set (0.00 sec)
15. describe time_zone\G [ display the design of the tabl time_zone ] -> this specific listing shows that there are two columns in the "time_zone" table with the details of what each column stores and how it stores it.
mysql> describe time_zone\G;
*************************** 1. row ***************************
Field: Time_zone_id
Type: int(10) unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: Use_leap_seconds
Type: enum('Y','N')
Null: NO
Key:
Default: N
Extra:
2 rows in set (0.00 sec)
16. select * from time_zone; [ display the contents of the time_zone ]
17. Different method of starting mysql server on linux:
a. mysqld: Invoke manually for debugging.
b. mysqld_safe: launches, monitors and restarts mysqld if needed.
c. mysql.server: wrapper for mysqld_safe for O/S's using system V
d. mysqld_multy: perl script for managing myltiple servers.
18. On linux: to start and stop:
a. /etc/rc.d/init.d/mysql start
b. /etc/rc.d/init.d/mysql stop
19. If the server does not start properly, look in the error log. The default error log name on Linux is host_name.err in the data directory, where host_name is the name of the server host.
20. Method and Descriptions:
a. mysqladmin: Connects to server as client to shutdown server local or remote.
b. mysql.server: Will stop and/or shutdown the local server
c. mysqld_multi: Invokes mysqladmin to stop and/or shutdown servers it manages.
21. mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables [ To check and repair tables and to upgrade the system tables ]
22. help <mysql_command> [ to get help on the mysql_command ]
23. mysql_upgrade --help
24. $mysql --help;
25. Few and/or default mysql server configuration:
[on windows]
a. basedir:
b. datadir:
c. shared-memory
d. enable-named-pipe
e. general_log
f. log-bin
g. slow_query_log=[1|0] -> [ 1=enable, 0=disable]
h. default-storage-engine=InnoDB
i. max_connections=200
j. key_buffer_size=128M
k. slow_query_log_file
26. $my_print_defaults [ display the options that are present in option group of the option files]
27. $mysql --print-defaults [ the same option file information can also be listed from the command ]
28. show variables like 'bulk%';
29. set global
30. set session
31. set bulk_insert_buffer_size=4100000;
32. show global status; [ display the status values for all connections to mysql]
33. show status [ provides server status information ]
34. show session status; [ display the status values for the current connection]
35. sql-mode=IGNORE_SPACE [ Setting the SQL mode ]
36. set [session|global] sql_mode='mode_value'
37. select @@sql_mode; [ to check the current sql_mode settings ]
38. set sql_mode='TRADITIONAL'; [ to set sql_mode to traditional ]
39. SQL MODE Values:
a. ANSI_QUOTES: This mode causes the double quote character (‘"’) to be interpreted as an identifier- quoting character rather than as a string-quoting character.
b. IGNORE_SPACE: By default, functions must be written with no space between the function name and the following parenthesis. Enabling this mode causes the server to ignore spaces after function names. This allows spaces to appear between the name and the parenthesis, but also causes function names to be reserved words.
c. ERROR_FOR_DIVISION_BY_ZERO: By default, division by zero produces a result of NULL and is not treated specially. Enabling this mode causes division by zero in the context of inserting data into tables to produce a warning, or an error in strict mode.
d. STRICT_TRANS_TABLES, STRICT_ALL_TABLES: These values enable "strict mode", which imposes certain restrictions on what values are acceptable as database input. By default, MySQL is forgiving about accepting values that are missing, out of range, or malformed. Enabling strict mode causes bad values to be treated as erroneous. STRICT_TRANS_TABLES enables strict mode for transactional tables, and STRICT_ALL_TABLES enables strict mode for all tables.
e. TRADITIONAL: Enables strict modes plus several restrictions on acceptance of input data. Enforces restrictions on input data values that are like other database servers, rather than MySQL's more forgiving behavior. Allows user accounts to be created only with the GRANT statement when a password is specified
f. ANSI: This is a composite mode that causes MySQL server to be more "ANSI-like". That is, it enables behaviors that are more like standard SQL, such as ANSI_QUOTES (described earlier) and PIPES_AS_CONCAT, which causes || to be treated as the string concatenation operator rather than logical OR.
mysqldumpslow
syntax:
mysqldumpslow <mysql_slow_log_file>
mysqldumpslow -s at <mysql_slow_log_file>
Count: 438 Time=5.80s (2540s) Lock=0.00s (0s) Rows=4331.9 (1897393), moviesfe[moviesfe]@4hosts
Notes on mysqldumpslow:
NOTE: -s t [ t for count => sort on hight count of a same query ]
NOTE: -s at [ at for time => sort on hight time take query ]
NOTE: -s l [ sort bashed on the query those lock the table ]
NOTE: -s al [ Sort on lock and large query size -> do a recheck, might be large query size ]
NOTE: -s r [ bashw=ed on row]
is in order:
t=count, at=time, l=lock, al=rows, r=
$mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
mysqlbinlog
1. --start-datetime and --stopdatetime syntax:
mysqlbinlog --start-datetime="11/09/06 04:00" --stop-datetime="11/09/06 05:00" <binfile_name>
Following command also works:
mysqlbinlog <binfile_name> --start-datetime="11/09/06 04:00" --stop-datetime="11/09/06 05:00"
mysqlbinlog -d <dbname> <binfile_name>
mysqlbinlog -help
mysqlbinlog Ver 3.0 for unknown-linux-gnu at x86_64
By Monty and Sasha, for your professional use
This software comes with NO WARRANTY: This is free software,
and you are welcome to modify and redistribute it under the GPL license
Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client
Usage: mysqlbinlog [options] log-files
-d, --database=name List entries for just this database (local log only).
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-f, --force-read Force reading unknown binlog events.
-?, --help Display this help and exit.
-h, --host=name Get the binlog from server.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
-P, --port=# Use port to connect to the remote server.
-j, --position=# Deprecated. Use --start-position instead.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-r, --result-file=name
Direct output to a given file.
-R, --read-from-remote-server
Read binary logs from a MySQL server
--open_files_limit=#
Used to reserve file descriptors for usage by this
program
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show the queries, no extra info.
-S, --socket=name Socket file to use for connection.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-V, --version Print version and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
database (No default value)
disable-log-bin FALSE
force-read FALSE
host elp
offset 0
port 3306
position 4
read-from-remote-server FALSE
open_files_limit 64
set-charset (No default value)
short-form FALSE
socket /tmp/mysql.sock
start-datetime (No default value)
stop-datetime (No default value)
start-position 4
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
local-load (No default value)
Creating a new table with the time value: and then entering value of the current time too:
* create table <table_name> (TIMESTAMP timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, TRAFFIC int(4), RES_TIME_MS int(5));
* insert into <table_name> value(now(), 17,65);
- From command line updating the value:
* mysql -uroot -p<password> -e "use <database_name>; insert into <table_name> value(now(), 17,65)"
* mysql -uroot -e "insert into <database_name>.<table_name> vaule(now(),17,70)"
* mysql -uroot -e "select * from <database_name>.<table_name>"
6) How to create MYSQL new users?
There are many different ways to establish users and privileges in MYSQL. Client and GRANT command assure you about a safe connection. The syntax for establishing new users and privileges is as follows
GRANT privileges ON database.* TO
‘username’@’hostname’ This can be identified by the password. Privileges can be assigned one by one or by specifying all.
7) Explain about the rules which should be followed while assigning a username?
A username has a maximum length of 16 characters. Spaces should be avoided while creating username because they are case sensitive. Hostname will be the computer from which you are going to connect. The best way to specify a username is to connect through a local host.
8) Explain about a security flaw which is present while creating a username?
Naming MYSQL databases has to be very careful because any database starting with the test name can be accessed by every one on the network. Make sure that you don’t start the databases naming with test. It should be used only for experimental purposes only.
9) State some security recommendations while using MYSQL?
Some of the security recommendations which should be followed while using MYSQL are as follows: -
1) Minimal privileges to users in the network.
2) Super and process privileges should be granted minimally.
3) File privileges should be granted minimally to administrators.
4) Validation of data and queries should be thoroughly checked.
10) Explain about database design?
Database design is also called as Data modeling. It is used for long-term management of database. This process is used to store information and to keep data for long term. Creating an efficient structure helps you to channelize information into good channels.
11) Explain about creating database?
CREATE DATABASE command will create you a database with the assigned name by the user. This is an optional statement but when you actually assign a name it checks for similarity and gives error if it encounters one. CREATE DATABASE models help you to create classic models.
12) Explain about primary keys?
MYSQL allows only one primary key. This primary key can be used on multiple tables. There are many rules which should be followed such as it shouldn’t be null and it can never change. Primary key assigned should be unique it cannot be matched with any other keys.
13) Explain about normalization?
Applying specific rules (normal forms) to the database is the primary process. These rules should be applied in the order specified starting with the first normal form. These rules should be adhered by every database they are
1) Each column should have only one value
2) Repeating columns of data cannot be done.
14) State two considerations which can improve the performance of MYSQL?
Two considerations which can improve the performance of MYSQL are as follows: -
1) Fixed length fields take up more space than variable length fields but they are a bit faster.
2) Size of the field should be restricted to the smallest possible value based upon the largest input value.
15) Explain about the time stamp field?
TIMESTAMP filed occurs when an INSERT and UPDATE field occurs when there is no value specified for the field. There are many behaviors for TIMESTAMP field and it depends upon the version of MYSQL.
16) Explain about MyISAM table?
This feature is a default type for tables. This table is not so much considered for transactions because it is not considered as safe but this kind of table is very fast in execution. The maximum key length is 1024 bytes and 64 keys per table. Size of this table entirely depends upon the operating system.
17) Explain about HEAP table?
This type of table is stored in the memory. Speed of execution of this table is very commendable. There are associated disadvantages associated with this table the primary one being loss of stored memory which occurs when there is power failure and can cause the server to run out of memory. Columns with AUTO_INCREMENT, TEXT characteristics and BLOB are not supported.
