CategoryMySQL

mysql中Incorrect string value乱码解决

你是否遇到过类似以下错误?

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x9C’ for column ‘content’ at row 1.

产生这种异常的原因在于,mysql中的utf8编码最多会用3个字节存储一个字符,如果一个字符的utf8

编码占用4个字节(最常见的就是ios中的emoji表情字符),那么在写入数据库时就会报错。

mysql从5.5.3版本开始,才支持4字节的utf8编码,编码名称为utf8mb4(mb4的意思是max bytes 4),这种编码方式最多用4个字节存储一个字符。

要想证明这个问题,可以执行以下sql:

select * from
information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'utf8%'

结果如图:

因此,要解决上述异常的发生,需要使用utf8mb4编码。

解决数据库编码后,还需要解决客户端Connection连接对象使用的编码问题。

调用创建的Connection对象执行以下sql:

conn.createStatement().execute("SET names 'utf8mb4'");

如果项目中使用了DataSource数据源,只需要对数据源进行相关配置即可,这里以apache的DBCP数据源为例讲解,在spring框架下配置如下:

<!-- 数据源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql://${${data-source.prefix}.data-source.host-name}:3306/${${data-source.prefix}.data-source.db-name}?characterEncoding=utf8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;maxReconnects=10&amp;allowMultiQueries=true" />
		<property name="username" value="${${data-source.prefix}.data-source.username}" />
		<property name="password" value="${${data-source.prefix}.data-source.password}" />
		<property name="maxActive" value="150" />
		<property name="maxIdle" value="2" />
		<property name="testOnBorrow" value="true" />
		<property name="testOnReturn" value="true" />
		<property name="testWhileIdle" value="true" />
		<property name="validationQuery" value="select 1" />
		<!-- 此配置用于在创建Connection对象时执行指定的初始化sql -->
		<property name="connectionInitSqls">
			<list>
				<value>set names 'utf8mb4'</value>
			</list>
		</property>
	</bean>

以下解释引用自mysql参考手册:

SET NAMES ‘charset_name’

SET NAMES显示客户端发送的SQL语句中使用什么字符集。

因此,SET NAMES ‘utf8mb4’语句告诉服务器:“将来从这个客户端传来的信息采用字符集utf8mb4”。它还为服务器发送回客户端的结果指定了字符集。(例如,如果你使用一个SELECT语句,它表示列值使用了什么字符集。)

SET NAMES ‘x’ 语句与这三个语句等价:

mysql> SET character_set_client = x;

mysql> SET character_set_results = x;

mysql> SET character_set_connection = x;

执行完此sql语句后,通过此连接对象后续创建的Statement都会成功地执行了。

讲到这里,问题已经得到完美解决,但是我又联想到一个新的问题:

jvm虚拟机运行时,内存中的字符串采用utf-16编码,对于ios中的emoji表情这种用4字节utf-8编码存储的字符,在java运行时又是怎样存储的呢?

于是,我找了一个emoji字符(4个字节的值分别为0xf0,0x9F,0x92,0x9c),做了以下试验。

