Shulou(Shulou.com)06/01 Report--

1. MYSQL server and main supporting tools

Tool name briefly introduces the sample mysqldMYSQL service program

Mysql safely launches the MYSQL server mysqld_safe-- defaults-file=/u01/3307/my.cnf 2 > & 1 > / dev/null & mysqladmin with mysql-uroot-prootmysqld_safe, a tool program that interactively executes sql commands, to complete various system maintenance and management tasks (view working status, set various permissions)

Mysqldumpmysql Database logical backup tool

Mysqlimport reads text file data into MYSQL datasheet

Mysqlshow is used to view database, table, and column information

Myisamchk checks and repairs MyISAM datasheet files

Myisampack compresses MyISAM datasheets and produces read-only datasheets

II. Introduction to the main options of mysqld

Directories and files basedir = path use a given directory as the root directory (installation directory) datadir = path given directory read database file pid-file filename specify a storage process ID file socket = filename specify a socket file memory management, optimization, query cache bulk_insert_buffer_siz = n the cache length allocated by the INSERT command that inserts more than one new record at a time, the default 8Mkey_buffer_size = n is used to store the RAM value of the index block Default 8Mjoin_buffer_size = n the length of the cache allocated for the join operation when the data column of the join operation has no index, default 128Kmax_heap_table_size = the maximum length of the nheap data table, default 16Mmax_connections = the most big data of the database connections processed by the nMYSQL server at the same time, and 100query_cache_limit = n the maximum length of query results allowed to be temporarily stored in the query cache area Default 1Mread_buffer_size = n length of cache reserved for read operations that sequentially read data from the data table The default 128Ksort_buffer = n is the cache length assigned for the recording operation, and the default 2Mtable_cache = n is the number of data tables opened at the same time. By default, the 64-day log log = file counts all connections and all SQL logs in the log (belongs to the general query log) log-slow-queryies = file will record the query commands that exceed the long_query_time variable value in the log long _ query_time = n slow query execution time limit The default setting is that 10slong_query_not_using_indexs logs all slow queries and query commands that do not use indexes during execution log-bin = filename all SQL commands that modify the data, and (insert, update and delete) log the maximum length of the binary file max_binlog_size = n binary log function of log-bin-index = filename binary log function in binary format, default 1G. Before the amount of information in a binary log file exceeds this maximum, the MYSQL server will automatically provide a new binary log file with binlog-do-db = dbname only to record the changes in the given database into the binary log file, while the changes in other databases do not record sync_binlog = n every time after N log writes, the log file is written to the hard disk, that is, the log information is synchronized. Safest log_error = file log file name mirror (master mirror server) servedr-id = n assign a unique ID number to the server binlog-do/ignore-db = dbname only record changes in a given database in a binary log file / do not record changes in a given database in the log file

Mirror (secondary mirror server) server-id = n assign a unique ID number to the server log-slave-updates to start the logging function on the secondary server Build a mirror link (A-> B-> C) master-host = the hostname or IP address of the hostname master server master- user = the user name used by the username slave server to connect to the master server master- password = password master server login password master-port=n port read-only=0/10, allowing the slave server to execute SQL commands independently 1. The slave server intelligently executes the SQL command from the master server replicate-do-table=dnmane.tablename only mirrors a given data table replicate-do-wild-table=dnmane.tablename as above, but the names of the database and database tables allow the wildcard'% 'replicate-do-db=dbname to mirror only this database. 2. Examples of common functions of mysqladmin

The example shows that [mysql@mysql ~] $mysqladmin-uroot-pmysql-I 2-c 5 status checks server status every two seconds Repeat a total of 5 times [mysql@mysql ~] $mysqladmin-uroot-pmysql password 'mysql123' modify root password [mysql@mysql ~] $mysqladmin-uroot-pmysql ping to see if mysqlserver can use [mysql@mysql ~] $mysqladmin-uroot-pmysql variables to see if the system variable value [mysql@mysql ~] $mysqladmin-uroot-pmysql processlist shows all processes running on the server [mysql@mysql ~] $mysqladmin-uroot-p reload overload permission information [mysql@mysql ~] $mysqladmin-uroot-p refresh refresh all table cache And close and open log [mysql@mysql ~] $mysqladmin-uroot-p shutdown using safe mode to shut down the database [mysql@mysql ~] $mysqladmin-uroot-p processlist [mysql@mysql ~] $mysqladmin-uroot-p kill idnummysqladmin execute the kill process [mysql@mysql ~] $mysqladmin-uroot-p stop-slave [mysql@mysql ~] $mysqladmin-uroot-p start-slave stop and start MySQL replication on a slave server

Remarks: continuous summary and update of follow-up tools

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.