Install a tutorial on configuring Mysql in a Aliyun CentOS environment _mysql

1 General Error Yum installation method:

In the previous article described in the CentOS 6.5 system in the Yum way to quickly build the LNMP environment, then can also be in the CentOS 7 or CentOS 7.1 system in accordance with gourd painting ladle installation MySql5.6.24 it? The answer is in the negative.

[root@typecodes ~]# yum-y install MySQL mysql-server ..............
.... Omit part of the installation process ....
.....................。

installed:
 mariadb.x86_64 1:5.5.41-2.el7_0             mariadb-devel.x86_64 1:5.5.41-2.el7_0            

Dependency Installed:
 keyutils-libs-devel.x86_64 0:1.5.8-3.el7 krb5-devel.x86_64 0:1.12.2-14.el7  libcom_ err-devel.x86_64 0:1.42.9-7.el7 libselinux-devel.x86_64 0:2.2.2-6.el7 
 libsepol-devel.x86_64 0:2.1.9-3.el7   libverto-devel.x86_64 0:0.2.5-4.el7 openssl-devel.x86_64 1:1.0.1e-42.el7.4 pcre-devel.x86_64 0:8.32-14.el7   
 zlib-devel.x86_64 0:1.2.7-13.el7   

complete!

From the above installation results can be seen, did not install the MySQL database, but installed MARIADB data! Because in CentOS 7 and CentOS 7.1 systems, the default installed MySQL is its branch mariadb. Here quoted Baidu Encyclopedia about MARIADB Description:

MARIADB database management System is a branch of MySQL, mainly by the open source community in the maintenance, the use of GPL licensed license MARIADB is fully compatible with MySQL, including APIs and command line, so that it can easily become a replacement for MySQL.
Therefore, the following normal MySQL operations are not valid:

###### #无法把mysql服务加入系统启动
[root@typecodes ~]# chkconfig mysqld on
Error reading information on service Mysqld:no s Uch file or directory
###### #启动不了mysql
[root@typecodes ~]# service mysqld start
redirecting to/bin/ Systemctl start Mysqld.service Failed to issue method Call:unit Mysqld.service Failed to
load:no such file or direct Ory.
###### #没有安装mysql相关包
[root@typecodes ~]# rpm-qa |grep mysql
php-mysql-5.4.16-23.el7_0.3.x86_64
[ Root@typecodes ~]#

2 the correct installation method:

As we all know, the Linux system has its own repo is not automatically update the latest version of each software (basically is the later stable version), so can not install the advanced version of MySQL yum. So we need to first install the RPM package with the currently available MYSQL5 family of Community Edition resources.

###### #安装rpm包
[root@typecodes ~]# RPM-UVH http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

Retrieving http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Preparing       ... ################################# [100%]
updating/installing ...
 1:mysql-community-release-el7-5 ################################# [100%]

At this point, view the currently available MySQL installation resources:

[Root@typecodes ~]# yum repolist enabled | grep "Mysql.*-community.*"

mysql-connectors-community/x86_64 MySQL Connectors community
mysql-tools-community/x86_64  MySQL Tools Community
mysql56-community/x86_64   MySQL 5.6 Community Server    139

As you can see from the list above, mysql56-community/x86_64 and MySQL 5.6 Community Server can be used.

Therefore, we can install the MySQL5.6 version directly in the Yum way.

[Root@typecodes ~]# yum-y Install Mysql-community-server

