MySQL performance tuning and architecture design Note: A MySQL architecture consists of

2.1mysql Physical File composition

2.1.1 Log files:

1. View MySQL config file: mysql--verbose--help | Grep-a 1 ' Default options ';

1, error log:--log-error[=file_name] Specify error log location

2, binary log:

--log-bin[=file_name] If file_name is not specified, the default is in the data directory mysql-bin.****

--max_binlog_size: Sets the maximum storage limit for the Binlog, and when the log reaches that limit , MySQL will recreate a journal to begin recording. However, there are occasional binlog that exceed this setting, generally because a larger transaction is generated when the upper limit is reached, and MySQL does not record the same transaction separately into two binlog in order to ensure transaction security.

--binlog-do-db=db_name: The parameters explicitly tell MySQL that a (db_name) database needs to be recorded Binlog, if the "--binlog-do-db=db_name" parameter is explicitly specified, MySQL ignores the other database executes a query, and simply records the query executed against the specified database

--binlog-ignore-db=db_name: In contrast to "--binlog-do-db=db_name", it explicitly refers to Binlog Record of a (db_name) database, when specified, MySQL records the specified Binlog of all databases outside of the database.

Note: The above two parameters are only user dbname, not SQL statements at query time

3, Inquiry log: Query logs--log[=file_name] to open the feature profile:

Will record all query, so the volume is very large, special request time to open

4, slow query log:--log-slow-queries[=file_name]

5, Slow query log analysis tool: Mysqlslowdump

-s t	sort	-s at	Sort by average query time
-s l	Sort by total locktime	-s al	Sort by average lock time
-s s	sort	-s as	Sort by average row send
-s c	Sort by count	--	--

2.1.2 Data Files

1,.frm file: The metadata information associated with the table is stored in this file, including the definition information for the table structure. This file is available for each of the storage engines

2,. MYD: File is a MyISAM storage engine dedicated to storing data for MyISAM tables. Each MyISAM table will have a ". MYD "file corresponds to, also stored in the folder of the owning database, and". frm "File together

3,. MYI: Files are also proprietary to the MyISAM storage engine, which mainly holds information about the indexes of MyISAM tables. For MyISAM Storage, the content of the cache can be primarily ". MYI "file. Each of the MyISAM tables corresponds to one ". MYI "file, stored in location and". frm "and". MYD "the same.

2.1.3Replication related files:

1,master.info file

2,relay log and relay log index

3,relay-log.info file

2.1.4 Other Documents:

1,system Config file/etc/my.cnf

2,pid file 3,socket File

2.2Mysql Server System Architecture:

2.2.1 Logic Module Composition

 

2.3 MySQL comes with tool use

1,mysql

1,mysql--help

It would be nice to recover with this statement.

2, mysql-uroot-p watch07 < d:/mysql.bak.20140724--default-character-set=utf8

2,mysqladmin

1,mysqladmin-u sky-ppwd-h localhost Ping

2,mysqladmin-u sky-ppwd-h localhost status

3. View server Current link thread information:

3,mysqldump:

mysqldump [OPTIONS] database [tables]

mysqldump [Options]--databases [options] DB1 [DB2 DB3] ...

mysqldump [Options]--all-databases [options]

1 "MyISAM engine, can directly copy data sheet.

2 "mysqldump: Data export to SQL statement

3 "SELECT * FROM table1 to outfile '/tmp/table1.txt ';

4 "Recovery: Load Data infile '/tmp/table1.txt ' into table table1;

4,mysqlimport

Mysqlimport [OPTIONS] Database textfile ...

The Mysqlimport program is a tool program that will store text data in a specific format (such as data files generated by "SELECT * into OUTFILE ...") into a specified MySQL Server, such as a A standard CSV file into the specified table for a specified database. The Mysqlimport tool is actually just a wrapper implementation of the "Load Data infile" command.

5,mysqlbinlog

Mysqlbinlog [OPTIONS] Log-files

6,mysqlcheck:

Mysqlcheck [OPTIONS] database [tables]

Mysqlcheck [OPTIONS]--databases DB1 [DB2 DB3 ...]

Mysqlcheck [OPTIONS]--all-databases

Mysqlcheck Tool program can check (check), repair (repair), analyze (analyze) and optimize (optimize) tables in MySQL Server, but not all of the storage engines support all four of these features, repair functionality is not supported like Innodb. In fact, all of the four functions of the Mysqlcheck program can be executed by MySQL connection after logging into MySQL Server to perform the exact same task.

7,myisamchk:myisamchk [OPTIONS] tables[. MYI]

8,myisampack

9,mysqlhotcopy

MySQL performance tuning and architecture design Note: A MySQL architecture consists of