MySQL master copy (2)

MySQL master-slave replication architecture and implementation Miscellaneous 1, set slave node as read-only mode

MariaDB [(None)]> SHOW GLOBAL VARIABLES like ' read_only '; +---------------+-------+| variable_name | Value |+---------------+-------+| Read_Only | Off |+---------------+-------+ slave node: View the copied master node information file. [[email protected] mysql]# cat Master.info 18master-bin.000003108710.201.106.131repluserreplpass330660001800.0000 from the node's own local relay log file location, and from which log file from the primary node, which location copies the record: [[ Email protected] mysql]# cat relay-log.info./relay-log.000003613master-bin.0000031087mariadb [(none)]> SHOW GLOBAL VARIABLES like '%relay_log% '; +----------------------------------+----------------+| variable_name | Value |+----------------------------------+----------------+| Innodb_recovery_update_relay_log | OFF | | Max_relay_log_size | 0 | | Relay_log | Relay-log | |                Relay_log_index | || Relay_log_info_file | Relay-log.info | | Relay_log_purge | On | | Relay_loG_recovery | OFF | | Relay_log_space_limit | 0 | | Sync_relay_log | 0 | | Sync_relay_log_info | 0 |+----------------------------------+----------------+

2, Master node view

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%master%‘;+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| master_verify_checksum | OFF   || sync_master_info       | 0     | 每一次给从节点发送一些event之后，本地对应的master_info会不会立即同步到磁盘上，能够让本地记录下来。保证从节点及时得到更新+------------------------+-------+

Primary master Architecture Implementation 1, restore default configuration

[[email protected] mysql]# systemctl stop mariadb.service[[email protected] mysql]# systemctl stop mariadb.service[[email protected] mysql]# rm -rf /data/mysql/*[[email protected] mysql]# rm -rf /data/mysql/*

2. Edit the configuration file

[[email protected] mysql]# vim /etc/my.cnf[mysqld]datadir=/data/mysqllog_bin=master-binrelay_log=relay-logserver_id=1innodb_file_per_table=ONskip_name_resolve=ONauto_increment_offset=1auto_increment_increment=2[[email protected] mysql]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlrelay-log-index=relay-log.indexdatadir=/data/mysqllog_bin=master-binrelay_log=relay-logserver_id=5innodb_file_per_table=ONskip_name_resolve=ONauto_increment_offset=2auto_increment_increment=2启动数据库[[email protected] mysql]# systemctl start mariadb.service[[email protected] mysql]# systemctl start mariadb.service查询配置是否生效：MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%log%%‘;

3. Create an account with copy rights

生产环境应该单独IP授权，一个一个给MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘10.201.106.%‘ IDENTIFIED BY ‘replpass‘;MariaDB [(none)]> FLUSH PRIVILEGES;MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘10.201.106.%‘ IDENTIFIED BY ‘replpass‘;MariaDB [(none)]> FLUSH PRIVILEGES;

4. Specify both sides as slave nodes

[[email protected] mysql]# MariaDB [(none)]> SHOW MASTER status;+-------------------+----------+------------ --+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+------------------+|      master-bin.000003 |              428 |                  | |+-------------------+----------+--------------+------------------+mariadb [(none)]> change MASTER to Master_ Host= ' 10.201.106.132 ', master_user= ' repluser ', master_password= ' Replpass ', master_log_file= ' master-bin.000003 ', master_log_pos=508; [[email protected] mysql]# MariaDB [(none)]> SHOW MASTER status;+-------------------+----------+------------ --+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+------------------+|      master-bin.000003 |              508 |                  | |+-------------------+----------+--------------+------------------+mariadb [(none)]> CHANGE MASTER to master_host= ' 10.201.106.131 ', master_user= ' repluser ', master_password= ' Replpass ', master_log_file= ' Master-bin.000003 ', master_log_pos=428; View status: MariaDB [(None)]> SHOW SLAVE status\g

5. Start thread

[[email protected] mysql]#MariaDB [(none)]> START SLAVE;[[email protected] mysql]# MariaDB [(none)]> START SLAVE;