Loaded Plugins:axelget, langpacks No metadata available for base no metadata available for Epel no metadata available fo                                  R Extras (Failed to load module Fastestmirror:no module named Fastestmirror) repomd.xml | 2.5 KB 00:00:00 Update mysql-connectors-community metadata successfully (Failed to load module Fastestmirror:no module Named Fastestmirror) repomd.xml |  2.5 KB 00:00:00 Update mysql-tools-community metadata successfully (Failed to load module Fastestmirror:no module named Fastestmirror) Repomd.xml | 2.5 KB 00:00:00 Update mysql56-community metadata successfully No metadata available for updates ity | 2.5 KB 00:00:00 mysql-tools-community | 2.5 KB 00:00:00 mysql56-community |              2.5 KB 00:00:00 (1/3): mysql-connectors-community/x86_64/primary_db          | 7.3 KB 00:00:00 (2/3): mysql56-community/x86_64/primary_db |  MB 00:00:01 mysql-tools-community/x86_64/p FAILED ==============================================] kB/s | MB KB 00:00:00 ETA http://repo.mysql.com/yum/mysql-tools-community/el/7/x86_64/repodata/ 3301D2EB425F300DEC5A46C4201D08F2CF9232A6-PRIMARY.SQLITE.BZ2: [Errno] Timeout on http://repo.mysql.com/yum/ MYSQL-TOOLS-COMMUNITY/EL/7/X86_64/REPODATA/3301D2EB425F300DEC5A46C4201D08F2CF9232A6-PRIMARY.SQLITE.BZ2: (28, ' Operation too slow.
Less than 1000 bytes/sec transferred "last 3 seconds") trying other mirror. (3/3): mysql-tools-community/x86_64/primary_db | MB 00:00:00 resolving dependencies--> Running transaction Check---> Package mysql-community-server.x86_64 0:5 .6.24-3.el7 'll be installed--> processing Dependency:mysql-community-common (x86-64) = 5.6.24-3.el7 for Package:mys ql-community-server-5.6.24-3.el7.x86_64--> ProcEssing dependency:mysql-community-client (x86-64) = 5.6.24-3.el7 for package:mysql-community-server-5.6.24-3.el7.x86 _64--> processing Dependency:perl (DBI) for package:mysql-community-server-5.6.24-3.el7.x86_64--> Transaction Check---> Package mariadb.x86_64 1:5.5.41-2.el7_0 'll be obsoleted---> Package mysql-community-clien T.x86_64 0:5.6.24-3.el7 'll be obsoleting--> processing dependency:mysql-community-libs (x86-64) = 5.6.24-3.el7 for P ackage:mysql-community-client-5.6.24-3.el7.x86_64---> Package mysql-community-common.x86_64 0:5.6.24-3.el7 would be installed---> Package perl-dbi.x86_64 0:1.627-4.el7 won't be installed--> processing Dependency:perl (RPC::P lser  ver) >= 0.2001 for package:perl-dbi-1.627-4.el7.x86_64--> processing (RPC::P dependency:perl) lclient 0.2000 For package:perl-dbi-1.627-4.el7.x86_64--> Running transaction Check---> Package mariadb-libs.x86_64 1:5.5.41-2 . El7_0 'll be obsoleted--> processing DEpendency:mariadb-libs (x86-64) = 1:5.5.41-2.el7_0 for package:1:mariadb-devel-5.5.41-2.el7_0.x86_64---> Package Mysql-community-libs.x86_64 0:5.6.24-3.el7 'll be obsoleting---> Package perl-plrpc.noarch 0:0.2020-14.el7 would be I nstalled--> processing Dependency:perl (Net::D Aemon) >= 0.13 for Package:perl-plrpc-0.2020-14.el7.noarch--> P Rocessing Dependency:perl (Net::D aemon::test) for Package:perl-plrpc-0.2020-14.el7.noarch--> Processing Dependency:perl (Net::D aemon::log) for Package:perl-plrpc-0.2020-14.el7.noarch--> processing Dependency:perl ( Compress::zlib) for Package:perl-plrpc-0.2020-14.el7.noarch--> Running transaction Check---> Package Mariadb-devel.x86_64 1:5.5.41-2.el7_0 'll be obsoleted---> Package mysql-community-devel.x86_64 0:5.6.24-3.el7 'll be obsoleting---> Package perl-io-compress.noarch 0:2.061-2.el7 'll be installed--> processing Erl (compress::raw::zlib) >= 2.061 for Package:perl-io-compress-2061-2.el7.noarch--> processing Dependency:perl (COMPRESS::RAW::BZIP2) >= 2.061 for package: Perl-io-compress-2.061-2.el7.noarch---> Package perl-net-daemon.noarch 0:0.48-5.el7 'll be installed--> Running transaction Check---> Package perl-compress-raw-bzip2.x86_64 0:2.061-3.el7 'll be installed---> Package p 

