Install MySQL + problem handling + security optimization on Ubuntu _ MySQL

This article mainly summarizes the installation of MySQL + problem handling + security optimization on Ubuntu, which is very detailed and comprehensive. if you need it, you can refer 0. description

Of course, MySQL is installed in a variety of ways. on Ubuntu, you can install it using apt-get. the advantage is that it is fast and convenient. Basically, it will help you solve the dependency problem of all function libraries. normally, MySQL can be used as long as apt-get is executed completely.

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

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 15.10 after Ubuntu 5.6, so it is of great reference value!

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:

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

2. installation preparation: install the MySQL dependency function library

MySQL depends on a function library named libaio and needs to be installed first. otherwise, problems may occur when installing MySQL later.

As follows:

Xpleaf @ leaf :~ $ Sudoapt-cachesearchlibaio # Search for libaio-related information xpleaf @ leaf :~ $ Sudoapt-getinstalllibaio1 # install libaio1

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

3. install and run MySQL

(1) create a user named mysql and a user group respectively.

As follows:

xpleaf@leaf:~$sudogroupaddmysqlxpleaf@leaf:~$sudouseradd-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/localxpleaf@leaf:/usr/local$sudotarzxvf/home/xpleaf/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gzxpleaf@leaf:/usr/local$sudoln-s/usr/local/mysql-5.6.28-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:

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

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

# Modify the owner of all files in the mysql directory to mysqlxpleaf @ leaf:/usr/local/mysql $ sudochown-Rmysql. # Modify the group of all files in the mysql directory to mysqlxpleaf @ leaf:/usr/local/mysql $ sudochgrp-Rmysql. # initialize data xpleaf @ leaf as a mysql User: /usr/local/mysql $ sudoscripts/mysql_install_db -- user = mysql # Modify the owner of all files in the mysql directory to rootxpleaf @ leaf: /usr/local/mysql $ sudochown-Rroot. # modify the data directory under the mysql directory to mysqlxpleaf @ leaf:/usr/local/mysql $ sudochown-Rmysqldata

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:

xpleaf@leaf:/usr/local/mysql$sudobin/mysqld_safe--user=mysql&[1]8365xpleaf@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

Make sure that the MySQL service is enabled:

xpleaf@leaf:~$sudonetstat-antup|grepmysqldtcp600:::3306:::*LISTEN8455/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:

xpleaf@leaf:/usr/local/mysql$sudobin/mysqladminversionbin/mysqladminVer8.42Distrib5.6.28,forlinux-glibc2.5onx86_64Copyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners. Serverversion5.6.28Protocolversion10ConnectionLocalhostviaUNIXsocketUNIXsocket/tmp/mysql.sockUptime:8min36sec Threads:1Questions:2Slowqueries:0Opens:67Flushtables:1Opentables:60Queriespersecondavg:0.003

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

# Use mysqladmin to disable mysql service xpleaf @ leaf:/usr/local/mysql $ sudobin/mysqladmin-urootshutdown # start mysql service xpleaf @ leaf: /usr/local/mysql $ sudobin/mysqld_safe -- user = mysql & # view the default database xpleaf @ leaf in the mysql database: /usr/local/mysql $ sudobin/mysqlshow + -------------------- + | Databases | + -------------------- + | information_schema | mysql | performance_schema | test | + Consumer + # view the mysql database (note) this mysql database is an entity, data table xpleaf @ leaf:/usr/local/mysql $ sudobin/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 | time_zone_leap_second | time_zone_name | time_zone_transition | time_zone_transition_type | user | + users + # View all user tables in mysql database xpleaf @ leaf: /usr/local/mysql $ sudobin/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, we will use the command line method for mysql database. However, before this step, we need to know that Ubuntu does not install the mysql client by default, therefore, we need to use the following command to install:

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

Then log on:

xpleaf@leaf:～$mysqlERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/var/run/mysqld/mysqld.sock'(13)

Problem: If the above error occurs during the operation, follow the steps below to solve the problem:

xpleaf@leaf:~$sudomkdir/var/run/mysqldxpleaf@leaf:~$cd/var/run/mysqld/xpleaf@leaf:/var/run/mysqld$sudotouchmysqld.sockxpleaf@leaf:/var/run/mysqld$sudochown-Rmysql:mysql/var/run/mysqld/xpleaf@leaf:/var/run/mysqld$cdxpleaf@leaf:~$sudobashroot@leaf:~#cd/usr/local/mysqlroot@leaf:/usr/local/mysql#exitxpleaf@leaf:~$sudobashroot@leaf:~#cd/usr/local/mysqlroot@leaf:/usr/local/mysql#echosocket=/var/run/msqld/mysqld.sock>>my.cnfroot@leaf:/usr/local/mysql#exitxpleaf@leaf:~$sudoln-s/var/lib/mysql/mysql.sock/tmp/mysql.sock

Start MySQL:

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

Then log on again:

xpleaf@leaf:/usr/local/mysql$mysql-urootWelcometotheMySQLmonitor.Commandsendwith;or\g.…… mysql>

Finally, you can!

If you encounter a different situation in this step, you can leave a message for me. After successful login, proceed with the following operations:

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 localhostmysql -u root -h leafmysql -u root -h 127.0.0.1mysql -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:

Use the set password statement

Update statement

Use the mysqladmin command

Method 1: Use the set password statement

xpleaf@leaf:～$mysql-uroot-pEnterpassword: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;

Method 2: Use the update statement

xpleaf@leaf:～$mysql-uroot-pEnterpassword:mysql>updatemysql.usersetpassword=password('123456')->whereUser='root';mysql>flushprivileges;

Of course, you can select either of the above two 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:

xpleaf@leaf:～$mysql-urootERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO)xpleaf@leaf:/usr/local/mysql$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:

xpleaf@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';mysql>flushprivileges;

(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 access thetestdatabase and other databases with names that start withtest _

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:

xpleaf@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 Ubuntu 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.