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

This article will explain in detail how to build the non-stop master of mysql 5.6. the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Environment description:

Version version 5.6.25-log

Main 1 Library ip: 10.219.24.25

Master 2 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

Mysql 5.6installation: http://blog.csdn.net/zhang123456456/article/details/53608554

Xtrabackup installation: http://blog.csdn.net/zhang123456456/article/details/72836184

The whole process: first build one master and one slave, and then build it the other way around, that is, double master replication:

First, one master, one slave:

1. Adjust the parameters of the main library

-- stop the main library mysql

[root@mysql02 ~] # service mysql stop

[root@mysql02 ~] # netstat-nltp | grep mysql | grep 3606

-- the main library creates a relay log directory

[root@mysql02 full] # mkdir-p / data/mysql/relaylog/

[root@mysql02 full] # chown-R mysql:mysql / data/mysql/relaylog

-- adjust my.cnf parameters

[root@mysql02 ~] # 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

Relay-log = / data/mysql/relaylog/relay

Relay-log-index = / data/mysql/relaylog/relay

Relay_log_purge = on

Binlog_format = mixed

Innodb_data_file_path = ibdata1:12M:autoextend

Auto_increment_increment = 10

Auto_increment_offset = 1

[mysql]

Default-character-set = utf8

Description:

A. Parameters that must be configured in the main library

Server-id (master and slave server-id must be different), log_bin, binlog_format, relay-log, relay-log-index, relay_log_purge

Auto-increment-offset 、 auto-increment-increment

B, log-slave-updates means whether these changes need to be included in your own binarylog after the relay log is executed. It needs to be turned on when your B server needs to be the master server for another server. That is, two masters back up each other, or multi-master cycle backup. We need it here, so open it.

C, auto-increment-offset and auto-increment-increment are used to back up each other in a dual-master (multi-master loop). Because each database server may insert data in the same table, if the table has an automatically growing primary key, primary key conflicts will occur on multiple servers. The solution to this problem is to make the self-increasing primary keys of each database discontiguous. As shown in the figure above, I assume that 10 servers may be needed for backup in the future, so auto-increment-increment is set to 10. 0. And auto-increment-offset=1 represents the serial number of the server. Starting from 1, do not exceed auto-increment-increment. After doing this, the first id I insert on this server is 1, and the id on the second line is 11, not 2. (similarly, the first id inserted on the second server is 2, and the second line is 12, which will be described later) so that there is no primary key conflict. We will demonstrate the effect of this id later.

-- start the main library

[root@mysql02] # mysqld_safe-- defaults-file=/etc/my.cnf &

2. Adjust the parameters from the library

-- stop mysql from library

[root@mysql01 ~] # service mysql stop

[root@mysql01 ~] # netstat-nltp | grep mysql | grep 3606

-- adjust my.cnf parameters

[root@mysql01 ~] # 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

Auto_increment_increment = 10

Auto_increment_offset = 2

[mysql]

Default-character-set = utf8

Description: parameters that must be configured from the library

Server-id 、 log_bin 、 relay-log 、 relay_log_purge 、 auto-increment-offset 、 auto-increment-increment 、

3. Backup of the main database

-- main library backup directory

[root@mysql02 full] # pwd

/ xtrabackup/full

-- innobackupex backup of the main library

[root@mysql02] # innobackupex-- user=root-- password=oracle-- port=3606 / xtrabackup/full/

170610 17:50:23 Backup created in directory'/ xtrabackup/full/2017-06-10 billion 17-50-19 Universe

MySQL binlog position: filename 'binlog.000010', position' 120'

....

170610 17:50:23 completed OK!

-- check backup binlog number and cut-off position

[root@mysql02 2017-06-10 17-50-19] # cat xtrabackup_binlog_info

Binlog.000010 120

4. Create the same backup directory as the master library from the slave library.

[root@mysql01] # mkdir-p / xtrabackup/full

[root@mysql01] # chown-R mysql:mysql / xtrabackup/full/

5. The master library will back up the scp to the slave library

[root@mysql02 full] # pwd

/ xtrabackup/full

[root@mysql02 full] # scp-r 2017-06-10 10.219.24.22:/xtrabackup/full 17-50-19

6. View the backups from scp from the library

[root@mysql01] # cd / xtrabackup/full/2017-06-10 17-50-19 /

[root@mysql01 2017-06-10 17-50-19] # ll

Total 12320

-rw-r-. 1 root root 419 Jun 10 18:01 backup-my.cnf

-rw-r-. 1 root root 12582912 Jun 10 18:01 ibdata1

Drwxr-x---. 2 root root 4096 Jun 10 18:01 mysql

Drwxr-x---. 2 root root 4096 Jun 10 18:01 performance_schema

Drwxr-x---. 2 root root 4096 Jun 10 18:01 test

-rw-r-. 1 root root 18 Jun 10 18:01 xtrabackup_binlog_info

-rw-r-. 1 root root 113 Jun 10 18:01 xtrabackup_checkpoints

-rw-r-. 1 root root 482 Jun 10 18:01 xtrabackup_info

