How to configure MySQL master-slave synchronization in Ubuntu 16.04

# iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT
#Delete any existing configuration to avoid multiple duplicate records $ sudo iptables -D INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT 
$ sudo iptables -D INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT 
$ sudo iptables -D INPUT -p tcp --dport 3306 -j DROP 
$ sudo iptables -D INPUT -p udp --dport 3306 -j DROP 
$ sudo iptables -D INPUT -p sctp --dport 3306 -j DROP
#Add configuration to allow only specific addresses to access the database port $ sudo iptables -A INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
$ sudo iptables -A INPUT -p udp --dport 3306 -j DROP
$ sudo iptables -A INPUT -p sctp --dport 3306 -j DROP
$ sudo iptables -L -n
#Save configuration $ sudo apt-get install iptables-persistent
$ sudo netfilter-persistent save
#The configuration is saved in the following two files: /etc/iptables/rules.v4 /etc/iptables/rules.v6.
#It is best to confirm the actual saved content, especially if other security software such as denyhosts is installed.
#Redundant rules may be recorded and need to be manually deleted from the library firewall configuration# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT
#Delete any existing configuration to avoid multiple duplicate records$ sudo iptables -D OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT
#Add configuration$ sudo iptables -A OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT
$ sudo iptables -L -n
#Save configuration $ sudo apt-get install iptables-persistent
$ sudo netfilter-persistent save
#The configuration is saved in the following two files: /etc/iptables/rules.v4 /etc/iptables/rules.v6.
#It is best to confirm the actual saved content, especially if other security software such as denyhosts is installed.
#Redundant rules may be recorded and need to be deleted manually

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
log-bin = /var/log/mysql/mysql-bin.log #Open binary log. It is commented out by default. We remove the comments. server-id = 1 #Set server-id
bind-address = 0.0.0.0 #The default is 127.0.0.1. Here we set it to any address to allow remote access. Before doing this, make sure the firewall is configured correctly, otherwise there will be security risks.

$ sudo service mysql restart
$ mysql -u root -p -e "CREATE USER 'repl'@'182.254.149.39' IDENTIFIED BY 'slavepass';" #Create user$ mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'182.254.149.39';" #Assign permissions$ mysql -u root -p -e "flush privileges;" #Refresh permissions

$ mysql -u root -p -e "SHOW MASTER STATUS;"
Enter password: 
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 333802 | | | |
+------------------+----------+--------------+------------------+-------------------+

#Here we take the backup of wordpress database as an example datadump=`which mysqldump`
mysqluser="root"
userpass="password" 
wordpressdb="wordpress"
backupwordpress_sql=$wordpressdb.`date +%Y%m%d`.sql
if $datadump -u $mysqluser --password=$userpass -h localhost --opt $wordpressdb > $backupwordpress_sql 2>&1
then
  echo " backup $wordpressdb success"
else
  echo " backup $wordpressdb error"
  exit 1
fi
#Check whether there is "-- Dump completed on" at the end of the file. If it does not exist, it means that the backup has failed.
if [ 0 -eq "$(sed '/^$/!h;$!d;g' $backupwordpress_sql | grep -c "Dump completed on")" ]; 
then
 echo " backup $wordpressdb error"
 exit 1 
else
 echo " backup $wordpressdb success"
fi

$ cd ~
$ sudo bash backup_wordpress.sh

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 2 #Set server-id, must be unique log_bin = /var/log/mysql/mysql-bin.log #It is also best to open the log

$ sudo service mysql restart
$ scp -P 22 -r [email protected]:~/wordpress.*.sql ./
#Delete a possible warning line that may prevent us from recovering data. $ sed -i "/^mysqldump: \[Warning\] Using a password on the command line interface can be insecure\./d" wordpress.*.sql
$ mysql -u root -p -e "drop database wordpress;"
$ mysql -u root -p -e "create database wordpress;"
$ mysql -u root -p wordpress < wordpress.*.sql

$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='121.199.27.227', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=333802;"

$ mysql -u root -p -e "start slave;"

$ mysql -u root -p -e "show slave status\G;"
Enter password:
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 121.199.27.227
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File:mysql-bin.000001
     Read_Master_Log_Pos: 9448236
        Relay_Log_File: VM-114-251-ubuntu-relay-bin.000002
        Relay_Log_Pos: 17780
    Relay_Master_Log_File: mysql-bin.000001
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
       Replicate_Do_DB:
     Replicate_Ignore_DB:
      Replicate_Do_Table:
    Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
    ...

# Which databases are not synchronized? binlog-ignore-db = mysql 
binlog-ignore-db = test 
binlog-ignore-db = information_schema 
# Only synchronize certain databases, other than these, do not synchronize other databases binlog-do-db = game