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

A cold backup:

Cold backup, stop service, tar command, package the data directory of mysql directly

The method of mysqldump hellodb (library name) > hellodb.sql will not back up the creation of the database, so when you delete the hellodb database, you can restore the tables and data in the database, but not the database.

Mysqldump-B hellodb > hellodb.sql will back up not only the tables and data in the library, but also the creation process of the library.

Mysqldump backup database-A, backup mysql and define database, it is recommended to make. Mysql libraries and custom libraries, including creation methods and data, are backed up.

Mysqldump backs up the database,-R backs up all stored procedures and declare definition functions

The combination of mysqldump-A full backup + mysqlbinlog looks at the current mark bits of the binary log before the full backup. If you continue to operate on the library after the full backup, the binary log will be used for incremental backup.

> mysqldump-A > xx.sql (at this point, the show master logs is mysql-bin.000001 8099)

> mysqlbinlog-- start-position=8099 / data/bin/mysql-bin.000001 > / data/backup/inc.sql (this operation will record the operations on the database after bit 8099, but not before 8099)

When restoring data, restore a global backup first, and then an incremental backup

InnoDB backup options:

Hot backup is supported, but warm backup is available but not recommended-single-transaction this option is recommended in Innodb and does not apply to MyISAM. This option starts the transaction by executing the START TRANSACTION directive before starting the backup. This option creates a consistent snapshot by dumping all tables in a single transaction. Applies only to tables stored in storage engines that support multi-version control (only InnoDB can do so); dumps are not guaranteed to be consistent with other storage engines. When performing a single transaction dump, to ensure a valid dump file (the correct table contents and binary log location), no other connection should use the following statement: ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE this option and-- lock-tables (this option implies committing pending transactions) option is mutually exclusive when backing up large tables, it is recommended to use the-- single-transaction option with-- quick

InnoDB recommends backup policy mysqldump-uroot-A-F-E-R-single-transaction-- master-data=1-- flush-privileges-- triggers-- default-character-set=utf8-- hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

Recommended InnoDB backup option mysqldump-A-single-transaction-master-data=1-hex-blob > / data/backup/fullbak_ `date +% F`.sql

A script for backup of Innodb sub-library

#! / bin/bsah

For db in `mysql-e'show databases' | grep-Evi 'information_schema | performance_schema | test | Database' `; do

Mysqldump-B ${db}-- single-transaction-- master-data=2 | gzip > / data/backup/$ {db} _ `date +% F`.sql.gz

Done

MyISAM backup options:

Support warm preparation Hot backup is not supported, so you must first lock the library to be backed up, and then start the backup operation to lock as follows: add a global read lock, lock all tables in all libraries, and add the-- singletransaction or-- lock-tables option to turn off this option. Note: when the amount of data is large. It may result in not being able to access the database concurrently for a long time. For each database that needs to be backed up, lock all its tables before starting the backup. The on,--skip-locktables option can be disabled by default. For backing up multiple libraries of MyISAM, it may cause data inconsistency. Note: the above options have the same effect on InnoDB tables for warm backup, but are not recommended.

MyISAM recommends backup policy mysqldump-uroot-A-F-E-R-x-master-data=1-- flush-privileges-- triggers-- defaultcharacter-set=utf8-- hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

Master and slave of mysql

Open the binary log on the master node and the relay log on the slave node, and the number of the server_id master and slave cannot be the same.

Main example:

From:

Create a user with permission to synchronize on the primary server

GRANT REPLICATION SLAVE ON *. * TO repluser@'192.168.23.148' IDENTIFIED BY '123456'

From the server:

CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4

Start slave

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.