MySQL 5.6 Non-stop machine master-slave build (one master one from Gtid replication)

Source: Internet
Author: User

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:

Principle of Master-slave replication: http://blog.csdn.net/zhang123456456/article/details/72972701
GTID Copy principle: http://blog.csdn.net/zhang123456456/article/details/73002216
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]

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

#GTID:
Server-id=25
Gtid_mode=on #开启gtid模式
Enforce_gtid_consistency=on #强制gtid一致性, not supported for specific CREATE table after opening

#binlog
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Binlog_format = Mixed
Log-slave-updates=1

#relay Log
Skip_slave_start=1
[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, Log-slave-updates, Gtid_mode, enforce_gtid_consistency

--Start the main library
[Email protected] ~]# Mysqld_safe--defaults-file=/etc/my.cnf &

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]
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
Read-only = 1

#GTID:
Server-id=22
Gtid_mode=on
Enforce_gtid_consistency=on

#binlog
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Log-slave-updates=1

#relay Log
Relay-log =/data/mysql/relaylog/relay
Relay-log-index =/data/mysql/relaylog/relay
Relay_log_purge = On
Skip_slave_start=1
[MySQL]
Default-character-set = UTF8
Description: The parameters that must be configured from the library
Server-id, Log_bin, Relay-log, Read-only, log-slave-updates

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!

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_auto_position=1;
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_auto_position:gtid mode, replication based on transaction ID
--Start slave status (start listening for msater changes)
mysql> start slave;
Query OK, 0 rows affected (0.01 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
Master_user:slave25
master_port:3306
Connect_retry:60
master_log_file:binlog.000016
read_master_log_pos:151
relay_log_file:relay.000021
relay_log_pos:355
relay_master_log_file:binlog.000016
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:151
relay_log_space:518
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: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:1
1 row in Set (0.00 sec)

ERROR:
No query specified

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

--From the library
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Binarylog |
| Gtid |
| MySQL |
| Performance_schema |
| Relaylog |
| Repl |
| Test |
+--------------------+
8 rows in Set (0.00 sec)
Mysql> Use Gtid
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 Gtid;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec) > Gtid synchronized successfully!

MySQL 5.6 Non-stop machine master-slave build (one master one from Gtid replication)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.