Shulou(Shulou.com)06/01 Report--

Preparatory work

1. The version of the master-slave database had better be the same.

two。 Data consistency in master-slave database

Master database: 121.199.27.227 / ubuntu 16.04MySQL 5.7.21 (Aliyun)

Slave 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 database port to avoid unnecessary attacks.

Main library firewall configuration

# iptables-An INPUT-p tcp-s slave_ip-- dport 3306-j ACCEPT# deletes a configuration that may already exist 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# increase configuration Only specific addresses are allowed to access database port $sudo iptables-An INPUT-p tcp-s 182.254.149.39-- dport 3306-j ACCEPT$ sudo iptables-An INPUT-p tcp-s 127.0.0.1-- dport 3306-j ACCEPT$ sudo iptables-An INPUT-p tcp-- dport 3306-j DROP$ sudo iptables-An INPUT-p udp-- dport 3306-j DROP$ sudo iptables-An INPUT-p sctp-- dport 3306-j DROP$ sudo iptables-L-n# Save configuration $ The sudo apt-get install iptables-persistent$ sudo netfilter-persistent save# configuration is saved under the / etc/iptables/rules.v4 / etc/iptables/rules.v6 files # it is best to confirm the actual saved content, especially if other security software such as denyhosts is installed, # may record extra rules and need to manually delete the firewall configuration from the library # iptables-An OUTPUT-p tcp-d master_ip-- dport 3306-j ACCEPT# deletes the configuration that may already exist Avoid multiple duplicate records $sudo iptables- D OUTPUT-p tcp-d 121.199.27.227-dport 3306-j ACCEPT# add configuration $sudo iptables- An 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 # it is best to confirm the actual saved content, especially if other security software such as denyhosts is installed, # may record extra rules and need to delete them manually

Master database master configuration

1. Modify mysql configuration

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

Make the following modifications in the [mysqld] section:

[mysqld] log-bin = / var/log/mysql/mysql-bin.log # enable binary log, which is commented out by default. Let's remove the comment server-id = 1 # set server-idbind-address = 0.0.0.0 # default is 127.0.0.1. Here we set it to any address and let go of remote access. Make sure that the firewall is configured correctly before doing this, otherwise it will cause security risks.

two。 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 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. View 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. Master library backup to prepare data for the first data synchronization of the slave library

Use the following script to generate database backup files

# here take backing up the wordpress database as an example datadump= `which mysqldump`mysqluser = "root" userpass= "password" wordpressdb= "wordpress" backupwordpress_sql=$ wordpressdb.`date +% Y% m% d`.sqlif $datadump-u $mysqluser-- password=$userpass-h localhost-- opt $wordpressdb > $backupwordpress_sql 2 > & 1then echo "backup $wordpressdb success" backup $wordpressdb error "exit 1fi# to verify whether"-Dump completed on "exists at the end of the file. If it exists, it means that the backup has made an error. If [0-eq "$(sed'/ ^ $/! hitschen / 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 server-id. The server-id requirements of each database are unique and cannot conflict with each other.

[mysqld] server-id = 2 # set server-id, must be unique log_bin = / var/log/mysql/mysql-bin.log # log is also best opened

two。 Restore the database for the first time:

$sudo service mysql restart$ scp-P 22-r root@121.199.27.227:~/wordpress.*.sql. / # remove a possible line of warning messages that may prevent us from recovering the 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.重启mysql，打开mysql会话，执行同步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;" 4.启动slave同步进程： $ mysql -u root -p -e "start slave;" 5.查看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: ... 当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>

Stop slave;), and then modify the master to see if the slave is modified accordingly (after you stop slave, the changes of master will not be synchronized to slave), you can complete the verification of the master-slave replication function.

Other relevant parameters that can also be used:

When binary log is enabled in master, the operations of all tables in all libraries are recorded by default. You can specify that only the specified database or even the specified table operations can be recorded through configuration. You can add and modify the following options in "mysqld" of mysql configuration file:

# which databases are not synchronized binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # which databases are synchronized only, other than that, binlog-do-db = game

For example, when you look at the master status before, you can see that only the test library is recorded, while the manual and mysql libraries are ignored.

Summary

The above is the editor introduced to you ubuntu 16.04 configuration MySQL master-slave synchronization configuration method, I hope to help you, if you have any questions, please leave me a message, the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.