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丢失后又恢复回来了。完毕。