Erl-compress-raw-zlib.x86_64 1:2.061-4.el7 'll be installed--> finished Dependency resolution dependencies Resolved =============================================================================================================== ======================================================== Package Arch Version Repository S Ize ============================================================================================================ =========================================================== installing:mysql-community-client x86_64 5.6.24- 3.el7 mysql56-community M replacing mariadb.x86_64 1:5.5.41-2.el7_0 mysql-community-devel x86_64 5.6.24-3.el7 mysql56-community 3.4 M replacing mariadb-d evel.x86_64 1:5.5.41-2.el7_0 mysql-community-libs x86_64 5.6.24-3.el7 mysql56-community 2.0 M R Eplacing mariadb-libs.x86_64 1:5.5.41-2.el7_0 mysql-community-server x86_64 5.6.24-3.el7 ity M Installing for Dependencies:mysql-community-common x86_64 5.6.24-3.EL7 mysql56-communit       Y 256 k perl-compress-raw-bzip2 x86_64 2.061-3.el7 base k perl-compress-raw-zlib  x86_64 1:2.061-4.el7 base k perl-dbi x86_64 1.627-4.EL7 Base 802 K perl-io-compress Noarch 2.061-2.el7 base Perl-net-daemon noarch 0.48- 5.el7 base Wuyi k perl-plrpc noarch 0.2020-14.el7 base k Transaction Summ ary =================================================================================================================================== ==================================== Install 4 Packages (+7 Dependent Packages) Total download size:83 M downloading PAC kages:mysql-community-devel-5.6.24-3.el7.x86_64.rpm | 3.4 MB 00:00:03 mysql-community-client-5.6.24-3.el7.x86_64.rpm | MB 00:00:05 mysql-community-server-5.6.24-3.el7.x86_64.rpm | MB 00:00:11 mysql-community-libs-5.6.24-3.el7.x86_64.rpm | 2.0 MB 00:00:02 perl-dbi-1.627-4.el7.x86_64.rpm | 0 B 00:00:01 ... perl-dbi-1.627-4.el7.x86_64.rpm | KB 00:00:00 (1/6): perl-compress-raw-zlib-2.061-4.el7.x86_64.rpm | MB 00:00:00 (2/6): perl-net-daemon-0.48-5.el7.noarch.rpm |                 Wuyi KB 00:00:00 (3/6): perl-plrpc-0.2020-14.el7.noarch.rpm         | MB 00:00:00 (4/6): perl-compress-raw-bzip2-2.061-3.el7.x86_64.rpm | MB 00:00:00 (5/6): perl-io-compress-2.061-2.el7.noarch.rpm | KB 00:00:00 Warning:/var/cache/yum/x86_64/7/mysql56-community/packages/  Mysql-community-common-5.6.24-3.el7.x86_64.rpm:v3 dsa/sha1 Signature, key ID 5072e1f5:nokey 00:00:01 ETA public key for MYSQL-COMMUNITY-COMMON-5.6.24-3.EL7.X86_64.RPM is isn't installed (6/6): mysql-community-common-5.6.24-3.el7.x86_64. RPM | 256 KB 00:00:01------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------Total 388 kb/s | 692 KB 00:00:01 Retrieving key from File:/etc/pki/rpm-gpg/rpm-gpg-key-mysql importing GPG key 0x5072e1f5:userid: "My SQL release Engineering <mysql-build@oss.oracle.com> "fingerprint:a4a9 4068 76fc bd3c 4567 70c88c71 8d3b 5072 e1f5 package:mysql-community-release-el7-5.noarch installed (from:file:/etc/pki/rpm-gpg/rpm-gpg-key) -mysql Running Transaction Check Running Transaction Test transaction test succeeded Running transaction Warning:rpmdb AL
 tered outside of Yum. installing:mysql-community-common-5.6.24-3.el7.x86_64 1/14 Installing:mysql-community-libs                           -5.6.24-3.el7.x86_64 2/14 installing:mysql-community-client-5.6.24-3.el7.x86_64 3/14 Installing:perl-net-daemon-0.48-5.el7.noarch 4/14 Installing:1:perl -compress-raw-zlib-2.061-4.el7.x86_64 5/14 installing:perl-compress-raw-bzip2-2.061-3.el7.x86 _64 6/14 Installing:perl-io-compress-2.061-2.el7.noarch 7/14 in     Stalling:perl-plrpc-0.2020-14.el7.noarch 8/14 installing:perl-dbi-1.627-4.el7.x86_64                         9/14 installing:mysql-community-server-5.6.24-3.el7.x86_64 10/14 I nstalling:mysql-community-devel-5.6.24-3.el7.x86_64 11/14 Erasing:1:mariadb-devel-5.5.41-2 . el7_0.x86_64 12/14 erasing:1:mariadb-5.5.41-2.el7_0.x86_64 13/ erasing:1:mariadb-libs-5.5.41-2.el7_0.x86_64 14/14 VERIFYING:PERL-COMPRESS-RAW-BZIP2                           -2.061-3.el7.x86_64 1/14 verifying:mysql-community-common-5.6.24-3.el7.x86_64 2/14 verifying:mysql-community-devel-5.6.24-3.el7.x86_64 3/14 verifying:mysq                             l-community-client-5.6.24-3.el7.x86_64 4/14 Verifying:perl-plrpc-0.2020-14.el7.noarch 5/14 verifying:mysql-community-server-5.6.24-3.el7.x86_64 6/14 Verif Ying:1:perl-compress-raw-zlib-2.061-4.el7.x86_64 7/14 verifying:mysql-community-libs-5.6.24-3.el7.x86_64 8/14 Verifying:perl-net-daemon-0.48-5.el7.noarch 9/14 verifying:perl-d                            bi-1.627-4.el7.x86_64 10/14 Verifying:perl-io-compress-2.061-2.el7.noarch 11/14 verifying:1:mariadb-5.5.41-2.el7_0.x86_64 12/14 verifying:1:mariadb-                            devel-5.5.41-2.el7_0.x86_64 13/14 verifying:1:mariadb-libs-5.5.41-2.el7_0.x86_64 14/14 installed:mysql-community-client.x86_64 0:5.6.24-3.el7 mysql-community-devel.x86_64 0:5.6.24- 3.el7 mysql-community-libs.x86_64 0:5.6.24-3.el7 mysql-community-server.x86_64 0:5.6.24-3.el7 Dependency ed:mysql-community-common.x86_64 0:5.6.24-3.el7 perl-compress-raw-bzip2.x86_64 0:2.061-3.el7 perl-compress-raw-zlib.x86_64 1:2.061-4.el7 perl-dbi.x86_64 0:1.627-4.el7 perl-io-compress.noarch 0:2.061-2.el7 perl-net-daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0: 0.2020-14.el7 Replaced:mariadb.x86_64 1:5.5.41-2.el7_0 mariadb-devel.x86_64 1:5.5.41-2.el7_0 mariadb-libs.x8

 6_64 1:5.5.41-2.el7_0 complete!

