Steps to install MySQL 5.7 in binary mode and optimize the system under Linux

This article mainly introduces the installation/startup/shutdown process of MySQL binary package.

Some people may ask why you should choose binary installation?

In fact, the answer is very simple. All functions have been configured in the official version, and we can use them easily.

There are four official MySQL versions: GA version, DMR version, RC version and Beta version. Generally speaking, the production environment or the test environment

Select the GA version (generally available version, tested with bug fixes).

Download address: https://dev.mysql.com/downloads/mysql/

After the download is complete, you can check the MD5. I downloaded the version here before, and here I will demonstrate the use of this command (md5sum file name)

[root@tse2 downloads]# md5sum mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz 
d903d3dbf235b74059a4b3e216c71161 mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz 

1. System environment detection before installation

No matter which version of MySQL is installed, in order to smoothly install the MySQL database and do a good job of optimizing the database later, it is very necessary to perform Linux system detection in the early stage.

1.1. selinux and iptables need to be disabled, set SELINUX=disabled, and restart the server to take effect.

I don't have iptables here, I use firewall so I don't need to turn off iptables

1.2. The default I/O scheduling system is cfq mode. It is strongly recommended to use deadline mode.

View the I/O scheduling file:

[root@tse2 downloads]# cat /sys/block/sda/queue/scheduler 
noop [deadline] cfq

1.3. Setting up the swap partition

The size of the swpapiness value has a great impact on how the swap partition is used.

It has two extreme values, 0 and 100. 0 means using the physical memory to the maximum extent possible, and then using the swap partition. This behavior is likely to cause system memory overflow and OOM errors, which may cause MySQL to be accidentally killed, so it needs to be set with caution.

100 means actively using the swap partition and moving the data in the memory to the swap partition in a timely manner (not recommended). It is recommended that you do not allocate swap, or allocating 4GB of space is sufficient.

How to view the swappiness file?

[root@tse2 grub2]# cat /proc/sys/vm/swappiness
[root@tse2 grub2]# sysctl -a|grep swap
sysctl: reading key "net.ipv6.conf.all.stable_secret"
sysctl: reading key "net.ipv6.conf.default.stable_secret"
sysctl: reading key "net.ipv6.conf.ens192.stable_secret"
sysctl: reading key "net.ipv6.conf.lo.stable_secret"
vm.swappiness = 30

To change the swappiness value, edit /etc/sysctl.conf and add the value of vm.swappiness.

1.4. File system selection

It is recommended to use the xfs file system here. Compared with ext4, it is easier to manage, supports dynamic expansion, and is also easy to delete files.

1.5. Operating system limitations

Let's first check some of the current operating system restrictions, use ulimit -a to view:

The two most important parameters are marked here, one is called open files and the other is called max user processes.

If the open files are not set properly, and the current server has too many connections or too many tables, it is possible that the table cannot be opened or accessed.

By default, the maximum number of handles in Linux is 1024, which means that a single process can access up to 1024 file handles. If the number exceeds the default value, a file handle limit error "too many open files" will occur.

Purpose of the max user processes parameter: Sometimes we may run many instances, but find that new connections cannot be created, and a "resource temporarily unavailable" error is reported, indicating that there are not enough resources.

In order to prevent the above two error situations, we can modify the system's soft and hard limits. Edit /etc/security/limits.conf and add relevant content to the restrictions. Remember that after changing the content, you need to restart the operating system for the changes to take effect.

[root@tse2 grub2]# vim /etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535 

1.6. numa needs to be closed

Simply put, turning off the NUMA function can better allocate memory and there is no need to use swap to obtain memory.

Because experienced system administrators and DBAs know how disgusting the degradation of database performance caused by using swap is.

The shutdown methods include shutting down in BIOS, operating system, or during database startup.

[root@tse2 bin]# numa --interleave=all /mysql/app/bin/mysqld_safe -defaults-file=/etc/my.conf &

2. MySQL 5.7 version installation process

After checking the operating system environment, we enter the MySQL installation phase, which can be summarized as a "three-step + one-step" approach.

2.1. Part One

Create a MySQL user and specify the user group where MySQL is located. The command is as follows:

[root@tse2 /]# groupadd mysql
[root@tse2 /]# useradd -g mysql mysql -s /sbin/nologin

I usually put the home directory (basedir) of the software package under /data/downloads:

[root@tse2 downloads]# pwd
/data/downloads

You need to unzip the MySQL package. The command is as follows:

[root@tse2 downloads]# tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz

You need to authorize the MySQL directory:

[root@tse2 downloads]# chown -R mysql:mysql /mysql

2.2 Part 2

Create the data directory (datadir) of the MySQL database. You can choose to create it under /mysql/:

[root@tse2 downloads]# mkdir -p /mysql/data

Also authorize the data directory:

[root@tse2 downloads]# chown -R mysql:mysql /mysql/data

2.3 Part III

Since it is a binary installation method, the database configuration file here needs to be configured by yourself. This completes the last step.

my.cnf configuration file download address: https://files.cnblogs.com/files/Sungeek/Tsemy.7z

Password: tse

2.4. The final step

The mysqld command initializes the database:

[root@tse2 mysql]# /mysql/app/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/mysql/app --datadir=/mysql/data/ --user=mysql -initialize

Note: If you add the --initialize parameter during the initialization process, a temporary database initialization password will be generated.

It is recorded in log-error (error log). If the --initialize-insecure parameter is added, it means no password entry. It is recommended to use the method of generating an initialization password!

The process of starting the database:

[root@tse2 mysql]# /mysql/app/bin/mysqld_safe --defaults-file=/etc/my.cnf &

After the database is successfully started, the initialization password to enter the database will be under /mysql/logs/error.log:

[root@tse2 logs]# cat /mysql/logs/error.log |grep password

After entering the database using the initialization password, you need to change the database root password and set it to never expire:

/mysql/app/bin/mysql -uroot -p
mysql>SET PASSWORD = '123456';
mysql>ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
mysql>flush privileges

The above is the installation and startup method of MySQL version 5.7 and some optimization instructions for system optimization.

Summarize

The above is the steps for installing MySQL 5.7 version and system optimization in binary mode under Linux introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!