MYSQL 5.6 Copy Transcript--replication

Main Library Recommended parameters

server_id = 1

Log_bin = Mysql-bin

Binlog_format = row

Sync_binlog = 1

Binlog_cache_size = 16M

Max_binlog_cache_size = 32M

Max_binlog_size = 512M

Expire_logs_days = 7

Master_info_repository = table

Relay_log_info_repository = table

Relay_log = Relay-bin

Relay_log_recovery = 1

Create a full backup of the main library

[Email protected] ~]# Su-mysql

[Email protected] ~]$./xtrabackup_full.sh

[Email protected] ~]$ Cd/usr/local/mysql/backup

[Email protected] backup]$ scp-r 2018-02-05_05-41-26 [email protected]:/usr/local/mysql/backup

[Email protected] ~]# mysql-uroot-pabcd.1234

mysql> grant replication Slave on * * to ' repl ' @ ' 192.168.1.% ' identified by ' repl ';

mysql> flush Privileges;

Recommended parameters for the standby library

server_id = 2

Log_bin = Mysql-bin

Binlog_format = row

Sync_binlog = 1

Binlog_cache_size = 16M

Max_binlog_cache_size = 32M

Max_binlog_size = 512M

Expire_logs_days = 7

Master_info_repository = table

Relay_log_info_repository = table

Relay_log = Relay-bin

Relay_log_recovery = 1

Skip_slave_start

Recovering a database in a standby repository

[Email protected] ~]# Su-mysql

[Email protected] ~]$ Innobackupex--defaults-file=/etc/my.cnf--apply-log/usr/local/mysql/backup/2018-02-05_ 05-41-26

[Email protected] ~]$ Innobackupex--defaults-file=/etc/my.cnf--copy-back/usr/local/mysql/backup/2018-02-05_ 05-41-26

[[Email protected] ~]# service mysqld start

[[Email protected] ~]# service mysqld start

Starting mysql.logging to '/usr/local/mysql/data/mysql.err '.

....... [OK]

[Email protected] ~]# cd/usr/local/mysql/backup/2018-02-05_05-41-26

[email protected] 2018-02-05_05-41-26]# cat Xtrabackup_binlog_info

mysql-bin.000007 23607448

[Email protected] ~]# mysql-uroot-pabcd.1234

mysql> change Master to \

Master_host= ' 192.168.1.201 ', \

Master_log_file= ' mysql-bin.000007 ', \

master_log_pos=23607448;

Query OK, 0 rows affected (0.29 sec)

mysql> start slave user= ' repl ' password= ' repl ';

Query OK, 0 rows affected, 1 warning (0.04 sec)

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.1.201

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000007

read_master_log_pos:23607737

relay_log_file:relay-bin.000002

relay_log_pos:572

relay_master_log_file:mysql-bin.000007

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:23607737

relay_log_space:739

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

Master_server_id:1

master_uuid:2019c32c-0cf8-11e8-b217-0800272bd617

Master_Info_File:mysql.slave_master_info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:

Executed_gtid_set:

auto_position:0

1 row in Set (0.00 sec)

MYSQL 5.6 Copy Transcript--replication