Of Stack

Brief analysis of CentOS6.8 tutorial for installing MySQL8.0. 18 of RPM mode

  • 2021-12-19 07:04:10
  • OfStack

Today, record how to install MySQL 8.0. 18 on an CentOS 6.8 server, and cut to the topic.

1. Uninstall the MySQL that comes with CentOS 6.8

First, uninstall the MySQL that comes with the CentOS 6.8 server, and enter the following command on the command line to view the MySQL that comes with the CentOS 6.8 server. 


[root@binghe151 src]# rpm -qa | grep -i mysql    
mysql-libs-5.1.73-7.el6.x86_64

As you can see, mysql-libs-5. 1.73-7.el6.x86_64 is installed by default on the CentOS 6.8 server, and then uninstall mysql-libs-5. 1.73-7.el6.x86_64, as shown below. 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#

Look again at the MySQL that comes with the CentOS 6.8 server, as shown below. 


[root@binghe151 src]# rpm -qa | grep -i mysql
[root@binghe151 src]#

Explain that MySQL, which comes with CentOS 6.8 server, was successfully uninstalled.

2. Install the MySQL dependency environment

Before installing MySQL, you need to install the underlying environment that MySQL depends on, as shown below. 


yum -y install wget gcc-c++ ncurses ncurses-devel cmake make perl bison openssl openssl-devel gcc* libxml2 libxml2-devel curl-devel libjpeg* libpng* freetype* make gcc-c++ cmake bison-devel ncurses-devel bison perl perl-devel perl perl-devel net-tools* numactl*

3. Add an mysql user

Add an mysql user by executing the following command on the command line. 


groupadd mysql
useradd -g mysql mysql

4. Download and install the RPM installation package for MySQL

First, download the RPM installation package for MySQL 8.0. 18 by executing the following command from the command line of the CentOS 6.8 server. 


wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-1.el6.x86_64.rpm-bundle.tar

Next, look at the downloaded RPM installation package for MySQL 8.0. 18, as follows 


[root@binghe151 src]# ll
total 744876
-rw-r--r--. 1 root root 762746880 Nov 18 14:32 mysql-8.0.18-1.el6.x86_64.rpm-bundle.tar

Next, unzip the downloaded installation package, as shown below. 


[root@binghe151 src]# tar xvf mysql-8.0.18-1.el6.x86_64.rpm-bundle.tar 
mysql-community-client-8.0.18-1.el6.x86_64.rpm
mysql-community-test-8.0.18-1.el6.x86_64.rpm
mysql-community-server-8.0.18-1.el6.x86_64.rpm
mysql-community-libs-compat-8.0.18-1.el6.x86_64.rpm
mysql-community-common-8.0.18-1.el6.x86_64.rpm
mysql-community-devel-8.0.18-1.el6.x86_64.rpm
mysql-community-libs-8.0.18-1.el6.x86_64.rpm

Next, install it in sequence 


mysql-community-common-8.0.18-1.el6.x86_64.rpm
mysql-community-libs-8.0.18-1.el6.x86_64.rpm
mysql-community-client-8.0.18-1.el6.x86_64.rpm
mysql-community-server-8.0.18-1.el6.x86_64.rpm

When installing, it is necessary to install in strict accordance with the above order.

First, install mysql-community-common-8.0.18-1.el6.x86_64.rpm , as shown below. 


[root@binghe151 src]# rpm -ivh mysql-community-common-8.0.18-1.el6.x86_64.rpm
warning: mysql-community-common-8.0.18-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...    ########################################### [100%]
 1:mysql-community-common ########################################### [100%]

Next, install mysql-community-libs-8. 0.18-1.el6.x86_64. rpm as shown below. 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#
0

Next, install mysql-community-client-8.0.18-1.el6.x86_64.rpm , as shown below. 


[root@binghe151 src]# rpm -ivh mysql-community-client-8.0.18-1.el6.x86_64.rpm
warning: mysql-community-client-8.0.18-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...    ########################################### [100%]
 1:mysql-community-client ########################################### [100%]

Finally, install mysql-community-server-8. 0.18-1.el6.x86_64. rpm as shown below. 


[root@binghe151 src]# rpm -ivh mysql-community-server-8.0.18-1.el6.x86_64.rpm
warning: mysql-community-server-8.0.18-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...    ########################################### [100%]
 1:mysql-community-server ########################################### [100%]

If you report an error while installing mysql-community-server-8. 0.18-1.el6.x86_64. rpm, as shown below. 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#
3

As you can see, the libnuma library is missing, so execute the following command to install libnuma.

yum install numactl* -y

After executing the command, install mysql-community-server-8. 0.18-1.el6.x86_64. rpm again.

5. Initialize MySQL data

First of all, let's talk about the official restrictions of MySQL on running MySQL.

In MySQL 8. x, initializing data requires ensuring that the database directories and files are owned by the mysql login account. If you are running the mysqld service as root, you need to confirm 1 and execute the following command with the--user option: 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#
4

