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

Mysql Master-Slave replication Architecture and mysql Master-Slave replication configuration

Primary node:

Start the binary log

Sets a globally unique ID number (server-id) for the current node

Create a user account with copy permission (replication slave,replication client)

Slave node:

Start relay log

Set a globally unique ID number for the current node

Use a user account with replication permission to connect to the primary server and start the replication thread step by step master node (192.168.182.130)

Edit the configuration file / etc/my.cnf to add the following

[mysqld] log_bin=1server_id=1innodb_file_per_table=on creates a user account with replication permission MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'192.168.182.132' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES; slave node (192.168.182.132)

Edit the configuration file / etc/my.cnf to add the following

[mysqld] relay_log=relay_logrelay_log_index=relay_log.indexserver_id=7skip_name_resolve=1

Connect to the primary server using a user account with replication permission

MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=245

Start the replication thread

MariaDB [(none)] > START SLAVE Check the status of slave MariaDB [(none)] > SHOW SLAVE STATUS\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.182.130 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 245 Relay_Log_File: 1.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000075 Slave_IO_Running: Yes # # Io thread started Slave_SQL_Running: Yes # # sql thread started 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: 245 Relay_Log_Space: 809 Until_Condition: None Until_Log_File: Until_Log_Pos: 0Master_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: problems that should be paid attention to in master-slave replication of 1mysql

Restrict slave server to read-only

Restrict the slave server to read-only, set read_only=on on the slave server, and write it in the configuration file if you want it to be permanent, but this restriction is not valid for users with SUPER permission

MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE 'read_only';+-+-+ | Variable_name | Value | +-+-+ | read_only | OFF | +-+-+ MariaDB [(none)] > SET GLOBAL read_only=1 If you want to restrict all users from writing, start a connection with a read lock and do not exit the connection MariaDB [(none)] > FLUSH TABLE WITH READ LOCK

How to ensure the transaction security of master-slave replication?

Enable parameters on the master node

Sync_binlog=

If you are using an InnoDB storage engine

Innodb_flush_log_at_trx_commitinnodb_support_xa on the slave node. The following parameters indicate whether to start the replication thread automatically when starting from the server. On means you don't need to start it automatically. Use skip_slave_start=on to start it manually.

Two files on the slave node

Information about slave connecting to master is saved in the master.info file, such as the ip address of the primary server, the user used for replication, password, port, and the currently synchronized binary log file and location.

The relay-log.info file stores the binary log files and locations that have been copied by the current slave node, and the copied contents are saved to which location of the relay log [root@backserver data] # cat relay-log.info./1.000002693mysql-bin.000075409

Monitoring and maintenance of replication:

Clean the log: use the PURGE command to make sure the data has been backed up before cleaning

Replication monitoring

MariaDB [(none)] > SHOW BINARY LOGS +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 483 | | mysql-bin.000002 | 264 | mysql-bin.000003 | 264 | mysql-bin.000004 | 264 | | mysql -bin.000005 | 990 | | mysql-bin.000006 | 514 | | mysql-bin.000007 | 264 | | mysql-bin.000008 | 245 | mysql-bin.000009 | 245 | +-+ MariaDB [(none)] > SHOW BINLOG EVENTS +- -+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-- -+-+ | mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.45-MariaDB-log Binlog ver: 4 | mysql-bin.000001 | 245 | Query | 1 | 315 | BEGIN | | mysql-bin.000001 | 315 | Intvar | 1 | 343 | INSERT_ID=9 | | mysql-bin.000001 | 343 | Query | 1 | 456 | use `SystSCC` | INSERT INTO S (sname,sdept) VALUES ('xiao' 'MA') | | mysql-bin.000001 | 456 | Xid | 1 | 483 | COMMIT / * xid=43 * / | +- -+-+ MariaDB [(none)] > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000009 | 245 | +-+ MariaDB [(none)] > SHOW PROCESSLIST +-+ -+ | Id | User | Host | db | Command | Time | State | Info | Progress | +- -+- -+ | 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | | 6 | system user | | NULL | Connect | 3850 | Waiting for master to send event | | NULL | 0.000 | | 7 | system user | | NULL | Connect | 3806 | Slave has read all relay log | Waiting for the slave waiting for the slave O thread to update it | NULL | 0.000 | +-+ -- + MariaDB [(none)] > SHOW SLAVE STATUS\ G

Whether the slave server lags behind the master server, there is a Seconds_Behind_Master in the salve of the slave server to view

MariaDB [(none)] > SHOW SLAVE STATUS- >\ Graph * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.182.130 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000079 Read_Master_Log_Pos: 334 Relay_Log_File: 1.000006 Relay_Log_Pos: 618Relay_Master_Log_File: mysql-bin.000079 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: MYDB 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: 334 Relay_Log_Space: 1182 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: 0 # how long does the slave server lag behind the master server Master_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

To determine whether the master-slave node data is consistent, use tools such as percona-tools

If the data is inconsistent, then delete all the data from the slave node, import a backup copy of the data from the master node to the slave node, and copy it again.

There is a parameter on the master node, which is set to 1, which means that every time the dump event reaches the slave node, the local master.info information should be synchronized to the disk immediately, so that the master.info of the slave node can be updated in time.

MariaDB [(none)] > SET GLOBAL sync_master_info=1;MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE 'sync_master_info' +-+-+ | Variable_name | Value | +-+-+ | sync_master_info | 1 | +-+-+ has parameters about relay_log on the slave node, such as sync_relay_log_info It means that each write operation is immediately synchronized to the disk file, and there is also a sync_relay_log double master model.

Problems encountered:

If the data is inconsistent, we may need to leave the server with high data reliability and do another slave server according to the retained mysql server.

