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

Following the author to understand the implementation of mysql log management and mysql backup and restore methods under linux, I believe that we will certainly benefit from reading, the text is not more refined, I hope to achieve mysql log management and mysql backup and restore methods under linux This short content is what you want.

mysql log management and mysql backup and restore under linux

1. SHOW GLOBAL VARIABLES LIKE '%log %'; Show all log-related options

2. Error logs: log_errors and log_warnings

3. General query log: general_log general_log_file log log_output

4. Slow query log: long_query_time log_slow_queries={YES}| NO} slow_query_log low_query_log_file

5. Binary logging: Any operation that causes or may cause changes to the database; replication and instant point recovery;

Binary log format: binlog_format

Based on statement: statement

Based on row: row

Mixing: mixed

Binary log events: time and relative location of occurrence

Binary log files: index files and binary log files

mysql> SHOW MASTER STATUS;#View binary log files currently in use

mysql> SHOW BINARY LOGS; #View all log files:

mysql> SHOW BINLOG EVENTS IN 'binary log file' [FROM location];#View events:

For example:SHOW BINLOG EVENTS IN 'mysql-bin.000004' FROM 107;

Delete Binary Log File: Delete all log files up to the specified binary log file

mysql> PURGE BINARY LOGS TO 'Binary log files'

View log information:mysqlbinlog command options are as follows:

--start-datetime --stop-datetime

--start-position --stop-position

Example:mysqlbinlog --start-position 177 --stop-position 331 mysql-bin.000004;

mysqlbinlog --start-datetime='16-07-07 13:32:07' mysql-bin.000004;

Rolling binary logs:FLUSH LOGS;

6. Relay log: events copied from the binary log file of the primary Cloud Virtual Machine and saved as log file;

Transaction logging: transactional storage engines are used to ensure atomicity, consistency, isolation, and persistence.

innodb_flush_log_at_trx_commit:

0: Sync every second and perform disk flush operations;

1: synchronize per transaction and perform disk flush operations;

2: Synchronization per transaction, but no disk flush operation;

8. mysqldump Backup and Restore Database Tool

8.1. Backup a single database, or specific tables in a library

Format:mysqldump database name [Table 1] [Table 2]

--master-data={0|1|2}

0: Do not record binary log files and road locations;

1: Record the location by CHNAGE MASTER TO, which can be used to directly start the slave Cloud Virtual Machine after recovery;

2: Record the position in the form of CHANGE MASTER TO, but the default is annotated;

--lock-all-tables: lock all tables

--flush-logs: execute log flush;

--single-transaction Start hot standby; if all tables in the specified library are of InnoDB type

8.2. Backup multiple libraries:

--all-databases: backup all databases

--databases DB_NAME,DB_NAME,...: Backup specified library

--events Event Scheduler

--routines stored procedures and stored functions

--triggers

Single database backup method 1:

mysql>FLUSH TABLES WITH READ LOCK;

mysql>FLUSH LOGS;

mysql>SHOW BINARY LOGS;

#mysqldump -u root -p --master-data=2 jiaowu>/root/jiaowu.sql #Backup does not create database statements

mysql>UNLOCK TABLES;

mysql>CREATE DATABASE jiaowu; #Database must be created manually

#mysql jiaowu

< /root/jiaowu.sql #指定还原至指定数据库 单个数据库备份方法二: #mysqldump -u root -p --lock-all-tables --flush-logs --masterdata=2 jiaowu>

/root/jiaowu.sql

Full backup of all databases:

#mysqldump -u root -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/alldatabases.sql

9. Example: use mysqldump command to achieve full weekly backups and incremental daily backups of all databases, and restore

Recommendation:mysql data directory placed in a separate hard disk or LVM, binary log files should also be placed in other directories

The following use of experimental environment simulation to achieve: Note that the experimental binary log file name to their experimental environment prevail

9.1. Full backup of all databases over the weekend

#mysqldump -u root -p --lock-all-tables --flush-logs --all-databases --masterdata=2 > /root/alldatabases.sql

9.2. Clear all binaries before scrolling logs, but it is recommended to save them before clearing

mysql>PURGE BINARY LOGS TO 'mysql-bin.000010';

mysql>SHOW BINARY LOGS;

