Analysis of common MySQL database management _ MySQL

Analysis of common MySQL database management I haven't sorted out things for a long time, and I feel more and more lazy. I just recently had a MYSQL project (an ORACLE application was migrated to MYSQL) and I have never touched on MYSQL before, however, we also know that MYSQL databases are widely used in various personal and business systems, and various technologies are relatively mature. Summarize the learning process.

?

??? We have already briefly introduced some basic MYSQL database operations. This chapter details the common management of MYSQL databases for MYSQL database administrators.

??? MYSQL has many management tools. I have downloaded one from the Internet. Mysql-gui-tools-noinstall-5.0-r12-win32, is a non-installed management software, contains four basic tools:

??? ? MySQLAdministrator
??? ? MySQLMigrationTool
??? ? MySQLQueryBrowser
??? ? MySQLSystemTrayMonitor

??? The specific use of these tools is very simple and the operations are flexible. here we will not detail the functions of these tools. if you are interested, you can download them by yourself, you will be familiar with it several times.

??? 1. MYSQL command line management tool

??? (1) first introduce several MYSQL command line tools:

??? Mysqld_safe, mysql. server, and mysqld_multi are server startup scripts.

??? Note: If you do not use the server startup script in Windows, you can use the WINDOWS command
???
??? Start the MYSQL service:
??? Net start mysql

??? Stop MYSQL service:
??? Net stop mysql
???
??? Or:

C:/Program Files/MySQL Server 5.0/bin> mysqladmin-uroot-p shutdown
Enter password :******

??? Note: mysqld is a MySQL server.

The following table lists several MYSQL servers supported by IPVS:

? After binary installation in WINDOWS, the mysqld-nt Server is used by default in WINDOWS services.

??? The usage of this command will be explained in detail on the MYSQL server.

??? Mysql_install_db initializes the data directory and initial database.

??? Mysql is a command line client program used to execute SQL statements in interactive or batch processing mode,

??? We have already used this command in the example above, so we will not talk about it here. Here is an example:

C: /Program Files/MySQL Server 5.0/bin> mysql-u root-p-e "SELECT * FROM ???????????????? ??????????? TEST limit 5; select * from test where id = 1000 "mytest
Enter password :******
+ ------ + --------------------- +
| ID ?? | MC ?? | DT ????????????????? | RQ ????????????????? |
+ ------ + --------------------- +
| 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|? 999 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|? 998 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|? 997 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|? 996 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+ ------ + --------------------- +
+ ------ + --------------------- +
| ID ?? | MC ?? | DT ????????????????? | RQ ????????????????? |
+ ------ + --------------------- +
| 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 1000 | test | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+ ------ + --------------------- +

??? Mysqladmin is a client program used for management.

??? We can use mysqladmin-help to obtain relevant information. mysqladmin can complete database creation and deletion, modify user passwords, check server status, refresh system variables, and many other management functions:

C:/Program Files/MySQL Server 5.0/bin> mysqladmin-uroot-p ping
Enter password :******
Mysqld is alive

C:/Program Files/MySQL Server 5.0/bin> mysqladmin-uroot-p status
Enter password :******
Upload Time: 17842? Threads: 2? Questions: 12? Slow queries: 0? Opens: 13? Flush tabl
Es: 1? Open tables: 1? Queries per second avg: 0.001

C:/Program Files/MySQL Server 5.0/bin> mysqladmin-uroot-p version
Enter password :******
Mysqladmin? Ver 8.41 Distrib 5.0.27, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with absolutely no warranty. This is free software,
And you are welcome to modify and redistribute it under the GPL license

Server version ????????? 5.0.27-community-nt
Protocol version ??????? 10
Connection ????????????? Localhost via TCP/IP
TCP port ??????????????? 3306
Uptime :???????????????? 4 hours 58 min 21 sec

Threads: 2? Questions: 13? Slow queries: 0? Opens: 13? Flush tables: 1? Open tab
Les: 1? Queries per second avg: 0.001

??? This command involves some of its basic functions in MYSQL database user management and backup recovery.

??? Mysqlcheck:

C:/Program Files/MySQL Server 5.0/bin> mysqlcheck-uroot-p mytest
Enter password :******
Mytest. aaa ???????????????????????????????????????? OK
Mytest. customer ??????????????????????????????????? OK
Mytest. mytable ???????????????????????????????????? OK
Mytest. sys_tests ?????????????????????????????????? OK
Mytest. test ??????????????????????????????????????? OK
Mytest. test1 ?????????????????????????????????????? OK
Mytest. test_isam ?????????????????????????????????? OK

??? Mysqldump database backup
??? Mysqlhotcopy database backup
??? Import data files using mysqlimport

??? These command tools will be described in detail in the subsequent backup recovery.

??? Mysqlshow displays information about databases and tables.

?

C:/Program Files/MySQL Server 5.0/bin> mysqlshow-uroot-p mytest
Enter password :******
Database: mytest
+ ----------- +
|? Tables ?? |
+ ----------- +
| Aa ??????? |
| Aaa ?????? |
| Customer? |
| Mytable ?? |
| Sys_tests |
| Test ????? |
| Test1 ???? |
| Test_isam |
+ ----------- +

??? The following are several operations that can be performed independently of the MYSQL server (which can be executed by the client.

??? Myisamchk performs table maintenance.
??? Myisampack generates compressed and read-only tables.
??? Mysqlbinlog is a utility used to process binary log files.

??? The binlog function in MySQL is similar to the archive log in Oracle. DBMS_LOGMNR is provided in Oracle to analyze the log files and solve the Redo SQL and Undo SQL statements, mySQL also provides a tool named mysqlbinlog to explain or retrieve the SQL statements stored in the binlog. Have you studied Undo SQL. the basic syntax is as follows:

Mysqlbinlog [options] log_file...

??? In the options, you can specify some filtering conditions to solve what you want to use. The options include:

-- Database = db_name,-d db_name
-- Offset = N,-o N
-- [Start | stop]-datetime = datetime
-- [Start | stop]-position = N

??? The solution is an SQL statement. if you execute these statements, the incremental recovery is achieved. it is estimated that the variable is not bound, maybe it is not very important to bind a variable to MySQL. However, it is important not to run multiple processes, because in this case, the order is not guaranteed. As follows:

$ Mysqlbinlog binlog.000001>? /Tmp/statements. SQL
$ Mysqlbinlog binlog.000002>/tmp/statements. SQL
$ Mysql-e "source/tmp/statements. SQL"

??? Oracle's LogMiner is not very good because it is not offline. it is much more convenient to make it into MySQL. you can imagine what the replication process in MySQL is like?

??? Mysqldumpslow

??? MySQL comes with the slow log analysis tool mysqldumpslow, but it is not described. This article analyzes the script and introduces its usage. Slow log is a file written by MySQL based on the execution time of SQL statements. it is used to analyze slow statements.

??? You only need to configure in the my. ini file:

Log-slow-queries = [slow_query_log_filename]

??? You can record the SQL statement that exceeds the default 10 s execution time.
??? To modify the default settings, you can add:

Long_query_time = 5

??? Set to 5S.

??? To record all SQL statements, you can write:

Log-long-format

# T = time, l = lock time, r = rows

??? # At, al, and ar are the corresponding average values.

??? Acceptable parameters of mysqldumpslow include: