mysql 1677_mysql之数据库主从复制配置报错1677

mysql之数据库某表字符集和数据库字符集不一致导致主从复制配置报错1677

mysql数据库中某张表字符集是utf8而my.cnf设置的数据库的字符集是utf8mb4,导致在配置主从复制时，报如下错误：

Last_Errno: 1677

Last_Error: Column 1 of table 'novel.novel_mp_custom' cannot be converted from type 'tinyint' to type 'int(5)'

原因：

表字符集和数据库字符集不一致导致的

解决过程如下：

主库79查看数据库字符集和报错表的字符集:

mysql> show create database novel\G

*************************** 1. row ***************************

Database: novel

Create Database: CREATE DATABASE `novel` /*!40100 DEFAULT CHARACTER SET utf8mb4 */

mysql> show create table novel_mp_custom\G

*************************** 1. row ***************************

Table: novel_mp_custom

Create Table: CREATE TABLE `novel_mp_custom` (

`id` int(5) NOT NULL AUTO_INCREMENT COMMENT '客服消息自增ID',

`push_num` int(6) NOT NULL DEFAULT '0' COMMENT '成功发送次数',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8 COMMENT='微信客服消息'

1 row in set (0.00 sec)

修改主库79机器上表字符集为utf8mb4:

mysql> alter table `novel_mp_custom` convert to character set utf8mb4;

Query OK, 61 rows affected (0.01 sec)

Records: 61 Duplicates: 0 Warnings: 0

重置79机器上主master信息:

reset master

重新授权同步账户：

mysql> grant replication slave on *.* to novelrep@'10.80.0.2' identified by 'rep@1111'; flush privileges;

Query OK, 0 rows affected, 1 warning (0.00 sec)

79机器上重新dump数据：

mysqldump -uroot -p'5Z$AJ$Jt5ert321' -A -B -F --master-data=2 --single-transaction --events >/root/222.novel.sql

scp -r -i shuosir_key -P 12089 222.novel.sql root@10.80.0.2:/root/

5机器上操作：

mysql>stop slave;reset slave all;

mysql> source /root/333.novel.sql;

CHANGE MASTER TO

MASTER_HOST='10.80.0.1',

MASTER_PORT=3306,

MASTER_USER='novelrep',

MASTER_PASSWORD='rep@1111';start slave;show slave status\G

到处主从复制报错解决