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

7. MySQL sub-database sub-table (1) introduction to the concept of sub-library sub-table

There are two ways to split the library table of    MySQL: vertical split and horizontal split.

   vertical split: vertical split is to divide tables into different database tables by module (of course, the principle is not to break the third paradigm), this split is very common in the evolution of large websites. When a website is still very small, there are only a small number of people to develop and maintain, the modules and tables are all together, and when the website continues to enrich and grow, it will become multiple subsystems to support. At this time, there is a need to divide the table by module and function. In fact, compared with vertical segmentation, a further step is service-oriented transformation, which simply means splitting the original strongly coupled system into multiple weakly coupled services to meet business needs through calls between services. therefore, after the table is split out, it should be exposed in the form of services, rather than directly calling the tables of different modules. (vertical split for distributed scenarios)

   horizontal split: to solve the problem of large amount of data in a single table, horizontal split is to divide a table into different tables or databases according to certain rules. For example, in large-scale e-commerce systems, the number of members is increasing every day. How to optimize the query after reaching a certain bottleneck. Optimization is achieved by dividing the table data horizontally into different tables. (implementation rules: hash, time, different dimensions)

   popular understanding: split rows horizontally, split row data into different tables, split vertically, split table data into different tables.

(2) the case of horizontal subtable

   sub-table principle: take the module split (consistent hash), you can distribute the data more evenly.

Here we take three tables as an example:

Example: first I create three tables user0 / user1 / user2, and then I create the uuid table, which is used to provide self-increasing id.

Code implementation:

Create table user0 (id int unsigned primary key, name varchar (22) not null default'', pwd varchar (22) not null default'') engine=myisam charset utf8;create table user1 (id int unsigned primary key, name varchar (22) not null default'', pwd varchar (22) not null default'') engine=myisam charset utf8;create table user2 (id int unsigned primary key, name varchar (22) not null default'', pwd varchar (22) not null default'') engine=myisam charset utf8;create table uuid (id int unsigned primary key auto_increment) engine=myisam charset utf8 / / subtable logic @ Servicepublic class UserService {@ Autowired private JdbcTemplate jdbcTemplate; public String regit (String name, String pwd) {/ / 1. First get the custom growth ID String idInsertSQL = "INSERT INTO uuid VALUES (NULL);"; jdbcTemplate.update (idInsertSQL); Long insertId = jdbcTemplate.queryForObject ("select last_insert_id ()", Long.class); / / 2. Determine the storage table name String tableName = "user" + insertId% 3; / / 3. Registration data String insertUserSql = "INSERT INTO" + tableName + "VALUES ('" + insertId + "','" + name + "','" + pwd + "');"; System.out.println ("insertUserSql:" + insertUserSql); jdbcTemplate.update (insertUserSql); return "success";} public String get (Long id) {String tableName = "user" + id% 3 String sql = "select name from" + tableName + "where id=" + id; System.out.println ("SQL:" + sql); String name = jdbcTemplate.queryForObject (sql, String.class); return name;}} 8.MySQL master-slave replication and read-write separation (1) introduction to master-slave replication

In the figure above   , 192.168.8.40 is the master node (MYSQL-A) and 192.168.8.41 is the slave node (MYSQL-B).

   affects the operation of the MySQL-A database, and after the database is executed, it is written to the local log system A. Suppose that the database event operations in the changed log system are sent to MYSQL-B through the network on port 3306 of MYSQL-An in real time. After the MYSQL-B is received, it is written to the local log system B, and then the database events are completed in the database one by one. Then, with the change of MYSQL-A, MYSQL-B will also change, which is the so-called replication of MYSQL, that is, MYSQL replication.

The binary log in the log type of    MYSQL, that is, bin log, which is specifically used to hold all actions that modify database tables. [note that MYSQL writes to the binary log after executing the statement and before releasing the lock to ensure transaction security]

   log system B is not a binary log, because it is copied from the binary log of MYSQL-A, and it is not caused by the change of its own database, so it has a sense of relay, which is called relay log, or relay log.

Problems caused by    master-slave replication:

  -how the primary server achieves load balancing and high availability.

  -how to ensure that data is not lost.

  -how to ensure the consistency of master-slave data.

(2) introduction of separation of reading and writing

Introduction to   : in the database cluster architecture, the master database is responsible for handling transactional queries, while the slave database is only responsible for handling select queries, so that the division of labor between the two can improve the overall read and write performance of the database. Of course, another function of the master database is to synchronize data changes caused by transactional queries to the slave database, that is, write operations.

The advantage of    write separation: share the server pressure and improve the system processing efficiency of the machine. Increase redundancy, improve server performance, and restore services from another library in the fastest way when one server goes down.

(3) Linux configuration for MySQL master-slave replication

Environment introduction:

① install MySQL

# here the editor is MySQL installed through rpm&&yum

# download installation package $wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm# install $rpm-ivh mysql57-community-release-el7-8.noarch.rpm$yum install mysql-server# start MySQL service $systemctl start mysqld# view root initial password $grep 'temporary password' / var/log/mysqld.log

