Tutorial on installing and configuring MySQL under Linux

mysqlOne of the most popular relational databases, currently owned by Oracle. Due to its small size, fast speed, low total cost of ownership, and open source code, So it is our first choice for daily development. Let's take a look at how to install and configure MySQL under Linux

System: Ubuntu 16.04LTS

1\Official website download mysql-5.7.18 -linux-glibc2.5-x86_64.tar.gz

2\Create workgroup:

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

3\Create 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 below mysql and its subfolders. After modification, you can Use ll to view permissions

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

6\Modify (or create)/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

Remember the initial password

Many problems may arise 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 package

Solution:

sudo apt-get install libaio-dev

It may also be that the previous steps were incorrect, resulting in insufficient permissions to operate the data file, etc. Follow the steps Come on, install whatever is missing (there are prompts). The initialization should be successful when you get here. Those Warnings are worth noting that Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. You can do it if you have time. Check what's going on. Ignoring this has no effect.

8\Don't rush to start, it can't start now. Execute the code and change the files except the data folder under mysql to root permissions

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

9\Startup

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 up startup

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 / /Now rename the info folder

2.$ sudo mkdir /var/lib/dpkg/info //Create a new info folder
3.$ sudo apt-get up
date, $ apt-get -f install //Repair dependency tree
4.$ sudo mv /var/lib/dpkg/info/* /var/lib/dpkg/info_old //Complete the previous step Some files will be generated in the new info folder later. Now move all these files to the info_old folder
5.$ sudo rm -rf /var/lib/dpkg/info //Move your new info file Delete the folder
6.$ sudo mv /var/lib/dpkg/info_old /var/lib/dpkg/info //Change the previous info folder back to its name

Finally, if it is Ubuntu The system may be unable to insert Chinese characters into the table and cannot query

Chinese characters from the table..Solution:

Close 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 selection:

[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 就是这一行,原来是没有的,要手动添加!
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 database service

~$ /etc/init.d/mysql restart

If you check the

character set

and get the following results, 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)

The above is the detailed content of Tutorial on installing and configuring MySQL under Linux. For more information, please follow other related articles on the PHP Chinese website!