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

Environment: 192.168.205.37: as ProxySQL server 192.168.205.47: as Master server 192.168.205.57: as Slave server version: OS: centos7 1810 with mini install mariadb-server 5.5.60proxysql-1.4.15-1-centos7.x86_64.rpm destination:

Proxysql can achieve read-write separation. It mainly reads the read_only variables of the database to determine which are the master and slave servers. When creating a read-write group, it automatically divides the nodes into corresponding groups according to the status, such as write group 10 and read group 20, and follows the established rules and corresponding group names, thus realizing the separation of read and write operations. Proxysql is based on a lightweight database, and all settings have to be changed in the database. Getting together is a little cumbersome.

Step: use the script security below to secure the two databases [root@master data] # cat maridb_yum.sh #! / bin/bashID= `ip a show dev eth0 | sed-r '3Secretdash s@ (. * inet) (. *) (/. *) @\ 2databases' | cut-d. -f4`rpm-Q mariadb-server | | yum install-y mariadb-server [- d / data/mysql] | | mkdir-p / data/mysql [- d / data/logs] | | mkdir-p / data/logschown mysql:mysql / data/ {mysql Logs} sed-I's invalid datadirblade / etc/my.cnf | | sed-I'/\ [mysqld\] / a log-bin=/data/logs/bin' / etc/my.cnfgrep "innodb_file_per_table" / etc/my.cnf | | sed-I'/\ [mysqld\] / an innodb_file_per_table = on' / etc/my.cnfgrep "skip | _ name_resolve "/ etc/my.cnf | | sed-I'/\ [mysqld\] / a skip_name_resolve = on' / etc/my.cnfgrep" server-id "/ etc/my.cnf | | sed-I" /\ [mysqld\] / a server-id=$ID "/ etc/my.cnfservice mariadb restart modify the database from node 57 to read-only [root@slave data] # vi / etc/ my.cnf [QLD] read-only Record the replication location of the master node and create a replication account MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | bin.000001 | 245 | +-+-+ 1 row in set (0.00 sec) [root@master data] # mysql-e "grant replication slave on *. * to repluser@'192.168.205.%' identified by 'centos' "

Modify the change master to of slave and start the I / o thread

MariaDB [(none)] > CHANGE MASTER TO-> MASTER_HOST='192.168.205.47',-> MASTER_USER='repluser',-> MASTER_PASSWORD='centos',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='bin.000001',-> MASTER_LOG_POS=245;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.47 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000001 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 679 Relay_Master_Log_File: bin.000001 Slave_IO_ Running: Yes Slave_SQL_Running: Yes Import Library Test And view the database on the slave node successfully [root@master ~] # mysql

