Install mysql+ problem handling on Ubuntu + security optimization _mysql

0. Notes

Of course, MySQL is installed in a variety of ways, in Ubuntu, you can use the Apt-get way to install, the advantage is: fast and convenient. Basically, it will help you solve all the function library dependencies, normally, as long as apt-get execution, then MySQL can be used.

But I prefer to use the source of the way to install MySQL, the reason is very simple: in addition to the detailed official documents, you can also very clearly know what you are doing, this will be in the future when the MySQL run a problem with a lot of help!

But even if you install it as an official document, you will encounter a variety of problems, and here I will present a complete process to all of you until you have completed the following 4 tasks:

Download MySQL 5.6

Installation Preparation: Installing the MySQL dependency function library

Installing and running MySQL

Optimizing MySQL

(1) Account security optimization

(2) Database security optimization

I was installed after the installation of Ubuntu 15.10 MySQL 5.6 installation, so very valuable reference!

1. Download MySQL 5.6

Download Address: http://dev.mysql.com/downloads/mysql/5.6.html

After entering the download address, 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 download is completed after the following package:

xpleaf@leaf:~ $lsmysql *
mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz

2. Installation Preparation: Install MySQL dependency function library

MySQL relies on a function library called Libaio, which needs to be installed first, otherwise there will be a problem installing MySQL later.

As follows:

xpleaf@leaf:~ $sudoapt-cachesearchlibaio# Find information about Libaio
xpleaf@leaf:~ $sudoapt-getinstalllibaio1# installation Libaio1

In this way, you can quickly install the Libaio function library.

3. Install and run MySQL

(1) Create a separate user and user group named MySQL

As follows:

xpleaf@leaf:~ $sudogroupaddmysql
xpleaf@leaf:~ $sudouseradd-r-gmysql-s/bin/falsemysql

The-R and-s parameters can make MySQL the user does not have access to your system, which can guarantee the security of the system.

(2) Unpack and build soft links

As follows:

[root@leaf~] #cd/usr/local
xpleaf@leaf:/usr/local$sudotarzxvf/home/xpleaf/mysql-5.6.28-linux-glibc2.5-x86_ 64.tar.gz
Xpleaf@leaf:/usr/local$sudoln-s/usr/local/mysql-5.6.28-linux-glibc2.5-x86_64/mysql

What needs to know is, normally, we are accustomed to put the compiled software in the/usr/local directory, of course, you can choose, but it is recommended here.

The advantage of establishing a soft link is that if you need to use the MySQL installation directory, you don't have to enter a long list of directory names, because we unpacked the MySQL package after the directory, the name is very long.

(3) Initialization of the data directory

After you unpack MySQL, there will be a data directory in the MySQL directory:

Xpleaf@leaf:/usr/local$cdmysql
xpleaf@leaf:/usr/local/mysql$ls-ddata/
data/

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

#修改mysql目录下的所有文件的属主为mysql
xpleaf@leaf:/usr/local/mysql$sudochown-rmysql.
#修改mysql目录下的所有文件的属组为mysql
xpleaf@leaf:/usr/local/mysql$sudochgrp-rmysql.
#以mysql用户的身份初始化数据
Xpleaf@leaf:/usr/local/mysql$sudoscripts/mysql_install_db--user=mysql
# The owner of all files under the MySQL directory is changed to root
xpleaf@leaf:/usr/local/mysql$sudochown-rroot.
#修改mysql目录下的data目录的属主为mysql
Xpleaf@leaf:/usr/local/mysql$sudochown-rmysqldata

Please be sure to follow the above operation, as to the details of what it means, why to do so, you can refer to the official document, there are very detailed explanations: http://dev.mysql.com/doc/refman/5.6/en/ Data-directory-initialization.html

(4) Start MySQL

As follows:

xpleaf@leaf:/usr/local/mysql$sudobin/mysqld_safe--user=mysql&
[1]8365
Xpleaf@leaf:/usr/local/mysql $16030814:13:26mysqld_safeloggingto '/usr/local/mysql-5.6.28-linux-glibc2.5-x86_64/data/leaf.err '.
16030814:13:26mysqld_safestartingmysqlddaemonwithdatabasesfrom/usr/local/mysql-5.6.28-linux-glibc2.5-x86_64/ Data

Verify that the MySQL service is open:

xpleaf@leaf:~ $sudonetstat-antup|grepmysqld
tcp600:::3306:::* listen8455/mysqld

The MySQL service has actually started successfully!

(5) Test MySQL service

To make MySQL better run on your system, a certain MySQL service test is recommended, as follows:

Xpleaf@leaf:/usr/local/mysql$sudobin/mysqladminversion
bin/mysqladminver8.42distrib5.6.28, forlinux-glibc2.5onx86_64
Copyright (c) 2000,2015,oracleand/oritsaffiliates. Allrightsreserved.
 
Oracleisaregisteredtrademarkoforaclecorporationand/orits
affiliates. Othernamesmaybetrademarksoftheirrespective
owners.
 
Serverversion5.6.28
Protocolversion10
connectionlocalhostviaunixsocket
unixsocket/tmp/mysql.sock
uptime:8min36sec
 
threads:1questions:2slowqueries:0opens:67flushtables:1opentables:60queriespersecondavg:0.003

It worked! Then we'll do the following hot warm-up:

#通过mysqladmin关闭mysql服务 Xpleaf@leaf:/usr/local/mysql$sudobin/mysqladmin-urootshutdown #启动mysql服务 xpleaf@leaf:/usr/ local/mysql$sudobin/mysqld_safe--user=mysql& #查看mysql数据库中默认存在的数据库 xpleaf@leaf:/usr/local/mysql$sudobin/ Mysqlshow +--------------------+ |
databases|
+--------------------+ |information_schema| |mysql| |performance_schema| |test| +--------------------+ #查看mysql数据库 (Note that this MySQL database is an entity, unlike the above collectively, the data table xpleaf@leaf:/usr/local/mysql$sudobin/ Mysqlshowmysql database:mysql +---------------------------+ |
tables| +---------------------------+ |columns_priv| |db| |event| |func| |general_log| |help_category| |help_keyword| |help_ relation| |help_topic| |innodb_index_stats| |innodb_table_stats| |ndb_binlog_index| |plugin| |proc| |procs_priv| | proxies_priv| |servers| |slave_master_info| |slave_relay_log_info| |slave_worker_info| |slow_log| |tables_priv| |time
_zone| |time_zone_leap_second| |time_zone_name| |time_zone_transition| |time_zone_transition_type| |user| +---------------------------+ #查看mysql数据库中的所有user表 xpleaf@leaf:/usr/local/mysql$sudobin/mysql-e "Selectuser,host,pluginfrommysql.user" MySQL +------+-----------+-----------------------+ | user|
host|plugin| +------+-----------+-----------------------+ |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|
 +------+-----------+-----------------------+

It is important to note that the above tests must be done before you have started the MySQL service. At the same time, if you want to know the detailed explanations for each step, you can refer to the official documentation: http://dev.mysql.com/doc/refman/5.6/en/testing-server.html

To be exact, MySQL has been successfully installed! Below we do some basic optimization, mainly from the perspective of security to consider.

4. Optimize MySQL

(1) MySQL database user account security optimization

--The user account is not secure by default

In the process of initializing the data directory, we MySQL actually created some MySQL accounts for us, these accounts and their permissions in mysql.user this table (there will be operations to view), here we have to explain these initialized accounts and the necessary management.

First, we do the MySQL database on a command-line basis, but before this step, we need to know that Ubuntu does not have a MySQL client installed by default, so we need to install it by using the following command:

xpleaf@leaf:~ $sudoapt-getinstallmysql-client-core-5.6

And then log in again:

xpleaf@leaf:~ $mysql
ERROR2002 (HY000): Can ' Tconnecttolocalmysqlserverthroughsocket '/var/run/mysqld/ Mysqld.sock ' (13)

Problem: If you have made the above error while doing this, follow these steps:

xpleaf@leaf:~ $sudomkdir/var/run/mysqld
xpleaf@leaf:~ $cd/var/run/mysqld/
xpleaf@leaf:/var/run/mysqld$ Sudotouchmysqld.sock
xpleaf@leaf:/var/run/mysqld$sudochown-rmysql:mysql/var/run/mysqld/
xpleaf@leaf:/ VAR/RUN/MYSQLD$CD
xpleaf@leaf:~ $sudobash
root@leaf:~ #cd/usr/local/mysql
root@leaf:/usr/local/mysql #exit
xpleaf@leaf:~ $sudobash
root@leaf:~ #cd/usr/local/mysql
root@leaf:/usr/local/mysql#echosocket =/VAR/RUN/MSQLD/MYSQLD.SOCK>>MY.CNF
root@leaf:/usr/local/mysql#exit
xpleaf@leaf:~ $sudoln-s/var /lib/mysql/mysql.sock/tmp/mysql.sock