byte[] bytes = new byte[] { (byte0xf0, (byte0x9F, (byte0x92, (byte0x9c };
		String s = new String(bytes, Charset.forName("utf-8"));
		System.out.println("length:"+s.length());
		for (int i=0;i<s.length();i++) {
			int ch = s.charAt(i);
			System.out.println("0x"+Integer.toHexString(ch));
		}

执行结果如下:

由结果可以看出,unicode值(也叫codePoint码点,后面介绍API会用到)大于0xffff的单个字符,jvm内部占用2个char的长度(也就是4个字节)存储。

所有大于0xffff的字符,全都在UTF编码表的辅助平面内(域辅助平台对应的是基础平面,简称BMP)。因此对于String中的某个char,是基础平面字符,还是辅助平面字符的一部分,也很好做出判断。下面介绍java.lang.Character中的一些API:

以下描述中,码点即是字符的unicode值

Character中API 描述
isValidCodePoint(int codePoint):boolean 判断输入码点是否是有效的,所有属于UTF定义平面的码点都是有效的
isBmpCodePoint(int codePoint):boolean 判断输入码点是否属于基础平面,即:0x0000~0xffff
isSupplementaryCodePoint(int codePoint):boolean 判断输入码点是否属于辅助平面,即:码点>0xffff
isSurrogate(char ch):boolean 判断输入的字符是否辅助平面字符的一部分

获取String中某个字符的码点也很容易,调用String.codePointAt(int index):int即可。

最后,关于unicode、UCS-2、UCS-4、UTF-8、UTF-16编码之间的关系,请读者自行百度。文章太多了,在此就不多做介绍了。

参考资料:

  • mysql utf8mb4与emoji表情:

http://my.oschina.net/wingyiu/blog/153357

  • 关于 MySQL UTF8 编码下生僻字符插入失败/假死问题的分析

http://my.oschina.net/leejun2005/blog/343353

MySQL Group Replicaiton配置

MySQL Group Replication(下简称:MGR)是MySQL官方推出的一种基于Paxos协议的状态机复制。在MGR出现之前,用户常见的MySQL高可用方式,无论怎么变化架构,本质就是Master-Slave架构。MySQL 5.7版本开始支持无损半同步复制(lossless semi-sync replication),从而进一步提示数据复制的强一致性。

Master-Slave始终无法解决的一个问题是选主(Leader Election),特别是当由于网络分区发生脑裂时,目前大多的高可用解决方案都会导致双写的问题,这在金融场景下显然是无法接受的。为避免此问题的发生,有时不得不强行关闭一台服务,从而保证同一时间只有一个节点可以写入,然而这时数据库集群可用性又可能会收到极大的影响。

MongoDB、TiDB这些出现的后起之后通过Raft协议来进行选主,从而避免脑裂问题的产生。然而他们依然是单写的场景,即一个高可用集群下,写入依然是单个节点。

MGR的解决方案现在来看可说非常完美:

  • 数据一致性保障:确保集群中大部分节点收到日志
  • 多节点写入支持:多写模式下支持集群中的所有节点都可以写入
  • Fault Tolerance: 确保系统发生故障(包括脑裂)依然可用,双写对系统无影响

从MGR公布的特性来基本是数据库所追求的最终完美形式。然而很多同学还会问MGR和无损半同步复制的区别,比如1个集群5个节点,无损半同步复制可以设置至少有2个节点收到ACK请求再提交事务,也能保障数据一致性。

Quorum原则(大部分原则)只是Paxos实现的一小部分,因此无损半同步复制解决的只是日志完整性的问题。若把日志看成是value,则只是解决了日志丢失问题。但是如何在分布式异常场景下确定这个value值,则需要Paxos协议来解决。比如,当发生脑裂情况下,谁是Primary,则MGR通过Paxos协议可以清楚的判断,从而避免双写问题。

组复制的限制

  • 存储引擎必须为Innodb
  • 每个表必须提供主键
  • 只支持ipv4,网络需求较高
  • 一个组最多只能有9台服务器
  • 不支持 Replication event checksums,
  • 不支持 Savepoints
  • 多主模式不支持SERIALIZABLE事务隔离级别
  • 多主模式不能完全支持级联外键约束
  • 多主模式不支持在不同节点上对同一个数据库对象并发执行DDL(在不同节点上对同一行并发进行RW事务,后发起的事务会失败)

配置过程

每个节点执行如下命令安装官方仓库的MySQL Server

apt-get update
cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb
dpkg -i mysql-apt-config_0.8.9-1_all.deb
apt-get update
aptitude install -y mysql-server

验证复制插件是否存在:

root@iZwz98pmxwulw67n9gxnl2Z:/etc/mysql# ll /usr/lib/mysql/plugin/
total 4120
drwxr-xr-x 3 root root    4096 Nov 11 11:14 ./
drwxr-xr-x 3 root root    4096 Nov 11 11:14 ../
...
-rw-r--r-- 1 root root 1751560 Sep 14 01:01 group_replication.so
...

注意: 不要安装Ubuntu 16.04自带的MySQL, 16.04自带的MySQL安装后没有 group_replication.so 这个东西, 一定要通过 mysql-apt-config_0.8.9-1_all.deb 提供的仓库安装. MySQL 官方版本的最新版本的仓库可以在这里下载: https://dev.mysql.com/downloa…

每个节点创建目录, 用于放置MGR(MySQL Group Replication)的配置文件

mkdir /etc/mysql/mgr.d

修改MGR配置文件

配置文件在本文Git仓库Markdown文件相同目录下, 文件名称依次为:

mgr-01.conf
mgr-02.conf
mgr-03.conf

修改上述三个文件对应的IP地址, 详细的说明参考: https://www.howtoing.com/how-…

生成复制组所需要的UUID备用.

root# uuidgen
00d17eae-73c8-4a7d-abf5-051bb68a9d7d

用上面生成的UUID替换 loose-group_replication_group_name

上传配置文件

https://github.com/developerw…

#!/bin/bash
# mkdir /etc/mysql/mgr.d
scp mgr-01.conf root@172.18.149.213:/etc/mysql/mgr.d/mgr.cnf
scp mgr-02.conf root@172.18.149.214:/etc/mysql/mgr.d/mgr.cnf
scp mgr-03.conf root@172.18.149.215:/etc/mysql/mgr.d/mgr.cnf

在每一个节点的 /etc/mysql/my.cnf 文件最后一行添加如下指令:

!includedir /etc/mysql/mgr.d/

更换MySQL的数据盘

双十一新购了3台本地SSD的ECS, 想把MySQL的数据目录移动到独立的SSD(/dev/vdb)上. 因此停止数据指标移动数据目录:

格式化磁盘

fdisk /dev/vdb

创建挂载点

mkdir /data

创建文件系统

mkfs.ext4 /dev/vdb1

查看磁盘UUID

blkid

复制 /dev/vdb1 的UUID, 在 /etc/fstab 下添加:

UUID=${UUID} /data           ext4    errors=remount-ro 0       1

用你自己的磁盘UUID替换 ${UUID}

挂载文件系统

mount /dev/vdb1 /data

创建软连接并启动MySQL

mv /var/lib/mysql /data
ln -s /data/mysql /var/lib/mysql
systemctl start mysql

目录权限问题

启动数据库查看日志(tail -f /var/log/mysql/error.log)发现如下错误消息:

2017-11-11T03:24:49.003621Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2017-11-11T03:24:49.003637Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2017-11-11T03:24:49.003642Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-11-11T03:24:49.604050Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-11-11T03:24:49.604070Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-11-11T03:24:49.604076Z 0 [ERROR] Failed to initialize plugins.
2017-11-11T03:24:49.604079Z 0 [ERROR] Aborting

解决办法

编辑如下文件

vi /etc/apparmor.d/usr.sbin.mysqld

在文件末尾 } 的上一行添加下面两行:

/data/mysql/ r,
/data/mysql/** rwk,

/data/mysql 为新的数据目录路径. 上面两行的作用是分配目录的读写权限, Ubuntu 16.04 默认的MySQL数据目录为 /var/lib/mysql.

复制配置

进入MySQL控制台

mysql -uroot -p

在所有节点执行如下命令:

SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 验证插件是否安装成功
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
...
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+

配置第一个节点

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

配置余下节点

START GROUP_REPLICATION;

加入组的问题

事务问题, 各个节点的事务状态不一致. 所有节点加入集群前最好不要修改任何数据, 否则就会出现下面的错误.

2017-11-10T19:07:26.918531Z 0 [ERROR] Plugin group_replication reported: 'This member has more > executed transactions than those present in the group. Local transactions: c3c274ff-c63e-11e7-> b339-00163e0c0288:1-4 > Group transactions: 2e6bfa69-0439-41c9-add7-795a9acfd499:1-10,
c5898a84-c63e-11e7-bc8b-00163e0af475:1-4'

解决办法:
http://blog.csdn.net/yuanlin6…

set global group_replication_allow_local_disjoint_gtids_join=ON;

IP地址变化的问题

对于一个3节点的单主集群来说, 当主节点挂了, 另外两个节点会自动选主. 其中一个会成为主节点, 并自动切换为读写模式.
因为对于单主模式来说, 只有主节点能够执行写操作. 那么我们如何知道主节点的IP地址呢?

可以在任意一个MySQL节点上通过如下SQL获取主节点的IP地址

SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');

参考资料

https://dev.mysql.com/doc/ref…
https://www.howtoing.com/how-…
http://blog.csdn.net/yuanlin6…
https://stackoverflow.com/que…

跨版本mysqldump恢复报错Errno1449

已经有一套主从mysql,新增两个slave
主库Server version: 5.6.22-log MySQL Community Server (GPL)
旧从库Server version: 5.6.28-log MySQL Community Server (GPL)

新增SLAVE 1: Server version: 5.6.22-log MySQL Community Server (GPL)
新增SLAVE 2: Server version: 5.7.10-log MySQL Community Server (GPL)
重新初始化新的两个slave后,从就从库的mysqldump文件导入恢复,因为增加了–dump-slave参数,可以看到主库的位置。
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.001196′, MASTER_LOG_POS=71925475;

dump文件导入完成后,在新增SLAVE 1执行:
change master to master_host=’192.168.72.142′, master_user=’repl’,master_password=’password’,master_port=3306,MASTER_LOG_FILE=’mysql-bin.001196′, MASTER_LOG_POS=71925475,MASTER_CONNECT_RETRY=30;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.72.142
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.001201
Read_Master_Log_Pos: 821264848
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 792405
Relay_Master_Log_File: mysql-bin.001196
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1449
Last_Error: Error ‘The user specified as a definer (‘usr_pre’@’%’) does not exist’ on query. Default database: ‘pre’. Query: ‘SELECT `pre`.`_getAutoIncrement`(_utf8’taobaoSoHeader’ COLLATE ‘utf8_general_ci’)’
Skip_Counter: 0
Exec_Master_Log_Pos: 72717597
Relay_Log_Space: 6129100284
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1449
Last_SQL_Error: Error ‘The user specified as a definer (‘usr_pre’@’%’) does not exist’ on query. Default database: ‘pre’. Query: ‘SELECT `pre`.`_getAutoIncrement`(_utf8’taobaoSoHeader’ COLLATE ‘utf8_general_ci’)’
Replicate_Ignore_Server_Ids:
Master_Server_Id: 142
Master_UUID: 6552d58f-7323-11e5-bc52-24b6fdf64510
Master_Info_File: /mysqldata/mysql_data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 160217 09:44:22
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
用工具查看binlog日志内容,发现也出错了,网上查了一下,说是BUG
-bash-4.1$ mysqlbinlog –start-position=72717597 –stop-position=72717598 -d pre mysql-bin.001201
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34
BINLOG ‘
5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz
xtk=
‘/*!*/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 577005919, event_type: 111
ERROR: Could not read entry at offset 72717597: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
-bash-4.1$ mysqlbinlog –start-position=72717597 –stop-position=72717598 mysql-bin.001201
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34
BINLOG ‘
5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz
xtk=
‘/*!*/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 577005919, event_type: 111
ERROR: Could not read entry at offset 72717597: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

-bash-4.1$ mysqlbinlog –start-position=72717597 –offset=1 mysql-bin.001201
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34
BINLOG ‘
5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz
xtk=
‘/*!*/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 577005919, event_type: 111
ERROR: Could not read entry at offset 72717597: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
怀疑权限问题
主库5.6.22上执行
mysql> show grants for ‘usr_pre’@’%’;
+——————————————————————————————————–+
| Grants for usr_pre@% |
+——————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘usr_pre’@’%’ IDENTIFIED BY PASSWORD ‘*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5’ |
| GRANT ALL PRIVILEGES ON `pre`.* TO ‘usr_pre’@’%’ |
+——————————————————————————————————–+
2 rows in set (0.02 sec)

旧SLAVE 5.6.28 备份导出的从库
mysql> show grants for ‘usr_pre’@’%’;
+——————————————————————————————————–+
| Grants for usr_pre@% |
+——————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘usr_pre’@’%’ IDENTIFIED BY PASSWORD ‘*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5’ |
| GRANT ALL PRIVILEGES ON `pre`.* TO ‘usr_pre’@’%’ |
+——————————————————————————————————–+
2 rows in set (0.01 sec)

新SLAVE 1是5.6.22,执行报错
mysql> show grants for ‘usr_pre’@’%’;
ERROR 1141 (42000): There is no such grant defined for user ‘usr_pre’ on host ‘%’

在新SLAVE 1执行
mysql> GRANT USAGE ON *.* TO ‘usr_pre’@’%’ IDENTIFIED BY PASSWORD ‘*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5’ ;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `pre`.* TO ‘usr_pre’@’%’;
ERROR 1133 (42000): Can’t find any matching row in the user table
flush privileges;
show grants for ‘usr_pre’@’%’;
发现已经恢复正常

开启slave后,可以正常复制。

———————–
处理新增SLAVE 2:跨一个大版本,5.7.10
执行下面语句时报错:
mysql> change master to master_host=’192.168.72.142′, master_user=’repl2′,master_password=’password’,master_port=3306,MASTER_LOG_FILE=’mysql-bin.001196′, MASTER_LOG_POS=71925475,MASTER_CONNECT_RETRY=30;

ERROR 1805 (HY000): Column count of mysql.slave_master_info is wrong. Expected 25, found 23. The table is probably corrupted

处理方法:
[mysql@lt-mysql02 mysql_backup]$ mysql_upgrade -s -uroot -p
Enter password:
The –upgrade-system-tables option was used, databases won’t be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.0).
Found 0 sys functions, but expected 21. Re-installing the sys schema.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
重复执行上面语句,恢复正常
mysql_upgrade是一个检查和升级表的命令,用于检查当前表是否和mysql server版本兼容,例如从低版本的mysqldump导入了高版本的情况。也可以用于升级系统表。
执行该命令的时候,如果该命令发现有表的兼容性有问题,那么会做表升级,
如果发现表有问题,会做表修复,如果该命令无法修复,则需要用重建的方式手工修复表或索引。
推荐每次升级后都执行mysql_upgrade

mysql_upgrade参数-s的意思是只升级系统表,不修复数据表
–upgrade-system-tables, -s
Upgrade only the system tables, do not upgrade data.

mysql的GTID复制和多源复制

配置基于GTID的复制
——————————————–
在参数文件/etc/my.cnf增加下面内容:
主库
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-host=10.45.10.209
report-port=3306
server_id = 2091
从库除了上面的参数外,还要增加
relay-log = relay-log
relay-log-index = relay-log.index
配置GTID的slave
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY ‘repl123′;
flush privileges;
change master to master_host=’10.45.10.209′, master_user=’repl’,master_password=’repl123′,master_auto_position=1;

GTID复制的好处:
对运维人员来说应该是一件大喜的事情,在主从切换后,在传统的方式里,你需要找到binlog和POS点,然后change master to指向,
而不是很有经验的运维,往往会将其找错,造成主从同步复制报错,在mysql5.6里,你无须再知道binlog和POS点,
你只需要知道master的IP、端口,账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步。

—————————————-
配置多源复制:
即多个主,复制到1个从库,所有db汇总到一个slave的实例
注意:
多源数据库不能有同名库,否则会导致多源复制失败
例如,在220的slave 3307实例上面配置多源复制,汇集3个主的数据
CHANGE MASTER TO MASTER_HOST=’10.45.10.209′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master209-2′;
CHANGE MASTER TO MASTER_HOST=’10.45.10.218′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master218-2′;
CHANGE MASTER TO MASTER_HOST=’10.45.10.219′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master219-2’;

常用管理命令:
mysql> select * from mysql.slave_master_info;

mysql> START SLAVE io_thread FOR CHANNEL ‘master209-2’;
mysql> START SLAVE FOR CHANNEL ‘master209-2’;

mysql> show SLAVE status FOR CHANNEL ‘master209-2’\G
多源复制的好处:
对于备份目的的主备架构,多个主可以备份到一台物理备机上,节约资源

mysql5.6配置semi_sync

测试环境:
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Server version: 5.6.22-log MySQL Community Server (GPL)

mysql的replication协议是异步的,虽然异步效率、性能很好,但是却无法保证主从数据一致性,
如果master crash,已经commit的事务不会被传送到任何的slave上,
从mysql5.5之后,mysql为了保证主从库数据一致性,引进了semi-sync功能,
semi-sync意思是MASTER只需要接收到其中一台SLAVE的返回信息,就会commit;否则需等待直至切换成异步再提交。

优点:
当事务返回客户端成功后,则日志一定在至少两台主机上存在。
MySQL的Semi-sync适合小事务,且两台主机的延迟又较小,则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。

缺点:
完成单个事务增加了额外的等待延迟,延迟的大小取决于网络的好坏。

配置方法:
在主库安装semisync_master插件:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.dll’; //windows

在备库安装semisync_slave插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;//windows
主库上,新增如下参数:
$vi /etc/my.cnf

[mysqld]

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

备库上新增:

$vi /etc/my.cnf

[mysqld]

rpl_semi_sync_slave_enabled=1

重启master和slave,semi_sync插件会自动加载

重启后,master显示如下:
mysql> show variables like ‘%rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON | #表示即使没有SLAVE也会等待过期时间结束,是默认值
+————————————+——-+
4 rows in set (0.00 sec)

mysql> show status like ‘%semi%’;
+——————————————–+———-+
| Variable_name | Value |
+——————————————–+———-+
| Rpl_semi_sync_master_clients | 3 | #有多少个Semi-sync的备库,我配置了3个
| Rpl_semi_sync_master_net_avg_wait_time | 2525 | #事务提交后,等待备库响应的平均时间
| Rpl_semi_sync_master_net_wait_time | 45892342 | #总的网络等待时间
| Rpl_semi_sync_master_net_waits | 18174 | #等待网络响应的总次数
| Rpl_semi_sync_master_no_times | 0 | #一共有几次从Semi-sync跌回普通状态
| Rpl_semi_sync_master_no_tx | 0 | #备库未及时响应的事务数
| Rpl_semi_sync_master_status | ON | #主库上Semi-sync是否正常开启
| Rpl_semi_sync_master_timefunc_failures | 0 | #时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time | 1196 | #开启Semi-sync,事务返回需要等待的平均时间
| Rpl_semi_sync_master_tx_wait_time | 7918635 | #事务等待备库响应的总时间
| Rpl_semi_sync_master_tx_waits | 6620 | #事务等待备库响应的总次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 | #改变当前等待最小二进制日志的次数
| Rpl_semi_sync_master_wait_sessions | 0 | #当前有几个线程在等备库响应
| Rpl_semi_sync_master_yes_tx | 6549 | #Semi-sync模式下,成功的事务数
+——————————————–+———-+
14 rows in set (0.00 sec)

slave上显示如下:
mysql> show variables like ‘%rpl_semi%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+———————————+——-+
2 rows in set (0.01 sec)

mysql> show status like ‘%semi%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Rpl_semi_sync_slave_status | ON |
+—————————-+——-+
1 row in set (0.00 sec)

优化mysql slave的同步速度

测试环境:
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Server version: 5.6.22-log MySQL Community Server (GPL)

我搭建了1主3从的环境,准备测试MHA架构,过程中发现,测试并发插入的时候,从库1可以跟上,从库2,3跟不上

如何判断是io thread慢还是 sql thread慢呢,有个方法,观察show slave status\G ,

判断3个参数(参数后面的值是默认空闲时候的正常值):
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Seconds_Behind_Master: 0

1.sql thread慢的表现:
Seconds_Behind_Master越来越大
Slave_SQL_Running_State: Reading event from the relay log

2.io thread慢的表现:
Seconds_Behind_Master为0
Slave_SQL_Running_State: 显示正常值
Slave_IO_State:显示忙碌状态

而我观察到的值是
Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 313
Slave_SQL_Running_State: Reading event from the relay log
因此推断是sql thread慢

为啥只有slave2,3慢,而slave1可以跟上呢,开始怀疑是参数配置的差异,比对/etc/my.cnf后发现,配置无差异
因此排除这个原因,后来用dstat观察,发现繁忙时候,slave的IO写速度上不去
slave1:
$ dstat
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 100 0 0 0|9308B 11k| 0 0 | 3B 3B| 63 63
3 4 54 40 0 0| 88k 10M| 45k 9438B| 0 0 |1857 2579
3 3 59 35 0 1| 80k 7552k| 40k 8486B| 0 0 |1675 2307
3 3 56 38 0 0| 72k 7824k| 42k 8816B| 0 0 |1727 2348
3 4 52 41 0 1| 96k 9688k| 49k 10k| 0 0 |2029 2874
3 4 54 39 0 0| 96k 8880k| 45k 9410B| 0 0 |1905 2674
3 3 53 40 0 1| 96k 9776k| 58k 10k| 0 0 |1935 2671
3 3 58 36 0 0| 64k 7848k| 40k 8420B| 0 0 |1724 2357
3 5 52 40 0 1| 96k 8936k| 49k 10k| 0 0 |1948 2680
3 4 51 42 0 1| 96k 9400k| 49k 10k| 0 0 |1988 2760
3 4 52 41 0 0| 88k 9752k| 49k 10k| 0 0 |2058 2868
4 4 51 41 0 1| 96k 9680k| 49k 9938B| 0 0 |1990 2750
3 3 59 35 0 0| 80k 7632k| 39k 8288B| 0 0 |1668 2275
3 4 52 42 0 1| 80k 8504k| 46k 9146B| 0 0 |1860 2523
3 4 51 42 0 0| 80k 8496k| 43k 8684B| 0 0 |1882 2516
2 3 65 30 0 0| 64k 5976k| 30k 6440B| 0 0 |1326 1802
3 4 53 40 0 1| 72k 8360k| 59k 10k| 0 0 |1859 2538
3 4 51 42 0 1| 96k 8840k| 53k 10k| 0 0 |1958 2648
2 4 51 43 0 0| 72k 7352k| 40k 7760B| 0 0 |1633 2219
3 4 51 42 0 1| 88k 7920k| 31k 6770B| 0 0 |1767 2373
3 3 54 40 0 0| 80k 8528k| 40k 8750B| 0 0 |1859 2549

slave2:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
2 1 50 47 0 1|8192B 1168k| 55k 10k| 0 0 | 533 771
1 1 51 48 0 0|8192B 1048k| 33k 7046B| 0 0 | 427 622
1 1 51 48 0 0|8192B 1080k| 58k 9806B| 0 0 | 500 709
1 1 50 48 0 0| 0 1864k| 51k 8486B| 0 0 | 502 669
1 2 51 47 0 0|8192B 1120k| 42k 8156B| 0 0 | 496 674
1 1 51 47 0 0|8192B 1160k| 32k 6350B| 0 0 | 467 655
1 2 51 47 0 0| 0 1288k| 50k 10k| 0 0 | 563 797
1 1 51 47 0 0|8192B 1200k| 43k 8486B| 0 0 | 493 728
2 1 50 47 0 0|8192B 1024k| 45k 8816B| 0 0 | 481 659
1 1 50 48 0 0|8192B 1248k| 49k 9450B| 0 0 | 517 772
1 1 50 48 0 0| 0 1264k| 47k 9146B| 0 0 | 516 756
1 2 50 47 0 1|8192B 1144k| 50k 10k| 0 0 | 520 765
1 1 51 48 0 0|8192B 1200k| 51k 8156B| 0 0 | 484 716
1 2 50 48 0 0|8192B 968k| 50k 9278B| 0 0 | 470 684
1 1 50 48 0 0|8192B 1128k| 39k 7892B| 0 0 | 476 679
1 1 51 47 0 0| 0 1248k| 45k 9476B| 0 0 | 523 760
1 2 50 48 0 0|8192B 1448k| 41k 7826B| 0 0 | 552 805
1 1 50 48 0 0|8192B 1120k| 44k 8090B| 0 0 | 470 692

slave3:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 1 50 49 0 0|8192B 1328k|1167B 170B| 0 0 | 385 515
1 1 51 48 0 0|8192B 1128k| 754B 170B| 0 0 | 325 449
1 1 50 49 0 0| 0 920k| 474B 314B| 0 0 | 279 381
0 1 50 49 0 0|8192B 664k|1633B 170B| 0 0 | 226 291
1 1 50 49 0 0|8192B 1200k|1250B 170B| 0 0 | 353 475
1 1 50 48 0 0| 0 1432k|1632B 170B| 0 0 | 402 551
1 1 51 48 0 0| 16k 1752k|1045B 170B| 0 0 | 487 664
1 1 50 48 0 0|8192B 1648k| 12k 170B| 0 0 | 461 636
1 1 51 48 0 0| 0 1272k| 886B 170B| 0 0 | 380 501
1 1 50 49 0 0|8192B 1000k|1023B 170B| 0 0 | 300 400
1 1 50 48 0 0|8192B 1096k| 747B 170B| 0 0 | 332 442
1 1 50 48 0 0|8192B 1448k|1003B 170B| 0 0 | 416 557
1 1 50 48 0 0| 0 1592k|1174B 170B| 0 0 | 450 614
1 1 51 48 0 0|8192B 1416k|1028B 170B| 0 0 | 404 552
0 1 50 49 0 0|8192B 1128k|1031B 170B| 0 0 | 331 447
1 1 51 48 0 0|8192B 1160k|1185B 170B| 0 0 | 340 458
1 1 50 49 0 0| 0 1120k| 633B 170B| 0 0 | 326 453
1 0 50 49 0 0|8192B 656k|8886B 170B| 0 0 | 221 288
1 1 50 49 0 0|8192B 1128k|1619B 170B| 0 0 | 335 451

slave1可以达到每秒9M的写入IO,而slave2,3只能达到每秒1M多,IO性能差很多,后来分析了下存储,发现是有很大差异的,也印证了我的推测

那么问题来了,要如何优化IO性能比较差的slave呢,其实很简单,修改两个参数
mysql> set global sync_binlog=20 ;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

innodb_flush_log_at_trx_commit
如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
注意:
由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog
sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。
注意:
如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。
而且mysql服务默认是autocommit打开的

修改参数后,slave2,3也一样可以跟上slave1的速度了
slave2,3:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
3 2 94 2 0 0| 32k 80k| 49k 10k| 0 0 |1042 658
3 2 94 1 0 1| 32k 72k| 49k 10k| 0 0 |1258 964
2 2 95 2 0 0| 32k 72k| 44k 9146B| 0 0 |1126 882
2 1 95 2 0 0| 32k 72k| 41k 8486B| 0 0 | 959 659
2 2 96 1 0 0| 32k 72k| 47k 9476B| 0 0 |1153 841
2 2 95 2 0 0| 24k 72k| 39k 8090B| 0 0 | 866 504
2 2 96 1 0 0| 24k 72k| 42k 7562B| 0 0 | 908 663
2 1 95 2 0 0| 40k 72k| 52k 10k| 0 0 |1084 685
3 1 94 2 0 1| 40k 80k| 54k 11k| 0 0 |1204 873
2 2 96 1 0 0| 16k 32k| 30k 6044B| 0 0 | 846 802
2 1 97 1 0 0| 24k 32k| 35k 7760B| 0 0 |1059 888
2 1 95 3 0 0| 32k 856k| 44k 9278B| 0 0 | 943 551
2 1 94 3 0 0| 32k 104k| 42k 8618B| 0 0 | 986 704
2 1 96 1 0 0| 24k 72k| 34k 7034B| 0 0 | 863 682
2 2 95 2 0 0| 32k 64k| 45k 8684B| 0 0 |1052 750
2 2 90 7 0 0| 24k 416k| 38k 7166B| 0 0 | 906 722
3 2 93 2 0 1| 32k 80k| 57k 10k| 0 0 |1069 829
3 2 94 1 0 0| 32k 72k| 42k 8486B| 0 0 |1076 942
2 1 96 1 0 0| 24k 72k| 37k 7496B| 0 0 | 859 575
2 2 94 2 0 1| 32k 64k| 43k 8684B| 0 0 |1138 1011
3 2 94 1 0 0| 32k 72k| 42k 9014B| 0 0 |1099 782
2 3 94 2 0 0| 32k 72k| 50k 10k| 0 0 |1332 1359
2 2 95 2 0 0| 24k 72k| 34k 6902B| 0 0 | 921 799
2 2 94 2 0 0| 40k 72k| 55k 11k| 0 0 |1318 1016
1 2 96 2 0 0| 32k 80k| 41k 8882B| 0 0 |1020 719
而且我观察到slave2,3的写入数量减少了两个数量级,从1M多下降到70k

orzdba工具安装注意事项

orzdba是一个监控mysql性能的一个比较好用的perl脚本,是淘宝开源的小工具,下载地址http://code.taobao.org/p/orzdba/src/trunk/

配置过程中除了参照《orzdba工具使用说明》外,还有一些注意事项

 

我的安装环境是

Red Hat Enterprise Linux Server release 6.3 (Santiago)

5.6.22-log MySQL Community Server (GPL)

 

安装tcprstat前,先安装包

yum install glibc-static-2.12-1.80.el6.x86_64.rpm

 

参照

http://my.oschina.net/moooofly/blog/157063安装tcprstat

安装tcprstat过程中,除了前面的那个glibc-static外,还需要安装5个包

yum -y install automake

yum install bison

yum install flex

yum install patch

yum install make

配置tcprstat的权限

chown mysql:mysql -R tcprstat

ln -sf /mysqldata/soft/tcprstat/src/tcprstat /usr/bin/tcprstat

chown root:root /usr/bin/tcprstat

chmod u+s /usr/bin/tcprstat

ll /usr/bin/tcprstat

lrwxrwxrwx 1 root root 37 Feb 11 11:22 /usr/bin/tcprstat -> /mysqldata/soft/tcprstat/src/tcprstat

查看目录/mysqldata/soft/tcprstat/src

[root@open_mysql1 src]# ll

total 1996

-rw-r–r– 1 mysql mysql    3006 Feb 10 15:48 capture.c

-rw-r–r– 1 mysql mysql    1057 Feb 10 15:48 capture.h

-rw-r–r– 1 mysql mysql    4015 Feb 10 15:48 functions.c

-rw-r–r– 1 mysql mysql    1019 Feb 10 15:48 functions.h

-rw-r–r– 1 mysql mysql    4325 Feb 10 15:48 local-addresses.c

-rw-r–r– 1 mysql mysql    1065 Feb 10 15:48 local-addresses.h

-rw-r–r– 1 mysql mysql   41829 Feb 11 11:07 Makefile

-rw-r–r– 1 mysql mysql    1800 Feb 10 15:48 Makefile.am

-rw-r–r– 1 mysql mysql   47676 Feb 11 11:07 Makefile.in

-rw-r–r– 1 mysql mysql   10157 Feb 10 15:48 output.c

-rw-r–r– 1 mysql mysql    1315 Feb 10 15:48 output.h

-rw-r–r– 1 mysql mysql    3970 Feb 10 15:48 process-packet.c

-rw-r–r– 1 mysql mysql    1139 Feb 10 15:48 process-packet.h

-rw-r–r– 1 mysql mysql   10411 Feb 10 15:48 stats.c

-rw-r–r– 1 mysql mysql    1958 Feb 10 15:48 stats.h

-rw-r–r– 1 mysql mysql    7845 Feb 10 15:48 stats-hash.c

-rw-r–r– 1 mysql mysql    1480 Feb 10 15:48 stats-hash.h

-rwsr-xr-x 1 root  root   291086 Feb 11 11:08 tcprstat

-rw-r–r– 1 mysql mysql    5970 Feb 10 15:48 tcprstat.c

-rw-r–r– 1 mysql mysql   10720 Feb 11 11:07 tcprstat-capture.o

-rw-r–r– 1 mysql mysql   10272 Feb 11 11:07 tcprstat-functions.o

-rw-r–r– 1 mysql mysql    1339 Feb 10 15:48 tcprstat.h

-rw-r–r– 1 mysql mysql   13568 Feb 11 11:08 tcprstat-local-addresses.o

-rw-r–r– 1 mysql mysql   23312 Feb 11 11:08 tcprstat-output.o

-rw-r–r– 1 mysql mysql   16072 Feb 11 11:08 tcprstat-process-packet.o

-rwxr-xr-x 1 mysql mysql 1258608 Feb 11 11:08 tcprstat-static

-rw-r–r– 1 mysql mysql   10720 Feb 11 11:08 tcprstat_static-capture.o

-rw-r–r– 1 mysql mysql   10272 Feb 11 11:08 tcprstat_static-functions.o

-rw-r–r– 1 mysql mysql   13568 Feb 11 11:08 tcprstat_static-local-addresses.o

-rw-r–r– 1 mysql mysql   23312 Feb 11 11:08 tcprstat_static-output.o

-rw-r–r– 1 mysql mysql   16072 Feb 11 11:08 tcprstat_static-process-packet.o

-rw-r–r– 1 mysql mysql   16056 Feb 11 11:08 tcprstat_static-stats-hash.o

-rw-r–r– 1 mysql mysql   27232 Feb 11 11:08 tcprstat_static-stats.o

-rw-r–r– 1 mysql mysql   18576 Feb 11 11:08 tcprstat_static-tcprstat.o

-rw-r–r– 1 mysql mysql   16056 Feb 11 11:08 tcprstat-stats-hash.o

-rw-r–r– 1 mysql mysql   27232 Feb 11 11:08 tcprstat-stats.o

-rw-r–r– 1 mysql mysql   18576 Feb 11 11:07 tcprstat-tcprstat.o

普通的yum,rpm方式安装version模块会报错,用如下安装方式解决

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

yum -y install perl-CPAN

yum install perl-Params-Validate

perl -MCPAN -e ‘install “Module::Build”‘

perl -MCPAN -e ‘install “File::Lockfile”‘

 

执行上面命令后,应该安装好了version, Class-Data-Inheritable, Module-Build, File::Lockfile等模块,可以用脚本查看是否安装成功

$ cat check_module.pl

#!/usr/bin/perl

use ExtUtils::Installed;

my $inst = ExtUtils::Installed->new();

print join “\n”,$inst->modules();

print “\n”;

对于orzdba脚本,在160行修改root密码,才能运行orzdba脚本,修改后样子如下:

my $MYSQL    = qq{mysql -s –skip-column-names -uroot -P$port -pLisx_new_123 };

改成这样后,会出现mysql5.6的明文密码告警:Warning: Using a password on the command line interface can be insecure.我是如下解决的

orzdba1

弄一个免密码的本地可以访问的管理员账户,用这个账户监控?

这个方案也不行,因为每一行都要回车确认

 

我的解决方案,在mysql的根目录新建隐藏文件,权限为400

-bash-4.1$ cat .my.cnf

[client]

password=lisx123

user=dbalisx

 

并且修改orzdba脚本的第160行处

my $MYSQL    = qq{mysql -s –skip-column-names -udbalisx -P$port };

orzdba2

好了,可以痛快的使用orzdba脚本了。

如何学习MySQL

转自高手的帖子:

1.坚持阅读官方手册,看MySQL书籍作用不会特别大;(挑选跟工作相关的内容优先阅读,例如InnoDB存储引擎,MySQL复制,查询优化)

2.阅读官方手册,同时对阅读的内容做对应的测试;

3.结合你现在的工作内容,多实战即可;

4.外部的BLOG ,也包过我的 http://mysqlops.com 只是作为参考即可,更多要培养自己的分析思考的模式;

备注:

国内人写的MySQL书籍都不要作为重点,包过我可能2014年出版一本关于MySQL的书籍,推荐大家只作为辅助的,可以上厕所的时候看看,坚持官方手册+测试+实战+思考总结为主的模式。

看来耐心看完官方文档是十分重要的!

pt-table-checksum、pt-table-sync核对主从库一致性

一.下载并安装工具
http://www.percona.com/downloads/percona-toolkit/
目前最新的版本是percona-toolkit_2.2.12.tar.gz
上传到服务器后,解压缩,并设置到环境变量
在mysql用户的环境变量文件增加路径
vi .bash_profile
export PATH=$PATH:/mysqldata/soft/percona-toolkit-2.2.12/bin

二.使用pt-table-checksum命令查找不一致的数据
主要关注的列是DIFFS,为0则一致,为1则不一致
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format h=localhost,u=root,p=passwd,P=3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T14:00:20 0 0 34 1 0 0.304 test.test_inv_log
01-21T14:00:20 0 0 9 1 0 0.304 test.test_inventory
01-21T14:00:21 0 0 2 1 0 0.061 test.test_lastexpressno
01-21T14:00:21 0 0 38 1 0 0.070 test.test_location
01-21T14:00:21 0 0 97 1 0 0.068 test.test_login_history
01-21T14:00:21 0 1 33 1 0 0.065 test.test_menu
01-21T14:00:21 0 0 11 1 0 0.061 test.test_pkd
01-21T14:00:21 0 0 1 1 0 0.061 test.test_promotion
01-21T14:00:21 0 0 1 1 0 0.064 test.test_promotion_condition
01-21T14:00:21 0 0 2 1 0 0.060 test.test_promotion_gift

实验1:在从库多插入记录,制造差异
上面标红的一行是我故意到从库手工插入的一条记录
为了制造不一致的数据,我停掉了双主复制的中的从库到主库的反向复制,关闭了只读参数,手工插入了一条记录。

pt-table-checksum还可以带很多参数,比较重要的用法如下:
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format [–replicate=rep_test.checksums –databases=rep_test –tables=test1] h=localhost,u=root,p=passwd,P=3306

中间的黑色部分,中括号里面的部分是可选的,
–nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用–databases来指定需要检查的数据库。
–no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。
–replicate-check-only :只显示不同步的信息。
–replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中,如果不指定,默认会在主库新建一个database叫percona,检查结果存放在percona的checksums表中。
–databases= :指定需要被检查的数据库,多个则用逗号隔开。
–tables= :指定需要被检查的表,多个用逗号隔开

我们再次尝试一下,加2个好用的参数,replicate-check-only和database参数
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –replicate-check-only –databases=test h=localhost,u=root,p=passwd,P=3306

使用这命令效果不好,不建议带replicate-check-only参数,因为输出的列不全
只使用—databases参数指定要核对的数据库比较好
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –databases=test h=localhost,u=root,p=passwd,P=3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T14:34:30 0 0 34 1 0 0.303 test.test_inv_log
01-21T14:34:30 0 0 9 1 0 0.061 test.test_inventory
01-21T14:34:30 0 0 2 1 0 0.306 test.test_lastexpressno
01-21T14:34:30 0 0 38 1 0 0.322 test.test_location
01-21T14:34:31 0 0 97 1 0 0.307 test.test_login_history
01-21T14:34:31 0 1 33 1 0 0.305 test.test_menu
01-21T14:34:31 0 0 11 1 0 0.060 test.test_pkd
01-21T14:34:31 0 0 1 1 0 0.055 test.test_promotion
01-21T14:34:31 0 0 1 1 0 0.057 test.test_promotion_condition
01-21T14:34:31 0 0 2 1 0 0.068 test.test_promotion_gift
01-21T14:34:31 0 0 2 1 0 0.058 test.test_promotion_rule
01-21T14:34:31 0 0 33 1 0 0.056 test.test_role
01-21T14:34:31 0 0 95 1 0 0.059 test.test_search_tab
01-21T14:34:32 0 0 25 1 0 0.303 test.test_send_template

执行后,可以发现差异,但是核查结果表percona.checksums,却发现有BUG,结果表显示无差异,结果表主要关注两列this_crc和master_crc
mysql> show tables ;
+——————-+
| Tables_in_percona |
+——————-+
| checksums |
+——————-+
1 row in set (0.00 sec)
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+——-+—————————+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+——-+—————————+——-+———-+———-+————+————+———————+
| test | test_inv_adjust_header | 1 | 2a7935e8 | 1 | 2a7935e8 | 1 | 2015-01-21 14:34:29 |
| test | test_inv_log | 1 | efc2d050 | 34 | efc2d050 | 34 | 2015-01-21 14:34:29 |
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 14:34:30 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 14:34:30 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 14:34:30 |
| test | test_menu | 1 | 6046676 | 33 | 6046676 | 33 | 2015-01-21 14:34:31 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 14:34:31 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 14:34:31 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 14:34:31 |
| test | test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 14:34:31 |
| test | test_promotion_rule | 1 | 76daf3aa | 2 | 76daf3aa | 2 | 2015-01-21 14:34:31 |

标红的部分显示,没有发现主从库的差异
因此,我推断核查差异的时候,以命令输出为准,检查结果表的数据不准确,后面的实验否定了这个结论

实验2:修改主库的一条数据,制造差异
停止从库的slave,在主库修改一条数据,和从库内容不一致
从库:
mysql> stop slave ;
Query OK, 0 rows affected (0.02 sec)

主库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)

mysql> update test.test_menu set menuDescr=’TEST2222′ where id=60 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

用pt-table-check命令核查,竟然没发现差异,难道是因为slave没有开启的缘故吗,但是一旦开启从库的话,同步了binlog,差异又没了,因此打算重新搭建slave,跳过几个transacation,试试看

从库执行
mysql> change master to master_host=’192.168.2.195′,master_user=’repl’,master_password=’slave123′,master_log_file=’mysql-bin.000006′,master_log_pos=692149 ;

mysql> start slave ;

配合后,主备复制正常,主备差异存在
主库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2222
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)