If you are logging in as an mysql account and executing the program, you can remove the--user option from the command, as shown below. 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#
5

From the above MySQL official restrictions on the operation of MySQL, it can be concluded.

If you are logged into the CentOS 6.8 server as an root user, you can execute the following command. 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#
6

If you are logged into the CentOS 6.8 server as an mysql user, you can execute the following command. 


mysqld --initialize
mysqld --initialize-insecure

Because I am logging in to the CentOS 6.8 server using the root account here, when initializing the database, I execute the following command. 


mkdir -p /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

Note: Here, when I execute the mysqld-initialize command, I specify the installation directory of MySQL. During the installation of MySQL, it is recommended to specify the installation directory of MySQL. Initialization fails when the data directory exists under the/usr/local/mysql, and you must ensure that there is no data directory under the/usr/local/mysql.

6. Start MySQL

Start the MySQL service by entering the following command from the CentOS 6.8 server command line. 


[root@binghe151 src]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps 
[root@binghe151 src]#
9

When you start MySQL, a temporary login password is generated for the root account of MySQL, which can be viewed in the/var/log/mysqld. log file. Use the vim editor to open the/var/log/mysqld. log file, as shown below. 


vim /var/log/mysqld.log 
2019-11-18T08:16:08.162464Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.18) initializing of server in progress as process 2111
2019-11-18T08:16:12.451542Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: -8cagKkdK#5G
2019-11-18T08:17:13.269505Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 2378
2019-11-18T08:17:16.568836Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-11-18T08:17:16.642494Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2019-11-18T08:17:16.652000Z 7 [Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instea
d'
2019-11-18T08:17:16.801986Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

Find the following 1 line of code. 


2019-11-18T08:16:12.451542Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: -8cagKkdK#5G

As you can see, the temporary login password generated when starting MySQL is-8cagKkdK # 5G.

7. Log in to MySQL

Log in to MySQL with a temporary password, as shown below. 


[root@binghe151 src]# mysql -uroot -p-8cagKkdK#5G
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18
Copyright (c) 2000, 2019, 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>

At this point, an SQL statement is executed on the MySQL command line to report an error and require the password to be reset, as shown below. 


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

Next, change the root account password for MySQL, as shown below. 


mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.03 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Next, exit MySQL by entering the exit command on the MySQL command line. Re-log in to MySQL with the modified root password, as shown below. 


mysql> exit
Bye
[root@binghe151 src]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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>

Description: root account password of MySQL was successfully modified.

Next, execute the SQL statement again on the MySQL command line, as shown below. 


mysql> SHOW DATABASES;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| mysql    |
| performance_schema |
| sys    |
+--------------------+
4 rows in set (0.36 sec)
mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>

As you can see, after modifying the root account password of MySQL, the SQL statement can be executed correctly on the MySQL command line.

8. Default directory for installing MySQL using rpm

1. Database directory

/var/lib/mysql/

2. Configuration file

/usr/share/mysql(mysql.server命令及配置文件)

3. Related commands

/usr/bin(mysqladmin mysqldump等命令)

4. Start the script

/etc/rc.d/init.d/(启动脚本文件mysql的目录)

9. Configure the my. cnf file

MySQL 8.0 does not have my. cnf by default. You can manually create the my. cnf file under the/etc directory, if you initialize the data with the command specifying the MySQL directory as follows. 


mkdir -p /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
则可以将my.cnf文件配置成如下所示。
[client]
port = 3306
#根据实际情况调整mysql.sock配置
socket = /tmp/mysql.sock
[mysqld]
#Mysql服务的唯1编号 每个mysql服务Id需唯1
server-id = 1
#服务端口号 默认3306
port = 3306
#mysql安装根目录
basedir = /usr/local/mysql
#mysql数据文件所在位置
datadir = /usr/local/mysql/data
#pid
pid-file = /usr/local/mysql/mysql.pid
#设置socke文件所在目录
socket = /tmp/mysql.sock
#设置临时目录
tmpdir = /tmp
# 用户
user = mysql
# 允许访问的IP网段
bind-address = 0.0.0.0
# 跳过密码登录
#skip-grant-tables
#主要用于MyISAM存储引擎,如果多台服务器连接1个数据库则建议注释下面内容
skip-external-locking
#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = READ-COMMITTED
#数据库默认字符集,主流字符集支持1些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
#数据库字符集对应1些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1
#最大连接数
max_connections = 400
#最大错误连接数
max_connect_errors = 1000
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
#MySQL连接闲置超过1定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728
#禁用mysql的缓存查询结果集功能
#后期根据业务情况测试决定是否开启
#大部分情况下关闭下面两项
query_cache_size = 0
query_cache_type = 0
#数据库错误日志文件
log_error = error.log
#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log
#检查未使用到索引的sql
log_queries_not_using_indexes = 1
#针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5
#作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1
#慢查询执行的秒数，必须达到此值可被记录
long_query_time = 8
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 5

Summarize

Related articles: