2017-11-21 11:55:12 |
jianhaiqing |
description |
show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`lastlogininfo` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
select * from user;
+----+-------------+-------------------------------------------------------------------------------+
| id | name | lastlogininfo |
+----+-------------+-------------------------------------------------------------------------------+
| 1 | lucy | {"ip": "192.168.1.1", "time": "2015-01-01 13:00:00", "result": "fail"} |
| 2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
| 3 | jianhaiqing | {"ip": "192.168.1.1", "time": "2017-10-25 17:10:44.000000", "result": "fail"} |
+----+-------------+-------------------------------------------------------------------------------
# mydumper -v 3 -h `hostname -i ` -u root -p xxx -P 3307 -r 300000 --regex '^(?!(mysql|test))' --skip-tz-utc -o /data/backup/mydumper/
# cat jianhaiqing.user.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
INSERT INTO `user` VALUES
(1,"lucy","{\"ip\": \"192.168.1.1\", \"time\": \"2015-01-01 13:00:00\", \"result\": \"fail\"}"),
(2,"bobo","{\"ip\": \"192.168.1.0\", \"time\": \"2015-10-07 06:44:00\", \"result\": \"success\"}"),
(3,"jianhaiqing","{\"ip\": \"192.168.1.1\", \"time\": \"2017-10-25 17:10:44.000000\", \"result\": \"fail\"}");
mysql> source /data/backup/mydumper/jianhaiqing.user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'
mydumper -V
mydumper 0.9.3, built against MySQL 5.7.19-17 |
show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`lastlogininfo` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
select * from user;
+----+-------------+-------------------------------------------------------------------------------+
| id | name | lastlogininfo |
+----+-------------+-------------------------------------------------------------------------------+
| 1 | lucy | {"ip": "192.168.1.1", "time": "2015-01-01 13:00:00", "result": "fail"} |
| 2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
| 3 | jianhaiqing | {"ip": "192.168.1.1", "time": "2017-10-25 17:10:44.000000", "result": "fail"} |
+----+-------------+-------------------------------------------------------------------------------
# mydumper -v 3 -h `hostname -i ` -u root -p xxx -P 3307 -r 300000 --regex '^(?!(mysql|test))' --skip-tz-utc -o /data/backup/mydumper/
# cat jianhaiqing.user.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
INSERT INTO `user` VALUES
(1,"lucy","{\"ip\": \"192.168.1.1\", \"time\": \"2015-01-01 13:00:00\", \"result\": \"fail\"}"),
(2,"bobo","{\"ip\": \"192.168.1.0\", \"time\": \"2015-10-07 06:44:00\", \"result\": \"success\"}"),
(3,"jianhaiqing","{\"ip\": \"192.168.1.1\", \"time\": \"2017-10-25 17:10:44.000000\", \"result\": \"fail\"}");
mysql> source /data/backup/mydumper/jianhaiqing.user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'
mydumper -V
mydumper 0.9.3, built against MySQL 5.7.19-17 |
|