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

Data is the core asset of Internet companies, so many companies should not only ensure the high availability of business systems, but also consider the high availability and security of data storage. The on-the-job company is an entrepreneurial company, and the previous application system is composed of .net and SQLserver. Due to the growth of business, the technical department uses Java to reconstruct the whole application system. The database chooses the open source database MYSQL, which has stepped on quite a lot of holes from the very beginning. I would like to share with you here.

Environment introduction:

Server: Ali CVM

Disk type: SSD

Operating system: CentOS6.5 64 bit

Software version: 5.5.50-MariaDB-wsrep

1. High availability scheme selection of database

At present, there are still many high-availability solutions for mysql, such as master / slave, MMM or MHA. We initially considered using Keepalived+Mysql (dual master hot backup) solution, but because Aliyun does not support virtual IP very well, we want to use other solutions, preferably with a cluster solution, and finally choose MariaDBGalera Cluster. Three nodes form a cluster. Ali Cloud SLB is used at the front end to achieve load balancing to reduce the pressure on the database.

Main functions of MariaDB Galera Cluster

Synchronous replication

Real multi-master, that is, all nodes can read and write to the database at the same time

Automatic node membership control, failure nodes are automatically cleared

New nodes join automatic data replication

True parallel replication, row level

Users can connect to the cluster directly, which is exactly the same as MySQL.

Advantages:

Because it is multi-master, there is no Slavelag (delay)

There is no lost transaction.

Have the ability to read and write at the same time

Smaller client latency

The data between nodes is synchronous, while the Master/Slave mode is asynchronous, and the binlog on different slave may be different

Online environment database architecture diagram

1. Cluster1 and cluster2 are dispatched by SLB to achieve database load balancing, and applications can connect cluster1 and cluster2 to write and read. Slave3 mainly implements data checksum backup.

Stepped on the pit:

1. Data capacity planning is seriously unreasonable.

Due to the lack of experience of startups, research and development personnel and operation and maintenance personnel, unreasonable disk capacity planning, rapid data growth and insufficient capacity were adopted in the procurement of the entire system design server. Finally, a hard disk was added. Since the server is an Ali CVM used, it is easy to expand disk capacity. After purchasing disk capacity from Ali Cloud console, restart the host (remote connection reboot restart) Check the disk with fdisk and other commands, and find that the expansion part does not take effect and toss about for a long time. Finally, call Aliyun after sales to solve the problem. To change the hardware configuration, you need to restart it in the Aliyun console.

2. Mysql independent and shared tablespaces

This pit is also found in the above capacity use, because some mysql default to use independent tablespaces, while 5.5.50-MariaDB-wsrep is default to use shared tablespaces, due to lack of previous experience, did not change these, the daily business volume is relatively large, so the amount of data growth is relatively fast, one day found under the mysql directory. The Ibdata file is already more than 80 gigabytes. Finding relevant information is a problem of independent and shared tablespaces, which contains redo log as well as the data and indexes of each table. Due to the timeliness of our data, more than a month will be transferred to the history database, and then the tables related to the main database will be deleted, while the shared tablespace does not support this large number of deletions, so we convert the tablespaces of the entire database. Here is a brief introduction to independent and shared tablespaces

Shared tablespaces: all the table data and index files of a database are placed in one file. By default, the file path of this shared tablespace is in the data directory. The default file name is: ibdata1 initializes to 10m.

Independent tablespaces: each table will be generated and stored as a separate file, with each table having a .frm table description file and an .ibd file. This file includes the data contents and index contents of a single table, and its storage location is also in the table location by default.

The advantages and disadvantages between the two

Shared tablespaces:

Advantages:

The tablespace can be divided into multiple files and stored on each disk (the tablespace file size is not limited by the table size, for example, a table can be distributed over non-synchronous files). Data and files are put together for easy management.

Disadvantages:

All the data and indexes are stored in one file, which means that there will be a very large file. although a large file can be divided into multiple small files, multiple tables and indexes are mixed and stored in the table space. in this way, there will be a lot of gaps in the table space after a large number of deletions have been done on a table, especially for statistical analysis, applications such as daily value systems are the least suitable to use shared table spaces.

Independent tablespaces: set in configuration file (my.cnf): innodb_file_per_table

Innodb_file_per_table=1 to use exclusive tablespaces

Innodb_file_per_table=0 for using shared tablespaces

Advantages of independent tablespaces:

1. Each table has its own independent tablespace.

2. The data and indexes of each table will be stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be recycled (table emptiness cannot be recycled by itself except for drop table operation)

A) the Drop table operation automatically reclaims tablespaces. For statistical analysis or daily value tables, you can delete a large amount of data through: alter table TableNameengine=innodb; to retract unused space.

B) using turncate table for Innodb of innodb-plugin also shrinks the space.

C) for tables that use independent tablespaces, no matter how much they are deleted, the fragmentation of the tablespaces will not seriously affect performance, and there is still a chance to deal with them.

Disadvantages:

Single table increases too much, such as more than 100 gigabytes.

3. Conversion from shared tablespace to independent tablespace

Due to the timeliness of our data, we need to transfer the data and delete the tables of the original library.

The default shared tablespace is converted to a separate tablespace.

Conversion scheme:

1. Mysqldump the data logically, change the configuration file, restart the database, drop the previous database, and import the new data.

2. Change the configuration file directly and restart the database.

The difference between the two

Scenario 1 is a more thorough approach, but a large amount of data means that the whole process will be very slow, because the logical backup of mysqldump to SQL is time-consuming. Scenario 2 is a more eclectic solution, which has no effect on the data table structure that has been created, and the table structure created later will use independent tablespaces.

For us, option 1 is more thorough, with a data volume of more than 200 gigabytes. Since most of our records are broken down on a monthly basis, some of the data can be cold data (which will not be changed in general). So we back up the cold data first, import it to other libraries to check the integrity, and then stop part of the business to deal with the business logic and other data.

4. Backup of mysqldump data sub-database

Experienced operators or DBA will certainly not use mysqldump to back up a large amount of data because it is very slow, but we have stepped on another hole here because of our inexperience. Using scripts and scheduled tasks to achieve data backup, backup at 2 p.m. Every Saturday, the amount of data in the early stage is relatively small, the whole business system is normal, and then when the data breaks through more than 100 gigabytes, a strange thing happens. every Saturday morning, the application system is always abnormal, the developers are very depressed, feel like hell, after many times to consider the data backup, study the implementation of the task It is found that it is indeed a data backup problem, and then take a xtrabackup backup.

Script:

# / bin/bashMYUSER=mysqlbackMYPASS=databack***#SOCKET=/data/3306/mysql.sockMYLOGIN= "mysql-u$MYUSER-p$MYPASS" MYDUMP= "mysqldump-u$MYUSER-p$MYPASS-B" DATABASE= "$($MYLOGIN-e" show databases; "| egrep-vi" Data | _ schema | mysql ")" for dbname in $DATABASE do MYDIR=/data/backup/$dbname [!-d $MYDIR] & & mkdir-p $MYDIR $MYDUMP $dbname | gzip > $MYDIR/$ {dbname} _ (date +% F) .sql.gzdone

5. Shared tablespace conversion independent tablespace change database configuration error

Configuration file: [server] # this is only for the mysqld standalone daemon [mysqld] skip-name-resolvecharacter-set-server=utf8datadir=/data/mysqlwait_timeout=1800interactive_timeout = 288000max_allowed_packet = 1000M#max_connections=3000max_connections=3000character-set-server=utf8#innodb_buffer_pool_size = 1000Minnodb_additional_mem_pool_size = 200Minnodb_flush_log_at_trx_commit=2innodb_autoextend_increment=800M#innodb_log_buffer_size = 200Minnodb_log_file_size = 100Mkeybufferbuffer size800Mreadbuffersize600Mthreadreadcachesizesize64innodb_ File_per_table=1 # independent tablespace # innodb_flush_log_at_trx_commit=2#innodb_log_file_size=1G # (log file) innodb_buffer_pool_size=6G

In order to optimize the performance of the database properly, the parameters are adjusted appropriately, and then the problem of comparison pit arises. The database cluster can only start one of them, and the other two are both reporting errors. At this time, it must be to check the log to solve the problem. The following log is caused by the problem of setting the configuration file parameters. Check the changed configuration files one by one. Finally, it is found that three innodb_buffer_pool_size parameters are inconsistent (the basic configuration of the three server clusters is similar, but the difference is that there are other applications running on one, so set it a little smaller, resulting in abnormal startup of the whole system)

Partial logs: InnoDB: Error: logfile. / ib_logfile0 is of different size 0 104857600 bytesInnoDB: than specified in the .cnf file0 1073741824 byteswords InnoDB: Possible causes for this error: (a) Incorrect logfile is used or logfile size is changed (b) In case default size is used this logfile is from 1073741824 (c) Log file is corrupted or there was not enough disk space In case (b) you need to set innodb_log_file_size = 48M170412 23:53:26 [ERROR] Plugin 'InnoDB' init Function returned error.170412 23:53:26 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.170412 23:53:26 [Note] Plugin' FEEDBACK' is disabled.170412 23:53:26 [ERROR] Unknown/unsupported storage engine: innodb170412 23:53:26 [ERROR] Aborting170412 23:53:28 [Note] WSREP: Closing send monitor...170412 23:53:28 [Note] WSREP: Closed send monitor.170412 23:53:28 [Note] WSREP: gcomm: terminating thread170412 23 virtual 53 Aborting170412 28 [Note] WSREP Gcomm: joining thread170412 23:53:28 [Note] WSREP: gcomm: closing backend170412 23:53:29 [Note] WSREP: view (view_id (NON_PRIM) 1d5436dcMagol 2) memb {1d5436dcMagol 0} joined {} left {} partitioned {effca7a8,0

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.