MARIADB Simple Introduction

Database for Internet companies is a company's heart, without it this company is absolutely just a bunch of ppt. Therefore, for an operation, the database is absolutely the most important maintenance, every day to make incremental backups of the database, a weekly full backup. Commonly used backup tools mysqldump This is a logical being divided tool that means that performance will be consumed by the computation; Extrabackup This is a physical backup tool with good performance, and a method of backing up with LVM, which is obviously not reliable, Since the data stored by LVM does not have the characteristics of hardware-level recovery data, we can only accept data loss in the event of extreme circumstances.

What does the database need to back up? 1 data, logs, 2 programs, storage routines, 3 configuration files. Since we want to back up this data, we need to understand their structure, the configuration file good understanding of backup, program and storage routines backup is also better backup as long as the provision of the operating environment of the unified sample.

The data backup is very troublesome, we need to fully understand the operation principle of mariadb. Where the MARIADB storage is using black box storage, that creates a problem, we want to view the data in the database only using the MySQL client. Then there are two ways we can back up this data. One is a logical backup, we reverse the data generated a MySQL client data generation method, this backup will have a great problem, the reverse generation will be very slow, the recovery of data is very slow, the other is a physical backup, we directly copy the black box data, but this kind of backup is very troublesome, Backing up the database we can't just stop the database. A large single database outage can have a big impact. Daily routine maintenance of the database it seems that only a certain transport company is doing this, as a user-centric company is likely to churn out customers, and the backup features depend on the storage engine.

Storage Engine

Table type: Table-level concept, it is not recommended to use a different engine on a table in the same library;

CREATE TABLE ... Engine[=]storage_engine_name ... #定义数据库表使用的存储引擎

SHOW TABLE STATUS #查看存储引擎

Common Storage engines:

MyISAM, Aria, InnoDB, Mrg_myisam, CSV, Blackhole, MEMORY, Performance_schema, ARCHIVE, Federated

Storage engines supported by the current database

Mysql> SHOW ENGINES;

Innodb:innobase

PERCONA-XTRADB, Supports transactions, Row-level locking, and foreign keys

The data is stored in the tablespace (table space):

(1) The data and indexes of all InnoDB tables are stored in the same table space;

Tablespace file: DataDir defined in the directory

Files: ibdata1, Ibdata2, ...

(2) Innodb_file_per_table=on, which means that each table uses a separate tablespace file;

Data file (data and index, stored in database directory): TBL_NAME.IBD

Table structure definition: In the database directory, tbl_name.frm

Transactional storage engine, suitable for scenarios where transaction requirements are high, but more suitable for dealing with a large number of short-term transactions;

Support high concurrency based on MVCC (Mutli Version Concurrency Control), support four isolation levels, default level is Repeatable-read, Gap lock to prevent phantom reading;

Use clustered index (primary key index);

Support "adaptive hash Index";

Lock granularity: Row-level lock, Gap lock;

Summarize:

Data storage: Table space;

Concurrency: MVCC, Gap Lock, row-level lock;

Business: Repeatable-read; applicable to a large number of short-term transactions;

Index: Clustered index, secondary index;

Performance: Pre-read operation, memory data buffer, memory index cache, Adaptive hash index, insert operation buffer;

Backup: Support hot standby;

MyISAM:

Support for full-text indexing (fulltext index), compression, spatial functions (GIS);

Transaction not supported

Lock granularity: Table-level lock

Crash does not guarantee table security recovery

Scenario: read-only or read-less scenarios, smaller tables (to ensure a shorter recovery time after a crash);

Files: Three files per table, stored in a database directory

TBL_NAME.FRM: Table-style definition;

Tbl_name. MYD: Data files;

Tbl_name. MYI: Index file;

Characteristics:

Locking and Concurrency: table-level locks;

FIX: Manual or automatic repair, but may lose data;

Index: Nonclustered index;

Delayed index updates;

Table compression;

Log backup

Log backup is difficult to understand, mariadb design time to consider the complete data, must ensure the success of data writing, then design a set of logging:

Access log

Slow access log, long-time access to commands

A binary log that records commands that cause changes to the database.

Relay log, database master-Slave

So why back up the log, because after we back up the data, the database changes so how to guarantee the complete data, here we can use the binary log, the backup database after the change operation again, we can recover the database data.

Logical Backup Recovery Database