3 after the MySQL installation completes, carries on the related configuration

After installing MySQL, you need to do some basic configuration work:

###### #安装成功后, add it to boot
[root@typecodes ~]# systemctl enable mysqld

###### #启动mysql服务进程
[root@typecodes ~] # systemctl start mysqld

###### #配置mysql (set password, etc.)
[Root@typecodes ~]# mysql_secure_installation

Note:running all PARTS The This SCRIPT are recommended for all MySQL
  SERVERS in PRODUCTION use! Please READ each step carefully!

In order to log into MySQL to secure it, we'll need the current password for the
root user. If you ' ve just installed MySQL, and you
haven ' t set the root password yet, the password'll be blank, so you
sh Ould just press ENTER here.

Enter current password for root (enter to none): 
OK, successfully used password, moving on ...

Setting The root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root Password?
[y/n] Y [set root user password] New password:re-enter new Password:password updated successfully!
 Reloading privilege tables. ...


success! By default, a MySQL installation has the anonymous user, allowing anyone to log into MySQL without have to have a user AC Count created for them. This is intended only for testing, and to make the installation go a bit smoother.

You are should remove them before moving into a production environment. Remove anonymous users? [y/n] Y [delete anonymous user] ...

success! Normally, Root should only is allowed to connect from ' localhost '.

This ensures is someone cannot guess at the root password from the network. Disallow root login remotely? [y/n] Y [prohibit root telnet] ...

success! By default, the MySQL comes with a database named ' test ' that anyone can access.

This is also intended only for testing, and should are removed before into a moving production. Remove test database and access to it? [y/n] Y [delete test database]-dropping test DatabaSe ... ERROR 1008 (HY000) at line 1:can ' t drop database ' test '; Database doesn ' t exist ... failed! Not critical, keep moving ...-removing privileges on test database ...

success!

Reloading the privilege tables would ensure this all changes made so far would take effect. Reload privilege tables now? [y/n] Y [Refresh permissions] ...




success! All done!

If you have ' ve completed all of the above steps, your MySQL installation should now be secure.


The for using mysql!

 Cleaning up ...

4. Possible effective MySQL configuration optimization
Add configuration parameters to the [mysqld] section, based on the original configuration file/ETC/MY.CNF. In practical applications, please modify the hardware and load as appropriate.

#add by Feng 120418--------------------------
#skip-locking
skip-name-resolve
skip-external-locking
key_buffer_size = 256M
#table_cache = 3072
table_open_cache = 3072
read_buffer_size = 2M
read_rnd _buffer_size = 2M
sort_buffer_size = 2M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_ cache_size= 512M
query_cache_limit= 5M
tmp_table_size=1024m
max_heap_table_size=3000m
max_ Allowed_packet = 16M
innodb_buffer_pool_size = 512M
innodb_log_file_size = 512M
innodb_additional_mem _pool_size=512m
innodb_log_buffer_size=64m
max_connections=2000
max_user_connections=800
Join_buffer_size = 8M
open_files_limit = 65535
#tmpdir =/dev/shm
max_connect_errors=1000
#add by Feng 120418 End---------------------