Install MySQL + installation problem solving + Security Optimization on CentOS source code

Install MySQL + installation problem solving + Security Optimization on CentOS source code

0. Description

Of course, MySQL is installed in a variety of ways. On CentOS, you can install MySQL in YUM mode. The advantage is that it is fast and convenient. Basically, it will help you solve all the library dependencies. Normally, MySQL can be used as long as YUM execution is complete.

However, I prefer to use the source code to install MySQL for a simple reason:In addition to the detailed official documents, you can also clearly know what you are doing. This will be of great help in the future when MySQL runs abnormally!

But even if you install it according to the official documents, you may encounter various problems. Here, I will present a complete process for you to complete the following four tasks:

Download MySQL 5.6

Installation preparation: Install the MySQL dependency function library

Install and run MySQL

Optimize MySQL

(1) Account Security Optimization

(2) database security Optimization

I installed MySQL 6.5 after CentOS 5.6, so it is very useful for reference!

1. Download MySQL 5.6

: Http://dev.mysql.com/downloads/mysql/5.6.html

After entering, select:

Linux-Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive

Or

Linux-Generic (glibc 2.5) (x86, 32-bit), Compressed TAR Archive

It depends on whether you are using 32-bit or 64-bit. Here, I downloaded 64-bit. The downloaded package is as follows:

[root@leaf~]#lsmysql-5.6.29-linux-glibc2.5-x86_64.tar.gz

2. Installation preparation: Install the MySQL dependency function library

MySQL depends onLibaioYou must first install the function library. Otherwise, problems may occur when you install MySQL later.

As follows:

[Root @ leaf ~] # Yumsearchlibaio # Find information about libaio [root @ leaf ~] # Yuminstalllibaio # Install libaioLoadedplugins: securitySettingupInstallProcessPackagelibaio-0.3.107-10.el6.x86_64alreadyinstalledandlatestversionNothingtodo

Of course, you may have installed CentOS after you have installed CentOS.LibaioIt depends on the installation package you selected when installing CentOS. As shown in my installation prompt above, it indicates that my system has been installed.Libaio.

 

3. Install and run MySQL

(1) createMysqlUser and user group

As follows:

[root@leaf~]#groupaddmysql[root@leaf~]#useradd-r-gmysql-s/bin/falsemysql

The-r and-s parameters allow the mysql user to have no permission to log on to your system, which ensures system security.

(2) unpack and establish soft links

As follows:

[root@leaf～]#cd/usr/local[root@leaflocal]#tarzxvf/root/mysql-5.6.29-linux-glibc2.5-x86_64.tar.gz[root@leaflocal]#ln-s/usr/local/mysql-5.6.29-linux-glibc2.5-x86_64/mysql

You need to know that, under normal circumstances, we are used to placing the compiled and installed software in the/usr/local directory. Of course you can choose your own, but we recommend that you put it here.

The advantage of creating a soft link is that if you need to use the mysql installation directory, you do not need to enter a long string of directory names, because the directory of the decompressed mysql package has a long name.

 

(3) initialize the Data directory

After you unpack MySQL, the MySQL directory contains a data directory:

[root@leaflocal]#cdmysql[root@leafmysql]#ls-ddata/data/

It contains the system information necessary for MySQL to run. Therefore, we need to initialize the data as follows:

[Root @ leafmysql] # chown-Rmysql. # modify the owner of all files in the mysql directory to mysql [root @ leafmysql] # chgrp-Rmysql. # modify the group of all files in the mysql directory to mysql [root @ leafmysql] # scripts/mysql_install_db -- user = mysql # initialize data as a mysql user [root @ leafmysql] # chown-Rroot. # modify the owner of all files in the mysql directory to root [root @ leafmysql] # chown-Rmysqldata # modify the owner of the data directory in the mysql directory to mysql

Please be sure to follow the above operations, as to what is the detailed meaning, why do so, can refer to the official documentation, there is a very detailed explanation: http://dev.mysql.com/doc/refman/5.6/en/data-directory-initialization.html

(4) Start MySQL

As follows:

[root@leafmysql]#bin/mysqld_safe--user=mysql&[1]30877[root@leafmysql]#16030611:58:50mysqld_safeLoggingto'/var/log/mysqld.log'.16030611:58:50mysqld_safeStartingmysqlddaemonwithdatabasesfrom/var/lib/mysql16030611:58:51mysqld_safemysqldfrompidfile/var/run/mysqld/mysqld.pidended

In theory, it should be okay, but according to the above operationsEndedThe prompt is that mysql cannot be started. Now let's take a look at the log information:

[root@leaf~]#tail-f/var/log/mysqld.log......2016-03-0612:00:3631231[ERROR]/usr/local/mysql/bin/mysqld:Can'tcreate/writetofile'/var/run/mysqld/mysqld.pid'(Errcode:2-Nosuchfileordirectory)2016-03-0612:00:3631231[ERROR]Can'tstartserver:can'tcreatePIDfile:Nosuchfileordirectory16030612:00:36mysqld_safemysqldfrompidfile/var/run/mysqld/mysqld.pidended