#首先准备好备份mysqldump--databases DATABASENAME--single-transaction-r--triggers-e--flush-logs--master-data=2 >/tmp/ Backup.sql# then back up the binary log, removing the statements you don't want to execute Mysqlbinlog--stop-position=#/var/lib/mysql/master-log.00000# >/tmp/ Bin.logmysql <<eof# stop Recording binary log set @ @session. sql_log_bin=off; #恢复备份的数据source/tmp/backup.sql;# Restores the data generated in the binary log source/tmp/bin.log; #开启记录二进制日志set @ @session. sql_log_bin=on;eof

Physical backup

Physical backup We can perform full-scale backups, incremental backups, and variable backups. Download xtrabackup:https://www.percona.com/downloads/xtrabackup/percona-xtrabackup-2.4.5/binary/redhat/7/x86_64 at this address /percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

Full-scale backup

Innobackupex--user=root--host=localhost--password=aaa/backup/dir backing up the database Innobackupex--apply-log/backup/dir # Backup LOG mysqlbinlog--start-position=#--stop-position=#/var/lib/mysql/master-log.00000# >/tmp/bin.log #然后备份二进制日志, Remove statements that you do not want to execute

Innobackupex--copy-back/backup/dir #恢复备份chown-R mysql.mysql/var/lib/mysqlsystemctl start mariadb.service# Recover data generated in binary log mysql <<eofset @ @session. sql_log_bin=off;source/tmp/bin.log;set @ @session. sql_log_bin=on;eof

Incremental backup

Innodbbackupex--incremental--user=root--host=localhost--password=aaa incremental-basedir=/backup/dir backup Merge, Consolidate data from incremental backups into full-scale backups Innodbbackupex--apply-log--redo-only base-dir incremental-basedir=/backup/dir

Master-slave replication

Only the backup is not enough, when the MARIADB fault this requires us to provide a high-availability cluster; When the database read request is particularly large, we need a server to share the read request, and write the request is also larger, not discussed here. First of all, read requests get bigger. We can increase the server, share the pressure, generally use master-slave, or use dual-master.

Subordinate

Using the master-slave, when the write node fault requires us to provide the migration tool commonly used MHA

Primary server

Vim/etc/my.cnfinnodb_file_per_table=1skip_name_resolve=1server_id= #log_bin =log-bin

Start the service and authorize mysql> Grant REPLICATION slave,replication CLIENT on *. * to ' USERNAME ' @ ' HOST ' identified by ' your_p            Assword '; mysql> FLUSH privileges;

From the server

Vim/etc/my.cnfinnodb_file_per_table=1skip_name_resolve=1server_id= #relay_log =relay-log Start-up service: mysql> change M ASTER to master_host= ' HOST ', master_user= ' USERNAME ', master_password= ' Your_password ', master_log_file= ' BINLOG ',            master_log_pos=#; mysql> START SLAVE [io_thread|            Sql_thread]; mysql> SHOW SLAVE STATUS;

Qs.

Configuring the primary server

Create a certificate file cd /etc/pki/ca/touch index.txtecho 01 > serial (umask 066;openssl  genrsa -out private/cakey.pem 2048) openssl req -new -x509 -key private /cakey.pem -out cacert.pem (umask 066;openssl genrsa -out mysql.key 2048;  ) Openssl req -new -key mysql.key -out mysql.csr -days 365openssl  ca -in mysql.csr -out mysql.crt -days 700 (umask 066;openssl  genrsa -out client.key 2048; ) openssl req -new -key client.key - Out client.csr -days 365openssl ca -in client.csr -out client.crt  -days 700# Copy the certificate file and change the genus owner cp cacert.pem mysql.crt mysql.key client.key  client.crt /etc/mysql/chown -r mysql.mysql /etc/mysql# Copy Key certificate, if you need to create/etc/mysql directory SCP on Slave  -p client.key Client.crt cacert.pem 172.16.29.10:/etc/mysql 

vim/etc/my.cnf# in the [Mysqld] segment, add the following configuration SSL #开启SSL功能ssl-ca =/etc/mysql/cacert.pem #指定CA文件位置ssl-cert =/etc/mysql/mysql.crt # Specify certificate file Location Ssl-key =/etc/mysql/mysql.key #指定密钥所在位置 # turn on server systemctl Restart mariadb.service# authorization mysqlgrant replication Slave,replication Client On * * to ' tom ' @ ' 172.16.%.% ' identified by ' Tom ' Require SSL;

