== Preparation: delete residual files & uninstall redundant programs!! = =

• RPM Check whether mysql and Mariadb exist. If they exist, uninstall them.
  • Centos7 is integrated with Mariadb internally, but installing mysql will conflict with mariadb files, so you need to firstUninstall mariadb. The system will prompt you that there is a dependency relationship, and you can forcibly uninstall it

    rpm -qa | grep mariadb
    rpm -e mariadb-libs --nodeps
    Copy the code

    

• Deleting residual Files(Mariadb and remnants of mysql if installed). Log file and var/lib/mysql folder.

  Rm -f /var/log/mysqld.log # Delete the mysql log file. Rm -rf /var/lib/mysql # delete data directory rm /etc/my.cnf rm -rf /usr/share/mysql-8.0Copy the code

• Userdel -r mysql, groupdel mysqldeletemysqlUsers and groups(If the remaining files have been deleted, this step can not be performed, because there will be no files belonging to this filemysqlUsers and groups).
  • Note: mysql automatically creates regular users, calledmysqlTo govern itself. However, the automatic creation is different from the manual creation. The automatic creation does not show up in the user entry when logging in to the system (but can be used to log in,passwd mysqlCommand to log in to the system after changing the password.
  • If mysql has been installed before, uninstall mysql beforemysqlThe user will not be deleted and re-installed if there is one belonging to the previous onemysqlIf the user’s files are not deleted clean, it is likely that the startup will failmysqlThe user and the oldmysqlThe user is not an ID, although the user will be overwritten, but the old file is bound to the oldmysqlUser ID, newmysqlThe user does not have permission to overwrite and use the old file

Installation steps

• < 1 > : mysql official website download RPM package, copy link with thunderbolt to download faster, thunderbolt may add a tar suffix, directly remove it

Centos7 is the package of el7

• < 2 >: Xftp transfer to Linux, usually under /opt/. You can create a new mysql folder and unpack the tar package into mysql/ to get several RPM packages.

  mkdir mysql
  # CentOS6Tar -xvf mysql-8.0.19-1.el6.x86_64. RPM -bundle.tar -c /opt/mysql# CentOS7Tar -xvf mysql-8.0.19-1.el7.x86_64. RPM -bundle.tar -c /opt/mysqlCopy the code

• < 3 >: RPM installation

  cd mysql
  #The RPM command supports wildcard characters. RPM -ivh mysql-*
  #But it's better to install it one at a time. I used wildcard to install mysqld -- there was an error when I initialize, and I installed it again
  cd /opt/mysql/
  Copy the code

  If you run the following install commandwarning ... Header V3 DSA/SHA1 Signature ... NOKEYDon’t worry about it. The reason may beRefer to the link

  #This command is used to install mysql el6 in CentOS6RPM -ivh mysql-community-common-8.0.19-1.el6.x86_64. RPM -ivh mysql-community-libs-8.0.19-1.el6.x86_64. RPM -ivh mysql-community-libs-8.0.19-1.el6.x86_64 Mysql - community - the client - 8.0.19-1. El6. X86_64. RPM RPM - the ivh mysql - community - server - 8.0.19-1. El6. X86_64. RPM
  #This command is used to install mysql el7 in CentOS7RPM -ivh mysql-community-common-8.0.19-1.el7.x86_64. RPM -ivh mysql-community-libs-8.0.19-1.el7.x86_64. RPM -ivh mysql-community-libs-8.0.19-1.el7.x86_64 Mysql - community - the client - 8.0.19-1. El7. X86_64. RPM	#If libaio.so is missing, run 'yum install libaio' and run the following command againRPM - the ivh mysql - community - server - 8.0.19-1. El7. X86_64. RPMCopy the code

• [四](This step is not required) : Perform this step after the installation is completemysqld --initializeInitialization commands (generate log files, assign random passwords to root, create the base database (in the data directory, no files before initialization), etc.
  • Mysql8: mysql8: mysql8: mysql8: mysql8: mysql8: mysql8: mysql8

• [五](This step is not required) : ==When the actual mysql8 is installed (mysql-community-server is installed), it will be automatically createdmysqlUsers and groups==, if no, create it first.

  #Check whether there are mysql users and groups:
  #Method one:
    id mysql
  #Method 2:Cat /etc/passwd Displays the user list. Cat /etc/group Displays the user group listCopy the code

• [六](This step is not required) : Change the data directory permission tomysqlUsers and groups.== actual mysql8 installed, file permissions are assigned by defaultmysqlUsers and groups, as shown in figure ==(If not, executechown -R mysql:mysql /var/lib/mysql)

– [Attach 1] : checkMysql Configuration fileLocation:mysqld --verbose --help |grep -A 1 'Default options' – [attach 2] : View the data directory location: Enter the mysql configuration filevim /etc/my.cnfSee cleardatadir=/var/lib/mysql/

• < 7 >: starting

  # CentOS6
  service mysqld start
  # CentOS7
  systemctl start mysqld
  Copy the code

  • If the startup fails: check the log file to see what errors are reported. The above figure shows the log file location (/var/log/mysqld.log). The corresponding error resolves itself. (There is a problem that is not easy to troubleshoot: because mysql has been installed before, the residual log file or data directory has not been deleted, and the new installation automatically generated by defaultmysqlThe user creates a file that cannot be overwritten and used without these two permissions, so the initialization will fail. You can try it nowrootUser delete, and then initialize to generate a password, and then start)
  • Mysql8 default startup ==

    # CentOS6
    chkconfig mysqld on
    # CentOS7
    systemctl enable mysql
    Copy the code

• Eight > <:mysql -u root -pLog in to the database (==Here, user root is the root user of mysql, not the systemrootThe user. Created at the beginningmysqlUsers and groups are also system, not mysql= =)
  • The password is in the log file and can be executedgrep "password" /var/log/mysqld.logTo view
• 9 > <: The root password must be changed. Otherwise, the operation cannot be performed. Perform:ALTER USER 'root'@'localhost' IDENTIFIED BY 'password ';
  • Note: Since MYSQL5.7, the password security check plug-in (VALIDate_password) has been installed by default. The default password check policy requires that the password must contain: uppercase and lowercase letters, digits, and special symbols, and must be at least 8 characters long. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ERROR.
• [10]: complete,show databasesYou can see the initial database.

Example Add a remote login user

== In this command, user root is the root user of the mysql database, not the root user of the system. Mysql users and groups created at the beginning are also system, not mysql ==

Add links to remote user resources

• By default, only the root user is allowed to log in locally. If you want to connect to mysql on another machine, you must change the root user to allow remote connections or add an account that allows remote connections.
• Change the root remote access permission:
• use mysql; –>select host, user from user;

• View the host of user root in the table. The default value is “localhost”. Only local access is supported and remote access is not allowed.
• Grant all permissions to user root and set remote access:update user set host='%' where user ='root';–>flush privileges;This takes effect only after the permissions are refreshed
• The firewall allows port 3306
  • CentOS6:vim /etc/sysconfig/iptables, add the following content in the red box, and restart the firewallservice iptables restart

– CentOS7: The firewall is not enabled by default. Run the firewalld command to enable itsystemctl start firewalld. Mysql Release commandfirewall-cmd --permanent --add-service=mysqlTo restart the firewallfirewall-cmd --reload

Change the default encoding format

The resources

• The default encoding of mysql8.0 is UTF8MB4. Therefore, you do not need to change the encoding. availableSHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';To view

Personal record

File permission Issues

• Change the data directory owner and group torootUser, the startup will fail.

– It is in the log fileThe reason for failurePermission deniedI think there’s some kind of access problemshell mysqld: File './binlog.index' not found (OS errno 13-permission denied) 2020-02-25t11:59:42.879908z 0 [System] [my-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 9241 2020-02-25t11:59:42.882557z 0 [ERROR] [my-010119] [Server] Aborting 2020-02-25t11:59:42.882687z 0 [System] [my-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server -gPL.

• Change the data directory permissions backmysqlIs successfully started. The UID of the mysql process ismysqlUsers, notrootUser, so the reason why it doesn’t start is because you don’t have access to the data directory? Looking at file permissions, it turns out that this is true. The other group users only have execute permissions, just the data directory owner and all groups dorootwithmysqlIf the user starts it, it can’t read itrootThe user’s file, as shown below:

• In fact, this also reflects the necessity of using an ordinary user to manage the database, which has been mentioned at the beginning of the article:

The specified user starts the service

D /mysqld; /etc/rc.d/init.d/mysqld; / user=root; But in the end, you can’t stop, you can only kill. I don’t know why

• However, when you view the process information, you can see that the process is started by the command, which has –user=mysql

• So let’s change the data directory permissions toroot, and then executeservice mysqld start --user=root “, this can see that success withrootThe user started the mysql database. Procedure

• Try turning off the service again, but it won’t work. In fact, if you’re a little bit more careful, you’ll notice that it’s added--user=rootAfter the option, another mysql process’s start directive is in--user=mysqlI added one on top of that--user=root“Then just kill it. As shown in figure:

Risk test of mysql running on root

Chown -r mysql:mysql /var/lib/mysql == -r :mysql /var/lib/mysql == -r :mysql /var/lib/mysql

• Start mysql as user root and add to /etc/my.cnfsecure_file_priv=/root/ performSELECT "hello" INTO OUTFILE "/root/hello.txt"To see if files can be written to the /root/directory.
  • If you can write files under /root/, you should be able to indicate the risk at the beginning of the article. In fact, a more accurate test should be to hack the mysql database startup user, and then modify the system file, but I do not have this ability…
• Step 1: Change user=mysql to user=root in /etc/rc.d/init.d/mysqld

• Step 2: add in /etc/my.cnfsecure_file_priv=/root/

• Step 3: Start — failed because you do not have permission to /root. ????? I have to userootYes, we do not have permissions for this directory.

• It has been taking a long time, let’s pause the experiment, maybe we will know what is going on later, after all, I am not proficient in Linux.
  • If secure_file_priv=/root/ is not written, the system can be started successfully, and so can the startup userroot. (user=root) (user=root) (user=root) (user=root) (user=rootrootThere may be other things that need to be changed
• == So far, tinkering for half a day, may start failure, executionchown -R mysql:mysql /var/lib/mysqlYou can basically solve ==