[Linux] PHP programmers play with Linux series-backup and restoration of MySQL and linuxmysql

[Linux] PHP programmers play with Linux series-backup and restoration of MySQL and linuxmysql

1. PHP programmers turn to the Linux series-how to install and use CentOS

2. PHP programmers play with Linux series-lnmp Environment Construction

3. PHP programmers play with the Linux series-build an FTP code Development Environment

There was a piece of news a few days ago, saying that gitlab engineers had mistakenly deleted the data files. This was a big event and many people went around. backup should be done at the beginning, otherwise it will lose the best time. To ensure the security of my data, I have to back up the data. backup can be divided into logical backup and physical backup. Logical backup is SQL export and physical backup is based on files.

 

Enable binlog

The first thing is to enable binlog and edit the mysql configuration file to enable it. It is disabled by default. edit/etc/my. cnf, log-bin = mysqlbinlog = is followed by the binlog name, which is generated in the data directory by default. restart mysql service and service mysqld restart.

View the mysql data directory. files such as/var/lib/mysql and mysqlbinlog.000001 are binlog log files.

 

Add some test data

Create a database, create a table, and insert some records for testing. The storage engine of the table is innodb, which is easy to test.

# Create database entmail charset = utf8; # create a table. The storage engine selects innodbcreate table user (id int auto_increment primary key, name varchar (100) not null default '') engine = innodb; # insert record insert into user values (null, 'shihan'); insert into user values (null, 'shihan1'); insert into user values (null, 'shihan2'); insert into user values (null, 'shihan3 ');

 

Use mysqldump for logical backup of small data volumes

The first backup method is to use mysqldump to export SQL statements, and then import the SQL statements again after restoration.

For my database, the user name root password is blank. The location where I put the SQL file is/home/shihan1/mysql/sqls/. Back up all the databases. My Export command is as follows:

mysqldump -uroot  --all-databases > /home/shihan1/mysql/sqls/all.sql  

At this point, I accidentally deleted the database entmail, drop database entmail

Don't worry, recover immediately. Execute the following import command and the database will return immediately. Note that one is the mysqldump command and the other is the mysql command.

mysql -uroot   < /home/shihan1/mysql/sqls/all.sql 

 

Why can't I back up data by copying Data Directories?

Some students use the direct copy data directory method for backup. I also tested it and proved that this method is incorrect. I used the tar command to package and remove the entire data directory, delete the entire data directory, and then move it back.

Because I have deleted all the user tables in mysql, mysql will Reinitialize them.

I moved the data file back and checked it in mysql. The result is as follows: the user table does not exist. This table is from the innodb engine, so this method is not available, fortunately, I have a logical backup. After a new import, the data is back.

Mysql-uroot

 

 

Use third-party software percona-xtrabackup for physical backup

There are a lot of software that can achieve physical backup, and mysql also has an enterprise-edition backup tool, but it seems to be charged. The open-source software percona-xtrabackup can well implement full backup and Incremental backup.

Go to this address to download the rpm package. You can select the version. The latest version 2.4.6 was selected at the beginning. The Error "Error: Built-in InnoDB in MySQL 5.1 is not supported in this release" is returned. you can either use Percona XtraBackup 2.0, or upgrade to InnoDB plugin. then, you can get a new version 2.0.0.

Https://www.percona.com/downloads/XtraBackup/

# Download rpmwget ready-I percona-xtrabackup-2.0.0-417.rhel6.x86_64.rpm # install the dependency yum-y install perl-devel libaio-devel perl-Time-HiRes perl-DBD-MySQL perl-MD5 rsync libev numactl

 

 

Backup started

Current data:

1. I will first back up all the data once, and the following directory is the directory for backup and storage. After the execution is complete, innobackupex: completed OK will appear! Indicates success. The generated backup file 2017-02-24_17-47-55

innobackupex --user=root /home/shihan1/mysql/backups/

2. Add several new data entries. Current Data Conditions

insert into user values(null,'shihan4');insert into user values(null,'shihan5');

3. When an incremental backup is performed, an additional parameter-incremental is added. The file generated by the incremental backup is 2017-02-24_17-51-32, and the data size is different.

innobackupex --user=root --incremental /home/shihan1/mysql/backups/

4. At this point, the disaster occurred. I deleted the data directory! Rm-rf/var/lib/mysql/*, mysql is no longer connected

5. recover data, prepare full backup files, merge Incremental backup files, and perform recovery.

# Prepare the full backup file innobackupex -- apply-log -- redo-only/home/shihan1/mysql/backups/2017-02-24_17-47-55/# merge Incremental backup files. Check the directory name.
# If multiple incremental backups exist, execute innobackupex -- apply-log -- redo-only incremental directory -- incremental-dir incremental directory innobackupex -- apply-log -- redo-only/home/shihan1/mysql/backups/2017-02-24_17-47-55 /-- incremental-dir/home/shihan1/mysql/backups/2017-02-24_17-51-32/# restore innobackupex -- copy-back/home/shihan1/mysql/backups/2017-02-24_17-47-55

6. The data has returned. Modify the data file group and its owner chown mysql: mysql-R/var/lib/mysql/*, restart mysql, and restore all data.

 

Next, run the backup script periodically.