MySQL replication cluster, and read/write separation

Why should I set up a MySQL cluster?

To alleviate the IO pressure on the MySQL server, set up multiple other MySQL servers to help him share the read and write operations

Types of 1.mysql replication clusters

Master-Slave Architecture (read-only from server, not writable)

A master one from,

A master multiplicity

Main Master Architecture

2. Copying principle

1) If the data set on the master server is large, then we need to make a backup of all the contents of the primary server database, then send it to the slave server, then the binary log file, and its coordinates for subsequent data synchronization

2) The so-called synchronization, is the SQL statement in the binary log on the primary server, sent to the relay log file from the server, and then the SQL statement replay implementation synchronization

3. Threads related to master-slave replication

From the server: IO thread: Used to connect to the master server, monitor the changes in the binary log, and accept the

SQL Thread: Monitor, read, and replay the SQL statements in the relay log, and write the data to the server database;

On the primary server: Dump thread: Sends the binary log of the slave request past




4. The process of master-slave copying:

1. The primary server data is modified, written to the database, and recorded in the binary log file

The 2.slave IO thread replicates the binary log that changed and logs it to its own trunk log

3.slave SQL thread, will copy the relay log to do replay, and maintain the same data on the master;

5. Master-Slave Copy Note:

1. Ensure the server_id settings of each server are different, (after reading and writing separation to do testing convenience)

2. Open the binary log file on the primary server

3. Close the binary log file from the server, turn on the trunk log file, and set the Read_only=on

4. master server Setup parameter sync_binlog=1 (change content is written to the binary log each time a data change occurs)

Innodb_flush_log_at_trx_commit=1 (The transaction log is saved to disk without committing a transaction)

5. Remember to log the file name and coordinates of the binary log

6. Authorize a user on the primary server that can be used for master-slave replication


6. Master-Slave Replication instance

Primary server File Configuration

Innodb_file_per_table=on//Open InnoDB separate tablespace skip_name_resolve=on//Skip hostname Resolution log_bin=binlogserver_id=123sync_binlog= 1innodb_flush_log_at_trx_commit=1

Configure from Server files

Innodb_file_per_table=onskip_name_resolve=onrelay_log=slavelogserver_id=7read_only=on

The primary server does the data backup and sends it to the slave server

mariadb [(none)]> show master status;  //record at this time binary log file and coordinates +---------------+----- -----+--------------+------------------+| file           | position | binlog_do_db | binlog_ignore_db |+---------------+----------+ --------------+------------------+| binlog.000015 |      617 |               |                   |+---------------+-------- --+--------------+------------------+1 row in set  (0.00 sec) mariadb [(none)]>  grant replication slave on *.* to  ' vuser ' @ '% '  identified by   ' 111111 ';   //authorize a user query ok, 0 rows affected  (0.02 sec) for master-slave replication [[ Email protected] ~]# mysqldump --all-databases --lock-all-tables > gg.sql  // Copy the current database and send it to the slave server [[Email protected] ~]# scp gg.sql [email protected]:/root

Working from the server

mariadb [zz]> \. /root/gg.sql   //Use the database file sent over, initialize the database to establish a master-slave connection: Mariadb [zz] > change master to master_host= ' 172.16.0.156 ', master_user= ' vuser ', master_password= ' 111111 ', master_port=3306,master_log_file= ' binlog.000015 ', master_log_pos=617; mariadb [zz]> start slave;   Open Slave Server mariadb [zz]> show slave  status\g;  View master-slave status *************************** 1. row ***************************                slave_io_state: waiting  for master to send event   //Configure success If this line is displayed                    master_host: 172.16.0.156                    Master_user: vuser                  master_port: 3306                 connect_retry: 60               Master_Log_File:  binlog.000015          read_master_log_pos: 831                Relay_Log_File:  slavelog.000002                 Relay_Log_Pos: 740        Relay_Master_Log_File:  Binlog.000015             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: 831               relay_log_space: 1027              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: 1231 row in set  (0.00 SEC)

To turn off the master and slave configuration, you need to use the command stop slave from the server







Two. Dual master replication

Two servers, open binary log files and relay log files at the same time, and make changes to the master to operation of each other




Three. Read/write Separation (premise: master-slave copy)

Mysqlrouter

Principle

By invoking two different interfaces, the MySQL statement is called separately

Installation: Mysqlrouter, configuration Profile/etc/mysqlrouter/mysqlrouter.conf

