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

I. basic knowledge

The importance of data needless to say, need to emphasize that backup is not the purpose, can restore is the purpose, the following are some of the concepts of Mariadb.

1. Classification of storage engines

In future versions of Mariadb5.1, the InnoDB storage engine that supports transactions is used by default. The previous MyISAM storage engine does not support transactions. The so-called transactional storage engine must meet the ACID principle:

A:atom (atomicity), that is, all operations in a transaction must be completed or not executed.

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

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

D:durability (persistence), which will be saved permanently once modified

two。 Transaction isolation level

The InnoDB storage engine is divided into four isolation levels:

Read-uncommitted: read uncommitted transactions, which can be read from other sessions, which is called dirty read

Read-committed: read commit, read transactions that have been committed by other sessions, solve dirty reads, and cause unreadable readings

Repeatable-read: rereadable (default). Through the MVCC multi-version mechanism, the data read by the initiated transaction does not change before committing, even if other sessions have committed the transaction, which solves the problem of non-rereading and causes phantom reading.

Serializable: serial commit, which imposes a shared lock on each read line and an exclusive lock on the written line, which solves phantom reading but reduces concurrency

3. Locking mechanism

Row lock and table lock: divided according to granularity, row lock has stronger concurrency ability.

Read lock and write lock: read lock is also called shared lock. After applying read lock, all transactions can only read and no longer write. Write lock is also called exclusive lock. After applying write lock, transactions other than the current transaction cannot perform read and write operations.

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

4. Backup mode

According to the backup method, the database is divided into physical backup and logical backup, and according to the running status of the service, it can be divided into cold backup and hot backup. For MyISAM storage engines that do not support things, warm backup can be used (provided that lvm2 or Btree file system must be used).

Physical backup: backup based on the file system layer, which has the advantage of high speed and the disadvantage of receiving file system constraints.

Logical backup: when backing up, the database is extracted into sql statements. The advantage is that it is not constrained by the operating system and has strong portability. The disadvantage is that the speed is slow when the database is large.

2. Use tools

1.mysqldump

-- single-transaction is only valid for the InnoDB engine and is used to protect data consistency. Create a data snapshot before the backup is initiated, and will not read any transactions committed after the backup starts until the backup ends.

-- quick cooperation-- single-transaction accelerates large space table backup

-- all-databases (- A) backs up all databases

-- where (- w) filters the data to be backed up by criteria

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

-- master-date=VALUE VALUE has two values: 1 | 2. If 1 adds a CHANGE MASTER TO statement to the backed-up file, and 2 adds a CHANGE MASTER TO statement and comments to the backed-up file, it is recommended to use this option and set VALUE to 2 when backing up, because this parameter will be backed up with the name and location of the binlog log at the current time, which is convenient for later recovery using binlog.

It should be noted that if mysqldump does not have the-A |-B option to back up the data structure by default, you need to create the database and table structure first when restoring. When you restore a single table, you only need to specify the library name, not the table name.

2.mysqlbinlog

Mysqlbinglog is a client tool that can access the mysql server as a client on other terminals

-- database=DATABASE is only applicable to local logs. Check the information of the specified database in the binary log (default is all libraries). It is recommended to use this option when exporting the binary log, otherwise an error will be reported when restoring a database that is not available on the server and cannot be recovered normally.

-- start-position=N views transactions after the specified location

-- to-last-log (- t) does not stop at the end of the requested binary log in the MySQL server, but continues to print until the end of the last binary log, which will loop indefinitely if sent to the same server. Need to use-- read-from-remote-server option at the same time

-- the result-file=FILENAME output is the specified file, with the same redirection ">"

III. Operation steps

The operating system version of this experiment is CentOS7.4, and the database version is Mariadb5.5.6. In order to avoid interference, selinux and iptables,172.16.10.30/24 are turned off as database servers, and 172.16.10.40 Universe 24 is used as backup server.

1. Cold standby

(1) stop the MariaDB service of 172.16.10.30tic24

Systemctl stop mariadb.service

(2) use the scp command to copy the hellodb database on the 172.16.10.30 server to the 172.16.10.40 server datadir directory

Scp-r hellodb root@172.16.10.40:/var/lib/mysql/

(3) the owner and subordinate group of the hellodb database of server 24 after modifying the copy.

Chown-R mysql.mysql / var/lib/mysql/hellodb

(4) start the MariaDB service of 172.16.10.40Universe 24

Systemctl start mariadb.service

two。 Hot standby

(1) back up the database on 172.16.10.30DB 24

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

(2) back up the subsequent sql statements to the specified file according to the binlog used to record the backup in / tmp/hellodb.sql and its location, if more than one binary log is subsequently appended.

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

(3) transfer hellodb.sql and / tmp/binlog.sql to 172.16.10.40 lap24 server

Scp-r {binlog.sql,hellodb.sql} root@172.16.10.40:/tmp/

(4) the process of importing the database does not need to record the binary log, so close it temporarily.

MariaDB [hellodb] > set sql_log_bin=0

(5) execute the sql statement to restore the database at 172.16.10.40 server

Mysql-uroot-p

Set sql_log_bin=1

Because of the importance of binary logs, it is recommended to put data files and binary files on different disks in the production environment. If there is a special log server, you can also synchronize sql statements in binary logs remotely.

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. Warm preparation

Warm standby is a compromise backup method for MyISAM storage engine that does not support transactions. It achieves almost hot backup effect through lvm2 snapshot function combined with binary log.

(1) synchronize the table on the 172.16.10.30 server to disk and lock the table

MariaDB [hellodb] > flush tables with read lock

(2) refresh the server log of 172.16.10.30lap24

MariaDB [hellodb] > flush logs

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

[root@host3 ~] # mysql-uroot-ppassword1!-e'show master status;' > / tmp/position.txt

(4) create a snapshot of the device where the datadir directory is located

[root@host3] # lvcreate-L 1G-s-n mylv_snap / dev/myvg/mylv

(5) unlock the read lock

MariaDB [hellodb] > unlock tables

(6) Mount the snapshot

Mount / dev/myvg/mylv_snap / mnt/

(7) copy the backup to 172.16.10.40 Universe 24 server, and modify the owner and group

[root@host3 mysql] # scp-r / mnt/hellodb root@172.16.10.40:/data/mysql

[root@host3 mysql] # chown-R mysq.mysq / data/mysql/

(8) the process of importing the database does not need to record the binary log, so close it temporarily.

MariaDB [hellodb] > set sql_log_bin=0

(9) synchronize the binaries on 172.16.10.30lap24 to 172.16.10.40lap24

[root@host4 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) execute the sql statement after the snapshot

[root@host4 mysql] # mysql-uroot-paired password1words'

Set sql_log_bin=1

(12) Delete the snapshot on 172.16.10.30lap24, and the operation is complete

[root@host3 mysql] # umount / mnt/

[root@host3 ~] # lvremove mylv_snap

Supplementary note:

In later versions of mysql5.6, the server on the remote server can be copied locally. In the mysqldump command,-raw (storing the logs of the remote server locally in the form of binlog) and-- stop-never (connecting to the remote mysql server to read the logs until the server is turned off will exit or be kill off) options are not found in mariadb10.1, and are not sure whether this feature is available.

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.