MySQL8. 0 upgraded stepping on the pit Adventure

Source of the article ： Learn through http://www.bdgxy.com/

Preface

I've been busy lately Fighting Project , So the pen is a little lax , You shouldn't have .

Just met the need from MySQL5.7.33 Upgrade to MySQL8.0.x The needs of , So record the whole upgrade process , Step over the pit .

Background outline ： Local docker Used in containers MySQL yes 5.7.33（ The mirror system uses ubuntu16.04）, And the new project uses... Online MySQL8.0. Exported from online sql file （ Table structure + data ） There is a lower version of MySQL Character encoding that will not be used , An error will be reported as soon as you import , The character set error encountered is as follows ：

 - Unknown collation: 'utf8mb4_0900_ai_ci'

After a search , I also calmed down , There are two ways of thinking , One is what I call the "cover your ears and steal your bells" solution , One is a radical cure .

The ostrich , seeing the name of a thing one thinks of its function , Don't solve the essential problem , Instead, it solves the problem of error reporting by replacing character sets in batches , That is, all the in the file utf8mb4_0900_ai_ci Replace with ：utf8_general_ci.

The second is to keep in touch with the online MySQL Versions, , This will support the character set .

So I used the second method , Upgrade my docker In container MySQL, By the way, you can also package a new image .

The upgrade process is also a little bumpy , But the logic is clear .

1. First completely uninstall the existing version MySQL.

You can use the following command ：

# Delete mysql Data files for 
sudo rm -R /var/lib/mysql/
# Delete mysql Configuration file for 

sudo rm -R /etc/mysql/

# Auto uninstall mysql（ Include server and client）
sudo apt-get autoremove mysql* --purge
sudo apt-get remove apparmor

Remember to delete the configuration file , Because it may not be compatible .

2. download deb And in accordance with the MySQL8.x.

You can download it on the official website , The official website address is https://dev.mysql.com/repo/apt

We choose the latest deb download , The website is ：
https://dev.mysql.com/get/mysql-apt-config_0.8.20-1_all.deb. Use... After command wget Command to download , as follows ：

wget https://dev.mysql.com/get/mysql-apt-config_0.8.20-1_all.deb

After downloading, use dpkg Command and update apt Source , Then you can install , The complete command is as follows ：

dpkg -i mysql-apt-config_0.8.20-1_all.deb
apt-get update
apt-get install -y mysql-server

During the last command execution , It will let you enter root Password and some basic configuration , Then you can complete the installation .

Because it is ubuntu It's installed inside MySQL, So the startup mode is ：

service mysql start

You can use the command to see how the process is running , It is similar in normal time. If you need to output ：

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock

3. Create users and configure permissions .

MySQL8.0.x During the actual use, it is found that the user authorization is a little different from the previous version , There are three steps ：

1. Create user .
2. Assign permissions .
3. Refresh the permissions .
   

Concrete sql The statement is as follows , The order is also strict ：

create user 'freephp'@'%' identified by '176serTTc8Cg';

grant all privileges on dev_database.* to ‘freephp’@‘%’ with grant option;

flush privileges;

above sql Explain it. , The first sentence is to create a project called freephp Users of , The password for 176serTTc8Cg,% Represents that any host can be connected .

The second sentence means to dev_database Give all permissions to the user freephp.

The third sentence is to refresh permissions immediately , Make the newly created permission effective .

Since then, the adventure has been completed ,MySQL8.0 New features need to be explored , Chat next time .

summary

This is about MySQL8.0 This is the article on upgrading stepping on the pit , More about MySQL8.0 To upgrade the content of stepping on the pit, please search the rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！