MySQL主从同步故障排除-优快云博客

mysql-1366

错误信息：

mysql 主从同步，突然发现从库同步失败，报错 Last_Errno: 1366 Last_Error: Error 'Incorrect string value: '\xE7\x8E\x8B1' for column 'name' at row 1' on query. Default database: 'live'. Query: 'INSERT INTO user (name,token,id) VALUES ('王1','111',1)'

根据错误提示找到对应的表。

查看表的编码用如下命令：

查看表创建的语句
show create table white_list;

查看表的所有列
desc white_list;

查看表的所有列的详细信息
show full columns from white_list;

发现其中的file_name 列的 collation的值是 latin1 说明该字段编码为latin1 不支持汉字从而导致上边的主从同步。

对比主从数据库的编码,使用如下命令：

show variables like 'character%';

发现主库

从库如下

修改从库的特定表中的特定column的编码：

alter table white_list change file_name  file_name varchar(100)  character set utf8mb4  not null;

其中white_list是表的名字， file_name 是列的名字 file_name varchar(100) 指要修改列的属性。 utf8mb4 从原来的latin1 修改为utf8mb4编码。

修改完后，问题解决。

还发现数据库的character_set_database 也变成了utf8mb4。

修改表的编码方式：

该命令用于将表test的编码方式改为utf8:

alter table  test  default character set utf8;

规避方法：

安装设置从库时，需要严格按照主库的mysql版本及配置，配置从库。还要使用主库的建库建表语言在从库上进行创建等。

需要停止数据库修改的方法参考这里，不过此方法不太适合此次修复。

https://www.cnblogs.com/Jerrycjc/p/5151223.html

mysql-主从复制

背景：

脚本或编译安装的mysql，配置默认配置基本相同。现在需要把主库数据库导出来，导入从库，并设置从库向主库同步。

检查：

检查两个数据库是否能够正常启动，检查数据库名称及表是否相同，建表语言是否相同，默认编码是否相同等准备工作。

1. master 数据库必须开启binlog功能。整个复制过程实际上就是slave从master端获取binlog日志，然后再在slave上以相同顺序执行获取的binlog日志中所记录的各种sql操作。

2. master 建立同步账号。

3. slave 配置master info

开启binlog的方法：

修改my.cnf 修改后需要重新启动mysql

[mysql]
server-id = 1    ##用于同步的每台机器或实例server-id都不能相同，可以设置为IP的最后一段。
log-bin = mysql-bin  ##log-bin文件名称后边会追加.0001等序号。

检查方法：

#
show variables like 'server_id';
#

show variables like 'log_bin';

添加主从复制账号的方法：

建立账号：

# 授权 replication slave 权限访问*.*  对user repl来访地址 10.51.70.%，通过密码repl验证。
grant replication slave on *.* to 'repl'@'10.51.70.%' identified by 'repl';
# 刷新权限使得授权的权限生效
flush privileges;

查看方法：

# 查表查看
select user,host,Repl_slave_priv from mysql.user;
# 查看刚创建的是否是REPLICATION SLAVE 权限
show grants for repl@'10.51.70.%';

数据库导出并为slave设置master info（导出过程中会默认锁表只读）：

导出主库数据：

# --master-data= 1(change master to 格式添加到sql文件中) ，2（会以注释的方式添加到sql文件中）该命令会自动开启锁表只读。数据库比较小或者夜间适合。
mysqldump -uroot -prepl --master-data=1 database1  >20171121.sql

# 如果数据库比较大可以进行压缩
mysqldump -uroot -prepl --master-data=1 database1  |gzip >20171121.sql.gz

复制到从库后导入：

# 如果是压缩文件先解压
gzip -d 20171121.sql.gz
# 导入数据库
./bin/mysql -uroot -prepl database1 < 20171121.sql

从导出的sql中找到位置点：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=810859846;

设置change master to 语句：

# 登录数据库
# 填写具体信息
change master to
master_host='10.51.70.216',
master_port=3368,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000016',
master_log_pos=810859846;
# 启动主从复制
start slave;
# 查看是否正常启动。
show slave status\G;

至此，主从复制配置完成。

mysql-1030 mysql-1050

数据库主从同步时，从库报错：

登录从库数据库发现 mysql库下找不到ha_health_check这个表。然后到数据目录 data/mysql/目录下找只有ha_health_check.ibd 一个文件 .frm文件缺失。只有想办法修复该数据库才能继续同步。

一个数据库的表的.frm文件丢失后的恢复方法如下：

模拟创建一个t1表，并手动将该表的.frm 文件删除。然后再修复：

1、创建实验用的MyISAM表t1，并插入数据：
mysql> create table t1(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.00 sec)
2、删除t1表的.frm文件
[root@localhost gusha]# cd /var/lib/mysql/gusha #进如gusha库的数据目录
[root@localhost gusha]# ls
db.opt t1.MYI t1.frm t1.MYD
[root@localhost gusha]# rm -rf t1.frm
此时在gusha库里已经查询不到t1表了：
mysql> show tables;
Empty set (0.00 sec)
还能查询t1表里的内容是因为有缓存，清下缓存：
mysql> select * from t1;
+------+
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+------+
8 rows in set (0.00 sec)
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
ERROR 1146 (42S02): Table 'gusha.t1' doesn't exist

损坏表的修复过程：

3、进行恢复，把gusha库对应的文件夹里的t1.MYD和t1.MYI文件移动到其它文件夹：
[root@localhost gusha]# mv t1.MY*
/var/lib/backup/
[root@localhost gusha]# ls
db.opt
在gusha库里重新创建一个t1表，表结构和原来的t1表一样：（这里应用到本次问题就是到主库查询创建表的语句，然后执行创建表的操作，其他操作一样。）
mysql> create table t1(id int) engine=myisam;
把t1.MYD和t1.MYI文件移动会gusha库对应的文件夹：
[root@localhost gusha]# mv
/var/lib/backup/t1.MY* .
mv: overwrite `./t1.MYD'? y
mv: overwrite `./t1.MYI'? y
此时MySQL会自动修复t1表
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+------+
8 rows in set (0.00 sec)
如果没有自动修复，则执行下面命令进行修复：
mysql> repair table t1;
+----------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+--------+----------+----------+
| gusha.t1 | repair | status | OK |
+----------+--------+----------+----------+
1 row in set (0.00 sec)
到此MyISAM表t1.frm丢失后又恢复回来了。完毕。