CERN 的 MySQL 社區版.pdf

1、MySQL Community Edition at CERNAbel Cabezas Alonso 1st May 2024Abel Cabezas Alonso|MySQL Community Edition CERNAbel Cabezas Alonso Database Engineer at CERN since 2019 Transition as DevOps engineer Early career as Software Developer1st May 20242Abel Cabezas Alonso abel.cabezas.alonsocern.chAbel Cabe

2、zas Alonso|MySQL Community Edition CERNEstablished in 1954 23 Member states Our mission:Unveil how the universe works and what it is made of Provide a unique range of particle accelerator facilities to enable research at the forefront of the human knowledge Unite people from all over the world to pu

3、sh the frontiers of science and technology1st May 20243Abel Cabezas Alonso|MySQL Community Edition CERNThe Large Hadron Collider1st May 20244Worlds largest particle accelerator 27 km(16.8 miles)ring of superconducting magnetsMagnets are cooled to-271.3C(-456.34F)a temperature colder than outer space

4、Particles circle the accelerator 11.245 times/s reaching 99.9999991%the speed of lightLead ion collisions create temperatures of 100 000 x hotter than the heart of the sunAbel Cabezas Alonso|MySQL Community Edition CERNThe Worldwide LHC Computing Grid(WLCG)Tier0:Data reconstruction+Tape archival+dat

5、a distribution to other tiers 200 PB of data per year1st May 202451 PB of data per second Only 1%is kept(events with specific characteristics)WLCG:-170 collaborating centers -36 countries-Data analysis Abel Cabezas Alonso|MySQL Community Edition CERNOracle databases since 1982 105 Oracle databases M

6、ore than 11.800 Oracle accounts RAC,Active DataGuard,OEM,RMAN Complex environment Used by Administrative Information Services Engineering teams Accelerator and experiments etc.Full DBA support 5PB of data Databases at CERN:Oracle1st May 20246Abel Cabezas Alonso|MySQL Community Edition CERNDatabase O

7、n Demand(DBOD)DBaaS conceived in 2011 A number of key database applications were running on user-managed MySQL database instances MySQL was the chosen/only supported technology for some applications Empowers users to be their own DBA Flexible architecture allowing to easily integrate other DBMS More

8、 than 1200 database server instances 600 MySQL,400 PostgreSQL,200 InfluxDB Used by CERN Authorization and authentication service Experiments (ATLAS,LHCb,etc.)WLCG file transfer service 150 TB of dataDatabases at CERN:DBOD1st May 2024717%34%49%MySQLPostgreSQLInfluxDBAbel Cabezas Alonso|MySQL Communit

9、y Edition CERNMySQL deployment Several MySQL binaries per host Several database instances per host Two different NetApp NFS volumes per DB instance:data directory+binary log directory Types of deployment Single instance Replication for disaster recovery Replication to scale out reads(app has to tole

10、rate eventual consistency)ProxySQL+primary-replica MySQL InnoDB cluster1st May 20248Abel Cabezas Alonso|MySQL Community Edition CERNObjective Automate failover About ProxySQL Built-in monitoring module Not designed for reconfiguring the topology No built-in failover/failback Scheduler module to exte

11、nd logic:Failover logic Resolution of conflicts(split brain)Monitoring of replication channel Limitations Not a pure HA solution SPOF Not possible to deploy several proxies for our use case Big maintenance effortTowards high availability:ProxySQL+async replication 1st May 20249Abel Cabezas Alonso|My

12、SQL Community Edition CERNHigh Availability with MySQL InnoDB cluster No SPOF by deploying several MySQL Routers Fully fledged HA solution Easy to integrate*Storage watchdog Easy to deploy with MySQL Shell Easy to maintain Simplified management with MySQL Shell Very good documentation Quorum loss Cl

13、uster reboot Seamlessly scale out reads through MySQL Router1st May 202410Abel Cabezas Alonso|MySQL Community Edition CERNAutomationWeb automation Automated backup and recovery services MySQL Shell upgrade checker Management of configuration files Cloning Integrated monitoring Integrated upgrades Pr

14、imary-replica upgrade logic Ops automation Continuous validation of backups Instance and storage migration Automated replica provisioning Automated replication switchover Detection of idle instances Integrated password hash cracker1st May 202411Abel Cabezas Alonso|MySQL Community Edition CERNAutomat

15、ing instance upgrades with MySQL Shell Upgrade checker utility MySQL Shell integration with extended logic Can be run on demand Upgrades disabled by default Only enabled once the upgrade checker report is“clear”Report shared via CERNBox(cloud storage)Users can correct errors and warnings before upgr

16、ading autonomously Extended logic for replication setups1st May 202412Abel Cabezas Alonso|MySQL Community Edition CERN MySQL is a key element of many services at CERN Ensure that MySQL evolution fits in the service Integration of MySQL InnoDB cluster to discontinue ProxySQL Promote the usage of utf8

17、mb4 Integrate InnoDB replica set to ease the management of existing replication setups Evaluation of MySQL clone plugin for clone provisioning Fully automate MySQL InnoDB cluster lifecycle Explore possibilities to further automate MySQL deployments at CERN Finalise the integration of our Business Co

18、ntinuity/Disaster Recovery plan Service evolution1st May 202413home.cernAbel Cabezas Alonso|MySQL Community Edition CERNAutomating utf8mb3 character set conversionutf8mb3 is deprecated Instances coming from 5.6.x/5.7.x Run once a day an automated check looking for utf8mb3 usage Enable the automated

19、charset conversion on the web interface for the affected instances Allow dry-run:Generates only DDL to be applied Run conversion Generate DDL dump from before and after+conversion log Recommended to first test in a cloned instance to avoid surprises like:A VARCHAR column can only accommodate up to 16383 characters for the utf8mb4 character set1st May 202415ERROR 1074 Column length too big for column foo(max=16383);use BLOB or TEXT instead