MySQL database master-slave replication configuration error 1677

Source: Internet
Author: User
Tags create database


MySQL database A table character set and database character set inconsistency cause master-slave replication configuration error 1677
A table character set in the MySQL database is UTF8, and the MY.CNF setting of the database is UTF8MB4, resulting in the following error when configuring Master-slave replication




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


Reason:
Table character set and database character set inconsistency result in
The resolution process is as follows:



Main Library 79 View the character set of the database character set and error table:


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‘ Customer message increment ID ’,
   `push_num` int (6) NOT NULL DEFAULT‘ 0 ’COMMENT‘ Number of successful transmissions ’,
   PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 70 DEFAULT CHARSET = utf8 COMMENT = ‘Customer Service’
1 row in set (0.00 sec)


Modify the table character set on the main library 79 machine to 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


Reset the master Master information on the 79 machine:
Reset Master
To re-authorize the Sync account:


mysql> grant replication slave on *.* to [email protected]‘10.80.0.2‘ identified by ‘[email protected]‘; flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)


79 re-dump data on the machine:


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 [email protected]:/root/


5 operation on the machine:


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=‘[email protected]‘;start slave;show slave status\G


Everywhere the master-slave replication error Resolution



MySQL database master-slave replication configuration error 1677


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.