For auto-growing fields, if one master node is 1mag2 id, and the other master node is also 1Magne2, there will be problems when merging. So, we can make the auto-growing fields of one node use even number id, and the other master node use odd number id, so there will be no problems when merging.

Setting of Odd id

Auto_increment_offset=1 # indicates the setting auto_increment_offset=2auto_increment_increment=2 configuration step of increasing 2 even id at a time, starting from 1.

Each node uses a unique server_id

Both start binary log and relay log

Create a user account with replication permission

Define the parity of the numerical range of the auto-growing id field.

One of the nodes is:

Edit configuration file

Log_bin=1server_id=1innodb_file_per_table=onrelay_log=relay-logrelay_log_index=relay-log.indexauto_increment_offset=1auto_increment_increment=2

Create a user account

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'192.168.182.132' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES; connects to the primary server using a user account with replication permissions and starts the slave thread MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.182.132',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=245 MariaDB [(none)] > START SLAVE

Actions on another primary node:

Edit configuration file

[mysqld] bin_log=1relay_log=1relay_log_index=relay-log.indexserver_id=7auto_increment_offset=2auto_increment_increment=2

Create a user account with replication permission

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'192.168.182.130' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES; connects to the primary node using a user account with replication privileges and enables the slave thread MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000075',MASTER_LOG_POS=665 MariaDB [(none)] > START SLAVE; semi-synchronous replication

Need to use a plug-in, if the rpm package installed the plug-in path is / usrl/lib64/mysql/plugin, I use the binary format to install the plug-in path is / usr/local/mysql/lib/plugin

Operations on the primary node

Edit configuration file

[mysqld] log_bin=1server_id=1innodb_file_per_table=onplugin_dir=/usr/local/mysql/lib/plugin

Add a user account with copy permission

MariaDB [(none)] > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'192.168.182.132' IDENTIFIED BY' replpass';MariaDB [(none)] > FLUSH PRIVILEGES

Install the plug-in

MariaDB [(none)] > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

Check what plug-ins are available

MariaDB [(none)] > SHOW PLUGINS

View semi-synchronous related variables

MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% semi%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +-+-+

Rpl_semi_sync_master_enabled: indicates for OFF a master node that is disabled for semi-synchronous replication

Rpl_semi_sync_master_timeout: indicates the timeout for waiting for a reply from the slave server. The default is 10s. If the slave server does not reply to the master server after this time, it will be degraded to run asynchronously instead of waiting.

Rpl_semi_sync_master_trace_level: indicates the trace level

Rpl_semi_sync_master_wait_no_slave: indicates whether to wait when there is no slave node. On: wait.

Enable semi-synchronous MariaDB [(none)] > SET GLOBAL rpl_semi_sync_master_enabled=1

From the actions on the node:

Edit configuration file

[mysqld] relay_log=1relay_log_index=relay-log.indexserver_id=7skip_name_resolve=1plugin_dir=/usr/local/mysql/lib/plugin/

Connect to the primary node using a user account with copy permission

MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.182.130',MASTER_USER='rpluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000078',MASTER_LOG_POS=245

Install the plug-in

MariaDB [(none)] > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

View semi-synchronous related variables

MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync _ slave_trace_level | 32 | +-+-+

Enable semi-synchronization

MariaDB [(none)] > SET GLOBAL rpl_semi_sync_slave_enabled=1; enable server thread MariaDB [(none)] > START SLAVE; to complete a semi-synchronous replication. You can view the relevant status variables on the master node to see if there is a semi-synchronous slave node MariaDB [(none)] > SHOW GLOBAL STATUS LIKE'% semi%'. +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 1 | Rpl_semi_sync_master_net_avg_wait_time | 0 | Rpl_semi_sync_master_net_wait_time | 0 | Rpl_semi_sync_master_net_waits | 0 | Rpl_semi_sync_master_no_times | 0 | Rpl_semi_ Sync_master_no_tx | 0 | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | Rpl_semi_sync_master_tx_avg_wait_time | 0 | Rpl_semi_sync_master_tx_wait_time | 0 | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | Rpl_semi_sync_master_yes_tx | 0 | +-+ # Rpl A _ semi_sync_master_clients of 0 means no A value of 1 means there is a replication filter

Let the slave node copy only the specified database, or the specified table of the specified database

There are two ways to do this:

The primary server only records events related to a specific database (specific table) in the binary log, but it may cause point-in-time restore to be impossible (after all, binary logging events are incomplete), and it is not recommended to use the

Binlog_do_db # specifies only which database modification operations are recorded in the binary log file. You can specify a list, using commas to separate binlog_ignore_db # to specify only those not recorded, and all others to be recorded. You can specify a list, separated by commas.

When SQL_THREAD relays events in the replay log from the server, only the events related to a specific database (specific table) are read and applied locally, but it results in a waste of network and disk io

Replicate_do_db= # the related events of the database specified here are replicated replicate_ignore_db=replicate_do_table=replicate_ignore_table=replicate_wild_do_table= # you can use the wildcard replcate_wild_ignore_table= when specifying the table example: copy only the related events of the MYDB database # operations performed on the slave server MariaDB [(none)] > SET GLOBAL replicate_do_db='MYDB' MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% replicate%' +-- +-+ | Variable_name | Value | +-- +-+ | replicate_annotate_row_events | | OFF | | replicate_do_db | MYDB | | replicate_do_table | replicate_events_marked_for_skip | replicate | | replicate_ignore_db | | replicate_ignore_table | | replicate_wild_do_table | | replicate_wild_ignore_table | | | +-+-+ MariaDB [(none)] > SHOW SLAVE STATUS\ G |

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.