MySQL getting started full guide and basic installation tutorials in Linux _ MySQL

This article mainly introduces the complete guide for getting started with MySQL and the basic installation tutorials for Linux systems. It provides a detailed explanation of the basic features of MySQL, for more information about the basic features of MySQL, see:

1. internal components and portability

• Use C and C ++
• Tested with many different compilers
• It can work on many different platforms. See 2.1.1 "MySQL-supported operating systems ".
• Use GNU Automake, Autoconf, and Libtool for porting.
• Provides APIs for C, C ++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl.
• If multiple CPUs are available for full multithreading using the core thread, it can easily use these CPUs.
• Provides a transactional and non-transactional storage engine.
• Uses extremely fast "B-tree" disk tables (MyISAM) and index compression.
• Adding another storage engine is relatively simple. This feature is useful if you plan to add an SQL interface to an internal database.
• Extremely fast thread-based memory allocation system.
• By using the optimized "single scan multiple connections", you can achieve extremely fast connections.
• The hash table in the memory is used as a temporary table.
• SQL functions are implemented using highly optimized class libraries and run quickly. Generally, there is no memory allocation after the query initialization is completed.
• Use Purify (commercial memory overflow detector) and GPL tool Valgrind (http://developer.kde.org /~ Sewardj/) to test the MySQL code.
• The server can run as a separate program in the client/server networking environment. It can also be provided as a library and can be embedded (linked) into independent applications. Such applications can be used independently or in a network environment.

2. column type

Many column types: Signed/unsigned integer, 1, 2, 3, 4, 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM, and OpenGIS spatial type. See Chapter 1 column type.

Fixed length and variable length records.

3. statements and functions

The complete support for operators and functions is provided in the WHERE clause of SELECT and query. For example:

mysql> SELECT CONCAT(first_name, ' ', last_name)  -> FROM citizen  -> WHERE income/dependents > 10000 AND age > 30;

Fully supports SQL GROUP BY and ORDER BY clauses. Supports aggregate functions (COUNT (), COUNT (DISTINCT ...), AVG (), STD (), SUM (), MAX (), MIN () and GROUP_CONCAT ()).

Supports left outer join and right outer join, using standard SQL and ODBC syntax.

Table alias and column alias are supported according to standard SQL requirements.

DELETE, INSERT, REPLACE, and UPDATE return the number of changed rows. When connecting to the server, you can set a flag to return the number of matched rows.

The SHOW command of MySQL can be used to retrieve information about databases, database engines, tables, and indexes. The EXPLAIN command can be used to determine how the optimizer processes the query.

The function name does not conflict with the table name or column name. For example, ABS is a valid column name. The only restriction is that no space is allowed between the function name and the subsequent symbol. See Section 9.6 "processing reserved words in MySQL ".

Tables in different databases can be mixed into the same query (just like in MySQL 3.22 ).

4. security

The flexible and secure permission and password system allows host-based verification. When connecting to the server, all the password transmission is encrypted to ensure password security.

5. scalability and restrictions

Processing large databases: We use MySQL servers and databases containing 10 million records. We also heard that some users use MySQL for databases containing 60000 tables and approximately 5 billion rows.

Each table supports up to 64 Indexes (32 before MySQL 4.1.2 ). Each index can be 1 ~ Consists of 16 columns or column elements. The maximum index width is 1000 bytes (500 before MySQL 4.1.2 ). You can use a column prefix with CHAR, VARCHAR, BLOB, or TEXT columns for an index.

6. connectivity

On any platform, the client can use the TCP/IP protocol to connect to the MySQL server. In the Windows NT series (NT, 2000, XP, or 2003), the client can use a named pipe for connection. In Unix systems, a client can establish a connection using a Unix-domain socket file.

In MySQL 4.1 and later versions, if you start with the "-- shared-memory" option, Windows servers also support shared memory connections. The client can use the "-- protocol = memory" option to establish a connection through the shared memory.

The Connector/ODBC (MyODBC) interface provides MySQL support for client programs connected using ODBC (open database connectivity. For example, you can use MS Access to connect to your MySQL server. The client can run on Windows or Unix platforms. Provides the MyODBC source. All ODBC 2.5 functions and many other functions are supported.

The Connector/J interface provides MySQL support for Java client programs connected using JDBC. The client can run on Windows or Unix platforms. Source code of Connector/J is provided.

7. localization

The server can provide error messages to clients in multiple languages. For more information, see section 5.10.2 "set error message language ".

Provides comprehensive support for different character sets, including latin1 (cp1252), german, big5, and ujis. For example, the table name and column name can contain the Nordic characters 'hangzhou', 'ä', and 'ö '. Unicode support has been provided since MySQL 4.1.

All data is saved in the selected character set. The comparison of normal string columns is case insensitive.

The category is based on the selected character set (by default, the Swedish proofreader is used. You can change this setting when starting the MySQL server. For examples of advanced classification, see the Czech classification code. The MySQL server supports many different character sets that can be specified during compilation and runtime.

8. clients and tools

The MySQL server provides internal support for SQL statements and can be used to check, optimize, and repair tables. You can use this type of statements on the command line through the mysqlcheck client. MySQL also includes myisamchk, a fast command line utility that can be used to perform such operations on MyISAM tables. See Chapter 1 database management.

All MySQL programs can use "-help" or "-?" Option to obtain online help information.

Install MySQL on Linux:

1. rpm Package format
(1) provided by the operating system Publisher
(2) officially provided by MySQL (version updates fix more common bugs) www.mysql.com/downloads
Introduction to the rpm Package type in MySQL:
MySQL-client component

MySQL-debuginfo

MySQL-devel wants to compile and install MySQL-dependent components such as PHP for MySQL.

Embedded MySQL-embedded MySQL version

MySQL-server shared library

MySQL-shared library

MySQL-shared-dompat to be compatible with shared libraries of earlier versions

MySQL-test MySQL test component (online processing function)
Installation method:
First, you can download the rpm Package of the corresponding version from the installation disc or to the mysql website as follows:

MySQL-server-community-5.5.28-1.rhel5.i386.rpm  MySQL-client-community-5.5.28-1.rhel5.i386.rpm 

Then we can use the rpm command for installation:

rpm -ivh MySQL-server-community-5.5.28-1.rhel5.i386.rpm  rpm -ivh MySQL-client-community-5.5.28-1.rhel5.i386.rpm 

Add:
-H use the symbol # display the installation progress

-V: Report operations in each step

2. general binary package
(1) create a user to run the process securely:

# groupadd -r mysql  # useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql  # chown -R mysql:mysql /mydata/data 

(2) install and initialize the mysql-5.5.28
Bytes
#

 tar xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local  # cd /usr/local/  # ln -sv mysql-5.5.28-linux2.6-i686 mysql  # cd mysql  # chown -R mysql:mysql .  # scripts/mysql_install_db --user=mysql --datadir=/mydata/data  # chown -R root . 

(3) provide the primary configuration file for mysql:

# cd /usr/local/mysql  # cp support-files/my-large.cnf /etc/my.cnf 

(4) modify the configuration file:
Modify the value of thread_concurrency in this file to multiply the number of your CPUs by 2. for example, use the following line here:

thread_concurrency = 2 

You also need to add the following lines to specify the storage location of mysql data files:

datadir = /mydata/data 

(5) provide the sysv service script for mysql:

# cd /usr/local/mysql  # cp support-files/mysql.server /etc/rc.d/init.d/mysqld 

(6) add to service list:

# chkconfig --add mysqld  # chkconfig mysqld on 

(7) then you can start the service test.

# service mysqld start

3. source code compilation
(The installation and compilation methods have changed a bit, and the configuration process has not changed much. Therefore, the steps are not described in detail later)
To compile and install the source code on the 5.0 Red Hat system, MySQL must use a cross-platform compiler cmake.
Therefore:
(1) install cmake
Make is required for cmake installation.

# tar xf cmake-2.8.8.tar.gz  # cd cmake-2.8.8  # ./bootstrap 

Use this script to check the compiling environment

# make  # make install 

(2) compile and install mysql-5.5.28
Using cmake to compile mysql-5.5.28, the option method has changed a brief introduction...
Cmake specifies the compilation option in a different way than make. the implementation method is as follows:
Cmake.

Cmake.-LH or ccmake. find available options
This option is often used to specify the installation path of the installation file:
-DCMAKE_INSTALL_PREFIX =/usr/local/mysql specifies the installation path

-DMYSQL_DATADIR =/data/mysql data Installation Path

-DSYSCONFDIR =/etc configuration file installation path
Because MySQL supports many storage engines, the default compiled storage engines include csv, myisam, myisammrg, and heap. To install other storage engines, you can use the following compilation options:
-DWITH_INNOBASE_STORAGE_ENGINE = 1 install the INNOBASE storage engine

-DWITH_ARCHIVE_STORAGE_ENGINE = 1 install the ARCHIVE storage engine

-DWITH_BLACKHOLE_STORAGE_ENGINE = 1 install the BLACKHOLE storage engine

-DWITH_FEDERATED_STORAGE_ENGINE = 1 install the FEDERATED Storage Engine

To explicitly specify not to compile a storage engine, you can use the following options:
-DWITHOUT _ _ STORAGE_ENGINE = 1
For example:
-DWITHOUT_EXAMPLE_STORAGE_ENGINE = 1: The EXAMPLE storage engine is not enabled or compiled.

-DWITHOUT_FEDERATED_STORAGE_ENGINE = 1

-DWITHOUT_PARTITION_STORAGE_ENGINE = 1
If you want to compile it into other functions, such as SSL, you can use the following options to compile a database or not use a database:
-DWITH_READLINE = 1

-DWITH_SSL = system: Use the built-in SSL library on the system.

-DWITH_ZLIB = system

-DWITH_LIBWRAP = 0
Other common options:
-DMYSQL_TCP_PORT = 3306 set the default port

-DMYSQL_UNIX_ADDR =/tmp/mysql. sock MySQL socket location for inter-process communication

-DENABLED_LOCAL_INFILE = 1 whether to start the local LOCAL_INFILE

-DEXTRA_CHARSETS = all: additional character sets supported

-DDEFAULT_CHARSET = utf8 default character set

-DDEFAULT_COLLATION = utf8_general_ci default character set sorting rule

-DWITH_DEBUG = 0: whether to enable the DEBUG function

-DENABLE_PROFILING = 1 whether to enable the performance analysis function
To clear the files generated by the previous compilation, run the following command:

make clean  rm CMakeCache.txt 

Compile and install

# tar xf mysql-5.5.28.tar.gz  # cd mysql-5.5.28  # groupadd -r mysql  # useradd -g -r mysql mysql  # mkdir -pv /data/mydata  # chown -R mysql:mysql /data/mydata  # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  # make  # make install # cd /usr/local/mysql 

# chown -R :mysql

Change Group

# scripts/mysql_install_db --user=mysql --datadir=/data/mydata/

Data storage location

# cp support-files/my-large.cnf /etc/my.cnf 

Create a configuration file

Edit configuration file

#vim /etc/my.cnf 

Add the following lines to specify the storage location of mysql data files:

datadir = /mydata/data 

Create execution scripts and start services

# cp support-files/mysql.server /etc/rc.d/init.d/mysqld 

Copy script

# chmod +x /etc/rc.d/init.d/mysqld 

Execution permission

# chkconfig -add mysql 

Add to service list

# service mysqld start 

Start the service

# bin/mysql  

Start mysql