In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Recently, it is a headache to do slow sql optimization. I see that the slow sql is full of aggregate statistical sql such as select count (*) or sum. Because my database is a traditional master-slave, or one master and multiple slaves, such slow sql is counted directly on the database, which has a certain impact on the online, and because of multiple db masters and slaves, it is relatively troublesome to count some related data. Therefore, I decided to build a multi-master-slave database specially used to query the operation or aggregate statistical operations, collect online data, facilitate query, and reduce the impact of these statistics on the online database!
Before building, let's say that previous official mysql versions only support replication from one master to one slave, while mariadb has supported replication from multiple master to one slave since 10. Applicable scenarios for mariadb multi-source (master) replication: 1) now many companies often want to divide the database into tables, so it's easy to put them in the same instance (but in this case, they don't have much advantage over partition tables). If you split the tables into different instances, we often need to do some summary data statistics, which can't be done at once without middleware or certain program code. Or want to do online boast instance join between two different db, such an operation, if there is a multi-master-slave library, there is a complete data, it will be much easier! 2) backup, if you want a complete online data backup, backup multi-master and slave database is a good choice!
Build the environment:
192.168.190.128 master128 (Master 1)
192.168.190.129 master129 (Master 2)
192.168.190.130 slave (from)
Step 1, there is no special difference between the main steps of building and the traditional master and slave, but different commands will be different. I have data here because both master128 and master129 have data, so I take the backup and restore method, and first collect the data of the two masters into the slave database: 1.master128 has a large amount of data, xtrabackup backup is adopted, and mydump-master-data-B dbname1,dbname2 is used for 2.master129. Backup method such as all0414.sql
Step 2. Use innobackupex to restore the maser128 data to slave. The specific recovery steps will not be discussed here, using mysql-U-P
< all0414.sql的恢复方式将master129的数据恢复到slave中,129这里面数据库访问权限,需要单独提添加下! 步骤三、 CHANGE MASTER 'master128' to MASTER_HOST='192.168.190.128',MASTER_USER='rep_user', MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=335; CHANGE MASTER 'master129' to MASTER_HOST='192.168.190.129',MASTER_USER='rep_user', MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=486; 步骤四: start slave 'master128'; start slave 'master129'; show all slaves status\G;Step 5. The above figure shows that synchronization is normal. We insert data to verify synchronization in different db names. For the same db name, we can also synchronize normally. If we want to ignore synchronization, we need to specify a different master128.replicate_ignore_db=mysql or master129.replicate_ignore_db=mysql. If not, specify replicate_ignore_db=mysql directly and the default mysql will ignore all!
Where the commands are different:
Stop all slaves, or stop slave 'maser128', stop slave' maser129'
Reset slave 'maser128' all; reset slave' maser129' all
Show slave 'master128' status\ G show slave' master129' status\ G
Show all slaves status\ G
Refer to the official documentation in other different places: https://mariadb.com/kb/zh-cn/multi-source-replication/#todo
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.