Learn to use database MySQL

mysql| Data | database
[Summary]:mysql is a GPL-compliant open source software, under the Linux platform it is lamp (lamp in English is "light" meaning, but in the IT industry is certainly not that simple meaning.) This term is very popular in Germany. Represents the Apache Web server on the Linux platform; The MySQL database and the combination of Perl, Python, or PHP programming languages combine important components. It also provides a C API that can be combined with glade/gtk+ to replace the Windows platform's Vb+access/ms SQL Server portfolio. This article describes the simple usage of database MySQL and its various tools.

One, why use Mysql:mysql is a high-performance database, because of the following reasons. It can compete with any similar product in the market.

L speed. MySQL itself is not complicated. Http://www.mysql.com/information/crash-me.php can find information on competing data from MySQL and other database products

L Upgradeable. MySQL can handle an unlimited number of advocates and 500 million of records at the same time

L simplicity. The core of MySQL is a small, fast database for people who understand the work of the database, who can work efficiently and write advanced functions.

L Value: MySQL is free software that follows the GPL and you can download and use it.

L portability. Because MySQL is open source free software, can run on all platforms, if not run on your platform, you can modify, compile source code.

Second, MySQL missing what

MySQL is protected by permissions, not market-based. What MySQL can do and can't do is what we need to know before we use it.

Graphical interface: MySQL begins to provide an interface similar to MS SQL Server, which is still in beta

L transaction processing: MySQL does not support transactions, but in the release notes of May 9, 2000, MySQL claims that the version 3.23.15 the berkely DB database of "pilot support" sleepy software. The version 3.23.25 fully implements this functionality.

L stored procedures: MySQL does not have a stored procedure mechanism. The MySQL development community has a stored procedure mechanism in the To do list that will be included in version 4.

L triggers: Triggers are programs that typically exist in an embedded scripting language such as Zend or Python. There is a message that this trigger will be applied to version 4, using the Zend script engine.

L Referential integrity: future support

L Record level Lock: MySQL does not support record level locking. Will improve in the future, this is the power of open source code.

L subquery: MySQL does not support subqueries. But the C language interface provided by it can be easily remedied.

L View: MySQL does not support views. But it's in the MySQL development community's to do list.

It's very clear what MySQL means for the future of the Linux platform application.

Third, the MySQL database system installation

Here we only discuss the installation of MySQL database system under Linux system, which can be installed through the RPM package and the tar package, only the RPM mode installation binary is discussed here.

With the RPM package installed, you need the following three files:

1, mysql-3.23.25-1.i386.rpm This is a server section. It contains the actual database server that accepts the request and returns the result

2, mysql-client-3.23.25-1.i386.rpm This is the client part.

3, mysql-devel-3.23.25-1.i386.rpm. From this file, you need a C API file.

The installation process is as follows: first install the server section, then install the client section, the final need to install the C program development needs of the file.

1, install the server:

Type the following command: #rpm –IVH mysql-3.23.25-1.i386.rpm

2, install the client

Type the following command: #rpm –IVH mysql-client-3.23.25-1.i386.rpm

4, the installation of the C development document

Type the following command: #rpm –IVH mysql-devel-3.23.25-1.i386.rpm

5, uninstall MySQL: First need to shut down the server

#mysqladmin shutdown

#rpm –E–VV mysql-3.23.25-1

#rpm –E–VV mysql-client-3.23.25-1

6, Installation account management MySQL server: Installation from the RPM. The MySQL user account is automatically created. This is a user that is used to run the MySQL service.

Change mysqladmin Password: To enhance security, you need to change the password for the root user of MySQL, which can be done with the following two commands

%mysqladmin–u root–p password ' new-password '

Or

%mysqladmin–u root–h servername–p password ' new-password '

Four, start and stop the MySQL server daemon

Using the RPM installation, the MySQL server Dameon automatically starts and stops for you, redhat the system in/etc/init.d/mysqld MySQL server script.

