[MySQL] High Availability

The actual size of the Instance contains fragments)
SELECTROUND (SUM (data_length)/1024/1024/1024), 5) ASDATA, ROUND (SUM (index_length)/1024/1024/1024), 5) ASidx, ROUND (SUM (data_length + index_length)/1024/1024/1024), 5) AStotalFROMinformation_schema.tables

Mysqldump]
# Support for MySQL Data Recovery of different versions
# Note that two mysqldump tables will cause the lock
# Large data volumes are not recommended
Export all table structures)
Mysqldump-uadmin-p-S/data/socket/mysql3350.sock -- opt -- triggers-E-d-R-B -- master-data = 2 -- default-character-set = utf8databases>/data /X. SQL
-- Opt: no database creation process
-E event
-B database creation
-R stored procedure-routines
-D: no data, only table structure
-- All-databases --->-
Export table-only Structure
Mysqldump-uadmin-p-S/data/socket/X. sock-B -- triggers-d -- master-data = 2 -- default-character-set = utf8databases>/data/X. SQL
Export table events and stored procedures
Mysqldump-uadmin-p-S/data/socket/X. sock-B -- triggers -- routines -- events -- master-data = 2 -- default-character-set = utf8databases>/data/X. SQL
Export from database
Mysqldump-uadmin-p-S/data/socket/X. sock-B -- triggers-E-R -- dump-slave = 2 -- default-character-set = utf8XXXX>/mysqlData/dump_XXXX. SQL

Remote export of SQL files
Mysqldump -- user = admin -- password = 'X' -- host = ip -- port = 3382 -- skip-opt -- master-data = 2 -- single-transaction -- add-drop-table -- create-options -- quick -- extended-insert -- set-charset -- disable-keys -- triggers-R -- flush-logsdatabasestables>/mysqlData/
Export SQL files locally
Mysqldump-uadmin-p-S/data/socket/mysql3382.sock -- skip-opt -- master-data = 2 -- single-transaction -- add-drop-table -- create-options -- quick -- extended-insert -- set-charset -- disable-keys -- triggers-R -- flush-logsdatabasestables>/mysqlData/
No database creation process. Pay attention to the database and table name.

SQL File Import
Mysql-uadmin-p-h '192. 168.169.1 '-P3344 -- default-character-set = utf8 </mysqlData/X. SQL
Mysql-uadmin-p-S/data/socket/mysql3384.sock -- default-character-set = utf8 </mysqlData/X. SQL

Delete the binlog file
Mysql-uadmin-p-S/data/socket/X. sock-e "purgebinarylogsto 'mysql-bin. X ';

Innobackupex]
Obtain online data backup locally
Innobackupex -- user = admin -- password = 'X' -- defaults-file =/data/config/X. cnf -- slave-info/mysqlData/backup/
If the remaining space on the local disk cannot meet the requirements of local backup, use remote backup.
Innobackupex -- user = admin -- password = xxx -- defaults-file =/data/config/X. cnf -- remote-host = 172. x. x. x -- slave-info/mysqlData/backup/

Restore
Innobackupex -- apply-log/mysqlData/bakbalog0628/2013-06-28_13-29-30
Innobackupex -- copy-back -- defaults-file =/data/config/my3392.cnf/mysqlData/bakbalog0628/2013-06-28_13-29-30

1. delete old data
/MysqlLog/logs /*
/MysqlData/data /*

2. Restore data (executed successively)
Innobackupex -- apply-log/mysqlData/2013-03-27_15-32-03/
Innobackupex -- copy-back -- defaults-file =/data/config/X. cnf/mysqlData/2013-03-27_15-32-03/
-
3. Set permissions
Chown-Rmysql.mysqldata and log directory
-
4. Start the database
Mysqld_safe -- defaults-file =/etc/XX. cnf &

-
5. View recovery points
Xtrabackup_slave_info used for backup on the master database
Xtrabackup_binlog_pos_innodb
Use xtrabackup_binlog_info for backup in the actual master database

Synchronization]
# Settings
Changemasterto
Master_host = '1970. 168.1.x ',
Master_user = 'userx ',
Master_password = 'userx ',
Master_port = XXXX,
Master_log_file = 'mysql-bin. x ',
Master_log_pos = X
# Synchronization error -->
Slavestop; setglobalsql_slave_skip_counter = 1; slavestart;

Authorization]
Mysql> UPDATEuserSETpassword = PASSWORDWHEREuser = 'root' andhost = '% ';
Mysql> FLUSHPRIVILEGES; ------ refresh the MySQL system permission table
Mysql> GRANTALLPRIVILEGESON *. * TO 'admin' @ '%' IDENTIFIEDBYPASSWORD 'X' WITHGRANTOPTION

View BinLog File Content]
Mysqlbinlog -- start-position = "63933950" -- stop-position = "63940929"/mysqlLog/mysql-bin.X -- no-defaults-v -- base64-output = DECODE-ROWS>/data/X.txt
Mysqlbinlog -- start-date = '2017-09-051: 24' -- stop-date = '2017-09-051: 26 '/mysqlLog/mysql-bin.X-v-v -- base64-output = DECODE-ROWS>/data/X.txt