MySQL database server Setup and basic management

1. Install MySQL Database

Download from official website RPM Package:http://dev.mysql.com/downloads/mysql/

Select bundles for the current system bundle Collection Package

[Email protected] Desktop ]# tar-vxf mysql-5.6.15-1.el6.x86_64.rpm-bundle.tar-c/opt/mysql/

MYSQL-SHARED-5.6.15-1.EL6.X86_64.RPM// Shared library

MYSQL-DEVEL-5.6.15-1.EL6.X86_64.RPM// libraries and header files

MYSQL-EMBEDDED-5.6.15-1.EL6.X86_64.RPM// embedded version

MYSQL-TEST-5.6.15-1.EL6.X86_64.RPM// Test Kit

MYSQL-SERVER-5.6.15-1.EL6.X86_64.RPM// service-side program

MYSQL-SHARED-COMPAT-5.6.15-1.EL6.X86_64.RPM// Compatibility Pack

MYSQL-CLIENT-5.6.15-1.EL6.X86_64.RPM// client program

2. RPM Way to install MySQL Database

Preparatory work:

-Uninstall the Mysql-server,mysql package from the system

- transfer /etc/my.cnf file

Rpm-q mysql-server MySQL

Rpm-e--nodeps mysql-server MySQL

TAR-XVF Mysql-5.6.15-1.el6.x86_64.rpm-bundle.tar

Installation: [Email protected]]# RPM-UVH mysql-*.rpm

[Email protected] mysql]# Rpm-qa | Grep-i MySQL

3. start the MySQL service program

Service script for /etc/init.d/mysql Note: not mysqld

Start Database services:service MySQL Status|start|stop|restart

[[email protected] mysql]# chkconfig--list MySQL

[[Email protected] mysql]# service MySQL start

[[email protected] mysql]# netstat-anptu |gerp MySQL

Mysql Default Listener TCP 3306 Port

4. Modify root Password

* Default Database Admin account:root, allow access from localhost, login password is randomly generated during installation and saved in /root/.mysql_secret file.

The *mysql database only allows root login when no authorization is granted .

* change root password:mysql> Set password [email protected] "localhost" =password ("123");

5. Establish my.cnf configuration file

- configuration file:/etc/my.cnf

- Database directory:/var/lib/mysql

[Email protected] mysql]# CP/USR/SHARE/MYSQL-DEFAULT.CNF/ETC/MY.CNF

[Email protected] mysql]# VIM/ETC/MY.CNF

[Mysqld]

.........

[.....]

6. mysql Server elements:

process name mysqld PS aux | grep mysqld

Port 3306 Netstat-untlap | grep:3306

Process owner / group mysql grep mysql/etc/passwd

Data Transfer Protocol Tcp

configuration file /etc/my.cnf

Database Directory /var/lib/mysql/

7. Connect to MySQL Server

* By default, only database administrator root is allowed to log on to the database server natively

* use mysql command

format: mysql -H ip address / hostname    -u user name    -P password    [ database name

quit mysql database:quit or exit

8. using mysql> to manage the environment

Action instruction Type

-MySQL Command: Environment switch, watch State, exit and other control

-SQL directives: Database definition / query / manipulation / Authorization Statements

Basic precautions

- operation directives are case-insensitive (except for passwords, variable values)

- each SQL command to ; End or separate

-Tab Auto- completion is not supported

-\c can discard the currently written error operation instructions

-\h View Help information

9. View the database structure

* list which libraries are currently on the MySQL server

- show databases;

* Default Library:

-Information_schema: Virtual Library, non-placeholder physical storage space, data stored in memory, storage of information on database servers already available in libraries and tables

-MySQL: Authorization library, storing authorization information *

-Performance_schema: Running information and running parameter information of database server during Operation

-Test: Public Library, default no table, any user who can log on to the database server has full permissions to this library

* use / switch to the specified library

- use database name;

* View the current library

- Select Database ();

* list which tables are in the current library

- Show Tables;

* View the field structure of the specified table

- desc/describe table name (database name . table name);

10. Create / Delete library

* database / table naming rules:

- uniqueness, cannot duplicate name

-can use numbers, letters, underscores, you can start with a number or letter, not a pure number, you cannot use the command keyword

- try not to use special characters

- Case Sensitive

* Create a new library with the specified name

- Create database name;

* Delete the specified library

- Drop database name;

* Delete table with specified name

- Drop table name;

* Delete all records of the table

- Delete from database name . table name;

* Delete a single record of the table:

- Delete from table name where field name = value;

11 . Create a new table with the specified name

- Create table table name (

field name 1 field type (width) constraint, /c10>

field name 2 field type (width) constraint,

..........

field name N field type (width) constraints,

PRIMARY Key ( primary Key name )

) ; // width and constraints can be unspecified, which is the default value.

12, in use text as table name and field name Settings

1) Adjust the character set settings:[[email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]

.. ..

Character_set_server=utf8

2) Restart the service program: [[email protected] ~]# service MySQL restart

when creating a library or table, manually add " default Charset=utf8"

* Example

13. inserting records into the table

- insert into database name . table name (field list)values ( List of field values ) ;

This article is from the Linux learning blog, so be sure to keep this source http://lovelinux1314.blog.51cto.com/9666723/1787023

MySQL database server Setup and basic management