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

[rivetci]

[root@ip-172-31-20-242 go-mysql-elasticsearch]# ./bin/go-mysql-elasticsearch -config=./etc/river.toml
[2015/10/20 21:55:29] dump.go:107 [Info] try dump MySQL and parse
[2015/10/20 21:55:29] status.go:52 [Info] run status http server 127.0.0.1:12800
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)
[2015/10/20 21:55:29] canal.go:138 [Error] canal dump mysql err: exit status 2

Will it works with AWS RDS mysql ?

[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.