6. Testing

MariaDB [(none)]> CREATE DATABASE mydb;MariaDB [(none)]> SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |      516 |              |                  |+-------------------+----------+--------------+------------------+备边查看：MariaDB [(none)]> SHOW SLAVE STATUS\G*************************** 1. row ***************************           Slave_IO_State: Waiting for master to send event              Master_Host: 10.201.106.131              Master_User: repluser              Master_Port: 3306            Connect_Retry: 60          Master_Log_File: master-bin.000003      Read_Master_Log_Pos: 516           Relay_Log_File: relay-log.000002            Relay_Log_Pos: 618

————————

Right node test: MariaDB [mydb]> CREATE TABLE tb1 (id INT UNSIGNED not NULL auto_increment PRIMARY key,name CHAR (30)); MariaDB [mydb]> DESC tb1;+-------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+----------------+| ID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) |     YES | |                NULL | |+-------+------------------+------+-----+---------+----------------+2 rows in Set (0.09 sec) MariaDB [mydb]> SHOW MASTER status;+-------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+------------------+|      master-bin.000003 |              659 |                  | | Left node Insert data: MariaDB [mydb]> insert INTO TB1 (name) VALUES (' Yang Kang '), (' Yang Guo '), (' Yang Yanzhao '); MariaDB [mydb]> SELECT * From tb1;+----+--------------+| ID |  Name |+----+--------------+| 1 |  Yang Kang | | 3 |  Yang Guo | | 5 | Yang Yanzhao | On right node insert data: MariaDB [mydb]> SELECT * from tb1;+----+---------------+| ID |  Name |+----+---------------+| 1 |  Yang Kang | | 3 |  Yang Guo | | 5 |  Yang Yanzhao | | 6 |  Zhu Yuanzhang | | 8 | Zhu di | | 10 | Zhu Yue |+----+---------------+

Semi-synchronous replication 1, see if there are related plugins

[[email protected] ~]# rpm -ql mariadb-server | grep semisync/usr/lib64/mysql/plugin/semisync_master.so/usr/lib64/mysql/plugin/semisync_slave.so

2. Clear the Configuration

[[email protected] ~]# systemctl stop mariadb[[email protected] ~]# rm -rf /data/mysql/*[[email protected] ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqllog_bin=master-binserver_id=1innodb_file_per_table=ONskip_name_resolve=ON[[email protected] ~]# systemctl start mariadb.service[[email protected] ~]# systemctl stop mariadb[[email protected] ~]# rm -rf /data/mysql/*[[email protected] ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlrelay-log-index=relay-log.indexrelay_log=relay-logserver_id=5innodb_file_per_table=ONskip_name_resolve=ON[[email protected] ~]# systemctl start mariadb.service

3. The master node creates an account with copy rights

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘10.201.106.%‘ IDENTIFIED BY ‘replpass‘;MariaDB [(none)]> FLUSH PRIVILEGES;MariaDB [(none)]> SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |      498 |              |                  |+-------------------+----------+--------------+------------------+

4. Where to read logs from the server configuration

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=‘10.201.106.131‘,MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘replpass‘,MASTER_LOG_FILE=‘master-bin.000003‘,MASTER_LOG_POS=498;

5, the main server installation plug-in

