Tag Archives: mysql5.7

[Solved] Backup mysql8.0 with the mysqldump of mysql5.7 Error

Use mysql5 The mysqldump command of version 7 backs up mysql8 An error will be reported when using the 0 version of the database:

mysqldump: Couldn't execute 'SET SQL_QUOTE_SHOW_CREATE=1/*!40102 ,SQL_MODE=concat(@@sql_mode, _utf8 ',NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS') */': Variable 'sql_mode' can't be set to the value of 'NO_KEY_OPTIONS' (1231)

The solution is as follows:

The first way is to upgrade MySQL 5 Version 7 database to MySQL 8.0 0, and then use the same command to back up

The second way is not to use MySQL 5 Version 7 database comes with the mysqldump command, but find a mysql8 Upload the mysqldump command file of version 0 database to the specified path of the host, and then use mysqldump under this path for backup operation

The second method is recommended

View the current mysqldump version command

# mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.35, for Linux (x86_64)

# mysqldump --version
mysqldump  Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)

[Solved] Error 1054 (42s22) unknown column ‘password’ in ‘field list’ when updating the password of mysql5.7 or above

Error 1054 (42s22) unknown column ‘password’ in ‘field list’ is reported after execution

The reason for the error is: There is no password field in the MySQL database in version 5.7, and the password field is changed to authentication_string

>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> use mysql;
Database changed
mysql> select User from user;  #This is the query user command
+-----------+
| User      |
+-----------+
| *******  |
| mysql.sys |
| root      |
+-----------+
3 rows in set (0.00 sec)

mysql> update user set password=password("*******") where user="*******";  #Change password error
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> update mysql.user set authentication_string=password('*******') where user='*******';  #Change password error
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;  #Effective immediately
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

n>mysql -u ******* -p #Login with this user was successful.
Enter password: ********
…………………………
mysql>

Mysql5.7 service startup error on Windows

After installation, start the service net start mysql, which cannot be started. The log reports an error. Some system tables, mysql.user and other tables are missing

Solution:

Execute under Bin: mysqld — initialize execute — user = MySQL

After execution, generate the directory C: \ program files \ MySQL \ MySQL server 5.7 \ data. Create a new root user without a password

mysqladmin  – u   root   password   123456, specify the root user password

 

 

ps:

Net start MySQL
net stop MySQL
mysqld — install, install services on windows, mysqld — remove remove remove services

Mysqld — console start printing log
mysqladmin – uroot shutdown stop service

 

Here is the most important thing to pay attention to. When installing MySQL service, you must switch to the bin directory of MySQL installation directory, no matter whether you configure environment variables or not, otherwise the above error will be reported when starting the service after installation

MySQL 5.7 installation, mysql. Plugin ‘doesn’t exist error resolution

Today, the installation free version of MySQL was deployed, and the problem of table ‘mysql. Plugin’ doesn’t exist appeared. After a long time, I finally found a solution on the Internet. Now I’ll sort it out and share it with you

System environment: win10 64 bit

MySQL version: mysql-5.7.17-winx64

The steps of deployment are as follows:

1: Modify the environment variable path, and add the value of C: \ “program files \” mysql-5.7.17-winx64 \ “bin

2: Modify my-default.ini and rename it to my.ini. Modify the contents of the file

# basedir = C:\Program Files\mysql-5.7.17-winX64
# datadir = C:\Program Files\mysql-5.7.17-winX64\data

Create an empty data folder in the home directory “C:// program files/mysql-5.7.17-winx64”

3: The administrator runs CMD, and enters: mysqld – install to show that the installation is successful; Then enter: Net start Mysql to prompt that the service failed to start

At this point: go to the data folder to see five files, open the file with suffix. Err, and prompt: table ‘mysql. Plugin’ doesn’t exist

The solution to this problem is as follows:

1. Copy my.ini to the folder

2. In the CMD to the directory, and then enter mysqld — initialize — user = MySQL — console to execute this command to generate the library

3. Start the service net start mysql

4. Enter the MySQL command: MySQL – uroot – P, and enter the password just given

5. Input set password = password (‘root ‘) to modify the login password of root