[Routing:master] Add two configuration segments//master interface bind_address = 172.16.0.155:40001//bound IP address and port number destinations = 172.16.0.156:3306/ /backend MySQL Service mode = read-write//mode (write allowed) Connect_time = 3//connection time [routing:slave]bind_address = 172.16.0.15 5:40002destinations = 172.16.0.155:3306mode = Read-onlyconnect_time = 1

Configuration complete to start the service

[Email protected] ~]# SS-TNL
State recv-q send-q Local address:port Peer address:port
LISTEN 0 *:3306 *:*
LISTEN 0 *:111 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 *:22 *:*
LISTEN 0 127.0.0.1:631 *:*
LISTEN 0 127.0.0.1:25 *:*
LISTEN 0 172.16.0.155:40001 *:*
LISTEN 0 128 172.16.0.155:40002





Mysqlrouter Test Example: (Because the mysqlrouter is dispatched to the backend, so we need to authorize users in the master)

MariaDB [(None)]> grant all on * * to ' CCCC ' @ ' 172.16.0.% ' identified by ' 111111 '; Query OK, 0 rows affected (0.01 sec) [[email protected] ~]# mysql-ucccc-h172.16.0.151-p40002-p111111-e ' SELECT @ @serve r_id; ' +-------------+|           @ @server_id |+-------------+| 7 |+-------------+[[email protected] ~]# mysql-ucccc-h172.16.0.151-p40001-p111111-e ' SELECT @ @server_id; ' +-------------+|         @ @server_id |+-------------+| 123 |+-------------+

Four: Proxysql to achieve read and write separation

1. Installing Proxysql

2. Start the Proxysql service

3. Access to the Proxysql management interface

[[email protected] ~]# mysql -uadmin -padmin -h127.0.0.1 -p6032   User password set by the system, and port number use the main database to add a monitoring point to the Mysql_servers table: Insert into mysql_servers (hostgroup_id,username,port )  values (10, ' 172.16.0.156 ', 3306), (10, ' 172.16.0.150 ', 3306), (10, ' 172.16.0.151 ', 3306), (10, ' 172.16.0.152 ', 3306); Create monitoring user  mariadb [(none) on master server]> grant replication client,replication  slave on *.* to  ' proxyuser ' @ ' 172.16.0.% '  identified by  ' 111111 ';    Modify variable parameters on Proxysql, set corresponding monitoring user  mysql [main]> set mysql-monitor_username= ' Proxyuser ';   //essentially modifies the Global_variables table in main data  MySQL [main]> set  mysql-monitor_password= ' 111111 ';  modify mysql_replication_hostgroups on the Proxysql server to add a more detailed grouping of tables  mysql [ main]> insert into mysql_replication_hostgroups  (Writer_hostgroup,reader_hostgroup)  values  (10,20);  here must have an ID and theThe same as set in the previous Mysql_servers table, as for the master and slave, will read the READ_ONLY parameter to the backend server configuration file    Manage user  mariadb [(none) in the master server settings > grant all on *.* to  ' root ' @ ' 172.16.0.% '  identified by   ' 111111 '; mariadb [(none)]> grant all on *.* to  ' sql ' @ ' 172.16.0.% '  identified  by   ' 111111 '; also add individual administrative users to Proxysql  mysql [main]> insert into mysql_ users  (Username,password,default_hostgroup)  values  (' Root ', ' 111111 ', Ten), (' SQL ', ' 111111 ', 20); This allows access to the primary server when using the root user, and when using SQL, accesses the information that was just configured on Proxysql from the server, synchronizes to the runtime environment, and synchronizes to disk     load  Mysql servers to runtime    save mysql servers to disk     load mysql variables to runtime    save  Mysql variables to disk    load mysql users to runtime   &nBsp; save mysql users to disk finally use the dispatch port to access mysql[[email protected] ~]#  mysql -uroot -h172.16.0.154 -p111111 -p6033 -e  "select @ @server_id;" +-------------+| @ @server_id  |+-------------+|          123 |+-------------+[[email protected] ~]# mysql -usql -h172.16.0.154 - p111111 -p6033 -e  "select @ @server_id;" +-------------+| @ @server_id  |+-------------+|            2 |+-------------+[[email protected] ~]# mysql -usql -h172.16.0.154  -p111111 -P6033 -e  "select @ @server_id;" +-------------+| @ @server_id  |+-------------+|            3 |+-------------+[[email protected] ~]# mysql -usql -h172.16.0.154  -p111111 -p6033 -e  "select @ @server_id;" +-------------+| @ @server_id  |+-------------+|            6 |+-------------+

Note: If the master-slave connection is unsuccessful, try changing the firewall policy or user authorization

MySQL replication cluster, and read/write separation