MySQL installation (4, 5, 6 can be omitted)

Declaration: CentOS version is 7.6, MySQL version installed is 8.0.17

1. First, uninstall mysql, including MariaDB.

rpm -pa | grep mysql  #Use 'rm -rf file name' to delete the found results, and skip if not
rpm -pa | grep mariadb  #Use 'rm -rf file name' to delete the found results, and skip if not
find / -name mysql  #Find and delete related folders, skip if not (same as above)
find / -name mariadb  #Find and delete related folders, skip if not (same as above)

2. Back up the default repo source of centOS, and download Alibaba cloud or Netease's repo source to replace the default source.

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

cd /etc/yum.repos.d/
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

3. Clean up yum and create yum cache.

sudo yum clean all
sudo yum makecache

4. View mysql related content in the software source warehouse

yum repolist | grep mysql

5. Check whether the corresponding version of mysql is enabled

cat /etc/yum.repos.d/mysql-community

6. Set the version to be installed to enabled state (mysql community version 8.0 is installed here)

yum-config-manager --enable mysql80-comminity

7. Perform installation

yum install mysql-community-server.x86_64

8. Check the running status of mysql. It is not started by default when just installed.

# Check the running status of MySQL service. active means started, inactive means not started, and failed means failed to start.
systemctl status mysqld.service

# Start MySQL service
systemctl start mysqld.service

# Stop MySQL service
systemctl stop mysqld.service

# Restart MySQL service
systemctl restart mysqld.service

9. View initial password

The newly installed version of mysql will automatically generate a temporary password, which will be saved in ` / etc/log/mysqld.log`

cat /var/log/mysqld.log | grep "password"

10. Log in with the initial password

Copy the password of the previous step, enter 'mysql-uroot-p password', or press enter without entering the password first, and paste the password at the prompt (the password will not be displayed, just paste once).

11. Change initial password

show databases;
use mysql;
# For example, change the password to NewPassword!, for security, try to include uppercase and lowercase alphanumeric symbols.
alter 'user'@'localhost' identified by 'NewPassword!';  

12. Modify access rights to enable remote connection

update user set Host='%' where User='root' and Host='localhost';

13. Refresh permission

flush privileges;

14. New user

create user username identified by 'password'; 

#For example, create a user and specify the accessible hosts, database tables and corresponding permissions.
create user user name @ 'hostname' identified by 'password';
grant select, update, create, delete on database name. Table name to user name;

15. Give permission. Refresh permission will take effect.

grant select on Database name.Table name  to user;   # All can be used for all permissions

flush privileges;

MySQL backup

Backup: data table structure + data

mysqdump -u root db1 > db1.sql -p;

Backup: data table structure

mysqdump -u root -d db1 > db1.sql -p;

Import existing data to a database

1. Create a new database first
   create database db10;

2. Import the existing database file into the db10 database

   mysqdump -u root -d db10 < db1.sql -p;

= = attention = =

==If the database reports an error:==

=="Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details."==

terms of settlement:

Database initialization:

rm -rf /var/log/mysql.log

rm -rf /var/ib/mysql

This article combines the problems encountered in the actual operation and the solutions collected on the network. Thank you for your help!