Preliminary knowledge of mariadb 3--mariadb backup and restore

first, the basic knowledge

The importance of data is needless to say, it should be emphasized that the backup is not the purpose, can recover is the purpose, the following mariadb some concepts to explain.

1. Classification of storage engines

Mariadb5.1 later versions, by default using the InnoDB storage engine that supports transactions, the previous MyISAM storage engine does not support transactions, and the so-called transactional storage engine must meet the acid principle:

A:atom (atomicity), that is, all operations in a transaction must complete or not execute

C:consistent (consistency), a transaction must remain consistent from start to end

I:isolation (isolation), the same data does not allow simultaneous access to multiple transactions

D:durability (persistent), permanently saved once modified

2. Transaction ISOLATION LEVEL

The InnoDB storage Engine is divided into 4 isolation levels:

Read-uncommitted: READ UNCOMMITTED, can read transactions not committed by other sessions, called Dirty reads

Read-committed: Read commit, can read transactions committed by other sessions, resolves dirty reads, throws non-stressed

Repeatable-read: Can be reread (default), the data read by the initiated transaction is not changed by the MVCC Multi-versioning mechanism, even if other sessions have committed the transaction, it resolves the non-stress, and causes the Phantom read

Serializable: Serial commit, applying a shared lock on each read line, applying an exclusive lock on the line being written, resolving the Phantom read, but reducing concurrency

3. Locking mechanism

Row and Table locks: Row locks are more capable of concurrency based on different granularity

Read lock and Write lock: Read lock, also known as shared lock, after the read lock is applied, all transactions can only read operation, no longer write operation, write lock is also called an exclusive lock, after the write lock, other than the current transaction can not read and write operations

The row lock of the InnoDB storage engine is actually added to the index.

4. How to Back up

Database according to the backup method is divided into physical and logical backup, according to the state of service operation can be divided into cold and hot spare, for the MyISAM storage engine that does not support things, it can be done in Win Bei way (if you must use LVM2 or Btree file system).

Physical backup: Based on the file system layer of backup, the advantage is fast, the disadvantage is to receive file system constraints

Logical Backup: Extract the database as SQL statement when backing up, the advantage is not affected by the operating system constraints, portability is strong, the disadvantage is that when the database is relatively slow

Second, the use of tools

1.mysqldump

--single-transaction is only valid for the InnoDB engine, to protect data consistency, to create a snapshot of the data before the backup is initiated, until the end of the backup does not read any transactions committed after the start of the backup

--quick with--single-transaction accelerating large space table backup

--all-databases (-a) backing up all databases

--where (-W) filter the data to be backed up by criteria

--databases (-B) backs up the specified database, separated by "," between multiple databases

The--master-date=valuevalue value has 1|2 two value, if 1 will add a change master to statement in the backed up file, if 2 will add a change master to statement in the backed up file and comment, This option is recommended for backup and the value is set to 2 because it carries the name and location of the Binlog log at the current moment when the parameter is backed up, facilitating later use Binlog recovery

Note that: mysqldump if you do not back up the data structure by default without the-a|-b option, you need to create the database and table structure before restoring. You only need to specify the library name when you restore a single table, without specifying a table name.

2.mysqlbinlog

Mysqlbinglog is a client-side tool that can access the MySQL server as a client at other terminals

--database=database applies only to local logs, to view information for the specified database in the binary log (by default, all libraries), which is recommended when exporting binary logs, or when the restore encounters a database that is not on the server and fails to recover correctly

--start-position=n viewing a transaction after a specified location

--to-last-log (-T) does not stop at the end of the binary log requested in the MySQL server, but continues printing until the end of the last binary log, which is infinite loop if sent to the same server. need to use the--read-from-remote-server option at the same time

--result-file=filename output for specified file, same redirect ">"

Third, the Operation procedure

The experimental operating system version is CentOS7.4, the database version is Mariadb5.5.6, to avoid interference, turn off SELinux and iptables,172.16.10.30/24 for the database server, 172.16.10.40/24 for the standby server

1. Cold Standby

(1) Stop 172.16.10.30/24 's mariadb service

Systemctl Stop Mariadb.service

(2) Use the SCP command to copy the HELLODB database on the 172.16.10.30/24 server to the 172.16.10.40/24 server DataDir directory

