Shulou(Shulou.com)05/31 Report--

MySQL 5.5.What is the process of master replication building? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

-Node 1 IP 192.168.78.141 PORT 3306

-Node 2 IP 192.168.78.137 PORT 5505

-- configure replication from node 1 to node 2

-- Edit the configuration file for Node 1

[root@localhost install] # vim / etc/my.cnf

# Log

Server-id = 100

Log-bin = / log/binlog/mysql-bin

-- install the MySQL software on node 2. For the installation process, please refer to the source code installation article.

Http://blog.itpub.net/26506993/viewspace-2072859/

-- on Node 1, create a full backup using Xtrabackup

For information about Xtrabackup, please refer to

Http://blog.itpub.net/26506993/viewspace-2087734/

Http://blog.itpub.net/26506993/viewspace-2088737/

[root@localhost backup] # / install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex-- defaults-file=/etc/my.cnf-- stream=tar / tmp-- user system-- password 'Mysql#2015' | gzip->

/ backup/xtra/xtra_fullbackup_20160501.tar.gz

-- copy backup to Node 2

[root@localhost backup] # scp / backup/xtra/xtra_fullbackup_20160501.tar.gz root@192.168.78.137:/backup/20160501

Extract the backup to the data file directory

[root@localhost 5505] # tar xivfz xtra_fullbackup_20160501.tar.gz-C / mysql_data/5505

Xtraback needs to be installed on node 2

-- use Xtrabackup to prepare data and apply logs to make data files consistent

[root@localhost bin] # / innobackupex-- defaults-file=/mysql_data/cnf/my.cnf-- apply-log / mysql_data/5505

.

InnoDB: Doing recovery: scanned up to log sequence number 68405269

InnoDB: Doing recovery: scanned up to log sequence number 68405269

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 414, file name / log/binlog/mysql-bin.000012

InnoDB: Removed temporary tablespace data file: "ibtmp1"

InnoDB: Creating shared tablespace for temporary tables

InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait...

InnoDB: File'. / ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment (s) found. 1 redo rollback segment (s) are active.

InnoDB: 32 non-redo rollback segment (s) are active.

InnoDB: 5.7.11 started; log sequence number 68405269

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 68405621

160430 23:51:25 completed OK!

-- create a dedicated replication account on the Node 1 database

Mysql > grant replication slave on *. * to 'repl'@'192.168.78.%' identified by' Mysql#2015'

Query OK, 0 rows affected (0.04 sec)

-- configure Node 2 configuration file

[root@localhost 5505] # vim / mysql_data/cnf/my.cnf

# Log

Server-id = 200

Log-bin = / mysql_log/binlog/mysql-bin

Relay-log = / mysql_log/binlog/product-relay-bin

Relay-log-index = / mysql_log/binlog/product-relay-index

Binlog_cache_size = 32m

Max_binlog_cache_size = 512m

Max_binlog_size = 512m

Binlog_format = MIXED

-- start the Mysql service of Node 2

[root@localhost bin] # / data/bin/mysqld_safe-- defaults-file=/mysql_data/cnf/my.cnf &

-- configure Slave node replication environment

Query the binaries and locations where Slave nodes connect to Master nodes

When using Xtrabackup backup, this information is saved in the xtrabackup_binlog_info file.

[root@localhost 5505] # more xtrabackup_binlog_info

Mysql-bin.000012 414

-- execute the CHANGE MASTER statement on Node 2

Mysql > change master to

-> master_host='192.168.78.141'

-> master_port=3306

-> master_user='repl'

-> master_password='Mysql#2015'

-> master_log_file='mysql-bin.000012'

-> master_log_pos=414

Query OK, 0 rows affected (0.13 sec)

-- start the application thread

Mysql > start slave

-- View synchronization status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.78.141

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000013

Read_Master_Log_Pos: 341

Relay_Log_File: product-relay-bin.000003

Relay_Log_Pos: 487

Relay_Master_Log_File: mysql-bin.000013

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

-- replication from Node 2 to Node 1

-- add the setting of relay log in the configuration file of Jiadian 1.

[root@localhost log] # vim / etc/my.cnf

Relay-log = / log/binlog/product-relay-bin

Relay-log-index = / log/binlog/product-relay-index

-- restart the database of Node 1

[root@localhost tmp] # / software/bin/mysqladmin-usystem-paired system 'shutdown

160512 02:47:54 mysqld_safe mysqld from pid file / var/run/mysqld/mysqld.pid ended

[1] + Done / software/bin/mysqld_safe-- defaults-file=/etc/my.cnf (wd: ~)

(wd now: / tmp)

[root@localhost tmp] # / software/bin/mysqld_safe-- defaults-file=/etc/my.cnf &

[1] 40246

[root@localhost tmp] # 160512 02:48:13 mysqld_safe Logging to'/ log/err.log'.

160512 02:48:13 mysqld_safe Starting mysqld daemon with databases from / data

Mysql > show variables like 'relay_log%'

+-+ +

| | Variable_name | Value |

+-+ +

| | relay_log | / log/binlog/product-relay-bin |

| | relay_log_index | / log/binlog/product-relay-index |

| | relay_log_info_file | relay-log.info |

| | relay_log_purge | ON |

| | relay_log_recovery | OFF |

| | relay_log_space_limit | 0 | |

+-+ +

6 rows in set (0.00 sec)

-add a global read-only lock to node 1. If the application is connected to only one node, such as node 1, this step can be ignored

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

-- View the current log name and location of Node 2 for the following change master to command on Node 1

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000006 | 14078491 | |

+-+

1 row in set (0.00 sec)

-- execute the CHANGE MASTER statement on Node 1

Mysql > change master to

-> master_host='192.168.78.137'

-> master_port=5505

-> master_user='repl'

-> master_password='Mysql#2015'

-> master_log_file='mysql-bin.000006'

-> master_log_pos=14078491

Query OK, 0 rows affected (0.13 sec)

-- start the application thread

Mysql > start slave

-Node 1 releases the global read-only lock

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

-- View synchronization status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.78.137

Master_User: repl

Master_Port: 5505

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 14078491

Relay_Log_File: product-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Add the relevant parameters of the sequence to the configuration files of the two nodes to avoid conflicts caused by the same sequence.

-Node 1

The sequence on node 1 starts from 1, and the growth value is 2, which is odd, such as 1, 3, 5.

# # set this to server-id value

Auto_increment_offset = 1

# # set this to the number of mysql servers

Auto_increment_increment = 2

-Node 2

The sequence on node 2 starts from 2, and the growth value is 2, which is odd, such as 2, 4, 6.

# # set this to server-id value

Auto_increment_offset = 2

# # set this to the number of mysql servers

Auto_increment_increment = 2

After reading the above, have you mastered the method of MySQL 5.5master replication building process? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.