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

First, the self-contained software backup mysqldump

1.mysqldump-uroot-p123456-- single-transaction-- master-data=2-- all-databases > / root/date +% F.sql

-- single-transaction. If mirroring is enabled, the database can be written normally.

-- master-data=2 records the location of the binary log at the time of backup and comments out. 1 is uncommented for binary log recovery.

-- all-databases backs up all databases.-- databases cs1 only backs up cs1 databases. You can back up multiple databases separated by spaces.

If there is master-slave replication and is based on gtid to add-- set-gtid-purged=off

Head-30 2019-09-06.sql | grep MASTER_LOG_FILE # # View the location of the binary file

two。 Use binaries to generate recovery files

Binary open method, add log_bin= to / etc/my.cnf (here is the path name plus name), the network disk is recommended, this file is very important

First write some data in the backed-up data just now.

Here I use mysql-uroot-p123456-e'show master status After seeing that the location of the binary file has changed, it should have added data for me. All the operations we do in the database will be recorded in the binary log file. If the database pops up, we can recover the binary file with the location of the backup record just now, we will be able to get the data at the time when we jumped to the database after the backup, and the complete data can be obtained by adding the previous backup.

Mysqlbinlog-- start-position=245 / var/lib/mysql/log_bin.000008 > logbin.sql

# specify the location, the location of the binary file, and what is the name to restore to that directory

3. Restore the database

2019-09-06.sql is the first full backup, restore this first, and then restore the backup data generated by the later binaries.

This completes the full backup and recovery of the data, which is suitable for small backups, less than 1G

two。 Use xtrabackup for full and incremental backups

1. Install first

Yum install percona-xtrabackup.x86_64-y

# the software is in the epel source and the epel source is deployed

two。 Full backup

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password= "--backup / root

The image above shows the result of the correct execution of the software. After success, there will be a directory with date commands under the root directory, in which there will be a backup.

3. Incremental backup

Innobackupex-- user=root-- password=-- incremental / root/-- incremental-basedir=/root/2019-09-06 11-33-50

#-- incremental / root/ specifies the path of backup-- incremental-basedir=, the directory behind the last backup.

4. Synchronize data and merge incremental backups

Innobackupex-- apply-log-- redo-only / root/2019-09-06 11-33-50 /

# because backup takes time, new things may not be synchronized to the data during the backup process. This command is to synchronize the current new things into the data. If it is the last backup, you can do it or not. There is an incremental backup at the back here, which must be done.

Innobackupex-- incremental--apply-log-- redo-only / root/2019-09-06 11-33-50 /-- incremental-dir=/root/2019-09-06 11-40-39

# append the data of the subsequent incremental backup to the complete data, so that the complete data contains the data of the second backup. When restoring, you can only restore the complete data. Command the first path is the path of the full backup.

5. Restore

Innobackupex-- defaults-file=/etc/my.cnf-- copy-back / root/2019-09-06 11-33-50 /

# just restore the full backup directly

When ps is restored, the root directory of mysql cannot have files and needs to be deleted. After the recovery, you should modify the owner. If you can't start up, see if sulinux is closed.

three。 Semi-synchronous

1. Configure the master node profile first

Vim / etc/my.cnf

[mysqld]

Skip_name_resolve=ON # # enable skipping name resolution

Innodb_file_per_table=ON # # enable independent tablespaces

Server_id=1 # # ID is unique

Log_bin=log-bin # # required to enable binary log

Rpl_semi_sync_master_enabled = ON # boot starts semi-synchronization. If the module has not been installed, comment it out first.

Slave node

[mysqld]

Skip_name_resolve=ON

Innodb_file_per_table=ON

Server_id=2

Relay_log=relay-log # # enable relay log, which must be opened by slave node

Rpl_semi_sync_slave_enabled = ON; # Boot starts semi-synchronization. If the module has not been installed, comment it out first.

two。 Install the semi-synchronous module

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

# installation module

Set global rpl_semi_sync_master_enabled = ON

# set the semi-synchronous status to startup. Note that if you want it to start automatically, write it to the configuration file

SHOW GLOBAL VARIABLES LIKE'% semi%'

# View status

Slave node

Configure the master and slave

CHANGE MASTER TO MASTER_HOST='192.168.0.14', MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='log_bin.000002', MASTER_LOG_POS=245

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

# install slave node module

Set global rpl_semi_sync_slave_enabled = ON

# Startup module

SHOW GLOBAL VARIABLES LIKE'% semi%'

# check the startup status of the startup module

Start slave;## initiates master-slave replication

Show slave status\ G; # View the status of master-slave replication

3. View statu

SHOW GLOBAL STATUS LIKE'% semi%'

The status status is ON, which means the semi-synchronous configuration is successful.

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.