Mysql Backup Recovery Optimization

Mysql Backup and recovery tuning Mysql Backup and recovery mysqldump, ibbackup, replication Backup points: Hot Backup (Hot Backup) online Backup, no impact on the DB when Cold Backup (Cold Backup) is stopped, simple: Copy physical files, offline backup of frm database files, shared tablespace files, independent tablespace ibd files, and backup of redo log files. Back up and compress DBA scripts. Warm Backup (Warm Backup) DB Backup during running has an impact on DB operations. A global read lock is added to ensure the consistency of Backup data. Logical backup means that the backup file is readable, usually a text file. The content is generally an SQL statement, and the actual data of the table, such as mysqldump and select * into OUTFILE. It is used for database upgrades, migration, and recovery for a long time. Mysqldump -- all-databases> file_name; back up all databases mysqldump -- databases db1 db2 db3> dbs. SQL; back up the specified database mysqldump -- single-transaction test> test_backup. SQL; back up the test architecture. Select * into OUTFILE; mostly used to export table data; select * into outfile 'xxxx' from a; load data infile 'xxx' into table a when restoring; restore mysql-uroot-p <test_backup. SQL source .... xxx. SQL mysql can export stored procedures, triggers, events, and data, but cannot export views. Copy a physical database file. The database can be running or stopped. Full backup full backup Incremental Backup backs up the updated data based on the last full backup. Log backup backs up binary logs. Its replication principle is to asynchronously redo binary logs in real time. [Mysqld] Enable binary sync_binlog = 1innodb_support_xa = 1 simple mysqlbinlog binlog.000001 | mysql-uroot-p testmysqlbinlog binlog. [0-10] * | mysql-uroot-p test restores multiple mysqldump and adds the-single-transaction option to obtain consistent backup. Backup is performed in a long transaction. You can compile a backup application based on your own policies to conveniently set backup methods and monitor backup results. You can use a third-party interface to notify DBA in real time, this enables 24x7 backup monitoring. In the DAO (Database Admin Online) system, DBA can back up, monitor, and hardware hundreds of mysql Database servers. In the event of a problem, the Apsara interface will be notified immediately. Python language: Django WEB page. Remote Backup is required at any time. Ibbackup high-performance Hot Standby XtraBackup GPL2 free linuxLVM (Logical Volume Manager, GNU/Linux Logical Volume Manager) for disk partition management, you can create a Volume Group for multiple disk partitions to form a unified structure. It is very fast to create an LVM snapshot. Data Distribution replication does not require much bandwidth. Read load balancing multiple slave servers and achieve Load Balancing through DNS's Round-Robin and Linux's LVS; replication is helpful for backup, but it is not a backup and cannot completely replace backup; the slave established through replication helps failover and reduces the downtime and recovery time of the fault. However, I am worried that the primary node may mistakenly operate on the Data. At this time, I am still working on it. This situation cannot be handled. Snapshot processing is required for the slave. You can set permissions on the server. Optimization 1. Select the appropriate CPU. OLTP does not have high CPU requirements, and requires less CPU consumption for complex queries, such as comparison, sorting, and connection. OLAP is a CPU-intensive operation. OLTP is an I/O-intensive operation. 2. Memory is important; Percona Vadim has compared the impact on memory, and the memory size can directly reflect the performance of the database. The increase of the 2 GB-> 22 GB buffer pool greatly improves the performance. Mysql> show global status like 'innodb % read % '; \ G + bytes + --------- + | Variable_name | Value | + bytes + --------- + | bytes | 0 | Innodb_buffer_pool_read_ahead | 0 | Number of pre-reads from a physical disk | bytes | 0 | expected page, but the number of pages that are replaced from the buffer pool if they are not read, which is generally used to determine the pre-Read efficiency | Innodb_buffer_pool_read_requests | 6592 | from Page read count in the buffer pool | Innodb_buffer_pool_reads | 396 | PAGE read count from physical disk | Innodb_data_pending_reads | 0 | Innodb_data_read | 8671232 | Total number of bytes read | Innodb_data_reads | 408 | initiated number of requests, you may need to read multiple pages each time. | Innodb_pages_read | 395 | Innodb_rows_read | 0 | + ----------------------------------------- + generally, the buffer pool hit rate should not be less than 99%. Even if the size of the buffer pool is greater than the size of the database file, it does not mean that no disk is read or written. The buffer pool is only used to store hotspot data. Buffer Pool hit rate = Bytes/(Bytes + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads) Average number of bytes read each time = Innodb_data_read/Innodb_data_reads 3. hard disks have an impact on database performance. Currently, most of them are traditional mechanical hard disks. This technology is very mature, such as SAS or SATA interface hard disks. Two indicators: track time and speed. Minimum seek time of 3 ms, 15000 rpm. The biggest problem is the read/write head. Random Access takes a long time to rotate and locate the head, so the sequential access speed is much higher than that of random access. RAID improves database performance. data files are distributed across different hard disks to achieve load balancing. Solid state drive, based on flash memory, low-latency, low-power and shockproof, TB-level storage solution, non-magnetic head. Data in flash memory cannot be updated. data can only be overwritten by sector overwrite, which requires time-consuming erasure before processing. The read and write operations on flash memory are non-symmetric, And the read operations are much faster than the write operations. 4. Proper RAID configuration; RAID: enhances data integration, fault tolerance, and increases the processing capacity or capacity. RAID 0: combines Hard Disks without redundancy, parallel I/O, and the fastest speed. If a disk is damaged, all data is lost. RAID 1: Two or more groups of N disks are mirrored to each other, with the highest performance. When the primary hard disk is damaged, the image hard disk works in place. The data security is the highest, but the disk utilization is the lowest. RAID 5: a solution that combines storage performance, data security, and storage costs. Use the Disk Striping technology. At least three hard disks are required. Instead of backing up, It stores the parity information and data to store different hard disks separately, and uses the remaining data and parity to restore. The read speed is the same as that of RAID0, but the write speed is slow due to parity check. You can use write Back (to put written data into your own cache) to improve performance. 5. The operating system is also very important. OS. Note that windows is case-insensitive, while linux is case-insensitive. The system supports memory. 6. Database impact of different file systems; windows NTFS, Solaris ZFS, and Linux EXT3/4. 7. select an appropriate benchmark testing tool. Sysbench (CPU performance, disk I/O performance, scheduler performance, memory allocation and transmission speed, POSIX thread performance, OLTP benchmark test)