/
mysql.ad
120 lines (86 loc) · 4.77 KB
/
mysql.ad
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
=== MySQL
MySQL supports several storage engines for different table types.
However, SymmetricDS requires a storage engine that handles transactions.
The recommended storage engine is InnoDB, which is included by default in MySQL 5.0 distributions.
Either select the InnoDB engine during installation or modify your server configuration.
To make InnoDB the default storage engine, modify your MySQL server configuration file (my.ini on Windows, my.cnf on Unix):
----
default-storage_engine = innodb
----
Alternatively, you can convert tables to the InnoDB storage engine with the following command:
----
alter table t engine = innodb;
----
On MySQL 5.0, the SymmetricDS user needs the SUPER privilege in order to create triggers.
----
grant super on *.* to symmetric;
----
On MySQL 5.1, the SymmetricDS user needs the TRIGGER, PROCESS, and CREATE ROUTINE privileges in order to create triggers and functions.
----
grant trigger on *.* to symmetric;
grant create routine on *.* to symmetric;
grant alter routine on *.* to symmetric;
grant process on *.* to symmetric;
----
Starting in MySQL 5.7.6, the "PROCESS" privilege is also required for the MySQL user that is modifying the application tables.
This is required to look up the transaction id. Internally, the trigger will submit this query during an insert/update/delete:
select TRX_ID from INFORMATION_SCHEMA.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID();
----
grant process on *.* to db_user;
----
MySQL allows '0000-00-00 00:00:00' to be entered as a value for datetime and timestamp columns.
JDBC cannot deal with a date value with a year of 0. In order to work around this SymmetricDS can be configured to treat date and time
columns as varchar columns for data capture and data load. To enable this feature set the db.treat.date.time.as.varchar.enabled property to true.
If you are using UTF-8 encoding in the database, you might consider using the characterEncoding parameter in the JDBC URL.
----
jdbc:mysql://hostname/databasename?tinyInt1isBit=false&characterEncoding=utf8
----
If you are using Amazon Web Services to run the MySQL instance, Amazon by default uses binary log replication and disables the creation
of triggers. In order to allow SymmetricDS to function correctly, the parameter 'log_bin_trust_function_creators' needs to be set
to a value of '1'. You can set this value by logging into the database as root and enter the following command:
----
mysql -u USERNAME -p
set global log_bin_trust_function_creators=1;
----
If you can not log into the server as root, then you can set the parameter in a new parameter group on the Relational Database Services
(RDS) web console. See the documentation from RDS for details.
ifdef::pro[]
If you are using log-based capture, then you must start MySQL using the `+--log-bin+` option. In addition, you must set the following system variables:
----
binlog_format=ROW
enforce_gtid_consistency=ON
gtid_mode=ON
----
Note that there are four possible values for `+gtid_mode+` and it must be set one step at a time. If `+gtid_mode+` is set to "OFF", then it must be set to
"OFF_PERMISSIVE" and then "ON_PERMISSIVE" before it can be set to "ON". Also note that in versions earlier than MySQL 5.7.6, the `+gtid_mode+` variable must
be set by starting the server with the `+--gtid-mode=ON+` option. Prior to MySQL 5.7.5, the `+--log-slave-updates+` option must also be used at startup.
endif::pro[]
.Supported Data Types
|===
|Data Type|Supported?
|TinyInt, SmallInt, Int, MediumInt, BigInt|Yes
|Decimal, Numeric|Yes
|Float, Double|Yes
|Bit|Yes
|Date, DateTime, TimeStamp, Time, Year|Yes
|Char, Varchar|Yes
|Binary, VarBinary|Yes
|TinyBlob, Blob, MediumBlob, BigBlob|Yes
|TinyText, Text, MediumText, BigText|Yes
|Enum|No
|Set|No
|Geometry, Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLinestring, MultiPolygon|No
|===
==== Loading Data
When registering a MySQL database with SymmetricDS, bulk loading will be checked by default.
image::images/appendix/mysql-enable-bulk-loading.png[]
To succesfully bulk load, once your MySQL database is registered with SymmetricDS, go to the top of the screen and select the MySQL node you want to bulk load from the drop down list of your nodes.
image::images/appendix/mysql-selecting-node-1.png[]
image::images/appendix/mysql-selecting-node-2.png[]
Then, go to the Manage tab, and go to Startup Parameters and set mysql.bulk.load.local to true, and restart SymmetricDS.
image::images/appendix/mysql-bulk-load-local-true.png[]
In order to bulk load, your MySQL database must have the local_infile variable set to ON. In order to set this, you can run the query below to set the variable and have it remain on, even upon the restarting of your database.
----
SET PERSIST local_infile=1;
----
After completing these steps, your MySQL database should be configured to bulk load.