Slave node

#在slave上连接cd/etc/mysql/mysql--ssl-ca=cacert.pem--ssl-cert=client.crt--ssl-key=client.key-h172.16.29.2-utom- ptom# View Connection Status \squit

The following line is displayed using SSL to represent success

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= " Https://s3.51cto.com/wyfs02/M00/8E/00/wKioL1iy53TAtcisAAAVhobocTc960.png "title=" Image.png "alt=" Wkiol1iy53tatcisaaavhoboctc960.png "/>

Configure the slave server

Systemctl Restart Mariadb.servicemysqlchange master to master_host= ' 172.16.29.2 ', master_user= ' Tom ', master_password= ' Tom ', master_log_file= ' master-log.000008 ', master_log_pos=245,master_ssl=1,master_ssl_ca= '/etc/mysql/cacert.pem ', master_ssl_cert= '/etc/mysql/client.crt ', master_ssl_key= '/etc/mysql/client.key '; start slave;show slave Status\G

Represents a successful configuration

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= " Https://s2.51cto.com/wyfs02/M01/8E/02/wKiom1iy54jhnkXXAABlk3SV_4Y191.png "title=" Image [1].png "alt=" Wkiom1iy54jhnkxxaablk3sv_4y191.png "/>

Mha

installation package Https://code.google.com/p/mysql-master-ha/downloads/list

Installing from a server

Yum Install Mha4mysql-node-0.54-0.el6.noarch.rpm-y

Primary server

yum install mha4mysql-node-0.54-0.el6.noarch.rpm mha4mysql-manager-0.55-0.el6.noarch.rpm - y[[email protected] ~]# cat /usr/local/mha/mha.cnf[server default]user=mha_rep # MHA Manage MySQL user name password=123456  #MHA管理mysql的密码manager_workdir =/usr/local/mha  #MHA的工作目录manager_log =/usr /local/mha/manager.log  #MHA的日志路径ssh_user =root  #免秘钥登陆的用户名repl_user =backup  #主从复制账号, Used to synchronize data between master and slave repl_password=backupping_interval=1  #ping间隔时间 to check if Master is normal [server1]hostname= 192.168.253.241master_binlog_dir=/data/mysql/candidate_master=1  #master宕机后, priority to enable this as Master[server2] Hostname=192.168.253.242master_binlog_dir=/data/mysql/candidate_master=1[server3]hostname= 192.168.253.243master_binlog_dir=/data/mysql/no_master=1  #设置na_master = 1, so that the server cannot be master# check whether SSH is unblocked Masterha_ check_ssh --conf=/usr/local/mha/mha.cnf  #运行, the script automatically exits after it takes effect nohup masterha_manager --conf=/ usr/local/mha/mha.cnf > /tmp/mha_manager.log 2>&1 & 

Dual Master

Mutual main from: two nodes each to open binlog and relay log;

1, inconsistent data;

2, automatic growth ID;

Define a node with an odd ID

Auto_increment_offset=1

auto_increment_increment=2

The other node uses an even ID

auto_increment_offset=2

auto_increment_increment=2

Configuration:

1, server_id must use different values;

2. Binlog and relay log are enabled;

3, there is an automatic Growth ID table, in order to make the ID does not conflict, need to define its automatic growth mode;

After the service starts, perform the following two steps:

4, all authorized to have the Copy permission user account;

5, each other designated as the main node;

Copy Filter

Copy only limited one or several database-related data, not all; by the replication filter;

There are two ways to achieve this:

(1) Primary server

The primary server records only the write operations related to a particular database in the binary log;

Problem: The point-recovery of other libraries will not be realized;

binlog_do_db=

binlog_ignore_db=

(2) from the server

The SQL thread from the server only replays events related to the database or table concerned and applies them locally;

Problem: Network IO and disk IO;

replicate_do_db=

replicate_ignore_db=

replicate_do_table=

replicate_ignore_table=

replicate_wild_do_table=

replicate_wild_ignore_table=

Summarize

The data backed up are: data, logs, configuration files, programs,

How to back up: logical backup with LVM Backup, physical backup (full backup, incremental backup, variable backup)

High availability: Master-slave, dual-master, encrypted communication, replication filtering

This article is from "Lao Wang Linux Journey" blog, please be sure to keep this source http://oldking.blog.51cto.com/10402759/1901490

MARIADB Simple Introduction