ubuntu 16.04 Configuration Method of MySQL Master Slave Synchronization

Preparatory work

1. The master-slave database version is best

2. Maintain the data in the master-slave database

Master database: 121.199. 27.227/ubuntu 16.04 MySQL 5.7. 21 (Alibaba Cloud)

From database: 182.254. 149.39/ubuntu 16.04 MySQL 5.7. 21 (Tencent Cloud)

Firewall configuration

Configure the primary server to allow only specific IP access to the port of the database to avoid unnecessary attacks.

Main Library Firewall Configuration

# iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT
# Delete configurations that may already exist 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 database ports 
$ 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
# Configuration is saved to /etc/iptables/rules.v4 /etc/iptables/rules.v6 Under these two files, 
# Better confirm 1 The actual saved content under the denyhosts And other security software, 
# Excessive rules may be recorded and need to be deleted manually 
 Slave Library Firewall Configuration 
# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT
# Delete configurations that may already exist to avoid multiple duplicate records 
$ sudo iptables -D OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT
# Adding 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
# Configuration is saved to /etc/iptables/rules.v4 /etc/iptables/rules.v6 Under these two files, 
# Better confirm 1 The actual saved content under the denyhosts And other security software, 
# Excessive rules may be recorded and need to be deleted manually 

Master database master configuration

1. Modify the mysql configuration

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

Make the following changes in the [mysqld] section:

[mysqld]
log-bin = /var/log/mysql/mysql-bin.log # Open 2 The binary log is commented out by default, so we will remove the comments 
server-id = 1 # Settings server-id
bind-address = 0.0.0.0 # The default is 127.0.0.1 Here we set it to any address and let go of remote access. Before doing this, 1 Make sure that the firewall is configured correctly, otherwise there will be security risks 

2. Restart mysql to create a user account for synchronization

Create user and authorize: User: repl Password: slavepass

$ sudo service mysql restart
$ mysql -u root -p -e "CREATE USER 'repl'@'182.254.149.39' IDENTIFIED BY 'slavepass';" # Create a 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 

3. Check the master status and record the binary file name (mysql-bin. 000001) and location (333802):

$ 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 |       |         |          |
+------------------+----------+--------------+------------------+-------------------+

4. Backup of the master library to prepare data for the first data synchronization of the slave library

Generate the database backup file using the following script

# Here to back up 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
# Verify that the tail of the file exists   " -- Dump completed on ", if it does not exist, then the backup is in error. 
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

Execute the script to ensure that the final output backup is successful

$ cd ~
$ sudo bash backup_wordpress.sh

Configuration from Server slave

1. Modify the mysql configuration

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

Modify server-id so that server-id requirements for each database are only 1 and cannot conflict with each other

[mysqld]
server-id = 2 # Settings server-id , must only 1
log_bin         = /var/log/mysql/mysql-bin.log # The log is also best opened 

2. Restore the database for the first time:

$ sudo service mysql restart
$ scp -P 22 -r root@121.199.27.227:~/wordpress.*.sql ./
# Delete possible 1 Line warning message, which may cause us to fail to recover 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

3. Restart mysql, open the mysql session, and execute the synchronous SQL statement (the host name of the main server, login credentials, and the name and location of the binary file are required):

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

0

4. Start the slave synchronization process:

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

1

5. View slave status:

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

2

When Slave_IO_Running and Slave_SQL_Running are both YES, the master-slave synchronization setting is successful. Next, one piece of verification can be performed, such as inserting one piece of data into one table of test database of main master database, checking whether there is new data in the same data table of test library of slave to verify whether the master-slave replication function is effective, and turning off slave (mysql) > stop slave;), and then modify master to see if slave is modified accordingly (slave is stopped, master modifications will not be synchronized to slave), you can complete the verification of master-slave replication function.

Other related parameters that can be used:

master records all tables of all libraries by default after opening binary log. You can specify to record only the specified database or even the specified table by configuration. Specifically, the following options can be added and modified in [mysqld] of mysql configuration file:

#  Which databases are not synchronized  
binlog-ignore-db = mysql 
binlog-ignore-db = test 
binlog-ignore-db = information_schema 
#  Only which databases are synchronized, and others are not synchronized  
binlog-do-db = game

As you can see when you looked at the master status earlier, only the test library was recorded, and the manual and mysql libraries were ignored.

Summarize