Show databases;+-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) install ProxySQL to create yum source, and install proxySQLcat show tables via yum +-+ | tables | +-+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +-+ 20 rows in set (0.00 sec) to view a MySQL [ (none)] > select * from sqlite_master where name='mysql_servers'\ gateway * 1. Row * * type: table name: mysql_serverstbl_name: mysql_serversrootpage: 2 sql: CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id > = 0) NOT NULL DEFAULT 0 Hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, status VARCHAR CHECK (UPPER (status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT',' OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', weight INT CHECK (weight > = 0) NOT NULL DEFAULT 1, compression INT CHECK (compression > = 0 AND compression = 0) NOT NULL DEFAULT 1000, max_replication_lag INT CHECK (max_replication_lag > = 0 AND max_replication_lag = 0) NOT NULL DEFAULT 0, comment VARCHAR NOT NULL DEFAULT'', PRIMARY KEY (hostgroup_id, hostname) Port) 1 row in set (0.00 sec) modifies the record of the primary node No matter how many nodes are added by the master or from the host, all of them are inserted into the database to monitor MySQL [(none)] > insert into mysql_servers (hostgroup_id,hostname,port) values. Query OK, 1 row affected (0.00 sec) MySQL [(none)] > insert into mysql_servers (hostgroup_id,hostname,port) values; Query OK, 1 row affected (you can see that one or two nodes have been inserted into the database MySQL [(none)] > select * from mysql_servers +- -+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | + -+ | 10 | 192.168.205.47 | 3306 | ONLINE | 1 | 0 | | 1000 | 0 | 0 | 0 | | 10 | 192.168.205.57 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | +-| -+- -+-+ 2 rows in set (0.00 sec) load configuration Make it effective MySQL [(none)] > load mysql servers to runtime Query OK, 0 rows affected (0.00 sec) is saved to disk MySQL [(none)] > save mysql servers to disk;Query OK, 0 rows affected (0.01 sec) because proxysql is a read_only that views master and slave data to determine who is master and who is slave, so set up an account to connect to master and slave servers. We need to set up this account on the master node, and it will be copied to MariaDB [(none)] > show variables like 'read_only' on the slave node. +-+-+ | Variable_name | Value | +-+-+ | read_only | OFF | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show variables like 'read_only' +-+-+ | Variable_name | Value | +-+-+ | read_only | ON | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > grant replication client on *. * to monitor@'192.168.205.%' identified by 'centos' Query OK, 0 rows affected (0.00 sec authorizes proxy to use this account to connect to the database, set monitoring account and password MySQL [(none)] > set mysql-monitor_username='monitor';Query OK, 1 row affected (0 sec) MySQL [(none)] > set mysql-monitor_password='centos'; Query OK, 1 row affected (0 sec)

Save the disk and take effect

MySQL [(none)] > load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > save mysql variables to disk; Query OK, 97 rows affected (0.00 sec)

Take a look at the relevant logs. The reason for the previous error is that you did not use the monitor password to connect with monitor by default (as can be seen in proxysql.cnf), so there will be an error. When you have finished adding the account, you will be successful.

[root@Proxy ~] # vi / etc/proxysql.cnf monitor_username= "monitor" monitor_password= "monitor" MySQL [(none)] > select * from mysql_server_connect_log + -- + | hostname | port | time_start_us | connect_success_time_us | connect_error | +- -+ | 192.168.205.47 | 3306 | 1565603995322153 | 0 | | Access denied for user 'monitor'@'192.168.205.37' (using password: YES) | | 192.168.205.47 | 3306 | 1565604055779260 | 0 | Access denied for user' monitor'@'192.168.205.37' (using password: YES) | | 192.168.205.57 | 3306 | 1565604159035893 | 3871 | NULL | | | 192.168.205.47 | 3306 | 1565604159905593 | 3563 | NULL | +-| +-+-+ 22 rows in set (0.00 sec) View the result MySQL of ping [(none) )] > select * from mysql_server_ping_log + -+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +-+ -- + | 192.168.205.57 | 3306 | 1565604094739272 | 0 | Access denied for user 'monitor'@'192.168.205.37' (using password: YES) | | 192.168.205.47 | 3306 | 1565604094919486 | 0 | Access denied for user 'monitor'@'192.168.205.37' (using password: YES) | | 192.168.205.57 | 3306 | 1565604099107658 | 745 | NULL | | 192.168.205.47 | 3306 | 1565604099295895 | 358 | NULL | +-| -+-+ 122 rows in set (0.00 sec) View read_only value is empty Because there is no grouping MySQL [(none)] > select * from mysql_server_read_only_log Empty set (0.00 sec) check that the replication result is empty MySQL [(none)] > select * from mysql_server_replication_lag_log;Empty set (0.00 sec). What needs to be modified is the mysql_replication_ hostgroups table in the main library, which has three fields: writer_hostgroup, reader_hostgroup,comment, the id of the specified write group is 10, the id of the read group is 20MySQL [(none)] > insert into mysql_replication_hostgroups values (10Magne20, "test"); Query OK, 1 row affected (0.00 sec)

Save effective

MySQL [(none)] > load mysql servers to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > save mysql servers to disk Query OK, 0 rows affected (0.02 sec) proxySQL will judge read_only based on the account just connected and automatically add two servers to this table: MySQL [(none)] > select hostgroup_id,hostname,port,status,weight from mysql_servers. +-+ | hostgroup_id | hostname | port | status | weight | +- +-+ | 20 | 192.168.205.57 | 3306 | ONLINE | 1 | | 10 | 192.168.205.47 | 3306 | ONLINE | 1 | +-+ 2 rows in set Sec) MySQL [(none)] > select * from mysql_server_read_only_log Check that the value of read_only already has a record +-+-+ | hostname | port | time_start_us | success_time_us | read_only | error | + -+ | 192.168.205.57 | 3306 | 1565605365323639 | 565 | 1 | NULL | | 192.168.205.47 | 3306 | 1565605365353823 | | 1595 | 0 | NULL | | 192.168.205.57 | 3306 | 1565605366824223 | 1275 | 1 | NULL | | 192.168.205.47 | 3306 | 1565605366844952 | 1607 | 0 | NULL | +-| -+ 280rows in set (0.02sec) proxysql still doesn't know which sql statements to read For writing, we have to define it and let it distinguish and send it to different servers. Create an accessible account on the primary node MariaDB [(none)] > grant all on *. * to sqluser@'192.168.205.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec) use this account to write when defining the connection on the proxysql, that is, use 10 groups, that is, write MySQL [(none)] > insert into mysql_users (username,password,default_hostgroup) values (' sqluser','centos',10) on the main server. Query OK, 1 row affected (0.00 sec)

