How to skip slave replication errors on Google Cloud SQL 2nd Gen

1
votes

I am in the process of migrating a database from an external server to cloud sql 2nd gen. Have been following the recommended steps and the 2TB mysqlsump process was complete and replication started. However, got an error:

'Error ''Access denied for user ''skip-grants user''@''skip-grants host'' (using password: NO)'' on query. Default database: ''mondovo_db''. Query: ''LOAD DATA INFILE ''/mysql/tmp/SQL_LOAD-0a868f6d-8681-11e9-b5d3-42010a8000a8-6498057-322806.data'' IGNORE INTO TABLE seoi_volume_update_tracker FIELDS TERMINATED BY ''^@^'' ENCLOSED BY '''' ESCAPED BY ''\'' LINES TERMINATED BY ''^|^'' (keyword_search_volume_id)'''

2 questions,

1) I'm guessing the error has come about because cloud sql requires LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE? However am quite sure on the master we run only LOAD DATA LOCAL INFILE so not sure how it changes to remove LOCAL while in replication, is that possible?

2) I can't stop the slave to skip the error and restart since SUPER privileges aren't available and so am not sure how to skip this error and also avoid it for the future while the the final sync happens. Suggestions?

1
google-cloud-platformgoogle-cloud-sql
You should be able to use LOAD DATA LOCAL INFILE. I am not sure that this error can be skipped. This might be a MySQL permissions issue with your user account. Perhaps it lacks the necessary privilege. In case the issue is the exported file, you can try this query from the Cloud SQL docs to avoid any issues. – Yasser Karout
The command failed is "LOAD DATA INFILE" and not "LOAD DATA LOCAL INFILE" which I'm guessing is an issue since without the "LOCAL" it doesn't work on cloud SQL. So now, I can't figure how to skip this error and continue with the replication. – Sam
Apologies, I misread the query. For your first question, can you clarify what was done one the master? the LOAD DATA INFILE should be ran on the Cloud SQL instance from my understanding only and not on the master. For your second question, can you clarify what you mean by skip the error? Is the error only happening for certain rows? If you are not using LOCAL the query would not run at all. To confirm, when you say replication, do you mean the import process to the main Cloud SQL instance? or do you have a replica setup as mentioned here – Yasser Karout
did you find an answer for this? I'm running into the same problem – blueether

1 Answers

1
votes

There was no way to work around the slave replication error in Google Cloud SQL, so had to come up with another way.

Since replication wasn't going to work, I had to do a copy of all the databases. However, because of the aggregate size of all my DBs being at 2TB, it was going to take a long time.

The final strategy that took the least amount of time:

1) Pre-requisite: You need to have at least 1.5X the amount of current database size in terms of disk space remaining on your SQL drive. So my 2TB DB was on a 2.7TB SSD, I needed to eventually move everything temporarily to a 6TB SSD before I could proceed with the steps below. DO NOT proceed without sufficient disk space, you'll waste a lot of your time as I did.

2) Install cloudsql-import on your server. Without this, you can't proceed and this took a while for me to discover. This will facilitate in the quick transfer of your SQL dumps to Google.

3) I had multiple databases to migrate. So if in a similar situation, pick one at a time and for the sites that access that DB, prevent any further insertions/updates. I needed to put a "Website under Maintenance" on each site, while I executed the operations outlined below.

4) Run the commands in the steps below in a separate screen. I launched a few processes in parallel on different screens. 

screen -S DB_NAME_import_process

5) Run a mysqldump using the following command and note, the output is an SQL file and not a compressed file:

mysqldump {DB_NAME} --hex-blob --default-character-set=utf8mb4 --skip-set-charset --skip-triggers --no-autocommit --single-transaction --set-gtid-purged=off > {DB_NAME}.sql

6) (Optional) For my largest DB of around 1.2TB, I also split the DB backup into individual table SQL files using the script mentioned here: https://stackoverflow.com/a/9949414/1396252

7) For each of the files dumped, I converted the INSERT commands into INSERT IGNORE because didn't want any further duplicate errors during the import process.

cat {DB_OR_TABLE_NAME}.sql | sed s/"^INSERT"/"INSERT IGNORE"/g > new_{DB_OR_TABLE_NAME}_ignore.sql

8) Create a database by the same name on Google Cloud SQL that you want to import. Also create a global user that has permission to access all the databases.

9) Now, we import the SQL files using the cloudsql-import plugin. If you split the larger DB into individual table files in Step 6, use the cat command to combine a batch of them into a single file and make as many batch files as you see appropriate.

Run the following command:

cloudsql-import --dump={DB_OR_TABLE_NAME}.sql --dsn='{DB_USER_ON_GLCOUD}:{DB_PASSWORD}@tcp({GCLOUD_SQL_PUBLIC_IP}:3306)/{DB_NAME_CREATED_ON_GOOGLE}'

10) While the process is running, you can step out of the screen session using Ctrl+a + Ctrl+d (or refer here) and then reconnect to the screen later to check on progress. You can create another screen session and repeat the same steps for each of the DBs/batches of tables that you need to import.

Because of the large sizes that I had to import, I believe it did take me a day or two, don't remember now since it's been a few months but I know that it's much faster than any other way. I had tried using Google's copy utility to copy the SQL files to Cloud Storage and then use Cloud SQL's built-in visual import tool but that was slow and not as fast as cloudsql-import. I would recommend this method up until Google fixes the ability to skip slave errors.