Start MySQL:

xpleaf@leaf:~ $CD/usr/local/mysql
xpleaf@leaf:/usr/local/mysql$sudobin/mysqld_safe--user=mysql&

And then landing again:

Xpleaf@leaf:/usr/local/mysql$mysql-uroot
welcometothemysqlmonitor.commandsendwith;or\g.
 
..... Mysql>

It's finally ready!

If you encounter a different situation with me in this step, you can leave a message for me. After successful landing, continue with the following actions:

To view the default user name:

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 that there are two types of users:

A.root User: With highest privileges

B. Anonymous users: with limited permissions

As for the host parameter, it is explained through the user, which way to enter the MySQL database, for example, for root users, now the method can be entered in 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 true of anonymous users, but either way, it means landing locally.

But we will find a problem, is that the two users of the password column is empty, which is why we just entered a MySQL directly can enter the reason, by default, root and anonymous users have no password!

This is why we have to do the basic optimization reasons, because you do not manage these users, then who can carry out your database, the database will not be able to talk about!

--Account security optimization

1: Create password for root user

The next thing we're going to do is:

Create a password for each root user

There are three ways you can do these things:

Using the Set Password statement

Using the UPDATE statement

Using the Mysqladmin command

Mode 1: Using the Set Password statement

xpleaf@leaf:~ $mysql-uroot-p
enterpassword:
mysql>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;

Mode 2: Use the UPDATE statement

xpleaf@leaf:~ $mysql-uroot-p
enterpassword:
mysql>updatemysql.usersetpassword=password (' 123456 ')
->whereuser= ' root ';
mysql>flushprivileges;

Of course, the above two ways to choose one on it. In this way, we create a password for the root user and then enter the password when logging in to the database using the root user, as follows:

xpleaf@leaf:~ $mysql-uroot
ERROR1045 (28000): Accessdeniedforuser ' root ' @ ' localhost ' (usingpassword:no)
Xpleaf@leaf:/usr/local/mysql$mysql-uroot-p
Enterpassword:

Then 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)

You can see that the root user has been created as a password, except that it shows the value of the password being hashed.

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

The next thing we're going to do is:

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

A. Create a password for anonymous users

Same as the previous method:

xpleaf@leaf:~ $mysql-uroot-p
enterpassword:
mysql>updatemysql.usersetpassword=password (' 123456 ') Whereuser= ';
mysql>flushprivileges;

Here we use the method of the UPDATE statement.

Also note the ' flush privileges ' statement here, which is used to make the operation that we just modified the password take effect immediately, without restarting the MySQL service, if not using the statement, and without restarting the MySQL service, The error 1045 (28000) is always prompted when you use the new password to login to MySQL: Access denied for user ' root ' @ ' localhost ' (using password:yes) errors.

B. Deleting anonymous users

If you feel that an anonymous user is not necessary, you can also delete it directly:

Shell>mysql-uroot-p
Enterpassword: (enterrootpasswordhere)
mysql>dropuser ' @ ' localhost ';
Mysql>dropuser ' @ ' host_name ';
mysql>flushprivileges;

(2) MySQL Test database security optimization

--The database itself has a security risk by default

Description of official Documentation:

Themysql.dbtable contains rows that permit all accounts to access thetestdatabase and other databases with names ' that star T Withtest_

That is, some of the rows in the Mysql.db table allow all users to access the test database or the database that starts with the name Test_, although this is convenient for testing the database, but it also brings some security risks, so we have to optimize it.

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

As follows:

xpleaf@leaf:~ $mysql-uroot-p
mysql>deletefrommysql.dbwheredblike ' test% ';
mysql>dropdatabasetest;
mysql>flushprivileges;

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

Here is the end of this article, if you are in Ubuntu for the first time compile and install MySQL, as long as you follow the above method to do, normally there will be no problem, Bo Master has been tested many times.

Of course, when you are fully familiar with such a process, you will not need to do so in the future when you compile the installation, just write a one-click script to install the deployment.