MySQL must know the common sense

• Objective

The book reads times, its righteousness is self-existing. We all know, but now the society is really impetuous, and how many people can calm down to see the book?! As long as a problem is Baidu, Google ... In fact, the book is very clear, all the solutions are in the book, for a product understanding, and who is more than the people who compiled him more clear!

Write to yourself, only with introspection!

• Resources

MySQL Official Handbook 5.1

• MySQL Program Overview:

MYSQLD is the MySQL server

Mysqld_safe, Mysql.server is a server startup script

mysql_install_db initializing the Data directory and the initial database

• Accessing the server's client program

MySQL is a command-line client program that executes SQL statements interactively or in batch mode.

Mysqladmin is a client program for managing functions.

Mysqldump is responsible for database backup.

• Declaring environment variables (compilation installation)

Echo ' Export path=/application/mysql/sbin: $PATH ' >/etc/profile

• MySQL executes SQL statements at the shell command line using the-e option

Shell> mysql-u root-p-E "show databases;"

Shell> mysql-u root-p-E "select User,host from MySQL. User; "

• MySQL Options Detailed

/etc/my.cnf==> global option, which is the startup option

Any long options that can be given at the command line when running a MySQL program can also be given in the options file.

The syntax for specifying options in the options file is similar to the command-line syntax, with the exception of ignoring two dashes.

If you have a source code distribution, you can find a sample option file named My-xxxx.cnf from the Support-file directory.

my-small.cnf Default configuration:

[[Email protected] support-files]# cat my-small.cnf# example mysql config  file for small systems.## This is for a system with  little memory  (<= 64m)  where MySQL is only used# from  Time to time and it ' s important that the mysqld daemon#  doesn ' t use much resources.## mysql programs look for option  Files in a set of# locations which depend on the deployment  platform.# You can copy this option file to one of  those# locations. for information about these locations, see:# http:// dev.mysql.com/doc/mysql/en/option-files.html## in this file, you can use  all long options  that a program supports.# if you want to know which options  a program supports, run the program# with the  "--help"   option.# the following options will be passed to all mysql  Clients[client] #password        = your_passwordport             = 3306socket           = /application/mysql5.1.72/tmp/mysql.sock# here follows entries  for some specific programs# The MySQL server[mysqld]port             = 3306socket           = /application/mysql5.1.72/tmp/mysql.sockskip-lockingkey_buffer_size =  16kmax_allowed_packet  = 1mtable_open_cache = 4sort_buffer_size = 64kread_buffer_size = 256kread_ Rnd_buffer_size = 256knet_buffer_length = 2kthread_stack = 128k# don ' t  listen on a TCP/IP port at all. This can be a  Security enhancement,# if all processes that need to connect to  mysqld run on the same host.# all interaction with mysqld  must be made via Unix sockets or named pipes.# Note  that using this option without enabling named pipes on windows#   (using the  "Enable-named-pipe"  option)  will render mysqld useless!#   #skip-networkingserver-id       = 1# uncomment the  following if you want to log updates#log-bin=mysql-bin# binary logging format -  Mixed recommended#binlog_format=mixed# uncomment the following if you are  using innodb tables#innodb_data_home_dir = /application/mysql5.1.72/data#innodb_data _file_path = ibdata1:10m:autoextend#innodb_log_group_home_dir = /application/mysql5.1.72/ DATA#&NBSP;YOU&NBSP;CAN&NBSP;SET&NBSP, .... _buffer_pool_size up to 50 - 80 %# of ram but beware of  setting memory usage too high#innodb_buffer_pool_size = 16m#innodb_ ADDITIONAL_MEM_POOL_SIZE&NBSP;=&NBSP;2M#&NBSP;SET&NBSP, .... _log_file_size to 25 % of buffer pool size#innodb_log_file_size =  5m#innodb_log_buffer_size = 8m#innodb_flush_log_at_trx_commit = 1#innodb_lock_wait_ Timeout = 50[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash# remove the next comment  Character if you are not familiar with sql#safe-updates[myisamchk]key_ Buffer_size = 8msort_buffer_size = 8m[mysqlhotcopy]interactive-timeout

Modify Options :

shell> MySQL--max_allowed_packet=16m maximum length allowed for communication

[MySQL]

max_allowed_packet=16m

Priority level:

The MySQL program first checks the environment variables--The options file--and the command line to determine which options are given.

If you specify an option more than once, the last option that appears is preempted. This indicates that the environment variable has the lowest priority, and the command-line option has the highest priority.

Command	Module
Mysqld	Read options from [mysqld] and [Server] groups
Mysqld_safe	Read options from [mysqld], [Server], [Mysqld_safe], and [safe_mysqld] groups
Mysql.server	Read options from [mysqld] and [mysql.server] groups.

• Server-side scripting

--mysqld_safe

Server startup scripts. If Mysqld-max exists, Mysqld_safe tries to start it, otherwise it starts mysqld.

Mysqld_safe reads all options from the [mysqld], [Server], and [Mysqld_safe] sections of the options file.

As a general rule, you should not edit the Mysqld_safe script. Instead, you should use the command-line option or the [Mysqld_safe] section of the MY.CNF options file

Option to configure the Mysqld_safe. You generally do not need to edit mysqld_safe to properly start the server. But, if you edit,

Upgrading MySQL will overwrite your modified version of Mysqld_safe in the future, so you should back up your modified version for future reloading.

--mysql.server

Server startup scripts. The script is used to use a system that contains a running directory of scripts that run startup services for a specific level. It calls Mysqld_safe to start the MySQL server.

Mysql.server is located in the Support-files directory in the MySQL source tree MySQL installation directory.

If you use the Linux server RPM package (mysql-server-version.rpm), the Mysql.server script will be installed in the/ETC/INIT.D directory, named Mysqld.

Mysql.server read options from [mysqld] section of [Mysql.server] and options files.

--mysql_install_db

The script creates a MySQL authorization table with default permissions. This is usually done only once when MySQL is first installed on the system.

• Option Modification Reference

 -- Online Temporary modification

+------------------+-------+| variable_name | Value |+------------------+-------+| Sort_buffer_size | 65536 |+------------------+-------+1 row in Set (0.00 sec) mysql> Set GLOBAL sort_buffer_size = ten * 1024x768 * 1024;MYSQL&G T \qbye...mysql> Show variables like ' sort_% '; +------------------+----------+| variable_name | Value |+------------------+----------+| Sort_buffer_size | 10485760 |+------------------+----------+1 row in Set (0.00 sec)

-- can be written in my.conf

[mysqld]sort_buffer_size = 10 * 1024 * 1024

• Mysql> show VARIABLES; View system variables and their values

mysql> show variables like  ' binlog_% '; +-----------------------------------------+------ -----+| variable_name                            | Value      |+-----------------------------------------+-----------+| binlog_cache_size                         | 32768     | |  binlog_direct_non_transactional_updates | off       | |  binlog_format                            | statement | |  binlog_stmt_cache_size                  | 32768     |+------------------ -----------------------+-----------+4 rows in set  (0.00 sec)

Add: Binlog_cache_size

The cache size that accommodates binary log SQL statements during a transaction. The binary log cache is the memory allocated to each client by the server that supports the transactional storage engine and the server has binary logging enabled (--log-bin option). If you frequently use large, multi-statement transactions, you can increase this value to get more performance. The Binlog_cache_use and Binlog_cache_disk_use state variables can be used to adjust the size of the variable.

This article is from the "by The Knife" blog, please be sure to keep this source http://chboy.blog.51cto.com/9959876/1707410

MySQL must know the common sense