Install and configure MySQL under Linux

System: Ubuntu 16.04LTS

1\Download mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz from the official website

2\Establish a working group:

$su
#groupadd mysql
#useradd -r -g mysql mysql

3\Create a directory

#mkdir /usr/local/mysql
#mkdir /usr/local/mysql/data

4\Unzip mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz and copy it to /usr/local/mysql

#tar -zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
#cp -r /home/jieyamulu/mysql-5.7.18-linux-glibc2.5-x86_64/* /usr/local/mysql

5\Modify the permissions of the mysql user on the files under mysql and its subfolders. After modification, you can use ll to view the permissions

root@Ice-***:/usr/local# chown -R mysql:mysql mysql
root@Ice-***:/usr/local#ll
Total dosage 44
drwxr-xr-x 11 root root 4096 May 19 07:39 ./
drwxr-xr-x 11 root root 4096 February 16 04:30 ../
drwxr-xr-x 2 root root 4096 Feb 16 04:19 bin/
drwxr-xr-x 2 root root 4096 February 16 04:19 etc/
drwxr-xr-x 2 root root 4096 February 16 04:19 games/
drwxr-xr-x 2 root root 4096 February 16 04:19 include/
drwxr-xr-x 4 root root 4096 Feb 16 04:23 lib/
lrwxrwxrwx 1 root root 9 3月29 14:11 man -> share/man/
drwxr-xr-x 10 mysql mysql 4096 May 19 07:48 mysql/
drwxr-xr-x 2 root root 4096 February 16 04:19 sbin/
drwxr-xr-x 8 root root 4096 February 16 04:34 share/
drwxr-xr-x 2 root root 4096 February 16 04:19 src/
root@Ice-***:/usr/local# cd mysql/
root@Ice-***:/usr/local/mysql#ll
Total dosage 64
drwxr-xr-x 10 mysql mysql 4096 May 19 07:48 ./
drwxr-xr-x 11 root root 4096 May 19 07:39 ../
drwxr-xr-x 2 mysql mysql 4096 May 19 07:48 bin/
-rw-r--r-- 1 mysql mysql 17987 May 19 07:48 COPYING
drwxr-xr-x 2 mysql mysql 4096 May 19 07:41 data/
drwxr-xr-x 2 mysql mysql 4096 May 19 07:48 docs/
drwxr-xr-x 3 mysql mysql 4096 May 19 07:48 include/
drwxr-xr-x 5 mysql mysql 4096 May 19 07:48 lib/
drwxr-xr-x 4 mysql mysql 4096 May 19 07:48 man/
-rw-r--r-- 1 mysql mysql 2478 May 19 07:48 README
drwxr-xr-x 28 mysql mysql 4096 May 19 07:48 share/
drwxr-xr-x 2 mysql mysql 4096 May 19 07:48 support-files/

6\Modify (or create) the /etc/my.cnf configuration file

root@Ice-***:/usr/local/mysql# vim /etc/my.cnf 
[mysqld] basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data
:wq

7\The most critical initialization

# cd /usr/local/mysql/
root@Ice-***:/usr/local/mysql# ./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize

2017-05-19T00:15:46.529420Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-05-19T00:15:47.066125Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-05-19T00:15:47.213711Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-05-19T00:15:47.286951Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4e958344-3c28-11e7-8334-c8d3ffd2db82.
2017-05-19T00:15:47.292857Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-05-19T00:15:47.294758Z 1 [Note] A temporary password is generated for root@localhost: YjaotQk*2ew4

The initial password should be remembered. There may be many problems here, such as:

Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Missing dependency packages

Solution: sudo apt-get install libaio-dev

It is also possible that the previous steps were incorrect, resulting in insufficient permissions to operate the data file, etc. Follow the steps and install what is missing (there are prompts). At this point, the initialization should be successful. Among the warnings, it is worth noting that Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. You can check what is going on when you have time. It will not affect you if you ignore this.

8\Don't start it in a hurry, it can't be started now. Execute the code to change the files under mysql except the data folder to root permissions

root@Ice-***:/usr/local/mysql# chown -R root .
root@Ice-***:/usr/local/mysql# chown -R mysql data

9\Start

root@Ice-***:/usr/local/mysql# bin/mysqld_safe --user=mysql &

Press Enter

root@Ice-***:/usr/local/mysql# /usr/local/mysql/bin/mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

10\Reset password

mysql> SET PASSWORD = PASSWORD('newpasswd');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+
4 rows in set (0.00 sec)
mysql> quit
Bye

11\Set boot up

root@Ice-***:/usr/local/mysql# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
root@Ice-***:/usr/local/mysql# chmod 755 /etc/init.d/mysqld

12\Install mysql-server and mysql-client

root@Ice-***:~# apt-get install mysql-server
root@Ice-***:~# apt-get install mysql-client
root@Ice-***:~# apt-get install libmysqlclient-dev


E: Sub-process /usr/bin/dpkg returned an error code (1)

Solution:

1.$ sudo mv /var/lib/dpkg/info /var/lib/dpkg/info_old //Rename the info folder
2.$ sudo mkdir /var/lib/dpkg/info //Create a new info folder
3.$ sudo apt-get update,
$ apt-get -f install //Repair dependency tree
4.$ sudo mv /var/lib/dpkg/info/* /var/lib/dpkg/info_old //After executing the previous step, some files will be generated in the new info folder. Now move all these files to the info_old folder
5.$ sudo rm -rf /var/lib/dpkg/info //Delete the newly created info folder
6.$ sudo mv /var/lib/dpkg/info_old /var/lib/dpkg/info //Rename the previous info folder back to its original name

Finally, if it is an Ubuntu system, you may not be able to insert Chinese characters into the table, and you may not be able to query Chinese characters from the table.

Solution:

Shut down the database service

service mysql stop
~$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add a line character_set_server=utf8 under [mysqld]
Configuration file excerpt:

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
character_set_server=utf8 is this line, it was not there originally, you have to add it manually!
skip-external-locking
#

Copy the above file to /etc/mysql/my.cnf

~$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf

Restart the database service

~$ /etc/init.d/mysql restart

If you can get the following result after checking the character set, it means success.

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

You may also be interested in:

Install Apache and PHP under Linux; Apache+PHP+MySQL configuration strategy
How to correctly restart MySQL in Linux
Graphic tutorial for installing mysql-5.6.4 under Linux
Explain MySQL installation and login method under Linux
Solution to the error "mysql deamon failed to start" in linux
linux mysql error: MYSQL: The server quit without updating PID file
MySQL installation and configuration under Linux Detailed explanation of MySQL configuration parameters
How to import sql files in linux (using command line to transfer mysql database)
A detailed introduction to installing and using MySQL under Linux
MySQL 5.7.13 installation and configuration method graphic tutorial (linux)
Installation and configuration process of linux mysql5.6 version

<<: A complete example of Vue's multi-level jump (page drill-down) function for related pages

>>: How to view the network routing table in Ubuntu