If for some reason you need to start the MySQL Dameon, use the following command

%safe_mysqld–user=mysql &

To turn off the MySQL server Dameon, send the following command:

%mysqladmin shutdown

V. Tools and procedures applied to MySQL

In addition to the MySQL server (mysqld). The MySQL program group contains a large number of programs to manage databases and database servers. When MySQL is installed through RPM, these programs are installed. Isamchk and Myisamchk are tools for validating and rebuilding database tables and files. Mysqlaccess is used to authorize, change, and revoke user permission permissions. Mysqladmin is an advanced management tool, such as creating a database and shutting down a database. Mysqldump is used to pour out data and backup tables in case of emergency, it outputs data in SQL INSERT statements, uses methods that feed output files to the MySQL server, and the rows are loaded. Mysqlimport is a monolithic loading tool, while Mysqlshow displays information about database objects, such as the superscript.

1. Isamchk: View and repair of destroyed tables

If the database table is destroyed for some unknown reason, the tool can fix it. The data file that holds the basic data is called the indexed sequential access (index sequential access Method,isam) file.

MySQL has the MyISAM file format as the default option in versions 3.23 and later. You need to use the Myisamchk tool, not the isamchk. The ISAM table structure is replaced by the MYISAM structure. Future ISAM will not be supported. The following highlights the MyISAM file structure:

ISAM old file structure: Each table in the database is actually a combination of three files: *. ISD file contains data, *. The ISM file contains information about the data structure, such as keys and indexes, and the *.frm file contains the structure of the table.

View three files found in the/var/lib/mysql/test_db/directory

Tb1_books_1.frm 88bytes

Tb1_books_1.myi 1024 bytes

Tb1_books_1.myd 8604 bytes

*.frm is the same as the old file structure, *. myi files and *. The ISM file is consistent and *. myd files conform to the old *. ISD files.

2. Myisamchk

This is the updated version of the Isamchk MyISAM storage format, and its basic usage is as follows:

Myisamchk [OPTIONS] tables. Myi

Use the following command to get a list of all the options available in your system



3. Mysqlaccess This tool is most worth discussing is the-HOWTO command line, when you execute the command, you will get an example of how to use this tool:

%mysqlaccess–howto

The use of mysqlaccess is fairly straightforward:

mysqlaccess [host [user [db]]] OPTIONS ... If you do not provide an option, you must provide both user and DB. If no host is specified, the local server is used.

4. Mysqladmin

The general usage of this command is as follows:

mysqladmin [option] command, options are standard UNIX commands:-V For detailed instructions,-T=XX refers to the timeout time to connect to the server. Using commands

%mysqladmin-?| More can see all the lists. The command section is used for the condition of the rule. So you don't have to use the command in the list.


Command

Description

Create db_name

Create a database called Db_name

Drop db_name

Remove database name

Flush-logs

Clear all logs

Flush-privileges or Reload.

Overload authorization table, or command to perform the same operation

Ping

To see if Mysqld is started

Shutdown

Close Database

Status

To view the status of a server




5. mysqldump

This is a tool for backing up, porting, or overloading a database. The command usage has the following two kinds:

%mysqldump [OPTIONS] database [tables]

Or

%mysqldump [OPTIONS]. When using the second method, you must provide the-databases or-all-databases option

6. Mysqlimport

This is a massive input tool for MySQL, similar to the BCP tool for MS SQL Server. It is the command-line interface for the load data statement, as follows:

%mysqlimport [OPTIONS] db_name file_name

7. Mysqlshow

Mysqlshow gives information about a database, table, or column, which is the command line interface for show statements

Usage:

%mysqlshow [OPTIONS] [database_name [TABLE_NAME [column]]]

8. Myisampack

Myisampack can compress a MyISAM table into a relatively small space. He's kind of like gzip, except it runs in the table. It runs in. MyD file, and compress it into a very small size. After you run Myisampack, you can view the table, but you cannot update the table.

All rights reserved, if you want to reprint, please specify the source.