Use Xtrabackup to do a full backup. After we have installed Xtrabackup, it gives us a few command-line tools where we back up the command to use is Innobackupex, which is a script that encapsulates several xtrabackup of several command lines internally, Makes the Innobackupex very easy to use first go to the official website to download the RPM package after installation can be used this tool is generally used for local backup, if you need to remotely connect to a database for backup also need to use other tools. Full backup: In general, backup is the data Export command Xtrabackupex--user=xx --PASSWORD=XX /save/to/path This enables full backups, To back up all data from the entire database to the specified path it is important to note that a user with the backup permission must be able to back up. We can specifically create a user to give him reload, lock tables, replication client These three permissions on line such as: Create user ' backup ' @ ' localhost ' Identified by ' PWD '; recover all of its privileges revoke all privileges, GRANT OPTION from [email protected] ' localhost '; After re-authorization backup permissions Grant RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' backup ' @ ' localhost '; re-refresh authorization table flush privileges; For example, we back up all libraries innobackupex-user=root-password= ' abc ' /mysql-backup will automatically create the Mysql-backup directory after execution, without the need for us to create it manually. After the backup is done, a directory with the current time name is generated under our backup directory. This directory, named after the current time, mainly contains the following file 1. We back up the names of all my databases 2. Our tablespace file 3. configuration file for MySQL 4. Binary log files, backup This moment your binary file name, and the postion location if you do not automatically create a backup directory, you will get an error: We need to manually create the directory, and the owner of the directory, belong to the group mysql backup goodAnd then you need to do something. In general, after the backup is complete, the data cannot be used for recovery operations because the backed up data may contain transactions that have not yet been committed, or have been committed, but have not been synchronized to the data file. Therefore, the data file remains in an inconsistent state at this time. So we need to do some preparatory work. The primary function of this work is to roll back uncommitted transactions. As well as synchronizing and committing transactions into data files, so that the data files are in a consistent state use the--apply-log option to achieve the above functions. For example: Innobackupex--apply-log /path/to/backup-dir followed up with the specified backup of the data, save the path, you can achieve the above work only executed this, To recover with the backed up data we back up, in case our database changes again, because it is an online database, always have users to store data. In this case, we need to use binary logs for instant point recovery. Therefore, the binary log is very important. After we have backed up, we also need to back up the binary log, the binary log is generally stored in the data directory, the name is generally mysql-bin.00000x backup binary log, first log on to MySQL to perform a refresh log flush logs; This will generate a new binary log after the refresh, and we'll back up the old one. Copy this file directly to implement restore: If we have backed up the log file and used Xtrabackup to make a full backup of the database. Our database has failed and we need to restore it. Note that restore data is restored by using Xtrabackup to restore the data in a database shutdown state: Use Innobackupex--cpoy-back/path/to/backup-dir the path to the data saved when you follow up with your backup data Note that the recovered data file is still stored under your original data directory, if the data belongs to the owner, the group becomes root, it needs to be changed to Mysqlchown-r Mysql.mysql /path/to/data Then log on to the database for binary log recovery. First find our backup binary file path read it out and save the additional file suffix named. sql, using the Mysqlbinlog command, such as mysqlbinlog/root/mysql-bin.000001 >/root/abc.sql then connectTo the MySQL database for binary log recovery mysql>set Sql_log_bin=0; mysql>source/root/abc.sql; mysql>set sql_log_bin=1; If there is no error, complete the we must periodically manually back up the binary logs
MySQL hot backup full backup
