Installation of MySQL under Linux

A. Download

http://dev.mysql.com/downloads/mysql/

Select the corresponding version, select "Linux-generic" here

Take the 64-bit system as an example, here are two files to download:

mysql-client-5.6.10-1.linux_glibc2.5.x86_64.rpm

mysql-server-5.6.10-1.linux_glibc2.5.x86_64.rpm

Two. Installation

Root User Installation

Execute the command under the download path:

Rpm-qa | grep mysql: Query If the system has MySQL package installed

If the installation first uninstall, because the system comes with the basic is the old version.

RPM-E mysql-libs-5.1.66-2.el6_3.x86_64--nodeps: Uninstall MySQL Package

Install the service first

RPM-IVH mysql-server-5.6.10-1.linux_glibc2.5.x86_64.rpm

RPM-IVH--replacepkgs--replacefiles mysql-server-5.6.10-1.linux_glibc2.5.x86_64.rpm

--replacepkgs: If it is already installed, you can use this option to override the installation

--replacefiles: If you encounter file conflicts, use this command to resolve

Then install the client

RPM-IVH mysql-client-5.6.10-1.linux_glibc2.5.x86_64.rpm

Three. Configure the boot file

Cp/usr/share/mysql/my-default.cnf/etc/my.cnf

Vi/etc/my.cnf

Add the following information under [MYSQLD]:

port= 3306 #端口

Default-character-set=utf8 #字符编码 5.5 or later to be configured as Character-set-server=utf8

wait_timeout=288000 # Link time-out, default is 8 hours, units in seconds

Four. Start and close MySQL

/etc/init.d/mysql Start/stop

Or

Service MySQL Start/stop/restart

MySQL installation is completed by default will be launched with the system, you can use the following command to view the running level of MySQL:

Chkconfig--list MySQL

Modify the run level for MySQL to start at 35:

Chkconfig--level MySQL on

Five. Log in to MySQL

The first login uses root's default password, and the default root password is saved in the Home/.mysql_secret of the current root user after installation is complete.

Mysql-u root-p

Login successful must first change the password, as follows:

Mysql>set PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' newpwd ');

Six. Open access rights

After logging in, execute the following command, open root Telnet permission, need to open 3306 port in iptables

Mysql>grant all privileges on * * to [e-mail protected] '% ' identified by ' newpwd ';

Mysql>grant all privileges on * * to [e-mail protected] ' localhost ' identified by ' newpwd ';

Mysql>flush privileges;

Seven. Create a new database

Mysql>create database newdbtest;

Mysql>use newdbtest; (Open library)

Eight. Create a new user and authorize

Mysql> Grant all privileges the newdbtest.* to [email protected] '% ' identified by ' 123456 ';

Nine. Remove root telnet permission

Mysql>revoke all privileges on * * FROM [email protected] '% ' identified by ' newpwd ';

10. Backup and Recovery

Backup Newdbtest:mysqldump-p--opt newdbtest > Back_newdbtest

Enter password:root Password

Recovery Newdbtest:mysql-u root-p Newdbtest < back_newdbtest

Enter password:root Password

11. de-Capitalization distinction

Linux after the installation of MySQL is the default: distinguish the case of the table name, do not distinguish between the case of the column name;

After login with root account, add Lower_case_table_names=1 after [mysqld] in/etc/my.cnf, restart MySQL service, it has been set successfully: The case of table name is not distinguished;
Lower_case_table_names parameter Details:
Lower_case_table_names=0
Where 0: Case sensitive, 1: Case insensitive

MySQL under Linux database name, table name, column name, alias casing rules are like this:
1, database name and table name are strictly case-sensitive;
2, the table alias is strictly case-sensitive;
3, the column name and the alias of the column in all cases are ignored case;
4, variable names are also strictly case-sensitive;
MySQL is case insensitive under Windows.

12. Turn on slow query and Bin-log log

After [mysqld] in/etc/my.cnf, add the following:

Long_query_time=2 #慢查询记录超过的时间, it's only 2 seconds.

Slow-query-log=1 #开启慢查询日志

Log_queries_not_using_indexes=1 #记录下没有使用索引的查询语句

Log_bin=mysql-bin #开启bin-log Log

Note: After the 5.6 version is slightly different from the previous version configuration parameters, you can use the following command to view a list of supported parameters:

Mysqld--verbose--help

You can use the Mysqldumpslow command to view slow query log information:

Mysqldumpslow-s c-t Host-slow.log

Mysqldumpslow-s r-t Host-slow.log

The above command shows the 20 most visited SQL statements and 20 SQL that returns the maximum number of recordsets.

Mysqldumpslow-t 10-s t-g "left join" Host-slow.log This is the SQL statement that returns the first 10 lines containing the links in the previous period.

13. Accidentally locked

For some reason, MySQL stops unexpectedly, such as killing the MYSLQ process directly, which can cause MySQL to be locked out:

[[Email protected] ~]# service MySQL status

MySQL isn't running, but lock file (/var/lock/subsys/mysql) exists[failed]

The workaround is to delete the/var/lock/subsys/mysql file.

Common MySQL Commands:

1. Login

Mysql-u Username-p password-H server IP address database: Log in and open the specified database, but the disadvantage is that the password should be entered in plaintext.

2. Post-Login commands

mysql> status; View database Status

Mysql> select version (); View the MySQL version number

Mysql> select Current_date (); View MySQL's current date

Mysql> Select Version (), current_date (); Also view MySQL's version number and current date

mysql> show databases; Displays the currently existing database

mysql> use MySQL Select database (using and QUIT commands do not need semicolons to end)

Mysql> Select Database (); Display the currently selected database

Mysql> Show tables; Displays the tables that exist in the current database

Mysql> select * from DB; Display the contents of the table (db)

Mysql> Describe[desc] mytable; Show the structure of a table

or Show columns from table name;

Mysql> select User,host,password from Mysql.user; Querying user Information

Mysql> source Mysql.sql; Execute SQL Script

Installation of MySQL under Linux