MySQL > select * from user;

What is read-write separation

The database is divided into master and slave libraries, with the master library only writing data and multiple slave libraries only reading data.

Two, read and write separation to solve what

Read/write separation is mainly used to solve the performance bottleneck of database read. Of course, some people say that to solve the bottleneck of reading, can be solved with cache. True, but if caching is already in use, is there still a bottleneck? This is when we think about load balancing, which is solved by sharing read requests from multiple libraries.

Third, other database bottlenecks

If a single table contains a large amount of data, the data can be read or queried slowly. How can I improve performance? First, database sharding comes to mind. Database segmentation is divided into horizontal segmentation and vertical segmentation. Horizontal partitioning is to divide the data in a table into multiple databases or tables. For example, by region, the data in the same province is divided into a database or table. In this way, a large table is divided into multiple smaller databases or tables. Vertical segmentation is to divide the fields in the table into two tables, and then associate the two tables, such as an article table (with title, author, publication time, content, keywords, classification and other fields), you can separate the content out of a separate table, and then associate with the article table. Secondly, search engines can be used to deal with, especially full-text retrieval requirements. You can use Solr or Elasticsearch to generate indexes for queries. Also, change the database, not MySQL. (Please look for information such as TiDB)

MySQL5.7.10 Read-write separation deployment

1, Proxysql

Proxysql is a lightweight, high-performance MySQL middleware developed in C++ that implements read/write separation, SQL routing, simple branch libraries and tables, caching, and node monitoring.

2. Server

MySQL5.7.10 database installation: see my other article, CentOS7 installing Mysql5.7

Proxysql installation: See my other article where CentOS7 installs ProxysQL-1.4.16

3. Master library configuration

[client] port=3306 default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] Character_set_server = UTf8MB4 init_connect='SET NAMES utf8MB4 'basedir= /usr/java-mysql-5.7.10-linux-glibc2.5-x86_64 Datadir =/usr/ Java /mysql-5.7.10-linux-glibc2.5-x86_64/data socket=/ TMP /mysql.sock # case-insensitive lower_case_table_names = 1 Sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Log-error =/var/log/mysqld.log pid-file=/usr/ Java /mysql-5.7.10-linux-glibc2.5-x86_64/data/mysqld.pid Server-id =32 # Enable/set binary log file name log-bin=master-bin binlog_format = MIXED # Record updates received from the master server to the slave server's own binary log file Log-slave-updates # Controls how often the binlog is written. Sync-binlog =1 # Sync-binlog =1 # Auto_increment_offset = 1 # This parameter is generally used in master/master synchronization to stagger the increment_offset = 1 # Number of days in which binary logs are automatically deleted or expire. The default value is 0, indicating that the data will not be automatically deleted. Expire_logs_days =7 # Copy the function to the slave log_bin_trust_function_creators = 1 #binlog_cache_size = 128m # max_binlog_cache_SIZE = Binlog-ignore -db = mysql binlog_ignore_db = information_schema binlog_ignore_db = performation_schema binlog_ignore_db = sys # Binlog-do-db = test binlog-do-db = shiroCopy the code

Restart the main library

Check whether log_bin is enabled

show variables like 'log_bin';
Copy the code

Checking master Status

show master status\G;
Copy the code

4. Slave library configuration

[client] port=3306 default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] Character_set_server = UTf8MB4 init_connect='SET NAMES utf8MB4 'basedir= /usr/java-mysql-5.7.10-linux-glibc2.5-x86_64 Datadir =/usr/ Java /mysql-5.7.10-linux-glibc2.5-x86_64/data socket=/ TMP /mysql.sock # case-insensitive lower_case_table_names = 1 Sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" Log-error =/var/log/mysqld.log pid-file= /usr/java-mysql-5.7.10-linux-glibc2.5-x86_64 /data/mysqld.pid Server-id =33 #read_only set database to read-only, so as to prevent secondary database data from being changed, the Super account still has write permission, so that the Super account is read-only. Read_only =1 log-slave-updates specifies which library replication slave wants to replicate. #replicate-do-db = test When the network between the Master and Slave of MySQL is interrupted, but the Master and Slave are not aware of it (such as firewall or routing problems). The Slave waits for the number of seconds set by slave_net_timeout before it considers that the network is faulty. Then, the master database data slave-net-timeout = 60 log_bin_trust_function_Creators = 1 is repartnered and pursuedCopy the code