# reset root password

$mysql_secure_installation

Note:

N is configured here, otherwise you cannot log in to MySQL using root.

# modify root password and set it for remote access: mysql > use mysql;mysql > update user set authentication_string=password ("123456") where user= "root"; mysql > flush privileges;mysql > select 'host','user' from user where user='root'

Mysql > UPDATE user SET grant_priv ='Y' WHERE user = 'root';mysql > select host,user from user

Mysql > update user set host ='% 'where user =' root';mysql > select host,user from user

Mysql > flush privileges;mysql > quit# tests whether $mysql-uroot-p-h 192.168.xxx.xxx is configured successfully

② creates a database

Create a database in master and slave respectively:

Mysql > create database test

Note: the slave database must exist in the synchronized library in the master database, otherwise an error will be reported during synchronization:

③ modifies the configuration in master

$vim / etc/my.cnf#master join server-id=1log-bin=mysql-binlog-slave-updates=1# database that needs to be synchronized binlog-do-db=test# ignored data binlog-ignore-db=mysql

④ creates a salve synchronization account in master

Mysql > grant replication slave on *. * to 'user1'@'192.168.130.133' identified by' Zy.123456';mysql > flush privileges

⑤ restart master and check the log

$systemctl restart mysqldmysql > show master status

⑥ modifies the configuration of MySQL in slave

# slave joins the database to be synchronized by server-id=2log-bin= mysql-binrelay-log= mysql-relay-binread-only=1log-slave-updates=1#. Not writing this line means synchronizing all databases replicate-do-db=test

⑦ restart slave and verify that master can be connected

$systemctl restart mysqld$mysql-uuser1-p123456-h 192.168.130.133mysql > show grants for user1@192.168.130.133

⑧ sets up slave replication and starts slave

Mysql > change master to master_host='192.168.130.134',master_user='user1',master_password='Zy.123456';-- start slavemysql > start slave;mysql > SHOW SLAVE STATUS

The main check is to see if both Slave_IO_Running and Slave_SQL_Running columns are YES.

⑨ tests whether the master and slave services are synchronized

Execute on the primary server:

Mysql > use test;mysql > create table test (id int,name char (10)); mysql > insert into test values (1meme Zaq'); mysql > insert into test values (1m Zaq'); mysql > select * from test

At this point, view the slave server:

To this master-slave copy, the configuration is complete!

(4) supplement

Solutions to the data already in the    main library:

  -scenario 1: choose to ignore the data before the main database and do not process it. This scheme is only suitable for unimportant and dispensable data, and can tolerate the inconsistency of master-slave database data in business.

  -Plan 2: back up the data of the master database, then import the exported data from the master database into the slave database, and then turn on master-slave replication to ensure the consistency of the master-slave database data.

Here the editor introduces how to use scenario 2 for data synchronization.

① backup data

Suppose we have a library here: weibo and the corresponding data in the database:

# Lock the master table mysql > flush tables with read lock; # ensure that the table can only be read

# check the status of the master database at this time, and record bin-file and pos

Mysql > show master status

# add: in / etc/my.cnf:

[mysqldump] user = rootpassword = rootpassword# restart service: [root@zzy ~] # systemctl restart mysqld# backup database mysqldump weibo > weibo_back.sql then transfer the backup data file to the machine and execute it in mysql: source / path/ weibo_back.sql

At this time, the data of the master-slave database has been synchronized!

② modifies configuration

The configuration of the master-slave server here is the same as that mentioned in the previous section.

③ starts slave

Mysql > stop slave;mysql > reset slave; change master to mysql > master_host='192.168.130.134', > master_user='user2', > master_password='Zy.123456', > master_log_file='mysql-bin.000004', > master_log_pos=154;mysql > start slave;mysql > SHOW SLAVE STATUS

Note: the master_log_file and master_log_pos here must be the same as those found from the master database with the "show master status" command.

④ unlocks the master database

Mysql > unlock tables

Then we can test and insert a record in the master database to see if there is data synchronization from the slave database.

9. Introduction and use of Mycat (1) introduction of mycat

   mycati is an open source distributed database system, but because databases generally have their own database engine, and mycat does not have its own unique database engine, so strictly speaking, it can not be regarded as a complete database system, but only a service middleware between the application and the database.

   between the emergence of mycat middleware, MySQL master-slave replication cluster, if you want to achieve the separation of read and write, it is generally implemented in the program segment, which brings a problem, that is, the coupling between the data segment and the program is too high. If the address of the database has changed, then my program has to be modified accordingly. If the database accidentally hangs up, it also means that the program is unavailable, and for many applications Is not acceptable. The introduction of Mycat middleware can well decouple the program from the database, so that the program only needs to pay attention to the address of the database middleware without knowing how the underlying database provides services. A large number of general data aggregation, transactions, data source switching and other work are handled by the middleware. The principle of Mycat middleware is to slice the data, which is divided into multiple sliced databases from the original library, and all the sliced database clusters form the completed database storage, which is somewhat similar to the RAID0 in the disk array.

   can easily achieve the read-write separation of the database by using mycat, and because of its characteristics, it not only achieves load balancing, but also improves the security of the cluster.