9.3. After a full backup of the database, after a day, the simulated database has deleted a data

mysql>USE jiaowu;

mysql>DELETE FROM tutors WHERE Age>80;

9.4. Make incremental backups daily

mysql>FLUSH LOGS; #Rolling logs

#cd /mydata/data

#cp mysql-bin.000010 /root #Copy delta binary log files directly

#mysqlbinlog mysql-bin.0000010 > /root/increment_"date +%F-%H-%M-%S".sql #or save to sql statement

9.5. Another day after the simulation, there was an insertion in the database.

mysql>USE jiaowu;

mysql>INSERT INTO tutors (Tname) VALUES ("Tom");

9.6. Simulate database crash on the same day, manually delete all files in the data directory, causing the database to be unusable

#cd /mydata/data

#cp mysql-bin.000011 /root/ #Backup the binary log file currently in use

#rm -rf *

9.7. Using previous backups to simulate a database restore

#killall mysqld

#cd /usr/local/mysql

#scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ #initialize database

#service mysqld start

#cd

#mysql -u root -p

< alldatabases.sql #还原完整备份时的数据库 #mysql -u root -p < increment_2016-07-08-13-30-00.sql #还原增量备份的数据 #mysqlbinlog mysql-bin.000011 | mysql -u root -p #还原当天数据库崩溃时的数据 10.单表备份 备份格式：SELECT * INTO OUTFILE '表的备份路径.txt' FROM 表名 [WHERE 条件]; 还原格式：LOAD DATA INFILE '表的备份路径.txt' INTO TABLE 表名; mysql>

CREATE TABLE students_new LIKE students; #Copy a table with the same structure as the source table

mysql> SELECT * INTO OUTFILE '/tmp/students_new.txt' FROM students;

mysql> DRIO TABLE students;

mysql> LOAD DATA INFILE '/tmp/students_new.txt' INTO TABLE students_new;

11. LVM Snapshot Backup

11.1. Open session, simulate start transaction

mysql>START TRANSACTION;

11.2. Open another session, add read lock, lock all tables;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> FLUSH LOGS;

mysql> SHOW MASTER STATUS;

11.3. storing binary log files and related position information through another terminal;

# mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /backup/master-`data +%F`.info

11.4. create a snapshot volume

# lvcreate -L 100M -s -p r -n mydata-snap /dev/myvg/mydata

11.5. release the lock

mysql> UNLOCK TABLES;

11.6. Mount snapshot volume and backup

mount -o ro /dev/myvg/mydata /mnt

cd /mnt/data

cp -a /mnt/data/* /backup/bak-full-`data +%F`

11.7. Delete snapshot volumes;

cd

umount /mnt

lvremove --froce /dev/myvg/mydata-snap

cp /backup/bak-full-2016-07-11

rm -rf mysql-bin.*

11.8. Simulate adding data and scrolling logs

mysql> USE jiaowu;

mysql> INSERT INTO tutors (Tname) VALUES ('stu200');

mysql> FLUSH LOGS;

mysql> INSERT INTO tutors (Tname) VALUES ('stu201');

mysql> INSERT INTO tutors (Tname) VALUES ('stu202');

mysql> FLUSH LOGS;

11.9. Backup binary logs added since last snapshot;

cp /mydata/data

cat /backup/master-2016-07-11.info #View binary log files at the time of backup, and all binary log files since it was backed up

mysqlbinlog --start-datetime='16-07-11 15:30:08' mysql-bin.000005 mysql-bin.000006 > /backup/increment-`data +F-%H-%M-%S`.sql

11.10. Simulate deletion of mysql database data directory, resulting in mysql data crash

service mysqld stop

rm -rf /mydata/data/*

11.11. restore the database

cp -a /backup/bak-full-2016-07-11/* /mydata/data/

ls -l /mydata/data

service mysqld start

mysql> SET sql_log_bin=0; #Connect to mysql and temporarily turn off binary logging

mysql> source/backup/increment-2016-07-11-15-58-36.sql #Import incremental binary log files

mysql> SET sql_log_bin=0; #Connect mysql and enable binary logging

After reading this article, many readers will definitely want to know more about mysql log management and mysql backup and restore methods under linux. For more industry information, you can pay attention to our industry information column.

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.