Save effective

MySQL [(none)] > load mysql users to runtime;Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > save mysql users to disk;Query OK, 0 rows affected (0.01 sec) now we use the previous account to connect to the database (note that the connection test is proxysql), it will only be sent to the master server by default, because there is no slave server defined. [root@Proxy ~] # mysql-usqluser-pcentos-P6033-h227.0.0.1-e'select @ @ server_id'+-+ | @ @ server_id | +-+ | 47 | +-+ [root@Proxy ~] # mysql-usqluser-pcentos-P6033-h227.0.0.1-e'create database testdb' [root@Proxy ~] # mysql-usqluser-pcentos testdb-P6033-h227.0.0.1-e 'create table t (id int)' check the libraries and tables you just created on the master server and slave server Synchronized past MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | testdb | +-+ 6 rows in set (0.00 sec) MariaDB [(none)] > select * from testdb.t Empty set (0.00 sec)

At present, proxysql still doesn't know what is read and what is written, so all operations will be sent to 10 groups, that is, the main server, so now let's build SQL statement rules.

# select.* for updata is sent to 10 groups for writing, while the other select is sent to 20 groups at the beginning, that is, to read. What about undefined ones? By default, it will be sent to 10, MySQL [(none)] > insert into mysql_query_rules-> (rule_id,active,match_digest,destination_hostgroup,apply) VALUES-> (1jue 1mai'^ SELECT.*FOR UPDATE$',10,1), (2jinglle'^ SELECT',20,1); Query OK, 2 rows affected (0.00 sec) # check the rule you added MySQL [(none)] > select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules +-- +-- +-+ | rule_id | active | match_digest | destination_hostgroup | apply | +- -+ | 1 | 1 | ^ SELECT.*FOR UPDATE$ | 10 | 1 | | 2 | 1 | ^ SELECT | 20 | 1 | +- +-+-+ 2 rows in set (0.00 sec)

Save effective

MySQL [(none)] > load mysql query rules to runtime;Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > save mysql query rules to disk Query OK, 0 rows affected (0. 01 sec) then test the command we ran above again The select statement sends [root@Proxy ~] # mysql-usqluser-pcentos-P6033-h227.0.0.1-e'select @ @ server_id' +-+ on slave server 57 on 20 | @ @ server_id | +-+ | 57 | +-+ what if it is executed as a transaction We found that it will be sent to the primary node, because begin starts with neither the beginning of select updata nor the beginning of select, so it is sent to the default node [root@Proxy ~] # mysql-usqluser-pcentos-P6033-h227.0.0.1-e 'begin Select @ @ server_id Commit' +-+ | @ @ server_id | +-+ | 47 | +-+ if it is a slave table creation operation, it is sent to the master node Then the master node is copied to the slave node [root@Proxy] # mysql-usqluser-pcentos testdb-P6033-h227.0.0.1-e 'create table T1 (id int)'. Looking at this table on the master node, it is found that Database changedMariaDB [testdb] > show tables has been built. +-+ | Tables_in_testdb | +-+ | t | | T1 | +-+ 2 rows in set (0.00 sec) MariaDB [testdb] > show tables is also copied on the node. +-+ | Tables_in_testdb | +-+ | t | | T1 | +-+ 2 rows in set (0.00 sec) We can use proxsql to check whether the schedule is successful MySQL [(none)] > select hostgroup hg,sum_time,count_star Digest_text from stats_mysql_query_digest order by sum_time desc +-+-- + | hg | sum_time | count_star | digest_text | +-+-- -- + | 10 | 18692 | 1 | create table t (id int) | | 10 | 5704 | 1 | create table T1 (id int) | | 10 | 2002 | 2 | select @ @ server_id | | 20 | 1546 | 1 | | Select @ @ server_id | | 10 | 819 | 1 | begin | | 10 | 717 | 1 | create database testdb | | 10 | 240 | 1 | commit | | 10 | 0 | 2 | select @ @ version_comment limit? | | 10 | 0 | 4 | | | select @ @ version_comment limit? | +-+ |

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.