Pxc- Readiness and recovery

Full backup command

Sudo mysqldump-usunx-packs mysqladmin'- P6612-h292.168.1.203-A-B-- skip-add-locks-- skip-lock-tables-F | gzip > / data/backup/all_$ (date +% F). Sql.gz

Mysqldump command options are explained in detail:

-P: specify the mysql service port

-h: specify the ip to connect to the mysql

-A: back up all database tables

-B: add statements to create and connect to the database

-F: refresh the bin-log log file before starting the export

-- skip-add-locks: no need to add locks

-- skip-lock-tables: no need to lock table backup

Extend:

Add-locks Add locks around INSERT statements. (Defaults to on; use-- skip-add-locks to disable.)

Add LOCK TABLES before each table is exported and then UNLOCK TABLE (for faster insertion into MySQL). It is on by default.

-f,-- force Continue even if we get an SQL error.

Ignore SQL errors during export

-x,-- lock-all-tables

The submit request locks all tables in all databases to ensure data consistency. This is a global read lock and automatically turns off the-- single-transaction and-- lock-tables options

-l,-- lock-tables Lock all tables for read.

(Defaults to on; use-skip-lock-tables to disable.)

Mysqldump-uroot-p-host=localhost-all-databases-lock-tables

Subscribe to all tables before you start exporting. Lock the table with READ LOCAL to allow MyISAM tables to be inserted in parallel. Tables that support transactions, such as InnoDB and BDB,--single-transaction, are a better choice because it does not need to lock the table at all.

Note that when exporting multiple databases,-- lock-tables locks tables for each database. Therefore, this option does not guarantee the logical consistency of the exported file's tables across databases. The export status of different database tables is completely different.

-- master-data [= #]

This option appends the location and file name of the binlog to the output file. If 1, the CHANGE MASTER command is output; if 2, comment information is added before the output CHANGE MASTER command. This option turns on the-- lock-all-tables option unless-- single-transaction is also locked.

Restore command

Backup data recovery for compression

1. Methods

Gzip-d / backup/mysql.sql.gzmysql-uroot-poldboy 2.sql

2. Methods

Gunzip SET GLOBAL pxc_strict_mode=DISABLED; then change the default wsrep_replicate_myisam = OFF to ONmysql > show VARIABLES like 'wsrep_replicate_myisam%';wsrep_replicate_myisam = OFFmysql > SET GLOBAL wsrep_replicate_myisam = ON;mysql > show VARIABLES like' wsrep_replicate_myisam%';wsrep_replicate_myisam = ON

It is best to change parameters on all pxc nodes