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

This paper describes the configuration method, matters needing attention and maintenance of single master mode and double master mode of MySQL.

1. Single master configuration instance 1.1 overall flow: master and slave time synchronization master enables binary recording function and authorizes a specific user to copy binary files. Slave enables relay log function, executes synchronization statements, and enables synchronization function. Note: when the version number is inconsistent, the master version can be 1.2 master lower than the slave version.

Edit / etc/my.cnf and add the following to the mysqld configuration section (need to restart the service)

Server-id=1log_bin=/data/mysql/logs/mysql-bin

Start the database and authorize:

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'node2'@'192.168.200.%' IDENTIFIED BY' node2pass';MariaDB [(none)] > Note on FLUSH PRIVILEGES;1.3 master / data/mysql/logs directory must exist, and the owner and group are users running mysql (because mysql needs to create and write files in this directory) mysql-bin can be defined by itself. The values after server-id are unique and cannot be repeated between nodes. 1.4 slave side configuration

Edit / etc/my.cnf and add the following to the mysqld configuration section (need to restart the service)

Server-id=2relay-log=/data/mysql/logs/relay-bin

After starting the service, set up the master node and start

MariaDB [(none)] > change master to master_host='192.168.200.41',master_port=3306,master_user='node2',master_password='node2pass',master_log_file='mysql-bin.000003', master_log_pos = 4bot MariaDB [(none)] > START SLAVE MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.200.41 Master_User: node2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 927 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 604 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec _ Master_Log_Pos: 927 Relay_Log_Space: 1783 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_ SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1

When viewing show slave status, Slave_IO_Running and Slave_SQL_Running are successful when the status is yes. You can create a library or a table in the master node to see if the slave node also exists.

1.5 Note on the slave side / data/mysql/logs directory must exist, and the owner and group are users running mysql (because mysql needs to create and write files in this directory) the relay-bin name can be defined by itself. The value after server-id is unique and cannot be repeated. In the case of change master, if the position of the master_log_pos is incorrect, it may cause the Slave I thread O thread to fail. The error log is as follows: 180603 16:46:49 [Note] Slave node2@192.168.200.41:3306',replication started inlog O thread: connected to master 'node2@192.168.200.41:3306',replication started inlog' mysql-bin.000003' at position 760180603 16:46:49 [ERROR] Error reading packet from server: binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000003' at 760, the last event read from' mysql-bin.000003' at 760, the last byte read from 'mysql-bin.000003' at 769. (server_errno=1236) 180603 16:46:49 [ERROR] Slave I from master when reading data from binary log O: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event' mysql-bin.000003' at 760, the last event read from 'mysql-bin.000003' at 760, the last byte read from' mysql-bin.000003' at 769, Error_code: 1236

Solution:

Check the current binaries through show binary logs, then find the correct POS location through show binlog events in 'BINLOG FILE NAME', and use the correct POS location when change master.

MariaDB [(none)] > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 30355 | mysql-bin.000002 | 1038814 | mysql-bin.000003 | 491 | +- -+-+ 3 rows in set (0.00 sec) MariaDB [(none)] > show binlog events in 'mysql-bin.000003' +- -Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + -+- -- + | mysql-bin.000003 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.56-MariaDB Binlog ver: 4 | | mysql-bin.000003 | 245 | Query | 1 | 416 | grant replication slave Replication client on *. * to lxk@'192.168.200.%' identified by 'lxkpass' | | mysql-bin.000003 | 416 | Query | 1 | 491 | flush privileges | +-- -+- -+ 3 rows in set (0.00 sec)

Such as the above code: choose Pos (start position) or End_log_pos (end position).

two。 Dual master configuration example 2.1 overall process: node 1 and node 2 time synchronization Node 1 and Node 2 turn on binary log and relay log function and authorize a specific user to copy binary files Node 1 and Node 2 execute synchronization statements, turn on the synchronization function.

Note:

When the version number is inconsistent, the master version can be 2.2 lower than the slave version configuration file:

Node 1: edit / etc/my.cnf, add the following to the mysqld configuration section to save and exit:

Log_bin=/data/mysql/logs/mysql-binrelay_log=/data/mysql/logs/relay-log

Note: make sure that mysql has read and write access to the / data/mysql/logs directory

Node 2: edit / etc/my.cnf, add the following to the mysqld configuration section to save and exit:

Log_bin=/data/mysql/logs/mysql-binrelay_log=/data/mysql/logs/relay-log

Note: make sure that mysql has read and write access to the / data/mysql/logs directory

Node 1 IP:192.168.200.41 Node 2 IP:192.168.200.42

2.3 Database operations:

Both Node 1 and Node 2 do the same to authorize a user:

MariaDB [(none)] > grant replication slave,replication client on *. * to lxk@'192.168.200.%' identified by 'lxkpass';Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.00 sec)

View the Pos location to copy:

Node 1:

MariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000003 | 491 | +- + 1 row in set (0.00 sec) MariaDB [(none)] > show binlog events in 'mysql-bin.000003' +- -Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + -+- -- + | mysql-bin.000003 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.56-MariaDB Binlog ver: 4 | | mysql-bin.000003 | 245 | Query | 1 | 416 | grant replication slave Replication client on *. * to lxk@'192.168.200.%' identified by 'lxkpass' | | mysql-bin.000003 | 416 | Query | 1 | 491 | flush privileges | +-- -+- -+ 3 rows in set (0.00 sec)

Node 2:

MariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000003 | 491 | +- + 1 row in set (0.00 sec) MariaDB [(none)] > show binlog events in 'mysql-bin.000003' +- -Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + -+- -- + | mysql-bin.000003 | 4 | Format_desc | 2 | 245 | Server ver: 5.5.56-MariaDB Binlog ver: 4 | | mysql-bin.000003 | 245 | Query | 2 | 416 | grant replication slave Replication client on *. * to lxk@'192.168.200.%' identified by 'lxkpass' | | mysql-bin.000003 | 416 | Query | 2 | 491 | flush privileges | +-- -+- -+ 3 rows in set (0.00 sec)

Because it is a new database, the pos value of the two nodes is the same, both 491, the production environment is adjusted as needed, and the general location selection starts from the authorized location.

Execute the synchronization statement:

Node 1:

MariaDB [(none)] > change master to master_host='192.168.200.42',master_user='lxk',master_password='lxkpass',master_log_file='mysql-bin.000003',master_log_pos=491;Query OK, 0 rows affected (0.27 sec) MariaDB [(none)] > start slave;Query OK, 0 rows affected (0.00 sec)

Node 2:

MariaDB [(none)] > change master to master_host='192.168.200.41',master_user='lxk',master_password='lxkpass',master_log_file='mysql-bin.000003',master_log_pos=491;Query OK, 0 rows affected (0.01sec) MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) 2.4.View status MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.200.41 # Master node address Master_User: lxk # Master node user name Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 491Relay_Log_File: relay -bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes # IO thread open Slave_SQL_Running: Yes # SQL thread start

As long as the status of slave_IO_Runing and Slave_SQL_Runing in the execution result is Yes, it means that the slave node connects to the master node successfully and the replication feature is enabled successfully.

Test: node 2 creates a database named testdbMariaDB [(none)] > create database testdb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | | testdb | +-+ 5 rows in set (0.00 sec) Node 1 View Local Database MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | | testdb | +-+ 5 rows in set (0. 00 sec) 3 Monitoring and maintenance: clean up logs as the service runs There will be more and more log files, bigger and bigger. It is best to copy the binary to another node (the binary must be saved), and then delete it under the mysql command line. Actions via PURGE are reflected in the .index file. MariaDB [testdb] > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 30355 | | mysql-bin.000002 | 1038814 | mysql-bin.000003 | 1364 | +- -- +-+ 3 rows in set (0.00 sec) MariaDB [testdb] > purge binary logs to 'mysql-bin.000003' # clear the log Query OK to mysql-bin.000003, 0 rows affected (0.01 sec) MariaDB [testdb] > show binary logs # View the log again +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000003 | 1364 | +-+ -+ 1 row in set (0.00 sec) [root@node1 ~] # ls / data/mysql/logs/ # View the binary log directory Mysql-bin.000003 mysql-bin.index relay-log.000001 relay-log.000002 relay-log.index has been deleted before 000003.

Replication monitoring

Determine whether the slave server lags behind the master server and check it through show slave status\ G. Seconds_Behind_Master: 0 # means not lagging behind

How to determine whether the data of master and slave nodes are consistent

It is very easy to happen in the two-master model. When the pt-table-checksum master-slave data in procona-tools is inconsistent, the replication thread can be stopped and restarted, and the slave server will start the replication, which is consistent with the master node.

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.