MySQL installation and basic configuration _ MySQL

I. INTRODUCTION to SQL language DDL: tables, views, indexes, trigger operations, etc. CREATEALTERDROP statement DML: data operation. SELECTINSERTUPDATEDELETEDCL: permission settings. GRANTREVOKE database access different languages use different database access technologies C # Usage 1. Introduction

SQL language

DDL: tables, views, indexes, and trigger operations. CREATE/ALTER/DROP statement

DML: data operations. SELECT/INSERT/UPDATE/DELETE

DCL: permission settings. GRANT/REVOKE

Database access

Use different database access technologies in different languages

C # use ADO. NET, JAVA use JDBC, etc.

Version

Community Edition: free and free to download without technical support

Enterprise: charged, not downloadable, with technical support

Alpha: development stage

Beta: Development completed, not tested

Gamma: a beta version has been released for a period of time.

Generally Available (GA): stable version

Tools

MySQL Community Server: core package integrated between the client and Server

MySQL Cluster: a package that provides Mysql Cluster functions

MySQL Fabric: provides a framework for high availability and Shard management.

MySQL Utilities: a utility for maintenance and management

MySQL Workbench: visual editing tool

MySQL Proxy: MySQL middleware. the Proxy receives requests sent to the MySQL database and routes the requests to different backend hosts.

MySQL Connectors: the connector of MySQL. The program connects to the MySQL driver.

MySQL Yum Repository: Download MySQL YUM source

MySQL APT Repository: APT source

RPM Package

MySQL-client: client connection tool. GUI tools include navicat and phpmyadmin.

MySQL-server: server package

MySQL-devel: Library and inclusion files

MySQL-shared: shared libraries that need to be dynamically loaded by certain languages and applications

MySQL-test: test component

MySQL-embedded: embedded

MySQL-bundle: integration package

Related Files

/Etc/my. cnf: configuration file

/Usr/share/doc/MySQL-server-5.6.26/my-default.cnf: reference configuration file

/Usr/share/mysql/my-default.cnf: Same as above

/Usr/bin: client programs and scripts

/Usr/sbin mysqld: Server

/Var/lib/mysql: log files, databases

/Usr/lib/mysql: Database

/Usr/share/doc/packages: document

/Usr/include/mysql: contains (header) files

/Usr/share/mysql: Error message and character set File

/Usr/share/SQL-benchmark: baseline program

II. Installation

Environment: CentOS 6.7x86, MySQL 5.6

An error is returned. delete the mysql-libs package.

[Root @ CentOS MySQL] # rpm-qa | grep-I mysql

Mysql-libs-5.1.73-5.el6_6.i686

[Root @ CentOS MySQL] # yum remove mysql-libs-5.1.73-5.el6_6.i686

RPM

[Root @ CentOS MySQL] # rpm-ivh MySQL-server-5.6.26-1.el6.i686.rpm

[Root @ CentOS MySQL] # rpm-ivh MySQL-client-5.6.26-1.el6.i686.rpm

YUM

[Root @ CentOS ~] # Yum install mysql-server mysql-client

Firewall

[Root @ CentOS ~] # Iptables-a input-p tcp-m state -- state NEW-m tcp -- dport 3306-j ACCEPT

[Root @ CentOS ~] # Service iptables save

[Root @ CentOS ~] # Service iptables restart

Configuration

In version 5.1, the root account has no password. after installation in version 5.6, the root account generates a random password at/root/. mysql_secret.

5.1 configuration file; 5.6 no configuration file, need to copy from/usr/share/mysql/my-default.cnf to/etc/my. cnf

5.1 Service name mysqld, 5.6 is mysql

Enable service

[Root @ CentOS ~] # Service mysql start

View the root password of the logged-on database account

[Root @ CentOS ~] # Cat. mysql_secret

# The random password set for the root user at Tue Sep 8 11:26:39 2015 (local time): 4VZTzey0LML2N7e1

Initialization settings

[Root @ CentOS ~] #/Usr/bin/mysql_secure_installation -- user = mysql

# Enter the root password

Enter current password for root (enter for none ):

# Whether to change the root password

Change the root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables ..

... Success!

# Deleting anonymous users

Remove anonymous users? [Y/n] y

... Success!

# Prohibit remote root login

Disallow root login remotely? [Y/n] y

... Success!

# Deleting a test database

Remove test database and access to it? [Y/n] y

-Dropping test database...

... Success!

-Removing privileges on test database...

... Success!

# Reload the permission table

Reload privilege tables now? [Y/n] y

... Success!

3. connect to the database

Parameters:

-H: host name or IP address

-P: port number. the default value is 3306.

-U: User name

-P: password

-E: specifies the SQL statement

[Root @ CentOS ~] # Mysql-u root-p

Enter password:

[Root @ CentOS ~] # Mysql-u root-p db01 # connect to the db01 database

[Root @ CentOS ~] # Mysql-h 192.168.41.135-u root-p # remote connection

4. create a remote user

User1 users have all permissions. % indicates that any host can be logged on.

Mysql> create user 'user1' @ '%' identified by '20140901 ';

Mysql> grant all privileges on *. * to 'user1' @ '% ';

V. data Migration Directory

Data Directory should be partitioned independently

Close service

[Root @ CentOS ~] # Service mysql stop

Move Directory

[Root @ CentOS ~] # Mv/var/lib/mysql // data/

Configuration file

[Root @ CentOS ~] # Cp-a/usr/share/mysql/my-default.cnf/etc/my. cnf

[Root @ CentOS ~] # Vim/etc/my. cnf

Datadir =/data/mysql

Socket =/data/mysql. sock

[Mysql]

Socket =/data/mysql. sock

# SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

Start the service

[Root @ CentOS ~] # Service mysql start

SELinux error solution (mv command retains permissions, so no error is reported)

[Root @ CentOS ~] # Chcon-R-t mysqld_db_t/data // mysql