从库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)
标红的就是主从库的差异

在主库再次执行pt-table-checksum命令
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –databases=test h=localhost,u=root,p=passwd,P=3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T15:17:41 0 0 4440 4 0 0.283 test.test_address
01-21T15:17:42 0 0 340 1 0 0.304 test.test_allocation
01-21T15:17:42 0 0 2 1 0 0.059 test.test_allocationexp
01-21T15:17:42 0 0 10 1 0 0.055 test.test_asn_detail
01-21T15:17:42 0 0 9 1 0 0.051 test.test_asn_header
01-21T15:17:42 0 0 8 1 0 0.051 test.test_biz_inventory
01-21T15:17:42 0 0 0 1 0 0.300 test.test_combination_map
01-21T15:17:42 0 0 20 1 0 0.055 test.test_common_property
01-21T15:17:42 0 0 103 1 0 0.055 test.test_doc_log
01-21T15:17:42 0 0 0 1 0 0.047 test.test_etrackno_used
01-21T15:17:42 0 0 20 1 0 0.051 test.test_express_company
01-21T15:17:42 0 0 2 1 0 0.051 test.test_inter_promotion_rule
01-21T15:17:42 0 0 1 1 0 0.062 test.test_inv_adjust_detail
01-21T15:17:43 0 0 1 1 0 0.056 test.test_inv_adjust_header
01-21T15:17:43 0 0 34 1 0 0.058 test.test_inv_log
01-21T15:17:43 0 0 9 1 0 0.054 test.test_inventory
01-21T15:17:43 0 0 2 1 0 0.058 test.test_lastexpressno
01-21T15:17:43 0 0 38 1 0 0.056 test.test_location
01-21T15:17:43 0 0 97 1 0 0.058 test.test_login_history
01-21T15:17:43 0 1 34 1 0 0.067 test.test_menu
01-21T15:17:43 0 0 11 1 0 0.161 test.test_pkd
01-21T15:17:44 0 0 1 1 0 0.347 test.test_promotion
01-21T15:17:44 0 0 1 1 0 0.409 test.test_promotion_condition
01-21T15:17:44 0 0 2 1 0 0.334 test.test_promotion_gift
01-21T15:17:45 0 0 2 1 0 0.163 test.test_promotion_rule
01-21T15:17:45 0 0 33 1 0 0.059 test.test_role
01-21T15:17:45 0 0 95 1 0 0.055 test.test_search_tab
01-21T15:17:45 0 0 25 1 0 0.309 test.test_send_template
01-21T15:17:45 0 0 4 1 0 0.057 test.test_sequence
01-21T15:17:45 0 0 30 1 0 0.300 test.test_sku
01-21T15:17:46 0 0 21 1 0 0.298 test.test_so_detail
01-21T15:17:46 0 0 15 1 0 0.069 test.test_so_header
01-21T15:17:46 0 0 2 1 0 0.056 test.test_soh_ee_relation
01-21T15:17:46 0 0 14 1 0 0.308 test.test_soh_eo_relation
01-21T15:17:46 0 0 12 1 0 0.060 test.test_store
01-21T15:17:46 0 0 23 1 0 0.059 test.test_sys_code
01-21T15:17:46 0 0 3 1 0 0.054 test.test_sys_config
01-21T15:17:47 0 0 32 1 0 0.055 test.test_sys_domain
01-21T15:17:47 0 0 19 1 0 0.061 test.test_taobao_so_detail
01-21T15:17:47 0 0 14 1 0 0.312 test.test_taobao_so_header
01-21T15:17:47 0 0 50 1 0 0.063 test.test_trackprint_template
01-21T15:17:47 0 0 32 1 0 0.057 test.test_user
01-21T15:17:47 0 0 31 1 0 0.053 test.test_user_role
01-21T15:17:47 0 0 31 1 0 0.060 test.test_warehouse
终于发现了差异

