Mysql_Notes2

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.