MySQL 5.6 Non-stop machine master-slave construction (one Master one from)

Environment Description:
Version 5.6.25-log
Main Library ip:10.219.24.25
From library ip:10.219.24.22
OS Version: CentOS 6.7
Hot standby software installed: Xtrabackup
Firewall is off

Add:
MySQL 5.6 Installation: http://blog.csdn.net/zhang123456456/article/details/53608554
Xtrabackup Installation: http://blog.csdn.net/zhang123456456/article/details/72836184

One master one from the building process:

1, the main library parameter adjustment
--Stop the main library MySQL
[[Email protected] ~]# service MySQL stop
[Email protected] ~]# Netstat-nltp|grep mysql|grep 3606
--Adjust MY.CNF parameters
[Email protected] ~]# CAT/ETC/MY.CNF
[Client]
Password = Oracle
Port = 3306
Socket =/data/mysql/mysql.sock
[Mysqld]
Server-id=25
Port = 3306
Socket =/data/mysql/mysql.sock
Character_set_server = UTF8
Character_set_client = UTF8
Collation-server=utf8_general_ci
Lower_case_table_names = 1
max_connections = 1000
DataDir =/data/mysql
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Binlog_format = Mixed
Innodb_data_file_path = Ibdata1:12m:autoextend
[MySQL]
Default-character-set = UTF8
Description: Parameters that the main library must configure
Server-id (master and slave Server-id must be different), Log_bin, Binlog_format
2, from the Library parameter adjustment
--Stop MySQL from the library
[[Email protected] ~]# service MySQL stop
[Email protected] ~]# Netstat-nltp|grep mysql|grep 3606
--Adjust MY.CNF parameters
[Email protected] ~]# CAT/ETC/MY.CNF
[Client]
Password = Oracle
Port = 3306
Socket =/data/mysql/mysql.sock
[Mysqld]
Server-id=22
Port = 3306
Socket =/data/mysql/mysql.sock
Character_set_server = UTF8
Character_set_client = UTF8
Collation-server=utf8_general_ci
Lower_case_table_names = 1
max_connections = 1000
DataDir =/data/mysql
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Relay-log =/data/mysql/relaylog/relay
Relay-log-index =/data/mysql/relaylog/relay
Relay_log_purge = On
Read-only = 1
[MySQL]
Default-character-set = UTF8
Description: The parameters that must be configured from the library
Server-id, Log_bin, Relay-log, read-only

3, the main library backup
--Main Library backup directory
[Email protected] full]# pwd
/xtrabackup/full
--Main Library Innobackupex backup
[Email protected] ~]# Innobackupex--user=root--password=oracle--port=3606/xtrabackup/full/
170610 17:50:23 Backup created in directory '/xtrabackup/full/2017-06-10_17-50-19/'
MySQL binlog position:filename ' binlog.000010 ', position ' 120 '
....
170610 17:50:23 completed ok!
--View backup Binlog number and cutoff position
[email protected] 2017-06-10_17-50-19]# cat Xtrabackup_binlog_info
binlog.000010120

4. Create the same backup directory from the library as the main library
[Email protected] ~]# mkdir-p/xtrabackup/full
[Email protected] ~]# chown-r mysql:mysql/xtrabackup/full/

5, the main library will back up the SCP to the from library
[[email protected] full]# pwd
/xtrabackup/full
[[email protected] full]# scp-r 2017-06-10_17-50-19 10.219.24.22:/xtrabackup/full
6, view the SCP backup from the library
[[email protected] ~]# cd/xtrabackup/ full/2017-06-10_17-50-19/
[[email protected] 2017-06-10_17-50-19]# ll
Total 12320
-rw-r-----. 1 root Root 419 June 18:01 backup-my.cnf
-rw-r-----. 1 root root 12582912 June 18:01 ibdata1
Drwxr-x---. 2 root root 40 18:01 MySQL
drwxr-x---. 2 root root 4096 June 18:01 Performance_schema
Drwxr-x---. 2 root root 4096 June 18:01 Test
-rw-r-----. 1 root root 18:0 June 18:01 xtrabackup_binlog_info
-rw-r-----. 1 root root 113 June 10 1 xtrabackup_checkpoints
-rw-r-----. 1 root root 482 June 18:01 xtrabackup_info
-rw-r-----. 1 root root 2560 June 1 0 18:01 Xtrabackup_logfile

