Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045) about go-mysql-elasticsearch

[siddontang]

I knew that we have no proper privilege to run mysqldump with --master-data flag in AWS RDS, so it is not allowed in AWS RDS.

[tpeng]

I had same issue when use go-mysql-elasticsearch with Amazon RDS MySQL server. managed to workaround it by following:

1. use mysqldump to dump the RDS MySQL server and restore it to local MySQL db.
2. shutdown RDS MySQL server
3. start go-mysql-elasticsearch with my_addr set to local db and wait until it's finished
4. stop go-mysql-elasticsearch and delete var/master.info
5. restart RDS MySQL server
6. restart go-mysql-elasticsearch with my_addr to set to RDS MySQL server and mysqldump to empty

it seems working properly, @siddontang any thoughts on this? thanks

[siddontang]

@tpeng

go-mysql-elasticsearch will try to use mysqldump to dump all data then sync with the position saved in mysqldump.

We can support starting from a binlog position to avoid using mysqldump.
Btw if the binlog is truncated, we may have not enough data to sync.

[tpeng]

@siddontang thanks for your reply.

the problem is as you posted above: "no proper privilege to run mysqldump with --master-data flag in AWS RDS, so it is not allowed in AWS RDS."

from what i tried yesterday, it seems still the case. is go-mysql-elasticsearch has way to avoid using --master-data flag to dump all the data?

starting from binlog position sounds good, but if i run show binary logs; it only show 2 rows. i would expect there should be more rows to keep all the binlog files, does RDS delete obsolete binlog files?

[siddontang]

Hi @tpeng

go-mysql-elasticsearch doesn't support starting from a position now, but you can update the master.info directly to let it work.

I have another way if possible:

1. Get the current binlog position with show master status
2. Dump the MySQl without --master-data
3. Sync from the got postion in step 1.

This can work well even you insert new data when do step 2 because binlog is row format, so any change synced to elasticsearch is idempotent.

[tpeng]

Hey @siddontang

just to clarity, the 3 steps above still requires dump the initial data from MySQL server (e.g. run mysqldump from command line), right?

[siddontang]

@tpeng

No, we can guarantee it.

[siddontang]

I will support this later if mysqldump with --master-data failed. Maybe this week.

[tpeng]

thanks @siddontang, that would be great. but i wonder how is it going to work when RDS MySQL deleted expired binlog files?

[siddontang]

@tpeng

If the binlog files are deleted after we dumping the data, we may meet an error, sadly.

[tpeng]

@siddontang

let me know when you done with the change, i'm happy to test it!

thanks,

[siddontang]

Hi @tpeng
Can you help me to make sure we can run show master status in RDS?

[tpeng]

Yes, we can

…

On Sat, 15 Jul 2017 at 10:33, siddontang ***@***.***> wrote: Hi @tpeng <https://github.com/tpeng> Can you help me to make sure we can run show master status in RDS? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#29 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAXNSjg3V3nRC4HCPFYTUjoFeSQXBldhks5sOHlcgaJpZM4GSqKs> .

[siddontang]

Hi @tpeng

I post a new branch siddontang/skip-master-data to fix this problem, see #116

Can you help me to check in RDS, you should set skip_master_data = true in the config file then run.

[tpeng]

it seems working quite well. see the following logs:

...
2017/07/16 23:37:31 status.go:52: [info] run status http server 127.0.0.1:12800
2017/07/16 23:37:31 dump.go:114: [info] skip master data, get current binlog position (mysql-bin-changelog.023727, 422)
2017/07/16 23:37:31 dump.go:120: [info] try dump MySQL and parse
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2017/07/16 23:39:52 dump.go:126: [info] dump MySQL and parse OK, use 140.72 seconds, start binlog replication at (mysql-bin-changelog.023727, 422)
2017/07/16 23:39:52 sync.go:21: [info] start sync binlog at (mysql-bin-changelog.023727, 422)
2017/07/16 23:39:52 binlogsyncer.go:268: [info] begin to sync binlog from position (mysql-bin-changelog.023727, 422)
...

thanks @siddontang

however seems to me using show master status to get binlog position seems superior than run mysqldump with --master-data, would it be better to always use it, so skip_master_data can be avoid?

[siddontang]

Hi @tpeng

Skipping master data will sync duplicated data if we insert new data before dumping although this may be not a big problem for ES.

The old mechanism has been running for a long time and it works well, I will remove the config later. Btw, you can send me a PR to do this :-)

[siddontang]

I will close the issue, it is fixed now.