MySQL Database system

1, the characteristics of MySQL:
1) multithreading, multi-user

2) based on C/S (client/server) architecture

3) Easy to use, fast query speed

4) Safe and reliable

2, MySQL compilation installation (* on behalf of the Keyboard tab key)

1) Ready to work: Uninstall MySQL installed using the RPM method

RPM-E MySQL--nodeps

Install CMake package;

Cd/media

Tar zxf cmake-*-c/usr/src

cd/usr/src/cmake-*

./configure && gmake && gmake Install

2) MySQL compile and install

Groupadd MySQL

Useradd-m-s/sbin/nologin mysql-g MySQL

Cd/media

Tar zxf mysql-*-c/usr/src

cd/usr/src/mysql-*

Cmake-dcmake_install_prefix=/usr/local/mysql-dsysconfdir=/etc && make && make INSTALL

3, after the installation of the adjustment

1) Add permissions to the MySQL directory

Chown-r Mysql:mysql/usr/local/mysql

2) generating the master configuration file

Cp support-files/my-medium.cnf/etc/my.cnf

3) Initializing the database

/usr/local/mysql/scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data /

4) Setting Environment variables

In-s/usr/local/mysql/bin/*/usr/local/bin/

or echo "path-$PATH:/usr/local/mysql/bin" >>/etc/profile

. /etc/profile (Effective immediately)

5) Add as System service

Cp Support-files/mysql.server/etc/rc.d/init.d/mysqld

Chmod +x/etc/rc.d/init.d/mysqld

Chkconfig--add mysqld

6) Start the MYSQLD service and view the running status

Service mysqld Restart

Service mysqld status or/ETC/INIT.D/MYSQLD status

7) MySQL server default port number is: 3306

4. mysql Management

1) Login mysql:mysql-u user name-p (with password Plus-p, no password when not added)

2) Change Password: two ways:

Method 1: Change the user password in the MySQL Library users table, use the flush command to refresh

Mode 2: Use at the command line: Mysqladmin-u user name-p password "New password"

3) Create the database: The name of the creation library; (Example: Create DB Benet;)

Using Database: Use library name;

View database: show databases;

4) Creating Table: Create table table name (field 1 type, field 2 type ...) ）;

Example: Create TABLE t387 (name char (+), password char (10));

View table: Show tables;

View table structure: describe table name;

5) Insert data: INSERT into table name values (' Value 1 ', ' Value 2 ' ...) ；

Example: INSERT into t387 values (' hanming ', ' 123456 ');

Note: Values should be in the same order as in the table specified earlier.

6) Query data: Select from table name; ( wildcard, representing all)

7) Update data: The Update table name set field = ' new value ' where condition;

Example: Update t387 set password= ' 654321 ' where name= ' hanming ';

8) Delete data: Delete form table name where condition;

9) Delete libraries and tables: Drop database/table library name/table name;

10) Authorization: Grant permission list on Library name. Table name to user @ source identified by ' password ';

11) Revoke permissions: Revoke permissions list on library name. Table name from user @ source;

5. Database Backup and Recovery

1) Backup of the database:

Method 1: You can back up the catalog directly/usr/local/mysql/data

Method 2: Use the Backup tool mysqldump

Backup operation: mysqldump-u root-p Backup options > backup file name. sql

Example: Mysqldump-u root-p benet t387 >/usr/local/mysql/hanming.sql

Backup options: library Name Table name back up the table in the library

     --databases  库名       备份数据库     --all-databases          备份所有数据库     --opt                   优化加快备份速度

Backup files can be opened with Vim to view content directly

2) Recovery:

Mysql-u ROOT-P Library Name < backup file name. sql

Example: Mysql-u root-p Benet </usr/local/mysql/hanming.sql

MySQL Database system