7. Create a sync user for the main library
mysql> GRANT replication Slave on * * to ' slave25 ' @ '% ' identified by ' Oracle ';
Query OK, 0 rows affected (0.05 sec)

8. Recovering the Master library data from the library
--Rename the original DataDir folder to a new location from the library and create the original folder
[Email protected] ~]# Mv/data/mysql/data/mysqlbak
[Email protected] ~]# mkdir-p/data/mysql
--Innobackupex Apply-log
[Email protected] ~]# Innobackupex--apply-log--user=oracle \
--password=oracle--port=3606/xtrabackup/full/2017-06-10_17-50-19/
--Innobackupex copy recovered files to the original data location
[Email protected] mysql]# Innobackupex--defaults-file=/etc/my.cnf--user=root \
--copy-back/xtrabackup/full/2017-06-10_17-50-19/

170610 18:25:11 completed ok!
--Create Binlog directory and Relaylog directory and empower
[Email protected] ~]# mkdir-p/data/mysql/binarylog
[Email protected] ~]# mkdir-p/data/mysql/relaylog/
[Email protected] mysql]# chown-r mysql:mysql/data/mysql

9, from the library configuration and detection
--Start from library
[Email protected] mysql]# Mysqld_safe--defaults-file=/etc/my.cnf &
--Specify the basic information to synchronize with the main library from the library
Mysql>
Change Master to
Master_host= ' 10.219.24.25 ',
master_port=3306,
Master_user= ' Slave25 ',
master_password= ' Oracle ',
Master_log_file= ' binlog.000010 ',
master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

Parameter explanation:
Master_host: Set the IP address of the primary server to connect to
Master_user: Set the user name of the primary server to connect to
Master_password: Set the password for the primary server to connect to
Master_log_file: Set the log name of the bin log of the primary server to which you want to connect
Master_log_pos: Set the record location for the bin log of the primary server to which you want to connect
--Start slave status (start listening for msater changes)
mysql> start slave;
Query OK, 0 rows affected, 1 Warning (0.00 sec)
--View the status of the slave.
Mysql> show slave status \g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.219.24.25 #主库 IP
Master_user:slave25 # User of Master library replication
master_port:3306 # Main Library mysqld
Connect_retry:60
MASTER_LOG_FILE:BINLOG.000010 #io_thread Read the main library master_log_file
read_master_log_pos:717 # Io_thread Read the main library Master_log_pos
relay_log_file:relay.000002
relay_log_pos:877
relay_master_log_file:binlog.000010 #sql_thread performing the master_log_file of the main library
Slave_io_running:yes # Key, io_thread whether Running
Slave_sql_running:yes # Key, sql_thread whether Running
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:717 #sql_thread performing the Master_log_pos of the main library
relay_log_space:1040
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 # delay from the library
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:25
master_uuid:29d68531-4cf9-11e7-8e1f-000c297c4100
Master_info_file:/data/mysql/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)

ERROR:
No query specified

10, master-Slave synchronization check
--Main Library
mysql> CREATE DATABASE Repl;
Query OK, 1 row Affected (0.00 sec)
Mysql> Use REPL
Database changed
Mysql> CREATE TABLE REPL (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO REPL values (1);
Query OK, 1 row Affected (0.00 sec)

--From the library
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Binarylog |
| MySQL |
| Performance_schema |
| Relaylog |
| Repl |
| Test |
+--------------------+
7 Rows in Set (0.00 sec)
Mysql> Use REPL
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a
Database changed
Mysql> select * FROM REPL;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec) > Sync succeeded!

MySQL 5.6 Non-stop machine master-slave construction (one Master one from)