Scp-r Hellodb [Email protected]:/var/lib/mysql/

(3) The owner and group of the 172.16.10.40/24 server HELLODB database after modifying the copy

Chown-r Mysql.mysql/var/lib/mysql/hellodb

(4) Start the 172.16.10.40/24 mariadb service

Systemctl Start Mariadb.service

2. Hot standby

(1) Backing up the database on the 172.16.10.30/24

mysqldump-uroot-ppassword1! --database hellodb--master-data=2--single-transaction--quick >/tmp/hellodb.sql

(2) According to the Binlog and its location used in recording the backup in/tmp/hellodb.sql, the subsequent SQL statements are backed up to the specified file if more than one binary log is subsequently appended

Mysqlbinlog--database hellodb--start-position=245 mysql-binlog.000005 >/tmp/binlog.sql

(3) Transferring Hellodb.sql and/tmp/binlog.sql to the 172.16.10.40/24 server

scp-r {binlog.sql,hellodb.sql} [email protected]:/tmp/

(4) Import database process does not need to log the binary log, the first to temporarily shut down

MariaDB [Hellodb]>set sql_log_bin=0

(5) Execute SQL statement Recovery database on 172.16.10.40/24 server

Mysql-uroot-p < Hellodb.sql

Mysql-uroot-p < Binlog.sql

(6) Open binary log

MariaDB [Hellodb]>set sql_log_bin=1

Because of the importance of binary logs, it is recommended that the data files and binaries be placed on separate disks in the production environment, and that SQL statements in the binary logs can be synchronized remotely if a dedicated log server is available

Mysqlbinlog--read-from-remote-server--host=172.16.10.30--port=3306--user=root--password=password1! --start-position=733--to-last-log mysql-binlog.000005--result-file=/tmp/bin_log.sql

3. Win Bei

Win Bei is a compromise backup method for MyISAM storage engines that do not support transactions, and it achieves almost hot-standby performance by combining binary logs with the LVM2 snapshot feature.

(1) Synchronize tables on the 172.16.10.30/24 server to disk and lock table

MariaDB [hellodb]> flush tables with read lock;

(2) Refresh the 172.16.10.30/24 server log

MariaDB [hellodb]> flush logs;

(3) record the current binary log file and location on the 172.16.10.30/24 server

[Email protected] ~]# mysql-uroot-ppassword1! -E ' Show master status; ' >/tmp/position.txt

(4) Create a snapshot for the device where the DataDir directory resides

[Email protected] ~]# lvcreate-l 1g-s-N mylv_snap/dev/myvg/mylv

(5) Unlock the read lock

MariaDB [hellodb]> unlock tables;

(6) Mount snapshot

mount/dev/myvg/mylv_snap/mnt/

(7) Copy the backup to the 172.16.10.40/24 server and modify the owner and owner group

[[email protected] MySQL] #scp-R/mnt/hellodb [email protected]:/data/mysql

[[email protected] MySQL] #chown-R mysq.mysq/data/mysql/

(8) Import database process does not need to log the binary log, the first to temporarily shut down

MariaDB [Hellodb]>set sql_log_bin=0

(9) Synchronizing binary files on 172.16.10.30/24 to 172.16.10.40/24

[Email protected] mysql]# mysqlbinlog--read-from-remote-server--host=172.16.10.30--port=3306--user=root-- password=password1! --start-position=245--to-last-log mysql-binlog.000006--result-file=/tmp/bin_log.sql

(10) SQL statement after execution of the snapshot

[Email protected] mysql]# mysql-uroot-p ' password1! ' </tmp/bin_log.sql

(11) Open binary Log

MariaDB [Hellodb]>set sql_log_bin=1

(12) Delete the snapshot on 172.16.10.30/24, and the operation is complete.

[[email protected] MySQL] #umount/mnt/

[Email protected] ~]# Lvremove Mylv_snap

Additional notes:

Later versions of mysql5.6 can replicate servers on the remote server locally, where the-raw in the mysqldump command (the log of the remote server is stored in binlog form) and--stop-never (connect to the remote MySQL server to read the logs and know that the server is down before exiting or was killed) option not found in mariadb10.1, not sure if the feature is available

Preliminary knowledge of mariadb 3--mariadb backup and restore