Troubleshooting MySQL Master/Slave replication

Here are the instruction about how to manual sync the master and slave DBs.

Run on master MySQL shell:

mysql> RESET MASTER;
Query OK, 0 rows affected (0.13 sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysqld-bin.000001 |     1990 |              |                  | 8cfd65ec-7caa-11ea-a450-901b0e959f81:1-4 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Take a note for File and Position.

Dump all databases with mysqldump:

mysqldump -u'user' -p'pwd' -h '127.0.0.1' -P '3306' --set-gtid-purged=OFF --column-statistics=0 --no-tablespaces --single-transaction --all-databases --triggers --routines --master-data --flush-privileges | gzip -5 > backup-j.sql.gz

After data is dumped run on master MySQL console - to unlock the tables:

mysql> UNLOCK TABLES;

Copy the backup from master and import it. After the import is finished - restart MySQL daemon:

scp -i ~/.ssh/key.pem user@master_ip:~/backup-j.sql.gz ./
gunzip backup-j.sql.gz
mysql -u root -p'slave_password' -h localhost < ./backup-j.sql
systemctl restart mysqld

On slave try to login with master credentials:

mysql -u master -p -h master_ip

On slave MySQL console issue the following to connect to master and start slave. Enter the values noted before:

mysql> CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_PORT=4306, MASTER_USER='master_user', MASTER_PASSWORD='master_pwd', MASTER_LOG_FILE='mysqld-bin.000001', MASTER_LOG_POS=1990, GET_MASTER_PUBLIC_KEY=1;
mysql> START SLAVE;

After some time, check slave status:

mysql> SHOW SLAVE STATUS \G;

Finally, try to create a new database on master and check if it was replicated to slave. Delete new-created database.