-rw-r-. 1 root root 2560 Jun 10 18:01 xtrabackup_logfile

7. Create synchronous users in the main library

Mysql > GRANT replication slave ON *. * TO 'slave25'@'%' IDENTIFIED BY' oracle'

Query OK, 0 rows affected (0.05 sec)

8. Recover the main database data from the library

Rename the original datadir folder to a new location from the library and create the original folder

[root@mysql01 ~] # mv / data/mysql / data/mysqlbak

[root@mysql01] # mkdir-p / data/mysql

-- innobackupex apply-log

[root@mysql01 ~] # innobackupex-- apply-log-- user=oracle\

-- password=oracle-- port=3606 / xtrabackup/full/2017-06-1010 17-50-19 /

-- innobackupex copy recovered files to the original data location

[root@mysql01 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 them

[root@mysql01] # mkdir-p / data/mysql/binarylog

[root@mysql01] # mkdir-p / data/mysql/relaylog/

[root@mysql01 mysql] # chown-R mysql:mysql / data/mysql

9. Slave library configuration and detection

-- launch from the library

[root@mysql01 mysql] # mysqld_safe-- defaults-file=/etc/my.cnf &

-- specify the basic information synchronized with the master library from the slave 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: sets the ip address of the primary server to connect to

MASTER_USER: sets the user name of the primary server to connect to

MASTER_PASSWORD: sets the password of the primary server to connect to

MASTER_LOG_FILE: sets the log name of the bin log of the primary server to connect to

MASTER_LOG_POS: sets the location of the bin log of the primary server to connect to

-- start slave status (start listening for msater changes)

Mysql > start slave

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

-- check the status of slave.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.219.24.25 # main library IP

Master_User: slave25 # users replicated by the main library

Master_Port: 3306 # main library mysqld

Connect_Retry: 60

Master_Log_File: binlog.000010 # io_thread reads the main library master_log_file

Read_Master_Log_Pos: 717 # io_thread reads the main library master_log_pos

Relay_Log_File: relay.000002

Relay_Log_Pos: 877

Relay_Master_Log_File: binlog.000010 # sql_thread executes the master_log_file of the main library

Slave_IO_Running: the key of Yes # is whether io_thread is running

Slave_SQL_Running: the key of Yes # is whether sql_thread is 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 executes 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 of slave 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 has 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

You 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) > one master and one slave synchronization succeeded!

# # #

# so far, the replication from A to B has been configured, and the replication from B to An is configured below. #

# # #

Declaration > in the following operations, the new master database is the original slave database (10.219.24.22), and the new slave database is the original master library (10.219.24.25).

11. Create synchronous users in the new main library

Mysql > GRANT replication slave ON *. * TO 'slave22'@'%' IDENTIFIED BY' oracle'

Query OK, 0 rows affected (0.00 sec)

12. Check the binlog file number and position point in the new main library.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | binlog.000004 | 313 |

+-+

1 row in set (0.00 sec)

13. The new slave library specifies the basic information synchronized with the master library.

Mysql >

Change master to

Master_host='10.219.24.22'

Master_port=3306

Master_user='slave22'

Master_password='oracle'

Master_log_file='binlog.000004'

Master_log_pos=313

Query OK, 0 rows affected, 2 warnings (0.04 sec)

14. Open the slave replication function in the new slave library.

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

15. New slave library detects synchronous replication status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.219.24.22

Master_User: slave22

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000004

Read_Master_Log_Pos: 313

Relay_Log_File: relay.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000004

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: 313

Relay_Log_Space: 443

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: 22

Master_UUID: 70023652-4dc7-11e7-9360-000c2944297a

Master_Info_File: / data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has 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

-- Test data synchronization status of new slave library

Mysql > create database mm_repl

Query OK, 1 row affected (0.00 sec)

Mysql > use mm_repl

Database changed

Mysql > create table mm_repl (id int auto_increment,name varchar (10), primary key (id))

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into mm_repl (name) values ("andy"), ("taoYe")

Query OK, 1 row affected (0.00 sec)

Mysql > select * from mm_repl

+-+ +

| | id | name |

+-+ +

| | 1 | andy |

| | 11 | taoYe |

+-+ +

2 rows in set (0.00 sec)

-- Test data synchronization status of the new main library

Mysql > select * from mm_repl

+-+ +

| | id | name |

+-+ +

| | 1 | andy |

| | 11 | taoYe |

+-+ +

2 rows in set (0.00 sec)

Mysql > insert into mm_repl (name) values ("andy"), ("taoYe")

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > select * from mm_repl

+-+ +

| | id | name |

+-+ +

| | 1 | andy |

| | 11 | taoYe |

| | 12 | andy |

| | 22 | taoYe |

+-+ +

4 rows in set (0.00 sec)

-- New slave library check synchronous replication

Mysql > select * from mm_repl

+-+ +

| | id | name |

+-+ +

| | 1 | andy |

| | 11 | taoYe |

| | 12 | andy |

| | 22 | taoYe |

+-+ +

4 rows in set (0.00 sec) > master master synchronization test succeeded

On how to carry out mysql 5.6 non-stop master building to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.