在主库检查结果表percona.checksums,没有显示差异,难道还是有BUG ?
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+——-+—————————+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+——-+—————————+——-+———-+———-+————+————+———————+
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| test | test_menu | 1 | d9d69755 | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 15:17:43 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |

68 rows in set (0.00 sec)

在从库检查结果表percona.checksums
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+——-+—————————+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| test | test_menu | 1 | 631fa3bf | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 15:17:43 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |
| test | test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 15:17:44 |

+——-+—————————+——-+———-+———-+————+————+———————+
68 rows in set (0.01 sec)
终于显示了差异,原来查看结果表记录的差异,要在slave库查看才行
前面在从库多插入的记录的那个实验,在从库查看的话,应该也没有问题,是我查看选了不正确的库查看导致的
结论:查看差异结果表,应该在slave库上查询

下面的结果是我后来又重做的实验,在从库查询的
select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_crc != master_crc ;
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_crc != master_crc ;
+—–+———-+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+—–+———-+——-+———-+———-+————+————+———————+
| test | test_menu | 1 | 3be44ed2 | 35 | d9d69755 | 34 | 2015-01-21 16:41:25 |
+—–+———-+——-+———-+———-+————+————+———————+
1 row in set (0.00 sec)
三.用pt-table-sync修复不一致数据
-bash-4.1$ pt-table-sync –print –replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p=passwd

