Mysql Getting Started complete guide and basic installation tutorials under Linux system _mysql

Let's take a look at the basic features of MySQL:

1. Internal Components and portability

• Written using C and C + +
• Tested with a number of different compilers
• Able to 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.
• Full multithreading with core threads if you have more than one CPU, it can easily use these CPUs.
• A transactional and non-transactional storage engine is provided.
• Uses an extremely fast "B-tree" Disk table (MyISAM) and index compression.
• Adding another storage engine is relatively straightforward. This feature is useful if you intend to add an SQL interface to the internal database.
• An extremely fast thread-based memory allocation system.
• An extremely fast connection can be achieved by using the optimized "single scan multiple connections".
• A hash table in a memory is used as a temporary form.
• SQL functions are implemented with a highly optimized class library and run quickly. Typically, there is no storage allocation after completion of query initialization.
• The MySQL code was tested with purify (commercial Memory Overflow checker) and GPL tool Valgrind (http://developer.kde.org/~sewardj/).
• The server can be run as a separate program in a client/server networking environment. It can also be provided as a library and can be embedded (linked) into a stand-alone application. This type of application can be used alone or in a networked environment.

2. Column type

Many column types: signed/unsigned integers, 1, 2, 3, 4, 8 bytes long, Float,double,char,varchar,text,blob,date,time,datetime,timestamp,year,set,enum, And the OpenGIS space type. See Chapter 11th: Column types.

Fixed length and variable length records.

3. Statements and Functions

In the WHERE clause of the Select and query, provide complete operator and function support. For example:

Mysql> SELECT CONCAT (first_name, ', last_name)
  -> from Citizen->
  WHERE income/dependents > 10000 D Age > 30;

Full support for SQL GROUP by and ORDER BY clauses. Supports aggregate functions (count (), COUNT (DISTINCT ...), AVG (), STD (), SUM (), MAX (), MIN () and Group_concat ()).

Supports the left OUTER join and right OUTER join, with standard SQL and ODBC syntax.

Supports table aliases and column aliases as required by standard SQL.

DELETE, INSERT, replace, and update return the number of rows that changed (affected). When you connect to the server, you can return a matching number of rows by setting the flag.

The show command for 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 handles queries.

The function name does not conflict with the table name or column name. For example, ABS is a valid column name. The only limitation is that there must be no spaces between the function name and the subsequent symbol "(") when calling the functions. See 9.6, "Processing of reserved words in MySQL."

You can mix tables from different databases in the same query (as in MySQL 3.22).

4. Security

A very flexible and secure permissions and cryptography system that allows host-based authentication. When connected to the server, all password transmissions are encrypted in such a way that the password is secure.

5. Scalability and Limitations

Working with large databases: We use a MySQL server and a database containing 50 million records. We've also heard that some users use MySQL for databases that contain 60,000 tables and 5 billion rows.

Each table can support up to 64 indexes (32 before MySQL 4.1.2). Each index can consist of 1~16 columns or column elements. The maximum index width is 1000 bytes (500 before the MySQL 4.1.2). Indexes can use column prefixes that have a char, VARCHAR, blob, or text column type.

6. Connectivity

On any platform, the client can connect to the MySQL server using the TCP/IP protocol. In the NT series of Windows systems (NT, 2000, XP, or 2003), clients can connect using named pipes. On UNIX systems, clients can use UNIX domain socket files to establish connections.

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

The CONNECTOR/ODBC (MYODBC) interface provides MySQL support for client programs that use ODBC (Open Database Connectivity) connections. For example, you can use MS Access to connect to your MySQL server. The client can run on a Windows or UNIX platform. Provides a MYODBC source. Supports all ODBC 2.5 functions, as well as many other functions.

The connector/j interface provides MySQL support for Java client programs that use JDBC connections. The client can run on a Windows or UNIX platform. Provides the connector/j source code.

7. Localization

The server can provide error messages to clients in multiple languages. See section 5.10.2, "Setting the error message language."

Comprehensive support for the different character sets of the logarithm, including latin1 (cp1252), German, Big5, Ujis, etc. For example, the Scandinavian character ' Å ', ' ä ', and ' o ' are allowed in the table name and column name. Unicode support is available starting with MySQL 4.1.

All data is saved in the selected character set. Comparisons of normal string columns are case-insensitive.

The classification is based on the selected character set (by default, Swedish proofing). You can change this setting when you start the MySQL server. To see an example of an advanced taxonomy, see Czech classification code. The MySQL server supports many different character sets, which can be specified at compile time and at run time.

8. Clients and tools

The MySQL server provides internal support for SQL statements that can be used to check, refine, and repair tables. This type of statement can be used on the command line through the Mysqlcheck client. MySQL also includes Myisamchk, a quick command-line utility that you can use to perform such operations on the MyISAM table. See Chapter 5th: Database Management.

For all MySQL programs, you can pass the "-help" or "-?" Option called to get online help information.

MySQL installation on Linux:

1,rpm Package Form
(1) operating system publisher provides
(2) MySQL official (version update, fixed more common bugs) www.mysql.com/downloads
Introduction to the RPM package type in MySQL:
 MySQL-client         client Components
&NBSP
 MySQL-debuginfo      Debug MySQL component  
 
 MySQL-devel           would like to install PHP for MySQL compiler and so on MySQL-dependent component packages
 
 mysql-embedded        MySQL embedded version
 
 MySQL-server          shared libraries
&NBSP
 MySQL-shared         shared libraries
&NBSP
 MySQL-shared-dompat  for compatibility with older versions of shared libraries
&NBSP
 MySQL-test            MySQL test components (online processing function)
Installation method:
You can first download the corresponding version of the RPM package from the installation CD 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 

We can then install using the RPM command:

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 one point:
-H display installation progress with symbol #

-V reporting on each step of the operation

2, Universal binary Package
(1) New user to run the process in a safe manner:

# groupadd-r MySQL 
 
# useradd-g mysql-r-s/sbin/nologin-m-d/mydata/data mysql 
 
# chown-r Mysql:mysql/mydata /data 

(2) Installation and initialization of mysql-5.5.28
First download the corresponding MySQL version of the platform to the local, here is the 32-bit platform, therefore, selected for the mysql-5.5.28-linux2.6-i686.tar.gz
#

 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 main 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 your CPU number by 2, for example, use the following line:

Thread_concurrency = 2 

You also need to add the following line to specify where the MySQL data file will be stored:

DataDir =/mydata/data 

(5) Provide SYSV service script for MySQL:

# cd/usr/local/mysql 
 
# CP Support-files/mysql.server/etc/rc.d/init.d/mysqld 

(6) Add to the list of services:

# chkconfig--add mysqld 
 
# chkconfig mysqld on 

(7) Then you can start the service test use.

# service Mysqld Start

3, source code compilation
(Installation of a change in the way, the configuration process is not too much change, so the following details are not detailed)
To compile the source code on the 5.0 series Red Hat system MySQL must be built on a cross-platform compiler cmake
So:
(1) First install CMake
Install cmake need to use make

# tar XF cmake-2.8.8.tar.gz 
 
# CD cmake-2.8.8 
 

Use this script to detect the compilation environment

# make 
 
# make install 

(2) Compile and install mysql-5.5.28
Using CMake to compile mysql-5.5.28, the options are changed in a simple way ...
CMAKE specifies compilation options in a different way than make, which is implemented in the following ways:
CMake.

CMake.        -lh or Ccmake. Find related options that you can use
The options that are commonly used when specifying the installation path for installation files:
-dcmake_install_prefix=/usr/local/mysql Specify Installation path

-dmysql_datadir=/data/mysql Data Installation path

-DSYSCONFDIR=/ETC Configuration File Installation path
Because MySQL supports a large number of storage engines, the default compiled storage engine includes CSV, MyISAM, MYISAMMRG, and heap. To install additional storage engines, you can use a compilation option similar to the following:
-dwith_innobase_storage_engine=1 Install Innobase Storage Engine

-dwith_archive_storage_engine=1 Install ARCHIVE Storage Engine

-dwith_blackhole_storage_engine=1 Install blackhole Storage Engine

-dwith_federated_storage_engine=1 Install federated Storage Engine

To explicitly specify that a storage engine is not compiled, you can use an option similar to the following:
-dwithout_<engine>_storage_engine=1
Like what:
-dwithout_example_storage_engine=1 does not enable or compile the EXAMPLE storage engine

-dwithout_federated_storage_engine=1

-dwithout_partition_storage_engine=1
If you want to compile into other functions, such as SSL, you can use one of the following options to implement a library or not use a library when compiling:
-dwith_readline=1

-dwith_ssl=system represents the use of a self-contained 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 the location of sockets for MySQL interprocess communication

-denabled_local_infile=1 whether to start the local local_infile

What additional character sets are supported by-dextra_charsets=all

-ddefault_charset=utf8 Default Character Set

-ddefault_collation=utf8_general_ci default character Set sorting rules

-dwith_debug=0 whether to start the DEBUG feature

-denable_profiling=1 whether the profiling feature is enabled
If you want to clean up the files generated by the previous compilation, you need to use the following command:

Make Clean 
 
rm CMakeCache.txt 

Compiling the installation

# 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 the genus Group

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

Specify where the data is stored

Creating a configuration file

Edit configuration file

#vim/etc/my.cnf 

Add the following line to specify where the MySQL data file will be stored:

DataDir =/mydata/data 

Creating execution scripts and starting services

Copy Script

Execute permissions

Add to List of services

Start a service

# Bin/mysql  

Start MySQL