(2) installation of mycat

   note that here mycat only does load balancing, but master-slave replication is not done, so if you want the MySQL cluster to be able to read and write separately, you need to use mycat and follow the original master-slave replication configuration.

   A pit encountered by the editor here is that it is best to use mycat1.5, that is, the above version, otherwise some link tools will report an error: there is a no mycat database selected problem!

The editor here uses the 1.6Linux version.

First download the corresponding version: http://dl.mycat.io/1.6-RELEASE/

① ensures that MySQL has JDK1.8 in its machine environment.

Need to configure JAVA_HOME!

Note here that experience recommends that the configuration should be under ~ / .bashrc of the corresponding user, not all in / etc/profile.

② configuration mycat

Here you need to configure four files: server.xml, schema.xml, rule.xml, and log4j2.xml

Here is a brief introduction to the editor:

Server.xml: configuring user

Schema.xml: configure associated tables and libraries, as well as host and password to connect to MySQL

Rule.xml: configure the corresponding sharding rules

Log4j2.xml: configure the log level.

# server.xml join: 123456 mycat 123456 mycat true#schema.xml modified to: select user ()

# rule.xml is basically unchanged

# log4j2.xml modifies log to debug

③ starts mycat

Enter the bin of mycat

# run $sh mycat start / sh startup_nowrap.sh

④ Test connection

Here you can connect through MySQL:

[root@zzy bin] # mysql-umycat-P8066-p123456-h227.0.0.1

The user and password here are configured in server.xml.

You can also connect through a tool, but it is important to note that the database you are connecting to here must be mycat.

(3) corresponding questions: 1) Error: Could not create the Java Virtual Machine. OpenJDK Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0.invalid maximum heap size:-Xmx4G

The reason for this problem is that the maximum memory required for mycat startup is 4 GB and the minimum memory is 1 GB by default. The virtual machine used here by Xiaobian does not have such a large memory, so you need to modify $MYCAT_HOMT/conf/ wrapper.conf:

Just adjust the configuration.

2) Error: Exception thrown by the agent: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: zzy: zzy: Name or service not known

The problem is that the mapping between the current host and IP needs to be configured in / etc/hosts:

(4) corresponding test

   here we have configured mycat and master-slave replication, here we only need to connect to port 8066 of mycat, and users can CRUD the database through mycat. Here we connect to mycat:

Insert a record in the t _ message table under the weibo library:

Then look at the master database and the slave database respectively:

At this point, the read-write separation of the high available version of MySQL's load balancing is complete!

The statements of the above two test tables:

DROP TABLE IF EXISTS `t_ message` CREATE TABLE `t_ message` (`tid` varchar (64) NOT NULL COMMENT 'Weibo ID', `user_ id` varchar (64) NOT NULL COMMENT' publish user', `messages_ info` varchar (255) DEFAULT NULL COMMENT 'Weibo content', `messages_ time`datetime DEFAULT NULL COMMENT 'release time', `messages_ commentnum` int (12) DEFAULT NULL COMMENT 'comments', `message_ deletion `tinyint (1) NOT NULL COMMENT 'deletion mark 1: deleted 0: not deleted' `message_ viewnum` int (12) DEFAULT NULL COMMENT 'views', PRIMARY KEY (`messages_ id`), KEY `user_ id` (`user_ id`), CONSTRAINT `tviewmessageviews ibfk1` FOREIGN KEY (`user_ id`) REFERENCES `t_ users` (`user_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 LOCK TABLES `tmessage` WRITE;;INSERT INTO `tmessage`tmessage` VALUES (2019-09-01 0014), (2019-09-01 00001), (2019-0901-01 00lb), (2019-08-21 0000 VALUES (2019-08-21, 0000), (2019-08-2100), (2019-08-2100); UNLOCK TABLES;DROP TABLE IF EXISTS `tuss` CREATE TABLE `tuss` (`user_ id` varchar (64) NOT NULL COMMENT 'registered user ID', `user_ email` varchar (64) NOT NULL COMMENT' registered user mailbox', `user_ password` varchar (64) NOT NULL COMMENT 'registered user password', `user_ Nikename` varchar (64) NOT NULL COMMENT 'registered user nickname', `user_ creatime` datetime NOT NULL COMMENT 'registration time', `user_ status` tinyint (1) NOT NULL COMMENT 'authentication status 1: verified `Deletedelete`tinyint (1) NOT NULL COMMENT 'delete mark 1: deleted 0: not deleted', PRIMARY KEY (`user_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 LOCK TABLES `t _ users` WRITE INSERT INTO `tusers` VALUES (2019-08-09 0000), (2019-08-09), (2019-08-09), (2019-08-09), (2019-08-09), (2019-08-09), (2019-08-09) 'www.41523511@qq.com','1456','zasdsa','2018-08-09 00 0012) UNLOCK TABLES

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.