Mysqldump belongs to the MySQL Client tool mysqldump Backup tool for MyISAM storage engine backup is warm backup, for the InnoDB storage engine is able to implement hot backup. Using the Mysqldump Backup database enables full backup + binary log files, which is also considered a full backup + incremental backup. First back up this database this is the full backup, and then the changes in the data after the daily backup of the binary log this is an incremental backup. Use of the mysqldump command: Usage mysqldump + options + database common options to back up:db_name:backing up a specified database if you back up a table in the database, db_name [tb_name] Backup does not include the CREATE DATABASE command, which means that you will need to manually create the database when you want to restore the data later. For example, we back up a database called file: If Linux below we can mysqldump-u Root-p file >/root/sql/file.sql Here is called the backup to/root/sql/directory, Named File.sqlwindows under Backup mysqldump-u root-p file > D:\sqlback\file.sql back up so we can use a text editor to open the You can see that the backup of mysqldump is the insert tone that backs up the data from the entire table into a bulk INSERT. So later if you delete the database that you backed up. The database you want to import is not imported, it does not have a command to create a database. For example, I want to try to import a backup database: Perform MySQL < file.sql directly under Linux switch to backup data path now we create a database called China and then import the MySQL version of Chinese provinces and cities data into Mysql-u root-p Lt MySQL version of the Chinese provinces and Cities data sheet. SQL to do this means import this MySQL version of the Chinese provincial and municipal data tables. SQL backup data to our China library This kind of backup must be in the database online to be able to back up, if the database is very busy at all times have write operations, Do not use this way to backup, we should first make a lock table and then back up. The steps are as follows: First use the MySQL client to connect to MySQL after, execute the following two sentences lock tables; #锁表, all tables can no longer write to the data. Can read data flush tables; #将内存中的数据刷新保存到硬盘或者执行flush tables with read lock; perform the unlock table command after backup, remember unlock tables;--master-data=nThe value range of n is 0-2 0 means that the binary log file is not logged and the path location 1 means that the location is recorded as change Master to, which can be used to start the recovery startup directly from server 2 to record the location as change master to, but the default is commented out Example: Mysqldump-u root-p--master-data=2 file >d:\sqlback\file ' date+%f-%h-%m-%s '. SQLThis means backing up the file library to the specified path under the current time name if the error: mysqldump:Error:Binlogging on server not active indicates that no binary log is turned on, it needs to be backed up in this way when the profile is turned on. We can see that the SQL file week will have a line--change master to master_log_file= ' current binary log filename ' master_log_pos= event log location ' The next time you back up the data from the binary log, you can back it up from this location in the binary file.--lock-tablesThat means locking all the tables automatically, and if we just back up a single library and lock all the tables, that's unreasonable, we can use this option. Log in to MySQL client to lock a single table and back up--flush_logs: Automatically perform log flush to disk before backupIf the storage engine for all tables in the specified library is InnoDB, you can use the--single-transactionstart a hot backup and start a hot backup without us having to manually lock the tableBack up multiple libraries:--all-databases; Back up all libraries--databases db_name1,db_name2,,,,,; Backing up multiple libraries These two command backups create a database command, so you don't need to manually create the restore time. For example: Mysqldump-u root-p--single-transaction--all-databases--master-data = 2-- Flush-logs >d:\sqlBack\all.sqlThis means that we will use a hot backup to back up all the libraries to the D drive, named All.sql--events: Event--routines: Stored procedure, stored function--triggers: Trigger
MySQL logical backup (use of MySQL dump)
