MySQL5.7升级到MySQL8记录

1、安装的时候选择密码验证方式的时候请选择兼容5.x版本的mysql_native_password

8.0支持的caching_sha2_password 用 Navicat及php测试发现连接不上

2、用户自定义函数报错

    This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法

    mysql>set global log_bin_trust_function_creators=1;

3、将5.x版本的字符集转为8.0版本默认的utf8mb4_0900_ai_ci

5.7使用的默认为utf8mb4_unicode_ci

• 创建转换的存储过程

    CREATE DEFINER=`root`@`localhost` PROCEDURE `alter_table_character`()
    begin
        declare f_name varchar(100); 
        declare b int default 0;    /*是否达到记录的末尾控制变量*/
    		-- 注意修改下面的数据库名称 populac
        declare table_name cursor for SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = 'populac' and TABLE_NAME like '%' AND TABLE_COLLATION = 'utf8mb4_unicode_ci';    
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
       
        OPEN table_name;
        REPEAT
        FETCH table_name INTO f_name; /*获取第一条记录*/
    				SET @STMT :=CONCAT("ALTER TABLE ",f_name," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;");   
    			PREPARE STMT FROM @STMT;   
        EXECUTE STMT;  
    -- INSERT into TestTable(name) VALUES (f_name);
           -- ALTER TABLE f_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 
        UNTIL b = 1
    		END REPEAT;
        close table_name;        
    end

• 调用存储过程

    call alter_table_character();

4 5.7升级到8.0

下载mysql-apt-config_0.8.13-1_all.deb：https://dev.mysql.com/downloads/repo/apt/

cd /var/tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
sudo apt-get update

安装新版的MySQL

service mysql stop  # 先停了MySQL service
sudo apt-get install mysql-server
sudo service mysql start  # 开启MySQL service

最后检查下升级后的版本

mysql -V