如何对MySQL数据库中的数据进行实时同步
\u5982\u4f55\u5bf9MySQL\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u8fdb\u884c\u5b9e\u65f6\u540c\u6b65\u5b9e\u73b0\u4e24\u4e2aMysql\u6570\u636e\u5e93\u4e4b\u95f4\u540c\u6b65\u540c\u6b65\u539f\u7406\uff1a
MySQL \u4e3a\u4e86\u5b9e\u73b0replication \u5fc5\u987b\u6253\u5f00bin-log \u9879\uff0c\u4e5f\u662f\u6253\u5f00\u4e8c\u8fdb\u5236\u7684MySQL \u65e5\u5fd7\u8bb0\u5f55\u9009\u9879\u3002MySQL \u7684bin log \u4e8c
\u8fdb\u5236\u65e5\u5fd7\uff0c\u53ef\u4ee5\u8bb0\u5f55\u6240\u6709\u5f71\u54cd\u5230\u6570\u636e\u5e93\u8868\u4e2d\u5b58\u50a8\u8bb0\u5f55\u5185\u5bb9\u7684sql \u64cd\u4f5c\uff0c\u5982insert / update / delete \u64cd\u4f5c\uff0c\u800c\u4e0d\u8bb0\u5f55
select \u8fd9\u6837\u7684\u64cd\u4f5c\u3002\u56e0\u6b64\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u4e8c\u8fdb\u5236\u65e5\u5fd7\u628a\u67d0\u4e00\u65f6\u95f4\u6bb5\u5185\u4e22\u5931\u7684\u6570\u636e\u53ef\u4ee5\u6062\u590d\u5230\u6570\u636e\u5e93\u4e2d\uff08\u5982\u679c\u4e8c\u8fdb\u5236\u65e5
\u5fd7\u4e2d\u8bb0\u5f55\u7684\u65e5\u5fd7\u9879\uff0c\u5305\u6db5\u6570\u636e\u5e93\u8868\u4e2d\u6240\u6709\u6570\u636e\uff0c\u90a3\u4e48\uff0c \u5c31\u53ef\u4ee5\u6062\u590d\u672c\u5730\u6570\u636e\u5e93\u7684\u5168\u90e8\u6570\u636e\u4e86\uff09\u3002 \u800c\u8fd9\u4e2a\u4e8c\u8fdb\u5236\u65e5\u5fd7\uff0c
\u5982\u679c\u7528\u4f5c\u8fdc\u7a0b\u6570\u636e\u5e93\u6062\u590d\uff0c\u90a3\u5c31\u662freplication \u4e86\u3002\u8fd9\u5c31\u662f\u4f7f\u7528replication \u800c\u4e0d\u7528sync \u7684\u539f\u56e0\u3002\u8fd9\u4e5f\u662f\u4e3a\u4ec0\u4e48\u8981\u8bbe
\u7f6ebin-log = \u8fd9\u4e2a\u9009\u9879\u7684\u539f\u56e0\u3002
\u5728\u540c\u6b65\u8fc7\u7a0b\u4e2d\uff0c\u6700\u91cd\u8981\u7684\u540c\u6b65\u53c2\u7167\u7269\uff0c\u5c31\u662f\u540c\u6b65\u4f7f\u7528\u90a3\u4e00\u4e2a\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\uff0c\u4ece\u90a3\u4e00\u6761\u8bb0\u5f55\u5f00\u59cb\u540c\u6b65\u3002\u4e0b\u9762\u5c31\u4ecb\u7ecd
\u4e0b\u600e\u6837\u5b9e\u73b0\u4e24\u4e2aMysql\u6570\u636e\u5e93\u4e4b\u95f4\u7684\u4e3b\u4ece\u540c\u6b65\u3002
\u4e00\u3001 \u6982\u8ff0
\u3000 MySQL\u4ece3.23.15\u7248\u672c\u4ee5\u540e\u63d0\u4f9b\u6570\u636e\u5e93\u590d\u5236\uff08replication\uff09\u529f\u80fd\uff0c\u5229\u7528\u8be5\u529f\u80fd\u53ef\u4ee5\u5b9e\u73b0\u4e24\u4e2a\u6570\u636e\u5e93\u540c\u6b65\u3001\u4e3b\u4ece\u6a21\u5f0f\u3001
\u4e92\u76f8\u5907\u4efd\u6a21\u5f0f\u7684\u529f\u80fd\u3002\u672c\u6587\u6863\u4e3b\u8981\u9610\u8ff0\u4e86\u5982\u4f55\u5728linux\u7cfb\u7edf\u4e2d\u5229\u7528mysql\u7684replication\u8fdb\u884c\u53cc\u673a\u70ed\u5907\u7684\u914d\u7f6e\u3002
\u4e8c\u3001 \u73af\u5883
\u64cd\u4f5c\u7cfb\u7edf\uff1aLinux 2.6.23.1-42.fc8 # SMP\uff08\u4e0d\u5b89\u88c5XEN\uff09
Mysql\u7248\u672c\uff1a5.0.45-4.fc8
\u8bbe\u5907\u73af\u5883\uff1aPC\uff08\u6216\u8005\u865a\u62df\u673a\uff09\u4e24\u53f0
\u4e09\u3001 \u914d\u7f6e
\u6570\u636e\u5e93\u540c\u6b65\u590d\u5236\u529f\u80fd\u7684\u8bbe\u7f6e\u90fd\u5728MySQL\u7684\u914d\u7f6e\u6587\u4ef6\u4e2d\u4f53\u73b0\uff0cMySQL\u7684\u914d\u7f6e\u6587\u4ef6\uff08\u4e00\u822c\u662fmy.cnf\uff09\uff1a\u5728\u672c\u73af\u5883\u4e0b
\u4e3a/etc/my.cnf\u3002
3.1 \u8bbe\u7f6e\u73af\u5883\uff1a
IP\u7684\u8bbe\u7f6e\uff1a
A\u4e3b\u673a IP\uff1a10.10.0.119
Mask\uff1a255.255.0.0
B\u4e3b\u673a IP\uff1a10.10.8.112
Mask\uff1a255.255.0.0
\u5728IP\u8bbe\u7f6e\u5b8c\u6210\u4ee5\u540e\uff0c\u9700\u8981\u786e\u5b9a\u4e24\u4e3b\u673a\u7684\u9632\u706b\u5899\u786e\u5b9e\u5df2\u7ecf\u5173\u95ed\u3002\u53ef\u4ee5\u4f7f\u7528\u547d\u4ee4service iptables status\u67e5\u770b\u9632\u706b\u5899\u72b6\u6001
\u3002\u5982\u679c\u9632\u706b\u5899\u72b6\u6001\u4e3a\u4ecd\u5728\u8fd0\u884c\u3002\u4f7f\u7528service iptables stop\u6765\u505c\u7528\u9632\u706b\u5899\u3002\u5982\u679c\u60f3\u542f\u52a8\u5173\u95ed\u9632\u706b\u5899\uff0c\u53ef\u4ee5\u4f7f\u7528setup\u547d
\u4ee4\u6765\u7981\u7528\u6216\u5b9a\u5236\u3002
\u6700\u7ec8\u4ee5\u4e24\u53f0\u4e3b\u673a\u53ef\u4ee5\u76f8\u4e92ping\u901a\u4e3a\u4f73\u3002
3.2 \u914d\u7f6eA\u4e3b(master) B\u4ece(slave)\u6a21\u5f0f
3.2.1 \u914d\u7f6eA \u4e3amaster
\u3000 1\u3001\u589e\u52a0\u4e00\u4e2a\u7528\u6237\u540c\u6b65\u4f7f\u7528\u7684\u5e10\u53f7\uff1a
GRANT FILE ON *.* TO \u2018backup\u2019@'10.10.8.112' IDENTIFIED BY \u20181234\u2019;
GRANTREPLICATION SLAVE ON *.* TO \u2018backup\u2019@'10.10.8.112' IDENTIFIED BY \u20181234\u2019;
\u8d4b\u4e8810.10.8.112\u4e5f\u5c31\u662fSlave\u673a\u5668\u6709File\u6743\u9650\uff0c\u53ea\u8d4b\u4e88Slave\u673a\u5668\u6709File\u6743\u9650\u8fd8\u4e0d\u884c\uff0c\u8fd8\u8981\u7ed9\u5b83REPLICATION SLAVE\u7684\u6743
\u9650\u624d\u53ef\u4ee5\u3002
2\u3001\u589e\u52a0\u4e00\u4e2a\u6570\u636e\u5e93\u4f5c\u4e3a\u540c\u6b65\u6570\u636e\u5e93\uff1a
create databbse test;
3\u3001\u521b\u5efa\u4e00\u4e2a\u8868\u7ed3\u6784\uff1a
create table mytest (username varchar(20),password varchar(20));
4\u3001\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\uff1a
\u4fee\u6539A\u7684/etc/my.cnf\u6587\u4ef6\uff0c\u5728my.cnf\u914d\u7f6e\u9879\u4e2d\u52a0\u5165\u4e0b\u9762\u914d\u7f6e\uff1a
server-id = 1 #Server\u6807\u8bc6
log-bin
binlog-do-db=test #\u6307\u5b9a\u9700\u8981\u65e5\u5fd7\u7684\u6570\u636e\u5e93
5\u3001\u91cd\u8d77\u6570\u636e\u5e93\u670d\u52a1\uff1a
service mysqld restart
\u67e5\u770bserver-id\uff1a
show variable like \u2018server_id\u2019\uff1b
\u5b9e\u4f8b\uff1a
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
6\u3001\u7528show master status/G\u547d\u4ee4\u770b\u65e5\u5fd7\u60c5\u51b5\u3002
\u6b63\u5e38\u4e3a\uff1a
mysql> show master status/G
*************************** 1. row ***************************
File: mysqld-bin.000002
positiion: 198
Binlog_Do_DB: test,test
Binlog_Ignore_DB:
1 row in set (0.08 sec)
3.2.2 \u914d\u7f6eB \u4e3aslave
1\u3001\u589e\u52a0\u4e00\u4e2a\u6570\u636e\u5e93\u4f5c\u4e3a\u540c\u6b65\u6570\u636e\u5e93\uff1a
create databbse test;
2\u3001\u521b\u5efa\u4e00\u4e2a\u8868\u7ed3\u6784\uff1a
create table mytest (username varchar(20),password varchar(20));
3\u3001\u4fee\u6539\u914d\u7f6e\u6587\u4ef6\uff1a
\u4fee\u6539B\u7684/etc/my.cnf\u6587\u4ef6\uff0c\u5728my.cnf\u914d\u7f6e\u9879\u4e2d\u52a0\u5165\u4e0b\u9762\u914d\u7f6e\uff1a
server-id=2
master-host=10.10. 0.119
master-user=backup #\u540c\u6b65\u7528\u6237\u5e10\u53f7
master-password=1234
master-port=3306
master-connect-retry=60 #\u9884\u8bbe\u91cd\u8bd5\u95f4\u969460\u79d2
replicate-do-db=test #\u544a\u8bc9slave\u53ea\u505abackup\u6570\u636e\u5e93\u7684\u66f4\u65b0
5\u3001\u91cd\u8d77\u6570\u636e\u5e93\u670d\u52a1\uff1a
service mysqld restart
\u67e5\u770bserver-id\uff1a
show variables like \u2018server_id\u2019\uff1b
\u5b9e\u4f8b\uff1a
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
6\u3001\u7528show slave status/G\u547d\u4ee4\u770b\u65e5\u5fd7\u60c5\u51b5\u3002
\u6b63\u5e38\u4e3a\uff1a
mysql> show slave status/G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.0.119
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 236
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test,test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 236
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: 0
1 row in set (0.01 sec)
3.2.3 \u9a8c\u8bc1\u914d\u7f6e
\u5206\u522b\u4f7f\u7528insert, delete , update\u5728A\u4e3b\u673a\u8fdb\u884c\u589e\u5220\u6539\u67e5\u6570\u636e\u5e93\uff1b\u67e5\u770bB\u4e3b\u673a\u7684\u6570\u636e\u5e93\u662f\u5426\u4e0eA\u4e3b\u673a\u4e00\u81f4\uff1b\u82e5\u4e00\u81f4\uff0c\u5219\u914d
\u7f6e\u6210\u529f\u3002
3.3\u53cc\u673a\u4e92\u5907\u6a21\u5f0f
\u5982\u679c\u5728A\u4e3b\u673a\u52a0\u5165slave\u8bbe\u7f6e\uff0c\u5728B\u4e3b\u673a\u52a0\u5165master\u8bbe\u7f6e\uff0c\u5219\u53ef\u4ee5\u505aB->A\u7684\u540c\u6b65\u3002
1\u3001\u5728A\u4e3b\u673a\u7684\u914d\u7f6e\u6587\u4ef6\u4e2d mysqld\u914d\u7f6e\u9879\u52a0\u5165\u4ee5\u4e0b\u8bbe\u7f6e\uff1a
master-host=10.10.8.112
master-user=backup
master-password=1234
replicate-do-db=test
master-connect-retry=10
2\u3001\u5728B\u7684\u914d\u7f6e\u6587\u4ef6\u4e2d mysqld\u914d\u7f6e\u9879\u52a0\u5165\u4ee5\u4e0b\u8bbe\u7f6e\uff1a
log-bin
binlog-do-db=test
\u3000\u3000\u6ce8\u610f\uff1a\u5f53\u6709\u9519\u8bef\u4ea7\u751f\u65f6\uff0c*.err\u65e5\u5fd7\u6587\u4ef6\u540c\u6b65\u7684\u7ebf\u7a0b\u9000\u51fa\uff0c\u5f53\u7ea0\u6b63\u9519\u8bef\u540e\uff0c\u8981\u8ba9\u540c\u6b65\u673a\u5236\u8fdb\u884c\u5de5\u4f5c\uff0c\u8fd0\u884cslave
start\u3002
\u91cd\u8d77A\u3001B\u673a\u5668\uff0c\u5219\u53ef\u4ee5\u5b9e\u73b0\u53cc\u5411\u7684\u70ed\u5907\u4efd\u3002
\u603b\u4e4b\uff0c\u6210\u529f\u5b8c\u6210\u4ee5\u4e0a\u914d\u7f6e\u540e\uff0c\u5728\u4e3b\u670d\u52a1\u5668A\u7684test\u5e93\u91cc\u6dfb\u52a0\u6570\u636e\u6216\u5220\u9664\u6570\u636e\uff0c\u5728\u4ece\u670d\u52a1\u5668B\u7684test\u5e93\u91cc\u9a6c\u4e0a\u4e5f\u80fd
\u770b\u5230\u76f8\u5e94\u7684\u53d8\u66f4\u3002\u4e24\u53f0\u670d\u52a1\u5668\u7684\u540c\u6b65\u64cd\u4f5c\u53ef\u4ee5\u8bf4\u662f\u77ac\u95f4\u5b8c\u6210\u7684\u3002
\u524d\u63d0\u6761\u4ef6
\u60a8\u9700\u8981\u5728\u60a8RDS for MySQL\u6240\u5728\u7684\u4e91\u8d26\u53f7\u4e0b\u5f00\u901a\u963f\u91cc\u4e91\u6570\u636e\u4f20\u8f93\u670d\u52a1\u3002\u5e76 \u70b9\u51fb\u6b64\u5904
\u4e0b\u8f7ddts-ads-writer\u63d2\u4ef6\u5230\u60a8\u7684\u4e00\u53f0\u670d\u52a1\u5668\u4e0a\u5e76\u89e3\u538b\uff08\u9700\u8981\u8be5\u670d\u52a1\u5668\u53ef\u4ee5\u8bbf\u95ee\u4e92\u8054\u7f51\uff0c\u5efa\u8bae\u4f7f\u7528\u963f\u91cc\u4e91ECS\u4ee5\u6700\u5927\u9650\u5ea6\u4fdd\u969c\u53ef\u7528\u6027\uff09\u3002\u670d\u52a1\u5668\u4e0a\u9700\u8981\u6709Java
6\u6216\u4ee5\u4e0a\u7684\u8fd0\u884c\u73af\u5883\uff08JRE/JDK\uff09\u3002
\u64cd\u4f5c\u6b65\u9aa4
1. \u5728\u5206\u6790\u578b\u6570\u636e\u5e93\u4e0a\u521b\u5efa\u76ee\u6807\u8868\uff0c\u6570\u636e\u66f4\u65b0\u7c7b\u578b\u4e3a\u5b9e\u65f6\u5199\u5165\uff0c\u5b57\u6bb5\u540d\u79f0\u548cMySQL\u4e2d\u7684\u5efa\u8bae\u5747\u76f8\u540c\uff1b
2. \u5728\u963f\u91cc\u4e91\u6570\u636e\u4f20\u8f93\u7684\u63a7\u5236\u53f0\u4e0a\u521b\u5efa\u6570\u636e\u8ba2\u9605\u901a\u9053\uff0c\u5e76\u8bb0\u5f55\u8fd9\u4e2a\u901a\u9053\u7684ID\uff1b
\uff08\u89c1\uff1a https://help.aliyun.com/document_detail/dts/Getting-Started/data-subscription.html\uff09\uff0c
3. \u914d\u7f6edts-ads-writer/app.conf\u6587\u4ef6\uff0c\u914d\u7f6e\u65b9\u5f0f\u5982\u4e0b\uff1a
\u6240\u6709\u914d\u7f6e\u5747\u4fdd\u5b58\u5728app.conf\u4e2d\uff0c\u8fd0\u884c\u524d\u8bf7\u4fdd\u8bc1\u914d\u7f6e\u6b63\u786e\uff1b\u4fee\u6539\u914d\u7f6e\u540e\uff0c\u8bf7\u91cd\u542fwriter
\u57fa\u672c\u914d\u7f6e\uff1a
{
"dtsAccessId": "", // \u62e5\u6709\u6570\u636e\u8ba2\u9605\u901a\u9053\u7684\u4e91\u8d26\u53f7\u7684accessId, \u5fc5\u987b\u914d\u7f6e
"dtsAccessKey": "", // \u62e5\u6709\u6570\u636e\u8ba2\u9605\u901a\u9053\u7684\u4e91\u8d26\u53f7\u7684accessKey, \u5fc5\u987b\u914d\u7f6e
"dtsTunnelId": "", // \u6570\u636e\u8ba2\u9605\u901a\u9053\u7684id, \u5fc5\u987b\u914d\u7f6e; \u6ce8\u610f\u662fid\uff0c\u4e0d\u662f\u901a\u9053\u540d\u79f0
"adsUserName": "", // \u8bbf\u95ee\u60a8\u7684\u5206\u6790\u578b\u6570\u636e\u5e93\u7684\u7528\u6237\u540d\uff08accessId\uff09, \u5fc5\u987b\u914d\u7f6e
"adsPassword": "", // \u8bbf\u95ee\u60a8\u7684\u5206\u6790\u578b\u6570\u636e\u5e93\u7684\u5bc6\u7801(accessKey), \u5fc5\u987b\u914d\u7f6e
"adsJdbcUrl": "", // \u8bbf\u95ee\u5206\u6790\u578b\u6570\u636e\u5e93\u7684jdbc\u8fde\u63a5\u4e32, \u5fc5\u987b\u914d\u7f6e\uff08\u683c\u5f0fjdbc:mysql://ip:port/dbname\uff09
"tables": [
{
"source": {
"primaryKeys": [""] // \u4e3b\u952e\u5b9a\u4e49, \u5fc5\u987b\u914d\u7f6e; \u6ce8\u610fRDS\u548c\u5206\u6790\u578b\u6570\u636e\u5e93\u4e2d\u7684\u4e3b\u952e\u5b9a\u4e49\u5fc5\u987b\u4e00\u81f4
"db": "", // \u6e90\u5934RDS\u7684db\u540d\u79f0, \u5fc5\u987b\u914d\u7f6e
"table": "", // \u6e90\u5934RDS\u7684table\u540d\u79f0, \u5fc5\u987b\u914d\u7f6e
"skipColumns": ["col1"] // \u53ef\u9009\uff0c\u82e5\u5728\u6b64\u914d\u7f6e\u4e86RDS\u8868\u67d0\u5217\u540d\uff0c\u5219\u8be5\u5217\u4e0d\u4f1a\u540c\u6b65
},
"target": {
"table": "" // \u5206\u6790\u578b\u6570\u636e\u5e93\u8868\u7684table\u540d\u79f0, \u5fc5\u987b\u914d\u7f6e
},
"columnMapping": {
"": "" // rds\u8868\u548cads\u8868\u7684\u5217\u5bf9\u5e94\u5173\u7cfb\uff1akey\u4e3ards\u7684\u5217\u540d, value\u4e3a\u5206\u6790\u578b\u6570\u636e\u5e93\u7684\u5217\u540d\uff0c\u9009\u586b\uff0c\u4e0d\u586b\u5219\u6309\u7167\u5217\u540d\u4e00\u4e00\u5bf9\u5e94
}
}
]
}
tables\u8282\u70b9\u7684\u914d\u7f6e\u793a\u4f8b\uff0c
\u8868\u793ards_db\u5e93\u4e0b\u7684rds_table\u8868\u5bf9\u5e94ads_table\u8868\uff0c\u5e76\u4e14rds_table\u8868\u7684col1\u5217\u5bf9\u5e94ads_table\u8868\u7684col1_ads\u5217\uff0c
rds_table\u8868\u7684col2\u5217\u5bf9\u5e94ads_table\u8868\u7684col2_ads\u5217
"tables": [
{
"source": {
"primaryKeys": [
"col1",
"col2"
],
"db": "rds_db",
"table": "rds_table"
},
"target": {
"table": "ads_table"
},
"columnMapping": {
"col1": "col1_ads",
"col2": "col2_ads"
}
}
]
\u6ce8\u610f\u4e8b\u9879\uff1a
1\uff09RDS for MySQL\u8868\u548c\u5206\u6790\u578b\u6570\u636e\u5e93\u4e2d\u8868\u7684\u4e3b\u952e\u5b9a\u4e49\u5fc5\u987b\u5b8c\u5168\u4e00\u81f4\uff1b\u5982\u679c\u4e0d\u4e00\u81f4\u4f1a\u51fa\u73b0\u6570\u636e\u4e0d\u4e00\u81f4\u95ee\u9898\u3002\u5982\u679c\u9700\u8981\u8c03\u6574RDS/\u5206\u6790\u578b\u6570\u636e\u5e93\u8868\u7684\u4e3b\u952e\uff0c\u5efa\u8bae\u5148\u505c\u6b62writer\u8fdb\u7a0b\uff1b
2\uff09\u4e00\u4e2a\u63d2\u4ef6\u8fdb\u7a0b\u4e2d\u5206\u6790\u578b\u6570\u636e\u5e93db\u53ea\u80fd\u662f\u4e00\u4e2a\uff0c\u7531adsJdbcUrl\u6307\u5b9a\uff1b
3\uff09\u4e00\u4e2a\u63d2\u4ef6\u8fdb\u7a0b\u53ea\u80fd\u5bf9\u5e94\u4e00\u4e2a\u6570\u636e\u8ba2\u9605\u901a\u9053\uff1b\u5982\u679c\u66f4\u65b0\u901a\u9053\u4e2d\u7684\u8ba2\u9605\u5bf9\u8c61\u65f6\uff0c\u9700\u8981\u91cd\u542f\u8fdb\u7a0b
4\uff09RDS for MySQL\u4e2dDDL\u64cd\u4f5c\u4e0d\u505a\u540c\u6b65\u5904\u7406\uff1b
5\uff09\u66f4\u65b0app.conf\u9700\u8981\u91cd\u542f\u63d2\u4ef6\u8fdb\u7a0b\u624d\u80fd\u751f\u6548\uff1b
6\uff09\u5982\u679c\u5de5\u5177\u51fa\u73b0bug\u6216\u67d0\u79cd\u5176\u5b83\u539f\u56e0\u9700\u8981\u91cd\u65b0\u540c\u6b65\u5386\u53f2\u6570\u636e\uff0c\u53ea\u80fd\u56de\u6eaf\u6700\u8fd124\u5c0f\u65f6\u7684\u6570\u636e\uff08\u5728\u963f\u91cc\u4e91\u6570\u636e\u4f20\u8f93\u7684\u63a7\u5236\u53f0\u4e2d\u4fee\u6539\u6d88\u8d39\u4f4d\u70b9\uff09\uff1b
7\uff09\u63d2\u4ef6\u7684\u6700\u5927\u540c\u6b65\u6027\u80fd\u4e0e\u8fd0\u884c\u63d2\u4ef6\u7684\u670d\u52a1\u5668\u7684\u4e92\u8054\u7f51\u5e26\u5bbd\u548c\u78c1\u76d8IOPS\u6210\u6b63\u6bd4\u3002
4. \u8fd0\u884cdts-ads-writer/bin/startup.sh(sh bin/startup.sh)\uff1b
5. \u914d\u7f6e\u76d1\u63a7\u7a0b\u5e8f\u76d1\u63a7\u8fdb\u7a0b\u5b58\u6d3b\u548c\u65e5\u5fd7\u4e2d\u7684\u5e38\u89c1\u9519\u8bef\u7801\u3002
logs\u76ee\u5f55\u4e0b\u7684\u65e5\u5fd7\u4e2d\u7684\u5f02\u5e38\u4fe1\u606f\u5747\u4ee5ErrorCode=XXXX ErrorMessage=XXXX\u5f62\u5f0f\u7ed9\u51fa\uff0c\u53ef\u4ee5\u8fdb\u884c\u76d1\u63a7
实现两个Mysql数据库之间同步同步原理：
MySQL 为了实现replication 必须打开bin-log 项，也是打开二进制的MySQL 日志记录选项。MySQL 的bin log 二
进制日志，可以记录所有影响到数据库表中存储记录内容的sql 操作，如insert / update / delete 操作，而不记录
select 这样的操作。因此，我们可以通过二进制日志把某一时间段内丢失的数据可以恢复到数据库中（如果二进制日
志中记录的日志项，包涵数据库表中所有数据，那么， 就可以恢复本地数据库的全部数据了）。 而这个二进制日志，
如果用作远程数据库恢复，那就是replication 了。这就是使用replication 而不用sync 的原因。这也是为什么要设
置bin-log = 这个选项的原因。
绛旓細鏂规硶濡備笅锛1銆侀鍏堟墦寮Mysql鍛戒护琛岀紪杈戝櫒锛岃繛鎺Mysql鏁版嵁搴锛2銆佷娇鐢ㄦ墍瑕佹搷浣鐨勬暟鎹搴擄紝鍏堟樉绀轰竴涓鏁版嵁搴撲腑鐨琛紱3銆佹樉绀轰竴涓嬭〃缁撴瀯锛屼簡瑙ｄ竴涓嬭〃涓殑鍒楋紱4銆佸悜琛ㄦ彃鍏ユ暟鎹紝insert into 琛ㄥ悕 锛堝垪鍚嶏級 values 锛堝硷級;5銆佹煡璇㈣〃鍙互鐪嬪埌鎵鎻掑叆鐨勬暟鎹紝select * from 琛ㄥ悕锛6銆佸彲浠ュ啀鎻掑叆涓涓暟鎹...
绛旓細杩欎釜鍙互鍚son鏍煎紡鐨勫瓧绗︿覆瑙ｆ瀽鎴愭暟缁刯son_decode()鍑芥暟锛屽彉鎴愭暟缁勪互鍚庡氨鍙互鏂逛究鎿嶄綔浜嗭紝鍙互鍒犻櫎鏁扮粍涓殑浠绘剰涓椤癸紝涔熷彲浠ュ鍔犱竴椤规瘮濡傦細array_push($data,['sort'=>3,'catentryId'=>10003]),鍐嶅彉鎴恓son鏍煎紡鐨勫瓨鍏鏁版嵁搴銆傛柟娉曟湁澶氱锛岃繖閲岀畝鍗曠殑绀轰緥涓 ...
绛旓細娌℃湁鐢ㄨ繃mysql鍔犲瘑锛屼絾涓鑸兘鏄敤鏈嶅姟绔瑷鎶婃暟鎹姞瀵嗗悗瀛樺叆鏁版嵁搴撲腑锛屾瘮濡傦紝鍦≒HP閲岀敤md5鍑芥暟鎶婄敤鎴风殑瀵嗙爜鍔犲瘑涔嬪悗瀛樺叆鏁版嵁涓紝涓鑸兘 鏄繖涔堣В鍐崇殑銆
绛旓細mysql鏁版嵁搴鍒嗗紑瀵煎嚭 mysql鏄垜浠涔犺繃绋嬩腑缁忓父瑕佺敤鍒鐨勬暟鎹搴擄紝涓庢鍚屾椂锛濡備綍瀵煎嚭鏁版嵁涔熸槸涓涓繀椤昏瀛︿範鐨勬妧鑳 宸ュ叿/鍘熸枡 mysql Navicat For MySql 杩炴帴鏁版嵁搴 1鍙屽嚮鎵撳紑Navicat,鐐瑰嚮鈥滆繛鎺モ濓紝鍦ㄥ脊鍑烘涓緭鍏ヨ繛鎺ュ悕锛堣繛鎺ュ悕鍙殢渚垮畾涔夛紝鍙鑳借瘑鍒竻妤氾紝渚嬪conn锛夛紝杈撳叆鐢ㄦ埛鍚嶏紝瀵嗙爜锛岀偣鍑烩滆繛鎺ユ祴璇...
绛旓細5銆佹垜浠垱寤轰竴涓鏁版嵁搴杩炴帴绫伙紝鐢ㄤ簬閰嶇疆鏁版嵁搴撹繛鎺ョ殑灞炴э紝濡傛暟鎹簱鐨勯┍鍔ㄣ乁RL銆佺敤鎴峰悕鍜屽瘑鐮併6銆佹垜浠垱寤哄畬鏁版嵁搴撶殑杩炴帴鍚庯紝浣跨敤getConnection()鏂规硶鑾峰彇杩炴帴锛7銆佷娇鐢╟lose()鏂规硶鍏抽棴杩炴帴銆傛垜浠湪浣跨敤mysql鏃堕渶瑕佹坊鍔爏mysql椹卞姩鐨刯ar鍖呫8銆佹帴涓嬫潵鎴戜滑鍒涘缓MVC妯″紡涓殑鏁版嵁鎸佷箙鍖栧眰锛岄鍏堟垜浠垱寤轰竴涓...
绛旓細杩欎釜宸ュ叿灏嗘偍鐨勬暟鎹簱涓鎵鏈夌殑璁捐鍊掕浆銆傚洜涓烘墍鏈夌殑涓滆タ閮借鍖呭惈鍒颁簡涓涓枃鏈枃浠朵腑銆傝繖涓枃鏈枃浠跺彲浠ョ敤涓涓畝鍗曠殑鎵瑰鐞嗗拰涓涓悎閫係QL璇彞瀵煎洖鍒MySQL涓傝繖涓伐鍏蜂护浜洪毦浠ョ疆淇″湴绠鍗曡屽揩閫熴傚喅涓嶄細鏈夊崐鐐硅浜哄ご鐤煎湴鍦版柟銆傚洜姝わ紝濡傛灉鎮ㄥ儚瑁呰浇鏁翠釜鏁版嵁搴揗eet_A_Geek鐨勫唴瀹瑰埌涓涓枃浠朵腑锛屽彲浠ヤ娇鐢ㄤ笅闈㈢殑...
绛旓細鈶″皢鎺掑ソ鐨勬暟鎹鎻掑叆鏂板缓鐨勪复鏃惰〃 Insert into tableName(鏃ユ湡,閲戦,鍟嗗搧)select 鏃ユ湡,閲戦,鍟嗗搧 from 鍘熷琛 order by 鏃ユ湡 鈶㈠皢鍘熻〃鏀规垚鍏朵粬鍚嶏紝骞跺皢绗簩姝ュ缓鐨勪腑闂磋〃鏀瑰悕涓哄師琛ㄥ悕 alter table 琛1 rename to 琛2 鎴戞墜澶存病鏈mysql鍙兘鎸夊嵃璞″啓鍑轰唬鐮侊紝浣犳嬁鍘昏瘯涓涓嬪惂銆傛垜鍗拌薄涓璵ysql瀵筽l鐨勬敮鎸...
绛旓細鍦MySQL 8.0 涔嬪墠锛 鎴戜滑鍋囪涓涓嬫湁涓鏉＄儌SQL锛mysqlselect * from t1 order by rand() ;浠ュ涓嚎绋嬪湪璺戯紝瀵艰嚧CPU琚窇婊′簡锛屽叾浠栫殑璇锋眰鍙兘琚樆濉炶繘涓嶆潵銆傞偅杩欑鎯呭喌鎬庝箞鍔烇紵澶ф鏈変互涓嬪嚑绉嶈В鍐冲姙娉曪細璁剧疆max_execution_time 鏉ラ樆姝㈠お闀跨殑璇籗QL銆傞偅鍙兘瀛樺湪鐨勯棶棰樻槸浼氭妸鎵鏈夐暱SQL閮界粰KILL 鎺夈
绛旓細鍒殑涓嶉渶瑕侊紝閮藉啓SQL璇彞灏卞彲浠ヤ簡锛屽湪浣犵殑鏌ヨ璇彞鐢ㄤ笅闈㈢殑璇彞鏇挎崲鍗冲彲銆傛帓搴忥細select * from t_user order by name asc/desc(瀵筺ame瀛楁 鍗囧簭/闄嶅簭)绛涢夛細select * from t_user where name like '%xhj%'(name瀛楁鍖呭惈xhj鐨勫皢琚煡璇㈠嚭鏉)...
绛旓細Ø 棣栧厛锛屾垜浠渶瑕佸紑鍚疢ySQL鏈嶅姟鍣ㄧ殑浜岃繘鍒舵棩蹇楀姛鑳斤紝鍏跺疄鐜版柟娉曟湁寰堝绉嶏紝鏈甯哥敤鐨勬槸鍦MySQL鐨閰嶇疆鏂囦欢鐨mysqld椤逛腑鍔犲叆log-bin=[filepath]椤癸紱涔熷彲浠ヤ娇鐢╩ysqld 鈥搇og-bin=[filepath]閲嶆柊鍚姩MySQL鏈嶅姟鍣ㄣØ 鍏舵锛屼娇鐢╩ysqldump瀵规暟鎹簱杩涜瀹屽叏澶囦唤锛屽畠鍙互瀹炵幇瀵规暟鎹嵁鐨勮仈鏈猴紝闈為樆濉...