REPLACE INTO `test`.`test_menu`(`id`, `createtime`, `updatetime`, `domain_id`, `creator_id`, `updator_id`, `status_id`, `version`, `menuname`, `parent_id`, `role_id`, `sortindex`, `menudescr`, `remark`, `udf1`, `udf2`, `udf3`, `udf4`) VALUES (’60’, ‘2015-01-21 13:58:18’, ‘2015-01-21 13:58:20’, ‘-1’, NULL, NULL, NULL, ‘0’, ‘MENU_TEST2′, ’54’, ’57’, ‘6’, ‘TEST2222′, NULL, NULL, NULL, NULL, NULL) /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=…,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=…,u=root lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8016 user:mysql host:open_mysql1*/;
-bash-4.1$ pt-table-sync –print –sync-to-master h=192.168.2.196,u=root,p=passwd –databases=test –tables=test_menu
REPLACE INTO `test`.`test_menu`(`id`, `createtime`, `updatetime`, `domain_id`, `creator_id`, `updator_id`, `status_id`, `version`, `menuname`, `parent_id`, `role_id`, `sortindex`, `menudescr`, `remark`, `udf1`, `udf2`, `udf3`, `udf4`) VALUES (’60’, ‘2015-01-21 13:58:18’, ‘2015-01-21 13:58:20’, ‘-1’, NULL, NULL, NULL, ‘0’, ‘MENU_TEST2′, ’54’, ’57’, ‘6’, ‘TEST2222′, NULL, NULL, NULL, NULL, NULL) /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:P=3306,h=192.168.2.195,p=…,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=…,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:8022 user:mysql host:open_mysql1*/;