MariaDB [(None)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so '; MariaDB [(None)]> SHOW plugins;+--------------------------------+----------+--------------------+------------- -------+---------+| Name | Status | Type | Library | License | | Rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | 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 |+------------------------------------+-------+mariadb [(none)]> SHOW GLOBAL STATUS like '%semi% '; +------------ --------------------------------+-------+| variable_name | Value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients | 0 | | 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 | OFF | | 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 |+--------------------------------------------+-------+

6. Install from node plugin from node

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;MariaDB [(none)]> SHOW PLUGINS;| rpl_semi_sync_slave            | ACTIVE   | REPLICATION        | semisync_slave.so | GPL     |MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled     | OFF   || rpl_semi_sync_slave_trace_level | 32    |+---------------------------------+-------+

7, turn on the master node plug-in function 7.1 open the main node

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%semi%‘;+------------------------------------+-------+| Variable_name                      | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled       | ON    |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    |

7.2 Open Slave node

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%SEMI%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled     | ON    |

8. Open the replication thread from the node

MariaDB [(none)]> START SLAVE;MariaDB [(none)]> SHOW SLAVE STATUS\G;

9. Testing

9.1 Master node Create database and Table mariadb [(none)]> Create DB mydb; MariaDB [mydb]> CREATE TABLE tb1 (id int,name char (30)); MariaDB [mydb]> SHOW GLOBAL STATUS like '%semi% '; +--------------------------------------------+-------+| variable_name | Value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 10910 | | Rpl_semi_sync_master_net_wait_time | 21821 | | Rpl_semi_sync_master_net_waits | 2 | | 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 | 11830 | | Rpl_semi_sync_master_tx_wait_time | 23660 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 2 |+--------------------------------------------+-------+

Copy filter 1, set copy only mydb database from database

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘replicate%‘;+----------------------------------+-----------+| Variable_name                    | Value     |+----------------------------------+-----------+| replicate_annotate_row_events    | OFF       || replicate_do_db                  |           || replicate_do_table               |           || replicate_events_marked_for_skip | replicate || replicate_ignore_db              |           || replicate_ignore_table           |           || replicate_wild_do_table          |           || replicate_wild_ignore_table      |           |+----------------------------------+-----------+

1.1 Temporarily shutting down replication threads

MariaDB [(none)]> STOP SLAVE;

1.2 Setting parameters

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      |重新开启复制进程：MariaDB [(none)]> START SLAVE;

1.3 Testing Replication data

主：MariaDB [mydb]> CREATE DATABASE testdb;从：MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mydb               || mysql              || performance_schema || test               |+--------------------+主：MariaDB [mydb]> INSERT INTO tb1 VALUES (1,"a");从：MariaDB [mydb]> SELECT * FROM tb1;+------+------+| id   | name |+------+------+|    1 | a    |+------+------+

2. SSL

MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE ‘%ssl%‘;+---------------+----------+| Variable_name | Value    |+---------------+----------+| have_openssl  | DISABLED || have_ssl      | DISABLED || ssl_ca        |          || ssl_capath    |          || ssl_cert      |          || ssl_cipher    |          || ssl_key       |          |+---------------+----------+

2.1 Create a copy account that requires an SSL connection

MariaDB [mydb]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser‘@‘10.201.106.%‘ IDENTIFIED BY ‘replpass‘ REQUIRE SSL;

Miscellaneous 1, Cleanup log

MariaDB [mydb]> SHOW BINARY LOGS;+-------------------+-----------+| Log_name          | File_size |+-------------------+-----------+| master-bin.000001 |     30349 || master-bin.000002 |   1038814 || master-bin.000003 |       958 |+-------------------+-----------+MariaDB [mydb]> PURGE BINARY LOGS TO ‘master-bin.000002‘; Query OK, 0 rows affected (0.03 sec)MariaDB [mydb]> SHOW BINARY LOGS;+-------------------+-----------+| Log_name          | File_size |+-------------------+-----------+| master-bin.000002 |   1038814 || master-bin.000003 |       958 |+-------------------+-----------+

2. View Replication Threads

MariaDB [mydb]> SHOW processlist;+----+----------+----------------------+------+-------------+------+--------- --------------------------------------------------------------+------------------+----------+| Id | User | Host | db | Command | Time | State | Info | Progress |+----+----------+----------------------+------+-------------+------+----------------------------------  -------------------------------------+------------------+----------+| 7 | Repluser | 10.201.106.132:55276 | NULL | Binlog Dump | 8090 | Master has sent all binlog to slave; Waiting for Binlog to be updated |    NULL |  0.000 | | 8 | Root | localhost | MyDB |    Query | 0 | NULL |    SHOW Processlist | 0.000 |+----+----------+----------------------+------+-------------+------+------------------------------------- ----------------------------------+------------------+----------+2 rows in Set (0.00 sec) 

3.

MySQL master copy (2)