Ubuntu16.04 Configuring the configuration method of Mysql master from synchronization
Preparations
1.The master and slave database versions are best to be consistent
2.The data in the master and slave database is consistent.
Main database: 121.199.27.227/ubuntu 16.04 MySQL 5.7.21 (Alibaba Cloud)
From the database: 182.254.149.39/ubuntu 16.04 MySQL 5.7.21 (Tencent Cloud)
Firewall configuration
Configure the main server to only allow specific IP to access 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 the configuration 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, only allow 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 under the two files/etc/iptables/rules.v4/etc/iptables/rules.v6, #It is best to confirm the actual saved content, especially when other security software such as denyhosts is installed, #Excessive rules may be recorded and need to be deleted manually From the library firewall configuration # iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT #Delete the configuration that may already exist 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 under the two files/etc/iptables/rules.v4/etc/iptables/rules.v6, #It is best to confirm the actual saved content, especially when other security software such as denyhosts is installed, #Excessive rules may be recorded and need to be deleted manually
Master database master configuration
1.Modify 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 the binary log, the default is commented out, we remove the comment 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 and let go of remote access.Before doing this, make sure that the firewall is configured correctly, otherwise it will cause security risks.
2.Restart mysql and 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 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 privileges
3.Check the master status, 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.Back up the main library, prepare data for the first data synchronization of the slave library
Use the following script to generate database backup files
#Here is an example of backing up wordpress database 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 if the end of the file exists "-- Dump completed on", if it does not exist, it means that the backup went wrong. 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
Slave configuration from server
1.Modify mysql configuration
$ sudo vim/etc/mysql/mysql.conf.d/mysqld.cnf
Modify the server-id, the server-id requirement of each database is unique and cannot conflict with each other
[mysqld] server-id = 2 #Set server-id, it must be unique log_bin =/var/log/mysql/mysql-bin.log #The log is also best to open
2.Restore the database for the first time:
$ sudo service mysql restart $ scp -P 22 -r [email protected]:~/wordpress.*.sql./ #Delete a line of warning messages that may exist, this line of warning messages may cause us to be unable 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 mysql session, and execute synchronous SQL statement (requiresThe host name of the main server, login credentials, the name and location of the binary file):
$ 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;"
4.Start the slave synchronization process:
$ mysql -u root -p -e "start slave;"
5.View slave status:
$ 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:
...
When Slave_IO_Running and Slave_SQL_Running are both YES, it means that the master-slave synchronization setting is successful.Next, you can perform some verifications, such as inserting a piece of data in a table of the test database of the master database, and checking whether there is new data in the same data table of the slave test database to verify whether the master-slave replication function is effective., You can also close the slave (mysql>stop slave;), and then modify the master to see if the slave is also modified accordingly (after stopping the slave, the master's modification will not be synchronized to the slave), and the verification of the master-slave replication function can be completed.
Other related parameters that can also be used:
After the master turns on the binary log, it records the operations of all tables in all libraries by default.You can specify to log only the operations of the specified database or even the specified table through configuration.Specifically, the following options can be added and modified in the [mysqld] of the mysql configuration file:
# Which databases are not synchronized binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # Which databases are only synchronized, other than that, others are not synchronized binlog-do-db = game
If you check the master status before, you can see that only the test library is recorded, and the manual and mysql libraries are ignored.
Summary
The above is the configuration method for ubuntu 16.04 to configure MySQL master-slave synchronization introduced by the editor.I hope it will be helpful to you.If you have any questions, please leave me a message, and the editor will reply to you in time.Thank you very much for your support to the website!
Tags
Technical otaku
Sought technology together
0 Comments