In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
This paper illustrates the principle and usage of master-slave replication and read-write separation of MySQL. Share with you for your reference, the details are as follows:
Master-slave replication
Concept
Operations that affect the MySQL-A database are written to the local log system An after the database is executed.
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.
In the above model, MYSQL-An is the master server, that is, master,MYSQL-B is the slave server, or slave.
Log system A, in fact, is the binary log in the log type of MYSQL, that is, it is specially used to save all actions that modify database tables, namely bin log. [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 changes in its own database. It has a sense of relay, which is called relay log, or relay log.
It can be found that through the above mechanism, the database data of MYSQL-An and MYSQL-B can be guaranteed to be consistent, but there must be a delay in time, that is, the data of MYSQL-B is lagging.
Even if the network factor is not taken into account, the database operation of MYSQL-A can be performed concurrently, but MYSQL-B can only read one item from relay log and execute it. So MYSQL-A writes frequently, and MYSQL-B probably can't keep up with it. ]
Solve the problem
How not to lose data
Backup
Separation of reading and writing
Database load balancing
High availability
Environment building
1. Prepare the environment
The two windows operating systems ip are 172.27.185.1 (master) and 172.27.185.2 (slave), respectively.
two。 Connect to the master service (172.27.185.1) server and assign account permissions to the slave node.
GRANT REPLICATION SLAVE ON. TO 'root'@'172.27.185.2' IDENTIFIED BY' root'
3. Add to the main service my.ini file
Server-id=200log-bin=mysql-binrelay-log=relay-binrelay-log-index=relay-bin-index
Restart the mysql service
4. Add in the slave service my.ini file
Server-id = 210replicate-do-db = itmayiedu # need to synchronize the database
Restart the mysql service
5. Synchronize master database from slave service
Stop slave;changemaster to master_host='172.27.185.1',master_user='root',master_password='root';start slave;show slave status
Matters needing attention
① must be in the same local area network
② uses 360WiFi to create local area network
③ better turn off all the firewalls.
What is the separation of reading and writing
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.
Benefits of separation of reading and writing
1) share the server pressure and improve the system processing efficiency of the machine.
Read-write separation is suitable for scenarios where read is far more than write. If there is a server, when there is a lot of select, update and delete will be blocked by the data in these select access, waiting for the end of select, and the performance of concurrency is not high, while the master and slave are only responsible for writing and reading respectively, greatly alleviating the contention of X lock and S lock.
If we have 1 master and 3 slaves, regardless of the unilateral setting of the slave library mentioned in 1 above, suppose there are 10 writes and 150 reads in 1 minute. So, 1 master and 3 slaves is equivalent to a total of 40 writes, while the total number of reads remains the same, so on average each server undertakes 10 writes and 50 reads (the master database does not undertake read operations). Therefore, although the write remains the same, the read is greatly apportioned and the system performance is improved. In addition, when the read is apportioned, the performance of the write is indirectly improved. Therefore, the overall performance has been improved, to put it bluntly, it is to trade the machine and bandwidth for performance.
2) increase redundancy and improve service availability. When one database server goes down, you can adjust another slave database to restore services as quickly as possible.
Principle of master-slave replication
Depending on the binary log, binary-log.
Record the statement in the binary log that causes the database to change
Insert 、 delete 、 update 、 create table
The difference between Scale-up and Scale-out
Scale Out means that Application can be expanded horizontally. Generally speaking, for data center applications, Scale out means that when more machines are added, applications can still make good use of the resources of these machines to improve their efficiency and achieve good scalability.
Scale Up means that Application can be extended vertically. Generally speaking, for a single machine, Scale Up is worth it when a computing node (machine) adds more CPU Cores, storage devices, and uses more memory, the application can make full use of these resources to improve its efficiency and achieve good scalability.
MyCat, what is Mycat?
Is an open source distributed database system, but because the database generally has its 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 a middleware that acts as a bridge between the application and the database.
Before the emergence of Mycat middleware, MySQL master-slave replication cluster, if you want to achieve the separation of read and write, it is usually 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. From the original library, it is divided into multiple sliced databases, and all the sliced database clusters form the completed database storage, which is somewhat similar to the RAID0 in the disk array.
Mycat installation
Create a table structure
CREATE DATABASE IF NOT EXISTS `weibo_ simple`;-Table structure for `t_ users` user table-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 -Table structure for `t_ message` Weibo table-DROP TABLE IF EXISTS `tmessage` 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_ deletes.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 `troommessageroomibfk1` FOREIGN KEY (`user_ id`) REFERENCES `t_ users` (`user_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8
Configure server.xml
Mycat mycat mycat_red mycat true
Configure schema.xml
Select user ()
Configure the rule.xml file
User_id func1 autopartition-long.txt
To better locate errors, modify log4j.xml
Double-click startup_nowrap.bat to start
common problem
If SHOW MASTER STATUS is, add a line to the my.ini file
Log-bin=mysql-bin
Assign permissions to the account
Grant all privileges on. To 'root'@'172.27.185.1' identified by' root'
More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"
It is hoped that what is described in this article will be helpful to everyone's MySQL database design.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.