5. Restart the master and slave libraries

service mysql stop
service mysql start
Copy the code

6. Connect to the secondary library and configure the primary library information

/usr/ Java /mysql-5.7.10/bin/ mysql-uroot -p /usr/ Java /mysql-5.7.10 Change master to master_host='192.168.137.32', master_user='root', master_password=' 123ABC ', master_port=3306, master_log_file='master-bin.000002', master_log_pos=154; Start slave;Copy the code

Viewing synchronization Status

show slave status\G;

Pit:

Slave_IO_Running: No, master and slave have equal MySQL server UUIDs. The UUID of the auto-. conf file in /data is the same. After the UUID of the secondary node is changed, the synchronization between the primary and secondary nodes is normal

7. Enable replication multithreading (recommended)

Because the database has multiple clients or application connections, if a SQL execution is long or deadlocked, the SQL backlog of the master server is not synchronized to the slave library, resulting in inconsistency between the master and slave. Therefore, it is recommended to enable multi-threading (MySQL5.7 support).

show variables like 'slave_parallel%';
Copy the code

stop slave; set global slave_parallel_type='logical_clock'; set global slave_parallel_workers=100; Start slave; show processlist;Copy the code

8. Configure Proxysql

(1) Add MySQL nodes

Use the mysql client to connect to the Proxysql management interface. The default administrator account and password are admin. If mysql is not installed, you can decompress the mysql tar package to use the mysql client.

/usr/ Java /mysql-5.7.10-linux-glibc2.5-x86_64/bin/ mysql-uadmin -padmin -p6032 -h127.0.0.1 --prompt 'admin>'Copy the code

Add a MySQL server node

Insert into mysql_servers (hostgroup_id, the hostname and port) values (10, '192.168.137.32, 3306); Insert into mysql_servers (hostgroup_id, the hostname and port) values (10, '192.168.137.33, 3306);Copy the code

Check whether it is successful.

select * from mysql_servers;
Copy the code

Load to Runtime and save to Disk

load mysql servers to runtime;
save mysql servers to disk;
Copy the code

(2) Monitor MySQL nodes

Set the monitoring user and password

set mysql-monitor_username='root';
set mysql-monitor_password='123abc';
Copy the code

After the modification, load it to RUNTIME and save it to Disk

load mysql variables to runtime;
save mysql variables to disk;
Copy the code

Modify the mysql_REPLICATION_hostgroups table. This table has only three fields: the first field is named writer_hostgroup, the second field is reader_hostgroup, and the third field is a comment field, which can be written at will.

Insert into mysql_replication_hostgroups values (10, 20, 'LLF);Copy the code

To view:

select hostgroup_id,hostname,port,status,weight from mysql_servers; 
Copy the code

In 10 groups

To take effect

load mysql servers to runtime;
save mysql servers to disk;
Copy the code

Once the read_only value is detected, the module will automatically move some nodes to read/write groups based on the read_only value. Of course, if your slave library does not have read_only = 1, then 33 will still be in group 10.

(3) Configure the MySQL account

Here I use the root account directly

insert into mysql_users(username,password,default_hostgroup) values('root','123abc',10); Load mysql users to runtime; save mysql users to disk;Copy the code

To view:

select * from mysql_users;
Copy the code

(4) Set transaction persistence

update mysql_users set transaction_persistent=1 where username='root'; Load mysql users to runtime; save mysql users to disk;Copy the code

(5) Configure read/write split routes

Insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR The UPDATE $', 10, 1), (2, 1, '^ the SELECT, 20, 1); Load mysql query rules to runtime; save mysql query rules to disk;Copy the code

Check the reading group

/usr/ Java /mysql-5.7.10-linux-glibc2.5-x86_64/bin/ mysql-uroot -p123abc -p6033 -h127.0.0.1 -e 'select @@server_id'Copy the code

Check the writing group

/usr/java-mysql-5.7.10-linux-glibc2.5-x86_64 /bin/ mysql-uroot -p123abc -p6033 -h127.0.0.1 -e 'start transaction; select @@server_id'Copy the code

Read/write separation is configured.

(6) Modify the mysql version

Update global_variables set variable_value="5.7.10" WHERE variable_name='mysql-server_version'; Mysql mysql variables to run; mysql mysql variables to run; mysql mysql variables to run; save mysql variables to disk;Copy the code