Centos7 completely uninstalls MySQL & installs & starts
Uninstall section
1. See what mysql has installed
rpm -qa |grep -i mysql
2. Start the uninstallation, and execute the installed XXX in sequence:
yum remove XXX
3. Check to see if the uninstall is complete
rpm -qa |grep -i mysql
4. Find mysql related directory
find / -name mysql
5. Delete all installed XXX directories
rm -rf XXX
6. Delete / etc / my cnf
rm -rf /etc/my.cnf
7. Delete / var / log / mysqld Log (if this file is not deleted, the newly installed mysql will fail to survive, the new password will fail to log in)
rm -rf /var/log/mysqld.log
Installation chapter
1. Download and install mysql source installation package
# download wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm # Install mysql source yum localinstall mysql57-community-release-el7-11.noarch.rpm
2. Check whether the mysql source is successfully installed
yum repolist enabled | grep "mysql.*-community.*"
3. Install MySQL
yum install -y mysql-community-server
If an error occurs: error: unable to find a match: MySQL community server, do the following: (I don't know the principle)
# Try running first yum module disable mysql # Then try the following command yum install mysql-community-server
Start article
1. Under CentOS 7, the new command to start / close the service is systemctl start|stop
systemctl start mysqld
2. View MySQL status with systemctl status
systemctl status mysqld
3. Set startup
systemctl enable mysqld Reload all modified configuration files # Reload all modified configuration files systemctl daemon-reload
4. Get root login password
After mysql installation is completed, in / var / log / mysqld A default password is generated for root in the log file. Find the default root password in the following way, and then log in to mysql to modify it:
grep 'temporary password' /var/log/mysqld.log
ps: if there is no return, the root password cannot be found. Solution:
# 1 delete the remaining data of the previously installed mysql (this step is very important, and that's the problem) rm -rf /var/lib/mysql # 2 restart mysqld service systemctl restart mysqld # 3. Find the temporary password grep 'temporary password' /var/log/mysqld.log
5. Log in and modify the root local account password
# Sign in mysql -uroot -p # Next, enter the temporary password to log in
# Change the password. I'll change the password here to 'ClimberCoding66` mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ClimberCoding66!';
Note: mysql 5.7 installs the password security check plug-in (validate_password) by default. The default password check policy requires that the password must contain uppercase and lowercase letters, numbers and special symbols, and the length must not be less than 8 digits. Otherwise, error 1819 (HY000) will be prompted: your password does not satisfy the current policy requirements error
Later, you can modify the password through the update set statement
mysql> use mysql;
mysql> update user set password=PASSWORD('ClimberCoding77!') where user='root';
mysql> flush privileges; # Refresh permissions
6. Add remote login user
By default, only the root account is allowed to log in locally. If you want to connect to mysql on other machines, you must add an account that allows remote connection
GRANT ALL PRIVILEGES ON *.* TO 'Climber'@'%' IDENTIFIED BY 'Climber2020!' WITH GRANT OPTION;
7. Set the default encoding to utf8
After mysql is installed, Chinese is not supported by default, and the code needs to be modified.
Modify / etc / my CNF configuration file, add coding configuration under relevant nodes (if not, add it yourself), as follows:
[mysqld] character-set-server=utf8 [client] default-character-set=utf8 [mysql] default-character-set=utf8
8. Restart the mysql service and query the code. You can see that it has been changed
shell> systemctl restart mysqld shell> mysql -uroot -p mysql> show variables like 'character%';
Supplement 1:
When installing with RPM package, the system will not prompt under which folder various files are installed. The following describes the directory of each main file, MySQL server directory and subdirectory, as shown in the following table:
| folder | Folder contents |
|---|---|
| /usr/bin | Client and script (mysqladmin, mysqldump and other commands) |
| /usr/sbin | mysqld server |
| /var/lib/mysql | Log files, socket files, and databases |
| /usr/share/info | Information format manual |
| /usr/share/man | UNIX help page |
| /usr/include/mysql | Header file |
| /usr/lib/mysql | library |
| /usr/share/mysql | Error messages, character sets, installation files, configuration files, etc |
| /etc/rc.d/init.d/ | The MySQL directory of the startup script file can be used to start and stop MySQL services |
Step 7): configure the MySQL service, copy a file with the suffix CNF in the / usr/share/mysql / or / usr/share / folder to the / etc / folder, and rename it my cnf. Use the vi editor to edit my CNF (where are we< my. Detailed explanation of CNF configuration file >The section introduces my The specific meaning of each parameter in CNF configuration file). The command is as follows:
cp /usr/share/mysql/my-large.cnf /etc/my.cnf
vi /etc/my.cnf
The first command can copy and rename, and the second command can edit my cnf.
Note: use vi to enter my After CNF file, press i or a to edit. Press Esc to exit the editing state and enter the command state. If you want to save the modified data, enter: w; if you don't want to save and exit directly, enter: q! Just.
Edit and save my After the CNF file, you must restart the MySQL service so that my The configuration in CNF will work.
Supplement 2:
Modify password policy
In / etc / my CNF file add validate_password_policy configuration, specifying password policy
# 0 (LOW): verify Length # 1 (MEDIUM): verify Length; numeric, lowercase/uppercase, and special characters # 2 (STRONG): verify Length; numeric, lowercase/uppercase, and special characters; dictionary file validate_password_policy=0
Of course, if you do not need a password policy, you can disable:
In / etc / my CNF file add
validate_password = off
Restart effective:
systemctl restart mysqld
The root user of Mysql can only be accessed locally. Here, a remote accessible user is created.
GRANT ALL PRIVILEGES ON *.* TO 'its'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
10. Ignore case
Log in to mysql to view
mysql> show variables like "%case%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | ##0 distinguished 1 indistinguishable +------------------------+-------+ 2 rows in set (0.00 sec)
Modify the configuration file / etc / my CNF add:
# 0: case sensitive, 1: case insensitive lower_case_table_names =1
Effective after restart:
systemctl restart mysqld
11. User rights
When migrating the database to a new server and executing the stored procedure, the following problems occur:
execute command denied to user 'user name'@'%' for routine 'Function name'
Later, it turned out that it was a permission problem. Just change the permission of the corresponding user with the following statement:
GRANT ALL PRIVILEGES ON *.* TO 'user name'@'%' ; FLUSH PRIVILEGES;
Corresponding revoke permission command:
REVOKE ALL PRIVILEGES ON *.* FROM 'user name'@'%' ; FLUSH PRIVILEGES;
12. When running a script:
- ERROR 1067 (42000): Invalid default value for 'FAILD_TIME '(this error will be reported when creating the table if the default value is not set or the flag not null is not set for the sub segment of TIMESTAMP type)
This is because SQL_ No in mode_ ZEROR_ '0000-00-00' is not allowed as a legal date in strict mode
Use the following command to view sql_mode
mysql>show variables like 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
Put no above_ ZERO_ Change the date to the following ALLOW_INVALID_DATES
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
The above settings are temporary settings. After re login, the settings are restored to NO_ZERO_DATE