Problem:Can't create PID file: No such file or directory, that is, the pid file started by mysql cannot be found.

Solution:The mysqld directory does not exist. You can create it.

[Root @ leafmysql] # mkdir/var/run/mysqld [root @ leafmysql] # cd/var/run/mysqld/[root @ leafmysqld] # touchmysqld. pid # create mysqld. pid file [root @ leafmysqld] # cd .. [root @ leafrun] # chown-Rmysqlmysqld # Set the owner of the mysqld directory to mysql

Go back to the mysql directory and start mysql again, as shown below:

[root@leafrun]#cd/usr/local/mysql[root@leafmysql]#bin/mysqld_safe--user=mysql16030612:12:45mysqld_safeLoggingto'/var/log/mysqld.log'.16030612:12:45mysqld_safeStartingmysqlddaemonwithdatabasesfrom/var/lib/mysql

We can see that there is no prompt to terminate the operation. Let's check whether the mysql service has been started:

[root@leaf~]#netstat-antupActiveInternetconnections(serversandestablished)ProtoRecv-QSend-QLocalAddressForeignAddressStatePID/Programnametcp000.0.0.0:33060.0.0.0:*LISTEN31484/mysqld

The mysql service has been started successfully!

(5) Test the mysql Service

To make mysql better run on your system, we recommend that you perform some mysql service tests as follows:

[root@leafmysql]#bin/mysqladminversionbin/mysqladmin:connecttoserverat'localhost'failederror:'Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysql.sock'(2)'Checkthatmysqldisrunningandthatthesocket:'/tmp/mysql.sock'exists!

Perform the test according to the official documentation, but the above problem occurs (note that my mysql service has been enabled !).

Problem:/Tmp/mysql. sock does not exist

Solution:In fact, mysql. sock exists, but it is not in the/tmp directory. By default, mysql. sock is in the/var/lib/mysql/directory. You only need to create a soft link to the/tmp directory.

[root@leafmysql]#ln-s/var/lib/mysql/mysql.sock/tmp/mysql.sock

Repeat the preceding operations:

[root@leafmysql]#bin/mysqladminversionbin/mysqladminVer8.42Distrib5.6.29,forlinux-glibc2.5onx86_64Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Serverversion5.6.29Protocolversion10ConnectionLocalhostviaUNIXsocketUNIXsocket/tmp/mysql.sockUptime:6min36secThreads:1Questions:2Slowqueries:0Opens:67Flushtables:1Opentables:60Queriespersecondavg:0.005

Successful! Then let's warm up the following operations:

[Root @ leafmysql] # bin/mysqladmin-urootshutdown # Use mysqladmin to disable the mysql service [root @ leafmysql] # bin/mysqld_safe -- user = mysql & # Start the mysql service # view the mysql database default database [root @ leafmysql] # bin/mysqlshow + -------------------- + | Databases | + Databases + | information_schema | mysql | performance_schema | test | + ------------------ + # view mysql database (note that this mysql database is an entity, [root @ leafmysql] # bin/mysqlshowmysqlDatabase: mysql + metrics + | Tables | + metrics + | columns_priv | db | event | func | general_log | help_category | help_keyword | help_relation | help_topic | innodb_index_stats | | metrics | plugin | proc | procs_priv | proxies_priv | servers | slave_master_info | metrics | slow_log | tables_priv | time_zone | principal | time_zone_name | time_zone_transition | time_zone_transition_type | user | + Principal + # view all user tables in the mysql database [root @ leafmysql] # bin/mysql-e "SELECTUser, host, pluginFROMmysql. user "mysql + ------ + ----------- + login + | User | Host | plugin | + ------ + ----------- + login + | root | localhost | mysql_native_password | root | leaf | mysql_native_password | root | 127.0.0.1 | mysql_native_password | root |:: 1 | mysql_native_password | localhost | mysql_native_password | leaf | mysql_native_password | + ------ + ----------- + ----------------------- +

Note that the above tests must be performed only when you have started the mysql service. At the same time, if you want to know the detailed explanation of each step, you can refer to the official documentation: http://dev.mysql.com/doc/refman/5.6/en/testing-server.html

To be precise, MySQL has been successfully installed! Next we will do some basic optimization, mainly from the security perspective.

4. Optimize MySQL

(1) MySQL Database User Account Security Optimization

-- The user account is insecure by default.

In the previous initialization of the data Directory, MySQL actually created some mysql accounts for us. These accounts and their permissions are stored in mysql. in the user table (see operations below), we need to describe and manage these initialized accounts.

First, execute the following command in the mysql database:

[root@leaf~]#mysqlWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis7Serverversion:5.6.29MySQLCommunityServer(GPL)Copyright(c)2000,2013,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>

You can see that you can access the database without any password. In fact, we log in to the mysql database as a root user.

View the default username:

mysql>selectUser,Host,Passwordfrommysql.user;+------+-----------+----------+|User|Host|Password|+------+-----------+----------+|root|localhost|||root|leaf|||root|127.0.0.1|||root|::1||||localhost||||leaf||+------+-----------+----------+6rowsinset(0.00sec)

From this table, we can see two types of users:

A. root User: has the highest Permissions

B. Anonymous Users: limited Permissions

As for the Host parameter, it indicates the method in which the user can access the mysql database. For example, for the root user, the current method can be used to access the mysql database:

Mysql-u root-h localhost

Mysql-u root-h leaf

Mysql-u root-h 127.0.0.1

Mysql-u root-h: 1

This is also true for anonymous users, but either of them actually means logging in from the local device.

However, we will find a problem, that is, the Password column of the two users is empty, which is why we can enter mysql directly. By default, neither the root user nor anonymous user has a password!

This is the reason why we want to optimize the database. If you do not manage these users, no one can perform your database!

 

-- Account Security optimization 1: Create a password for the root user

What we need to do next is:

Create a password for each root user

You can perform these operations in three ways:

1. Use the set password statement

   Update statement

   Use the mysqladmin command

   Method 1: Use the set password statement

   [root@leaf~]#mysql-urootmysql>setpasswordfor'root'@'localhost'=password('123456');mysql>setpasswordfor'root'@'127.0.0.1'=password('123456');mysql>setpasswordfor'root'@'::1'=password('123456');mysql>setpasswordfor'root'@'leaf'=password('123456');mysql>flushprivileges;

   Method 2: Use the update statement

   [root@leaf~]#mysql-urootmysql>updatemysql.usersetpassword=password('123456')->whereUser='root';mysql>flushprivileges;

   Method 3: Use the mysqladmin command

   [root@leaf~]#mysqladmin-urootpassword'123456'

   Of course, you can select one of the above three methods. In this case, we have created a password for the root user. Then, when using the root user to log on to the database, we need to enter the password as follows:

   [root@leaf~]#mysql-urootERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO)[root@leaf~]#mysql-uroot-pEnterpassword:

   Now let's take a look at the mysql. user table again:

   mysql>selectUser,Host,Passwordfrommysql.user;+------+-----------+-------------------------------------------+|User|Host|Password|+------+-----------+-------------------------------------------+|root|localhost|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||root|leaf|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||root|127.0.0.1|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||root|::1|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|||localhost||||leaf||+------+-----------+-------------------------------------------+6rowsinset(0.00sec)

   We can see that the password has been created for the root user, but the value after the password is hashed and encrypted is displayed here.

   -- Account Security optimization 2: Create a password for an anonymous user or delete an anonymous user

   What we need to do next is:

   Create a password for an anonymous user or delete an anonymous user

   A. Create a password for an anonymous user

   Same as the above method:

   [root@leaf~]#mysql-uroot-pEnterpassword:mysql>updatemysql.usersetpassword=password('123456')whereUser='';mysql>flushprivileges;

   Here we use the update statement.

   Note the 'flush privileges' statement here. This statement takes effect immediately after we change the password, without restarting the mysql service. If this statement is not used, at the same time, the mysql service has not been restarted. When you log on to mysql again with the new password, ERROR 1045 (28000) is always prompted: Access denied for user 'root' @ 'localhost' (using password: YES) error.

   B. Delete Anonymous Users

   If you think it is unnecessary to keep anonymous users, you can delete them directly:

   shell>mysql-uroot-pEnterpassword:(enterrootpasswordhere)mysql>DROPUSER''@'localhost';mysql>DROPUSER''@'host_name';

   (2) MySQL Test Database Security Optimization

   -- By default, the database itself has security risks.

   Official documents:

   Themysql.dbTable contains rows that permit all accounts to accesstestDatabase and other databases with names that starttest_

   That is to say, mysql. some rows in the db table allow all users to access the test database or the database whose name starts with test _. Although this is very convenient for the test database, it still brings some security risks, so we also need to optimize it.

   -- Database security optimization: Delete the test database or database whose name starts with test.

   As follows:

   [root@leaf~]#mysql-uroot-pmysql>deletefrommysql.dbwheredblike'test%';mysql>dropdatabasetest;mysql>flushprivileges;

   For basic security optimization, refer to the official documentation: http://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

   This article is over. If you compile and install MySQL on CentOS for the first time, as long as you follow the above method, there will be no problems under normal circumstances, the blogger has performed multiple tests.

   Of course, after you are fully familiar with such a process, you do not need to perform this step by step during later compilation and installation. You only need to write a one-click installation and deployment script.