上面两个命令等价:
参数的意义:
–replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。
–databases= : 指定执行同步的数据库,多个用逗号隔开。
–tables= :指定执行同步的表,多个用逗号隔开。
–sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。
u=root :帐号。
p=123456 :密码。
–print :打印,但不执行命令。
–execute :执行命令。

修复方法:上面生成的语句直接在slave库执行

注意如果从库比主库记录多,得到的是delete语句,也在从库执行
-bash-4.1$ pt-table-sync –print –replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p=passwd
DELETE FROM `test`.`test_menu` WHERE `id`=’61’ LIMIT 1 /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=…,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=…,u=root lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8029 user:mysql host:open_mysql1*/;

迁移MySQL生产库的data目录,并搭建slave

大致思路:
1.通知开发停应用
2.停mysql库
3. 以read only方式打开数据库
4. 逻辑备份mysql数据库
5. 记录数据库当前的binlog位置
6. 新建目录/data/mysql_data
7. 拷贝数据库相关文件到mysql_data
8. 修改my.cnf中的mysql数据库文件路径
9. 以read only启动mysql数据库,查看binlog位置
10. 关闭mysql数据库
11. 将/data/mysql_data目录下的所有文件打包传输到备库的mysql数据库目录
12. 启动备库
13. 启动主库
14. 在备库设置主库的配置信息,启动slave
15. 过一天后删除/data目录下的mysql数据库文件
————————————————————————————
Continue reading

© 2019 Init dba

Theme by Anders NorenUp ↑